//
you're reading...
Oracle Internal

To dump the Oracle Database data file header information through oracle event

Direct use of the data file header dump, you can not query the data file header information

Directly through the sql command

alter system dump datafile n block min 1 max 10;

1) It is not any useful information, as follows:

Start dump data blocks tsn: 0 file#:1 minblk 1 maxblk 10
Block 1 (file header) not dumped:use dump file header command
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0 rdba=4194306

Block 1 (file header) not dumped:use dump file header command – From the first sentence can not see the data file DUMP out.

2) Use the oracle event data file header information to DUMP

Oracle Trace Event Description:

Dump (dump) Oracle internal structure of various types can be included in the trace file, so that users can be based on file content to solve various problems. The immediate keyword indicates that the command is issued, immediately assigned structure dump to the trace file, the keyword is used only in alter session statement, and can noy be used with eventnumber, forever keyboard.

trace name is the keyword.

eventname refers to the name of the event (see below), that is, to the actual structure of the name dump. If eventname of context, refers to the internal event tracking number.

forever keyword indicates that the event remains active in the instance or the session period, the same can not be immediate use.

level keyword for the event. But in the wrong stack dump (errorstack) the absence of level.

levelnumber event indicates the level number, usually from 1 to 10, indicates that only dump the structure of the header information, and 10 dump all the information structures.

As in the following experiments:

Headers dump all data files: file_hdrs event

level 1 – represents the control file dump all the data file header.

level 2 – dump all data files represent a common header -generic header.

level 3 – level2 and contains header information in the datafile

level 10 – represent a complete file header dump all data files.

Experiment command is as follows: – Requirements database MOUNT or OPEN state can execute the following command.

SQL> alter system set events 'immediate trace name file_hdrs level 3'; --All data files head are dumped
System altered.
SQL> oradebug setmypid; --require SYSDBA previliage, otherwise user will receive ORA-01031: insufficient privileges
Statement processed.
SQL> oradebug tracefile_name --Find the generated TRACE file
/u01/app/oracle/product/11.2.0.3/dbhome_1/log/diag/rdbms/testdb/testdb/trace/testdb_ora_14751.trc

3) testdb_ora_14751.trc file contents: a simple analysis of the file header information SYSTEM

Trace file /u01/app/oracle/product/11.2.0.3/dbhome_1/log/diag/rdbms/testdb/testdb/trace/testdb_ora_14751.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.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:    newzonetestdb01.newzonetech.com
Release:    2.6.32-200.13.1.el5uek
Version:    #1 SMP Wed Jul 27 22:21:53 EDT 2012
Machine:    i686
Instance name: testdb
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 14751, image: oracle@newzonetestdb01 (TNS V1-V3)
##########################################################################
*** 2014-12-17 10:43:18.205
*** SESSION ID:(64.6265) 2014-12-17 10:43:18.205
*** CLIENT ID:() 2014-12-17 10:43:18.205
*** SERVICE NAME:(SYS$USERS) 2014-12-17 10:43:18.205
*** MODULE NAME:(sqlplus@newzonetestdb01 (TNS V1-V3)) 2014-12-17 10:43:18.205
*** ACTION NAME:() 2014-12-17 10:43:18.205
##########################################################################
DUMP OF DATA FILES: 4 files in database

DATA FILE #1:
name #4: /u01/oradata/bys3/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
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:67 scn: 0x0000.000b3984 11/17/2014 10:00:19
 Stop scn: 0xffff.ffffffff 11/16/2014 12:07:14
 Creation Checkpointed at scn:  0x0000.00000015 11/14/2014 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
 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
  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='testdb'
    Activation ID=0=0x0
    Control Seq=1525=0x5f5, File size=64000=0xfa00
    File Number=1, Blksiz=8192, File Type=3 DATA     
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000015 11/14/2014 14:24:22 --The time stamp the database was created. The database was created manually, so it is ture time,
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0  --RMAN backups do not update this record, hot backup BEGIN BACKUP mode will update this information
 reset logs count:0x318f5cd7 scn: 0x0000.00000001
 prev reset logs count:0x0 scn: 0x0000.00000000
 recovered at 11/15/2014 10:50:16
 status:0x2004 root dba:0x00400208 chkpt cnt: 67 ctl cnt:66
 dba: 0x00400208, specifically point to the location: FILE 1 BLOCK 520
begin-hot-backup file size: 0
 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
