Oracle Internals之Dumps

Dumps Immediate Dumps Conditional Dumps Memory Dumps File Dumps Immediate Dumps There are three ways of specifying an immediate dump Immediate dumps can be specified using the ALTER SESSION command ALTER SESSION SET EVENTS 'immediate trace

Dumps

Immediate Dumps

Conditional Dumps

<span> </span>

Memory Dumps

File Dumps

Immediate Dumps

There are three ways of specifying an immediate dump

Immediate dumps can be specified using the ALTER SESSION command

<span>    ALTER SESSION SET EVENTS</span>

    'immediate trace name dump level level';

Immediate dumps can be specified in ORADEBUG

    ORADEBUG DUMP dump level

Immediate dumps can also be specified using the DBMS_SYSTEM.SET_EV procedure

    EXECUTE dbms_system.set_ev (sid,serial#,65535,level,'dump');

Immediate dumps cannot be invoked from init.ora

Conditional Dumps

Conditional dumps are invoked when an error occurs

Conditional dump can be specified in the init.ora file e.g

    event="error trace name dump level level"

Conditional dumps can be specified in the current session

<span>    ALTER SESSION SET EVENTS</span>

    'error trace name dump level level';

Conditional dumps can be specified in ORADEBUG

    ORADEBUG EVENT error trace name dump level level

<span> </span>

    ORADEBUG SESSION_EVENT error trace name dump level level

Memory Dumps

Dumping the Global Area

The following command can be used to dump the global area

    ALTER SESSION SET EVENTS 'immediate trace name global_area level level';

Levels are (bitmask)

<span> </span>

Level	Description
1	Include PGA
2	Include SGA
4	Include UGA
8	Include indirect memory dumps

The following ORADEBUG command has the same effect

    ORADEBUG DUMP GLOBAL_AREA level

The structure of the fixed SGA is externalised in X$KSMFSV

<span> </span>

Name	Type	Description
ADDR	RAW(4)	<span> </span>
INDX	NUMBER	<span> </span>
INST_ID	NUMBER	<span> </span>
KSMFSNAM	VARCHAR2(64)	Name
KSMFSTYP	VARCHAR2(64)	Type
KSMFSADR	RAW(4)	Address
KSMFSSIZ	NUMBER	Size in Bytes

This can be queried using

    SELECT

        SUBSTR (ksmfsnam,1,20) AS "Name",

        SUBSTR (ksmfstyp,1,20) AS "Type",

        ksmfsadr AS "Address",

        ksmfssiz AS "Size"

    FROM x$ksmfsv;

<span> </span>

Dumping the Library Cache

The following command can be used to dump the library cache

    ALTER SESSION SET EVENTS 'immediate trace name library_cache level level';

where level is one of the following

<span> </span>

Level	Description
1	Dump library cache statistics
2	Include hash table histogram
3	Include dump of object handles
4	Include dump of object structures (heap 0)

For example

    ALTER SESSION SET EVENTS 'immediate trace name library_cache level 1';

The following ORADEBUG command has the same effect

    ORADEBUG DUMP LIBRARY_CACHE level

Dumping the Row (Dictionary) Cache

ROW_CACHE

The row cache is also known as the dictionary cache

The following command can be used to dump the row cache

<span>    ALTER SESSION SET EVENTS 'immediate trace name row_cache level <em>level</em>';</span>

where level is one of the following

<span> </span>

Level	Description
1	Dump row cache statistics
2	Include hash table histogram
8	Include dump of object structures

For example

<span>    ALTER SESSION SET EVENTS 'immediate trace name row_cache level 1';</span>

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP ROW_CACHE <em>level</em></span>

Dumping Multiple Buffers

Multiple Buffers

To dump buffer headers and buffer contents for buffers currently in the cache

<span>    ALTER SESSION SET EVENTS 'immediate trace name buffers level <em>level</em>';</span>

where level is one of the following

<span> </span>

Level	Description
1	Buffer headers only
2	Level 1 + block headers
3	Level 2 + block contents
4	Buffer headers only + hash chain
5	Level 1 + block headers + hash chain
6	Level 2 + block contents + hash chain
8	Buffer headers only + hash chain + users/waiters
9	Level 1 + block headers + hash chain + users/waiters
10	Level 2 + block contents + hash chain + users/waiters

Dumping Individual Buffers

Individual Buffers

In Oracle 8.0 and above is is possible to dump buffer all buffers currently in the cache for a specific block

For example where a block has been modified and is subject to consistent read from a number of transactions, there may be more than one copy of the block in the buffer cache

First identify（确定，定位，指定） the tablespace number for the block e.g for tablespace TS01

<span>    SELECT ts# FROM sys.ts$</span>

<span>    WHERE name = 'TS01';</span>

Set the tablespace number using

<span>    ALTER SESSION SET EVENTS </span>

<span>    'immediate trace name set_tsn_p1 level <em>level</em>';          //</span>

where level is the tablespace number + 1

Identify （确定，定位，指定）the relative DBA for the block

This is equal to

<span>    RelativeFileNumber * 4194304 + BlockNumber</span>

e.g. for a block with relative file number of 5 and a block number of 127874

<span>    5 * 4194304 + 127874 = 21099394</span>

Dump the buffer using

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name buffer level <em>level</em>';</span>

where level is the relative DBA e.g.

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name buffer level 21099394';

<span><strong>注释：
</strong></span></span><pre class="brush:php;toolbar:false"><span><strong><span> ALTER SESSION SET EVENTS </span></strong></span>

<span><strong><span>    'immediate trace name set_tsn_p1 level <em>level</em>';  
和
</span></strong></span><pre class="brush:php;toolbar:false"><span>ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name buffer level 21099394';</span>

这两句话要联合起来使用，才能把一个指定的数据块的内容给转存出来。
也就是说，dump（转存）一个指定的数据块的内容时，要将该数据块所在的表空间的表空间号提供给负责执行dump（转存）的进程（通过使用第一句话），之后，

<pre class="brush:php;toolbar:false"><span><strong>负责执行dump（转存）的进程还得知道该数据块的RDBA是多少（通过使用第二句话），这样，负责执行dump（转存）的进程就可以锁定（或说找到）该数据块的具
体位置从而将其内容转存出来。这里，说明一点，RDBA里使用的是相对文件号（<span>RelativeFileNumber</span>），</strong></span><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><span><strong>相对文件号（<span>RelativeFileNumber</span>）是给在一个表空间范围里的文件进行的编号，</strong></span><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><span><strong>DBA里使用的是绝对文件号（absolute<span>FileNumber</span>，简称</strong></span><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><span><strong><span>FileNumber</span>），</strong></span><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><strong>绝对文件号（absolute<span>FileNumber</span>，简称</strong><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><strong><span>FileNumber</span>）是</strong><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><pre class="brush:php;toolbar:false"><strong>给在一个数据库范围里的文件进行的编号。</strong>

<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">

Copy after login

<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">

Copy after login

Dumping Memory Heaps

Memory Heaps

To dump the top-level heap in a memory area

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name heapdump level <em>level</em>';</span>

Levels are

<span> </span>

Level	Description
1	PGA summary
2	SGA summary
4	UGA summary
8	Callheap (Current)
16	Callheap (User)
32	Large pool
64	Streams pool
128	Java pool
1025	PGA with contents
2050	SGA with contents
4100	UGA with contents
8200	Callheap with contents (Current)
16400	Callheap with contents (User)
32800	Large pool with contents
65600	Streams pool with contents
131200	Java pool with contents

Levels are correct to Oracle 10.2.0.1

The levels can be combined. For example a level 3 dump will contain both the PGA and SGA

Dumping Memory Subheaps

Memory Subheaps

Each heap may have one or more subheaps. These can be dumped using the command

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name heapdump_addr level <em>level</em>';</span>

where the level is the address of the subheap.

The syntax for this command changed in Oracle 9.2

Oracle 9.0.1 and below

Oracle 9.2.0 and above

Oracle 9.0.1 and below

For Oracle 9.0.1 and below a summary dump is obtained by setting the level to the decimal value of the address. A detailed dump is obtained by adding 1 to the decimal value of the address.

For example to dump the subheap at address 0x8057eb78, first convert the address to decimal (2153245560)

The subheap address can be found in the heapdump, for example

<span>    ds=0x8057eb78</span>

For a summary dump use

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name heapdump_addr level 2153245560';</span>

For a detailed dump, add 1 to the address e.g.

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name heapdump_addr level 2153245561';</span>

Oracle 9.2.0 and above

In Oracle 9.2 and above, for a summary dump

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name heapdump_addr level 1, addr 2153245560';</span>

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP HEAPDUMP_ADDR 1 2153245560</span>

In Oracle 9.2 and above, for a detailed dump

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name heapdump_addr level 2, addr 2153245560';</span>

Note that in Oracle 9.2 it is no longer necessary to add 1 to the address

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP HEAPDUMP_ADDR 2 2153245560</span>

Dumping Process State

Process State

To dump the process state use:

<span>    ALTER SESSION SET EVENTS </span>

<span>    'immediate trace name processstate level <em>level</em>';</span>

For example

<span>    ALTER SESSION SET EVENTS 'immediate trace name processstate level 10';</span>

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP PROCESSSTATE <em>level</em></span>

The amount of library cache dump output for state object dumps can be limited using event 10065

Dumping System State

System State

A system state dump contains the process state for every process.

Every state object for every process is dumped.

A state object represents the state of a database resource including

• processes
• sessions
• enqueues (locks)
• buffers

State objects are held in the SGA

A system state dump does not represent a snapshot of the instance because the database is not frozen for the duration of the dump. The start point of the dump will be earlier than the end point.

Oracle recommends the use of system state dumps to diagnose

• hanging databases
• slow databases
• database errors
• waiting processes
• blocking processes
• resource contention

To dump the system state use

<span>    ALTER SESSION SET EVENTS </span>

<span>    'immediate trace name systemstate level <em>level</em>';</span>

For example

<span>    ALTER SESSION SET EVENTS 'immediate trace name systemstate level 10';</span>

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP SYSTEMSTATE <em>level</em></span>

A system state dump can be triggered by an error, for example the following init.ora parameter

<span>    event = "60 trace name systemstate level 10"</span>

will generate a systemstate dump whenever a deadlock is detected (ORA-00060)

The amount of library cache dump output for state object dumps can be limited using event 10065

Dumping the Error Stack

Error Stack

An error stack describes the current state of a process. It includes the current SQL statement and the process state for the process.

Oracle recommends taking an error stack dump to diagnose

• what the process is doing
• a problem identified by a systemstate dump
• processes looping or hanging

Error stack dumps can also be triggered by an error

To dump an error stack use

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name errorstack level <em>level</em>';</span>

where level is one of the following

<span> </span>

Level	Description
0	Error stack only
1	Error stack and function call stack
2	As level 1 plus the process state
3	As level 2 plus the context area

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP ERRORSTACK <em>level</em></span>

An alternative syntax for the same command is

<span>    ORADEBUG EVENT IMMEDIATE TRACE NAME ERRORSTACK <em>level</em></span>

To dump a level 3 errorstack when ORA-00942 (table or view does not exist) use

<span>    ALTER SESSION SET EVENTS </span>

<span>    '942 trace name errorstack level 3';</span>

A conditional errorstack dump can also be specified without a level e.g.

<span>    ALTER SESSION SET EVENTS </span>

<span>    '604 trace name errorstack';</span>

Alternatively a conditional errorstack dump can be specified in the init.ora file

<span>    event = "942 trace name errorstack level 3"</span>

Errorstacks can also be dumped conditionally using ORADEBUG

<span>    ORADEBUG EVENT 942 TRACE NAME ERRORSTACK LEVEL 3</span>

A level 3 errorstack contains the following divs

<span> </span>

Call Stack Trace Files Currently Opened Process State Pinned Buffer History Cursor Dump Fixed PGA Fixed SGA Fixed UGA In memory trace dump

Dumping Hang Analysis

Hang Analysis

This dump is available in Oracle 8.0.6 and Oracle 8.1.6 and above. It is not available in Oracle 8.1.5.

It contains

• systemstate level 1 dump
• processes currently waiting, blocking or spinning
• errorstacks

The HANGANALYZE dump is more selective than a SYSTEMSTATE dump in that it only includes details of processes of interest. It is particularly intended for situations where a database is hanging.

For example

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name hanganalyze level 5';</span>

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP HANGANALYZE <em>level</em></span>

e.g.

<span>    ORADEBUG DUMP HANGANALYZE 5</span>

A HANGANALYZE dump can also be generated on an error e.g. when a deadlock is detected (ORA-00060) using the init.ora file

<span>    event = "60 trace name hanganalyze level 5"</span>

or in the current session using

<span>    ALTER SESSION SET EVENTS</span>

<span>    '60 trace name hanganalyze level 5';</span>

Dumping Work Areas

Work Areas

To dump the current workareas use

<span>    ALTER SESSION SET EVENTS 'immediate trace name workareatab_dump level <em>level</em>';</span>

Levels are (bitmask)

<span> </span>

Level	Description
1	Global SGA Info
2	Workarea Table (Summary)
3	Workarea Table (Detail)

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP WORKAREATAB_DUMP <em>level</em></span>

Dumping Enqueues

Enqueues

To dump the current enqueue states use

<span>    ALTER SESSION SET EVENTS 'immediate trace name enqueues level <em>level</em>';</span>

Levels are

<span> </span>

Level	Description
1	Dump a summary of active resources and locks, the resource free list and the hash table
2	Include a dump of resource structures
3	Include a dump of lock structures

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP ENQUEUES <em>level</em></span>

Dumping Latches

Latches

To dump the current latch status use

<span>    ALTER SESSION SET EVENTS 'immediate trace name latches level <em>level</em>';</span>

Levels are

<span> </span>

Level	Description
1	Latches
2	Include statistics

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP LATCHES <em>level</em></span>

Dumping Events

Events

To dump the events set for the current session

<span>    ALTER SESSION SET EVENTS 'immediate trace name events level <em>level</em>';</span>

Levels are

<span> </span>

Level	Description
1	Session
2	Process
4	System

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP EVENTS <em>level</em></span>

Dumping Shared Server (MTS) Processes

Shared Server (MTS) Processes

To dump buffer headers and buffer contents for buffers currently in the cache

<span>    ALTER SESSION SET EVENTS 'immediate trace name shared_server_state level <em>level</em>';</span>

where level is in the range 1 to 14

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP SHARED_SERVER_STATE <em>level</em></span>

In Oracle 8.1.7 and below, this dump is called MTSSTATE

Dumping Background Messages

Background process messages can be traced using

<span>    ALTER SESSION SET EVENTS </span>

<span>    'immediate trace name bg_messages level <em>level</em>';</span>

where level identifies the background process (+1)

For example to find the level of SMON, first identify the index number using the following query

<span>    SELECT indx + 1 FROM x$ksbdp</span>

<span>    WHERE ksbdpnam = 'SMON';</span>

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP BG_MESSAGES <em>level</em></span>

File Dumps

Dumping Columns

Columns

To dump the internal representation of columns use the DUMP built-in function

<span>    DUMP (<em>column_value</em>, <em>format</em>)</span>

where format is

<span> </span>

Format	Description
8	Octal
10	Decimal
16	Hexadecimal
17	Single Character

For example

<span>    SELECT DUMP (1001,16) FROM dual;</span>

returns

<span>    Typ=2 Len=3: c2,b,2</span>

To output a column in hexadecimal use the 'XXXXXXXX' format mask e.g.

<span>    SELECT TO_CHAR (65536,'XXXXXXXX') FROM dual;</span>

returns

<span>    10000</span>

Dumping Database Blocks

Database Blocks

The syntax used for dumping database blocks changed when Oracle8 was introduced

Oracle7 and below

Oracle8 and above

Oracle 7 and below

In Oracle7, blocks are identified by a file number and a block number. These must be converted into a data block address. The block can then be dumped using the ALTER SESSION command.

<span>    COLUMN decimalDBA new_value decimalDBA</span>

<span> </span>

<span>    SELECT</span>

<span>        dbms_utility.make_data_block_address (&file,&block) decimalDBA</span>

<span>    FROM dual;</span>

<span> </span>

<span>    ALTER SESSION SET EVENTS</span>

<span>    'immediate trace name blockdump level &decimalDBA';</span>

Oracle 8 and above

In Oracle8 and above, blocks are uniquely identified by an absolute file number and a block number. The syntax of the ALTER SYSTEM command has been extended to include block dumps

To dump a block

<span>    ALTER SYSTEM DUMP DATAFILE <em>absolute_file_number</em> </span>

<span>    BLOCK <em>block_number</em>;</span>

To dump a range of blocks

<span>    ALTER SYSTEM DUMP DATAFILE <em>absolute_file_number</em> </span>

<span>    BLOCK MIN <em>minimum_block_number</em> </span>

<span>    BLOCK MAX <em>maximum_block_number</em>;</span>

The DATAFILE clause can specify an absolute file number of a datafile name. If the DATAFILE clause specifies a datafile name, the blocks can also be dumped from a closed database e.g.

<span>    ALTER SYSTEM DUMP DATAFILE '<em>file_name</em>'</span>

<span>    BLOCK <em>block_number</em>;</span>

Normally a symbolic block dump is output. However, this may not be possible if the block has become corrupt. It is possible to output the block dump in hexadecimal.

To dump a block in hexadecimal, enable event 10289

<span>    ALTER SESSION SET EVENTS</span>

<span>    '10289 trace name context forever, level 1';</span>

dump the block(s) using one of the above commands and then disable 10289 again using

<span>    ALTER SESSION SET EVENTS</span>

<span>    '10289 trace name context off';</span>

On Unix systems blocks can also be dumped using the od utility.

<span>    dd bs=8k if=<em>filename</em> skip=200 count=4 | od -x</span>

where

• bs is the Oracle block size e.g. 8k
• if is the datafile name
• skip is the number of blocks to skip from the start of the file
• count is the number of blocks to dump

As blocks are written back to the datafiles asynchronously by DBWR, it is possible that changed blocks have not been written back to the disk when they are dumped using operating system utilities.

The probability that a block has been written back to disk can be increased by performing a checkpoint using

<span>    ALTER SYSTEM CHECKPOINT;</span>

or a logfile switch using

<span>    ALTER SYSTEM SWITCH LOGFILE;</span>

Dumping Indexes

Index Tree Dumps

An index tree can be dumped using

<span>    ALTER SESSION SET EVENTS 'immediate trace name treedump level <em>object_id</em>';</span>

where object_id is the object number of the index (in DBA_OBJECTS)

The tree dump includes

• branch block headers
• leaf block headers
• contents of leaf blocks

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP TREEDUMP <em>object_id</em></span>

In Oracle 9.2 a treedump may crash if the index has been created by a primary / unique constraint e.g.

<span>    CREATE TABLE t1 (c1 NUMBER PRIMARY KEY);</span>

or

<span>    CREATE TABLE t1 (c1 NUMBER);</span>

<span>    ALTER TABLE t1 </span>

<span>    ADD CONSTRAINT t1pk PRIMARY KEY (c1);</span>

The treedump crashes when ind$.property > 256

This problem can be prevented by creating the index before creating the constraint

Dumping Undo Segment Headers

Undo Headers

To dump an undo segment header use the command function

<span>    ALTER SYSTEM DUMP UNDO_HEADER '<em>segment_name</em>';</span>

In Oracle 9.0.1 and above, if system managed undo is enabled, the segment name has to be in double quotes and upper case. This is because there is a leading underscore in the segment name.

A list of undo segment IDs and names can be obtained using

<span>    SELECT segment_id, segment_name</span>

<span>    FROM dba_rollback_segs</span>

<span>    ORDER BY segment_id;</span>

Dumping Undo Blocks

Undo Blocks

To dump an undo block use the equivalent datafile block dump command

For example to dump an undo block in Oracle8 and above use

    ALTER SYSTEM DUMP DATABASE absolute_file_number     BLOCK block_number;

Dumping Undo for a Transaction

Undo for a Transaction

To dump all the undo written for a specific transaction, first identify the transaction ID using

<span>    SELECT xidusn, xidslot, xidsqn</span>

<span>    FROM v$transaction;</span>

Dump the undo using the transaction ID

<span>    ALTER SYSTEM DUMP UNDO BLOCK '<em>segment_name</em>'</span>

<span>    XID xidusn xidslot xidsqn;</span>

Dumping File Headers

To dump all the datafile headers use

    ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level level';

Levels (circa Oracle 8.1.5) are

<span> </span>

Level	Description
1	Dump datafile entry from control file
2	Level 1 + generic file header
3	Level 2 + datafile header
10	Same as level 3

In later versions, level 7 appears to generate additional trace

The following ORADEBUG command has the same effect

    ORADEBUG DUMP FILE_HDRS level

Dumping Control Files

Controlfiles

To dump the current latch status use

<span>    ALTER SESSION SET EVENTS 'immediate trace name controlf level <em>level</em>';</span>

Levels (circa Oracle 8.1.5) are

<span> </span>

Level	Description
1	Generic file header
2	Level 1 + database information + checkpoint progress records
3	Level 2 + reuse record div
10	Same as level 3

In later versions, level 15 appears to generate additional trace

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP CONTROLF <em>level</em></span>

Dumping Redo Log Headers

Redo Log Headers

To dump the redo log headers

<span>    ALTER SESSION SET EVENTS 'immediate trace name redohdr level 1';</span>

Levels (circa Oracle 8.1.5) are

<span> </span>

Level	Description
1	Dump redo log entry from control file
2	Level 1 + generic file header
3	Level 2 + log file header
10	Same as level 3

The following ORADEBUG command has the same effect

<span>    ORADEBUG DUMP REDOHDR <em>level</em></span>

Dumping Redo Logs

Redo Logs

To identify the current redo log use

<span>    SELECT member FROM v$logfile</span>

<span>    WHERE group# = </span>

<span>    (  SELECT group# FROM v$log</span>

<span>        WHERE status = 'CURRENT'</span>

<span>    );</span>

To dump a redo log file use

<span>    ALTER SYSTEM DUMP LOGFILE '<em>FileName</em>';</span>

e.g.

<span>    ALTER SYSTEM DUMP LOGFILE 'R:/Oracle/Oradata/JD92001/Redo01.log';</span>

The syntax of this statement is as follows

<span>    ALTER SYSTEM DUMP LOGFILE '<em>FileName</em>'</span>

<span>        SCN MIN <em>MinimumSCN</em></span>

<span>        SCN MAX <em>MaximumSCN</em></span>

<span>        TIME MIN <em>MinimumTime</em></span>

<span>        TIME MAX <em>MaximumTime</em></span>

<span>        LAYER <em>Layer</em></span>

<span>        OPCODE <em>Opcode</em></span>

<span>        DBA MIN <em>FileNumber</em> . <em>BlockNumber</em></span>

<span>        DBA MAX <em>FileNumber</em> . <em>BlockNumber</em></span>

<span>        RBA MIN <em>LogFileSequenceNumber</em> . <em>BlockNumber</em></span>

<span>        RBA MAX <em>LogFileSequenceNumber</em> . <em>BlockNumber</em>;</span>

The minimum and maximum SCN is a decimal number

The minimum and maximum time is a decimal number representing the number of seconds since midnight 01Jan1988. These are calculated using the following formula

<span>    time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * </span>

<span>           60 + mi) * 60 + ss;</span>

yyyy	Year
mm	month	01-12
dd	day	01-31
hh	hour	00-23
mi	minute	00-59
ss	second	00-59

 where

<span> </span>

<span> </span>

This is the same formula that is used to represent time within the redo log

The layer and opcode are those used to indicate specific operations within the redo log e.g. LAYER 5 OPCODE 4 is an undo segment header commit operation

Note that there must be spaces around the periods in the DBA and RBA.

See Metalink Note 1031381.6 for further examples

http://www.juliandyke.com/index.htm

Oracle Internals

参考：set_tsn_p1     百度