Oracle HOWTO: how to obtain the dbid of a Database

Reprinted -- http://www.eygle.com/archives/2006/02/oracle_howto_get_oracle_dbid.html
In the process of database recovery, we often need to know the dbid of the Oracle database. There are usually the following methods to obtain the dbid of the database.

1. query v $ database to obtain

Because dbid has records in the control file and data file, you can query the V $ database view if you can mount the database.

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

2. In nomount status

If the database is configured with the automatic control file backup (Oracle9i) with the default name, we can get the 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.

3. Restore from automatic backup

Dbid is required for restoration. It is usually because all control files are lost. An error occurs during restoration.

[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 Automatic Backup exists, we can usually directly restore the control file. After the database is mounted, It is easy:

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 from the surviving file directly

Because dbid exists in the data file and control file, we can directly read 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

This method is only caused by test interest and is not recommended.