10 Using Oracle In-Memory Database Cache with Data Guard

This chapter describes how to configure Oracle In-Memory Database Cache (IMDB Cache) to work with either synchronous local Data Guard or Data Guard used during planned maintenance. It includes the following topics:

Components of MAA for Oracle In-Memory Database Cache

Oracle Maximum Availability Architecture (MAA) is Oracle's best practices blueprint based on proven Oracle high availability (HA) technologies and recommendations. The goal of MAA is to achieve the optimal high availability architecture at the lowest cost and complexity.

To be compliant with MAA, IMDB Cache must support Oracle Real Application Clusters (Oracle RAC) and Oracle Data Guard, as well as have its own HA capability. IMDB Cache provides its own HA capability through active standby pair replication of cache tables in read-only and AWT cache groups. See "Using Oracle In-Memory Database Cache in an Oracle RAC Environment" for more information on IMDB Cache and Oracle RAC.

Oracle Data Guard provides the management, monitoring, and automation software infrastructure to create and maintain one or more synchronized standby databases to protect data from failures, disasters, errors and corruptions. If the primary database becomes unavailable because of a planned or an unplanned outage, Data Guard can switch any standby database to the primary role, thus minimizing downtime and preventing any data loss. For more information about Data Guard, see Oracle Data Guard Concepts and Administration.

The MAA framework for IMDB Cache supports cache tables in explicitly loaded read-only and AWT cache groups. For cache tables in dynamic cache groups of any cache group type, SWT cache groups, and user managed cache groups that use the AUTOREFRESH cache group attribute, IMDB Cache cannot access the Oracle database during a failover and switchover because cache applications will have to wait until the failover and switchover completes.

In general, however, all cache groups types are supported with synchronous local Data Guard or Data Guard during planned maintenance.

How IMDB Cache works with Data Guard

IMDB Cache works with synchronous physical standby failover and switchover and logical standby switchover as long as the object IDs for cached Oracle tables remain the same on the primary and standby databases. Object IDs can change if the table is dropped and re-created, altered, or a truncated flashback operation or online segment shrink is executed.

During a transient upgrade, a physical standby database is transformed into a logical standby database. For the time that the standby database is logical, the user must ensure that the object IDs of the cached Oracle tables do not change. Specifically, tables that are cached should not drop and re-created, truncated, altered, flashed back or online segment shrunk.

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