6 Managing a Caching Environment

Cache agent and replication connections

When a connection from the cache agent to the Oracle database fails, the cache agent attempts to connect every 10 seconds. If the cache agent cannot connect to the Oracle database, the cache agent restarts after 10 minutes. This behavior repeats forever.

When a connection from the replication agent to the Oracle database fails, the replication agent attempts to reconnect to the Oracle database after 120 seconds. If it cannot reconnect after 120 seconds, the replication agent stops and does not restart.

If Fast Application Notification (FAN) is enabled on the Oracle database, the cache agent and the replication agent receive immediate notification of connection failures. If FAN is not enabled, the agents may wait until a TCP timeout occurs before becoming aware that the connection has failed.

If the Oracle Real Application Clusters (Oracle RAC) is enable on the Oracle database, along with FAN and Transparent Application Failover (TAF), then TAF manages the connection to a new Oracle Database instance. See Chapter 9, "Using TimesTen Cache in an Oracle RAC Environment".

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.

% ttIsql "DSN=cache1;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 (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

• Autorefresh 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 autorefresh operations on cache groups

TimesTen Classic offers several mechanisms to obtain information and statistics about autorefresh operations on cache groups. See "Monitoring autorefresh cache groups" in Oracle TimesTen In-Memory Database Troubleshooting Guide.

Monitoring AWT cache groups

TimesTen Classic offers several mechanisms to obtain information and statistics about operations in AWT cache groups. See "AWT performance monitoring" in Oracle TimesTen In-Memory Database Troubleshooting 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 Database tables and which updates have not. If updates are not being automatically propagated to the Oracle database because of a failure, transaction log files accumulate on the file system. Examples of a failure that prevents propagation are that the replication agent is not running or the Oracle database server is unavailable. For more information about accumulation of transaction log files, see "Monitoring accumulation of transaction log files" in Oracle TimesTen In-Memory Database Operations Guide.

You can call the ttCacheAWTThresholdSet built-in procedure as the cache manager user to set a threshold for the number of transaction log files that can accumulate before TimesTen Classic 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.

After the threshold has been exceeded, you need to manually synchronize the cache tables with the cached Oracle Database 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 Database tables.

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

Command> CALL ttCacheAWTThresholdGet;
< 5 >
Command> exit

Tracking DDL statements issued on cached Oracle Database tables

When a DDL statement is issued on a cached Oracle Database table, this statement can be tracked in the Oracle Database TT_version_DDL_L table when the Oracle Database TT_version_schema-ID_DDL_T trigger is fired to insert a row into the table, where version is an internal TimesTen Classic version number and schema-ID is the ID of user that owns the cached Oracle Database table. A trigger is created for each Oracle Database user that owns cached Oracle Database tables. One DDL tracking table is created to store DDL statements issued on any cached Oracle Database 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 Database 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 "ttCacheDDLTrackingConfig" in Oracle TimesTen In-Memory Database Reference.

% ttIsql "DSN=cache1;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 Database objects are created when you create a cache group after tracking of DDL statements has been enabled.

If you manually created the Oracle Database objects used to manage the caching of Oracle Database 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 Database user that owns cached Oracle Database 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.

% ttIsql "DSN=cache1;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

When you need to issue DDL statements such as CREATE, DROP or ALTER on cached Oracle Database tables in order to make changes to the Oracle Database schema, drop the affected cache groups before you modify the Oracle Database schema. Otherwise operations such as autorefresh may fail. You do not need to drop cache groups if you are altering the Oracle Database table to add a column. To issue other DDL statements for Oracle Database tables, first perform the following tasks:

1. Use DROP CACHE GROUP statements to drop all cache groups that cache the affected Oracle Database 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 Database tables before the cache group is dropped.

   % ttIsql "DSN=cache1;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 Database schema.

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

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

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

2. Truncate the Oracle Database table.

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

Autorefresh operations resume after you refresh the cache group.

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

% cd timesten_home/install/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 Database user that owns cached Oracle Database 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 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.

Defragmenting change log tables in the tablespace

Prolonged use or a heavy workload of the change log tables for autorefresh cache groups can result in fragmentation of the tablespace. In order to prevent degradation of the tablespace from fragmentation of the change log tables, TimesTen Classic calculates the percentage of fragmentation for the change log tables as a ratio of used space to the total size of the space. If this ratio falls below a defined threshold, TimesTen alerts you of the necessity for defragmentation of the change log tables by logging a message. By default, this threshold is set to 40%. You can configure what the fragmentation threshold should be with the ttCacheConfig built-in procedure.

To set the fragmentation threshold, 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 AutoRefreshLogFragmentationWarningPCT string to the Param parameter and the threshold setting as a numeric string to the Value parameter.

