Skip Headers
Oracle® In-Memory Database Cache User's Guide
Release 11.2.1

Part Number E13073-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Managing a Caching Environment

This chapter describes how to manage and monitor various aspects of a caching system such as cache grids, cache groups and the cache agent process. It includes the following topics:

Checking the status of the cache and replication agents

You can use either the ttAdmin or ttStatus utility to check whether the TimesTen cache agent and replication agent processes are running as well as determine each agent's start policy.

Example 7-1 Using ttAdmin to determine the cache and replication agents status

You can use a ttAdmin -query utility command to determine whether the cache and replication agents are running, and the cache and replication agent start policies for a TimesTen database:

% ttAdmin -query cachealone1
RAM Residence Policy            : inUse
Replication Agent Policy        : manual
Replication Manually Started    : True
Cache Agent Policy              : always
Cache Agent Manually Started    : True

For more information about the ttAdmin utility, see "ttAdmin" in Oracle TimesTen In-Memory Database Reference.

Example 7-2 Using ttStatus to determine the cache and replication agents status

You can use the ttStatus utility to determine whether the cache and replication agents are running, and the cache and replication agent start policies for all TimesTen databases in the installed instance:

% ttStatus
TimesTen status report as of Thu May  7 13:42:01 2009

Daemon pid 9818 port 4173 instance myinst
TimesTen server pid 9826 started on port 4175
------------------------------------------------------------------------
Data store /users/OracleCache/alone1
There are 38 connections to the data store
Shared Memory KEY 0x02011c82 ID 895844354
PL/SQL Memory KEY 0x03011c82 ID 895877123 Address 0x10000000
Type            PID     Context     Connection Name              ConnID
Cache Agent     1019    0x0828f840  Handler                           2
Cache Agent     1019    0x083a3d40  Timer                             3
Cache Agent     1019    0x0842d820  Aging                             4
Cache Agent     1019    0x08664fd8  Garbage Collector(-1580741728)    5
Cache Agent     1019    0x084d6ef8  Marker(-1580213344)               6
Cache Agent     1019    0xa5bb8058  DeadDsMonitor(-1579684960)        7
Cache Agent     1019    0x088b49a0  CacheGridEnv                     14
Cache Agent     1019    0x0896b9d0  CacheGridSend                    15
Cache Agent     1019    0x089fb020  CacheGridSend                    16
Cache Agent     1019    0x08a619f8  CacheGridSend                    17
Cache Agent     1019    0x08ace538  CacheGridRec                     18
Cache Agent     1019    0x08b42e88  CacheGridRec                     19
Cache Agent     1019    0x08bb77d8  CacheGridRec                     20
Cache Agent     1019    0x08c2c128  CacheGridRec                     21
Cache Agent     1019    0x08ca0a78  CacheGridRec                     22
Cache Agent     1019    0x08d153c8  CacheGridRec                     23
Cache Agent     1019    0x08d89d18  CacheGridRec                     24
Cache Agent     1019    0x08dfe668  CacheGridRec                     25
Cache Agent     1019    0x08e72fb8  CacheGridRec                     26
Cache Agent     1019    0x08ee8020  CacheGridRec                     27
Cache Agent     1019    0x08f5d088  CacheGridRec                     28
Cache Agent     1019    0x08fd23f8  CacheGridRec                     29
Cache Agent     1019    0x09047768  CacheGridRec                     30
Replication     18051   0x08c3d900  RECEIVER                          8
Replication     18051   0x08b53298  REPHOLD                           9
Replication     18051   0x08af8138  REPLISTENER                      10
Replication     18051   0x08a82f20  LOGFORCE                         11
Replication     18051   0x08bce660  TRANSMITTER                      12
Subdaemon       9822    0x080a2180  Manager                        2032
Subdaemon       9822    0x080ff260  Rollback                       2033
Subdaemon       9822    0x08548c38  Flusher                        2034
Subdaemon       9822    0x085e3b00  Monitor                        2035
Subdaemon       9822    0x0828fc10  Deadlock Detector              2036
Subdaemon       9822    0x082ead70  Checkpoint                     2037
Subdaemon       9822    0x08345ed0  Aging                          2038
Subdaemon       9822    0x083a1030  Log Marker                     2039
Subdaemon       9822    0x083fc190  AsyncMV                        2040
Subdaemon       9822    0x084572f0  HistGC                         2041
Replication policy  : Manual
Replication agent is running.
Cache Agent policy  : Always
TimesTen's Cache agent is running for this data store
PL/SQL enabled.
------------------------------------------------------------------------

