"Oracle redo log dump"의 두 판 사이의 차이

DB CAFE

2024년 6월 19일 (수) 10:33 기준 최신판

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

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.

1.3 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).

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=Oracle_redo_log_dump&oldid=11923"

@@ 1번째 줄: / 1번째 줄: @@
-=== ORACLE REDO LOG DUMP ===
+=== 리두 로그 덤프  REDO LOG DUMP ===
 ==== 로그 파일 덤프 순서 ====
 # 리두 헤더 덤프하기 위해서는 'alter session' 명령
@@ 7번째 줄: / 7번째 줄: @@
 * '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:
-. To dump records based in DBA (Data Block Address)
+==== 리두 로그 항목별 덤프  ====
-. To dump records based on RBA (Redo Block Address)
+#  DBA (Data Block Address) 덤프
-. To dump records based on SCN
+# 언두블럭 덤프, RBA (Redo Block Address)
-. To dump records based on time
+# SCN 덤프
-. To dump records based on layer and opcode
+# 시간대 로그 덤프
-. Dump the file header information
+# 레이어(layer) 와 opcode 덤프
-. Dump an entire log file:
+# 파일 헤더 정보 덤프
+# 로그파일 전체 덤프
-. To dump records based on DBA (Data Block Address)
 --------------------------------------------------
+===== DBA (Data Block Address) =====
-This will dump all redo records for the range of data
+* This will dump all redo records for the range of data blocks specified for a given file # and block # range.
-blocks specified for a given file # and block # range.
+* sqlplus (sqldba or svrmgr) 명령어
+<source lang=sql>
-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;
+</source>
+: Example: redo records 파일 번호 #5, 블럭 31125 에서 블럭 31150 까지 덤프 .
-Example:
+<source lang=sql>
-========
 ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
-DBA MIN 5 . 31125
+              DBA MIN 5 . 31125 -- 10 이상은 DBA MIN 5 31125
-DBA MAX 5 . 31150;
+              DBA MAX 5 . 31150; -- 10 이상은 5 31150
+</source>
+* ORA-01963: Must specify a block number 에러 발생시  점(.) 삭제 ==> DBA MIN 5 . 31125 =>  DBA MIN 5 31125
-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:
+===== To dump records based on RBA (Redo Block Address)=====
-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;
-. To dump records based on RBA (Redo Block Address)
--------------------------------------------------
 This will dump all redo records for the range of redo
@@ 63번째 줄: / 46번째 줄: @@
 Syntax:
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'filename'
 RBA MIN seqno . blockno
 RBA MAX seqno . blockno;
+</source>
 Example:
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
 RBA MIN 2050 . 13255
 RBA MAX 2255 . 15555;
+</source>
-. To dump records based on SCN
+===== 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.
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'filename'
 SCN MIN minscn
 SCN MAX maxscn;
+</source>
 Example:
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
 SCN MIN 103243
 SCN MAX 103294;
+</source>
 If the purpose is to check the dumpfile you can rather do the following,
+<source lang=sql>
 SQL> ALTER SYSTEM DUMP LOGFILE 'filename' SCN MIN 1 SCN MAX 1;
+</source>
 If the above completes sucessfully it ensures no issues with the archivelog.
-. To dump records based on time.
+===== To dump records based on time.=====
 ------------------------------
@@ 99번째 줄: / 86번째 줄: @@
 From sqlplus (sqldba or svrmgr for older versions), issue the following command:
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'filename'
 TIME MIN value
 TIME MAX value;
+</source>
 Example:
-========
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
 TIME MIN 299425687
 TIME MAX 299458800;
+</source>
 Please Note: the time value is given in REDO DUMP TIME
-. To dump records based on layer and opcode.
+===== To dump records based on layer and opcode.=====
 ------------------------------------------
@@ 119번째 줄: / 106번째 줄: @@
 From sqlplus (sqldba or svrmgr for older versions), issue the following command:
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'filename'
 LAYER value
 OPCODE value;
+</source>
 Example:
-========
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf'
 LAYER 11
 OPCODE 3;
+</source>
-. Dump the file header information:
+===== 로그 파일 헤더 정보만 덤프  =====
 ---------------------------------
@@ 141번째 줄: / 128번째 줄: @@
 For dumping archivelog header,issue the following command:
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'filename' RBA MIN 1 1 RBA MAX 1 1;
+</source>
-. Dump an entire log file:
+===== 로그 파일 전체 덤프 =====
-------------------------
+<source lang=sql>
-From sqlplus (sqldba or svrmgr for older versions), issue the following command:
 ALTER SYSTEM DUMP LOGFILE 'filename';
+</source>
-Please note:
+* 전체 경로 명시 , ' 포함
-Fully qualify the filename, and include the single quotes.
 Example:
-========
+<source lang=sql>
 ALTER SYSTEM DUMP LOGFILE 'u01/oracle/V7323/dbs/arch1_76.dbf';
+</source>
 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.
@@ 166번째 줄: / 149번째 줄: @@
 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
+<source lang=sql>
 ALTER SYSTEM DUMP REDO [option] .... [option];
@@ 177번째 줄: / 160번째 줄: @@
 xid [undoseg#] [slot#] [wrap#] |
 validate
+</source>
 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:
+==== CONSTRAINTS/LIMITATIONS: ====
 . Database must be mounted ( or opened) .
@@ 193번째 줄: / 176번째 줄: @@
 . 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).
+[[category:oracle]]

"Oracle redo log dump"의 두 판 사이의 차이

DB CAFE

2024년 6월 19일 (수) 10:33 기준 최신판

목차

1 리두 로그 덤프 REDO LOG DUMP[편집]

1.1 로그 파일 덤프 순서[편집]

1.2 리두 로그 항목별 덤프[편집]

1.2.1 DBA (Data Block Address)[편집]

1.2.2 To dump records based on RBA (Redo Block Address)[편집]

1.2.3 To dump records based on SCN[편집]

1.2.4 To dump records based on time.[편집]

1.2.5 To dump records based on layer and opcode.[편집]

1.2.6 로그 파일 헤더 정보만 덤프[편집]

1.2.7 로그 파일 전체 덤프[편집]

1.3 CONSTRAINTS/LIMITATIONS:[편집]