Shulou(Shulou.com)06/01 Report--

This article explains how to obtain a dump in an online or archived redo log file.

Constraints and restrictions:

one. The database must be installed (or open).

Changing the system dump log file is independent of any instance, so you do not need to install the database for its operation.

However, in the case of ALTER SYSTEM DUMP REDO, the system needs to know what the instance is and where the other log files are.

This lookup requires a control file, so you must install or open the database.

two. DUMP REDO restricts the set of log files identified in the control file.

Because we look for log files and instances in the control file, if there are unreferenced redo logs in the control file, these redo logs will not be considered in the dump file.

An example of this is if the log file is deleted, manually or the instance is deleted from the RAC cluster.

three. All log files must be accessed from the calling instance, and although all online redo logs are stored on a shared disk, archived logs for each instance are not required.

Here is how to dump the redo log file:

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 sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename'

DBA MIN fileno blockno

DBA MAX fileno blockno

Example:

=

ALTER SYSTEM DUMP LOGFILE 'u01Universe oracleAccord V7323 Universe dbsUnique 2percent 76.dbf'

DBA MIN 5 31125

DBA MAX 5 31150

This results in all changes to the specified range of blocks.

Dump to a trace file. In the given example, all the records of the redo file # 5

Sections 31125 to 31150 are dump

Note

=

For 10g:

ALTER SYSTEM DUMP LOGFILE 'u01Universe oracleAccord V7323 Universe dbsUnique 2percent 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 'u01Universe oracleAccord V7323 Universe dbsUnique 2percent 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 'u01Universe oracleAccord V7323 Universe dbsUnique 2percent 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 Universe oracleUniverse V7323 Universe dbsUnique 2percent 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 sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename'

TIME MIN value

TIME MAX value

Example:

=

ALTER SYSTEM DUMP LOGFILE 'u01Universe oracleAccord V7323 Universe dbsUnique 2percent 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 sqlplus (sqldba or svrmgr for older versions), issue the following command:

ALTER SYSTEM DUMP LOGFILE 'filename'

LAYER value

OPCODE value

Example:

=

ALTER SYSTEM DUMP LOGFILE 'u01Universe oracleAccord V7323 Universe dbsUnique 2percent 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 sqlplus (sqldba or svrmgr for older versions), issue the following command:

Alter session set events' immediate trace name redohdr level 10'

For dumping archivelog header,issue the following command:

ALTER SYSTEM DUMP 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 qualify the filename, and include the single quotes.

Example:

=

ALTER SYSTEM DUMP LOGFILE 'u01 Universe oracleUniverse V7323 Universe dbsUnique 2percent 76.dbf'

Add:

ALTER SYSTEM DUMP REDO [option].... [option]

[options]-> scn min [scn] | scn max [scn] |

Dba min [file#] [block#] | dba max [file#] [block#] |

Time min [ub4] | time max [ub4] |

Layer [word] |

Opcode [word] |

Objno [word] |

Xid [undoseg#] [slot#] [wrap#] |

Validate

Reference: How to Dump Redo Log File Information (documentation ID 1031381.6)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.