The information displayed by the ttStatus utility include the following that pertains to Oracle In-Memory Database Cache for each TimesTen database in the installed instance:

For more information about the ttStatus utility, see "ttStatus" in Oracle TimesTen In-Memory Database Reference.

Monitoring cache groups and cache grids

The following sections describe how to obtain information about cache grids and cache groups, and how to monitor the status of cache group operations:

Using the ttIsql utility's cachegroups command

You can obtain information about cache groups in a TimesTen database using the ttIsql utility's cachegroups command.

Example 7-3 ttIsql utility's cachegroups command

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

Cache Group CACHEUSER.RECENT_SHIPPED_ORDERS:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 1440 Minutes
  Autorefresh Status: ok
  Aging: Timestamp based uses column WHEN_SHIPPED lifetime 30 days cycle 24 hours on

  Root Table: ORATT.ORDERS
  Table Type: Read Only


Cache Group CACHEUSER.SUBSCRIBER_ACCOUNTS:

  Cache Group Type: Asynchronous Writethrough global (Dynamic)
  Autorefresh: No
  Aging: LRU on

  Root Table: ORATT.SUBSCRIBER
  Table Type: Propagate

Cache Group CACHEUSER.WESTERN_CUSTOMERS:

  Cache Group Type: User Managed
  Autorefresh: No
  Aging: No aging defined

  Root Table: ORATT.ACTIVE_CUSTOMER
  Where Clause: (oratt.active_customer.region = 'West')
  Table Type: Propagate

  Child Table: ORATT.ORDERTAB
  Table Type: Propagate

  Child Table: ORATT.ORDERDETAILS
  Where Clause: (oratt.orderdetails.quantity >= 5)
  Table Type: Not Propagate

  Child Table: ORATT.CUST_INTERESTS
  Table Type: Read Only

3 cache groups found.

The information displayed by the ttIsql utility's cachegroups command include:

  • Cache group type including whether the cache group is dynamic or global

  • Automatic refresh attributes (mode, state, interval) and status, if applicable

  • Aging policy, if applicable

  • Name of root table and, if applicable, name of child tables

  • Cache table WHERE clause, if applicable

  • Cache table attributes (read-only, propagate, not propagate)

For more information about the ttIsql utility's cachegroups command, see "ttIsql" in Oracle TimesTen In-Memory Database Reference.

Monitoring automatic refresh operations on cache groups

You can use the following mechanisms to obtain information and statistics about automatic refresh operations on cache groups, and monitor when cache groups are automatically refreshed:

Monitoring AWT cache group operations

You can use the following instructions to monitor the performance of AWT cache groups and determine how much time is spent performing particular tasks in each component of the AWT cache group workflow:

  1. Call the ttCacheAWTMonitorConfig built-in procedure as the cache manager user to enable AWT monitoring and specify an AWT workflow sampling factor:

    % ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
    Command> call ttCacheAWTMonitorConfig('ON',16);
    Command> exit
    

    The replication agent must be running in order to enable monitoring of AWT cache groups.

    For more information about the ttCacheAWTMonitorConfig built-in procedure, see Oracle TimesTen In-Memory Database Reference.

  2. Run a ttRepAdmin -showstatus -awtmoninfo utility command to display the monitoring results.

    % ttRepAdmin -showstatus -awtmoninfo cachealone1
    

    For information about the output of the ttRepAdmin -showstatus -awtmoninfo utility command, see "Monitoring AWT performance" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.

To determine whether asynchronous writethrough operations are keeping up with the rate of updates on cache tables in AWT cache groups, query the LAST_LOG_FILE, REPHOLD_LOG_FILE and REPHOLD_LOG_OFF columns of the SYS.MONITOR system table. If the difference between the value in the LAST_LOG_FILE column and the value in the REPHOLD_LOG_FILE column is increasing over time, and the value in the REPHOLD_LOG_OFF column is increasing slowly or not changing, then the tables are being updated at a faster rate than the updates are being replicated.

Then run a ttRepAdmin -receiver -list utility command and find the row where _ORACLE is in the "Peer name" field. View the values in the Last Msg Sent, Last Msg Recv, Latency and TPS fields within the same row to determine if the replication activity that is falling behind is asynchronous writethrough operations.

