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
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 theADMIN
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 theADMIN
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