Replicating Cache Tables in TimesTen Classic

To achieve high availability in TimesTen Classic, configure an active standby pair replication scheme for cache tables in a read-only cache group or an AWT cache group.

An active standby pair that replicates cache tables from one of these cache group types can automatically change the role of a TimesTen Classic database as part of failover and recovery with minimal chance of data loss. Cache groups themselves provide resilience from Oracle database outages, further strengthening system availability. An active standby pair replication scheme provides for high availability of a TimesTen Classic database.

Note:

This section describes one scenario in including cache groups within an active standby pair replication scheme. See Administering an Active Standby Pair with Cache Groups in Oracle TimesTen In-Memory Database Replication Guide for more scenarios for including AWT and read-only cache groups in an active standby pair replication scheme.

Oracle Real Application Clusters (Oracle RAC) provides for high availability of an Oracle database. See Using Cache in an Oracle RAC Environment.

Perform the following tasks to configure an active standby pair for TimesTen Classic databases that cache Oracle Database tables:

• Create and Configure the Active Database

• Create and Configure the Standby Database

• Create and Configure the Read-Only Subscriber Database

Create and Configure the Active Database

This example shows how to create and configure the active database in an active standby pair replication scheme.

The following is the definition of the cacheactive DSN for the active database of the active standby pair:

[cacheactive]
DataStore=/users/OracleCache/cacheact
PermSize=64
OracleNetServiceName=orcl
DatabaseCharacterSet=WE8ISO8859P1
CacheAdminWallet=1

Note:

If you set the CacheAdminWallet as a first connection attribute (normally set in the DSN), then when you register the cache administration user credentials with the ttCacheUidPwdSet built-in procedure, they are stored in an Oracle Wallet rather than in memory.

Start the ttIsql utility and connect to the cacheactive DSN as the instance administrator to create the database. Then create the TimesTen cache administration user cacheadmin whose name is the same as the Oracle cache administration user.

Then, create a cache table user sales whose name is the same as the Oracle Database schema user who owns the Oracle Database tables to be cached in the TimesTen Classic database.

% ttIsql cacheactive
Command> CREATE USER cacheadmin IDENTIFIED BY timesten;
Command> CREATE USER sales IDENTIFIED BY timesten;

As the instance administrator, use the ttIsql utility to grant the TimesTen cache administration user cacheadmin the privileges required as well as create an active standby pair replication scheme which requires the ADMIN privilege:

Command> GRANT CREATE SESSION, CACHE_MANAGER,
        CREATE ANY TABLE, ADMIN TO cacheadmin;
Command> exit

Start the ttIsql utility and connect to the cacheactive DSN as the TimesTen cache administration user. Set the Oracle cache administration user name and password by calling the ttCacheUidPwdSet built-in procedure.

% ttIsql "DSN=cacheactive;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheUidPwdSet('cacheadmin','orapwd');

If desired, you can test the connectivity between the active database and the Oracle database using the instructions stated in Testing the Connectivity Between the TimesTen and Oracle Databases.

Start the cache agent on the active database by calling the ttCacheStart built-in procedure as the TimesTen cache administration user:

Command> CALL ttCacheStart;

The following statement is the definition of the Oracle Database table that is to be cached in a dynamic AWT cache group. The Oracle Database table is owned by the schema user sales.

CREATE TABLE subscriber
(subscriberid       NUMBER(10) NOT NULL PRIMARY KEY,
 name               VARCHAR2(100) NOT NULL,
 minutes_balance    NUMBER(5) NOT NULL,
 last_call_duration NUMBER(4) NOT NULL);

The Oracle cache administration user must be granted the SELECT privilege on the sales.subscriber table so that the TimesTen cache administration user can create an AWT cache group that caches this table. The Oracle cache administration user must be granted the INSERT, UPDATE and DELETE Oracle Database privileges for the sales.subscriber table for asynchronous writethrough operations to be applied to the Oracle Database.

Then, create cache groups in the TimesTen Classic database with the CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP statement as the TimesTen cache administration user. For example, the following statement creates a dynamic AWT cache group subscriber_accounts that caches the sales.subscriber table:

CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP subscriber_accounts
FROM sales.subscriber
 (subscriberid       NUMBER(10) NOT NULL PRIMARY KEY,
  name               VARCHAR2(100) NOT NULL,
  minutes_balance    NUMBER(5) NOT NULL,
  last_call_duration NUMBER(4) NOT NULL);

As the TimesTen cache administration user, create an active standby pair replication scheme in the active database using a CREATE ACTIVE STANDBY PAIR statement.

