Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 14 November 2012

How to find Oracle DBID

DBID stands for database identifier, which is a unique identifier for each oracle database running. It is found in control files as well as datafile header. If the database is open you can directly querying with the v$database and find the DBID. You can find it without access to the datafiles too.
SQL>startup mount;
SQL>select DBID from v$database;
The DBID is important when you need to recover spfile & controlfile. When you try to recover controlfile without setting DBID through the RMAN it will give you the error:
RMAN-06495: must explicitly specify DBID with SET DBID command
Here if you are using recovery catalog then connect to the recovery catalog of RMAN and issue the “list incarnation” command. You must first nomount the database.
C:\ rman target sys/oracle@orcl3 catalog catalog/catalog@rman
RMAN-06193: connected to target database (not started)
RMAN-06008: connected to recovery catalog database
RMAN> startup nomount;
RMAN-06196: Oracle instance started
Total System Global Area      94980124 bytes
Fixed Size                       75804 bytes
Variable Size                 57585664 bytes
Database Buffers              37240832 bytes
Redo Buffers                     77824 bytes
RMAN> list incarnation;
RMAN-03022: compiling command: list
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            CUR Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       2       ORCL3    691421794        YES 542853     23-AUG-12

Incase of control file recovery if your backups are not in default location or you do not have recovery catalog then RMAN cannot have the information, that which backup piece is appropriate for restore.
Here, you have to set DBID first then try the above recovery. Sometimes it becomes a challenging task as we know DBID is stored in control file which we have already lost. So it is important for a DBA to document DBID and server information in case of emergency.
This article stands here. Is there a way to find DBID if the database is down and you are not using recovery catalog and if you did not maintain any document”?
Best of my knowledge check the following way in this situation:
  1. We can get DBID from RMAN output (either backup log or RMAN session)
  2. From RMAN autobackup (while control file autobackup parameter ON)
  3. We can configure alert log file to store DBID regularly
  4. We can retrieve DBID from a file dump.
1. DBID from RMAN Output:
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: ISSCOHR (DBID=2613999945)
connected to recovery catalog database
2. 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.
Starting Control File and SPFILE Autobackup at 31-OCT-12
piece handle=E:\RMAN_BACKUP\C-2613999945-20121031-00 comment=NONE
Finished Control File and SPFILE autobackup at 31-OCT-12
Where C indicates that it is a control file backup
2613999945 indicates DBID
20121031 indicates the date backup was created
00 indicates a hex sequence number to make the filename unique on the same day
3. DBID from the 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));
You will get an entry in alert log such as:
DBID: 1681257132
If you did not set autobackup ON then in that case try to check DBID from backup piece or any image copy that holds either SYSTEM or SYSAUX or UNDO datafiles.
Note: If you backup your database as ‘backup as compressed’ then with this method you will not be able to see DBID.
If you have SYSTEM datafile or UNDO datafile either as image copy or as backup piece then you can use below command on UNIX platform to find DBID respectively:
strings file_name |grep MAXVALUE
strings undotbs01.dbf |grep MAXVALUE
If you have SYSAUX datafile either as image copy or as backup piece then you can use:
strings file_name |grep DBID
4. DBID FROM A FILE DUMP:
Other way is, if any of the physical files(datafiles/logfiles and even archived log files) are available, we can extract the DBID to a trace file. For that we do not need the database to be mounted:
SQL> connect /@rman as sysdba
SQL>startup nomount;
SQL>alter system dump datafile 'D:\Isscohr\oradata\SYSTEM.DBF' block min 1 block max 10;
Now search in the trace file generated in user_dump_dest location with the string 'Db ID' and you will get an entry such as:
Db ID=1681257132=0x6435f2ac, Db Name='RMAN'
You can use the same syntax to make a dump of redo log files/archived log files as :
SQL> alter system dump logfile ' ';

If from all the above four steps you are not able to get the DBID because you have lost all database files, you are not using a recovery catalog and you are not using autobackup of controlfile then if you have old controlfile available then mount the database with the old controlfile then query with v$database view.

5 comments:

  1. Hello Shahid, your post is really awesome. Specially different methods you have explained to find Oracle dbid. Thanks for sharing with us.

    ReplyDelete
  2. Its working but can i find out the DB id without using the rman.

    ReplyDelete
  3. Seriously it's perfect....

    ReplyDelete
  4. Hi Shahid, thanks for your sharing.

    ReplyDelete