Backup Methods and Backup Frequency (SAP Library - Database Administration (Oracle) with SAPDBA)

Backup Methods and Backup Frequency

Backing Up the Complete Database (Production System)

The frequency of image backups of the entire database should depend on the degree of activity in your database system. High activity in the database increases the number of redo log files written between complete backups. This increases the time required for any necessary recovery.

The safety level of your database operations is increased the more often you run backups. If a redo log file is lost, complete recovery of the database after an error is often not possible. Instead, you will only be able to recover up to the gap in the redo log file sequence.

Performing frequent complete backups reduces the number of redo log files which must exist in order to make a complete recovery. The data loss should one of these files be lost can thus be kept to a minimum.

To perform an online backup, the database must be in ARCHIVELOG mode; production operations can then be continued without restrictions, unlike and offline backup.

SAP recommends keeping several generations of complete backups and the corresponding redo log files. This ensures that you can still recover the database, even if the last complete backup is lost.

To enable fast, simple recovery of the database, back up at least the changed tablespaces and the control file after every structure change (new, changed, or deleted tablespaces; new data files). After a reorganization with data files, always back up the affected tablespace if you want to use the recovery functions of SAPDBA. Follow the instructions for the tablespace backup below.

Use the SAP utility BRBACKUP to back up the database and BRARCHIVE to archive the offline redo log files (see Using BRBACKUP and BRARCHIVE).

Backing Up a Tablespace

Backing up tablespaces that are changed frequently can reduce the time required for any necessary recovery. When a more recent backup of an intensively used tablespace is available, fewer redo log entries will have to be processed in order to update the tablespace. If you are able to back up the entire database on a daily basis, tablespace backups are not necessary.

Note that tablespace backups are no replacement for frequent backups of the entire database.

If you only perform tablespace backups for a long period of time, this increases your dependence on the archived redo log files, and therefore the risk of data loss if one of the redo log files is lost.
If tablespace backups are used, the database administrator decides what has to be backed up. The SAP utility BRBACKUP supports the backup operation itself, but does not help you decide which tablespaces to back up (exceptions: operations such as a tablespace extension, or an SAPDBA reorganization; in these situations, SAPDBA recommends that you backup the tablespace immediately).

Backing Up the Control File

Another type of partial backup is to back up the control file. The control file records the physical file structure of the corresponding database. You should therefore back up the control file after every structure change.

Mirrored control files protect you against the loss of a single control file. If data files are damaged, an older control file that mirrors the corresponding structure of the database may be necessary for recovery. For this reason, mirroring the control files is by no means a replacement for backing up the control file after every change in the structure of the database.

When the SAP utility BRBACKUP is used to back up the database files, the control file is always saved along with them. The control file is saved before and after the operation for various administration measures with SAPDBA (for example, tablespace extension, reorganization of a tablespace).

Backing Up a Test System

The data of a test database might not have to be backed up as often, depending on how your test system is used. If you accept the restriction that you will only be able to recover the database from the last offline backup, you can operate the database in NOARCHIVELOG mode. If you do not back up the database at all, you will have to reinstall the database in a recovery situation.