Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How would you answer this as an interview question?

The database crashed and you lost a drive containing one of your control files and another drive containing an entire redo log group. You have a level 0 backup from yesterday morning, a differential backup from yesterday evening, and a cumulative backup from this morning. In addition three days of redo logs are on disk. You backed up the control file to trace just an hour before the crash. How do you recover the database?

share|improve this question
up vote 2 down vote accepted

The very first thing I'd try is copying the surviving control file then restarting the DB - the loss of the controlfile is what caused the DB to crash, and if the redo log group happened to be INACTIVE at the time, then I'm pretty much OK. I'd find this out from the alert log. So long as there is a disk in the right place, I can issue CLEAR LOGFILE and they will just be recreated.

If not then we have a problem - the SCN in the controlfile is ahead of the DBFs and we've no way to get there. We have no choice but to perform a point-in-time-recovery, and we have to discard those DBFs as well. In RMAN, restore database (assuming controlfile autobackup), recover until the highest SCN in the archived redo logs, then OPEN RESETLOGS. Since I have the cumulative incremental, I don't need the differential incremental (I'm not sure why you'd mix the two types? Also why if we had several disks for redo why we'd put an entire group on one disk rather than multiplexing all groups).

(There is probably a much better answer involving flashback, but I have to confess that I have only used explicitly created flash restore points so far, never run through a DR scenario).

share|improve this answer
Mixing differential and cumulative backups allows a balancing of backup time and recovery time. You are correct, in this scenario the differential was just there to confuse. – Leigh Riffel Apr 22 '11 at 19:03
You wouldn't/shouldn't arrange the redo logs like that and your mention of that showed the understanding the question was meant to elicit. – Leigh Riffel Apr 22 '11 at 19:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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