For information on possible causes of poor asynchronous writethrough performance and potential resolutions to the problem, see "Troubleshooting AWT Cache Groups" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.

Configuring a transaction log file threshold for AWT cache groups

The replication agent uses the transaction log to determine which updates on cache tables in AWT cache groups have been propagated to the cached Oracle tables and which updates have not. If updates are not being automatically propagated to Oracle because of a failure such as the replication agent is not running or the Oracle server is unavailable, transaction log files will accumulate on disk.

You can call the ttCacheAWTThresholdSet built-in procedure as the cache administration user to set a threshold for the number of transaction log files that can accumulate before TimesTen stops tracking updates on cache tables in AWT cache groups. The default threshold is 0. This built-in procedure can only be called if the TimesTen database contains AWT cache groups.

Once the threshold has been exceeded, you need to manually synchronize the cache tables with the cached Oracle tables using an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement. TimesTen may purge transaction log files even if they contain updates that have not been propagated to the cached Oracle tables.

Example 7-4 Setting a transaction log file threshold for AWT cache groups

In this example, if the number of transaction log files that contain updates on cache tables in AWT cache groups exceeds 5, TimesTen will stop tracking updates and can then purge transaction log files that may contain unpropagated updates:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheAWTThresholdSet(5);

You can call the ttCacheAWTThresholdGet built-in procedure to determine the current transaction log file threshold setting:

Command> call ttCacheAWTThresholdGet;
< 5 >
Command> exit

Obtaining information about cache grids

You can use the following mechanisms to display information about cache grids and their grid members:

  • Call the ttGridInfo built-in procedure as the cache manager user to return the grid name, cache administration user name, operating system platform, and TimesTen major release number for a specified or all existing cache grids:

    % ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
    Command> call ttGridInfo('ttGrid');
    < TTGRID, CACHEUSER, Linux Intel x86, 32-bit, 11, 2, 1 >
    

    For more information about the ttGridInfo built-in procedure, see Oracle TimesTen In-Memory Database Reference.

  • Call the ttGridNodeStatus built-in procedure as the cache manager user to return the grid name, member ID, node number, host name, member name, IP address, and cache agent TCP/IP port number for all members of a specified or all existing cache grids:

    % ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
    Command> call ttGridNodeStatus;
    < TTGRID, 1, 1, T, sys1, TTGRID_alone1_1, 140.87.0.201, 5001, <NULL>, <NULL>,
    <NULL>, <NULL>, <NULL> >
    < TTGRID, 2, 1, T, sys2, TTGRID_alone2_2, 140.87.0.202, 5002, <NULL>, <NULL>,
    <NULL>, <NULL>, <NULL> >
    < TTGRID, 3, 1, T, sys3, TTGRID_cacheact_3A, 140.87.0.203, 5003, T, sys4, TTGRID_cachestand_3B, 140.87.0.204, 5004 >
    

    For more information about the ttGridNodeStatus built-in procedure, see Oracle TimesTen In-Memory Database Reference.

Tracking DDL statements issued on cached Oracle tables

When a DDL statement is issued on a cached Oracle table, this statement can be tracked in the Oracle TT_version_DDL_L table when the Oracle TT_version_schema-ID_DDL_T trigger is fired to insert a row into the table, where version is an internal TimesTen version number and schema-ID is the ID of user that owns the cached Oracle table. A trigger is created for each Oracle user that owns cached Oracle tables. One DDL tracking table is created to store DDL statements issued on any cached Oracle table. The cache administration user owns the TT_version_DDL_L table and the TT_version_schema-ID_DDL_T trigger.

To enable tracking of DDL statements issued on cached Oracle tables, call the ttCacheDDLTrackingConfig built-in procedure as the cache manager user. By default, DDL statements are not tracked.

For more information about the ttCacheDDLTrackingConfig built-in procedure, see Oracle TimesTen In-Memory Database Reference.

Example 7-5 Enabling tracking of DDL statements issued on cached Oracle tables

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheDDLTrackingConfig('enable');

The TT_version_DDL_L table and TT_version_schema-ID_DDL_T trigger are automatically created if the cache administration user has been granted the set of required privileges including RESOURCE and CREATE ANY TRIGGER. These Oracle objects are created when you create a cache group after tracking of DDL statements has been enabled.

