How to Dump Redo Log File Information [ID 1031381.6]

摘录原文，备用。

 

PURPOSE

This article explain how to obtain a dump of theheader information in the

online redo log file(s), as well as obtaining selectedinformation from the

online or archived redo log files.

 

SCOPE & APPLICATION

Informational

 

You are working with Oracle Technical Support.  As part of the diagnostic

process, you have been asked to take a dump of theredo log files.   The

information in the logs is often used to help diagnosecorruption issues.

   

The following commands will be used in this process:

   

1.  The 'altersession' 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 isissued.  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 operatingsystems are the same.

 

Output from the command is put into the session'strace file.

 

The following ways of dumping a redo log file arecovered:

 

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 ofdata 

blocks specified for a given file # and block # range.

 

From sqlplus (sqldba or svrmgr for older versions),issue the following command:

 

ALTER SYSTEM DUMP LOGFILE 'filename'

 DBA MIN fileno. blockno

   DBA MAXfileno . blockno;

 

        Example:

        ========

        ALTERSYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'

        DBA MIN5 . 31125

        DBA MAX5 . 31150;

 

This will cause all the changes to the specified rangeof data blocks to be 

dumped to the trace file.  In the example given, all redo records forfile #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 theredo dumps

    ALTER SYSTEMDUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'

    DBA MIN 531125 DBA MAX 5 31150;

 

 

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

  -------------------------------------------------

 

This will dump all redo records for the range ofredo 

addresses specified for the given sequence number andblock 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 owningchanges within the SCN range 

specified to be dumped to the trace file.

 

ALTER SYSTEM DUMP LOGFILE 'filename'

   SCN MINminscn

   SCN MAXmaxscn;

 

Example:

ALTER SYSTEM DUMP LOGFILE'u01/oracle/V7323/dbs/arch1_76.dbf'

   SCN MIN103243

   SCN MAX103294;

 

If the purpose is to check the dumpfile you can ratherdo the following,

SQL> ALTER SYSTEM DUMP LOGFILE 'filename' SCN MIN 1SCN MAX 1;

 

If the above completes sucessfully it ensures noissues with the archivelog.

 

 

4. To dump records based on time.

  ------------------------------

 

Using this option will cause redo records createdwithin the time range 

specified to be dumped to the trace file.

 

From sqlplus (sqldba or svrmgr for older versions),issue the following command:

 

ALTER SYSTEM DUMP LOGFILE 'filename'

   TIME MINvalue

   TIME MAXvalue;

 

        Example:

        ========

        ALTERSYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'

        TIME MIN299425687

        TIME MAX299458800;

 

 

        PleaseNote: 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 fora particular type of

redo record, such as all dropped row pieces.

 

From sqlplus (sqldba or svrmgr for older versions),issue the following command:

 

ALTER SYSTEM DUMP LOGFILE 'filename'

   LAYER value

   OPCODE value;

 

        Example:

        ========

        ALTERSYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'

        LAYER 11

        OPCODE3;

 

 

6. Dump the file header information:

  ---------------------------------

 

This will dump file header information for every

online redo log file.

 

From sqlplus (sqldba or svrmgr for older versions),issue the following command:

  

   alter sessionset events 'immediate trace name redohdr level 10';

 

For dumping archivelog header,issue the followingcommand:

 

   ALTER SYSTEMDUMP LOGFILE 'filename' RBA MIN 1 1 RBA MAX 1 1;

 

7. Dump an entire log file:

  ------------------------

 

From sqlplus (sqldba or svrmgr for older versions),issue the following command:

 

ALTER SYSTEM DUMP LOGFILE 'filename';

 

 Please note:

 Fully qualifythe filename, and include the single quotes.

 

 

 Example:

 ========

 ALTER SYSTEMDUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf';

 

 

The dump of the logfile will be written into a tracefile in the udump destination.

Use the command 'show parameters dump' within ansqlplus session.

The ouput will show the location for the udumpdestination where

the trace file exists.