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
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