Basics of Physical and Logical Standby Oracle 12c Databases

By Chris Ruel, Michael Wessler

A physical standby database is a block-for-block copy of the primary Oracle 12c database. It is built off a backup of the primary site and is maintained by shipping and applying archive logs to the standby site in the same way the transactions were committed on the primary site.

Physical standby databases can’t be open for changes. You can stop recovery on the physical standby site and open it for read-only transactions. During this time, the standby site falls behind the primary site in terms of synchronicity. All the transactions are saved until the standby site’s recovery is reactivated after reporting operations are done.

If you want a standby site available for reporting operations, consider setting up dual standby sites. That way, one can stay in recovery mode, and you perhaps can open the other for reporting operations during the day and then close it at night for catch-up. That way if you ever need to have a standby site activated, you won’t have to wait for it to catch up first.

Here’s a high-level overview of the steps to configure a physical standby database. In this example, the primary site name is prod_a and the standby site name is prod_b:

  1. Set various initialization parameters in the primary database to prepare it for redo log shipping:

    instance_name (different on each site)

    instance_name = prod_a

    db_name (same on each site)

    db_name = prod

    remote_archive_enable (enables sending of logs to remote site)

    remote_archive_enable = true

    log_archive_dest_1, 2

    log_archive_dest_1 = 'LOCATION=/u01/arch/prod'
    log_Archive_dest_2 = ' ARCH'

    log_archive_format (tells primary how to name local and standby logs)

    log_archive_format = arch_%S.arc

    standby_file_management (makes adding data files easier)

    standby_file_management = true

    fal_client (tells primary where to re-ship “lost” archive logs)

    fal_client = ''

    Regarding Steps 1 and 6: Set all the parameters on both sites to facilitate failover/switchover operations.

  2. Create a standby copy of your primary control file by logging in to SQL*Plus on the primary and typing the following:

    <alter database create standby controlfile as

    You should see this:

    Database altered.
  3. Move this copy to the standby site and put it in the directory of your choice.

  4. Modify the initialization parameters on the prod_b instance to point to the new control file.

    You can rename it however you want.

  5. Restore a backup of your primary site to the standby site.

    You can do this with Recovery Management or traditional hot/cold backup methods. To simplify things, put the files in the same locations on the standby site as the primary.

    If you can’t do that, you have to rename the files after you mount the database, or you need to use the following initialization parameters on the standby site so the instance can convert the locations. Say the files were in /u01/app/oracle/oradata/prod on the primary and /disk1/app/oracle/oradata/prod on the standby:

    DB_FILE_NAME_CONVERT = '/u01/', '/disk1/'

    Oracle finds all instances of /u01 in your data filename and replaces them with /u02.

  6. Set the initialization parameters on the standby site:

    instance_name (different on each site)

    instance_name = prod_b

    db_name (same on each site)

    db_name = prod

    remote_archive_enable (enables receiving of logs on remote site)

    remote_archive_enable = true

    standby_archive_dest (tells standby database where to find logs)

    standby_archive_dest = /disk1/arch/prod

    log_archive_format (tells standby how to interpret log names, set same as primary)

    log_archive_format = arch_%S.arc

    standby_file_management (makes adding data files easier)

    standby_file_management = true

    fal_server (tells standby where to search for “lost” archive logs)

    fal_server = ''
  7. Mount the standby database:

    <alter database mount standby database;>

    You should see this:

    Database altered.
  8. Start recovery on the standby database:

    <recover managed standby database disconnect;>

    You see this:

    Media recovery complete.
  9. Log out of the standby site.

    Let the recovery run in the background.

A logical standby database works by copying your primary site with a backup. Then a process called SQL Apply takes the archive logs from the primary site and extracts the SQL statements from them to apply them to the logical standby database.

During this time, the logical standby database is up and open. It’s like having the best of both worlds. People can have updated data with the primary site for reporting purposes.

Because the standby database will be up and open, you must protect the data from being modified by anyone other than the SQL Apply services. If the data is modified outside of this procedure, the standby database will diverge from the primary. If you ever need to switch over to it for disaster recovery purposes, it won’t match the primary.

To prevent replicated objects in the standby site from being modified, issue the following command in the standby environment:


Another unique feature of a logical standby database: the ability to replicate only certain objects. By default, all objects are replicated. However, you can force SQL Apply processes to skip certain objects. In addition, you can configure those skipped objects to allow modifications to them.