ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How to Dump Redo Log File Information [ID 1031381.6]

How to Dump Redo Log File Information [ID 1031381.6]

原创 Linux操作系统 作者:spider0283 时间:2012-04-24 16:05:52 0 删除 编辑
***Checked for relevance on 18-Jan-2012***

PURPOSE
This article explain how to obtain a dump of the header information in the 
online redo log file(s), as well as obtaining selected information 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 the redo log files.   The 
information in the logs is often used to help diagnose corruption issues. 
    
The following commands will be used in this process: 
    
1.  The 'alter session' 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 is issued.  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 operating systems are the same. 
 
Output from the command is put into the session's trace file.

The following ways of dumping a redo log file are covered:

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; 
 
This will cause all the changes to the specified range of data blocks to be  
dumped to the trace file.  In the example given, all redo records for file #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 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';


The dump of the logfile will be written into a trace file in the udump destination. 
Use the command 'show parameters dump' within an sqlplus session. 
The ouput will show the location for the udump destination where 
the trace file exists.
        
Back to TopBack to Top

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

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    621769