10 Using Oracle In-Memory Database Cache with Data Guard

Configuring the Oracle databases

In order for Oracle In-Memory Database Cache to failover and switchover 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 Oracle In-Memory Database Cache daemon processes and application clients will 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 Oracle In-Memory Database Cache and its client applications will 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 on 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:

   exec 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);
   

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:

   exec 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);
   

5. Create two triggers to relocate the database service to a Data Guard standby database (RAC or non-RAC) after it has switched to the primary role. The first trigger fires on the system start event and will start 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 will relocate the DBSERV service from the old primary to the new primary database, and will disconnect any connections to that service on the old primary database so that Oracle In-Memory Database 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 Oracle In-Memory Database 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 will 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 Oracle In-Memory Database Cache and its applications.

See the White Paper Maximum Availability Architecture, Oracle Best Practices for High Availability 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 Oracle In-Memory Database 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 = LOC1PRIMARY)(PORT = 1521)) 
     (ADDRESS = (PROTOCOL = TCP)(HOST = LOC1STANDBY)(PORT = 1521)) 
     (ADDRESS = (PROTOCOL = TCP)(HOST = LOC2PRIMARY)(PORT = 1521)) 
     (ADDRESS = (PROTOCOL = TCP)(HOST = LOC2STANDBY)(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 will be 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 will suffice in most environments. For example, add the following entry to the sqlnet.ora file:

   SQLNET.OUTBOUND_CONNECT_TIMEOUT=3