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.
- 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
- Set a tracefile identifier for easy identification of the trace file that will be generated.
SQL> alter session set tracefile_identifier = rajat; Session altered.
- 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.
- Check the trace file directory for a file with the term “rajat” in it
prolin1:/PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace>ls -l *rajat* -rw-r--r-- 1 oracle asmadmin 145611 Apr 24 21:17 PROQA31_ora_61079250_rajat.trc -rw-r--r-- 1 oracle asmadmin 146 Apr 24 21:17 PROQA31_ora_61079250_rajat.trm
- Open that file. Here is an excerpt of that file.
Trace file /PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace/PROQA31_ora_61079250_rajat.trc Oracle Database 11g Enterprise Edition Release 220.127.116.11.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
- Note the section marked in red. The DBID is prominently displayed there.
- That’s it. Now you have the DBID.