Oracle Standby Databases

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 and the data file itself can be created at the operating system level. The file can be added in either of the following ways:

    • Automatically by Oracle if Oracle parameter standby_file_management is set to auto.

    • Automatically by BRARCHIVE if used for applying archive redo logs.

  • 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.

  Structural changes rarely cause major problems. For more information about the effects of structural database changes, see the appropriate Oracle documentation .

• Copy of archived redo logs is automated

  Oracle provides the Data Guard Utility to copy the archived redo logs from the primary database to the standby database. Alternatively, you can use BRARCHIVE for this. See “Oracle Standby Database with BRBACKUP and BRARCHIVE” below for a possible solution for this.

• Recovery of standby database is automated

  Oracle provides the Data Guard Utility to automatically start the permanent recovery of the standby database. Alternatively, you can use BRARCHIVE for this. 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 back up 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 (Windows) the archive directory of the standby database from the production system, or to be able to use a remote copy command like rcp/scp or ftp.

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

Creating an Oracle Standby Database

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

We recommend 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

  Caution

  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 when 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<DBSID>.sap) as follows:

  backup_type = offline_stop

  Perform the following steps with this procedure:

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

  2. 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_type of offline_stop as described above.

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

  4. Start up the database at the production site.

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

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

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

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

  9. 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:

  1. 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.

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

     startup mount

     recover database until cancel using backup controlfile

  3. Apply all available archived redo logs

  4. Cancel recovery and shutdown the production database

  5. 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.

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

  7. 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.

  8. Start up the database at the production site.

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

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

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

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

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

  14. Resume the normal backup strategy.

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