Oracle 12c – Finding the DBID – The last resort

By William Sescu

The DBID is a very important part for Oracle databases. It is an internal, uniquely generated number that differentiates databases. Oracle creates this number automatically as soon as you create the database.

During normal operation, it is quite easy to find your DBID. Whenever you start your RMAN session, it displays the DBID.

oracle@oel001:/home/oracle/ [OCM121] rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Nov 28 10:32:47 2016

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

connected to target database: OCM121 (DBID=3827054096)

RMAN>

Or you can just simply select it from your v$database view.

SQL> select DBID from v$database;

DBID
----------
3827054096

But what happens in case you have a restore/recovery scenario where you lost your database. In the NOMOUNT state, it is not possible to retrieve the DBID.

SQL> select DBID from v$database;
select DBID from v$database
                 *
ERROR at line 1:
ORA-01507: database not mounted

You can take a look into the alert.log or any other trace file in your DIAG destination, but you will not find a DBID there.

So, if the only thing that you have left is your RMAN Catalog, and your datafile copies in your FRA + Archivelogs, then you need the DBID beforehand, before you can restore/recover your database correctly.

There are three possibilities to get your DBID

• You could check your RMAN backup log files, if you have set it up correctly
• You could connect to your RMAN catalog and query the “DB” table from the catalog owner. Be careful, there might be more than one entry for your DB name, and then it might become difficult to get the correct one.  In my example, I have only one entry

  SQL> select * from db;
  
      DB_KEY      DB_ID REG_DB_UNIQUE_NAME             CURR_DBINC_KEY S
  ---------- ---------- ------------------------------ -------------- -
           1 3827054096 OCM121                                      2 N

• And as the last resort, you can startup nomount (either with a backup pfile or with the RMAN dummy instance), and afterwards you can dump out the header of your datafile copies in your FRA

Dumping out the first block is usually enough, and besides that, you are not limited to the SYSTEM datafile. You can use any of your datafile copies in your FRA (like SYSAUX, USERS and so on) to dump out the block, like shown in the following example:

-- Dump the first block from the SYSTEM datafile
SQL> alter session set tracefile_identifier = dbid_system;
Session altered.

SQL> alter system dump datafile '+fra/OCM121/datafile/SYSTEM.457.926419155' block min 1 block max 1;
System altered.

oracle@oel001:/u00/app/oracle/diag/rdbms/ocm121/OCM121/trace/ [OCM121] cat OCM121_ora_6459_DBID_SYSTEM.trc | grep "Db ID"
        Db ID=3827054096=0xe41c3610, Db Name='OCM121'

-- Dump the first block from the SYSAUX datafile		
SQL> alter session set tracefile_identifier = dbid_sysaux;
Session altered.

SQL> alter system dump datafile '+fra/OCM121/datafile/SYSAUX.354.926417851' block min 1 block max 1;
System altered.

oracle@oel001:/u00/app/oracle/diag/rdbms/ocm121/OCM121/trace/ [OCM121] cat OCM121_ora_7035_DBID_SYSAUX.trc | grep "Db ID"
        Db ID=3827054096=0xe41c3610, Db Name='OCM121'

-- Dump the first block from the USERS datafile
SQL> alter session set tracefile_identifier = dbid_users;
Session altered.

SQL> alter system dump datafile '+fra/OCM121/datafile/USERS.533.926419511' block min 1 block max 1;
System altered.

oracle@oel001:/u00/app/oracle/diag/rdbms/ocm121/OCM121/trace/ [OCM121] cat OCM121_ora_7064_DBID_USERS.trc | grep "Db ID"
        Db ID=3827054096=0xe41c3610, Db Name='OCM121'

As soon as you have your DBID, it is straight forward to do the rest. Connect to your target and RMAN catalog, set the DBID and then run your restore, recovery scripts.

rman target sys/manager catalog rman/rman@rman
set dbid=3827054096
run {
restore spfile from autobackup;
}

run {
restore controlfile ....
}

run {
restore database ....
recover database ....
}

Conclusion

Don’t forget to save your DBID with your RMAN backup jobs somewhere. Recovering a database at 3 o’clock in the morning with a missing DBID might become a nightmare.
Cheers,
William