Configuring an Active Standby Pair
with TimesTen Application-Tier Database Cache and Clusterware

This tutorial will make use of the predefined DSN cachedb1_1122 shipped with TimesTen 11.2.2. The hostnames of the two machines in the examples are ttcwnode1 and ttcwnode2. On ttcwnode1 will be the active cache database, and on ttcwnode2 the standby cache database.

The following steps are required to configure an Active Standby Pair with Oracle TimesTen Application-Tier Database Cache (TimesTen Cache) and Clusterware:

1. Create a cache database

1. Follow the tutorials below to create an initial cache database cachedb1_1122 on ttcwnode1:

   1. Required Configuration on the Oracle Database
   2. Setting Up a Cache Database



2. On ttcwnode2, ensure that an identical system DSN (i.e. [cachedb1_1122]) and database file name (cachedb1_1122) is defined. This is necessary for the successful creation of the standby database on ttcwnode2.

2. Create a database user to administer the active standby pair

1. On ttcwnode1, create a database user and assign to be the database and replication administrator by granting the user the admin privilege. This user is responsible for setting up and managing the active standby pair.

   Instead of creating a new database user, you can also grant the required privilege to the previously created cache manager user cacheadm. This will make cacheadm responsible for managing both replication and cache operations.

   Using ttIsql, log into the database cachedb1_1122 as the instance administrator and execute:

   grant admin to cacheadm;

   Note: In the Setting Up a Cache Database tutorial, we have already granted the ADMIN privilege to cacheadm, hence this step is not needed.

3. Detach database from the cache grid
     (required only if the database is currently attached to a cache grid)

1. Use the built-in ttGridDetach to detach the database from the cache grid.

   call ttgriddetach;




2. Use the procedure ttGridNodeStatus to verify that cachedb1 is no longer attached to a cache grid.

   call ttgridnodestatus;

   

4. Start the cache agent

1. On ttcwnode1, log in to ttIsql as the user cacheadm, passing in the TimesTen and Oracle user passwords for cacheadm.

   ttIsql

   connect "dsn=cachedb1_1122;uid=cacheadm;pwd=cacheadm;oraclepwd=cacheadm";




2. Call the built-in procedure ttCacheStart to start the cache agent for the current database.

   call ttcachestart;

   The cache agent process is responsible for managing the communication between cache databases, as well as controlling the data flow from an Oracle database to a TimesTen Application-Tier Database Cache.

5. Define cache groups

1. ttCWAdmin supports Read Only, AWT, SWT, dynamic and non-dynamic local and global cache groups.

   On ttcwnode1 run the script create_local_ro.sql or enter the following CREATE CACHE GROUP statement, located in the <install_dir>/quickstart/sample_scripts/cachegrid directory to create a read-only local cache group named ro to cache the HR.DEPARTMENTS table.

   Either run the create_local_ro.sql script:

   @quickstart/sample_scripts/cachegrid/create_local_ro.sql;

   or run the following code:

   create readonly cache group ro
   autorefresh
   interval 5 seconds
   mode incremental
   from
   hr.departments ( department_id number(4) not null primary key,
                    department_name varchar2(30) not null,
                    manager_id number(6),
                    location_id number(4));

6. Register the TimesTen Instance Information

1. TimesTen instance information is stored in the Oracle Cluster Registry. As the root user on UNIX or Linux platforms, or as the instance administrator on Windows, register the TimesTen instance information by executing the ttCWAdmin -ocrConfig command on one of the hosts. For example, on ttcwnode1:

   ttcwadmin -ocrconfig

   

7. Start the Cluster Agent

1. Start the TimesTen cluster agent by executing the ttCWAdmin -init command on one of the hosts. For example on ttcwnode1:

   ttCWAdmin -init

   

   This command starts TimesTen Clusterware agents and TimesTen daemons on all nodes in the cluster and starts monitoring them through Oracle Clusterware.

8. Create the TimesTen cluster.oracle.ini configuration file

1. For a basic active standby pair configuration with local cache groups, only the MasterHosts and CacheConnect attributes are needed.

   

   The cluster.oracle.ini entry name (eg [cachedb1_1122]) must correspond to the system DSN for the active and standby databases.

   If you use global cache groups or need to perform global cache grid queries, then the GridPort attribute must also be defined. This attribute lists the port numbers used by the cache grid agents for the active database and the standby database in an active standby pair that is a cache grid member.

   e.g. GridPort=9991,9992

   The TCP/IP port numbers 9991 and 9992 are arbitrary, but they should not conflict with other TCP ports (e.g. ftp, telnet etc.), specifically the hostname + port combination should be unique.

   Note: When you use the installer or ttmodinstall to add support for Oracle Clusterware, the mandatory MasterHosts attribute will be created in the cluster.oracle.ini file for you. For more complex configurations, you need to add the relevant attributes yourself in the cluster.oracle.ini file on every node in the cluster.

9. Create an Active Standby Pair

1. Drop any existing [SQL] Active Standby Pair for the cachedb1_1122 database.

   drop active standby pair;

   You cannot mix Active Standby Pairs managed by SQL and managed by ttCWAdmin. An Active Standby Pair for a cache database should be managed by either SQL or by ttCWAdmin, and not by both.




2. Create an Active Standby Pair replication scheme by executing the ttCWAdmin -create command on any host. For example on ttcwnode1:

   ttcwadmin -create -dsn cachedb1_1122

   

   Based on the cachedb1_1122 entries in the system DSN and the cluster.oracle.ini, this command created the replication scheme for the cachedb1_1122 database on the ttcwnode1 and ttcwnode2 machines.

10. Start the Active Standby Pair

1. Start the Active Standby Pair replication scheme by executing the ttCWAdmin -start command on any host. For example on ttcwnode2:

   ttcwadmin -start -dsn cachedb1_1122

   

   Notes:

   • ttCWAdmin -start will automatically start the replication agent on the standby machine and duplicate the active database into the standby host.
   • The time taken to duplicate the active database will be proportional to the size of the active database and the performance of the hardware used.
   • Once the Active Standby Pair has be started by ttCWAdmin, committed writes to the active database will be written to its local transaction logs, but they will not get replicated to the standby database until the duplicate operation is complete.
   • The ttCWAdmin -status -dsn <your_DSN> command enables you to check the progress of the database duplication.
   • If you have global cache groups or need to perform global cache grid queries (i.e. GridPort has been defined in cluster.oracle.ini), then ttCWAdmin -start will also attach both the active and the standby database to the cache grid.
   • You can login to the cachedb1_1122 database using ttIsql and use the built-in procedure ttRepStateGet to determine to current replication and grid state of a database in an active standby pair. If the output from ttRepStateGet is:
     < ACTIVE, NO GRID > in ttcwnode1
     < STANDBY, NO GRID > in ttcwnode2
     then the configuration of the active standby pair is complete.

11. Preload data into cache groups

1. Populate the cache group by loading the rows from Oracle.

   This command must be run on the active node, which in this example was initially set to ttcwnode1.

   load cache group ro commit every 256 rows;

   

For more information about Using and Configuring an Active Standby Pair with TimesTen Cache and Oracle Clusterware refer to the documentation here