# Archivelog file deleted, resulting in 'ORA-01113: file 1 needs media recovery'

(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

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.

• So you created a backup, then deleted it, and now expect it to work. Post the full output of all steps and the files you still have. Depending on that, your database may be recovered, or it may be as good as gone. As a last resort, your database can be still opened in an unsupported way, for salvaging data. – Balazs Papp Jan 19 '18 at 22:00
• Clarification: I did a full backup, did an archivelog only backup, did another archivelog only backup, deleted only the second set of archiveonly files. All I need is to get back to the state at which I did a full backup. – aioobe Jan 19 '18 at 22:07
• You can try CROSSCHECK ARCHIVELOG ALL command + DELETE EXPIRED ARCHIVELOG ALL;. And then try to add lost archivelogs and/or backups using CATALOG START WITH '/directory_with_archivelogs/' – Mindaugas Riauba Jan 22 '18 at 10:50
• did you resolve the issue then? – Jack Douglas Jan 22 '18 at 17:27
• I unfortunately ran out of time and had to reinstall the database to get business done. From the sound of it, EdStevens last comment seemed spot on. Before I read that comment, I thought that the second archive logs were a continuation of the first archive logs. By viewing them as two separate sequences (and selecting the existing one with SET SCN) everything kind of fell into place. I wish I had time to reproduce the issue and see if it indeed works, but unfortunately I don't. – aioobe Jan 23 '18 at 7:43

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.

• Ok, so here's exactly what I did: I did a full RMAN backup, ran an archivelog only backup, changed the output directory of my archivelog backup script to a fresh directory, ran the archivelog only backup again, deleted the files in that directory. It feels like I should be able to get to a consistent state since I still have one set of archivelog files. Also, I don't mind resetting to the state where I did the full backup, I just want to do ALTER DATABASE OPEN without getting ORA-01113: file 1 needs media recovery. – aioobe Jan 19 '18 at 22:03
• sounds like you just need the CROSSCHECK then? – Jack Douglas Jan 19 '18 at 22:08
• Right. I ran CROSSCHECK BACKUP;, the command found the missing file and says EXPIRED. I did DELETE EXPIRED BACKUP; and then RUN { RESTORE DATABASE; RECOVER DATABASE; } but that results in this: pastebin.com/HrjhktnN – aioobe Jan 19 '18 at 22:14
• @aioobe That is normal and expected in case of an incomplete recovery. Go ahaed and open the database with alter database open resetlogs;. – Balazs Papp Jan 19 '18 at 22:24
• So you actually had two (sequential) sets of archivelog backups. As I understand, it was the second set you deleted. If that is the case, you can still recover into the first set, the one immediately following the database backup. You need to do a LIST BACKUP and look for an SCN number associated with the archivelogs you do have, then before starting your RESTORE/RECOVER sequence, do a SET SCN to that value. It should be able to recover to that SCN and not try to go beyond. Then open with RESETLOGS. – EdStevens Jan 20 '18 at 14:11