File Dumps（ORACLE各类文件转储）

Dumping Columns

Columns

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

DUMP (column_value,format)

whereformatis

Format Description 8 Octal 10 Decimal 16 Hexadecimal 17 Single Character

For example

SELECT DUMP (1001,16) FROM dual;

returns

Typ=2 Len=3: c2,b,2

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

SELECT TO_CHAR (65536,'XXXXXXXX') FROM dual;

returns

10000

 

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.

COLUMN decimalDBA new_value decimalDBA SELECT dbms_utility.make_data_block_address (&file,&block) decimalDBA FROM dual; ALTER SESSION SET EVENTS 'immediate trace name blockdump level &decimalDBA';

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

ALTER SYSTEM DUMP DATAFILEabsolute_file_numberBLOCKblock_number;

To dump a range of blocks

ALTER SYSTEM DUMP DATAFILEabsolute_file_numberBLOCK MINminimum_block_numberBLOCK MAXmaximum_block_number;

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.

ALTER SYSTEM DUMP DATAFILE 'file_name' BLOCKblock_number;

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

ALTER SESSION SET EVENTS '10289 trace name context forever, level 1';

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

ALTER SESSION SET EVENTS '10289 trace name context off';

On Unix systems blocks can also be dumped using theodutility.

dd bs=8k if=filenameskip=200 count=4 | od -x

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

ALTER SYSTEM CHECKPOINT;

or a logfile switch using

ALTER SYSTEM SWITCH LOGFILE;

Dumping Indexes

Index Tree Dumps

An index tree can be dumped using

ALTER SESSION SET EVENTS 'immediate trace name treedump levelobject_id';

whereobject_idis 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

ORADEBUG DUMP TREEDUMPobject_id

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

CREATE TABLE t1 (c1 NUMBER PRIMARY KEY);

or

CREATE TABLE t1 (c1 NUMBER); ALTER TABLE t1 ADD CONSTRAINT t1pk PRIMARY KEY (c1);

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

ALTER SYSTEM DUMP UNDO_HEADER 'segment_name';

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

SELECT segment_id, segment_name FROM dba_rollback_segs ORDER BY segment_id;

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 DATABASEabsolute_file_numberBLOCKblock_number;

SeeDumping Database Blocksfor more information

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

SELECT xidusn, xidslot, xidsqn FROM v$transaction;

Dump the undo using the transaction ID

ALTER SYSTEM DUMP UNDO BLOCK 'segment_name' XIDxidusnxidslotxidsqn;

Dumping File Headers

File Headers

To dump all the datafile headers use

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

Levels (circa Oracle 8.1.5) are

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_HDRSlevel

Dumping Control Files

Controlfiles

To dump the current latch status use

ALTER SESSION SET EVENTS 'immediate trace name controlf levellevel';

Levels (circa Oracle 8.1.5) are

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

In later versions, level 15 appears to generate additional trace

The following ORADEBUG command has the same effect

ORADEBUG DUMP CONTROLFlevel

Dumping Redo Log Headers

Redo Log Headers

To dump the redo log headers

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

Levels (circa Oracle 8.1.5) are

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

ORADEBUG DUMP REDOHDRlevel

Dumping Redo Logs

Redo Logs

To identify the current redo log use

SELECT member FROM v$logfile WHERE group# = ( SELECT group# FROM v$log WHERE status = 'CURRENT' );

To dump a redo log file use

ALTER SYSTEM DUMP LOGFILE 'FileName';

e.g.

ALTER SYSTEM DUMP LOGFILE 'R:\Oracle\Oradata\JD92001\Redo01.log';

The syntax of this statement is as follows

ALTER SYSTEM DUMP LOGFILE 'FileName' SCN MINMinimumSCNSCN MAXMaximumSCNTIME MINMinimumTimeTIME MAXMaximumTimeLAYERLayerOPCODEOpcodeDBA MINFileNumber.BlockNumberDBA MAXFileNumber.BlockNumberRBA MINLogFileSequenceNumber.BlockNumberRBA MAXLogFileSequenceNumber.BlockNumber;

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

time = (((((yyyy - 1988)) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;

where

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

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

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

转载于:http://blog.itpub.net/7351078/viewspace-677306/