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:
Impact of failed automatic refresh operations on TimesTen databases
Dropping Oracle objects used by automatic refresh cache groups
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:
The names of the cache agent process threads that are connected to the TimesTen database
The names of the replication agent process threads that are connected to the TimesTen database
Status on whether the cache agent is running
Status on whether the replication agent is running
The cache agent start policy
The replication agent start policy
For more information about the ttStatus utility, see "ttStatus" in Oracle TimesTen In-Memory Database Reference.
The following sections describe how to obtain information about cache grids and cache groups, and how to monitor the status of cache group operations:
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.
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:
Call the ttCacheAutorefreshStatsGet built-in procedure to obtain information about the last ten automatic refresh operations on a specified cache group:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheAutorefreshStatsGet('cacheuser','recent_shipped_orders');
The information returned includes the start time and duration of each automatic refresh operation, and the number of rows that were refreshed into the cache tables during each operation.
For more information about the ttCacheAutorefreshStatsGet built-in procedure, see "ttCacheAutorefreshStatsGet" in Oracle TimesTen In-Memory Database Reference and "Using the ttCacheAutorefreshStatsGet procedure" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.
Run the TimesTen_install_dir/oraclescripts/cacheInfo.sql SQL*Plus script as the cache administration user to display change log table information for all Oracle tables cached in an automatic refresh cache group:
% cd TimesTen_install_dir/oraclescripts
% sqlplus cacheuser/oracle
SQL> @cacheInfo
*************Autorefresh Objects Information ***************
Host name: sys1
Timesten datastore name: /users/OracleCache/alone1
Cache table name: ORATT.ORDERS
Change log table name: tt_05_69245_L
Number of rows in change log table: 1
Maximum logseq on the change log table: 0
Timesten has autorefreshed updates upto logseq: 0
Number of updates waiting to be autorefreshed: 0
Number of updates that has not been marked with a valid logseq: 0
****************************
The information returned for each change log table includes the name of the change log table, the name of its corresponding TimesTen cache table, the number of rows in the change log table, and the number of updates in the change log table that have not been automatically refreshed into the cache table.
For more details about the information returned from the cacheInfo.sql script, see "Displaying information from the change log tables" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.
Use the ttDaemonLog utility to view informational, warning and error messages in the support log that pertain to automatic refresh operations:
% ttDaemonLog -show ora
For details about the ttDaemonLog utility, see "ttDaemonLog" in Oracle TimesTen In-Memory Database Reference.
For details about the automatic refresh operations messages in the support log, see "Understanding messages about autorefresh in the support log" and "Diagnosing autorefresh failure" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.
Use the ttTraceMon utility to generate trace records for the AUTOREFRESH component:
% ttTraceMon cachealone1 Trace monitor; empty line to exit Trace> level autorefresh 2 Trace> flush Trace> outfile autorefresh.out Trace> <Press Enter to exit ttTraceMon>
The information shown in the trace records include the name of the cache group that each automatic refresh operation was performed on, the duration of each operation, and the number of rows that were refreshed into the cache tables during each operation.
For more information about the ttTraceMon utility, see "ttTraceMon" in Oracle TimesTen In-Memory Database Reference.
For details about the automatic refresh trace records, see "AUTOREFRESH tracing" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.
Enable SNMP traps to be thrown by setting the -enabled flag to 1 in the TimesTen_install_dir/info/snmp.ini and monitor for the various traps from the TimesTen_install_dir/mibs/TimesTen-MIB.txt file that apply to automatic refresh error conditions.
For more information about SNMP traps and which traps pertain to automatic refresh operations, see "Diagnostics Through SNMP Traps" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps and "Using SNMP traps for alerts about autorefresh problems" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.
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:
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 "ttCacheAWTMonitorConfig" in Oracle TimesTen In-Memory Database Reference.
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.
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, transaction log files accumulate on disk. Examples of a failure that prevents propagation are that the replication agent is not running or the Oracle server is unavailable.
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.
After 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 stops 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
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 "ttGridInfo" in 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, indication of whether the node is attached to the grid, host name, node 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 "ttGridNodeStatus" in Oracle TimesTen In-Memory Database Reference.
You can use the ttGridGlobalCGSuspend built-in procedure to temporarily block these operations for global AWT cache groups:
Dynamic loading
Deleting cache instances
Use the ttGridGlobalCGResume built-in procedure to re-enable these operations.
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 "ttCacheDDLTrackingConfig" in 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
When 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, drop the affected cache groups before you modify the Oracle schema. Otherwise operations such as automatic refresh may fail. You do not need to drop cache groups if you are altering the Oracle table to add a column. To issue other DDL statements for Oracle tables, first perform the following tasks:
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);
Stop the cache agent.
Make the desired changes to the Oracle schema.
Use CREATE CACHE GROUP statements to re-create the cache groups, if feasible.
If you want to truncate an Oracle table that is cached in an automatic refresh cache group, perform the following tasks:
Use an ALTER CACHE GROUP statement to set the cache group's automatic refresh state to PAUSED.
Truncate the Oracle table.
Manually refresh the cache group using a REFRESH CACHE GROUP statement without a WHERE or WITH ID clause.
Automatic refresh operations 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.
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:
Create a cache administration user in the Oracle database as described in "Create the Oracle users".
Set the cache administration user name and password in the TimesTen database as described in "Set the cache administration user name and password".
Start the cache agent as described in "Managing the cache agent".
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. |
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. This results 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 are not 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 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:
Oracle tables are cached in automatic refresh cache groups 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
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 are not 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 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 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 are not 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:
ok: All of the deleted rows from the change log tables were applied to its cache tables. Incremental automatic refresh operations continue to occur on the cache group.
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 tables. Automatic refresh operations have ceased on the cache group and will 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 tables, the cache group's automatic refresh status is set to ok, and incremental automatic refresh operations resume on the cache group.
The following are the possible automatic refresh statuses for a TimesTen database:
alive: All of its automatic refresh cache groups have an automatic refresh status of ok
dead: All of its automatic refresh cache groups have an automatic refresh status of dead
recovering: At least one of its automatic refresh cache groups have an automatic refresh 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 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 is 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:
Normal: When the cache agent starts, a full automatic refresh operation is performed on cache groups whose automatic refresh status is dead in order to recover those cache groups. This is the default recovery method.
Manual: For each explicitly loaded cache group whose automatic refresh 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 automatic refresh 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 automatic refresh status is dead must be dropped and then re-created after the cache agent starts in order to recover them.
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 is 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 statuses 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 resumes 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.
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
**************************************************************************
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 is 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:
None: Return an Oracle error to the application when an update operation is issued on the cached Oracle table. This is the default action.
Reload: Delete rows from the change log table and perform a full automatic refresh operation on the cache table upon the next automatic refresh cycle when an update operation is issued on the cached Oracle table.
Example 7-11 Configuring an action when the cache administration user's tablespace becomes full
In the following example, rows are deleted from the change log table and a full automatic refresh operation is 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 is 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 is thrown when the cache administration user's tablespace usage has exceeded the configured threshold.
When a standalone database grid member fails, the cache agent automatically restarts if the cache agent start policy is manual or always. The grid member is automatically reattached to the grid when the database recovers. If the cache agent start policy is norestart, you must restart the cache agent and then call the ttGridAttach built-in procedure to reattach the member to the grid. See "Set a cache agent start policy".
You can verify that a standalone database grid member is attached to the grid by calling the ttRepStateGet built-in procedure. If it is attached, you should see this output:
Command> CALL ttRepStateGet; < IDLE, AVAILABLE > 1 row found.
If the active or the standby database node in an active standby pair grid member fails when Oracle Clusterware is managing the nodes in the grid, the grid node is automatically reattached to the grid when the cache agent restarts. For more information about how Oracle Clusterware handles failures, see "Recovering from failures" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.
If the active standby pair grid member is not managed by Oracle Clusterware, then perform the steps in "Recovering from a failure of the active database" or "Recovering from a failure of the standby database" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. If the cache agent start policy is manual or always, the grid node is automatically reattached to the grid after the database recovers."). If the cache agent start policy is norestart, call the ttGridAttach built-in procedure to reattach the member to the grid.
Call the ttRepStateGet built-in procedure from the active database to verify that the active database is available and that the active standby pair is attached to the grid:
Command> CALL ttRepStateGet; < ACTIVE, AVAILABLE > 1 row found.
For more information, see "ttRepStateGet" in Oracle TimesTen In-Memory Database Reference.
A multinode failure can occur because of a hardware failure or network failure, for example. After a multinode failure occurs, call the ttGridAttach built-in procedure for each member that needs to be reattached. The operation will fail for each grid member until you call the built-in procedure on the last grid member to be reattached. Call ttGridAttach again for the grid members that have not yet been attached and the operation will succeed. This sequence is necessary to prevent a "split-brain" situation with grid members being unaware of each other's states.