A number of books and manuals suggest a backup schedule for Oracle databases in archivelog mode something like, daily hot (online) backups and weekly cold backups. What is strange about this recommendation is that the weekly cold backups serve no apparent purpose, other than to needlessly remove the instance or instances from service for some period of time. Shutting the database down for a cold backup also has the side effect of requiring the buffer cache be reloaded all over again after startup, which degrades performance while all reads are initially physical for a period of time.
Hot backups, used exclusively as a backup method, provide protection that is equal in effectiveness to cold backups. No additional protection can be gained by adding periodic cold backups to a backup regime. In an environment requiring a high degree of availability from the Oracle instance, there is only one scenario in which a cold backup could be considered appropriate. In the event that the database is opened with the resetlogs option, such as during a major (7.x®8.x) version upgrade, or after incomplete media recovery, new logs generated after the database is open cannot be applied to a previous backup. If there is no external way that new transactions could be re-created in the event of a failure, a cold backup may be taken prior to making the database available for general service. This provides a backup to which the new archived redologs may be applied in case a recovery is required very soon after the resetlogs. Alternatively the DBA could begin an online backup as soon as the database is opened, allowing users to perform transactions while the backup is taken, hoping that no media failures occur before the initial backup completes. In such a case, the database is briefly prone to an unrecoverable media failure, in which a backup prior to the resetlogs would have to be restored, and rolled forward only up to the time of the resetlogs. As soon as the initial online backup completes, however, the database is in the clear, and all subsequent transactions can be applied to the initial backup. No incomplete point-in-time recovery between the resetlogs and completion of the initial backup will be possible.
In the unusual event that recovery past a resetlogs is necessary, Oracle Support can guide users through a procedure for doing so that is not generally supported but that works fine.
One concern often voiced in support of periodic cold backup is the possibility of corruption in the archived redologs necessary to recover an online backup. This valid concern may be mitigated in several ways:
- Maintain a site repository for redundant storage of archived redologs from several instances
- Reduce backup time using parallelism and fast media. This decreases the statistical probability that any redo entry necessary to bring a database consistent will be corrupt
- Use disk mirroring in addition to Oracle log multiplexing
- Monitor the O/S system logs for I/O-related messages
- Apply the logs to a standby to prove their applicability
Understand that the likelihood of encountering redo corruption in the relatively small set of logs that are necessary to bring an online backup consistent, and that such complications may be overcome in many circumstances using undocumented methods under the guidance of Oracle Support.