Events logged Descriptions
name #4: /u01/oradata/bys3/system01.dbf Dumped system01.dbf file head
tablespace 0, index=1 krfil=1 prev_file=0 prev_file represent the same table space data file number of the previous one, so the value is 0 because there is none
Checkpoint cnt:67 scn: 0x0000.000b3984 11/17/2014 10:00:19 Time of the last checkpoint occurred
Stop scn: 0xffff.ffffffff 11/16/2014 12:07:14 Database still running, so the value of the STOP SCN is 0xffff.ffffffff
Online Checkpointed at scn: 0x0000.00000000 This represents the data file online or offline for quickly skip archive log recovery
V10 STYLE FILE HEADER This is a generic file header, level must be> = 2 have this information before,,,,,,, sentence from the control file, after the data from the file header
Compatibility Vsn = 186646528=0xb200000 control, redo, datafile, tempfile have the same generic file header. By comparing information later
Db ID=3358363031=0xc82c8d97, Db Name=’testdb’ DBID and DBNAME database exists in the first BLOCK DATAFILE. The hex conversion to decimal: select to_number ( ‘c82c8d97’, ‘xxxxxxxxxxxxx’) from dual;
Control Seq=1525=0x5f5, File size=64000=0xfa00 control seq indicates the serial number of the control file. Once the control file update, the seq number will also updated. If the control file control seq less than the data file control sel, that means the control file is come from a backup. File size does not indicate the size of the entire file, but rather that the size of the currently stored in the cache layer of
File Number=1, Blksiz=8192, File Type=3 DATA TYPE = 3 represents common data files (including undo, not temp file, the type of temp file = 6)
–file number is recorded in the file$ table, if the data file is deleted, the file number can be reused
Tablespace #0 – SYSTEM rel_fn:1 This information is obtained from the datafile header block 2, the dump level must be 3 to get this information. Tablespace #0 indicates table space number can query v$tablespace.ts# to verify, rel_fn: 1 indicates relative file, number can be found through the view dba_data_files.relative_fno

		 sys@testdb>select name,ts# from v$tablespace;
		NAME                                  TS#
		------------------------------ ----------
		SYSTEM                                  0
		sys@testdb>select file_name,relative_fno from dba_data_files;
		FILE_NAME                                RELATIVE_FNO
		---------------------------------------- ------------
		/u01/oradata/testdb/system01.dbf                      1
		
Creation at scn: 0x0000.00000015 11/14/2014 14:24:22 The time stamp the database was created. The database was created manually, so it is ture time
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0 RMAN backups do not update this record, hot backup BEGIN BACKUP mode will update this information
status:0x2004 root dba:0x00400208 chkpt cnt: 67 ctl cnt:66 Only system have root dba, used to locate bootstrap$, ctl cnt is a copy of the control file, for discriminating whether the control file from a backup on root dba:. A root dba in the SYSTEM file header, Oracle storage: Root dba: This field only occurs in data file #1, and is the location of blocks required during bootstrapping the data dictionary (bootstrap$). Before Oracle10g, root dba: 0x004001a1, pointing file 1 block 417 objects, DUMP 417 objects can be found in the final point of the file 1 block 377. . 10G is root dba: 0x00400179, pointing file 1 block 377. Now 11G is root
begin-hot-backup file size: 0 The above backup scn update, here is also updated

4.FILE SYSTEM other information in addition to outside – an important part of the interception

DATA FILE #2:
  name #5: /data01/oradata/testdb/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/2014 10:00:19
 Stop scn: 0xffff.ffffffff 11/16/2014 12:07:14
 Creation Checkpointed at scn:  0x0000.00000740 11/14/2014 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='testdb'
    Activation ID=0=0x0
    Control Seq=1525=0x5f5, File size=41600=0xa280
    File Number=2, Blksiz=8192, File Type=3 DATA
Tablespace #1 - SYSAUX  rel_fn:2
Creation   at   scn: 0x0000.00000740 11/14/2014 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/2014 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: /data01/oradata/testdb/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/2014 10:00:19
 Stop scn: 0xffff.ffffffff 11/16/2014 12:07:14
 Creation Checkpointed at scn:  0x0000.00000b51 11/14/2014 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='testdb'
    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/2014 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/2014 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/2014 10:00:19
 
DATA FILE #4:
  name #8: /data01/oradata/testdb/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/2014 10:00:19
 Stop scn: 0xffff.ffffffff 11/16/2014 12:07:14
 Creation Checkpointed at scn:  0x0000.000034f9 11/14/2014 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='testdb'
    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/2014 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/2014 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/2014 10:00:19
 
TEMP FILE #1: External File #201
  name #7: /data01/oradata/testdb/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/2014 14:25:12
 V10 STYLE FILE HEADER:
    Compatibility Vsn = 186646528=0xb200000
    Db ID=3358363031=0xc82c8d97, Db Name='testdb'
    Activation ID=0=0x0
    Control Seq=37=0x25, File size=2560=0xa00
    File Number=1, Blksiz=8192, File Type=6 TEMP FILE
Tablespace #3 - TEMP  rel_fn:1
Creation   at   scn: 0x0000.00000cb8 11/14/2014 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

*** 2014-12-17 12:47:28.961
Processing Oradebug command 'setmypid'

*** 2014-12-17 12:47:28.975
Oradebug command 'setmypid' console output: <none>

*** 2014-12-17 12:47:36.900
Processing Oradebug command 'tracefile_name'

*** 2014-12-17 12:47:36.901
Oradebug command 'tracefile_name' console output:
/u01/app/oracle/product/11.2.0.3/dbhome_1/log/diag/rdbms/testdb/testdb/trace/testdb_ora_14751.trc
Advertisements

About daviewning

I am an Oracle DBA

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: