Oracle Standby Databases

Use

An Oracle standby database uses a copy of the production database on a second hardware system, so greatly improving the overall reliability of the database service. For more information about standby databases in general, see Standby Databases.

The following diagram shows the setup for an Oracle standby database:

Oracle Standby Database

Features

·        Setup overview

The production database is copied to a second location. The work done in the production database is recorded in redo log files. They are archived and shipped to the second location and applied there to keep the standby database up to date.

·        Separation of standby system from production system

The standby system is normally located at a remote site, since otherwise it too might be affected by whatever destroyed the production system.

·        Standby is a copy of production database

The standby database does not have to be an exact physical copy of the production database. Directory structures and file names might be different. The standby database might contain only parts of the production database. The standby database consists of copied data files, online redo log files and a standby control file created at the primary site and moved to the standby site.

If BRBACKUP is used to make offline backups of the standby database, it has to be an exact copy. For more information, see “Oracle Standby Database with BRBACKUP and BRARCHIVE” below.

·        Structural database changes

Changes to the structure of the production database might affect the standby database. Certain changes are propagated to the standby database (that is, the control file of the standby database is updated). A discussion of potential problems with structural changes follows.

·        Standby mode

The standby database is mounted in standby mode. This means the database cannot be used in any way other than for recovery. Another consequence of mounting the database in standby mode is that it cannot be opened in the standard way. It has to be activated first and then opened. This prevents an accidental open of the database, which would invalidate the standby state of the database.

·        Standby database runs in recovery mode

Once the standby database has been mounted, it is put in recovery mode. The redo log files archived at the production site have to be shipped to the remote site and applied there using the database recovery mechanisms. This performs a “redo” of all work done in the production database in the standby database. The standby database always lags slightly behind, because the redo log file currently used by the production database cannot be shipped yet.

·        What happens when the production database fails?

If the production database becomes unavailable, the standby database has to be activated, shutdown, and then opened for normal use.

When the production database fails, some committed transactions might be lost, because the current online redo log file that the production database was using at the time of the disaster might be inaccessible. The standby database can then only be recovered to the state reflected in the last archived redo log file.

Before activating the standby database, always try to archive the current redo log in the production database, ship it to the standby site and apply it.

Perform a backup of the standby database

It is important to immediately perform a backup of the standby database once it is activated and opened for normal use. If no backup is performed and problems occur in the standby database, all work done since the activation is lost. This backup is also important to enable you to subsequently restore the database at the production site.

·        What happens if the production database comes online again?

If the production site becomes available again, SAP recommends not to use (that is, start) the database. The reason for this is that it is impossible to apply “new” transactions of the “production database” to the standby database. These transactions are lost when you revert to the original configuration (see next point below).

·        Revert to the original configuration as soon as possible

If the standby database is put into productive operation due to a disaster, it should then be considered the production system. Once the disaster situation is resolved at the production site, you have to decide how to switch back to the original configuration (if that is desired at all).

Problems with Oracle Standby Databases

The following are the main missing and problematic features with the Oracle Standby Database:

·        Structural database changes

This section discusses the following types of structural changes of the primary database only:

Ў        When a data file is added, the system uses information in the redo log to update the control file of the standby database, but the data file itself is not created at the operating system level. The file can be added in either of the following ways:

§         Copy a backup of the file from the production database to the standby database

§         Enter the following command (the size does not have to be specified because this information is already available in the control file):

alter database create datafile

If you forget to add a file and the recovery process finds redo data for that file in the redo logs, the recovery returns errors and aborts. You can now add the data file as described above and resume the recovery.

Ў        When a data file is dropped from the primary database, the standby control file is updated, that is, the data file is dropped from the standby database as well. Note that the data file is not deleted at the operating system level. You still have to do this manually.

In general, structural changes should not cause major problems. Refer to the appropriate Oracle documentation for a detailed description of the effects of structural database changes.

·        Copy of archived redo logs not automated

Oracle does not provide anything to copy the archived redo logs from the primary database to the standby database. You have to manage this yourself. See “Oracle Standby Database with BRBACKUP and BRARCHIVE” below for a possible solution for this.

·        Recovery of standby database not automated

Oracle does not provide a mechanism to automatically start the permanent recovery of the standby database. You have to initiate recovery with the command recover standby database. See “Oracle Standby Database with BRBACKUP and BRARCHIVE” below for a possible solution to this problem.

·        I/O errors and disk failures

These can cause datafiles to go offline and such files are not recovered. If this happens on the standby database, inconsistencies result and tablespaces might be lost. In this situation, you have to make a new copy of the production database to set up the standby database from scratch again.

·        Data corruption during transfer

Compression utilities used to electronically transfer files from one machine to another might cause data corruption. Make sure that the data files and the archived redo log files are transferred in such a way that no corruption or loss of files can occur.

·        The effects on applications connected to the database

See "Problems with Standby Databases" in Standby Databases.

Oracle Standby Database with BRBACKUP and BRARCHIVE

BRBACKUP and BRARCHIVE support a standby database, as follows:

·        BRBACKUP can be used to make offline backups of the standby database. BRBACKUP retrieves information about the database structure from the production database and backs up the standby database accordingly. This implies that the standby database is an exact copy of the production database. The advantage of this setup is that no backups have to be done at the production site, so reducing the load there.

·        BRARCHIVE can be used to automate the process of copying archived redo log files to the standby database and recovering the standby database. BRARCHIVE also saves the redo log files at the standby site. A prerequisite is to be able to NFS mount (UNIX) or share (NT) the archive directory of the standby database from the production system.

