Understanding Oracle RAC/Standby/Replication Environments

By: Scott Jesse, Bill Burton, Bryan Vongray


Oracle Data Guard  is one of the core components in a Maximum Available Architecture. With Oracle Data Guard, you implement one or more standby databases for your Oracle RAC database, which can be either Oracle RAC or a single instance. You can take advantage of Transparent Application Failover (TAF) within your Oracle RAC database, as this is the primary environment suited for use of TAF. With Oracle RAC, you can always be assured that when failing over to another node, you are still accessing the same data as before—because it is the same database. However, TAF can also be used in other environments, such as physical or logical standby environments.

Determining the suitability depends on how these environments are used. For example, with a standby database, users can experience a transparent failover, when the database is switched over to the standby. This depends, however, on how quickly the switchover occurs and also on the settings for RETRIES and DELAY. With logical standby, clients can failover as well, without necessarily having to perform a switchover, because in this case, the target databases for failover is open. However, successful select failover would depend on the availability of the data at the target sites, as the propagation of data to a logical standby does not necessarily have to be synchronous.

The DB_ROLE_CHANGE system event

The Oracle Data Guard database failover process is straightforward. But especially in complete site failover, life is not that easy, and more steps are involved to failover an entire application. Certain components need to be either reconfigured or restarted to make the application work again as expected.

To address this, you can use the DB_ROLE_CHANGE system event. This event (event inside the database) fires every time the role of the database is changed. When a physical or logical standby is converted to a primary or a primary is converted to a physical or logical standby, the DB_ROLE_CHANGE system event is fired. This event then can be caught by a trigger that fires after the role change event happens. The trigger is coded to call the DBMS_SCHEDULER and the scheduler executes an external script. This script can contain all kind of actions you need to perform to make your application failover, such as starting the application servers.

The following is an example of how the trigger could look. Once the trigger is created, as a result of a role change, the script/u01/scripts/role_change_actions.sh would be executed. The script should be placed on all nodes.

   SQL> CREATE OR REPLACE TRIGGER role_change_actions AFTER DB_ROLE_CHANGE
   ON DATABASE
    BEGIN
     dbms_scheduler.create_job(
     job_name=>'role_change_actions',
     job_type=>'executable',
     job_action=>'/u01/scripts/role_change_actions.sh',
     enabled=>TRUE
     );
    END;

Setting up the client

The connect string of the client should contain the SCAN of the primary and the standby cluster because this avoids any reconfiguration during a failover. The connect string should use a service name as well:

   TEST =
     (DESCRIPTION =
       (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = ratgen-scan.us.oracle.com)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST = ratlnxclu02-scan.us.oracle.com)(PORT =
   1521))

       (LOAD_BALANCE = yes)
       )
       (CONNECT_DATA =
        (SERVICE_NAME = TEST)
     )
   )

 

The service, however, should be running only on the primary side. If the service is running on the standby side as well, client connections would attempt to connect to the standby side; therefore, the service name should not be listed under SERVICE_ NAMES in the SPFILE. To automate the starting of the service when the role of the database is primary, you can deploy a database startup trigger:

   SQL> CREATE OR REPLACE TRIGGER start_service_test AFTER STARTUP ON DATABASE
    DECLARE
    role VARCHAR(30);
    BEGIN
    SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
     IF role = 'PRIMARY' THEN
     DBMS_SERVICE.START_SERVICE('TEST');
     END IF;
   END;

This trigger would not cover the situation that would occur when a logical database is converted into a primary database, because this action does not require a restart of the standby database. Subsequently, the startup database trigger will not fire. In this case, a second trigger (the first one is still needed) dependent on the DB_ROLE_ CHANGE can catch that situation:

   SQL> CREATE OR REPLACE TRIGGER start_service_test_role_change AFTER
   DB_ROLE_CHANGE ON DATABASE
    DECLARE
    role VARCHAR(30);
    BEGIN
    SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
     IF role = 'PRIMARY' THEN
      DBMS_SERVICE.START_SERVICE('TEST');
      END IF;
   END;

NOTE

When using the Oracle Data Guard Broker in an 11g Release 2 Data Guard environment, a new feature called Role-Based Services allows database services to start on a designated database when that database is in a specific role. This avoids much of the complexity described above but does require the use of the Oracle Data Guard Broker to manage the role of the database within the clusterware.

FAN and Oracle Data Guard

When an Oracle Data Guard failover is performed using the Oracle Data Guard Broker, the FAN OCI event is automatically created and published to the application. The Java Database Connectivity (JDBC) application makes use of FAN Oracle Notification Service (ONS), but FAN ONS is designed to work only within a cluster; thus, FAN events cannot send FAN events to another cluster. Having said that, this is exactly what is needed in the event of a failover to the standby cluster. To resolve this, an external ONS publisher must be configured. This external publisher will create events that tell the application that the primary database is down and where it can find the new primary database.

Leave a Reply