Normal system backups, referred to as either Hot or Cold backups, are used to protect from media failure. A Cold backup, that is, one done with the database in a shutdown state, provides a complete copy of the database which can be restored exactly.
A Hot backup, or one taken while the database is active, can only give a read consistent copy, but doesn't handle active transactions. All data in the Oracle or system buffers and all non-committed changes may be lost unless a redo log switch is forced, the resulting archive log and a control file copy taken along with the hot file backup. In order to use the hot backup methodology, the database must be in archivelog mode.
This topic addresses:
An offline cold backup is a physical backup of the database after it has been shutdown using the SHUTDOWN NORMAL command. If the database is shutdown with the IMMEDIATE or ABORT option, it should be restarted in RESTRICT mode and then shutdown with the NORMAL option. An operating system utility is used to perform the backup. For example, in Unix you could use cpio, tar, dd, fbackup or some third party utility. To have a complete cold backup the following files must be backed up.
The location of all database files can be found in the data dictionary views, DBA_DATA_FILES, V$DATAFILE, V$LOGFILE and V$CONTROLFILE. These views can be queried even when the database is mounted and not open.
A cold backup of the database is an image copy of the database at a point in time. The database is consistent and restorable. This image copy can be used to move the database to another computer provided the same operating system is being used. If the database is in ARCHIVELOG mode, the cold backup would be the starting point for a point-in-time recovery. All archive logfiles necessary would be applied to the database once it is restored from the cold backup. Cold backups are useful if your business requirements allow for a shut-down window to backup the database. If your database is very large or you have 24x7 processing, cold backups are not an option, and you must use online (hot) backups.
When databases must remain operational 24 hours a day, 7 days a week, or have become so large that a cold backup would take too long, Oracle provides for online (hot) backups to be made while the database is open and being used. To perform a hot backup, the database must be in ARCHIVELOG mode. Unlike a cold backup, in which the whole database is usually backed up at the same time, tablespaces in a hot backup scenario can be backed up on different schedules. The other major difference between hot and cold backups is that before a tablespace can be backed up, the database must be informed when a backup is starting and when it is complete. This is done by executing two commands:
Alter tablespace tablespace_name begin backup;
Perform Operating System Backup of tablespace_name datafiles
Alter tablespace tablespace_name end backup;
At the conclusion of a hot backup, the redo logs should be forced to switch and all archived redo log files and the control file should also be backed up, in addition to the datafiles The control file cannot be backed up with a backup utility. It must be backed up with the following Oracle command in server manager:
Alter database backup controlfile to 'file_name';
The following example assumes the database is in ARCHIVELOG mode and is open. The following steps show the correct sequence of steps to perform a valid hot backup.
Archive log list
alter tablespace tablespace_name begin backup;
alter tablespace tablespace_name end backup;
Repeat Steps 2, 3 and 4 for each tablespace that you want to back up.
Alter system switch logfile;
alter database backup controlfile to 'file_name';
Note: The control file should always be backed up after any structural change to the database is made.
The following would be a typical backup scenario for a large database that must operate 24 hours a day, 7 days a week, and is too big to backup all at once. The business requirements do not require fault tolerance, but management wants recovery time to be minimized. The database is laid out in 12 tablespaces across 24 disk drives. The tablespaces are all between 6 and 9 gigabytes. The heaviest time for batch programs is on Saturday making it a bad day for performing backups. The following hot backup schedule would support these business requirements.
With this schedule, you have a complete backup of the database once a week, although not all tablespaces are backed up at the same time. With this schedule, the worst case in a recovery would be a media failure on a drive that was going to be backed up that night. In this case, the datafiles on the failed drive would be restored to a new drive, and seven days worth of archive logs applied to the tablespaces affected by the failure.
Once a hot backup is started, the database cannot be shutdown with the NORMAL or IMMEDIATE option. An error message will indicate the database is in backup mode. If the database is aborted or crashes when it is restarted, the database will think it needs to be recovered. If this happens, from Sever Manager use the following command:
alter database datafile 'file_name' end backup;
This will change the file status from backup mode to nobackup mode.
Hot backups with archive logging will support 24 hour operations. It will also give you the option of recovering individual tablespaces while other parts of the database stay online. In addition to the hot backup scenario, Oracle provides a fault tolerant feature that allows you to have a standby database in case the primary database fails.
Insights about using standby databases: