We need DBID whenever we lost our all database files, or whenever we need to restore spfile or control file.
After some analysis on discovering DBID I got several ways to find DBID. I will try to demonstrate the procedure.
A) If 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
----------
9210735510
B) 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 May 6 01:25:48 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ZIYADEV (DBID=9210735510)
C) If you have configured AUTOBACKUP ON,
RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE
Now we take BACKUP DATAFILE 4,
RMAN>BACKUP DATAFILE 4;
.
.
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/c-9210735510-20080506-04
The autobackup format is by default c-IIIIIIIIII-YYYYMMDD-QQ,(when it is %F) where:
IIIIIIIIII 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 c-9210735510-20080506-04 we get DBID is 9210735510.
D)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.
1)
RMAN> backup datafile 1;
Starting backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 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/ZIYADEV/datafiles/ZIYADEV/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oradata2/ZIYADEV/flash_recovery_area/ZIYADEV/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp
.
.
RMAN> exit;
Recovery Manager complete.
bash-3.00$ strings /oradata2/ZIYADEV/flash_recovery_area/ZIYADEV/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp |grep MAXVALUE,
.
.
9210735510, MAXVALUE,
So here 9210735510 is the DBID.
2)
RMAN> backup as copy datafile 2;
Starting backup at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/oradata1/ZIYADEV/datafiles/ZIYADEV/undotbs01.dbf
output filename=/oradata2/ZIYADEV/flash_recovery_area/ZIYADEV/datafile/o1_mf_undotbs1_41zwjtx6_.dbf
bash-3.00$ strings /oradata2/ZIYADEV/flash_recovery_area/ZIYADEV/datafile/o1_mf_undotbs1_41zwjtx6_.dbf |grep MAXVALUE
9210735510, MAXVALUE
Here 9210735510 is the DBID
3) From physical data file you can also follow the same method.
From sysaux datafile,
bash-3.00$ strings /oradata1/ZIYADEV/datafiles/ZIYADEV/sysaux01.dbf |grep DBID=
connected to target database: ZIYADEV (DBID=9210735510, not open)
Here 9210735510 is the DBID.
4) From whole database backup you can also follow same method.
RMAN> BACKUP DATABASE;
Starting backup at 06-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
.
.
piece handle=/oradata2/ZIYADEV/flash_recovery_area/ZIYADEV/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp
bash-3.00$ strings /oradata2/ZIYADEV/flash_recovery_area/ZIYADEV/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp |grep MAXVALUE,
.
.
9210735510, MAXVALUE,
Here 9210735510 is the DBID.
: )
