Monday, May 5, 2008

How to Discover find DBID

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
----------
2869417476

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: ARJU (DBID=2869417476)

C)If you have configured AUTOBACKUP ON,

RMAN> SHOW CONTROLFILE AUTOBACKUP;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

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-2869417476-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-2869417476-20080506-04 we get DBID is 2869417476.


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/arju/datafiles/ARJU/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/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp
.
.

RMAN> exit;
Recovery Manager complete.

bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T014702_41zw6p8j_.bkp |grep MAXVALUE,
.
.

2869417476, MAXVALUE,

So here 2869417476 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/arju/datafiles/ARJU/undotbs01.dbf
output filename=/oradata2/arju/flash_recovery_area/ARJU/datafile/o1_mf_undotbs1_41zwjtx6_.dbf

bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/datafile/o1_mf_undotbs1_41zwjtx6_.dbf |grep MAXVALUE

2869417476, MAXVALUE

Here 2869417476 is the DBID

3)From physical data file you can also follow the same method.

From sysaux datafile,

bash-3.00$ strings /oradata1/arju/datafiles/ARJU/sysaux01.dbf |grep DBID=
connected to target database: ARJU (DBID=2869417476, not open)

Here 2869417476 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/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp

bash-3.00$ strings /oradata2/arju/flash_recovery_area/ARJU/backupset/
2008_05_06/o1_mf_nnndf_TAG20080506T015705_41zwskgv_.bkp |grep MAXVALUE,

.
.
2869417476, MAXVALUE,

Here 2869417476 is the DBID.

Enjoy!!!

Related Documents
How to Change Database Name and DBID?
Posted by at
Labels: ,

3 comments:

  1. RonarldAugust 7, 2008 at 1:45 AM

    It is life saving Answer. Thank You.

    ReplyDelete
  2. AnonymousMay 10, 2010 at 2:51 AM

    Hi arju...
    I have seen that this format works when we set specifically/explicitly configure as
    CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';

    arju..i didnt see this ..
    great article...

    ReplyDelete
  3. AnonymousMay 10, 2010 at 3:06 AM

    what is it arju?
    RMAN> SHOW CONTROLFILE AUTOBACKUP;

    RMAN configuration parameters are:
    CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

    RMAN> BACKUP AS COPY DATAFILE 2;

    Starting backup at 10-MAY-10
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00002 name=/u01/app/oracle/oradata/testdb/undotbs01.dbf
    output filename=/u01/app/oracle/flash_recovery_area/TESTDB/datafile/o1_mf_undotbs1_5yhp36v5_.dbf tag=TAG20100510T152653 recid=11 stamp=718644421
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
    Finished backup at 10-MAY-10

    i got output, like this below:
    [oracle@cdbs1 ~]$ strings /u01/app/oracle/flash_recovery_area/TESTDB/datafile/o1_mf_undotbs1_5yhp36v5_.dbf | grep MAXVALUE
    MAXVALUE$

    what is it MAXVALUES$?

    ReplyDelete

Subscribe to: Post Comments (Atom)