Oracle Hot Backup

Let's discuss about hot backup this month. I shall also give the scripts to take a complete hot backup.

Excerpts from ORACLE BACKUP AND RECOVERY HANDBOOK (7.3 Edition) by Rama Velpuri - Oracle Press

AN INTERNAL VIEW

The control file reflects the structure of a database at particular point in time. It contains the checkpoint information, names of log files and data files, header information of the files and log sequence number which is very important for recovery purposes. The recovery is done only by applying the log files whose sequence number is greater than log sequence number in the control file.

· Datafile information in control file:
· Names of datafiles and log files with exact path.
· File size.
· Block size.(Oracle block size)
· Whether the datafile is online or offline.
· Whether the datafile was taken offline automatically or not.
· Whether the datafile belongs to the system tablespace or not.
· Entry for each datafile giving a log sequence number when tablespace was taken offline.
· Log file information in control file:
· Name with exact path.
· File size.
· Block size. (O/S block size)
· Log sequence#
· Has the file been archived.
· Information in the datafile header:
· Log sequence number of next log file that could be applied.
· Whether online backup in progress.
· Information in the log file header:
· Log sequence#
· Archival information.

ONLINE BACKUP (HOT BACKUP)

At sites where database must operate 24 hours per day and when it is not feasible to take offline backups, an alternative is provided by ORACLE RDBMS to perform physical backups while the database remains available for both reading and updating. For this kind of backup the database must be in ARCHIVELOG mode. Only data files and current control file need to be backed up. Unlike offline backups, the unit of a online backup is tablespace, and any or all tablespaces can backed up whenever needed. Different datafiles can be backed up at different times.

WHAT HAPPENS DURING HOT BACKUP

Once the ALTER TABLESPACE ts_name BEGIN BACKUP is issued, the status in the datafile header is changed to indicate that the datafile is being backed up. Oracle stops recording the occurrence of checkpoints in the header of the database files. This means that when a database file is restored, it will have knowledge of the most recent checkpoint that occurred BEFORE the backup, not any that occurred during the backup. This way, the system will ask for the appropriate set of redo log files to apply should recovery be needed. Since vital information needed for recovery is recorded in the Redo logs, these REDO LOGS are considered as part of the backup. Hence, while backing up the database in this way the database must be in ARCHIVELOG mode. Status in the datafile header is not reset until END BACKUP is issued. On END BACKUP, the system again begins noting the occurrence of the checkpoints in each file of the database. The checkpoint in the datafile header is changed during the next log switch after END BACKUP is issued. The above information will allow the tablespace to be recovered as if the database had been offline when the backup took place.

Click here for a database hot backup script

WHAT THE SCRIPT DOES