Retrieve the DATABASE IDENTIFIER from datafiles or online logs

This article is about the steps which require recovery when we retrieve the DATABASE IDENTIFIER dumping it from datafiles or online and archived redo logs . DBID stands for database identifier, which is a unique identifier for each Oracle database running. It is found in control files as well as datafile header. If the database is open you can directly be querying the v$database and find the DBID.

These are some conditions when do we require DBID for database recovery

The set dbid command is useful for restoring the control file when each of these conditions is met:

•     The control file has been lost and must be restored from a backup.
•     You are using a recovery catalog.
•     Multiple databases registered in the recovery catalog share a database name.
•     You receive the “RMAN-20005: target database name is ambiguous” message when you attempt to restore the control file.
•     you don’t have even a flash recovery area configured OR you have it but you didn’t specify the %F in the RMAN autobackup option;

I think it’s very difficult today to be in a situation where it’s required to know your database identifier to recover the database.
But according to an OP (original poster), it seems it could happen: indeed on that Oracle forum’s thread it was asked how it could be possible to determine a database identifier (DBID) when:

1. an instance is already crashed and no control files are available
2. No one saved the RMAN output of daily backup
3. You are using a flash recovery area and your autobackup are saved using OMF syntax.

So if your instance is down and control files are unavailable you cannot open the database and query the V$DATABASE to know the DBID.
I mean you can’t execute a query like this:

SQL> select DBID from V$DATABASE;

DBID
----------
1229390655

Then when you connect to your instance using RMAN it will show you the database identifier…

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Oct 16 06:49:13 2012

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

connected to target database: ORCL (DBID=1229390655)

…but as long as you didn’t redirect RMAN output to some logs and preserved them in a safe directory or simply email it to you during daily backup, when instance is down and without control files RMAN could not help you anymore:

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Oct 16 06:57:44 2012

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

connected to target database (not started)

Moreover, if you didn’t specify the %F format to your autobackup control file option, I mean something like:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/app/oracle/autobackup_controlfile/%F';

…your database will save it using the OMF format. As you can read on the Oracle documentation at this link
“All files in the fast recovery area are maintained by Oracle Database and associated file names are maintained in the Oracle Managed Files (OMF) format” indeed your autobackup control file won’t be useful to deduct your DBID using the desired format c-IIIIIIIIII-YYYYMMDD-QQ (where IIIIIIIIII would be your database identifier).

So then how can you proceed? Is it no more possible to know the database identifier?

My suggestion was to simply use the “ALTER SYSTEM DUMP” command.
As long as you can dump any datafiles, redo logs and even archived redo logs the instance could be in NOMOUNT mode: to obtain the desired DBID you have only to know the exact path of your file.

Have a look at the following samples:

[oracle@localhost ~]$ sqlplus / as sysdba
SQL> startup nomount;

Command to dump the SYSTEM datafile:

SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/system01.dbf' block min 1 block max 2;

System altered.

Under the trace directory I found a new trace file which content is not so clear, but at least our database identifier (Db ID=1229390655) is showed.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/system01.dbf minblk 1 maxblk 2
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=1229390655=0x4947033f, Db Name='ORCL'
...

Command to dump the UNDO datafile:

SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf' block min 1 block max 2;

System altered.

Again on the same trace file Oracle appends the dump of the UNDO datafile requested and the same DBID.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf minblk 1 maxblk 2
V10 STYLE FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=1229390655=0x4947033f, Db Name='ORCL'
...

What about to dump an online redo log?

SQL> alter system dump logfile '/home/oracle/app/oracle/oradata/orcl/redo01.log' ;

System altered.

Always on the same trace file you can find the DBID.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
DUMP OF REDO FROM FILE '/home/oracle/app/oracle/oradata/orcl/redo01.log'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=1229390655=0x4947033f, Db Name='ORCL'
Activation ID=1323612153=0x4ee4b7f9
...

Finally even dumping an ARCHIVED redo log…

SQL> alter system dump logfile '/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_10_05/o1_mf_1_20_86xpzzvr_.arc';

System altered.

… and looking at the trace file, the DBID is showed again.

[oracle@localhost ~]$ tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
DUMP OF REDO FROM FILE '/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_10_05/o1_mf_1_20_86xpzzvr_.arc'
Opcodes *.*
RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
Times: creation thru eternity
FILE HEADER:
Compatibility Vsn = 186646528=0xb200000
Db ID=1229390655=0x4947033f, Db Name='ORCL'
...

You have no excuse to be not able retrieving a specific database identifier.

Once the update is done, Follow the same process of upgrading agents.

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp