oracle之 如何 dump logfile

這篇文章解釋瞭如何在聯機或歸檔重做日誌檔案中獲取轉儲。

約束和限制:
1。資料庫必須安裝(或開啟)。
更改系統轉儲日誌檔案與任何例項無關，因此不需要為其操作安裝資料庫。
但是，在ALTER SYSTEM DUMP REDO的情況下，系統需要知道例項是什麼，以及其他日誌檔案在哪裡。
此查詢需要控制檔案，因此必須安裝或開啟資料庫。

2。DUMP REDO限制在控制檔案中識別的日誌檔案集。
因為我們在控制檔案中查詢日誌檔案和例項，如果在控制檔案中有未引用的重做日誌，那麼這些重做日誌將不會在轉儲檔案中被考慮。
這樣做的一個例子是，如果將日誌檔案刪除，手動或例項從RAC叢集中刪除。

3。所有的日誌檔案都必須從呼叫例項中訪問，儘管所有的聯機重做日誌都儲存在共享磁碟上，但是每個例項的歸檔日誌不需要。

 

下面介紹了轉儲重做日誌檔案的方法:

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 'u01/oracle/V7323/dbs/arch1_76.dbf' 
DBA MIN 5 31125 
DBA MAX 5 31150; 

這將導致對指定範圍的資料塊進行所有更改。
轉儲到跟蹤檔案。在給定的示例中，所有重做檔案#5的記錄，
第31125至31150條被dump

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 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 '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 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 '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 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/oracle/V7323/dbs/arch1_76.dbf';

補充：
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

參考：How to Dump Redo Log File Information (文件 ID 1031381.6)