玉面飞龙的BLOG

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]

The following commands will be used in this process: 

1.  The ‘alter session’ command is used to dump redo headers. 

2.  Use the ‘alter system dump logfile’ to dump log file contents. 

This command requires ‘ALTER SYSTEM’ system privilege. The database can be in
mount, nomount or open state when the command is issued.  An online log file
or an archived log file can be dumped.  It is even possible to dump a
file from another database, as long as the operating systems are the same. 

Output from the command is put into the session’s trace file.

The following ways of dumping a redo log file are covered:

1. To dump records based in DBA (Data Block Address)
2. To dump records based on RBA (Redo Block Address)
3. To dump records based on SCN
4. To dump records based on time
5. To dump records based on layer and opcode
6. Dump the file header information
7. Dump an entire log file: 

1. To dump records based on DBA  (Data Block Address)
   ————————————————– 

This will dump all redo records for the range of data
blocks specified for a given file # and block # range. 

From sqldba or svrmgr, issue the following command: 

ALTER SYSTEM DUMP LOGFILE ‘filename’
 DBA MIN fileno . blockno
   DBA MAX fileno . blockno; 

        Example:
        ========
        ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
        DBA MIN 5 . 31125
        DBA MAX 5 . 31150; 

This will cause all the changes to the specified range of data blocks to be
dumped to the trace file.  In the example given, all redo records for file #5,
blocks 31125 thru 31150 are dumped. 

Note
====
For 10g:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
    DBA MIN 5 . 31125 DBA MAX 5 . 31150;

will raise:
  ORA-01963: Must specify a block number

In 10g we need to skip the dot ‘.’ while doing the redo dumps
    ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
    DBA MIN 5 31125 DBA MAX 5 31150;

2. To dump records based on RBA (Redo Block Address)
   ————————————————- 

This will dump all redo records for the range of redo
addresses specified for the given sequence number and block number. 

Syntax:
ALTER SYSTEM DUMP LOGFILE ‘filename’
   RBA MIN seqno . blockno
   RBA MAX seqno . blockno; 

Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
   RBA MIN 2050 . 13255
   RBA MAX 2255 . 15555;

3. To dump records based on SCN
   —————————- 

Using this option will cause redo records owning changes within the SCN range
specified to be dumped to the trace file. 

ALTER SYSTEM DUMP LOGFILE ‘filename’
   SCN MIN minscn
   SCN MAX maxscn;

Example:
ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
   SCN MIN 103243
   SCN MAX 103294;

If the purpose is to check the dumpfile you can rather do the following,
SQL> ALTER SYSTEM DUMP LOGFILE ‘filename’ SCN MIN 1 SCN MAX 1;

If the above completes sucessfully it ensures no issues with the archivelog.

4. To dump records based on time.
   —————————— 

Using this option will cause redo records created within the time range
specified to be dumped to the trace file. 

From sqldba or svrmgr, issue the following command: 

ALTER SYSTEM DUMP LOGFILE ‘filename’
   TIME MIN value
   TIME MAX value; 

        Example:
        ========
        ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
        TIME MIN 299425687
        TIME MAX 299458800; 

        Please Note: the time value is given in REDO DUMP TIME 

5. To dump records based on layer and opcode.
   —————————————— 

LAYER and OPCODE are used to dump all log records for a particular type of
redo record, such as all dropped row pieces. 

From sqldba or svrmgr, issue the following command: 

ALTER SYSTEM DUMP LOGFILE ‘filename’
   LAYER value
   OPCODE value; 

        Example:
        ========
        ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’
        LAYER 11
        OPCODE 3; 

6. Dump the file header information:
   ——————————— 

This will dump file header information for every
online redo log file. 

From sqldba or svrmgr, issue the following command: 

   alter session set events ‘immediate trace name redohdr level 10′; 

7. Dump an entire log file:
   ———————— 

From sqldba or svrmgr, issue the following command: 

ALTER SYSTEM DUMP LOGFILE ‘filename’; 

 Please note:
 Fully qualify the filename, and include the single quotes. 

 Example:
 ========
 ALTER SYSTEM DUMP LOGFILE ‘u01/oracle/V7323/dbs/arch1_76.dbf’;

一个例子，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 =