How to recover database without knowing DBID or DB Name

This article is a bit different from other recovery and backup case studies. The solution to this scenario has given by my fellow DBA Sham. Being a DBA we all are must aware of some different conditions that can occur in different conditions.

In this post, we are going to learn about the steps which we use to recover database when we don’t have the knowledge of DBID and DBNAME. As we know what is DBID and DBNAME in Oracle for new DBA we have mentioned about the technical definition of DBID and DBNAME.

DBID stands for database identifier, which is a unique identifier for each Oracle database running. It is found in control files as well as datafile header.

DBNAME stands for the database name.

Let’s start Recover the Database using RMAN Backup without having DBID or DBName

I have RMAN backup set under /u02/bkp directory, but nothing I have about these BACKUP SETS. I am trying to find the way to recover the database from these backup sets.
Let’s simulate the scenario step by step.

[oracle@RAC1 bkp]$ pwd
/u02/bkp
[oracle@RAC1 bkp]$ ls -l
total 1138684
-rwxrwxr-x 1 oracle oinstall 48741888 Dec 22 21:53 o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp
-rwxrwxr-x 1 oracle oinstall 1229312 Dec 22 21:53 o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
-rwxrwxr-x 1 oracle oinstall 9830400 Dec 22 21:53 o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp
-rwxrwxr-x 1 oracle oinstall 1109966848 Dec 22 21:56 o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp

[oracle@RAC1 ~]$ echo $ORACLE_SID
[oracle@RAC1 ~]$ ps -ef | grep smon
oracle 4249 1 0 04:38 ? 00:00:00 ora_smon_orcldb
oracle 13225 5730 0 04:44 pts/1 00:00:00 grep smon

we have already orcldb Instance is running on the Server. This SID of orcldb is useful only to connect to the rman utility.

