Configuring the Oracle Databases for TimesTen and Synchronous Data Guard

You can configure TimesTen to fail over and switch over when using synchronous Data Guard.

In order for TimesTen to fail over and switch over properly, configure the primary and standby Oracle databases using the following steps:

1. The Data Guard configuration must be managed by the Data Guard Broker so that the TimesTen daemon processes and application clients respond faster to failover and switchover events.
2. If you are configuring an Oracle RAC database, use the Oracle Enterprise Manager Cluster Managed Database Services Page to create Oracle database services that TimesTen and its client applications use to connect to the Oracle primary database. See Workload Management with Dynamic Database Services in Oracle Real Application Clusters Administration and Deployment Guide.
3. If you created the Oracle database service in step 2, use the MODIFY_SERVICE function of the DBMS_SERVICE PL/SQL package to modify the service to enable high availability notification to be sent through Advanced Queuing (AQ) by setting the aq_ha_notifications attribute to TRUE. To configure server side TAF settings, set the failover attributes, as shown in the following example:

   BEGIN
   DBMS_SERVICE.MODIFY_SERVICE 
   (service_name => 'DBSERV',
    goal => DBMS_SERVICE.GOAL_NONE,
    dtp => false,
    aq_ha_notifications => true,
    failover_method => 'BASIC', 
    failover_type => 'SELECT', 
    failover_retries => 180, 
    failover_delay => 1);
   END;

4. If you did not create the database service in step 2, use the CREATE_SERVICE function of the DBMS_SERVICE PL/SQL package to create the database service, enable high availability notification, and configure server side TAF settings:

   BEGIN
   DBMS_SERVICE.CREATE_SERVICE
   (service_name => 'DBSERV', 
    network_name => 'DBSERV', 
    goal => DBMS_SERVICE.GOAL_NONE,
    dtp => false,
    aq_ha_notifications => true, 
    failover_method => 'BASIC', 
    failover_type => 'SELECT', 
    failover_retries => 180, 
    failover_delay => 1);
   END;

5. Create two triggers to relocate the database service to a Data Guard standby database (Oracle RAC or non-Oracle RAC) after it has switched to the primary role. The first trigger fires on the system start event and starts up the DBSERV service:

   CREATE OR REPLACE TRIGGER manage_service 
   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('DBSERV'); 
     END IF; 
   END;

   The second trigger fires when the standby database remains open during a failover and switchover upon a database role change. It relocates the DBSERV service from the old primary to the new primary database and disconnects any connections to that service on the old primary database so that TimesTen and its client applications can reconnect to the new primary database:

   CREATE OR REPLACE TRIGGER relocate_service 
   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('DBSERV');
     ELSE
       dbms_service.stop_service('DBSERV');
     dbms_lock.sleep(2);
     FOR x IN (SELECT s.sid, s.serial# 
               FROM v$session s, v$process p
               WHERE s.service_name='DBSERV' AND s.paddr=p.addr)
       LOOP 
         BEGIN 
           EXECUTE IMMEDIATE 
              'ALTER SYSTEM DISCONNECT SESSION 
              ''' || x.sid || ','|| x.serial# || ''' IMMEDIATE';
           EXCEPTION WHEN OTHERS THEN 
           BEGIN 
             DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
           END; 
         END; 
       END LOOP; 
     END IF; 
   END;

6. As an option, to reduce the performance impact to TimesTen applications and minimize the downtime during a physical or logical standby database switchover, run the following procedure right before initiating the Data Guard switchover to a physical or logical standby database:

   DECLARE
     role varchar(30);
   BEGIN
     SELECT database_role INTO role FROM v$database;
     IF role = 'PRIMARY' THEN
       dbms_service.stop_service('DBSERV');
     dbms_lock.sleep(2);
     FOR x IN (SELECT s.sid, s.serial# 
               FROM v$session s, v$process p 
               WHERE s.service_name='DBSERV' AND s.paddr=p.addr)
       LOOP 
         BEGIN
           EXECUTE IMMEDIATE
               'ALTER SYSTEM DISCONNECT SESSION 
               ''' || x.sid || ',' || x.serial# || ''' IMMEDIATE'; 
           EXCEPTION WHEN OTHERS THEN
           BEGIN
             DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK);
           END;
         END;
       END LOOP;
     ELSE
       dbms_service.start_service('DBSERV');
     END IF; 
   END;

   This procedure should be performed first on the physical or logical standby database, and then on the primary database, right before the switchover process. Before running the procedure for a physical standby database switchover, Active Data Guard must be enabled on the physical standby database.

Before performing a switchover to a logical standby database, stop the Oracle Database service for TimesTen on the primary database and disconnect all sessions connected to that service. Then start the service on the standby database.

At this point, cache applications try to reconnect to the standby database. If a switchover occurs, there is no wait required to migrate the connections from the primary database to the standby database. This eliminates the performance impact on TimesTen and its applications.