SAP does not make any recommendations for use of the Oracle standby database feature.

Activities

Creating an Oracle Standby Database

Before you start creating the standby database, you need to have installed the Oracle Software at the standby site. It is best if the software setup is identical between the two nodes. This includes making an Oracle parameter file init<SID>.ora available as well. Later we discuss how to use SAP tools (BRBACKUP, BRARCHIVE) to simplify the maintenance of a standby database setup. For the SAP tools to work, you have to install them at the standby site. You also have to make available the parameter files (that is, init<SID>.dba and init<SID>.sap) for the SAP tools. Both the Oracle and SAP parameter files are usually found in $ORACLE_HOME/dbs. Therefore, you can simplify setup by copying this directory and all its contents to the standby site.

...

       1.      Take a backup (online or offline) of the data files of the production database

       2.      Create a control file at the production site to be used at the standby site, by entering the following command:

alter database create standby controlfile as <filename>

       3.      Archive the current online redo log of the production database, by entering the following command:

alter system archive log current

       4.      Transfer the backed up data files, the control file and all archived redo log files to the standby site.

Maintaining an Oracle Standby Database

...

       1.      Startup the standby database without mounting it, by entering the following command:

startup nomount

       2.      Mount the standby database in standby mode, by entering the following command:

alter database mount standby database

       3.      Transfer archived redo log files from the production database site to the standby database site.

       4.      Put the standby database in recovery mode, by entering the following command:

recover standby database

Switching Back to the Primary System After a Disaster has been Resolved

SAP recommends that you adopt one of the following procedures for switching back to the primary database after the failure has been resolved:

·        Standard procedure for switchback to primary database

Backup of standby database must be full offline (see step 2)

A standard offline backup with BRBACKUP opens the database after the backup to update backup log information. Therefore, you must not do a standard offline backup with BRBACKUP here because the opening of the standby database after the backup means that the standby database and its backup are no longer identical. This in turn means it would not be possible to mount the database at the standby site in standby mode with a standby control file created at the production site, once the database has been restored there.

A new backup type has been introduced in BRBACKUP to solve this problem. This backup type is documented here only and you should only use it in the case where a standby database is backed up offline to enable a restore at the production site. With this backup type the database is shut down and backed up offline, and is not restarted after the backup.

Set the backup type in the BRBACKUP profile ( init<SID>.sap) as follows:

backup_type = offline_stop

Perform the following steps with this procedure:

...

                            a.      Stop production use of the standby database (that is, shut down all application services and close the database)

                            b.      Take a complete backup of the standby database (data files, online redo log files, control files) and copy this to the production site. If you use BRBACKUP to create the backup, use a backup_typeof offline_stop as described above.

                            c.      At the production site, clear the directory for archived redo logs (usually saparch) of all files, including BRARCHIVE log files.

                            d.      Start up the database at the production site.

                            e.      Create a standby control file at the production site and move it to the standby site.

                              f.      Mount the database at the standby site in standby mode.

                            g.      Start the application services (profiles have to point to database host at the production site).

                            h.      Resume normal production and standby operation of the two databases.

                              i.      Resume the normal backup strategy.

The standard procedure outlined above is straightforward and easy to handle. On the other hand, the SAP system is unavailable for a long period (that is, the time it takes to copy the whole database, including offline backup and restore)

·        Advanced procedure for switchback to primary database

Depending on how long the standby database was used, one or more backups are available. To repeat, you must back up the standby database immediately after activation (if the standby database was used for a longer period, you should have taken regular backups). You must have at least one (regular) backup of the standby database to use the advanced procedure described below

Perform the following steps while the standby database is still open for normal use:

...

                            a.      Restore a regular backup of the standby database to the production site (data files, backup control file) and all redo logs archived since the backup.

                            b.      Mount the database at the production site and recover it, using the following commands:

startup mount
recover database until cancel using backup controlfile

                            c.      Apply all available archived redo logs

                            d.      Cancel recovery and shutdown the production database

                            e.      Stop the standby database (that is, stop all hosts running SAP application services and shut down the standby database too).

The standby database is closed for the remaining steps.

                              f.      Copy the current control file, all online redo log files and, if applicable, archived redo log files (that is, redo logs that have not been applied to the database at the production site yet, because they have just been archived) from the standby database to the production site.

                            g.      Mount and recover the database at the production site using the following commands:

startup mount
recover database

Since the current control file is used, this performs a complete media recovery. The database at the production site is up-to-date at the end of the recovery.

                            h.      Start up the database at the production site.

                              i.      Create a standby control file at the production site and move it to the standby site.

                              j.      Mount the database at the standby site in standby mode.

                            k.      At the production site, clear the directory for archived redo logs (usually saparch) of all files, including BRARCHIVE log files.

                              l.      Start the hosts running application services (profiles have to point to database host at production site).

                          m.      Resume normal production and standby operation of the two databases.

                            n.      Resume the normal backup strategy.

The advanced procedure outlined above has a short downtime (the time taken after shutdown of the standby database to copy the current control file, online redo logs and, maybe, archived redo logs and to recover to the current point in time). On the other hand, it is a more difficult procedure with greater risk of handling errors.

Both switchback procedures described above assume a backup of the standby database is restored to the production site. The backup must have been taken after the standby database was activated and opened for normal use. It is not possible to restore a backup taken before the activation and then recover beyond the activation of the standby database to the current point in time.

See also:

SAP Database Guide: Oracle