Under the heading "What files need to be backed up under 9i for a cold backup?" posed on 29 December 2005, you had answered that: "To perform a cold backup, I query V$DATAFILE for all of the database datafiles, V$CONTROLFILE for all of the control files and V$LOGFILE for all of the online redo log files. I also back up the parameter files as well as the password file. Optionally, I create a binary backup of the control file as well as dump the contents of the control file to a trace file as you have indicated above. The only files you missed in your question are the online redo log files."
RMAN itself does not back up the online redo log files. And in most cases they do not recommend backing up online redo log files. Can you please explain?
For hot backups, I never back up the online redo logs. However, the question pertained to cold backups. This assumes I am not using RMAN for my cold backups. I assumed the original poster of the question was not using RMAN, otherwise they would have no need to query V$DATAFILE, etc.
I recall answering this question and I was hoping I would get this precise question asked. It shows that someone is paying attention to me out there! While I back up the online redo logs, there are things to be aware of when restoring from that cold backup.
If you are running your database in ARCHIVELOG mode, then never restore the online redo logs from this backup! You will want your current online redo logs (and possibly the archived redo logs) to roll forward any transactions since the time the cold backup was taken. If you overwrite the current online redo logs with the ones in your cold backup, you will only be able to restore up to the point in time of your last archived redo log, which is an incomplete recovery.
If you are running in NOARCHIVELOG mode, then your easiest restore is to overwrite everything . . . the datafiles, the control files and the online redo logs. This way, all files have the same SCN and you do not have any conflicts to resolve. There is no harm in overwriting the online redo logs in this recovery scenario since you will not be able to roll forward anyway.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading