谈谈Oracle undo表空间(二)
session A >select id,name,rowid from tt;
ID NAME ROWID
---------- ---------- ------------------
1 a AAARFuAAIAAAAAQAAA
2 b AAARFuAAIAAAAAQAAB
3 c AAARFuAAIAAAAAQAAC
SQL> select dbms_rowid.rowid_relative_fno('AAARFuAAIAAAAAQAAA') as file#,
2 dbms_rowid.rowid_block_number('AAARFuAAIAAAAAQAAA') as block#
3 from dual; www.2cto.com
FILE# BLOCK#
---------- ----------
8 16
(5)根据查询到的文件号和块号进行dump,注意这个地方dump的其实是内存里面的数据,如果需要dump磁盘上的数据文件,那么把8改成具体的路径就可以了,因为oracle写是异步的,这个时候磁盘的数据文件并不一定已经有这个信息了.
www.2cto.com
session A >alter system dump datafile 8 block 16;
System altered.
session A >SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 FROM (SELECT p.spid
4 FROM v$mystat m, v$session s, v$process p
5 WHERE m.statisti
c# = 1
6 AND s.SID = m.SID
7 AND p.addr = s.paddr) p,
8 (SELECT t.INSTANCE
9 FROM v$thread t, v$parameter v
10 WHERE v.NAME = 'thread'
11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
www.2cto.com
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3827.trc
(6)打开trace文件,进行观察,分别截取开头和有关事务的内容:
[root@pmiscs ~]# more /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3790.trc
Dump file /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3790.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:
Linux www.2cto.com
Node name: pmiscs
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine: i686
Instance name: pmisdb
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 3790, image: oracle@pmiscs (TNS V1-V3)
*** ACTION NAME:() 2012-09-25 13:19:01.211
*** MODULE NAME:(sqlplus@pmiscs (TNS V1-V3)) 2012-09-25 13:19:01.211
*** SERVICE NAME:() 2012-09-25 13:19:01.211
*** SESSION ID:(544.3) 2012-09-25 13:19:01.211
Successfully allocated 2 recovery slaves
Using 543 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 613, block 2, scn 18284996
cache-low rba: logseq 613, block 449 www.2cto.com
on-disk rba: logseq 613, block 508, scn 18285712
change track rba: logseq 613, block 507, scn 18285711
start recovery at logseq 613, block 449, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 29Kb in 0.19s => 0.15 Mb/sec
Total physical reads: 4096Kb
Longest record: 2Kb, moves: 0/11 (0%)
Longest LWN: 6Kb, moves: 0/47 (0%), moved: 0Mb
Last redo scn: 0x0000.0117048f (18285711)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 12/21 = 0.6
----------------------------------------------
*** 2012-09-25 13:19:01.