alter session set events语句dump REDO日志文件头信息示例

最新推荐文章于 2021-04-04 20:49:04 发布

Yushan Bai

最新推荐文章于 2021-04-04 20:49:04 发布

阅读量2.3k

点赞数 1

分类专栏： ORACLE 数据结构 ORACLE实战

本文链接：https://blog.csdn.net/haibusuanyun/article/details/16370539

版权

ORACLE实战同时被 2 个专栏收录

88 篇文章 2 订阅

订阅专栏

ORACLE 数据结构

46 篇文章 1 订阅

订阅专栏

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