In the following example, cacheact, cachestand and subscr are the file name prefixes of the checkpoint and transaction log files of the active database, standby database and read-only subscriber database. sys3, sys4 and sys5 are the host names of the TimesTen systems where the active database, standby database and read-only subscriber database reside, respectively.

Command> CREATE ACTIVE STANDBY PAIR cacheact ON "sys3", cachestand ON "sys4"
        SUBSCRIBER subscr ON "sys5";

As the TimesTen cache administration user, start the replication agent on the active database by calling the ttRepStart built-in procedure. Then declare the database as the active by calling the ttRepStateSet built-in procedure.

Command> CALL ttRepStart;
Command> CALL ttRepStateSet('active');

Create and Configure the Standby Database

This example shows how to create and configure a standby database in an active standby pair replication scheme.

The following is the definition of the cachestandby DSN for the standby database of the active standby pair:

[cachestandby]
DataStore=/users/OracleCache/cachestand
PermSize=64
OracleNetServiceName=orcl
DatabaseCharacterSet=WE8ISO8859P1
CacheAdminWallet=1

Note:

If you set the CacheAdminWallet as a first connection attribute (normally set in the DSN), then when you register the cache administration user credentials with the ttCacheUidPwdSet built-in procedure, they are stored in an Oracle Wallet rather than in memory.

As the instance administrator, create the standby database as a duplicate of the active database by running a ttRepAdmin -duplicate utility command from the standby database system. The instance administrator user name of the active database's and standby database's instances must be identical.

Use the -keepCG option so that cache tables in the active database are duplicated as cache tables in the standby database, because the standby database is connected with the Oracle database.

In the following example:

• The -from option specifies the file name prefix of the active database's checkpoint and transaction log files.

• The -host option specifies the host name of the TimesTen system where the active database resides.

• The -uid and -pwd options specify a user name and password of a TimesTen internal user defined in the active database that has been granted the ADMIN privilege.

• The -cacheuid and -cachepwd options specify the Oracle cache administration user name and password.

• cachestandby is the DSN of the standby database.

• The -keepCG option specifies that the standby database keeps the cache groups defined on the active database.

% ttRepAdmin -duplicate -from cacheact -host "sys3" -uid cacheadmin -pwd timesten
    -cacheuid cacheadmin -cachepwd orapwd -keepCG cachestandby

Start the ttIsql utility and connect to the cachestandby DSN as the cache administration user. Set the Oracle cache administration user name and password by calling the ttCacheUidPwdSet built-in procedure.

% ttIsql "DSN=cachestandby;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheUidPwdSet('cacheadmin','orapwd');

If desired, you can test the connectivity between the standby database and the Oracle database using the instructions stated in Testing the Connectivity Between the TimesTen and Oracle Databases.

Start the cache agent on the standby database by calling the ttCacheStart built-in procedure as the TimesTen cache administration user:

Command> CALL ttCacheStart;

As the TimesTen cache administration user, start the replication agent on the standby database by calling the ttRepStart built-in procedure.

Command> CALL ttRepStart;

Create and Configure the Read-Only Subscriber Database

This example demonstrates how to create and configure a read-only subscriber within an active standby pair replication scheme.

The following is the definition of the rosubscriber DSN for the read-only subscriber database of the active standby pair:

[rosubscriber]
DataStore=/users/OracleCache/subscr
PermSize=64
DatabaseCharacterSet=WE8ISO8859P1

As the instance administrator, create the read-only subscriber database as a duplicate of the standby database by running a ttRepAdmin -duplicate utility command from the read-only subscriber database system. The instance administrator user name of the standby database and read-only subscriber database must be identical.

Use the -noKeepCG option so that cache tables in the standby database are duplicated as regular tables in the read-only subscriber database because the read-only subscriber database is not connected with the Oracle database.

In the following example:

• The -from option specifies the file name prefix of the standby database's checkpoint and transaction log files.

• The -host option specifies the host name of the TimesTen system where the standby database resides.

• The -uid and -pwd options specify a user name and password of a TimesTen internal user defined in the standby database that has been granted the ADMIN privilege.

• rosubscriber is the DSN of the read-only subscriber database.

% ttRepAdmin -duplicate -from cachestand -host "sys4" -uid cacheadmin -pwd timesten
    -noKeepCG rosubscriber

As the TimesTen cache administration user, start the replication agent on the read-only subscriber database by calling the ttRepStart built-in procedure.

% ttIsql "DSN=rosubscriber;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttRepStart;
Command> exit