[oracle@RAC1 ~]$ export ORACLE_SID=orcldb
[oracle@RAC1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 23 04:45:04 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLDB (DBID=2399416466)

RMAN> catalog start with '/u02/bkp' noprompt;
using target database control file instead of recovery catalog
searching for all files that match the pattern /u02/bkp
List of Files Unknown to the Database
=====================================
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
File Name: /u02/bkp/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
File Name: /u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp
cataloging files...
no files cataloged

List of Files Which Where Not Cataloged
=======================================
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp
RMAN-07518: Reason: Foreign database file DBID: 2983310641 Database Name: TESCO
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
RMAN-07518: Reason: Foreign database file DBID: 2983310641 Database Name: TESCO
File Name: /u02/bkp/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
RMAN-07518: Reason: Foreign database file DBID: 2983310641 Database Name: TESCO
File Name: /u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp
RMAN-07518: Reason: Foreign database file DBID: 2983310641 Database Name: TESCO

Finally, I found the DB_NAME and DBID. Database name is TESCO and DBID is 298331064. Now I am going to recover the database using minimum parameters.

$ vi inittesco.ora
db_name=tesco

Now export the ORACLE_SID and nomount the database.

$ export ORACLE_SID=tesco
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Dec 23 04:53:56 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='/home/oracle/inittesco.ora' nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Now we can restore the controlfile from backup.

$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Dec 23 04:58:53 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: TESCO (not mounted)
RMAN> restore controlfile from '/u02/bkp';
Starting restore at 23-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/23/2017 04:59:50
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

As we got the above error of AUTOBACKUP, we need to try restore control file from availabale backup sets one by one.

RMAN> restore controlfile from '/u02/bkp/o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp';
Starting restore at 23-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/23/2017 05:01:21
ORA-19870: error while restoring backup piece /u02/bkp/o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp
ORA-19626: backup set type is archived log - can not be processed by this conversation

RMAN> restore controlfile from '/u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp';
Starting restore at 23-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/23/2017 05:03:00
ORA-19870: error while restoring backup piece /u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
ORA-19626: backup set type is archived log - can not be processed by this conversation

RMAN> restore controlfile from '/u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp';
Starting restore at 23-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u02/app/oracle/product/11.2.0/dbhome_2/dbs/cntrltesco.dbf
Finished restore at 23-DEC-17

Successfully we have restored controlfile from the backup. Control file has been restored $ORACLE_HOME/dbs, Later we will relocate this control file. Now controlfile is available so that we can MOUNT the Database Instance.

RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1

RMAN does NOT aware about these backup sets so that we need to register these RMAN backup files to the controlfile using following RMAN CATALOG command.

RMAN> catalog start with '/u02/bkp';
searching for all files that match the pattern /u02/bkp
List of Files Unknown to the Database
=====================================
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
File Name: /u02/bkp/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
File Name: /u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T210946_f3sphcf1_.bkp
File Name: /u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
File Name: /u02/bkp/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
File Name: /u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp

Now we can restore the database.

RMAN> restore database;
Starting restore at 23-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/tesco/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/tesco/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/tesco/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/tesco/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/tesco/example01.dbf
channel ORA_DISK_1: reading from backup piece
/u02/app/oracle/flash_recovery_area/TESCO/backupset/2017_12_22/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /u02/bkp/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
ORA-19504: failed to create file "/u02/app/oracle/oradata/tesco/system01.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/23/2017 05:09:07
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Here RMAN is trying to restore datafiles but path is NOT available on the TARGET Server.Create appropriate directory on the Target Server.

[oracle@RAC1 ~] $ mkdir -p /u02/app/oracle/oradata/tesco
[oracle@RAC1 ~]$ ls -l /u02/app/oracle/oradata/
total 8
drwxr-x--- 5 oracle oinstall 4096 Dec 23 04:35 ORCLDB
drwxr-xr-x 2 oracle oinstall 4096 Dec 23 05:10 tesco

Now I am trying to execute same command. Let’s see ..

RMAN> restore database;
Starting restore at 23-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/app/oracle/oradata/tesco/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/app/oracle/oradata/tesco/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/tesco/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/app/oracle/oradata/tesco/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/tesco/example01.dbf
channel ORA_DISK_1: reading from backup piece
/u02/app/oracle/flash_recovery_area/TESCO/backupset/2017_12_22/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
channel ORA_DISK_1: errors found reading piece
handle=/u02/app/oracle/flash_recovery_area/TESCO/backupset/2017_12_22/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp
channel ORA_DISK_1: failover to piece handle=/u02/bkp/o1_mf_nnndf_TAG20171222T210951_f3sphjsf_.bkp tag=TAG20171222T210951
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 23-DEC-17

Wow, something looks good! Now easily we can do recovery!

RMAN> recover database;
Starting recover at 23-DEC-17
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp
channel ORA_DISK_1: piece handle=/u02/bkp/o1_mf_annnn_TAG20171222T211344_f3sppr6m_.bkp tag=TAG20171222T211344
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/app/oracle/product/11.2.0/dbhome_2/dbs/arch1_5_963435380.dbf thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2017 05:17:27
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 992246

Nothing we can ignore above warning because is asking to supply SEQUENCE 6! In our case we have recovered up to 5 th SEQUENCE. Now we can open the database in resetlog.

RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
SQL> select status from v$instance;
STATUS
------------
OPEN

SQL> select name from v$database;
NAME
---------
TESCO

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/app/oracle/product/11.2.0/dbhome_2/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/tesco/redo03.log
/u02/app/oracle/oradata/tesco/redo02.log
/u02/app/oracle/oradata/tesco/redo01.log

SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/tesco/system01.dbf
/u02/app/oracle/oradata/tesco/sysaux01.dbf
/u02/app/oracle/oradata/tesco/undotbs01.dbf
/u02/app/oracle/oradata/tesco/users01.dbf
/u02/app/oracle/oradata/tesco/example01.dbf

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/product/11.2.0/dbhome_2/dbs/cntrltesco.dbf

SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

Now we can restrore the spfile from backup

RMAN> restore spfile;
Starting restore at 23-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=/u02/app/oracle/product/11.2.0/dbhome_2/dbs/spfiletesco.ora
channel ORA_DISK_1: reading from backup piece /u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp
channel ORA_DISK_1: piece handle=/u02/bkp/o1_mf_ncsnf_TAG20171222T210951_f3spppc3_.bkp tag=TAG20171222T210951
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 23-DEC-17

Now we can read the spfile to make directory structure

SQL> create pfile='/tmp/inittesco.ora' from spfile;
File created.
$ ls -l /tmp/init*
-rw-r--r-- 1 oracle oinstall 924 Dec 24 04:15 /tmp/inittesco.ora
$ vi /inittesco.ora
tesco.__db_cache_size=872415232
tesco.__java_pool_size=16777216
tesco.__large_pool_size=16777216
tesco.__pga_aggregate_target=419430400
tesco.__sga_target=1241513984
tesco.__shared_io_pool_size=0
tesco.__shared_pool_size=301989888
tesco.__streams_pool_size=16777216
*.audit_file_dest='/u02/app/oracle/admin/tesco/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/tesco/control01.ctl','/u02/app/oracle/flash_recovery_area/tesco/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='tesco'
*.db_recovery_file_dest='/u02/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u02/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=tescoXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=413138944
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1241513984
*.undo_tablespace='UNDOTBS1'

Create required directory structure

$ mkdir –p /u02/app/oracle/admin/tesco/adump
$ mkdir –p /u02/app/oracle/flash_recovery_area/tesco

Shutdown the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Existing controlfile in $ORACLE_HOME/dbs

$ cd $ORACLE_HOME/dbs
$ ls -l cntr*
-rw-r----- 1 oracle oinstall 9748480 Dec 24 23:12 cntrltesco.dbf

Restoring the controlfile as per SPFILE

[oracle@RAC1 dbs]$ cp cntrltesco.dbf /u02/app/oracle/oradata/tesco/control01.ctl
[oracle@RAC1 dbs]$ cp cntrltesco.dbf /u02/app/oracle/flash_recovery_area/tesco/control02.ctl

Simply startup the Database Instance!

SQL> startup
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u02/app/oracle/product/11.2.0
/dbhome_2/dbs/spfiletesco.ora

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/tesco/control01. ctl
/u02/app/oracle/flash_recovery_area/tesco/control02.ctl

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp