Archivelog file deleted, resulting in ‘ORA-01113: file 1 needs media recovery’

backupdisaster recoveryoraclerecoveryrman

(This is not a production database. I'm practicing in a staging enviroment.)

I'm trying to set up RMAN for backup/recovery.

I did a full backup, which seems to be in a good state.

I then did an archivelog backup, which went well. And then, (here's where I f***ed up), I…

  1. Created a new tmp directory.
  2. Ran an RMAN archivelog only backup with the tmp directory as destination
  3. Changed my mind and deleted that tmp directory.

I now realize that RMAN keeps track of paths to previously written backups, so I can't just go around and delete these files!

Now, after I do

RUN {
    RESTORE DATABASE;
    RECOVER DATABASE;
}

I get

ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/19/2018 20:21:01
RMAN-20506: no backup of archived log found
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of archived log for thread 1 with sequence 15 and starting SCN of 1361136 found to restore

And when I try to open the database, it looks like follows:

2018-01-19 20:23:04 SYS@... AS SYSDBA> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/path/to/a/file.dbf'

I've tried the solution posted here: ORA-01113: file # needs media recovery ORA-01110

But I can't get that to work. (I run into ORA-00308: cannot open archived log.)

Update: I tried CROSSCHECK BACKUP; followed by DELETE EXPIRED BACKUP; but this unfortunately results in this.

Best Answer

Ran an RMAN archivelog backup to a new directory
Changed my mind and deleted that directory.

Assuming you still have the archivelog files and they were not deleted when they were backed up, you just need to tell RMAN that those files aren't coming back:

Use the CROSSCHECK command to synchronize the physical reality of backups and copies with their logical records in the RMAN repository

If, on the other hand, the original archivelog files were deleted along with their backups, you do not have a full recovery set and it is a good thing you said "This is not a production database. I'm practising in a staging environment". A full backup run online is not a consistent backup — you need the archivelog files containing log records from just before the backup starts until just after it finishes or there is no way to restore the database consistently.

Related Question