01,创建环境
SQL> create table t3 (id int);
Table created.
SQL> insert into t3 values(1);
1 row created.
SQL> insert into t3 values(2);
1 row created.
SQL> insert into t3 values(3);
1 row created.
SQL> commit;
Commit complete.
02,查看块
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),id from t3;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
------------------------------------ ------------------------------------ ----------
1 104537 1
1 104537 2
1 104537 3
03,更新事务
SQL> update t3 set id=5 where id=3;
1 row updated.
这时候更新了下数据库
04,查看回滚段
SQL> desc v$transaction --视图信息查看
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(8)
XIDUSN NUMBER
XIDSLOT NUMBER
XIDSQN NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBASQN NUMBER
UBAREC NUMBER
STATUS VARCHAR2(16)
START_TIME VARCHAR2(20)
START_SCNB NUMBER
START_SCNW NUMBER
START_UEXT NUMBER
START_UBAFIL NUMBER
START_UBABLK NUMBER
START_UBASQN NUMBER
START_UBAREC NUMBER
SES_ADDR RAW(8)
FLAG NUMBER
SPACE VARCHAR2(3)
RECURSIVE VARCHAR2(3)
NOUNDO VARCHAR2(3)
PTX VARCHAR2(3)
NAME VARCHAR2(256)
PRV_XIDUSN NUMBER
PRV_XIDSLT NUMBER
PRV_XIDSQN NUMBER
PTX_XIDUSN NUMBER
PTX_XIDSLT NUMBER
PTX_XIDSQN NUMBER
DSCN-B NUMBER
DSCN-W NUMBER
USED_UBLK NUMBER
USED_UREC NUMBER
LOG_IO NUMBER
PHY_IO NUMBER
CR_GET NUMBER
CR_CHANGE NUMBER
START_DATE DATE
DSCN_BASE NUMBER
DSCN_WRAP NUMBER
START_SCN NUMBER
DEPENDENT_SCN NUMBER
XID RAW(8)
PRV_XID RAW(8)
PTX_XID RAW(8)
CON_ID NUMBER
Column Datatype Description
ADDR RAW(4 | 8) Address of the transaction state object
XIDUSN NUMBER Undo segment number
XIDSLOT NUMBER Slot number
XIDSQN NUMBER Sequence number
UBAFIL NUMBER Undo block address (UBA) filenum
UBABLK NUMBER UBA block number
UBASQN NUMBER UBA sequence number
UBAREC NUMBER UBA record number
STATUS VARCHAR2(16) Status
START_TIME VARCHAR2(20) Start time (wall clock)
START_SCNB NUMBER Start system change number (SCN) base
START_SCNW NUMBER Start SCN wrap
START_UEXT NUMBER Start extent number
START_UBAFIL NUMBER Start UBA file number
START_UBABLK NUMBER Start UBA block number
START_UBASQN NUMBER Start UBA sequence number
START_UBAREC NUMBER Start UBA record number
SES_ADDR RAW(4 | 8) User session object address
FLAG NUMBER Flag
SPACE VARCHAR2(3) YES if a space transaction
RECURSIVE VARCHAR2(3) YES if a recursive transaction
NOUNDO VARCHAR2(3) YES if a no undo transaction
PTX VARCHAR 2(3) YES if parallel transaction
NAME VARCHAR2(256) Name of a named transaction
PRV_XIDUSN NUMBER Previous transaction undo segment number
PRV_XIDSLT NUMBER Previous transaction slot number
PRV_XIDSQN NUMBER Previous transaction sequence number
PTX_XIDUSN NUMBER Rollback segment number of the parent XID
PTX_XIDSLT NUMBER Slot number of the parent XID
PTX_XIDSQN NUMBER Sequence number of the parent XID
DSCN-B NUMBER This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_BASE.
DSCN-W NUMBER This column is obsolete and maintained for backward compatibility. The value of this column is always equal to the value in DSCN_WRAP.
USED_UBLK NUMBER Number of undo blocks used
USED_UREC NUMBER Number of undo records used
LOG_IO NUMBER Logical I/O
PHY_IO NUMBER Physical I/O
CR_GET NUMBER Consistent gets
CR_CHANGE NUMBER Consistent changes
START_DATE DATE Start time (wall clock)
DSCN_BASE NUMBER Dependent SCN base
DSCN_WRAP NUMBER Dependent SCN wrap
START_SCN NUMBER Start SCN
DEPENDENT_SCN NUMBER Dependent SCN
XID RAW(8) Transaction XID
PRV_XID RAW(8) Previous transaction XID
PTX_XID RAW(8) Parent transaction XID
SQL> select UBAFIL,UBABLK,XIDUSN,XIDSLOT,XIDSQN,START_SCNB from v$transaction ;
UBAFIL UBABLK XIDUSN XIDSLOT XIDSQN START_SCNB
---------- ---------- ---------- ---------- ---------- ----------
4 4955 9 16 2339 5870968
包含的信息详细内容查看上
05,查看段名
SQL> select * from v$rollname; --这里对应的是XIDUSN
USN NAME CON_ID
---------- ------------------------------ ----------
0 SYSTEM 1
1 _SYSSMU1_762089623$ 1
2 _SYSSMU2_3062791661$ 1
3 _SYSSMU3_1499641855$ 1
4 _SYSSMU4_3564003469$ 1
5 _SYSSMU5_1728379857$ 1
6 _SYSSMU6_965511687$ 1
7 _SYSSMU7_2247632671$ 1
8 _SYSSMU8_437891266$ 1
9 _SYSSMU9_3215744559$ 1
10 _SYSSMU10_2925533193$ 1
可以查看到这个事务的Undo segment number 为9
根据这个id查找到段名为 _SYSSMU9_3215744559$
所以我们直接可以dump 出这个段头即可
06,dump 段头
SQL> alter system dump undo header '_SYSSMU9_3215744559$';
System altered.
SQL>
07,查看当前会话id
SQL> select spid from v$process where addr in (select paddr from v$session where sid=(select sid from v$mystat where rownum=1));
SPID
------------------------
1392
这个会话id 跟随着物理磁盘存储的id,寻找到这个也就能找到dump 出来的东西
这是时候去找这个文件,查看内容就有对应的信息
08,dump 块
这时候也可以dump 数据块
SQL> select UBAFIL,UBABLK,XIDUSN,XIDSLOT,XIDSQN,START_SCNB from v$transaction ;
UBAFIL UBABLK XIDUSN XIDSLOT XIDSQN START_SCNB
---------- ---------- ---------- ---------- ---------- ----------
4 4955 9 16 2339 5870968
这里就是写了4号文件块4955块
我们就dump 这个出来
alter system dump datafile 4 block 4955;
09,或通过id dump数据块
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),id from t3;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID
------------------------------------ ------------------------------------ ----------
1 104537 1
1 104537 2
1 104537 3
所以dump 这个也可以的
alter system dump datafile 1 block 104537 ;
10,查看dump
对接上第一个dump操作
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
查看这个文件就行了
1 [root@node12c01 oracle]# ll /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
2 -rw-r----- 1 oracle oinstall 1420 Apr 19 04:00 /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
3 [root@node12c01 oracle]# more /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
4 Trace file /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
5 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
6 Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
7 ORACLE_HOME: /orcl/app/oracle/product/12.1.0/db_1
8 System name: Linux
9 Node name: node12c01
10 Release: 3.10.0-693.el7.x86_64
11 Version: #1 SMP Tue Aug 22 21:09:27 UTC 2017
12 Machine: x86_64
13 Instance name: orcl
14 Redo thread mounted by this instance: 1
15 Oracle process number: 56
16 Unix process pid: 1392, image: oracle@node12c01 (TNS V1-V3)
17
18
19 *** 2019-04-19T04:00:53.883414-04:00 (CDB$ROOT(1))
20 *** SESSION ID:(28.29402) 2019-04-19T04:00:53.883452-04:00
21 *** CLIENT ID:() 2019-04-19T04:00:53.883457-04:00
22 *** SERVICE NAME:(SYS$USERS) 2019-04-19T04:00:53.883462-04:00
23 *** MODULE NAME:(sqlplus@node12c01 (TNS V1-V3)) 2019-04-19T04:00:53.883466-04:00
24 *** ACTION NAME:() 2019-04-19T04:00:53.883471-04:00
25 *** CLIENT DRIVER:(SQL*PLUS) 2019-04-19T04:00:53.883474-04:00
26 *** CONTAINER ID:(1) 2019-04-19T04:00:53.883478-04:00
27
28 Processing Oradebug command 'setmypid'
29
30 *** 2019-04-19T04:00:53.883507-04:00 (CDB$ROOT(1))
31 Oradebug command 'setmypid' console output: <none>
32
33 *** 2019-04-19T04:00:59.063237-04:00 (CDB$ROOT(1))
34 Processing Oradebug command 'tracefile_name'
35
36 *** 2019-04-19T04:00:59.063299-04:00 (CDB$ROOT(1))
37 Oradebug command 'tracefile_name' console output:
38 /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
View Code
dump 归类
01,undo头信息dump
SQL> alter system dump undo header '_SYSSMU9_3215744559$';
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
02,dump整个block
SQL> select UBAFIL,UBABLK,XIDUSN,XIDSLOT,XIDSQN,START_SCNB from v$transaction;
UBAFIL UBABLK XIDUSN XIDSLOT XIDSQN START_SCNB
---------- ---------- ---------- ---------- ---------- ----------
4 4955 9 16 2339 5870968
SQL> alter system dump datafile 4 block 4955;
System altered.
SQL> oradebug tracefile_name
/orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
文件内容略
03,按照事务进行dump
SQL> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_3215744559$' XID 9 16 2339;
System altered.
SQL> oradebug tracefile_name
/orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
1 [root@node12c01 oracle]# more /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
2 Trace file /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
3 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
4 Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
5 ORACLE_HOME: /orcl/app/oracle/product/12.1.0/db_1
6 System name: Linux
7 Node name: node12c01
8 Release: 3.10.0-693.el7.x86_64
9 Version: #1 SMP Tue Aug 22 21:09:27 UTC 2017
10 Machine: x86_64
11 Instance name: orcl
12 Redo thread mounted by this instance: 1
13 Oracle process number: 56
14 Unix process pid: 1392, image: oracle@node12c01 (TNS V1-V3)
15
16
17 *** 2019-04-19T04:16:25.988842-04:00 (CDB$ROOT(1))
18 *** SESSION ID:(28.29402) 2019-04-19T04:16:25.988868-04:00
19 *** CLIENT ID:() 2019-04-19T04:16:25.988873-04:00
20 *** SERVICE NAME:(SYS$USERS) 2019-04-19T04:16:25.988877-04:00
21 *** MODULE NAME:(sqlplus@node12c01 (TNS V1-V3)) 2019-04-19T04:16:25.988882-04:00
22 *** ACTION NAME:() 2019-04-19T04:16:25.988886-04:00
23 *** CLIENT DRIVER:(SQL*PLUS) 2019-04-19T04:16:25.988889-04:00
24 *** CONTAINER ID:(1) 2019-04-19T04:16:25.988894-04:00
25
26
27 *** TRACE FILE RECREATED AFTER BEING REMOVED ***
28
29
30 ********************************************************************************
31 Undo Segment: _SYSSMU9_3215744559$ (9)
32 xid: 0x0009.010.00000923
33 Low Blk : (0, 0)
34 High Blk : (2, 127)
35 Object Id : ALL
36 Layer : ALL
37 Opcode : ALL
38 Level : 2
39
40 ********************************************************************************
41 UNDO BLK: Extent: 2 Block: 91 dba (file#, block#): 4,0x0000135b
42 xid: 0x0009.010.00000923 seq: 0x306 cnt: 0x37 irb: 0x37 icl: 0x0 flg: 0x0000
43
44 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
45 ---------------------------------------------------------------------------
46 0x01 0x1f70 0x02 0x1f1c 0x03 0x1e94 0x04 0x1e20 0x05 0x1d7c
47 0x06 0x1cf4 0x07 0x1c94 0x08 0x1c2c 0x09 0x1bd8 0x0a 0x1b78
48 0x0b 0x1b10 0x0c 0x1aa4 0x0d 0x1a50 0x0e 0x19f0 0x0f 0x197c
49 0x10 0x1914 0x11 0x18ac 0x12 0x184c 0x13 0x17f8 0x14 0x1798
50 0x15 0x16e4 0x16 0x166c 0x17 0x1604 0x18 0x159c 0x19 0x14e8
51 0x1a 0x143c 0x1b 0x1390 0x1c 0x12e4 0x1d 0x1238 0x1e 0x118c
52 0x1f 0x10e0 0x20 0x1034 0x21 0x0f88 0x22 0x0edc 0x23 0x0e30
53 0x24 0x0d84 0x25 0x0cd8 0x26 0x0c60 0x27 0x0bb4 0x28 0x0b4c
54 0x29 0x0a98 0x2a 0x0a30 0x2b 0x0984 0x2c 0x091c 0x2d 0x08b4
55 0x2e 0x084c 0x2f 0x07e4 0x30 0x0794 0x31 0x0714 0x32 0x06c4
56 0x33 0x0644 0x34 0x05d4 0x35 0x0590 0x36 0x054c 0x37 0x04a8
57
58 *-----------------------------
59 * Rec #0x37 slt: 0x10 objn: 78557(0x000132dd) objd: 78557 tblspc: 0(0x00000000
60 )
61 * Layer: 11 (Row) opc: 1 rci 0x00
62 Undo type: Regular undo Begin trans Last buffer split: No
63 Temp Object: No
64 Tablespace Undo: No
65 rdba: 0x00000000Ext idx: 0
66 flg2: 0
67 *-----------------------------
68 uba: 0x0100135b.0306.34 ctl max scn: 0x00000000005988a6 prv tx scn: 0x000000000059
69 88ab
70 txn start scn: scn: 0x0000000000599622 logon user: 0
71 prev brb: 16782158 prev bcl: 0
72 KDO undo record:
73 KTB Redo
74 op: 0x03 ver: 0x01
75 compat bit: 4 (post-11) padding: 1
76 op: Z
77 Array Update of 1 rows:
78 tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 33
79 ncol: 1 nnew: 1 size: 0
80 KDO Op code: 21 row dependencies Disabled
81 xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x00419859 hdba: 0x00419858
82 itli: 2 ispac: 0 maxfr: 4863
83 vect = 0
84 col 0: [ 2] c1 04
85
86
87 +++++++++++ Next block not in extent map - rollback segment has been shrunk.
88 + WARNING + Block dba (file#, block#): 0,0x00000000
89 +++++++++++
90
91
92 *************************************
93 Total undo blocks scanned = 1
94 Total undo records scanned = 1
95 Total undo blocks dumped = 1
96 Total undo records dumped = 1
97
98 ##Total warnings issued = 1
99 *************************************
100
101 *** 2019-04-19T04:16:31.811478-04:00 (CDB$ROOT(1))
102 Processing Oradebug command 'tracefile_name'
103
104 *** 2019-04-19T04:16:31.811531-04:00 (CDB$ROOT(1))
105 Oradebug command 'tracefile_name' console output:
106 /orcl/app/oracle/diag/rdbms/node12c01/orcl/trace/orcl_ora_1392.trc
View Code
