Oracle redo log dump
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 ORACLE REDO LOG DUMP[편집]
1.1 로그 파일 덤프 순서[편집]
- 리두 헤더 덤프하기 위해서는 'alter session' 명령
- 리두 내용 덤프는 'alter system dump logfile'
- online log 와 archived log file 둘다 가능
- OS 운영체제가 같다면 다른 데이터베이스 로그파일도 덤프 가능느
- 'ALTER SYSTEM' 권한필요.
- database 상태가 mount, nomount,open 일때 가능
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:
2 ==[편집]
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
3 ==[편집]
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:
3.1 ==[편집]
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:
3.2 ==[편집]
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:
3.3 ==[편집]
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.
From 11g onwards, we can use ALTER SYSTEM DUMP REDO to dump a merged collection of redo records from multiple logfiles into a single trace file ordered by SCN. The database needs to be mounted. This differs from dump logfile in that a file(redolog or archivelog) is specified. The redo record chain can span multiple log files and instances (threads). SYNTAX
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
If no scn or time is specified then the default is to 12 hours back from current time. The scn should be specified as just the base SCN value, in hex or decimal.
CONSTRAINTS/LIMITATIONS:
1. Database must be mounted ( or opened) . ALTER SYSTEM DUMP LOGFILE is not associated with any instance and therefore a database need not be mounted for its operation. However, in the case of ALTER SYSTEM DUMP REDO, the system needs to know what the instances are and where the other logfiles are. The control file is needed for this lookup and therefore the database must be mounted or opened.
2. DUMP REDO constrained to set of logfiles recognized in Control File Because we look up the logfiles and instances in the control file, if there are redo logs that exist that are not referenced in the control file, those redo logs will not be considered in the dump. An example of this would be if logfiles were removed, either manually or an instance was removed from the RAC cluster
3. All logfiles must be accessible from the calling instance Though all online redo logs from any instance are stored on the shared disk, the archived logs for each instance need not be. In order for ALTER SYSTEM DUMP REDO to read and merge files, the set of files in question must be accessible by the calling instance. (ie. the paths of the logfiles specified in the control file must be recognizable and valid).