We need DBID whenever we lost our all database files, or whenever we need to restore spfile or control file.
DBID is necessary forany Oracle DBA in multiple scenarios.As it's unique for database to database few people use to keep a record of their DBID,
But this page is written for those who don't keep a record for that (Just Like me 'a Lazy DBA' :-)
So there are different ways to find DBID of a database, when a database is up/down.
Assuming that the database is up:
You can query V$database and get the DBID and record it in somewhere.
or,if the database is down and you have control file then you can mount the database and query from V$SATABASE.
SQL> SELECT DBID FROM V$DATABASE;
DBID
----------
236329879
If you log the RMAN backup or if you preserve output of RMAN session then you can get DBID from that output.
$rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Aug 6 04:45:06 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: KRISH (DBID=236329879)
If you have configured AUTOBACKUP ON,
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
Now, take the backup of some datafile,
RMAN>backup datafile 12;
.
.
Starting Control File and SPFILE Autobackup at 06-AUG-13
piece handle=/backup/rman_bkp/ctrl_bkp-236329879-20130806-06
The autobackup format is by default c-XXXX-YYYYMMDD-QQ,(when it is %F) where:
XXXX stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF
I have seen that this format works when we set specifically/explicitly configure as
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
Now in the file from ctrl_bkp-236329879-20130806-06 we get DBID is 236329879.
If you did not set Autobackup on which is by default. After many analysis I found that DBID can be seen from backup piece or any image copy that holds either SYSTEM or SYSAUX or UNDO datafiles.
Though if you backup your database as backup as compressed then with this method you will not be able to discover DBID.
If you have SYSTEM datafile or UNDO datafile either as image copy or as backup piece then you can use,
strings file_name |grep MAXVALUE, (In case of SYSTEM datafile)
strings file_name |grep MAXVALUE (In case of UNDO datafile)
to find DBID.
If you have SYSAUX datafile either as image copy or as backup piece then you can use,
strings file_name |grep DBID= to find DBID.
Examples:
-----------------
Both of these example is based on UNIX scenario.
I - RMAN> backup datafile 1;
Starting backup at 06-AUG-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=123 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata1/arju/datafiles/ARJU/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-AUG-13
channel ORA_DISK_1: finished piece 1 at 06-AUG-13
piece handle=/backup/rman_bkp/bkp_jjsk_TAG20130806T025704_46zwk6p8j_.bkp
RMAN> exit;
Recovery Manager complete.
bash-3.00$ strings /backup/rman_bkp/bkp_jjsk_TAG20130806T025704_46zwk6p8j_.bkp |grep MAXVALUE,
.
.
236329879, MAXVALUE,
So here 236329879 is the DBID.
II - RMAN> backup as copy datafile 2;
Starting backup at 06-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=123 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oradata/krish/undotbs01.dbf
output filename=/backup/rman_bkp/o6_kg_undotbs1_41kghtx6_.dbf
bash-3.00$ strings /backup/rman_bkp/o6_kg_undotbs1_41kghtx6_.dbf
|grep MAXVALUE
236329879 , MAXVALUE
Here 236329879 is the DBID
III - From physical data file you can also follow the same method.
From sysaux datafile,
bash-3.00$ strings /oradata/krish/sysaux01.dbf |grep DBID=
connected to target database: KRISH (DBID=236329879 , not open)
Here 236329879 is the DBID.
IV - From whole database backup you can also follow same method.
RMAN> BACKUP DATABASE;
Starting backup at 06-AUG-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
.
.
piece handle=/backup/rman_bkp/o6_kg_yyyfs_TAG20130806T01436_51udskgk.bkp
bash-3.00$ strings /backup/rman_bkp/o6_kg_yyyfs_TAG20130806T01436_51udskgk.bkp |grep MAXVALUE,
.
.
236329879 , MAXVALUE,
Here 236329879 is the DBID.
No comments:
Post a Comment
Thank for showing interest in giving comments/feedback/suggestions
Note: Only a member of this blog may post a comment.