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.
- Set a tracefile identifier for easy identification of the trace file that will be generated.
- Dump the first few blocks of the datafile. The file of the SYSTEM tablespace works perfectly. 10 blocks will do nice
- Check the trace file directory for a file with the term "ARUP" in it
- Open that file. Here is an excerpt of that file.
- Note the section marked in red. The DBID is prominently displayed there.
- That's it. Now you have the DBID.
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
SQL> alter session set tracefile_identifier = arup; Session altered.
SQL> alter system dump datafile '+PROQA3DATA1/PROQA3/PROQA1_system_01.dbf' block min 1 block max 10; System altered.
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
Trace file /PROQA/orabase/diag/rdbms/PROQA3/PROQA31/trace/PROQA31_ora_61079250_ARUP.trc Oracle Database 11g Enterprise Edition Release 18.104.22.168.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