Data Guard Redo Log retry Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data GuardChapter 5 - Log Management Services

Retrying Archiving to a Failed Destination

LOG_ARCHIVE_DEST_2 = 'SERVICE=appsstdby1 MANDATORY REOPEN=90 MAX_FAILURE=5'

The default value of REOPEN is 300 seconds. These parameters are not related to any specific type of failure.  A heartbeat process on the primary database keeps polling the physical standby database. The frequency of polling is 60 seconds by default.  When the REOPEN attribute is set, the heartbeat polling frequency changes to the REOPEN value. The minimum value of REOPEN can be 0 seconds, but the heartbeat will still poll the physical standby site every 60 seconds even if the REOPEN is set to less than 60 seconds.  Hence, setting it to less than 60 seconds does not have any effect.

WARNING - MAX_FAILURE attribute did not work in release 9.2.0.2 due to Bug 2449124. Check the MOSC site for any bug fixes on this issue if the attribute  is not working in the existing environment.

Archiving to an Alternate archived log Destination

LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1

REOPEN=90 MAX_FAILURE=2 ALTERNATE=LOG_ARCHIVE_DEST_4'

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_4='LOCATION=/oracle/appsdb/arch_2'

LOG_ARCHIVE_DEST_STATE_4=ALTERNATE

The primary database will not reuse the online redo log file if the log is archived to an alternate location for an archive destination defined as MANDATORY. If a destination is specified as listed below and the appsstdby1 service is not accessible, the archiver will archive the redo file into the destination specified by log_archive_dest_4.  The online redo log file, however, will not be marked as archived; therefore, it will not be re-used by log writer.

LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1 MANDATORY REOPEN=0 ALTERNATE=LOG_ARCHIVE_DEST_4'

Dependence relationship between log archive destinations

In some Data Guard configurations, the DBA may want to keep only one repository of archived redo logs and feed all the standby databases from this repository. In this kind of scenario, the physical archival of log files to one common location needs to be configured.  Upon the successful archival of the log file, inform the dependent standby databases so that the recovery process on the standby databases can continue.

Log Management Services - Dependence relationship between archival destinations

This is more like parent-child relationships between the log archival destinations. Using the DEPENDENCY attribute of the log_archive_dest_n parameter, such a parent-child relationship can be set. The validity of the child destination depends upon the success of archival in the parent archive destination.

Also, the standby_archive_dest parameter needs to be updated on the standby databases to point to the location where the physical archiving will take place. The log_archive_format on the standby databases and the primary database should be the same; otherwise, the MRP process on the standby databases will not be able to identify the logs required for recovery.

In the following example, the redo logs will not be physically archived to the Oracle instance pointed by appsstdby1 service name. Instead, this destination is dependent on log_archive_dest_1. When a log file is archived to the /oracle/appsdb/arch location, the name of archived redo log is registered in the Oracle instance and the recovery process on the Oracle instance reads the log file from the /oracle/appsdb/arch directory:

LOG_ARCHIVE_DEST_1='LOCATION=/oracle/appsdb/arch
MANDATORY'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_2='SERVICE=appsstdby1
DEPENDENCY=LOG_ARCHIVE_DEST_1'
LOG_ARCHIVE_DEST_STATE_2=ENABLE