10 Using TimesTen Cache with Data Guard

Configuring the primary and standby Oracle databases

When you create and configure Active Data Guard with primary and standby Oracle databases, ensure that the configuration includes the following to support the TimesTen cache environment.

1. Configure both the primary and standby Oracle databases to use Flashback queries. For more information, see "Configuring Recovery Settings" in the Oracle Database 2 Day DBA guide.

2. The Data Guard configuration must be managed by the Data Guard Broker so that the TimesTen Cache daemon processes and application clients respond faster to failover and switchover events. For more information, see the Data Guard Broker guide.

3. Create two Oracle Database Services, where one points to the primary Oracle Database and the other points to the physical standby Oracle Database. See "Creating two Oracle Database services" for details.

Configuring the active standby pair with read-only cache groups

The Active Data Guard with asynchronous redo transport mode supports an active standby pair replication scheme that only contains replicated read-only cache groups. All replicated read-only cache groups must be created before you create the active standby pair. You cannot exclude a replicated read-only cache group when you are creating the active standby pair and you cannot add another replicated read-only cache group to the active standby pair after creation.

When you create and configure an active standby pair to support replicated read-only cache groups, perform the following to support asynchronous Active Data Guard:

1. When you create the active standby pair, we recommend that you keep both the active and standby masters within the same physical site. They can be on different hosts within the same site.

2. If you want a read-only subscriber for disaster recovery, you can add a read-only subscriber on the same disaster recovery site as the standby Oracle database and enable the subscriber for cache groups. The subscriber that you should create when using Active Data Guard is created with a duplicate operation with the ttRepAdmin -duplicate -activeDataGuard options.

   The -activeDataGuard option, which is solely for the Active Data Guard environment, enables the subscriber to keep replicated read-only cache groups intact as it would for a standby master. Since the subscriber retains these cache groups, you must provide the cache user name and cache user password on the ttRepAdmin utility command line.

   Note:

   Alternatively, you can use the ttRepDuplicateEx C function setting the TT_REPDUP_ADG flag in ttRepDuplicateExArg.flags.

   The following example creates a read-only subscriber on the disaster recovery site duplicating from the standby master providing the -activeDataGuard option, the cache user name, and the cache user password.

   ttRepAdmin -duplicate -from master2 -host node1
    -uid cacheuser -pwd timesten -cacheuid cacheuser -cachepwd oracle 
    -activeDataGuard adgsubscriber
   

3. Create the cache environment on the primary Oracle database. You do not need to perform any of these steps on the standby Oracle database.

4. On the primary Oracle database, grant the cache admin user the EXECUTE privilege for the SYS.DBMS_FLASHBACK package. This privilege is granted as part of the initCacheAdminSchema.sql and grantCacheAdminPrivileges.sql scripts as of the TimesTen Classic 18.1 release.

5. Configure the same connection attributes that you would for a TimesTen database that caches data from an Oracle database. In addition, since we are also monitoring transactions from the standby Oracle database, configure the StandbyNetServiceName connection attribute with the Oracle net service name of the standby Oracle database instance.

   On Microsoft Windows systems, the net service name of the Oracle database instance is specified in the Oracle Net Service Name field of the TimesTen Cache tab within the TimesTen ODBC Setup dialog box. The standby Oracle database instance is specified in the Standby Oracle Net Service Name field on the same page.

   Configure the StandbyNetServiceName ODBC.INI attribute on the active master to configure the net service name of the physical standby Oracle database:

   [cachedb]
   DataStore=/myDb/cachedb
   PermSize=256
   TempSize=256
   PLSQL=0
   DatabaseCharacterSet=WE8DEC
   OracleNetServiceName=primaryinstance
   StandbyNetServiceName=standbyinstance
   

Recovery after failure when using asynchronous Active Data Guard

The following sections describe what to do if the primary Oracle database fails, the standby Oracle database fails, or the entire primary site fails taking down the primary Oracle database as well as the active and standby masters:

• Failure of the standby Oracle database

• Failure of the primary Oracle database

• Failure of the primary site

Failure of the primary Oracle database

If the primary Oracle database fails, then Data Guard switches over to the standby Oracle database and the TimesTen cache agent switches autorefresh over to the new primary Oracle database.

Figure 10-2 Failure of the primary Oracle database

Description of ''Figure 10-2 Failure of the primary Oracle database''

Failure of the primary site

If the entire site where the primary Oracle database as well as the active and standby master databases are located fails, then the standby Oracle database becomes the primary Oracle database. Then, you may want the disaster recovery site to become the primary TimesTen database. Thus, on the disaster recovery site, the standby Oracle database is now a sole Oracle database and the read-only subscriber becomes a single TimesTen database that caches data in the Oracle database.

Transform the subscriber into a single TimesTen database with cached tables by:

1. Drop the active standby pair on the TimesTen database on the disaster recovery site.

2. Alter the existing read-only cache groups on the disaster recovery site to set the autorefresh state to on.

After which, the cache tables on the TimesTen database in the disaster recovery site receive updates from the new primary Oracle database.

Figure 10-3 Recovery after failure of primary site

Description of ''Figure 10-3 Recovery after failure of primary site''

Recovering from a failure of the primary site

The following is the process to recover the primary site and rebuild your environment to the original state:

1. Create a new active standby pair on the disaster recovery site.

2. Alter the existing read-only cache groups on the disaster recovery site to set the autorefresh state to off to stop any future updates from the primary Oracle database.

3. Create the ADG enabled read-only subscriber on the recovered primary site.

4. Drop the active standby pair on the ADG enabled read-only subscriber on the primary site, if it still exists after recovering the primary site.

5. Switch over the Oracle databases in the Active Data Guard. Currently, the applications are updating the primary Oracle database on the disaster recovery site. However, once you recover the Oracle database on the primary site, we want it to take over again as the primary and to make the Oracle database on the disaster recovery site as the secondary.

   The TimesTen database starts to receive updates from the Oracle database on the primary site.

   
   Description of the illustration ''adg_recover1.png''
   
   

6. Create a new active standby pair on the primary site.

7. Create a new ADG enabled read-only subscriber on the disaster recovery site.

   
   Description of the illustration ''adg_recover2.png''
   
   

Configuring the Oracle databases

In order for TimesTen Cache 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 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 Oracle database services that TimesTen Cache 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 for information about creating database services.

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 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 TimesTen 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 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, 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 TimesTen 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 Database instance. Use the Oracle Client shipped with TimesTen 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