Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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:


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



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

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.


The above text is an excerpt from the book: Oracle Data Guard Handbook

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.