通過 alter system dump logfile語句dump REDO及歸檔日誌資訊示例

還不算暈發表於2013-11-17

說明：alter system dump logfile 'filename';

這個語句在NOMOUNT/MOUNT/OPEN狀態下，均可以DUMP REDO日誌或歸檔日誌，從而可以從檔案頭資訊中找到DBID，在資料恢復時很有用。
因為我們可以僅使用任意一個引數檔案，就可以將一個例項開啟到NOMOUNT，然後將待恢復卻不知道DBID的資料庫的REDO或歸檔檔案進行DUMP（也可傳輸到遠端庫），找出DBID。

實驗語句如下：

SYS@ bys3>select status from v$instance;   --NOMOUNT狀態下即可，MOUNT或OPEN時當然也可以了。
STATUS
------------
STARTED
SYS@ bys3> alter system dump logfile '/u01/oradata/bys3/redo01.log';
System altered.
Elapsed: 00:01:46.62    --這個語句執行時間較長，生成的TRC檔案也很大。
SYS@ bys3>oradebug setmypid;      --此語句要用SYSDBA許可權執行，普通DBA使用者會報錯：ORA-01031: insufficient privileges
Statement processed.
SYS@ bys3>oradebug tracefile_name   --找出產生的TRACE檔名
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_5044.trc

DUMP歸檔日誌的語句就不貼了，和DUMP REOD的一樣，只是檔案路徑檔名改一下就行。在最下面貼出有一部分DUMP歸檔日誌的資訊。

################################################################

2.檢視DUMP REDO檔案產生的TRACE檔案：--檔案太大，擷取開頭的一部分

[oracle@bys3 trace]$ head -n 200 bys3_ora_5044.trc   --檢視TRACE檔案開頭的兩百行。
Trace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_5044.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux                                 --所在主機的OS、資料庫相關資訊。
Node name:      bys3.bys.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine:        i686
Instance name: bys3
Redo thread mounted by this instance: 0 <none>
Oracle process number: 17
Unix process pid: 5044, image: oracle@bys3.bys.com (TNS V1-V3)

*** 2013-11-17 15:18:38.083                           ---產生此TRACE的會話相關資訊
*** SESSION ID:(1.3) 2013-11-17 15:18:38.083
*** CLIENT ID:() 2013-11-17 15:18:38.083
*** SERVICE NAME:() 2013-11-17 15:18:38.083
*** MODULE NAME:(sqlplus@bys3.bys.com (TNS V1-V3)) 2013-11-17 15:18:38.083
*** ACTION NAME:() 2013-11-17 15:18:38.083
 
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
 
DUMP OF REDO FROM FILE '/u01/oradata/bys3/redo01.log'    --DUMP的檔案資訊-這裡DUMP的是REDO日誌
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=3358363031=0xc82c8d97, Db Name='BYS3'   ---資料庫的DBID和DB_NAME資訊
        Activation ID=3358374039=0xc82cb897
        Control Seq=1596=0x63c, File size=102400=0x19000
        File Number=1, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000049, SCN 0x0000000b3984-0xffffffffffff"
 thread: 1 nab: 0x10e3a seq: 0x00000031 hws: 0x5 eot: 1 dis: 0
 resetlogs count: 0x318f5cd7 scn: 0x0000.00000001 (1)
 prev resetlogs count: 0x0 scn: 0x0000.00000000
 Low  scn: 0x0000.000b3984 (735620) 11/17/2013 10:00:19
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
 Enabled scn: 0x0000.00000001 (1) 11/14/2013 14:23:38
 Thread closed scn: 0x0000.000b8756 (755542) 11/17/2013 15:10:53
 Disk cksum: 0x39cb Calc cksum: 0x39cb
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 1530 blocks
 End-of-redo stream : No
 Unprotected mode
 Miscellaneous flags: 0x800000
 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
 Zero blocks: 8
 Format ID is 2
 redo log key is 8956285942a954206654d49467f23e84
 redo log key flag is 5
 Enabled redo threads: 1
 
REDO RECORD - Thread:1 RBA: 0x000031.00000002.0010 LEN: 0x0500 VLD: 0x06
SCN: 0x0000.000b3984 SUBSCN:  1 11/17/2013 10:00:19
(LWN RBA: 0x000031.00000002.0010 LEN: 0005 NST: 0001 SCN: 0x0000.000b3984)
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:23.1 ENC:0
 Block Written - afn: 3 rdba: 0x00c00a90 BFT:(1024,12585616) non-BFT:(3,2704)

                   scn: 0x0000.000b38bd seq: 0x02 flg:0x04

################################################################

3.DUMP 歸檔日誌的TRACE檔案

[oracle@bys3 trace]$ head -n 150 bys3_ora_11074.trc
Trace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_11074.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      bys3.bys.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine:        i686
Instance name: bys3
Redo thread mounted by this instance: 0 <none>
Oracle process number: 17
Unix process pid: 11074, image: oracle@bys3.bys.com (TNS V1-V3)


*** 2013-11-17 15:43:43.376
*** SESSION ID:(1.5) 2013-11-17 15:43:43.376
*** CLIENT ID:() 2013-11-17 15:43:43.376
*** SERVICE NAME:() 2013-11-17 15:43:43.376
*** MODULE NAME:(sqlplus@bys3.bys.com (TNS V1-V3)) 2013-11-17 15:43:43.376
*** ACTION NAME:() 2013-11-17 15:43:43.376
 
Initial buffer sizes: read 1024K, overflow 832K, change 805K
Log read is SYNCHRONOUS though disk_asynch_io is enabled!
 
DUMP OF REDO FROM FILE '/home/oracle/arc_1_12_822301084.arc'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=3957527513=0xebe313d9, Db Name='BYS1'
        Activation ID=3957570009=0xebe3b9d9
        Control Seq=936=0x3a8, File size=102400=0x19000
        File Number=3, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000012, SCN 0x0000000e38ff-0x0000000e3a35"
 thread: 1 nab: 0x82 seq: 0x0000000c hws: 0x3 eot: 0 dis: 0
 resetlogs count: 0x3103519c scn: 0x0000.000b8338 (754488)
 prev resetlogs count: 0x296a3120 scn: 0x0000.00000001 (1)
 Low  scn: 0x0000.000e38ff (932095) 08/08/2013 16:33:05
 Next scn: 0x0000.000e3a35 (932405) 08/08/2013 16:34:02
 Enabled scn: 0x0000.000b8338 (754488) 08/01/2013 08:58:04
 Thread closed scn: 0x0000.000e38ff (932095) 08/08/2013 16:33:05
 Disk cksum: 0xfb18 Calc cksum: 0xfb18
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 15 blocks
 End-of-redo stream : No
 Unprotected mode
 Miscellaneous flags: 0x800011
 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
 Zero blocks: 8
 Format ID is 0
 redo log key is 986daba9692cd9af21da3cb2493c8a95
 redo log key flag is 5
 Enabled redo threads: 1

相關文章