19 | 10 | 2017
Latest Articles
Popular Articles

Administration

Dump file headers

User Rating:  / 1
PoorBest 

Dump file headers, database blocks and generate recreate control file script

 

The below commands are safe, their added value is limited.

 

With the below command we dump database control file info

SQL> alter session set events 'immediate trace name controlf level 10';

Session altered.

With the below command we dump data file header info

SQL> alter session set events 'immediate trace name file_hdrs level 10';


Session altered.

With the below command we dump redo log file header info

SQL> alter session set events 'immediate trace name redohdr level 10';


Session altered.

With the below command we generate a trace file in the user dump directory. This can be used to recreate the controlfile. The trace file is reabable, it does not take a hero to recreate database control files. HOWEVER ONLY RECREATE CONTROL FILES  FOR PRODUCTION DATABASES IF REALLY NEEDED. THE CONTROL FILE IS A CRITICAL PIECE OF INFORMATION FOR ZERO DATA LOSS RECOVERY SCENARIO' S. IF YOU ARE SERIOUS WITH YOUR CAREER AS ORACLE DBA, THEN TAKE DAILY BACKUPS OF THE DATABASE CONTROL FILE. THE BACKUP CONTROLFILE SHOULD BE TAKEN AFTER THE BACKUP OF DATA FILES

Prior to Oracle 9iR2 the DBA needed to recreate the database control file(s)

1. when all the control files were "lost" and you have no control file backups ( shame on you )

2. when the database needed to be renamed

From 9iR2 onwards the DBA only needs to recreate the database control file(s)

1. when all the control files were "lost" and you have no control file backups ( shame on you ) 

SQL> alter database backup controlfile to trace;


Database altered.

With the below command we dump 1 data block ( segment header )

SQL> select segment_name,header_file,header_block from dba_segments where segment_name='T_EXCHANGERATE_ER';


SEGMENT_NAME


HEADER_FILE HEADER_BLOCK


T_EXCHANGERATE_ER
      5         378

SQL> alter system dump datafile 5 block min 378 block max 378;


System altered.

With the below command we dump a few data blocks

SQL> alter system dump datafile 5 block min 379 block max 381;


System altered.