How to Obtain DBID Information of Database in oracle

1. Query v $database for

Because DBID has records in both control files and data files, you can query the v $database view if you can use the mount database.

SQL> alter database mount;
Database altered.
SQL> select dbid from v$database;
      DBID
----------
3152029224

2. In the nomount state

If the database is configured with automatic control file backup (Oracle9i) and the name is the default, then we can get DBID from the automatic backup file.

[oracle@jumper dbs]$ cd $ORACLE_HOME/dbs
[oracle@jumper dbs]$ ll c-*
-rw-r----- 1 oracle dba 3375104 Dec 21 11:13 c-3152029224-20051221-00
-rw-r----- 1 oracle dba 3358720 Jan 21 14:03 c-3152029224-20060121-00
-rw-r----- 1 oracle dba 3358720 Jan 21 14:08 c-3152029224-20060121-01

Here 3152029224 is DBID. In 10g, using Flash Recovery Area, there is no such naming convention.

3. Restore from an automatic backup

DBID is required or missing for recovery usually because all control files are missing. Errors will be encountered during recovery.

[oracle@jumper dbs]$ rman target  /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.
connected to target database: conner (not mounted)
RMAN> restore controlfile from autobackup;
Starting restore at 05-FEB-06
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=11 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/05/2006 20:47:25
RMAN-06495: must explicitly specify DBID with SET DBID command

If there is an automatic backup, we can usually restore the control files directly, and it will be easy to handle after the mount database:

RMAN> restore controlfile from '/opt/oracle/product/9.2.0/dbs/c-3152029224-20051221-00';
Starting restore at 05-FEB-06
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/opt/oracle/oradata/conner/control01.ctl
output filename=/opt/oracle/oradata/conner/control02.ctl
output filename=/opt/oracle/oradata/conner/control03.ctl
Finished restore at 05-FEB-06

4. Read directly from the surviving file

Since DBID exists in the data file and control file, we can read it directly from the file through the PL/SQL program:

SQL> select eygle.get_dbid('/opt/oracle/oradata/conner','user02.dbf') from dual;
EYGLE.GET_DBID('/OPT/ORACLE/OR
------------------------------
3152029224
SQL> select dbid from v$database;
DBID
----------
3152029224

In fact, it is simpler to use BBED.

This method is only for testing interest and is not recommended.