How to Dump Redo Log File Information

How to Dump Redo Log File Information

When you encounter an archivelog corruption issue as seen from an ora-353 or ora-600 [3020] (stuck recovery) , or you need to know what&who&how&where were changed It is a common practice to dump the archivelog to determine the nature of the corruption&change.

And Dump Redo Log File has muptiple fashion,its syntax is list below.[DocID 1031381.6]

一个例子，dump哪些Block在哪个日志文件作了什么修改。

SQL> create table test tablespace users01 as select * from dba_objects where rownum<10;
Table created.

SQL> alter system switch logfile;
System altered.

SQL> select header_file,header_block,blocks from dba_segments where owner=user and segment_name=’TEST’;
HEADER_FILE HEADER_BLOCK BLOCKS
———– ———— ———-
9 9 1280

SQL> delete from test where rownum<10;
9 rows deleted.
SQL> commit;
Commit complete.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Oldest online log sequence 3135
Next log sequence to archive 3138
Current log sequence 3138

SQL> alter system switch logfile;
System altered.

SQL> ALTER SYSTEM DUMP LOGFILE ‘/oracle/ORCL/archive/ORCL/ORCL_3138.arc’ DBA MIN 9 . 9 DBA MAX 9 . 10;
System altered.

摘录部分trace文件

REDO RECORD - Thread:1 RBA: 0×000c42.00000006.0010 LEN: 0×01dc VLD: 0×01
SCN: 0×0000.1bfa48a3 SUBSCN: 1 04/01/2007 19:46:22
CHANGE #1 TYP:0 CLS:65 AFN:79 DBA:0×13c00039 SCN:0×0000.1bfa4886 SEQ: 1 OP:5.2
ktudh redo: slt: 0×0019 sqn: 0×0004dd67 flg: 0×0012 siz: 240 fbi: 0
uba: 0×1c40a0fb.43b1.11 pxid: 0×0000.000.00000000
CHANGE #2 TYP:0 CLS:66 AFN:113 DBA:0×1c40a0fb SCN:0×0000.1bfa4885 SEQ: 11 OP:5.1
ktudb redo: siz: 240 spc: 6414 flg: 0×0012 seq: 0×43b1 rec: 0×11
xid: 0×0019.019.0004dd67
ktubl redo: slt: 25 rci: 0 opc: 11.1 objn: 44770 objd: 44770 tsn: 3
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0×00000000 prev ctl uba: 0×1c40a0fb.43b1.06
prev ctl max cmt scn: 0×0000.1bfa4266 prev tx cmt scn: 0×0000.1bfa42b3
KDO undo record:
KTB Redo
op: 0×03 ver: 0×01
op: Z
KDO Op code: IRP row dependencies Disabled
xtype: XA bdba: 0×0240000a hdba: 0×02400009
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0×0) size/delt: 76
fb: –H-FL– lb: 0×0 cc: 13
null:
01234567890123456789012345678901234567890123456789012345678901234567890123456789
–N———-
col 0: [ 3] 53 59 53
col 1: [ 7] 41 43 43 45 53 53 24
col 2: *NULL*
col 3: [ 2] c1 62
col 4: [ 2] c1 62
col 5: [ 5] 54 41 42 4c 45
col 6: [ 7] 78 6a 06 0d 02 1b 29
col 7: [ 7] 78 6a 0a 1c 01 2f 03
col 8: [19] 32 30 30 36 2d 30 36 2d 31 33 3a 30 31 3a 32 36 3a 34 30
col 9: [ 5] 56 41 4c 49 44
col 10: [ 1] 4e
col 11: [ 1] 4e
col 12: [ 1] 4e
CHANGE #3 TYP:0 CLS: 1 AFN:9 DBA:0×0240000a SCN:0×0000.1bfa4831 SEQ: 2 OP:11.3
KTB Redo
op: 0×01 ver: 0×01
op: F xid: 0×0019.019.0004dd67 uba: 0×1c40a0fb.43b1.11
KDO Op code: DRP row dependencies Disabled
xtype: XA bdba: 0×0240000a hdba: 0×02400009
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 0(0×0)
CHANGE #4 MEDIA RECOVERY MARKER SCN:0×0000.00000000 SEQ: 0 OP:5.20
session number = 124
serial number = 28620
transaction name =

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8432937/viewspace-1029251/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8432937/viewspace-1029251/