If you manually created the Oracle objects used to manage the caching of Oracle data, you need to run the ttIsql utility's cachesqlget command with the ORACLE_DDL_TRACKING option and the INSTALL flag as the cache manager user. This command should be run for each Oracle user that owns cached Oracle tables that you want to track DDL statements on. Running this command generates a SQL*Plus script used to create the TT_version_DDL_L table and TT_version_schema-ID_DDL_T trigger in the Oracle database.

After generating the script, use SQL*Plus to run the script as the sys user.

Example 7-6 Creating DDL tracking table and trigger when Oracle objects were manually created

In this example, the SQL*Plus script generated by the ttIsql utility's cachesqlget command is saved to the /tmp/trackddl.sql file. The owner of the cached Oracle table oratt is passed as an argument to the command.

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> cachesqlget ORACLE_DDL_TRACKING oratt INSTALL /tmp/trackddl.sql;
Command> exit

% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/trackddl
SQL> exit

If you need to issue DDL statements such as CREATE, DROP or ALTER on cached Oracle tables in order to make changes to the Oracle schema, perform the following tasks:

  1. Use DROP CACHE GROUP statements to drop all cache groups that cache the affected Oracle tables. If you are dropping an AWT cache group, use the ttRepSubscriberWait built-in procedure to make sure that all committed updates on the cache tables have been propagated to the cached Oracle tables before the cache group is dropped.

    % ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
    Command> call ttRepSubscriberWait('_AWTREPSCHEME','TTREP','_ORACLE','sys1',-1);
    
  2. Stop the cache agent.

  3. Make the desired changes to the Oracle schema.

  4. Use CREATE CACHE GROUP statements to re-create the cache groups, if feasible.

If you do not drop the affected cache groups before you modify the Oracle schema, operations on those cache groups such as automatic refresh may fail.

If you want to truncate an Oracle table that is cached in an automatic refresh cache group, perform the following tasks:

  1. Use an ALTER CACHE GROUP statement to set the cache group's automatic refresh state to PAUSED.

  2. Truncate the Oracle table.

  3. Manually refresh the cache group using a REFRESH CACHE GROUP statement without a WHERE or WITH ID clause.

Automatic refresh operations will resume after you refresh the cache group.

You can run the TimesTen_install_dir/oraclescripts/cacheInfo.sql SQL*Plus script as the cache administration user to display information about the Oracle objects used to track DDL statements issued on cached Oracle tables:

% cd TimesTen_install_dir/oraclescripts
% sqlplus cacheuser/oracle
SQL> @cacheInfo
*************DDL Tracking Object Information  ***************
Common DDL Log Table Name: TT_05_DDL_L
DDL Trigger Name: TT_05_315_DDL_T
Schema for which DDL Trigger is tracking: ORATT
Number of cache groups using the DDL Trigger: 10
****************************

The information returned for each Oracle user that owns cached Oracle tables includes the name of the DDL tracking table, the name of its corresponding DDL trigger, the name of the user that the DDL trigger is associated with, and the number of cache groups that cache a table owned by the user associated with the DDL trigger.

If a particular table is cached in more than one grid member, each grid member contributes to the cache group count. An active standby pair counts as one grid member. If a cache group contains more than one cache table, each cache table owned by the user associated with the DDL trigger contributes to the cache group count.

Oracle objects used to manage a caching environment

For an automatic refresh cache group, TimesTen creates a change log table and trigger in the Oracle database for each cache table in the cache group. The trigger is fired for each committed insert, update or delete operation on the cached Oracle table. The trigger records the primary key of the updated rows in the change log table. The cache agent periodically scans the change log table for updated keys and then joins this table with the cached Oracle table to get a snapshot of the latest updates.

The Oracle objects used to process automatic refresh operations can be automatically created by TimesTen as described in "Automatically create Oracle objects used to manage caching of Oracle data" when you create a cache group with the AUTOREFRESH MODE INCREMENTAL cache group attribute. Alternatively, you can manually create these objects as described in "Manually create Oracle objects used to manage caching of Oracle data" before performing any cache grid or cache group operation if, for security purposes, you do not want to grant the RESOURCE and CREATE ANY TRIGGER privileges to the cache administration user required to automatically create these objects.

Before the Oracle objects can be automatically or manually created, you must:

For each cache administration user, TimesTen creates the following Oracle tables, where version is an internal TimesTen version number and object-ID is the ID of the cached Oracle table:

