What Needs Backing Up? (SAP Library - SAP Database Guide: Oracle (BC-DB-ORA-DBA))

What Needs Backing Up?

Apart from deciding whether to perform an online or offline, a complete or incremental backup of your Oracle database, you need to decide what to back up. The following graphic summarizes the different items that you need to consider in your backup approach:

This graphic is explained in the accompanying text

For more information on the tools, see:

BRBACKUP

BRARCHIVE

BRRESTORE

The rest of this section discusses what you need to back up from a logical viewpoint.

Backing Up the Complete Database

Consider the following when you decide the frequency of complete database backups:

The frequency of complete database backups should depend on the degree of activity in your database. High database activity increases the number of redo log files written between complete backups, which increases the time required for any necessary recovery.
Performing frequent complete backups reduces the number of redo log files that must exist in order to make a complete recovery. This reduces the data loss if one of these files is lost.

For more information, see Backup Cycles.

If a redo log file is lost, you can often not completely recover the database after an error, even if you have a complete database backup. Instead, you can only recover up to the gap in the redo log file sequence.

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 (that is, new, changed, or deleted tablespaces, or new datafiles). After a reorganization with datafiles, always back up the affected tablespace if you want to use the SAPDBA recovery functions. Follow the instructions for the tablespace backup below.

Use BRBACKUP to back up the database and BRARCHIVE to archive the offline redo log files. See Common Features of 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 have to be processed in order to update the tablespace. If you can back up the entire database on a daily basis, tablespace backups are not necessary.

However, tablespace backups are no replacement for frequent backups of the complete database because:

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, you decide what has to be backed up. BRBACKUP supports the backup operation itself, but does not help you decide which tablespaces to back up. Therefore, it is possible that you might forget to back up certain tablespaces. However, for operations such as a tablespace extension or a reorganization, SAPDBA recommends you to back up the tablespace immediately.

You can use tablespace backup for large databases.

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. Therefore, you should 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 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 or 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.

Backing Up Executable Programs and Other SAP Components

In addition to backing up database files and offline redo log files, we recommend you to also back up the following non-database files:

Permanent Files

You can find these files, for example, on UNIX systems in the subdirectories /usr/sap/<SAPSID>/SYS and <ORACLE_HOME>/dbs (UNIX) or \\sapmnt\<SAPSID>\SYS and <ORACLE_HOME> (Windows). They include executable programs and profiles of the SAP System and of the Oracle database system. We recommend you to back up the SAP directories after an SAP System upgrade and the Oracle directories after a database upgrade.

Temporary Files

You can find these files, for example, on UNIX systems in the subdirectory /usr/sap/<SAPSID>/<INSTANCE>. The loss of these files is not critical, and does not cause data inconsistency. SAP provides tools that can reset the references to these files in the database, when required.

BRBACKUP backs up non-database files as well as database files, but we recommend you to only use it for this in exceptional circumstances. For more information, see Backing Up Non-Database Files and Directories.

Caution

Backing up non-database files using BRBACKUP is not a replacement for backing up the file system at operating system level. For more information, see the documentation for your operating system.