To set the time interval for how often to perform the calculation of the fragmentation percentage, 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 AutorefreshLogMonitorInterval string to the Param parameter and the time interval (in seconds) as a numeric string to the Value parameter.

% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheConfig('AutoRefreshLogFragmentationWarningPCT',,,'50');
< AutoRefreshLogFragmentationWarningPCT, <NULL>, <NULL>, 50 >
1 row found.
Command> CALL ttCacheConfig('AutorefreshLogMonitorInterval',,,'3600');
< AutorefreshLogMonitorInterval, <NULL>, <NULL>, 3600 >
1 row found.

To determine the current fragmentation threshold setting, call ttCacheConfig passing the AutoRefreshLogFragmentationWarningPCT string to the Param parameter:

Command> CALL ttCacheConfig('AutoRefreshLogFragmentationWarningPCT');
< AutoRefreshLogFragmentationWarningPCT, <NULL>, <NULL>, 50 >

You can either configure TimesTen to perform defragmentation automatically or manually initiate defragmentation. To configure what action is taken when the ratio falls below the fragmentation threshold, call the ttCacheConfig built-in procedure with the AutoRefreshLogDeFragmentAction string to the Param parameter and the desired action as the Value parameter as follows:

• Manual. This is the default. No action is taken to defragment the change log tables. Any defragmentation must be performed manually by executing the ttCacheAutoRefreshLogDeFrag built-in procedure. See "Manually defragmenting the change log tables for autorefresh cache groups" for more information.

• Compact: TimesTen defragments the change log tables.

• CompactAndReclaim: TimesTen defragments the change log tables and reclaims the space.

  Note:

  When reclaiming space, the change log table is briefly locked, which temporarily suspends writing into the base table.

% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheConfig('AutoRefreshLogDeFragmentAction',,,'CompactAndReclaim');
< AutoRefreshLogDeFragmentAction, <NULL>, <NULL>, compactandreclaim >
1 row found.

Command> CALL ttCacheConfig('AutoRefreshLogDeFragmentAction');
< AutoRefreshLogDeFragmentAction , <NULL>, <NULL>, compactandreclaim >

You can discover the fragmentation percentage of the tablespace and when the last defragmentation operation was performed with the following returned columns from the ttCacheAutorefreshStatsGet built-in procedure:

• AutoRefreshLogFragmentationPCT: The current fragmentation percentage for the tablespace.

• AutoRefreshLogFragmentationTS: The timestamp of when the last fragmentation percentage was calculated.

• autorefLogDeFragCnt: The count for how many times the tables in this particular cache group have been defragmented.

For more details, see "ttCacheAutorefreshStatsGet" in the Oracle TimesTen In-Memory Database Reference.

% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheAutoRefreshLogDeFrag('CompactAndReclaim');

Receiving notification on tablespace usage

In order to avoid the tablespace becoming full, you can configure TimesTen Classic to return a warning to the application when an update operation such as an UPDATE, INSERT or DELETE statement is issued on cached Oracle Database 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 AutoRefreshLogTblSpaceUsagePCT 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 Database 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 Database 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.

% ttIsql "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheConfig('AutoRefreshLogTblSpaceUsagePCT',,,'80');

Command> CALL ttCacheConfig('AutoRefreshLogTblSpaceUsagePCT');
< AutoRefreshLogTblSpaceUsagePCT, <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.

Recovering from a full tablespace

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

Rather than TimesTen returning an error to the Oracle Database 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 Database table. If some of the deleted change log table rows have not been applied to the TimesTen cache tables, a full autorefresh operation is performed on those cache tables in each TimesTen database that contains the tables upon the next autorefresh 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 Database 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:

• None: Return an Oracle Database error to the application when an update operation is issued on the cached Oracle Database table. This is the default action.

• Reload: Delete rows from the change log table and perform a full autorefresh operation on the cache table upon the next autorefresh cycle when an update operation is issued on the cached Oracle Database table.

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

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

The action to take when update operations are issued on a cached Oracle Database 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.

Backing up and restoring using the ttBackup and ttRestore utilities

When you use the ttBackup utility, it backs up the TimesTen database with all of its data at a particular time. Thus, if you want to use these cache groups again, restoring this backup requires additional action as the restored data within the cache groups are out of date and out of sync with the data in the backend Oracle database. See "Backup, Restore, and Migrate Data in TimesTen Classic" in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.

If the restored database connects to a different backend Oracle database than what it had originally connected with and you want to use the ttBackup and ttRestore utilities to backup and restore your database, then perform the following:

