Thursday, April 24, 2014

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.
  2. 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
    
    
  3. Set a tracefile identifier for easy identification of the trace file that will be generated.
  4. SQL> alter session set tracefile_identifier = arup;
    
    Session altered.
    
    
  5. Dump the first few blocks of the datafile. The file of the SYSTEM tablespace works perfectly. 10 blocks will do nice
  6. SQL> alter system dump datafile '+PROQA3DATA1/PROQA3/PROQA1_system_01.dbf' block min 1 block max 10;
    
    System altered.
    
    
  7. Check the trace file directory for a file with the term "ARUP" in it
  8. 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
    
  9. Open that file. Here is an excerpt of that file.
  10. 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
    
    
  11. Note the section marked in red. The DBID is prominently displayed there.
  12. Db ID=2553024456
    
  13. That's it. Now you have the DBID.
Post a Comment

Translate