Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 148,961
  • Archives

  • Categories

  • Subscribe

  • Advertisements

Get DBID when Instance is in NOMOUNT mode.

Posted by FatDBA on May 5, 2014

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

Below is the excerpt from the trace file which contains header information

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
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

Prashant Dixit


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: