How to obtain the oracle dbid and oracledbid

How to obtain the oracle dbid and oracledbid
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
----------
1363251591






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 c-1363251591-20051221-00
-Rw-r ----- 1 oracle dba 3358720 Jan 21 c-1363251591-20060121-00
-Rw-r ----- 1 oracle dba 3358720 Jan 21 c-1363251591-20060121-01




Here, 1363251591 is DBID. If Flash Recovery Area is used in 10 Gb, this naming rule does not exist.


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,200 2, 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-1363251591-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
------------------------------
1363251591


SQL> select dbid from v $ database;


DBID
----------
1363251591


In fact, it is easier to use BBED.


This method is only caused by test interest and is not recommended.
--Here is the custom function of eygle (I will add it when I take the time)




5. Get the dbid through the dump data file:
SYS @ orcl> alter system dump datafile 'd: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ users01.dbf' block 1000;


The system has been changed.


SYS @ orcl> select tracefile from v $ process where addr in (select paddr from v $ session where sid in (select sid from v $ mystat ));


-- View the trace file: Db ID = 1363251591
TRACEFILE
------------------------------------------------------------------------------
D: \ app \ administrator \ diag \ rdbms \ orcl \ trace \ orcl_ora_6740.trc


Start dump data block from file D: \ APP \ ADMINISTRATOR \ ORADATA \ ORCL \ USERS01.DBF minblk 0 maxblk-1
V10 style file header:
Compatibility Vsn = 186646528 = 0xb200000
Db ID = 1363251591 = 0x51419187, Db Name = 'orcl'
Activation ID = 0 = 0x0
Control Seq = 10579 = 0x2953, File size = 1600 = 0x640
File Number = 4, blksiz= 8192, File Type = 3 DATA

Dump all the blocks in range:

...

-------------------------------

Dylan Presents.

How does one obtain DBID when ORACLE loses control files?

If all the data files are present, you can obtain them by recreating the control file.

How to obtain the month in oracle

Substr ('1970-05-21 ', 6,7)