Wednesday, October 17, 2012

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

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.

10 comments:

Anonymous said...

I've been exploring for a little bit for any high quality articles or blog posts on this kind of space . Exploring in Yahoo I ultimately stumbled upon this web site. Studying this information So i am satisfied to convey that I have a very just right uncanny feeling I found out just what I needed. I most surely will make sure to don?t disregard this website and give it a look on a constant basis.

My web page Three Certain shot techniques for getting rid of man boobs

Anonymous said...

Hi, I would like to subscribe for this weblog to take hottest
updates, therefore where can i do it please help out.


Here is my webpage: password For

Anonymous said...

Great article, just what I was looking for.

Here is my web-site; minecraft world seeds

Anonymous said...

What's Happening i am new to this, I stumbled upon this I've discovered
It positively helpful and it has aided me
out loads. I am hoping to give a contribution & help different users like its aided me.

Great job.

Here is my web page ... get minecraft for free

Anonymous said...

Everything is very open with a very clear clarification of the challenges.
It was definitely informative. Your website is useful. Thanks for sharing!



my page - Free Minecraft

Anonymous said...

It's very simple to find out any topic on net as compared to books, as I found this paragraph at this web site.

Here is my web site; Funny Youtube Videos

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher