Restore a database with RMAN backup after dropping the database

After taking a full rman backup of the database we drop the database as follows

rman target / catalog rcat/@alpha
RMAN> backup database plus archivelog;

Before dropping the database please note the DBID of the database, as this will be required to restore the dropped database.

SELECT DBID FROM V$DATABASE; 
  
DBID
----
2901288553

export ORACLE_SID=alpha
SQL> sqlplus sys as sysdba
Enter password : ********

SQL> shutdown immediate;

SQL> startup mount exclusive restrict
ORACLE instance started

ORACLE instance started.

Total System Global Area 2717907456 bytes
Fixed Size		    9689600 bytes
Variable Size		  620756992 bytes
Database Buffers	 2080374784 bytes
Redo Buffers		    7086080 bytes
Database mounted.

SQL> drop database;
  
Database dropped.

Below are the steps to restore the database after it has been dropped.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 20 09:57:25 2014

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

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/app/oracle/product/21.0.0/dbhome_1/dbs/init.ora’ force;
Total System Global Area 2717907456 bytes
Fixed Size		    9689600 bytes
Variable Size		  620756992 bytes
Database Buffers	 2080374784 bytes
Redo Buffers		    7086080 bytes

SQL> exit;

Now connect to RMAN to recover the spfile and the control files.

$ rman target / nocatalog  
RMAN> set dbid=2901277553

If we try to restore the spfile from the backup sets we may get the following errors

RMAN> restore spfile from '/home/oracle/backup/BackupALPHA_DB_043005bc_4_1_1_%S_%P';

Starting restore at 16-JUL-24
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/BackupALPHA_DB_043005bc_4_1_1_%S_%P
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/16/2024 09:11:47
ORA-19687: SPFILE not found in backup set

RMAN> restore spfile from '/home/oracle/backup/BackupALPHA_DB_053005cc_5_1_1_%S_%P';

Starting restore at 16-JUL-24
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/BackupALPHA_DB_053005cc_5_1_1_%S_%P
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/16/2024 09:11:58
ORA-19870: error while restoring backup piece /home/oracle/backup/BackupALPHA_DB_053005cc_5_1_1_%S_%P
ORA-19626: backup set type is archived log - can not be processed by this conversation

Restore spfile from autobackup; 

IF restore is not possible from the backup sets, we can restore the spfile from the autobackup.

RMAN> restore spfile from autobackup;
Starting restore at 16-JUL-24
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240716
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240715
channel ORA_DISK_1: AUTOBACKUP found: c-2901288553-20240715-00
channel ORA_DISK_1: restoring spfile from AUTOBACKUP c-2901288553-20240715-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 16-JUL-24

Similarly when trying to restore the control file from any of the backup sets, we may get the below error.

RMAN> restore controlfile from '/home/oracle/backup/BackupALPHA_DB_043005bc_4_1_1_%S_%P';

Starting restore at 16-JUL-24
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 07/16/2024 09:45:33
ORA-19697: standby control file not found in backup set

Restore the controlfile from autobackup; We can restore the controlfile from the autobackup

RMAN> restore controlfile from autobackup;

Starting restore at 16-JUL-24
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240716
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240715
channel ORA_DISK_1: AUTOBACKUP found: c-2901288553-20240715-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-2901288553-20240715-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ALPHA/control01.ctl
output file name=/u01/app/oracle/oradata/ALPHA/control02.ctl
Finished restore at 16-JUL-24  

When restoring the backups to another server please make sure the copy the AUTOBACKUP folder files also to the target server. 

You can find the location of the autobackup file from cmd prompt as follows

$ find . -name c-2901288553-20240715-00
  
It will generally be located in the following directory path.
  /u01/app/oracle/homes/OraDB21Home5/dbs

After recovering the spfile and the control file we can now start the database in mount mode.

SQL> startup mount
or
SQL> alter database mount;
ORACLE instance started.

Total System Global Area 2717907456 bytes
Fixed Size		    9689600 bytes
Variable Size		  620756992 bytes
Database Buffers	 2080374784 bytes
Redo Buffers		    7086080 bytes
Database mounted.
SQL> exit  

As a last step we connect to rman and resotre the database. After successful restore we open the datbase with resetlogs command.

$ rman target / nocatalog  
  
RMAN> restore database;
Starting restore at 16-JUL-24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=391 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 00009 to /u01/app/oracle/oradata/ALPHA/alphapdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/ALPHA/alphapdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ALPHA/alphapdb1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ALPHA/alphapdb1/users01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/RMANDBALPHA/rmanalpha_catalog.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/BackupALPHA_DB_023004v4_2_1_1_%S_%P
channel ORA_DISK_1: piece handle=/home/oracle/backup/BackupALPHA_DB_023004v4_2_1_1_%S_%P tag=TAG20240715T170230
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:17
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 /u01/app/oracle/oradata/ALPHA/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ALPHA/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ALPHA/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ALPHA/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/BackupALPHA_DB_0330058n_3_1_1_%S_%P
channel ORA_DISK_1: piece handle=/home/oracle/backup/BackupALPHA_DB_0330058n_3_1_1_%S_%P tag=TAG20240715T170230
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
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 00005 to /u01/app/oracle/oradata/ALPHA/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/ALPHA/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ALPHA/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/BackupALPHA_DB_043005bc_4_1_1_%S_%P
channel ORA_DISK_1: piece handle=/home/oracle/backup/BackupALPHA_DB_043005bc_4_1_1_%S_%P tag=TAG20240715T170230
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 16-JUL-24

$ sqlplus sys@alpha as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Tue Jul 16 10:44:31 2024
Version 21.3.0.0.0

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

SQL> alter database open resetlogs;

Database altered.
  
SQL> alter pluggable database all open;
  
Pluggable database altered.

SQL> exit

References 

https://rajeevjhaoracle.wordpress.com/2013/07/09/step-by-step-rman-recovery-catalog-creation-and-configuration-windows/ 

https://somireddy.wordpress.com/2013/07/16/rman-cold-backup-and-restore-method-1/

Thought for the day

Honor the Lord with your substance and with the first fruits of all your produce; 

Then your barns will be filled with plenty, and your vats will be bursting with wine.

Proverbs 3:9-10