Find DBID from System Data File


Found an interesting way to determine the database id (dbid) from the system data file.

The method comes from location 9758 of the Kindle edition of RMAN Recipes for Oracle Database 12c: A Problem-Solution Approach (2nd Ed.) by Darl Kuhn, Sam R. Alapati, and Arup Nanda. It was published by A-Press in 2013.

The method is as follows:

C:\Users\Douglas.RYDE>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Oct 28 19:58:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Personal Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 'D:\APP\ORACLE\ORADATA\RYDE\SYSTEM01.DBF' block min 1 block max 10;

System altered.

SQL> oradebug tracefile_name
D:\APP\ORACLE\diag\rdbms\ryde\ryde\trace\ryde_ora_535848.trc
SQL>

The contents of the trace file includes the following:

*** 2013-10-28 19:43:49.218
Start dump data block from file D:\APP\ORACLE\ORADATA\RYDE\SYSTEM01.DBF minblk 1 maxblk 10
 V10 STYLE FILE HEADER:
	Compatibility Vsn = 202375168=0xc100000
	Db ID=3524156584=0xd20e5ca8, Db Name='RYDE'
	Activation ID=0=0x0
	Control Seq=32662=0x7f96, File size=101120=0x18b00
	File Number=1, Blksiz=8192, File Type=3 DATA

This says that the dbid of my database is 3524156584. I can confirm this by the following query:

SELECT dbid FROM v$database;

The result is:

      DBID
----------
3524156584

Note: If you try the following command instead:

alter system dump datafile 1 block min 1 block max 10;

The trace file will show the following message:

*** 2013-10-28 19:58:33.046
Start dump data blocks tsn: 0 file#:1 minblk 1 maxblk 10
Block 1 (file header) not dumped:use dump file header command
Block dump from cache:

Leave a comment