Just to discuss, a question I've seen while studying:
What file or files must be restored if one datafile is lost or removed when the restored files are based on a cold backup?
1) The lost datafile and neccessary archive logs
2) All data files only
3) All data files and control files
4) All data files, control files and online redo logs
That question seemed very misleading for me. If the database is in ARCHIVELOG and the failure happened after the last cold backup the answer is (1). So, it is enough to restore the lost the datafile from the cold backup and recover it.
However, the "right" answer is (4), that makes sense if the database is in NOARCHIVELOG. That way, there is not recovery, just the last cold backup is restored and all the changes made between the backup and the failure are lost.
Very unclear, don't you think? If exams questions are like that (bad-written), it'll be pretty tough.
If your backup is based upon a cold backup, and you need to restore a lost datafile, you need to restore the datafiles and control files (common perception is that redo logs need to be copied for a cold backup, but not necessary).
You can't just restore the one datafile because the SCN's in it will be out of sync with the rest of the database. That eliminates #2.
A backup consists of copying the datafiles, control files, init.ora file and archive log files. This eliminates #4 - you don't backup online redo logs.
You don't "restore" archive log files - you apply them. Picky wording, but it eliminates #1.
If your database was in archivelog mode which the question implies, I would say all the answers are wrong:
#1 wrong, because the archived logs to be restored are older than the backup.
#2 is wrong because you don't have to restore data files that weren't lost.
#3 is wrong because you wouldn't want to restore the control file and lose the current SCN.
#4 is wrong for the same as #3.
If your database is in NOARCHIVELOG mode, then #4 is correct. #3 is also technically correct, but that's not the way they teach it.
#4 would never be correct. Oracle specifically recommends against doing anything with online redo logs with respect to "restoring" them.
Media Failure and Recovery in Noarchivelog Mode
In this case, your only option is to restore a backup of your Oracle files. The files you need are all datafiles, and control files. You only need to restore the password file or parameter files if they are lost or are corrupted.
Media Failure and Recovery in Archivelog Mode
e) Recovery with missing online redo logs
Missing online redo logs means that somehow you have lost your redo logs before they had a chance to archived. This means that crash recovery cannot be performed, so media recovery is required instead. All datafiles will need to be restored and rolled forward until the last available archived log file is applied. This is thus an incomplete recovery, and as such, the recover database command is necessary (i.e. you cannot do a datafile or tablespace recovery).
As always, when an incomplete recovery is performed, you must open the database with resetlogs.
Note: the best way to avoid this kind of a loss, is to mirror your online log files.
Current documentation suggests that when doing a cold backup, you backup the control files, the .dbf files, and your parameter file (init.ora) and not the redo logs. If media failure occurs during the recovery method, a vaild closed database backup must exist in order to recover, otherwise an
incomplete recovery is done.
Well, I read the question again and changed my mind.
It implies that files are restored from the cold backup, so archive logs and online redo logs included in the backup are useless (if the database was cleanly checkpointed and shutdown, what is the correct for a cold backup).
The archive logs and online redo logs that should be applied after the database is restored probably will be in disk. These files were generated AFTER the cold backup (and are not included in that), so they weren't backed up yet.
So, there are the following possibilities:
a) Archivelogs and online redologs after backup available; database in ARCHIVELOG => Restore control files and datafiles and then recover (answer #3)
b) Archivelogs and online redologs after backup unavailable; database in NOARCHIVELOG => Restore control files and datafiles and then open the database without recovery (answer #3)
I enjoy this kind of dialog. It reminds me of trying to argue with my ex-wife, which usually accomplished nothing because she would change the original basis of the argument as we went along, much like changing the interpretation of the Sybex question. The best outcome one could hope for was for both parties to agree to disagree.
Anyway, I will take my exam next monday, not yet sure of what Sybex meant with that question. However, it made me think a lot and review some concepts forgotten somewhere in my brain. I mean, the arguing was pretty really productive.
Just wish me luck (better, wish me I don't need luck at all).