Saturday, February 14, 2009

HOW TO GET ORACLE DATABASE IDENTIFIER (DBID)

DBID stands for database identifier. Which is a unique identifier for each oracle database running.we can get the DBID from the v$database view.

sql>select DBID from v$database;

DBID
----------
396604920

so, above command will show you the unique database identifier of your database which is stored in your CONTROL files.You may need to know your Database DBID on some recovery scenarios.one example is , you may need to restore your control files from rman backup as you have lost all of your control files.There are lot of conditions apply in recovering a control file depending on the type of backup taken and the location of backup.We will discuss about this
in the control file recovery situations documentation afterward .But, while recovering the control file you may end up here:

RMAN> connect target /@lion
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
OUTPUT asking about DBID:

RMAN-06495: must explicitly specify DBID with SET DBID command


this is where this article stands firm.........
Now , you have to set DBID first then try the above recovery stunts.Now ,it becomes a challenging task as we know DBID is stored in control file which we have already lost........

So, is there a way out???
Yes, Oracle can do almost everything........

Let us check the following way to know what is my database DBID

We can get ORACLE DATABASE DBID from :
a) a rman autobackup file.(backup taken with control file autobackup on parameter)
b) we can configure alert log file to store DBID regularly
c) we can retrieve DBID from a file dump
d) we can get DBID from rman output

In detail:

a)DBID FROM CONTROL FILE AUTOBACKUP:
If you choose to take rman control file autobackup on, then mention the format parameter with %F, so that it will generate the file name as %F, this %F parameter includes the DBID in the filename.

ex: %F maintains the c-IIIIIIIIII-YYYYMMDD-QQ format .
where c indicates that it is a control file backup
IIIIIIIIII indicates DBID
YYYYMMDD indicates the date backup was created
QQ indicates a hex sequence number to make the filename unique on the same day

b)GET DBID FROM ALERT LOG FILE:
We can make sure that my database DBID is written to alert log file on regular basis with the help of a package named DBMS_SYSTEM .
To do that, include the following to your regular backup job or script:

COL dbid NEW_VALUE hold_dbid

SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));

after running this code, check your alert log now. you will get an entry like:
DBID: 396604920

c)DBID FROM A FILE DUMP:
Other way is, if any of the physical files(datafiles,logfiles,even archived log files) are available, we can extract the DBID to a trace file. More importantly, we dont need the database to be mounted for this:
example:
sql> connect /@lion as sysdba
sql>startup nomount;
sql>alter system dump datafile 'D:\lion\data\SYSTEM.DBF' block min 1 block max 10;

Now search the in the trace file generated in user_dump_dest location with the string 'Db ID'. and you will get an entry like:
Db ID=396604920=0x17a3b5f8, Db Name='LION'

we can use the same syntax to make a dump of redo log files/archived log files as :

SQL> alter system dump logfile '';

d)DBID from RMAN output:
whenever you will connect to your target database rman utility, it will show the DBID as follows on the post connection output. see the following:(for this your database should be at least mounted,when you connect RMAN)
Rman> connect target /@lion
connected to target database: LION (DBID=396604920, not open)

So, you can view your previously rman output log files to get the DBID , as you surely have executed rman scripts earlier.


Rman uses DBID to identify a database uniquely. So, in control file recovery scenario,if your backups are not in default location or you dont have recovery catalog then rman cannot have the information , that which backup piece is appropriate for restore. So, it requieres DBID.

Thanks,

have a nice day..............

3 comments:

Anonymous said...

thanks..... gud engh and helpful.wll explnd

Anonymous said...

Hi, I do think this is a great site. I stumbledupon it ;) I will return
once again since i have book marked it. Money and freedom is the greatest
way to change, may you be rich and continue to help others.



Here is my homepage - http://www.youtube.com/watch?v=d-rCr9DBuvg

Anonymous said...

awesome tips!

when all the alternatives fail, the last resort of outputting the datafile to trace saves your day!

there you have your DB ID

Big thanks to ASKERSHAD!!