教学文章
Technology Exchange
热门课程
400电话

免费咨询热线
400-090-9964

教学文章

Oracle dump数据文件头信息

时间:2017-08-10 来源:

  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

  (以上内容摘于网络,如有侵权,请告之,将第一时间删除)

版权所有@北京神脑资讯技术有限公司(CUUG,中国UNIX用户协会) Copyright ALL Rights Reserved 京ICP备11008061号-1

CUUG旗下网站:www.cuug.com.cn www.cuug.com oracle.cuug.com bbs.cuug.com www.cuug.net

电话:010-59426307 010-59426319 邮政编码:100089

地址:北京市海淀区北清路164号28-38号院