Data Guard failover to logical standby Tips Oracle Database Tips by Donald BurlesonDecember 9, 2015

Oracle Data Guard - Failover to a Logical Standby Database

Conceptually, the failover operation to a logical Oracle instance is similar to failover to a physical Oracle instance in that it involves the following steps: verify the status of the recovery process; recover as much data as possible on the target logical standby database; and then transition to the primary database role.

This section describes these steps, in detail, and is accompanied by the SQL statements required to perform the failover operation:

• The initialization parameter file on the target Oracle instance should be updated to include the other logical Oracle instance as the archival destination. All the physical standby databases will be invalidated after the role transition, so do not include the physical standby databases.
   

• Remove any previously set delay in the log apply service using the DBMS_LOGSTDBY package as explained in "Preparing a Logical Oracle instance for Switchover" section.

• Manually copy the archived redo logs from the primary database or any other physical standby database, if the primary database local archival destination is damaged in order to resolve any gap sequence on the target logical standby database. The archive gap sequence can be obtained using the script, find_gap.sql, from the code depot.
   

• Once the archived redo logs have been manually copied to the target logical standby database, register them using the following statement:

ALTER DATABASE REGISTER LOGICAL LOGFILE 'filespec';

• If the DBA has access to the online redo log files of the original primary database, these should be copied to the target logical standby site and registered using the above-mentioned statement.
   

• Verify the overall progress of the log apply service on this logical Oracle instance using the script, log_progress.sql, from the code depot. Once all the redo data has been applied by SQL apply operation, stop the recovery process on the standby site and activate it as the primary database using the following statements:

ALTER DATABASE STOP LOGICAL STANDBY APPLY;
ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;

• At this point, the logical Oracle instance has been converted to the primary database and Oracle Net should be configured to send all the application requests to the new primary database.
   

• As explained in the "Switchover to a Logical Standby Database" section, create a database link on the other logical Oracle instance in the Data Guard environment to support future switchover operations.

Verify Data Guard Failover Operation

The verification of failover operation remains the same as the switchover operations. That is, after completing the failover, the current redo log on the new primary database should be archived. Then the contents of the STANDBY_ARCHIVE_DEST directory of the other standby databases should be checked.

Existence of the most recent archived log file in this directory will prove the log transfer service is working properly after the failover. Additionally, check the content of the alert log file on both the new primary and the standby sites to verify the status of the log apply services.

If the failover is to a logical standby database, check for oracle error ORA-16109 in the alert log file of the remaining logical standby databases. If this error is present in the alert log file of the logical standby database, this logical Oracle instance should be created from the new primary database.

Impact on Other Oracle instance in Configuration

Typically, a Data Guard configuration consists of more than one standby database. A role transition from the original primary to a Oracle instance can affect the other standby databases in the environment. The impact depends on the type of database selected for role transition.

Impact on Other Oracle instance in Configuration

If a physical Oracle instance is used for role transition, all other standby databases will not be affected. In fact, the other standby databases in the Data Guard environment do not need to be restarted and should operate without any issue. If the role transition is to a logical standby database, all the physical standby databases will be invalidated. Other logical standby databases may or may not be affected.

Sometimes, Oracle error ORA-16109 will be encountered when the log apply service is started using the archived redo logs from the new primary database. In this case, the logical Oracle instance should be recreated from the new primary database.

Transparent Application Failover

Transparent Application Failover (TAF) is a functionality offered by Oracle Net to re-establish a failed connection to another database instance. TAF can be used with the Data Guard configuration to provide complete end-to-end failover functionality. Oracle Net configuration files, tnsnames.ora or Oracle Name server can be configured to avail TAF functionalities.

There are various configurations that can be used for TAF. In this section, information will be presented on a connect-time failure configuration. For more information on TAF refer to Oracle9i Net Services Administrator's Guide Release 2 (9.2).

A connect-time failure configuration requires inclusion of the failover_mode parameter in the tnsnames.ora file. A sample tnsnames.ora file is shown in the following example:

Oracle Data Guard - Switchover and Failover

Transparent Application Failover

Appsdb =
 (DESCRIPTION=
 (FAILOVER=ON)
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=jrpr01) 
       (PORT=1521))
  (ADDRESS=
       (PROTOCOL=tcp) 
       (HOST=jrbk01) 
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=appsdb)
    (FAILOVER_MODE=(TYPE=session)(METHOD=basic))
  ))

In the above connection string, client connection will be tried on host jrpr01 or jrbk01 for service "appsdb". The original primary database runs on jrpr01. As long as the original primary database is active, the client requests will go to "appsdb" database on jrpr01. After a failover, the database on jrpr01 will not respond, and the client connection requests will be routed to "appsdb" database on jrbk01 host.