Unable to open database after losing data file

oracleoracle-11grecoveryrman

Last day I've deleted the data files in my database server (Oracle11g in Windows platform) by mistake. Unfortunately, now my database is not opening up. It's showing the following error.

    SQL> startup
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             620758576 bytes
Database Buffers         1946157056 bytes
Redo Buffers                2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10:
'C:\APP\SERVICEADMIN\ORADATA\TESTPROD\TEST_IDX02.DBF'

I'm having RMAN backups for my database, but I've deleted these backup files as the FRA folder size was getting increased to the maximum. However, I've saved these backup files in my local machine. When I tried to restore the DB using RMAN after coping these files to FRA backup set location, I encountered the below error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/23/2017 14:58:35
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

As suggested, I tried adding catalog for the backup, but still unable to recover the file. 

RMAN> CATALOG BACKUPPIECE 'C:\FRA\TRENDZPROD\BACKUPSET\2017_02_23\O1_MF_NNNDF_TA
G20170223T112327_DBWY6SJK_.BKP';

cataloged backup piece
backup piece handle=C:\FRA\TRENDZPROD\BACKUPSET\2017_02_23\O1_MF_NNNDF_TAG201702
23T112327_DBWY6SJK_.BKP RECID=27 STAMP=936726443

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

RMAN> recover database;

Starting recover at 23-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/23/2017 17:48:37
RMAN-06094: datafile 10 must be restored

I am not getting any idea how to restore my database using these files.

Best Answer

You just need to restore or catalog the backups which were stored another location. Follow the following steps.

I assume that you have spfile, controlfile and redo log files in place.

As you said you have copied the RMAN backup in another location, restore that backup to your regular backup location.

Catalog the backup files.

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/backupset/';

Then use RMAN to restore database. 

RMAN> restore database;

Recover the database with available archived logs.

RMAN> recover database;

Then simply try to open the database(use resetlogs clause if you performed incomplete recovery).

SQL> alter database open;

OR

 SQL> alter database open resetlogs;

Done!

Related Question