How to Dump Redo Log File Information --metalink
How to Dump Redo Log File Information
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 sqldba or svrmgr, 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 sqldba or svrmgr, 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 sqldba or svrmgr, 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 sqldba or svrmgr, issue the following command:
alter session set events 'immediate trace name redohdr level 10';
7. Dump an entire log file:
------------------------
From sqldba or svrmgr, 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';
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-242413/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- redo log file 最佳化
- Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)ORMSQL
- Oracle RAC+DG 調整redo/standby log fileOracle
- 更改online redo log file 提示 ORA-01511 ORA-01141
- undo log和redo log
- mysql之 redo logMySql
- MySQL的Redo log 以及Bin logMySql
- How to Convert Class File to Java File Online?Java
- MySQL中的redo log和undo logMySql
- MySQL Undo Log和Redo Log介紹MySql
- 高通進dump和抓取解析dump log
- 【Mysql】三大日誌 redo log、bin log、undo logMySql
- How to Convert a Class File to a Ja
- Oracle-真實環境的丟失current redo log file的故障恢復Oracle
- log file switch
- MySQL redo log最佳化MySql
- MySQL重做日誌(redo log)MySql
- Oracle redo解析之-2、BBED & DUMP工具使用Oracle Redo
- How to use Qt Designed Ui fileQTUI
- Oracle redo解析之-1、oracle redo log結構計算Oracle Redo
- MySQL中的redo log和checkpointMySql
- InnoDB文件筆記(二)—— Redo Log筆記
- 硬核乾貨!一文掌握 binlog 、redo log、undo log
- MySQL中redo log、undo log、binlog關係以及區別MySql
- mysql日誌:redo log、binlog、undo log 區別與作用MySql
- 深入理解MySQL系列之redo log、undo log和binlogMySql
- MySQL 日誌系統 redo log、binlogMySql
- 【等待事件】log file sync事件
- log file sync等待事件事件
- [20220128]Check the datapump file header information in Oracle.txtHeaderORMOracle
- [20190522]How to get dump or list parameters set at session level.txtSession
- MySQL學習之change buffer 和 redo logMySql
- MySQL如何計算統計redo log大小MySql
- 基於Redo Log和Undo Log的MySQL崩潰恢復流程MySql
- How to get the exact duration of an audio file in js All In OneJS
- MySQL Redo log頁內邏輯怎麼理解MySql
- redo log 和 binlog 的一些總結
- 必須瞭解的mysql三大日誌-binlog、redo log和undo logMySql