How to retrieve the DATABASE IDENTIFIER dumping it from datafiles or online and archived redo logs

Tweet

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.

A typical scenario where it is asked to know the database identifier is indeed when:
- you are restoring a control file (and I can image you lose all control files) AND
- you don't have a recovery catalog AND
- 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;

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 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 dentifier).

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.

That's all.