How to Get the DBID when Instance in in NOMOUNT State

You lost your controlfile and the catalog. To restore the controlfile, you must know the DBID. Did you follow the advise to write down the DBID in a safe place? You didn't, did you? Well, what do you do next? Don't worry; you can still get the DBID from the header of the data files. Read on to learn how.


If you have lost your controlfile and the catalog database (or the database was not registered to a recovery catalog anyway), you  need to restore the controlfile first and then restore the other files. I wrote a blog post on that activity earlier. In summary, here is what you need to do to restore the controlfile from the backup:

You need the DBID. IF you don't know the DBID, don't panic. 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. If you have the database mounted, this blog post is not for you since you have access to the V$DATABASE view and therefore the DBID. But at that point the DBID is not required anyway.

1. Bring up the instance in nomount mode.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 6.8413E+10 bytes
Fixed Size 2238616 bytes
Variable Size 1.6777E+10 bytes
Database Buffers 5.1540E+10 bytes
Redo Buffers 93618176 bytes

2. Set a tracefile identifier for easy identification of the trace file that will be generated.

SQL> alter session set tracefile_identifier = arup;

Session altered.

3. Dump the first few blocks of the datafile. The file of the SYSTEM tablespace works perfectly. 10 blocks will do nice

SQL> alter system dump datafile '+PROQA3DATA1/PROQA3/PROQA1_system_01.dbf' block min 1 block max 10;

System altered.

4. Check the trace file directory for a file with the term "ARUP" in it

prolin1:/PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace>ls -l *ARUP*
-rw-r--r-- 1 oracle asmadmin 145611 Apr 24 21:17 PROQA31_ora_61079250_ARUP.trc
-rw-r--r-- 1 oracle asmadmin 146 Apr 24 21:17 PROQA31_ora_61079250_ARUP.trm

5. Open that file. Here is an excerpt of that file.

Trace file /PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace/PROQA31_ora_61079250_ARUP.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /PROQA/oracle/db1
System name: AIX
Node name: prolin1
Release: 1
Version: 7
Machine: 0008F1CBD400
Instance name: PROQA31
Redo thread mounted by this instance: 0 
Oracle process number: 26
Unix process pid: 61079250, image: oracle@prolin1(TNS V1-V3)


*** 2014-04-24 21:17:16.957
*** SESSION ID:(937.3) 2014-04-24 21:17:16.957
*** CLIENT ID:() 2014-04-24 21:17:16.957
*** SERVICE NAME:() 2014-04-24 21:17:16.957
*** MODULE NAME:(sqlplus@prolin1 (TNS V1-V3)) 2014-04-24 21:17:16.957
*** ACTION NAME:() 2014-04-24 21:17:16.957


*** TRACE CONTINUED FROM FILE /PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace/PROQA31_ora_61079250.trc ***

Start dump data block from file +PROQA3DATA1/PROQA3/PROQA1_system_01.dbf minblk 1 maxblk 10
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=2553024456=0x982c0fc8, Db Name='PROQA3'
Activation ID=0=0x0
Control Seq=8419=0x20e3, File size=524288=0x80000
File Number=1, Blksiz=8192, File Type=3 DATA
Dump all the blocks in range:
buffer tsn: 0 rdba: 0x00400002 (1024/4194306)
scn: 0x071b.e7e3500f seq: 0x02 flg: 0x04 tail: 0x500f1d02

6. Note the section marked in red. The DBID is prominently displayed there.

Db ID=2553024456

7. That's it. Now you have the DBID.