0

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.

  • Did you try to restore with RMAN? If so what was the error you got? – Marco Feb 23 '17 at 7:36
  • I already run command delete backup while faced the disk space issue. Also i am not using recovery catalog. Do I have an option for recovery? – akr Feb 23 '17 at 9:45
  • Then catalog these backups using RMAN then try to restore.docs.oracle.com/cd/B28359_01/backup.111/b28273/… – JSapkota Feb 23 '17 at 10:15
  • Still I'm unable to recover the file. Please see the log added in the question above. – akr Feb 23 '17 at 12:23
  • RMAN>restore database; then if you have archived logs then RMAN>recover database; then SQL>alter database open; if no archived logs then SQL>alter database open resetlogs;. – JSapkota Feb 23 '17 at 12:29
0

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!

  • I tried as you said, but unable to restore (Please see above error in my question). – akr Feb 23 '17 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.