Table Name Description
TT_version_AGENT_STATUS Created when the first cache group is created. Stores information about each Oracle table cached in an automatic refresh cache group.
TT_version_AR_PARAMS Created when the cache administration user name and password is set. Stores the action to take when the cache administration user's tablespace is full.
TT_version_CACHE_STATS Created when the cache administration user name and password is set.
TT_version_DATABASES Created when the cache administration user name and password is set. Stores the automatic refresh status for all TimesTen databases that cache data from the Oracle database.
TT_version_DB_PARAMS Created when the cache administration user name and password is set. Stores the cache agent timeout, recovery method for dead cache groups, and the cache administration user's tablespace usage threshold.
TT_version_DDL_L Created when the cache administration user name and password is set. Tracks DDL statements issued on cached Oracle tables.
TT_version_DDL_TRACKING Created when the cache administration user name and password is set. Stores a flag indicating whether tracking of DDL statements on cached Oracle tables is enabled or disabled.
TT_version_REPACTIVESTANDBY Created when the first AWT cache group is created. Tracks the state and roles of TimesTen databases containing cache tables in an AWT cache group that are replicated in an active standby pair replication scheme.
TT_version_REPPEERS Created when the first AWT cache group is created. Tracks the time and commit sequence number of the last update on the cache tables that was asynchronously propagated to the cached Oracle tables.
TT_version_SYNC_OBJS Created when the first cache group is created.
TT_version_USER_COUNT Created when the first cache group is created. Stores information about each cached Oracle table.
TT_version_object-ID_L One change log table is created per Oracle table cached in an automatic refresh cache group when the cache group is created. Tracks updates on the cached Oracle table.

For each cache administration user, TimesTen creates the following Oracle triggers, where version is an internal TimesTen version number, object-ID is the ID of the cached Oracle table, and schema-ID is the ID of user who owns the cached Oracle table:

Trigger Name Description
TT_version_REPACTIVESTANDBY_T Created when the first AWT cache group is created. When fired, inserts rows into the TT_version_REPACTIVESTANDBY table.
TT_version_object-ID_T One trigger is created per Oracle table cached in an automatic refresh cache group when the cache group is created. Fired for each insert, delete or update operation issued on the cached Oracle table to track operations in the TT_version_object-ID_L change log table.
TT_version_schema-ID_DDL_T One trigger for each user who owns cached Oracle tables. Created when a cache group is created after tracking of DDL statements has been enabled. Fired for each DDL statement issued on a cached Oracle table to track operations in the TT_version_DDL_L table.

The Oracle objects used to process asynchronous writethrough operations can be automatically created by TimesTen as described in "Automatically create Oracle objects used to manage caching of Oracle data" when you create an AWT cache group. Alternatively, you can manually create these objects as described in "Manually create Oracle objects used to manage caching of Oracle data" before performing any cache grid or cache group operation if, for security purposes, you do not want to grant the RESOURCE privilege to the cache administration user required to automatically create these objects.

For the timesten user, TimesTen creates the following Oracle tables:

Table Name Description
TT_GRIDID Created by running the SQL*Plus script initCacheGlobalSchema.sql. Stores the ID number assigned to the most recently created cache grid.
TT_GRIDINFO Created by running the SQL*Plus script initCacheGlobalSchema.sql. Stores the grid name, grid ID, and name of the cache administration user for all existing cache grids.

For each cache administration user, TimesTen creates the following Oracle tables, where version is an internal TimesTen version number and grid-ID is the ID number of the cache grid:

Table Name Description
TT_version_grid-name_grid-IDCGNODEID One table is created per cache grid when a grid is created. Stores the operating system name and version, and TimesTen release number.
TT_version_grid-name_grid-IDCGNODEINFO One table is created per cache grid when a grid is created. Stores the host name, member name, IP address, and cache agent TCP/IP port of all attached grid members.
TT_version_grid-name_grid-IDCGGROUPDEFS One table is created per cache grid when a grid is created. Stores the cache group name, owner, reference count and SQL text of all global cache groups in standalone TimesTen databases or active standby pairs that are associated with the cache grid.

Impact of failed automatic refresh operations on TimesTen databases

A change log table is created in the cache administration user's tablespace for each Oracle table that is cached in an automatic refresh cache group. For each update operation issued on these cached Oracle tables, a row is inserted into their change log table to keep track of updates that need to be applied to the TimesTen cache tables upon the next incremental automatic refresh cycle. TimesTen periodically deletes rows in the change log tables that have been applied to the cache tables.

