Cache in TimesTen Works With Asynchronous Active Data Guard

You can cache tables from an Oracle Active Data Guard with the asynchronous redo transport mode into read-only cache groups.

When using cache with Active Data Guard, you can only use read-only cache groups that are replicated within an active standby pair replication scheme.

The Active Data Guard configuration includes a primary Oracle database that communicates over an asynchronous transport to a single physical standby Oracle database. As shown in Figure 10-1, the primary Oracle database is located on the primary site, while the standby Oracle database is located on a disaster recovery site.

Figure 10-1 Recommended Configuration for Asynchronous Active Data Guard

Description of "Figure 10-1 Recommended Configuration for Asynchronous Active Data Guard"

On TimesTen, the read-only cache groups on the primary site are autorefreshed from the primary Oracle database; however, the only transactions that are autorefreshed are those whose changes have been successfully replicated to the standby Oracle database. Once refreshed to the active master, all changes are then propagated to the TimesTen standby master and a read-only subscriber using standard TimesTen replication processes.

For the best failover and recovery action, you should locate the read-only subscriber on the same disaster recovery site as the standby Oracle database. Create this read-only subscriber with the ttRepAdmin -duplicate -activeDataGuard utility option, which replicates the read-only cache groups directly to the subscriber as it would to a standby master database. That is, instead of the cache groups being converted to tables when replicated to a subscriber, the cache groups themselves are replicated to the read-only subscriber. This is to provide a recovery and failover option if the primary site fails. See Recovery After Failure When Using Asynchronous Active Data Guard.

The following sections provide more details on the environment for asynchronous Active Data Guard when using replicated read-only cache groups:

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 specific configuration that supports the TimesTen cache environment.

Configure both the primary and standby Oracle databases to use Flashback queries. See Configuring Recovery Settings in the Oracle Database 2 Day DBA guide.
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. See the Data Guard Broker guide.
Create two supporting database services on both the primary and standby Oracle databases in the Oracle Cluster. One database service points to the primary Oracle Database and the other points to the physical standby Oracle Database. You can create these either through role based services or through system triggers.

See the following sections for details.
- Configuring Oracle Database Services Through Role Based Services
- Configuring Oracle Database Services Through System Triggers

Configuring Oracle Database Services Through Role Based Services

You can automatically control the startup of Oracle database services on both the primary and standby Oracle databases by assigning a database role to each service.

An Oracle database service automatically starts when the Oracle database starts if the Oracle database policy is set to AUTOMATIC and if the service role matches the current role of the database. In this case, the role for the Oracle database is either in the primary or standby role as part of the Active Data Guard configuration.

Configure services with the srvctl utility identically on all Oracle databases in the Data Guard configuration. The following example shows two services created identically on both the primary and the standby Oracle databases. See srvctl add service in the Oracle Database Administrator's Guide.

The following steps add the primaryrole and standbyrole database services to both the primary and standby Oracle databases when the primary Oracle database is located in Austin and the standby Oracle database is located in Houston.

On the primary Oracle database, add the primaryrole database service. While this Oracle database acts as the primary, this service is started.
```
srvctl add service -d Austin -s primaryrole -r ssa1,ssa2,ssa3,
 ssa4 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
```
On the primary Oracle database, add the standbyrole database service. This service starts only if this Oracle database switches to the standby role and then provides real-time reporting on the standby Oracle database.
```
srvctl add service -d Austin -s standbyrole -r ssa1,ssa2,ssa3,
 ssa4 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
```
On the standby Oracle database, add the primaryrole database service. This service starts only if this Oracle database switches to the primary role.
```
srvctl add service -d Houston -s primaryrole -r ssb1,ssb2,ssb3,
 ssb4 -l PRIMARY -q TRUE -e SESSION -m BASIC -w 10 -z 150
```
On the standby Oracle database, add the standbyrole database service. While this Oracle database acts as the standby, this service is started and then provides real-time reporting on the standby Oracle database.
```
srvctl add service -d Houston -s standbyrole -r ssb1,ssb2,ssb3,
 ssb4 -l PHYSICAL_STANDBY -q TRUE -e SESSION -m BASIC -w 10 -z 150
```
Run the following SQL statement on the primary Oracle database so that the service definitions are transmitted and applied to the physical standby Oracle database.
```
EXECUTE DBMS_SERVICE.CREATE_SERVICE('standbyrole', 'standbyrole', NULL, 
 NULL, TRUE, 'BASIC', 'SESSION', 150, 10, NULL);
```