1. Execute the ttBackup utility command to backup the database and its objects into a binary file. For example, to backup the cache1 database using the /tmp/dump directory for temporary storage:

   % ttBackup -dir /tmp/dump -connstr "DSN=cache1"
   

2. Drop all cache groups and destroy the database. Since the database still exists with its cache groups, drop the cache groups and then destroy the database before restoring in the same or another location.

   % ttIsql -connstr "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
   Command> call ttCacheStop;
   Command> DROP CACHE GROUP readcache;
   Command> exit;
   Disconnecting...
   Done.
   
   % ttDestroy cache1
   

3. Restore the database with the ttRestore utility and then delete the temporary directory.

   % ttRestore -dir /tmp/dump -connstr "DSN=cache1"
   Restore started ...
   Restore complete
   
   % rm -r /tmp/dump
   

4. In order to re-synchronize the data within the cache groups, you must drop and recreate the cache groups:

   1. Connect to the TimesTen database.

   2. Drop the cache groups that were restored with the ttRestore utility. Because the data is out of sync, you may see errors.

   3. Specify the cache administrator user name and password with the ttCacheUidPwdSet built-in procedure.

   4. Start the cache agent.

   5. Recreate and, if required, reload the cache groups.

   % ttIsql -connstr "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
   
   Command> DROP CACHE GROUP readcache;
   Command> call ttCacheUidPwdSet('cacheuser','oracle');
   Command> call ttCacheStart;
   Command> CREATE READONLY CACHE GROUP readcache
           AUTOREFRESH INTERVAL 5 SECONDS
           FROM oratt.readtab
           (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
   Command> LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
   2 cache instances affected.
   

Backing up and restoring with the ttMigrate utility

The ttMigrate utility saves tables and indexes from a TimesTen database into a binary file. When a cache group is migrated and included in the binary file, it includes the cache group definition and schema; however, the data of the cache group is not migrated.

If the restored database connects to a different backend Oracle database than what it had originally connected with and you want to use the ttMigrate utility for backing up and restoring the database, then perform the following:

1. Execute the ttMigrate -c utility command to save the database and its objects into a binary file.

   % ttMigrate -c "DSN=cache1" cache1.ttm
   ...
   Saving user CACHEUSER
   User successfully saved.
   
   Saving user ORATT
   User successfully saved.
    
   Saving table CACHEUSER.READTAB
     Saving rows...
     2/2 rows saved.
   Table successfully saved.
    
   Saving cache group CACHEUSER.READCACHE
     Saving cached table ORATT.READTAB
   Cache group successfully saved.
   

2. Drop all cache groups and destroy the TimesTen database:

   1. Stop the cache agent.

   2. Drop all cache groups. You may see errors reported, which can be ignored. When you drop all cache groups before destroying the TimesTen database, all metadata on the Oracle Database for these cache groups is deleted.

   3. Destroy the TimesTen database.

      Command> call ttCacheStop;
      Command> DROP CACHE GROUP readcache;
      Command> exit
      Disconnecting...
      Done.
      % ttDestroy cache1
      

3. Create and restore the database:

   1. Create the TimesTen database with a first connection request.

   2. Create the TimesTen cache table user and the TimesTen cache manager user. Grant appropriate privileges to these users.

      Note:

      Depending on which TimesTen Classic release you are migrating from, the users and privileges may or may not be migrated. See "ttMigrate" in the Oracle TimesTen In-Memory Database Reference for more information.

   3. Restore the database from the saved binary file with the ttMigrate -r utility command.

      % ttIsql cache1
      Command> CREATE USER cacheuser IDENTIFIED BY timesten;
       User created.
       
      Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
      Command> CREATE USER oratt IDENTIFIED BY timesten; 
      User created.
       
      Command> exit
      Disconnecting...
      Done.
      
      % ttMigrate -r -relaxedUpgrade -cacheuid cacheuser -cachepwd oracle
       -connstr "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
       cache1.ttm
      ...
      Restoring table CACHEUSER.READTAB
        Restoring rows...
        2/2 rows restored.
      Table successfully restored.
       
      Restoring cache group CACHEUSER.READCACHE
        Restoring cached table ORATT.READTAB
        1/1 cached table restored.
      Cache group successfully restored.
      

4. Connect to the restored database and reset the cache autorefresh state:

   1. Connect to the TimesTen database with ttIsql.

   2. Specify the cache administrator user name and password with the ttCacheUidPwdSet built-in procedure.

   3. Start the cache agent.

   4. Alter the cache groups to set autorefresh state to ON.

      % ttIsql -connstr "DSN=cache1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
      Command> call ttCacheUidPwdSet('cacheuser','oracle');
      Command> call ttCacheStart;
      Command> ALTER CACHE GROUP readcache SET AUTOREFRESH STATE ON;