Tales From A Lazy Fat DBA

One fine day we found that we have lost our control-file and the catalog.
RULE: To restore the controlfile, you must know the DBID. Being a UAT/Testing server we don’t have noted the DBID at any safe place.
Method/Fix: You can extract the DBID from the header of a datafile, assuming you have access to it. The database instance needs to up in NOMOUNT mode. Well, it has to be NOMOUNT because you haven’t restored the controlfile yet, a major requirement for the mount operation.

We have forced the DB to start in NOMOUNT Mode.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1337632 bytes
Variable Size             402654944 bytes
Database Buffers          113246208 bytes
Redo Buffers                5869568 bytes

SQL> show parameter db_name
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_name                              string      sairam

Let us place a unique identifier in the trace file names in order to easily identify the trace generated which used to find the DBID of the database.
SQL> alter session set tracefile_identifier = dixit;
Session altered.

We’ll now dump one of the datafile and dump few of the DB Blocks (12 Blocks)
SQL> alter system dump datafile ‘/u01/app/oracle/oradata/sairam/xyz.dbf’ block min 1 block max 12;
System altered.

Traces generated during the ALTER SYSTEM DUMP step which contains block header information which includes DBID.

-rw-r—– 1 oracle oinstall  170 May  5 21:44 sairam_ora_9192_DIXIT.trm
-rw-r—– 1 oracle oinstall 177K May  5 21:44 sairam_ora_9192_DIXIT.trc
[oracle@prashant trace]$ pwd
/u01/app/oracle/diag/rdbms/sairam/sairam/trace

Below is the excerpt from the trace file which contains header information
*IN BOLD WE HAVE THE DBID OF THE DATABASE.

System name:    Linux
Node name:      prashant
Release:        2.6.18-164.el5
Version:        #1 SMP Tue Aug 18 15:51:54 EDT 2009
Machine:        i686
Instance name: sairam
Redo thread mounted by this instance: 0 <none>
Oracle process number: 19
Unix process pid: 9192, image: oracle@prashant (TNS V1-V3)

*** 2014-05-05 21:44:16.933
*** SESSION ID:(1.3) 2014-05-05 21:44:16.933
*** CLIENT ID:() 2014-05-05 21:44:16.933
*** SERVICE NAME:() 2014-05-05 21:44:16.933
*** MODULE NAME:(sqlplus@prashant (TNS V1-V3)) 2014-05-05 21:44:16.933
*** ACTION NAME:() 2014-05-05 21:44:16.933

Start dump data block from file /u01/app/oracle/oradata/sairam/xyz.dbf minblk 1 maxblk 12
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=410122232=0x1871f7f8, Db Name=’SAIRAM’
Activation ID=0=0x0
Control Seq=22929=0x5991, File size=2560=0xa00
File Number=7, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 7 rdba: 0x01c00002 (1024/29360130)
scn: 0x0000.001a1cf6 seq: 0x01 flg: 0x04 tail: 0x1cf61d01
frmt: 0x02 chkval: 0xb87a type: 0x1d=KTFB Bitmapped File Space Header
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00518600 to 0x0051A600

Thanks
Prashant Dixit