9 Cleaning up the Caching Environment

Detaching a TimesTen database from a cache grid

Call the ttGridDetach built-in procedure to detach a grid member from the cache grid that it is attached to. If the grid member is an active standby pair, the active and standby databases must both be detached, and they must be detached separately. When a grid member has been detached, you can no longer perform operations on its global cache groups or on their cache tables. The grid member also relinquishes ownership of all cache instances that it had owned. The cache agent and replication agent processes cannot be stopped until the database detaches from its cache grid.

From the cachealone1 database, call the ttGridDetach built-in procedure as the cache manager user to detach the member from the ttGrid cache grid. For example:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttGridDetach;

To make sure that all committed updates on cache tables in the global cache groups in cachealone1 have been propagated to the cached Oracle Database tables before the TimesTen database is detached from its cache grid, specify the number of seconds to wait before executing the detach. In this example, the wait is 60 seconds:

Command> CALL ttGridDetach(,,60);

Then after the database has been detached from its grid, the replication agent running on the database can be stopped.

You can force detach a grid member that becomes unavailable but is still attached to the grid. A grid member's underlying TimesTen database is unavailable, for example, when the TimesTen system is taken offline or the database has been destroyed. Call the ttGridDetach built-in procedure as the cache manager user passing the value 1 to the force parameter from any one of the TimesTen databases that are available except from the read-only subscriber databases.

Command> CALL ttGridDetach('TTGRID_alone2_2',1);

To determine the names of all attached grid members, call the ttGridNodeStatus built-in procedure.

You can force detach a set of grid members that become unavailable but are still attached to the grid by calling the ttGridDetachList built-in procedure as the cache manager user from any one of the TimesTen databases that are available except from the read-only subscriber databases. Pass the value 1 to the force parameter.

Command> CALL ttGridDetachList('TTGRID_cacheact_3A TTGRID_cachestand_3B',1);

You can detach all of the grid members by calling the ttGridDetachAll built-in procedure. In this example, the detach operation waits 60 seconds:

Command> CALL ttGridDetachAll(60);

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 cachealone1, cachealone2, 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. If the active standby pair is a grid member, the grid member must be detached from the grid before dropping the active standby pair.

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

You must unload the data in a global cache group in all grid members before dropping the cache group. Set the GlobalProcessing optimizer flag to 1 and unload the cache group:

CALL ttOptSetFlag('GlobalProcessing', 1);
UNLOAD CACHE GROUP subscriber_accounts;

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 cachealone1, cachealone2, 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 cachealone1 database:

% ttIsql cachealone1
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=cachealone1;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 cachealone1, cachealone2, cacheactive and cachestandby databases to drop the subscriber_accounts global cache group. The following example shows the SQL statement issued from the cachealone1 database:

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

Destroying a cache grid

Call the ttGridDestroy built-in procedure to destroy a cache grid. By default, a cache grid cannot be destroyed if there are existing cache groups or attached grid members.

From any one of the TimesTen databases, except from the read-only subscriber databases, call the ttGridDestroy built-in procedure as the cache manager user to destroy the ttGrid cache grid:

Command> CALL ttGridDestroy('ttGrid');

You can force destroy a cache grid even if a grid member whose TimesTen database becomes unavailable while it contains cache groups or is attached to the grid. A TimesTen database is unavailable, for example, when the TimesTen system is taken offline or the database has been destroyed. Call the ttGridDestroy built-in procedure as the cache manager user passing the value 1 to the force parameter from any one of the TimesTen databases except from the read-only subscriber databases.

Command> CALL ttGridDestroy('ttGrid',1);

A cache grid should be destroyed only if it is no longer needed and there is no intent to attach to it again.

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 cachealone1, cachealone2, cacheactive and cachestandby databases, issue the following built-in procedure call to stop the cache agent on the database:

Command> CALL ttCacheStop;
Command> exit

The cache agent cannot be stopped if the TimesTen database is still attached to a cache grid.

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 cachealone1 database:

% ttDestroy cachealone1

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

Recommended method for a scheduled shutdown of an 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.