11 Using Oracle In-Memory Database Cache with Data Guard

Configuring the Oracle databases

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

1. The Data Guard configuration must be managed by the Data Guard Broker so that IMDB Cache 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 database services that IMDB Cache and its client applications use to connect to the Oracle primary database. See "Introduction to Automatic Workload Management" in Oracle Real Application Clusters Administration and Deployment Guide for information about creating database services.

3. If you created the 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 IMDB Cache 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 IMDB Cache 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 executed first on the physical or logical standby database, and then on the primary database, right before the switchover process. Before executing 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 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, the 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 IMDB Cache and its applications.

See the Maximum Availability Architecture, Oracle Best Practices for High Availability white paper for more information.

Configuring the TimesTen database

Configure TimesTen to receive notification of FAN HA events and to avoid reconnecting to a failed Oracle instance. Use the Oracle Client shipped with IMDB Cache.

1. Create an Oracle Net service name that includes all primary and standby hosts in ADDRESS_LIST. For example:

   DBSERV = 
   (DESCRIPTION = 
     (ADDRESS_LIST = 
     (ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARYDB)(PORT = 1521)) 
     (ADDRESS = (PROTOCOL = TCP)(HOST = STANDBYDB)(PORT = 1521)) 
     (LOAD_BALANCE = yes) 
     ) 
     (CONNECT_DATA= (SERVICE_NAME=DBSERV))
   )
   

2. In the client's sqlnet.ora file, set the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter to enable clients to quickly traverse an address list in the event of a failure. For example, if a client attempts to connect to a host that is unavailable, the connection attempt is bounded to the time specified by the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter, after which the client attempts to connect to the next host in the address list. Connection attempts continue for each host in the address list until a connection is made.

   Setting the SQLNET.OUTBOUND_CONNECT_TIMEOUT parameter to a value of 3 seconds suffices in most environments. For example, add the following entry to the sqlnet.ora file:

   SQLNET.OUTBOUND_CONNECT_TIMEOUT=3