An Oracle table cannot be cached in more than one cache group within a TimesTen database. However, an Oracle table can be cached in more than one TimesTen database which will result in an Oracle table corresponding to multiple TimesTen cache tables. If updates on cached Oracle tables are not being automatically refreshed into all of their corresponding cache tables because the cache agent is not running on one or more of the TimesTen databases that the Oracle tables are cached in, rows in their change log tables will not be deleted by default. The cache agent may not be running on a particular TimesTen database because the agent was explicitly stopped or never started, the database was destroyed, or the installed instance that the database resides in is down. As a result, rows will accumulate in the change log tables and degrade the performance of automatic refresh operations on cache tables in TimesTen databases where the cache agent is running. This can also cause the cache administration user's tablespace to fill up.

You can set a cache agent timeout to prevent rows from accumulating in the change log tables and not getting deleted. The following criteria must be met in order for TimesTen to delete rows in the change log tables when the cache agent is not running on a TimesTen database and a cache agent timeout is set:

Call the ttCacheConfig built-in procedure as the cache manager user from any of the TimesTen databases that cache data from the Oracle database. Pass the AgentTimeout string to the Param parameter and the timeout setting as a numeric string to the Value parameter. Do not pass in any values to the tblOwner and tblName parameters as they are not applicable to setting a cache agent timeout.

Example 7-7 Setting a cache agent timeout

In the following example, the cache agent timeout is set to 900 seconds (15 minutes):

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheConfig('AgentTimeout',,,'900');

To determine the current cache agent timeout setting, call ttCacheConfig passing only the AgentTimeout string to the Param parameter:

Command> call ttCacheConfig('AgentTimeout');
< AgentTimeout, <NULL>, <NULL>, 900 >

The default cache agent timeout is 0 seconds which means rows in the change log tables will not be deleted until they have been applied to all its cache tables. If you set the cache agent timeout to a value between 1 and 600 seconds, the timeout will be set to 600 seconds. The cache agent timeout applies to all TimesTen databases that cache data from the same Oracle database and have the same cache administration user name setting.

When determining a proper cache agent timeout setting, consider the time it takes to load the TimesTen database into memory, the time to start the cache agent process, potential duration of network outages, and anticipated duration of planned maintenance activities.

Each TimesTen database, and all of its automatic refresh cache groups have an automatic refresh status to determine whether any deleted rows from the change log tables were not applied to the cache tables in the cache groups. If rows were deleted from the change log tables and not applied to some cache tables because the cache agent on the database was down for a period of time that exceeded the cache agent timeout, those cache tables are no longer synchronized with the cached Oracle tables. Subsequent updates on the cached Oracle tables will not be automatically refreshed into the cache tables until the accompanying cache group is recovered.

The following are the possible automatic refresh statuses for an automatic refresh cache group:

The following are the possible automatic refresh statuses for a TimesTen database:

If the cache agent on a TimesTen database is down for a period of time that exceeds the cache agent timeout, the automatic refresh status of the database is set to dead. Also, the automatic refresh status of all automatic refresh cache groups within that database are set to dead.

If you have enabled SNMP traps, a trap will be thrown when the automatic refresh status of a database is set to dead.

Call the ttCacheDbCgStatus built-in procedure as the cache manager user to determine the automatic refresh status of a cache group and its accompanying TimesTen database. Pass the owner of the cache group to the cgOwner parameter and the name of the cache group to the cgName parameter.

Example 7-8 Determining the automatic refresh status of a cache group and TimesTen database

In the following example, the automatic refresh status of the database is alive and the automatic refresh status of the cacheuser.customer_orders read-only cache group is ok:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheDbCgStatus('cacheuser','customer_orders');
< alive, ok >

To view only the automatic refresh status of the database and not of a particular cache group, call ttCacheDbCgStatus without any parameters:

Command> call ttCacheDbCgStatus;
< dead, <NULL> >

If the automatic refresh status of a cache group is ok, its cache tables are being automatically refreshed based on its automatic refresh interval. If the automatic refresh status of a database is alive, the automatic refresh status of all its automatic refresh cache groups are ok.

If the automatic refresh status of a cache group is dead, its cache tables are no longer being automatically refreshed when updates are committed on the cached Oracle tables. The cache group must be recovered in order to resynchronize the cache tables with the cached Oracle tables.

You can configure a recovery method for cache groups whose automatic refresh status is dead.

