1. Cache Guide
  2. Managing a Caching Environment
  3. Impact of Failed Autorefresh Operations on TimesTen Databases

Impact of Failed Autorefresh Operations on TimesTen Databases

TimesTen does not delete rows in the change log tables when the cache agent is not running on a TimesTen database. In this case, you can set a cache agent timeout to prevent rows from accumulating in the change log tables.

A change log table is created in the Oracle cache administration user's tablespace for each Oracle Database table that is cached in a cache group with autorefresh. For each update operation issued on these cached Oracle Database 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 autorefresh cycle. TimesTen periodically deletes rows in the change log tables that have been applied to the cache tables.

An Oracle Database table cannot be cached in more than one cache group within a TimesTen database. However, an Oracle Database table can be cached in more than one TimesTen database. This results in an Oracle Database table corresponding to multiple TimesTen cache tables. If updates on cached Oracle Database 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 Database tables are cached in, rows in their change log tables are not deleted by default. The cache agent may not be running on a particular TimesTen database because the agent was either stopped or never started, the database was destroyed, or the TimesTen instance is down. As a result, rows accumulate in the change log tables and degrade the performance of autorefresh operations on cache tables in TimesTen databases where the cache agent is running. This can also cause the Oracle cache administration user's tablespace to fill up.

For example, if a single Oracle Database table is cached by two or more TimesTen databases where one of the TimesTen databases is unable to connect to the Oracle database, then autorefresh for the disconnected TimesTen database is not performed. Instead, the records in the change log table accumulate (so that the disconnected TimesTen database can catch up once a connection to the Oracle database is established). If the AgentTimeout parameter is set to 0 (the default), then all change log records are kept indefinitely until they have been applied to all its cache tables. The change log records of the other TimesTen databases are not purged even though the transaction logs are already applied to the local TimesTen database. Alternatively, you can set the AgentTimeout parameter to define a specific timeout to wait before purging the saved change log records and stop the accumulation of these change log records.

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:

  • Oracle Database tables are cached in cache groups with autorefresh enabled within more than one TimesTen database.

  • The cache agent is running on at least one of the TimesTen databases but is not running on at least another database.

  • Rows in the change log tables have been applied to the cache tables on all TimesTen databases where the cache agent is running.

  • For those databases where the cache agent is not running, the agent process has been down for a period of time that exceeds the cache agent timeout.

To set the cache agent timeout and prevent rows from accumulating in the change log tables, set the AgentTimeout parameter with the ttCacheConfig built-in procedure as the TimesTen cache administration 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.

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

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
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 setting is 0, which means that all change log records are kept indefinitely 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 is 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 Oracle 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 cache groups have an autorefresh 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 Database tables. Subsequent updates on the cached Oracle Database tables are not automatically refreshed into the cache tables until the accompanying cache group is recovered.

The following are the possible statuses for a cache group with autorefresh:

  • ok: All of the deleted rows from the change log tables were applied to its cache tables. Incremental autorefresh operations continue to occur on the cache group.

  • disabled or dead: Some of the deleted rows from the change log tables were not applied to its cache tables so the cache tables are not synchronized with the cached Oracle Database tables. Autorefresh operations have ceased on the cache group and do not resume until the cache group has been recovered.

  • recovering: The cache group is being recovered. Once recovery completes, the cache tables are synchronized with the cached Oracle Database tables, the cache group's autorefresh status is set to ok, and incremental autorefresh operations resume on the cache group.

The following are the possible autorefresh statuses for a TimesTen database:

  • alive: All of its cache groups with autorefresh have an autorefresh status of OK.

  • dead: All of its cache groups with autorefresh have an autorefresh status of dead.

  • recovering: At least one of its cache groups with autorefresh have an autorefresh status of recovering.

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

Call the ttCacheDbCgStatus built-in procedure as the TimesTen cache administration user to determine the autorefresh 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.

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

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheDbCgStatus('cacheadmin','customer_orders');
< alive, ok >

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

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

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

If the autorefresh status of a cache group is disabled or dead, its cache tables are no longer being automatically refreshed when updates are committed on the cached Oracle Database tables. The cache group must be recovered in order to resynchronize the cache tables with the cached Oracle Database tables. See Disabling Full Autorefresh for Cache Groups.

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

Call the ttCacheConfig built-in procedure as the TimesTen cache administration 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:

  • Normal: When the cache agent starts, a full autorefresh operation is performed on cache groups whose autorefresh status is dead in order to recover those cache groups. This is the default recovery method. However, if you set the DisableFullAutorefresh cache configuration parameter to 1, then the DeadDbRecovery cache configuration parameter automatically changes to Manual.

  • Manual: For each static cache group whose autorefresh status is dead, a REFRESH CACHE GROUP statement must be issued in order to recover these cache groups after the cache agent starts.

    For each dynamic cache group whose autorefresh status is dead, a REFRESH CACHE GROUP or UNLOAD CACHE GROUP statement must be issued in order to recover these cache groups after the cache agent starts.

  • None: Cache groups whose autorefresh status is dead must be dropped and then re-created after the cache agent starts in order to recover them.

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

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
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 cache groups with autorefresh in all TimesTen databases that cache data from the same Oracle database and have the same Oracle cache administration user name setting.

When a cache group begins the recovery process, its autorefresh 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 autorefresh status is changed from recovering to ok. Once all cache groups have been recovered and their autorefresh statuses are ok, the status of the accompanying TimesTen database is changed from recovering to alive.

A full autorefresh operation requires more system resources to process than an incremental autorefresh 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 Database tables that are cached in cache groups with autorefresh 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 autorefresh operations resumes on cache tables in cache groups with autorefresh. Full autorefresh operations are avoided because the autorefresh 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.

See ttCacheConfig in the Oracle TimesTen In-Memory Database Reference.