Add connection aliases in the appropriate tnsnames.ora files to identify the primary and standby Oracle databases and specify the database service names for each.

primaryinstance=
  (DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)
    (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
           (ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521)))
               (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primaryrole)))
 
    (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
           (ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521)))
               (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primaryrole))))
 
standbyinstance=
   (DESCRIPTION_LIST= 
     (LOAD_BALANCE=off)
     (FAILOVER=on)
     (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521)))
                (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standbyrole)))
 
     (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521)))
               (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standbyrole))))

On the primary Oracle database, start the primaryrole database service.
```
srvctl start service -d Austin -s primaryrole 
```
On the standby Oracle database, start the standbyrole database service.
```
srvctl start service -d Houston -s standbyrole
```

Configuring Oracle Database Services Through System Triggers

You can perform certain steps to create the primaryrole and standbyrole database services on the primary Oracle database using triggers. After creation, these are replicated to the standby Oracle database.

Create the primaryrole and standbyrole database services in the primary Oracle database.

exec DBMS_SERVICE.CREATE_SERVICE( 
 service_name => 'primaryrole', 
 network_name => 'primaryrole',
 aq_ha_notifications => true, failover_method => 'BASIC', 
 failover_type => 'SELECT', failover_retries => 180, failover_delay => 1 );

exec DBMS_SERVICE.CREATE_SERVICE( 
 service_name => 'standbyrole', 
 network_name => 'standbyrole',
 aq_ha_notifications => true, failover_method => 'BASIC', 
 failover_type => 'SELECT', failover_retries => 180, failover_delay => 1 );

Create the primaryrole and standbyrole triggers in the primary Oracle database for when the database starts.

CREATE OR REPLACE TRIGGER manage_OCIService
after startup on database
DECLARE
  role VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
  IF role = 'PRIMARY' THEN
    BEGIN
      DBMS_SERVICE.START_SERVICE('primaryrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    BEGIN
      DBMS_SERVICE.STOP_SERVICE('standbyrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  ELSE
    BEGIN
      DBMS_SERVICE.STOP_SERVICE('primaryrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    BEGIN
      DBMS_SERVICE.START_SERVICE('standbyrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END IF;
END;

Create the following trigger on the primary Oracle database to run when the database changes roles:

CREATE OR REPLACE TRIGGER manage_OCIService2
AFTER DB_ROLE_CHANGE ON DATABASE
DECLARE
  role VARCHAR(30);
BEGIN
  SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
  IF role = 'PRIMARY' THEN
    BEGIN
      DBMS_SERVICE.START_SERVICE('primaryrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    BEGIN
      DBMS_SERVICE.STOP_SERVICE('standbyrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  ELSE
    BEGIN
      DBMS_SERVICE.STOP_SERVICE('primaryrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    BEGIN
      DBMS_SERVICE.START_SERVICE('standbyrole');
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END IF;
END;

Add connection aliases in the appropriate tnsnames.ora files to identify the primary and standby Oracle databases and specify the database service names for each.

primaryinstance=
  (DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)
    (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
           (ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521)))
               (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primaryrole)))
 
    (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
           (ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521)))
               (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=primaryrole))))
 