Call the ttCacheConfig built-in procedure as the cache manager user from any of the TimesTen databases that cache data from the Oracle database. Pass the DeadDbRecovery string to the Param parameter and the recovery method as a string to the Value parameter. Do not pass in any values to the tblOwner and tblName parameters as they are not applicable to setting a recovery method for dead cache groups.

The following are the valid recovery methods:

Example 7-9 Configuring the recovery method for dead cache groups

In the following example, the recovery method is set to Manual for cache groups whose automatic refresh status is dead:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheConfig('DeadDbRecovery',,,'Manual');

To determine the current recovery method for dead cache groups, call ttCacheConfig passing only the DeadDbRecovery string to the Param parameter:

Command> call ttCacheConfig('DeadDbRecovery');
< DeadDbRecovery, <NULL>, <NULL>, manual >

The recovery method applies to all automatic refresh cache groups in all TimesTen databases that cache data from the same Oracle database and have the same cache administration user name setting.

If you have enabled SNMP traps, a trap will be thrown when the cache agent starts and the recovery method is set to Manual or None to alert you to manually issue a statement such as REFRESH CACHE GROUP or DROP CACHE GROUP in order to recover cache groups in the database whose automatic refresh status is dead.

When a cache group begins the recovery process, its automatic refresh status is changed from dead to recovering, and the status of the accompanying TimesTen database is changed to recovering, if it is currently dead.

After the cache group has been recovered, its automatic refresh status is changed from recovering to ok. Once all cache groups have been recovered and their automatic refresh status are ok, the status of the accompanying TimesTen database is changed from recovering to alive.

A full automatic refresh operation requires more system resources to process than an incremental automatic refresh operation when there is a small volume of updates to refresh and a large number of rows in the cache tables. If you need to bring a TimesTen database down for maintenance activities and the volume of updates anticipated during the downtime on the Oracle tables that are cached in automatic refresh cache groups is small, you can consider temporarily setting the cache agent timeout to 0. When the database is brought back up and the cache agent restarted, incremental automatic refresh operations will resume on cache tables in automatic refresh cache groups. Full automatic refresh operations are avoided because the automatic refresh status on the accompanying cache groups were not changed from ok to dead so those cache groups do not need to go through the recovery process. Make sure to set the cache agent timeout back to its original value once the database is back up and the cache agent has been started.

Dropping Oracle objects used by automatic refresh cache groups

If a TimesTen database that contains automatic refresh cache groups becomes unavailable, Oracle objects such as change log tables and triggers used to implement automatic refresh operations continue to exist in the Oracle database. A TimesTen database is unavailable, for example, when the TimesTen system is taken offline or the database has been destroyed without dropping its automatic refresh cache groups.

Oracle objects used to implement automatic refresh operations also continue to exist in the Oracle database when a TimesTen database is no longer being used but still contains automatic refresh cache groups. Rows continue to accumulate in the change log tables. This impacts automatic refresh performance on other TimesTen databases. Therefore, it is desirable to drop these Oracle objects associated with the unavailable or abandoned TimesTen database.

Run the TimesTen_install_dir/oraclescripts/cacheCleanUp.sql SQL*Plus script as the cache administration user to drop the Oracle objects used to implement automatic refresh operations. The host name of the TimesTen system and the TimesTen database path name are passed as arguments to the cacheCleanUp.sql script. You can run the cacheInfo.sql script as the cache administration user to determine the host name of the TimesTen system and the database path name. The cacheInfo.sql script can also be used to determine whether any objects used to implement automatic refresh operations exist in the Oracle database.

Example 7-10 Dropping Oracle objects for automatic refresh cache groups

In the following example, the TimesTen database still contained one read-only cache group customer_orders with cache tables oratt.customer and oratt.orders when the database was dropped. The cacheCleanUp.sql script drops the change log tables and triggers associated with the two cache tables.

% cd TimesTen_install_dir/oraclescripts
% sqlplus cacheuser/oracle
SQL> @cacheCleanUp "sys1" "/users/OracleCache/alone1"

*****************************OUTPUT**************************************
Performing cleanup for object_id: 69959 which belongs to table : CUSTOMER
Executing: delete from tt_05_agent_status where host = sys1 and datastore =
/users/OracleCache/alone1 and object_id = 69959
Executing: drop table tt_05_69959_L
Executing: drop trigger tt_05_69959_T
Executing: delete from tt_05_user_count where object_id = object_id1
Performing cleanup for object_id: 69966 which belongs to table : ORDERS
Executing: delete from tt_05_agent_status where host = sys1 and datastore =
/users/OracleCache/alone1 and object_id = 69966
Executing: drop table tt_05_69966_L
Executing: drop trigger tt_05_69966_T
Executing: delete from tt_05_user_count where object_id = object_id1
**************************************************************************

