Managing Log Apply Services

You can manage Redo Apply and SQL Apply on physical and logical standby databases by using database properties related to log apply.

The database properties related to log apply are as follows:

• Properties common to Redo Apply and SQL Apply

  • ApplyInstanceTimeout

  • DelayMins

  • PreferredApplyInstance

• Properties specific to Redo Apply

  • ApplyParallel

  • ApplyInstances

There are some properties related to SQL Apply that, if changed, may require a restart of SQL Apply if the current database state is APPLY-ON. See the information in Oracle Data Guard Broker Properties about properties related to SQL Apply, to determine which ones require SQL Apply to be restarted.

If the current database state is APPLY-OFF, the property changes will take effect the next time the database state is changed to APPLY-ON.

Managing Delayed Apply

You can set up Apply Services so that the application of redo to the standby database is delayed.

This allows the standby database to lag behind the primary database, and if a user error (for example, dropping a table) occurs during this window of time, the standby database will still contain the correct data that can be transmitted back to the primary database to repair the data.

By default, no delay is configured and the redo data is applied on a standby database as soon as possible. If the standby database has standby redo logs configured, the broker will enable real-time apply. When Redo Apply and SQL Apply apply redo in real time, the redo data is recovered directly from the standby redo log files as they are being filled. This means that the standby database does not have to wait for the log files to be archived before applying redo data from the archived redo log files. This minimizes the transactional lag between the primary and the standby.

Use the DelayMins database property to specify the number of minutes that log apply services must wait before applying redo data to the standby database. Note that only log apply services on the standby database are delayed. Redo transport services on the primary database are not delayed, thus the primary database data is still well protected by the standby database.

Caution:

Because the broker automatically enables real-time apply on standby databases, Oracle recommends that you configure all databases to use Flashback Database.

Managing Parallel Apply with Redo Apply

For Redo Apply, you can configure whether multiple parallel processes are used to apply redo data received from the primary database by using the ApplyParallel database property.

Parallelism is enabled by default, which means Redo Apply automatically chooses the optimal number of parallel processes based on the number of CPUs in the system. (This is equivalent to setting the ApplyParallel property to AUTO.) You can disable parallelism by setting the ApplyParallel property to NO.

Note:

Parallel Redo Apply is different from multi-instance Redo Apply. Parallel Redo Apply means that there are multiple Redo Apply slaves per instance; this value is set using the broker ApplyParallel property. Multi-instance Redo Apply means that there are multiple instances running Redo Apply; this value is set with the broker ApplyInstances property. The two properties can be used together to control the Redo Apply appliers on each instance on which apply is running in multi-instance apply. The number of parallel appliers specified by the ApplyParallel property will be the same on each instance in a multi-instance apply configuration.

See Also:

ApplyParallel

ApplyInstances

Managing Multi-Instance Redo Apply

For Redo Apply in an Oracle RAC database, you can configure the number of instances that can be engaged in recovery by means of the ApplyInstances property value.

By default, only one instance is involved in recovery activity. However, the ApplyInstances property can be set to indicate a specific number of instances or the value ALL, to indicate all instances. When recovery is started, it checks to see if enough instances as configured are available to start recovery on. If not, then broker delays starting recovery for one minute to allow other instances to start up and then starts recovery.

During periodic health checks, broker checks to see if more instances have started that could potentially be engaged in recovery. If so, then broker stops and restarts recovery to engage the additional instances.

Changing the value of the ApplyInstances property value results in recovery being restarted with the new values.

All instances must be in the same state (open or mounted) to be able to engage that instance in recovery.

Apply Services in an Oracle RAC Database Environment

When a standby database is an Oracle RAC database, SQL Apply and Redo Apply make use of an apply instance.

SQL Apply can run on only one instance of an Oracle RAC database at any time. This instance is called the apply instance.

Redo Apply can run on all instances of an Oracle RAC database at the same time, but only one of the instances is the apply coordinator and that is the instance that the broker considers to be the apply instance. This feature requires that the ApplyInstances database configurable property (valid only on physical standby databases) be set to a non-zero value. See ApplyInstances.

If the apply instance fails, then the broker automatically restarts SQL Apply, or the Redo Apply coordinator, as appropriate, on a different instance. This is called apply instance failover (see Apply Instance Failover).

Selecting the Apply Instance

If you have no preference which instance is to be the apply instance in an Oracle RAC standby database, the broker randomly picks an apply instance. If you want to select a particular instance as the apply instance, there are two methods to do so.

Note:

The information in this section is not applicable to snapshot standby databases or far sync instances.

• The first method is to set the value of the PreferredApplyInstance database property to the name of the instance (see the InstanceName property) you want to be the apply instance. The broker starts log apply services on that instance if no apply instance is yet selected in the Oracle RAC standby database. This could be the case before you enable the standby database for the first time, or if the apply instance just failed and the broker is about to do an apply instance failover, or if the Oracle RAC database is currently the primary and you want to specify its apply instance in preparation for a switchover. Once the apply instance is selected and, as long as the apply instance is still running, the broker disregards the value of the PreferredApplyInstance property even if you change it.