standbyinstance=
   (DESCRIPTION_LIST= 
     (LOAD_BALANCE=off)
     (FAILOVER=on)
     (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=TCP)(HOST=myhost1)(PORT=1521)))
                (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standbyrole)))
 
     (DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=TCP)(HOST=myhost2)(PORT=1521)))
               (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=standbyrole))))

Restart both of the Oracle databases to enable the trigger to start and stop the correct database services. Alternatively, if you do not want to restart both Oracle databases, you can start and stop the appropriate database services on each Oracle database as follows:
On the primary Oracle database:
```
exec DBMS_SERVICE.START_SERVICE('primaryrole');
exec DBMS_SERVICE.STOP_SERVICE('standbyrole');
```
On the standby Oracle database:
```
exec DBMS_SERVICE.STOP_SERVICE('primaryrole');
exec DBMS_SERVICE.START_SERVICE('standbyrole'); 
```

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:

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.
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 Oracle cache administration user name and 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 administration user name and passwords.
```
ttRepAdmin -duplicate -from master2 -host node1
 -uid cacheadmin -pwd timesten -cacheuid cacheadmin -cachepwd orapwd 
 -activeDataGuard adgsubscriber
```
Create the cache environment on the primary Oracle database. You do not need to perform any of these steps on the standby Oracle database.
On the primary Oracle database, grant the Oracle cache administration user the EXECUTE privilege for the SYS.DBMS_FLASHBACK package. This privilege is granted as part of the initCacheAdminSchema.sql and grantCacheAdminPrivileges.sql scripts.
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 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
DatabaseCharacterSet=WE8DEC
OracleNetServiceName=primaryinstance
StandbyNetServiceName=standbyinstance
```

Recovery After Failure When Using Asynchronous Active Data Guard

There are recommended recovery procedures 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

When the standby Oracle database in an Active Data Guard configuration fails, the cache agent retries the connection to the standby Oracle database.

Note:

You can notify the cache agent of whether the standby Oracle database is active or has failed by calling the ttCacheADGStandbyStateSet built-in procedure with either the ON or the FAILED arguments.

If a timeout is set, then the cache agent waits for the amount of time specified with the ttCacheADGStandbyTimeoutSet built-in procedure. If the standby Oracle database has not recovered after this period, then the cache agent sets the state of the standby Oracle database by calling the ttCacheADGStandbyStateSet built-in procedure with the FAILED argument and then facilitates autorefresh using only the primary Oracle database.
If no timeout has been set with the ttCacheADGStandbyTimeoutSet built-in procedure (default value is 0), then the cache agent continues to wait on the standby Oracle database, unless you inform the cache agent that the standby Oracle database is not recovering by calling the ttCacheADGStandbyStateSet built-in procedure with the FAILED argument.

Once the state of the standby Oracle database is set to FAILED, the cache agent resumes autorefresh with only the primary Oracle database until you reset the state of the standby Oracle database by calling the ttCacheADGStandbyStateSet built-in procedure with the ON argument. Even if the standby Oracle database eventually does recover, the cache agent does not recognize that the standby Oracle database is active until you reset its state to ON.

Once the state of the standby Oracle database is set to ON, the cache agent pauses to wait for the standby Oracle database to catch up to the primary Oracle database. After which, the cache agent resumes autorefresh from the primary Oracle database for those transactions that have successfully replicated to the standby Oracle database.

You can restore the original Active Data Guard configuration by dropping the active standby pair and then loading the cache groups.

See ttCacheADGStandbyTimeoutSet and ttCacheADGStandbyStateSet in the Oracle TimesTen In-Memory Database Reference.

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.

After which, 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:

Drop the active standby pair on the TimesTen database on the disaster recovery site.
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"

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

Create a new active standby pair on the disaster recovery site.
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.
Create the ADG enabled read-only subscriber on the recovered primary site.
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.
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.eps
Create a new active standby pair on the primary site.
Create a new ADG enabled read-only subscriber on the disaster recovery site.

Description of the illustration adg_recover2.eps