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:

• Offline (Cold) Backups
• Online (Hot) Backups
• The Hot Backup Process
• A Hot Backup Scenario
• Hot Backup Insights
• Supporting 24×7 Operations

Offline (Cold) Backups

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.

• All datafiles
• All control files
• All online redo log files (optional)
• The init.ora file (can be recreated manually)

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 24×7 processing, cold backups are not an option, and you must use online (hot) backups.

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';

Hot Backup Process

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.

1. Find the oldest online log sequence number with the following command:
    

   Archive log list

2. In Server Manager, put the tablespace you want to back up in BEGIN backup mode as follows: 
    

   alter tablespace tablespace_name begin backup;

3. Backup all the database files associated with the tablespace using an operating system utility.

• Set the tablespace in END backup mode by using the following command:
   

  alter tablespace tablespace_name end backup;

  Repeat Steps 2, 3 and 4 for each tablespace that you want to back up.

• In Server Manager, execute the ARCHIVE LOG LIST command to get the current log sequence number. This is the last logfile you will keep as part of the hot backup. Next, force a log switch so Oracle will archive the current redo log file. 
   

  Alter system switch logfile;

• Backup all the archived log files, beginning with the log sequence in step 1 to the log sequence in step 5.

• Backup the control file using the following command:
   

  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.

Hot Backup Scenario

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.

Hot Backup Insights

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.

Supporting 24×7 Operations

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:

• When data files are added to the primary database they will automatically be created at the standby site when the logs are applied. The standby database will attempt to create the data file in the same location as on the primary site. If the standby database is unable to create the data file, recovery on the standby database will stop. 
   
• If you rename a data file on the primary site, the change will not happen on the standby database. If you want the names to stay in sync then perform the rename operation at the standby site as well. 
   
• You can add or drop logfile groups at the primary site without affecting the standby database. While it is not required, it is still a good practice to keep the log groups the same on the primary and standby database. The standby database can become invalid if you use the ALTER DATABASE CLEAR UNARCHIVED LOGFILE command or open the primary database with the RESETLOGS option. Likewise, the standby database becomes invalid if you use the CREATE CONTROLFILE command to change the max number of redo log groups/members, change the max number of data files, or change the max number of instances.
   
• Unrecoverable operations do not propagate to the standby database because there is no redo information generated. To get the changes from your primary database to the standby database, re-create the standby database from a new database backup or back up the affected tablespaces and current logs in the primary database, copy them to the standby database, and resume recovery.