• The second method is to change the apply instance when the apply instance is already selected and is running. To change the apply instance, issue the DGMGRL SET STATE command to set the standby database state to APPLY-ON, with a specific apply instance argument. The SET STATE command will update the PreferredApplyInstance property to the new apply instance value, and then move log apply services to the new instance. For example, use DGMGRL SHOW command to show the available instances for the standby database, then issue the EDIT DATABASE command to move log apply services to the new instance:

  DGMGRL> SHOW DATABASE 'South_Sales'
   
  Database - South_Sales
   
    Role:            PHYSICAL STANDBY
    Intended State:  APPLY-ON
    Transport Lag:   0 seconds (computed 1 second ago)
    Apply Lag:       0 seconds (computed 1 second ago)
    Apply Rate:      1017.00 KByte/s
    Real Time Query: OFF
    Instance(s):
      south_sales1   (apply instance)
      south_sales2
   
  Database Status:
  SUCCESS
  

  DGMGRL> EDIT DATABASE 'South_Sales' SET STATE='APPLY-ON' WITH APPLY INSTANCE='south_sales2';
  Succeeded.
  
  DGMGRL> SHOW DATABASE 'South_Sales' 'PreferredApplyInstance';
    PreferredApplyInstance = 'south_sales2'
  

  DGMGRL> SHOW DATABASE 'South_Sales'
   
  Database - South_Sales
   
    Role:            PHYSICAL STANDBY
    Intended State:  APPLY-ON
    Transport Lag:   0 seconds (computed 1 second ago)
    Apply Lag:       0 seconds (computed 1 second ago)
    Apply Rate:      1017.00 KByte/s
    Real Time Query: OFF
    Instance(s):
      south_sales1
      south_sales2   (apply instance)
   
  Database Status:
  SUCCESS
  

Ensure that the new apply instance is running when the command is issued. Otherwise, the apply instance remains the same.

Once the apply instance is selected, the broker keeps apply instance information in the broker configuration file so that even if the standby database is shut down and restarted, the broker still selects the same instance to start log apply services. The apply instance remains unchanged until changed by the user or it fails for any reason and the broker decides to do an apply instance failover.

Apply Instance Failover

To tolerate a failure of the apply instance, the broker leverages the availability of the Oracle RAC standby database by automatically failing over log apply services to a different standby instance.

The apply instance failover capability provided by the broker enhances data protection.

To set up apply instance failover, set the ApplyInstanceTimeout database property to specify the time period that the broker will wait after detecting an apply instance failure and before initiating an apply instance failover. To select an appropriate timeout value, you need to consider:

• If there is another mechanism in the cluster (such as Oracle Clusterware) that will try to recover the failed apply instance.

• How long your business can tolerate not applying redo data on the standby database.

• The overhead associated with moving the log apply services to a different instance.

The broker default value of the ApplyInstanceTimeout property is 0 seconds, indicating that apply instance failover should occur immediately upon detection of the failure of the current apply instance.

After the broker initiates an apply instance failover, the broker selects a new apply instance according to the following rule: if the PreferredApplyInstance property indicates an instance that is currently running, select it as the new apply instance; otherwise pick a random instance that is currently running to be the new apply instance.

In addition, if the physical standby database was operating in real-time query mode when the apply instance failed, then after Oracle recovery cleanup is completed, the broker opens any instances that had been automatically closed. If the failed apply instance was the only instance open, then the instance chosen as the new apply instance is opened before starting apply services so that real-time query is once again in effect.

See Also:

• Oracle Data Guard Concepts and Administration for more information about real-time query mode

• The My Oracle Support note 1357597.1 at https://support.oracle.com for additional information about apply instance failures in an Oracle Active Data Guard Oracle RAC standby

Apply Lag

Apply lag is a measure of the degree to which the data in a standby database lags behind the data in the primary database, due to delays in propagating and applying the redo.

Both Cloud Control and the DGMGRL client display the apply lag for each managed standby database. Cloud Control displays the apply lag on the Oracle Data Guard home page. The DGMGRL client displays the apply lag in the SHOW DATABASE output. There is no apply lag displayed for a primary database or far sync instance. For example:

DGMGRL> SHOW DATABASE 'South_Sales';

Database - South_Sales

  Role:                PHYSICAL STANDBY
  Intended State:      APPLY-ON
  Transport Lag:       0 seconds (computed 1 second ago)
  Apply Lag:           0 seconds (computed 1 second ago)
  Average Apply Rate:  13.00 KByte/s
  Real Time Query:     OFF
  Instance(s):
    SouthSales

Database Status:
SUCCESS

Starting with Oracle Database Release 19c, the SHOW CONFIGURATION LAG command displays a summary of the broker configuration and the apply lag of all standby databases.

The apply lag can help you identify any problems that may exist with both the redo transport services and the log apply services.

You can set the ApplyLagThreshold database configurable property to generate a health check warning when a standby database or far sync instance lags behind the data in the primary database.

The following command sets the ApplyLagThreshold property to 15 seconds:

DGMGRL> EDIT DATABASE 'South_Sales' SET PROPERTY 'ApplyLagThreshold'=15;
Property ApplyLagThreshold updated