Oracle Backup and Recovery Interview Questions: 20 Scenarios

Published Aug 2026 · 9 min read

Backup questions are where interviews stop being theoretical. Anyone can define a full backup. What a panel wants to know is what you do at 2am when the controlfile is gone, the archivelog you need was deleted by a cleanup script, and someone senior is asking for an ETA.

These are 20 recovery scenarios that come up repeatedly, in the order an interview usually escalates: routine, then awkward, then genuinely bad. Each answer gives the short version you should lead with, then the detail that shows you have actually done it. Commands were verified against Oracle 19c with RMAN.

The questions that open the round

1. What is the difference between a full backup and a level 0 backup?

Both copy every used block. The difference is what can follow them: a level 0 can be the parent of an incremental, a plain full backup cannot. So if you ever intend to run incrementals, your baseline must be level 0. This trips up more candidates than it should, and it is a fast way for a panel to see whether you have designed a strategy or just run commands.

2. What does the control file actually contain, and why does that matter for recovery?

Database name and DBID, datafile and redo log locations, checkpoint information, the archive log history, and — unless you use a catalog — the entire RMAN repository. It matters because recovery is driven from it. Lose every copy and RMAN no longer knows what backups exist or how far they roll forward, which is why the controlfile autobackup is not optional in a serious environment.

3. What is the difference between complete and incomplete recovery?

Complete recovery applies all available redo and loses nothing. Incomplete recovery deliberately stops at a point — a time, an SCN or a log sequence — and everything after that point is discarded, which is why it requires opening with RESETLOGS. Say the consequence out loud: incomplete recovery is a data-loss event, so it is a business decision, not just a technical one.

4. What does OPEN RESETLOGS do, and what must you do immediately after?

It resets the log sequence to 1 and starts a new incarnation of the database. Immediately afterwards, take a fresh full backup. Older backups belong to the previous incarnation, and while RMAN can work across incarnations, you do not want to be discovering that under pressure. Mentioning the new backup unprompted is a strong signal.

5. How do you verify a backup is actually usable?

By testing it, not by reading a success message. RMAN can validate backups without restoring them, and for anything important you restore to a separate host on a schedule. The line worth saying: an untested backup is not a backup, it is a hope.

-- prove the backup can be restored, without touching the live database
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

-- check for physical and logical corruption in the backup itself
VALIDATE CHECK LOGICAL BACKUPSET;

-- what does RMAN think it has?
LIST BACKUP SUMMARY;
REPORT NEED BACKUP;
REPORT UNRECOVERABLE;

Single-file failures

6. A non-system datafile is lost while the database is open. What do you do?

You do not need downtime for the whole database. Take that datafile offline, restore and recover just it, then bring it online. The rest of the database keeps serving users — which is the point worth making, because it shows you think about availability, not only about restoring.

-- open database, one datafile lost
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' OFFLINE;

-- in RMAN
RESTORE DATAFILE 4;
RECOVER DATAFILE 4;

ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' ONLINE;

7. What if the lost datafile belongs to SYSTEM or UNDO?

Then the database cannot stay open. SYSTEM and UNDO are not optional for a running instance, so you shut down, mount, restore and recover, then open. If a panel asks why the difference exists, the answer is that Oracle cannot maintain a consistent open database without the data dictionary or the ability to roll back — so it will not let you try.

8. You lost a member of a multiplexed redo log group. How bad is it?

Not bad, which is the point of multiplexing. The instance keeps running on the surviving member and writes a message to the alert log. You drop the failed member and add a replacement. The follow-up question is usually about a whole group, so have that ready too.

9. You lost an entire online redo log group. Now what?

It depends on the group's state. If it is inactive and already archived, clear it. If it is the current group, you are looking at losing the redo it held, which means incomplete recovery and RESETLOGS. Be honest about that distinction rather than giving one blanket answer — the distinction is the answer.

-- which group is which?
SELECT group#, thread#, sequence#, archived, status FROM v$log;
SELECT group#, member, status FROM v$logfile;

-- inactive and archived: safe to clear
ALTER DATABASE CLEAR LOGFILE GROUP 3;

-- inactive but NOT yet archived
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
-- (take a full backup straight after: the archive chain now has a hole)

10. A single block is corrupt. Do you restore the whole datafile?

No. Block media recovery repairs just that block while the datafile stays online, so a corruption in one block costs you seconds instead of an outage. This is a favourite question because the wrong answer — restore the file — reveals someone who learned backup from a textbook rather than an incident.

-- find corruption
SELECT * FROM v$database_block_corruption;

-- repair in place, database stays open
RECOVER CORRUPTION LIST;
-- or a specific block
BLOCKRECOVER DATAFILE 5 BLOCK 137;

The genuinely bad ones

11. You have lost every copy of the control file. Walk me through it.

Restore it from autobackup. You need the DBID if you are not connected to a catalog, which is why every runbook should record it. Start the instance nomount, restore the controlfile from autobackup, mount, then recover the database and open with RESETLOGS — the restored controlfile is older than the datafiles, so recovery is mandatory.

RMAN> SET DBID 1234567890;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

12. The archivelog you need for recovery was deleted. What are your options?

First, check whether it only went missing from disk — it may still be on tape, on the standby, or in the flash recovery area, and RMAN's catalog view will tell you what it believes exists. If the redo is genuinely gone, recovery stops at the end of the last available log and you take incomplete recovery up to that point. Then say the important part: you go and fix whatever deleted it, because a cleanup script that removes unarchived redo will do it again.

-- what does RMAN think exists vs what is really on disk?
RMAN> CROSSCHECK ARCHIVELOG ALL;
RMAN> LIST EXPIRED ARCHIVELOG ALL;
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

-- recover as far as the redo allows
RMAN> RECOVER DATABASE UNTIL SEQUENCE 4211 THREAD 1;
RMAN> ALTER DATABASE OPEN RESETLOGS;

13. Someone dropped a production table an hour ago. What is the fastest route back?

Check the recycle bin first — if the table is still there, FLASHBACK TABLE takes seconds. If it was purged or the drop was a truncate, consider Flashback Query or Flashback Table to an SCN if undo retention still covers an hour. Only when none of those apply do you reach for RMAN point-in-time recovery of a subset. Ordering these cheapest-first is what the question is really testing.

14. What is a tablespace point-in-time recovery, and when would you use it?

It recovers one tablespace to an earlier time while the rest of the database stays current — useful when a single application's data was corrupted logically and you cannot rewind the whole database. The caveat to mention: it requires that the tablespace be self-contained, so objects with dependencies outside it will block you.

15. The whole server is gone. You have backups on remote storage. Talk me through the restore.

Build or obtain a host with matching Oracle software, restore the spfile and controlfile from autobackup, mount, restore the database, recover to the latest available redo, and open with RESETLOGS. Then verify: check the alert log, run a validate, confirm applications connect. The detail that impresses is mentioning that you restore the software version and patch level to match, because a datafile restored under a different patch level can refuse to open.

Strategy questions behind the scenarios

16. How do you decide a backup schedule?

Work backwards from RPO and RTO, which come from the business, not from you. RPO drives how often you back up and how frequently you archive; RTO drives the restore method — incrementals and image copies restore faster than full backupsets from tape. Saying "the business gives me RPO and RTO and I design to meet them" is a stronger answer than any particular schedule.

17. What is the difference between the retention policy settings?

A redundancy policy keeps a number of copies. A recovery window keeps whatever is needed to recover to any point within N days. Recovery window is usually the right choice because it maps directly to a business promise, whereas redundancy only counts files.

-- design to the promise, not to a file count
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> SHOW ALL;

-- what is now obsolete under that policy?
RMAN> REPORT OBSOLETE;
RMAN> DELETE OBSOLETE;

18. What does the fast recovery area do, and how does it fail?

It is a managed location for backups, archivelogs and flashback logs, with Oracle handling space reclamation against your retention policy. It fails in one memorable way: when it fills and cannot reclaim, archiving stops and the database hangs. Every DBA who has run one has seen it, so the panel is checking whether you have.

19. How does a Data Guard standby change your backup strategy?

It gives you a second place to take backups, offloading the work from primary, and it provides a fast route back from many failures. What it does not do is replace backups — a logical error such as a dropped table replicates to the standby quite happily. Being clear that a standby is availability, not history, is the point of this question.

20. What is the most important thing about a recovery plan?

That it has been rehearsed. Say that you run restore tests on a schedule, that the runbook records the DBID, the backup locations and the credentials, and that someone other than you has followed it successfully. Interviewers ask this last because the answer reveals whether you have lived through a real recovery or only planned for one.

How to answer these well

A pattern runs through every scenario above: establish what is actually lost, choose the cheapest recovery that meets the requirement, state the data-loss implication before you act, and verify afterwards. Candidates who jump straight to a command lose marks even when the command is right, because recovery is a decision process and the command is only the last step.

The single best preparation is to break a database on purpose. Delete a datafile while it is open. Remove every controlfile. Corrupt a block. Recover from each one, and note where you hesitated — that hesitation is exactly what an interview will find.

If you want to run those drills against a real database with someone checking your work, our live Oracle DBA training covers RMAN, Data Guard and full disaster-recovery labs. You can see where past students ended up in our Oracle DBA success stories, and if you are preparing broadly, the companion piece on Oracle RAC interview questions covers the clustering half of the same interview. Oracle's Backup and Recovery User's Guide is the reference to keep open while you practise.