How To Find DBID in NOMOUNT State

Oracle Database identifier in short DBID is an internal, unique identifier for an Oracle database. Database administrator must note down the DBID in safe place, so that any miss-happening to the database could be easily identified and recovered. In case it is required to recover SPFILE or control file from autobackup, such as disaster recovery, you will need to set DBID. So lets see how to get DBID in NOMOUNT State.

Why DBID is important? 

• It is an unique identifier for a database.
• In case of backup and recovery RMAN distinguishes databases by DBID.
• When DBID of a database is changed, all previous backups and archived logs of the database become unusable.
• After you change the DBID, you must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their log sequence to 1 
• You should make a backup of the whole database immediately after changing the DBID.

Let's take an example of getting it in nomount state:

First shut down the database using shut immediate command

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Now startup database in nomount state

SQL> startup nomount
ORACLE instance started.
Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             402657268 bytes
Database Buffers          197132288 bytes
Redo Buffers                5640192 bytes

You can also set tracefile identifier for easily identification of tracefile.

SQL> alter session set tracefile_identifier=orahow;
Session altered.

Now, dump first ten block of datafile, because each block header contains dbid information.

SQL> alter system dump datafile 'D:\app\SantoshTiwari\oradata\TEST11\USERS01.DBF'
  2  block min 1 block max 10;
System altered.

Now find the location of Trace file.

SQL> show parameter user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      d:\app\santoshtiwari\diag\rdbm
                                                 s\test11\test11\trace

Now search for Db ID inside the trace file. In Linux you can use cat command with grep to find it:

cat filename | grep Db id

Here you can see the dump here:

Start dump data block from file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\USERS01.DBF minblk 1 maxblk 10
 V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=3561501508=0xd4483344, Db Name='TEST11'
Activation ID=0=0x0
Control Seq=3522=0xdc2, File size=640=0x280
File Number=4, Blksiz=8192, File Type=3 DATA


In simple you can also get it using v$database:

SQL> select name, dbid from v$database;

NAME            DBID

--------- ----------

TEST11    3561501508

 DBID is also displayed by the RMAN client when it starts up and connects to your database. Typical output follows:

SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 6 19:59:06 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)