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');