8 Cleaning up the Caching Environment

Stopping the replication agent

Call the ttRepStop built-in procedure to stop the replication agent. This must be done on each TimesTen database of the active standby pair including any read-only subscriber databases, and any standalone TimesTen databases that contain AWT cache groups.

From the cache1, cache2, cacheactive, cachestandby and rosubscriber databases, call the ttRepStop built-in procedure as the cache manager user to stop the replication agent on the database:

Command> CALL ttRepStop;

Dropping a cache group

Use the DROP CACHE GROUP statement to drop a cache group and its cache tables. Oracle Database objects used to manage the caching of Oracle Database data are automatically dropped when you use the DROP CACHE GROUP statement to drop a cache group, or an ALTER CACHE GROUP statement to set the autorefresh state to OFF for autorefresh cache groups.

If you issue a DROP CACHE GROUP statement on a cache group that has an autorefresh operation in progress:

• The autorefresh operation stops if the LockWait connection attribute setting is greater than 0. The DROP CACHE GROUP statement preempts the autorefresh operation.

• The autorefresh operation continues if the LockWait connection attribute setting is 0. The DROP CACHE GROUP statement is blocked until the autorefresh operation completes or the statement fails with a lock timeout error.

If cache tables are being replicated in an active standby pair and the cache tables are the only elements that are being replicated, you must drop the active standby pair using a DROP ACTIVE STANDBY PAIR statement before dropping the cache groups.

Execute the following statement as the cache manager user on the cacheactive, cachestandby and rosubscriber databases to drop the active standby pair replication scheme:

Command> DROP ACTIVE STANDBY PAIR;
Command> exit

Before you can drop a cache group, you must grant the DROP ANY TABLE privilege to the cache manager user. Execute the following statement as the instance administrator on the cache1, cache2, cacheactive and cachestandby databases to grant the DROP ANY TABLE privilege to the cache manager user. The following example shows the SQL statement issued from the cache1 database:

% ttIsql cache1
Command> GRANT DROP ANY TABLE TO cacheuser;
Command> exit

If you are dropping an AWT cache group, use the ttRepSubscriberWait built-in procedure to make sure that all committed updates on its cache tables have been propagated to the cached Oracle Database tables before dropping the cache group.

% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttRepSubscriberWait('_AWTREPSCHEME','TTREP','_ORACLE','sys1',-1);

The replication scheme that was created for the AWT cache group to enable committed updates on its cache tables to be asynchronously propagated to the cached Oracle tables is automatically dropped when you drop the cache group.

Use a DROP CACHE GROUP statement to drop the cache groups from the standalone TimesTen databases and the active and standby databases.

Execute the following statement as the cache manager user on the cache1, cache2, cacheactive and cachestandby databases to drop the subscriber_accounts cache group. The following example shows the SQL statement issued from the cache1 database:

% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> DROP CACHE GROUP subscriber_accounts;

Stopping the cache agent

Call the ttCacheStop built-in procedure to stop the cache agent. This must be done on the active and standby databases of the active standby pair, and all standalone TimesTen databases.

From the cache1, cache2, cacheactive and cachestandby databases, issue the following built-in procedure call to stop the cache agent on the database:

Command> CALL ttCacheStop;
Command> exit

Destroying the TimesTen databases

If the TimesTen databases are no longer needed, you can use the ttDestroy utility to destroy the databases.

The following example shows the ttDestroy utility connecting to and then destroying the cache1 database:

% ttDestroy cache1

Dropping Oracle Database users and objects

Use SQL*Plus as the sys user to drop the timesten user, the schema user oratt, and the cache administration user cacheuser, and all objects such as tables and triggers owned by these users. Then drop the TT_CACHE_ADMIN_ROLE role, and the default tablespace cachetblsp used by the timesten user and the cache administration user including the contents of the tablespace and its data file.

% sqlplus sys as sysdba
Enter password: password
SQL> DROP USER timesten CASCADE;
SQL> DROP USER oratt CASCADE;
SQL> DROP USER cacheuser CASCADE;
SQL> DROP ROLE tt_cache_admin_role;
SQL> DROP TABLESPACE cachetblsp INCLUDING CONTENTS AND DATAFILES;
SQL> exit

Scheduling a shutdown of active standby pair with AWT cache groups

When you are using active standby pairs with AWT cache groups, the environment includes both an active and a standby master, potentially one or more subscribers, and at least one Oracle Database. The following is the recommended method when you initiate a scheduled shutdown of outstanding transactions in this environment. This order of events provides the time needed to finish applying outstanding transactions before shut down and minimizes the time needed to restart all components.

1. Shut down all applications.

2. Ensure that all transactions have propagated to the Oracle database.

3. Shut down TimesTen.

4. Shut down the Oracle Database.

Then, when you are ready to restart all components:

1. Restart the Oracle Database.

2. Restart TimesTen.

3. Restart any applications.

You can shut down all of these products in any order without error. The order matters only to maximize performance and reduce the need for preserving unapplied transactions. For example, when you are using AWT cache groups within the active standby pair and if you shut down the Oracle database before TimesTen, then all unapplied transactions accumulate in the TimesTen transaction logs. Thus, when you restart TimesTen and Oracle, you could potentially have a lower throughput while pending transactions are applied to the Oracle database. Thus, shutting down TimesTen before the Oracle database provides the most efficient method for your scheduled shutdown and startup. In addition, shutting down the applications before TimesTen stops any additional requests from being sent to an unavailable TimesTen database.