Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 129,480
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

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
/u01/app/oracle/diag/rdbms/sairam/sairam/trace

Below is the excerpt from the trace file which contains header information
*IN BOLD WE HAVE THE DBID OF THE DATABASE.

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
V10 STYLE FILE HEADER:
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

Thanks
Prashant Dixit

Advertisements

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 )

w

Connecting to %s

 
%d bloggers like this: