How to Find DBID

  • by

In some situations, you may have to know DBID of the database to let backup catalog know the subsequent steps are for specific database in RMAN.

In this post, I will show you how to find DBID when:

  1. The database is open.
  2. The database is close.

A. When the Database is Open (or Mount)

When the database is up and online, we can get both the database name and identifier. Suppose we have mounted the database. We can discover DBID by SQL*Plus or RMAN.

1. SQLPLUS

We retrieve DB_NAME and DBID by querying V$DATABASE.

SQL> conn / as sysdba
Connected.
SQL> select name, dbid from v$database;

NAME            DBID
--------- ----------
ORCLCDB   3411734329

2. RMAN

Or we can connect to the database through RMAN.

[oracle@test ~]$ rman target /
...
connected to target database: ORCLCDB (DBID=3411734329, not open)

RMAN>

Also, we can get both the database name and identifier in RMAN.

B. When the Database is Close

Somehow, you can't startup the database at this moment, not to say a NOMOUNT state. In such case, it's really not easy to find DBID. The likely solution is to inspect the trace and audit files and hope for a chance to find DBID.

1. Trace and Audit files

We use grep to list only the last 10 occurrences of DBID of trace files.

[oracle@test ~]$ grep -ri "DBID" $ORACLE_BASE/diag/rdbms/$DB_NAME/$ORACLE_SID/trace | tail

In the above command, please replace $DB_NAME with yours in lowercase.

Also, we can look for only the most recent 10 occurrences of DBID of audit files.

[oracle@test ~]$ grep -ri "DBID" $ORACLE_BASE/admin/$ORACLE_SID/adump | tail

If you still find nothing about DBID, OK, let's continue.

2. Force Nomount

When your database is damaged badly, you can't startup a database normally, you can only forcibly startup a NOMOUNT instance. Which is a dummy instance that we can use it later.

[oracle@test ~]$ rman target /
...
connected to target database (not started)

RMAN> startup force nomount;

Please note that, if there's any error thrown by the above command, they can be ignored. If there's no error, it means that Oracle has already found a proper SPFILE to start the instance.

Since the first block of any data file usually contains the information of the database, so we use the dummy instance to dump the first block of a data file to trace file.

[oracle@test ~]$ sqlplus / as sysdba
...
SQL> alter system dump datafile '/u01/app/oracle/oradata/ORCL/system01.dbf' block 1;

System altered.

Please note that, any data file can be a candidate of this dump.

Then we check the very last trace file of your database and search for "DB ID=" under $ORACLE_BASE.

[oracle@test ~]$ grep -ri "DB ID=" $ORACLE_BASE/diag/rdbms
/u01/app/oracle/diag/rdbms/dummy/ORCLCDB/trace/ORCLCDB_ora_1729.trc:    Db ID=3411734329=0xcb5aef39, Db Name='ORCLCDB'

We found it.

Leave a Reply

Your email address will not be published. Required fields are marked *