Technology Exchange
免费咨询热线
400-090-9964
1.直接使用dump数据文件头,无法查询数据文件头信息
直接通过alter system dump datafile n block min 1 max 10;是得不到任何有用信息的,如下:
SQL> alter system dump datafile 1 block min 1 block max 10;
部分的DUMP文件内容
Start dump data blocks tsn: 0 file#:1 minblk 1 maxblk 10
Block 1 (file header) not dumped:use dump file header command --从这句可以看到数据文件头未DUMP出来。
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0 rdba=4194306
2.使用oracle event来DUMP数据文件头信息
Oracle跟踪事件简介:
可以将Oracle各类内部结构中所包含的信息转储(dump)到跟踪文件中,以便用户能根据文件内容来解决各种故障。
immediate关键字表示命令发出后,立即将指定的结构dump到跟踪文件中,这个关键字只用在alter session语句中,并且不能与eventnumber、forever关键字同用。
trace name 是关键字。
eventname指事件名称(见后面),即要进行dump的实际结构名。若eventname为context,则指根据内部事件号进行跟踪。
forever关键字表示事件在实例或会话的周期内保持有效状态,不能与immediate同用。
level为事件级别关键字。但在dump错误栈(errorstack)时不存在级别。
levelnumber表示事件级别号,一般从1到10,1表示只dump结构头部信息,10表示dump结构的所有信息。
如在下面实验中:
file_hdrs事件:dump所有数据文件的头部信息
evel 1'; --表示dump 控制文件中所有数据文件头部的。
level 2'; --表示dump 所有数据文件的通用文件头-generic header。
level 3; ---包含level2以及header information in the datafile
level 10'--表示dump 所有数据文件的完整文件头。
实验命令如下:--要求数据库在MOUNT或OPEN状态才可以执行以下命令。
SYS@ bys3>alter system set events 'immediate trace name file_hdrs level 3'; --将所有的数据文件头都转储
System altered.
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_14351.trc
##################################################################################################
3.bys3_ora_14351.trc文件内容:附简单分析SYSTEM文件头信息
Trace file /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_14351.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: 1
Oracle process number: 23
Unix process pid: 14351, image: oracle@bys3.bys.com (TNS V1-V3)
#########################################################
*** 2013-11-17 10:43:18.205 ---产生此TRACE的会话相关信息
*** SESSION ID:(64.6265) 2013-11-17 10:43:18.205
*** CLIENT ID:() 2013-11-17 10:43:18.205
*** SERVICE NAME:(SYS$USERS) 2013-11-17 10:43:18.205
*** MODULE NAME:(sqlplus@bys3.bys.com (TNS V1-V3)) 2013-11-17 10:43:18.205
*** ACTION NAME:() 2013-11-17 10:43:18.205
###################################################
DUMP OF DATA FILES: 4 files in database
DATA FILE #1:
name #4: /u01/oradata/bys3/system01.dbf --此条目转储的是system01.dbf文件的数据头
creation size=64000 block size=8192 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0 --prev_file表示相同表空间的前一个数据文件号,这里没有所以是0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:67 scn: 0x0000.000b3984 11/17/2013 10:00:19 --最近的检查点发生的时间
Stop scn: 0xffff.ffffffff 11/16/2013 12:07:14 --数据库仍在运行,所以STOP SCN是0xffff.ffffffff。
Creation Checkpointed at scn: 0x0000.00000015 11/14/2013 14:24:22
thread:1 rba:(0x1.3.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 --有很多行0,节约篇幅,删掉了。
00000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.00000000 prev_range: 0
Online Checkpointed at scn: 0x0000.00000000 --表示此数据文件online还是offline的,用于快速跳过archive log恢复
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0
V10 STYLE FILE HEADER: --这一片是generic file header,level必须>=2才有此信息,,,,,,,此句之前来自控制文件,之后来自数据文件头。
Compatibility Vsn = 186646528=0xb200000 --control,redo,datafile,tempfile都有相同的generic file header.通过以后的信息对比
Db ID=3358363031=0xc82c8d97, Db Name='BYS3' --数据库的DBID及DB_NAME,存在于DATAFILE的第一个BLOCK。 将16进制转换为10进制:select to_number('c82c8d97','xxxxxxxxxxxxx') from dual;
Activation ID=0=0x0
Control Seq=1525=0x5f5, File size=64000=0xfa00 ---control seq表示控制文件序列号,更新控制文件的时候也会更新seq,如果控制文件中的control seq小于数据文件中的control sel,表示控制文件来自于备份。File size 不是表示整个文件的大小,而是表示当前保存在cache layer中的大小
File Number=1, Blksiz=8192, File Type=3 DATA -----TYPE=3表示普通的数据文件(包括undo,没有temp,temp的type=6)
-------file number是记录在file$表中的,如果数据文件被删除,那么file number可以重用
Tablespace #0 - SYSTEM rel_fn:1 ----这一片信息是从datafile header block 2中得到的,转储level必须为3才能得到此信息。---tablespace#0表示表空间序号可以查询v$tablespace.ts#验证,rel_fn:1表示relative file
number可以通过视图dba_data_files.relative_fno查到
SYS@ bys3>select name,ts# from v$tablespace; --只截取了部分显示
NAME TS#
------------------------------ ----------
SYSTEM 0
SYS@ bys3>select file_name,relative_fno from dba_data_files; --只截取了部分显示
FILE_NAME RELATIVE_FNO
---------------------------------------- ------------
/u01/oradata/bys3/system01.dbf 1
Creation at scn: 0x0000.00000015 11/14/2013 14:24:22 -------建库的时间。我这里是手动建库,所以时间就是真实的建库时间。如DBCA通过模板建库,可能显示为模板的时间。
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 --RMAN备份不更新此记录,热备份BEGIN BACKUP方式会更新此信息。
reset logs count:0x318f5cd7 scn: 0x0000.00000001
prev reset logs count:0x0 scn: 0x0000.00000000
recovered at 11/15/2013 10:50:16
status:0x2004 root dba:0x00400208 chkpt cnt: 67 ctl cnt:66 ---只有system才有root dba,用来定位bootstrap$,ctl cnt 是控制文件的一份拷贝,用于鉴别控制文件是否来自于备份.关于root dba:在SYSTEM文件头,Oracle存储了一个root dba:Root dba: This field only occurs in data file #1, and is the location of blocks required during bootstrapping the data dictionary (bootstrap$)。Oracle10g之前, root dba:0x004001a1,指向file 1 block 417对象,DUMP 417对象可以发现最终指向的是file 1 block 377。。10G中是root dba:0x00400179,指向file 1 block 377。现在11G中是root dba:0x00400208,具体指向位置:FILE 1 BLOCK 520
begin-hot-backup file size: 0 --上面的backup scn更新,此处也更新
Checkpointed at scn: 0x0000.000b3984 11/17/2013 10:00:19
thread:1 rba:(0x31.2.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
Backup Checkpointed at scn: 0x0000.00000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp 01/01/1988 00:00:00
Platform Information: Creation Platform ID: 10
Current Platform ID: 10 Last Platform ID: 10
4.除SYSTEM外的其它FILE信息--截取部分重要的
DATA FILE #2:
name #5: /u01/oradata/bys3/sysaux01.dbf
creation size=41600 block size=8192 status=0xe head=5 tail=5 dup=1
tablespace 1, index=2 krfil=2 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:67 scn: 0x0000.000b3984 11/17/2013 10:00:19
Stop scn: 0xffff.ffffffff 11/16/2013 12:07:14
Creation Checkpointed at scn: 0x0000.00000740 11/14/2013 14:24:54
thread:1 rba:(0x1.1afb.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.00000000 prev_range: 0
Online Checkpointed at scn: 0x0000.00000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3358363031=0xc82c8d97, Db Name='BYS3'
Activation ID=0=0x0
Control Seq=1525=0x5f5, File size=41600=0xa280
File Number=2, Blksiz=8192, File Type=3 DATA -----TYPE=3表示普通的数据文件(包括undo,没有temp,temp的type=6)
Tablespace #1 - SYSAUX rel_fn:2
Creation at scn: 0x0000.00000740 11/14/2013 14:24:54
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x318f5cd7 scn: 0x0000.00000001
prev reset logs count:0x0 scn: 0x0000.00000000
recovered at 11/15/2013 10:50:16
status:0x4 root dba:0x00000000 chkpt cnt: 67 ctl cnt:66
begin-hot-backup file size: 0
DATA FILE #3:
name #6: /u01/oradata/bys3/undotbs01.dbf
creation size=25600 block size=8192 status=0xe head=6 tail=6 dup=1
tablespace 2, index=3 krfil=3 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:67 scn: 0x0000.000b3984 11/17/2013 10:00:19
Stop scn: 0xffff.ffffffff 11/16/2013 12:07:14
Creation Checkpointed at scn: 0x0000.00000b51 11/14/2013 14:25:10
thread:1 rba:(0x1.25a6.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.00000000 prev_range: 0
Online Checkpointed at scn: 0x0000.00000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3358363031=0xc82c8d97, Db Name='BYS3'
Activation ID=0=0x0
Control Seq=1525=0x5f5, File size=25600=0x6400
File Number=3, Blksiz=8192, File Type=3 DATA
Tablespace #2 - UNDOTBS1 rel_fn:3
Creation at scn: 0x0000.00000b51 11/14/2013 14:25:10
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x318f5cd7 scn: 0x0000.00000001
prev reset logs count:0x0 scn: 0x0000.00000000
recovered at 11/15/2013 10:50:16
status:0x4 root dba:0x00000000 chkpt cnt: 67 ctl cnt:66
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.000b3984 11/17/2013 10:00:19
DATA FILE #4:
name #8: /u01/oradata/bys3/user01.dbf
creation size=6400 block size=8192 status=0xe head=8 tail=8 dup=1
tablespace 4, index=5 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:67 scn: 0x0000.000b3984 11/17/2013 10:00:19
Stop scn: 0xffff.ffffffff 11/16/2013 12:07:14
Creation Checkpointed at scn: 0x0000.000034f9 11/14/2013 14:26:26
thread:1 rba:(0x1.ce8a.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
Offline scn: 0x0000.00000000 prev_range: 0
Online Checkpointed at scn: 0x0000.00000000
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
Hot Backup end marker scn: 0x0000.00000000
aux_file is NOT DEFINED
Plugged readony: NO
Plugin scnscn: 0x0000.00000000
Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00
Online move state: 0
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3358363031=0xc82c8d97, Db Name='BYS3'
Activation ID=0=0x0
Control Seq=1525=0x5f5, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS rel_fn:4
Creation at scn: 0x0000.000034f9 11/14/2013 14:26:26
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x318f5cd7 scn: 0x0000.00000001
prev reset logs count:0x0 scn: 0x0000.00000000
recovered at 11/15/2013 10:50:16
status:0x4 root dba:0x00000000 chkpt cnt: 67 ctl cnt:66
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.000b3984 11/17/2013 10:00:19
TEMP FILE #1: External File #201
name #7: /u01/oradata/bys3/temp01.dbf
creation size=2560 block size=8192 status=0xe head=7 tail=7 dup=1
tablespace 3, index=4 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000cb8 11/14/2013 14:25:12
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3358363031=0xc82c8d97, Db Name='BYS3'
Activation ID=0=0x0
Control Seq=37=0x25, File size=2560=0xa00
File Number=1, Blksiz=8192, File Type=6 TEMP FILE -----TYPE=3表示普通的数据文件(包括undo,没有temp,temp的type=6)
Tablespace #3 - TEMP rel_fn:1
Creation at scn: 0x0000.00000cb8 11/14/2013 14:25:12
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x0 scn: 0x0000.00000000
prev reset logs count:0x0 scn: 0x0000.00000000
recovered at 01/01/1988 00:00:00
status:0x0 root dba:0x00000000 chkpt cnt: 0 ctl cnt:0
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.00000000
*** 2013-11-17 10:43:28.961
Processing Oradebug command 'setmypid'
*** 2013-11-17 10:43:28.975
Oradebug command 'setmypid' console output:
*** 2013-11-17 10:43:36.900
Processing Oradebug command 'tracefile_name'
*** 2013-11-17 10:43:36.901
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/bys3/bys3/trace/bys3_ora_14351.trc
(以上内容摘于网络,如有侵权,请告之,将第一时间删除)