Monitoring the cache administration user's tablespace

By default, when the cache administration user's tablespace is full, an error is returned to the application from Oracle when an update operation such as an UPDATE, INSERT or DELETE statement is issued on a particular cached Oracle table.

Rather than return an error to the application when the cache administration user's tablespace is full, you can configure TimesTen to delete existing rows from the change log tables to make space for new rows when an update operation is issued on a particular cached Oracle table. If some of the deleted change log table rows have not been applied to the TimesTen cache tables, a full automatic refresh operation will be performed on those cache tables in each TimesTen database that contains the tables upon the next automatic refresh cycle.

Call the ttCacheConfig built-in procedure as the cache manager user from any of the TimesTen databases that cache tables from the Oracle database. Pass the TblSpaceFullRecovery string to the Param parameter, the owner and name of the cached Oracle table to the tblOwner and tblName parameters, respectively, on which you want to configure an action to take if the cache administration user's tablespace becomes full, and the action itself as a string to the Value parameter.

The following are the valid actions:

Example 7-11 Configuring an action when the cache administration user's tablespace becomes full

In the following example, rows will be deleted from the change log table and a full automatic refresh operation will be performed on the cache table upon the next automatic refresh cycle when an update operation is issued on the oratt.customer cached Oracle table while the cache administration user's tablespace is full:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheConfig('TblSpaceFullRecovery','oratt','customer','Reload');

To determine the current action to take when an update operation is issued on a particular cached Oracle table if the cache administration user's tablespace is full, call ttCacheConfig passing only the TblSpaceFullRecovery string to the Param parameter, and the owner and name of the cached Oracle table to the tblOwner and tblName parameters, respectively:

Command> call ttCacheConfig('TblSpaceFullRecovery','oratt','customer');
< TblSpaceFullRecovery, ORATT, CUSTOMER, reload >

The action to take when update operations are issued on a cached Oracle table while the cache administration user's tablespace is full applies to all TimesTen databases that cache tables from the same Oracle database and have the same cache administration user name setting,

If you have enabled SNMP traps, a trap will be thrown when an update operation is issued on a cached Oracle table and the cache administration user's tablespace is full.

You can configure TimesTen to return a warning to the application when an update operation such as an UPDATE, INSERT or DELETE statement is issued on cached Oracle tables and causes the usage of the cache administration user's tablespace to exceed a specified threshold.

Call the ttCacheConfig built-in procedure as the cache manager user from any of the TimesTen databases that cache tables from the Oracle database. Pass the TblSpaceThreshold string to the Param parameter and the threshold as a numeric string to the Value parameter. The threshold value represents the percentage of space used in the cache administration user's tablespace upon which a warning is returned to the application when an update operation is issued on a cached Oracle table. Do not pass in any values to the tblOwner and tblName parameters as they are not applicable to setting a warning threshold for the usage of the cache administration user's tablespace.

The cache administration user must be granted the SELECT privilege on the Oracle SYS.DBA_DATA_FILES table in order for the cache manager user to set a warning threshold on the cache administration user's tablespace usage, and for the cache administration user to monitor its tablespace to determine if the configured threshold has been exceeded.

Example 7-12 Setting a cache administration user's tablespace usage warning threshold

The following example configures a warning to be returned to the application that issues an update operation on a cached Oracle table if it results in the usage of the cache administration user's tablespace to exceed 80 percent:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheConfig('TblSpaceThreshold',,,'80');

To determine the current cache administration user's tablespace usage warning threshold, call ttCacheConfig passing only the TblSpaceThreshold string to the Param parameter:

Command> call ttCacheConfig('TblSpaceThreshold');
< TblspaceThreshold, <NULL>, <NULL>, 80 >

The default cache administration user's tablespace usage warning threshold is 0 percent which means that no warning is returned to the application regardless of the tablespace usage. The cache administration user's tablespace usage warning threshold applies to all TimesTen databases that cache tables from the same Oracle database and have the same cache administration user name setting.

If you have enabled SNMP traps, a trap will be thrown when the cache administration user's tablespace usage has exceeded the configured threshold.