alter session set events語句dump REDO日誌檔案頭資訊示例

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

alter session set events 'immediate trace name redohdr level 1';語句簡介

1 控制檔案中的redo log資訊--不包含DBID資訊，只是顯示控制檔案中各REDO LOG FILE的相關資訊
2 level 1 + 檔案頭資訊  --包含有LVEEL的資訊，幷包含有REDO檔案頭的資訊-包含DBID相關資訊
3 level 2 + 日誌檔案頭資訊--比LEVLE2多出一小部分： Low scn、 Next scn等資訊-文中未演示命令-在最後貼出有這部分資訊。
10 level 3
更詳細的alter session set events--Oracle跟蹤事件簡介見http://blog.csdn.net/q947817003/article/details/16369041 第2小節。
##########################################################################

1.實驗命令如下：

SYS@ bys3>alter session set events 'immediate trace name redohdr level 2';    --轉儲控制檔案中的REDO LOGFILE資訊及REDO檔案頭資訊
Session altered.
Elapsed: 00:00:00.02
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_14953.trc
##########################################################################

2.檢視產生的TRACE檔案中的資訊--資訊並不太多，全部顯示如下：

[oracle@bys3 trace]$ head -n 100 bys3_ora_14953.trc
Trace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_14953.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: 1
Oracle process number: 17
Unix process pid: 14953, image: oracle@bys3.bys.com (TNS V1-V3)

*** 2013-11-17 16:22:42.502
*** SESSION ID:(1.29) 2013-11-17 16:22:42.502
*** CLIENT ID:() 2013-11-17 16:22:42.502
*** SERVICE NAME:() 2013-11-17 16:22:42.502
*** MODULE NAME:(sqlplus@bys3.bys.com (TNS V1-V3)) 2013-11-17 16:22:42.502
*** ACTION NAME:() 2013-11-17 16:22:42.502
 
DUMP OF LOG FILES: 3 logs in database    
LOG FILE #1:
  name #1: /u01/oradata/bys3/redo01.log               --DUMP的檔案資訊-這裡DUMP的是REDO日誌1
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000031 hws: 0x5 bsz: 512 nab: 0x10e3a flg: 0x8 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000adc6c
 Low scn: 0x0000.000b3984 11/17/2013 10:00:19
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=3358363031=0xc82c8d97, Db Name='BYS3'       --資料庫的DBID及DB_NAME，存在於LOGFILE的頭部。
               Activation ID=3358374039=0xc82cb897
        Control Seq=1596=0x63c, File size=102400=0x19000
        File Number=1, Blksiz=512, File Type=2 LOG     --檔案TYPE是2 ，TYPE=3表示普通的資料檔案(包括undo,沒有temp,temp的type=6) 
 Format ID is 2
 redo log key is 8956285942a954206654d49467f23e84
 redo log key flag is 5
LOG FILE #2:
  name #2: /u01/oradata/bys3/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x0000002f hws: 0x3 bsz: 512 nab: 0x16720 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a18a3
 Low scn: 0x0000.000a597d 11/16/2013 23:00:23
 Next scn: 0x0000.000adc6c 11/17/2013 06:00:38
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=3358363031=0xc82c8d97, Db Name='BYS3'
        Activation ID=3358374039=0xc82cb897
        Control Seq=1592=0x638, File size=102400=0x19000
        File Number=2, Blksiz=512, File Type=2 LOG
 Format ID is 2
 redo log key is 15e2e7bcf35d74d08dd14096f6b364e
 redo log key flag is 5
LOG FILE #3:
  name #3: /u01/oradata/bys3/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000030 hws: 0x3 bsz: 512 nab: 0x15729 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a597d
 Low scn: 0x0000.000adc6c 11/17/2013 06:00:38
 Next scn: 0x0000.000b3984 11/17/2013 10:00:19
 FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=3358363031=0xc82c8d97, Db Name='BYS3'
        Activation ID=3358374039=0xc82cb897
        Control Seq=1592=0x638, File size=102400=0x19000
        File Number=3, Blksiz=512, File Type=2 LOG
 Format ID is 2
 redo log key is 40458a715699679eb8a3e979eae4bbd6
 redo log key flag is 5
########################################

3.這一段是Oracle跟蹤事件的alter session set events 'immediate trace name redohdr level 3才會產生的。

為方便檢視我手動增加在這裡的。
 descrip:"Thread 0001, Seq# 0000000048, SCN 0x0000000adc6c-0x0000000b3984"
 thread: 1 nab: 0x15729 seq: 0x00000030 hws: 0x3 eot: 0 dis: 0
 reset logs count: 0x318f5cd7 scn: 0x0000.00000001
 Low scn: 0x0000.000adc6c 11/17/2013 06:00:38
 Next scn: 0x0000.000b3984 11/17/2013 10:00:19
 Enabled scn: 0x0000.00000001 11/14/2013 14:23:38
 Thread closed scn: 0x0000.000adc6c 11/17/2013 06:00:38
 Disk cksum: 0x9a35 Calc cksum: 0x9a35
 Terminal Recovery Stop scn: 0x0000.00000000
 Terminal Recovery Stamp  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 1530 blocks
 Miscellaneous flags: 0x800000
 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
 Zero blocks: 8
 Enabled redo threads: 1
##########################################
*** 2013-11-17 16:22:45.024
Processing Oradebug command 'setmypid'
*** 2013-11-17 16:22:45.024
Oradebug command 'setmypid' console output: <none>
*** 2013-11-17 16:22:47.587
Processing Oradebug command 'tracefile_name'
*** 2013-11-17 16:22:47.587
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_14953.trc

相關文章