| Oracle® In-Memory Database Cache User's Guide 11g Release 2 (11.2.2) E21634-08 |
|
|
PDF · Mobi · ePub |
| Oracle® In-Memory Database Cache User's Guide 11g Release 2 (11.2.2) E21634-08 |
|
|
PDF · Mobi · ePub |
The following sections describe how to manage and monitor various aspects of a caching system such as cache grids, cache groups and the cache agent process:
Impact of failed autorefresh operations on TimesTen databases
Dropping Oracle Database objects used by autorefresh 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 IMDB 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.
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 11, "Using Oracle In-Memory Database Cache in an Oracle RAC Environment".
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
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.
TimesTen 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.
TimesTen 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.
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 disk. 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 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 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.
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 on any cache grid 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 cache grid 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 cache grid 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 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 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.
Example 7-5 Enabling tracking of DDL statements issued on cached Oracle Database 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 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.
Example 7-6 Creating DDL tracking table and trigger when Oracle Database 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 Database 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 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:
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=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 Database schema.
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:
Use an ALTER CACHE GROUP statement to set the cache group's autorefresh state to PAUSED.
Truncate the Oracle Database table.
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_install_dir/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_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 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 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 autorefresh 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 Database 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 Database table to get a snapshot of the latest updates.
Note:
If you are caching the same Oracle table in more than one TimesTen database, see "Caching the same Oracle table on two or more TimesTen databases" for performance considerations.The Oracle Database objects used to process autorefresh writethrough operations can be automatically created by TimesTen as described in "Automatically create Oracle Database objects used to manage data caching" 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 Database objects used to manage data caching" 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 Database objects can be automatically or manually created, you must:
Create a cache administration user in the Oracle database as described in "Create the Oracle database 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 Database tables, where version is an internal TimesTen version number and object-ID is the ID of the cached Oracle Database table:
| Table Name | Description |
|---|---|
TT_version_AGENT_STATUS |
Created when the first cache group is created. Stores information about each Oracle Database table cached in an autorefresh 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 autorefresh 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_DBSPECIFIC_PARAMS |
Internal use. |
TT_version_DDL_L |
Created when the cache administration user name and password is set. Tracks DDL statements issued on cached Oracle Database 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 Database 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 Database 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 Database table. |
TT_version_object-ID_L |
One change log table is created per Oracle Database table cached in an autorefresh cache group when the cache group is created. Tracks updates on the cached Oracle Database table. |
For each cache administration user, TimesTen creates the following Oracle Database triggers, where version is an internal TimesTen version number, object-ID is the ID of the cached Oracle Database table, and schema-ID is the ID of user who owns the cached Oracle Database 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 Database table cached in an autorefresh cache group when the cache group is created. Fired for each insert, delete or update operation issued on the cached Oracle Database 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 Database 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 Database table to track operations in the TT_version_DDL_L table. |
For the timesten user, TimesTen creates the following Oracle Database 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 Database 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 Database table that is cached in an autorefresh cache group. 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 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 autorefresh 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 being 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 Database tables are cached in autorefresh 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 autorefresh 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 an autorefresh cache group:
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.
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 autorefresh cache groups have an autorefresh status of OK.
dead: All of its autorefresh cache groups have an autorefresh status of dead.
recovering: At least one of its autorefresh cache groups 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 autorefresh cache groups within that database are set to dead.
If you have enabled SNMP traps, a trap is thrown when the autorefresh status of a database is set to dead.
Call the ttCacheDbCgStatus built-in procedure as the cache manager 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.
Example 7-8 Determining the autorefresh status of a cache group and TimesTen database
In the following example, the autorefresh status of the database is alive and the autorefresh 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 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 autorefresh cache groups are ok.
If the autorefresh status of a cache group is 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.
You can configure a recovery method for cache groups whose autorefresh 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 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.
Manual: For each explicitly loaded 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.
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 autorefresh 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 autorefresh 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 autorefresh status is dead.
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 autorefresh 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 autorefresh operations resumes on cache tables in autorefresh cache groups. 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.
If a TimesTen database that contains autorefresh cache groups becomes unavailable, Oracle Database objects such as change log tables and triggers used to implement autorefresh 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 autorefresh cache groups.
Oracle Database objects used to implement autorefresh operations also continue to exist in the Oracle database when a TimesTen database is no longer being used but still contains autorefresh cache groups. Rows continue to accumulate in the change log tables. This impacts autorefresh performance on other TimesTen databases. Therefore, it is desirable to drop these Oracle Database 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 Database objects used to implement autorefresh 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 autorefresh operations exist in the Oracle database.
Example 7-10 Dropping Oracle Database objects for autorefresh 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
**************************************************************************
The following sections describe how to manage the cache administration user's 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 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 and, if you have enabled SNMP traps, by throwing the ttCacheAutorefreshLogSpaceDeFragDetectedTrap SNMP trap. By default, this threshold is set to 40%. You can configure what the fragmentation threshold should be with the ttCacheConfig built-in procedure.
Note:
Messages are logged to the user and support error logs. For details, see "Modifying informational messages" in the Oracle TimesTen In-Memory Database Operations Guide.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.
Note:
Do not pass in any values to thetblOwner and tblName parameters as they are not applicable to setting the fragmentation threshold.Example 7-11 Setting a fragmentation threshold
In the following example, the fragmentation threshold is set to 50%:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheConfig('AutoRefreshLogFragmentationWarningPCT',,,'50');
< AutoRefreshLogFragmentationWarningPCT, <NULL>, <NULL>, 50 >
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:
Note:
Do not pass in any values to thetblOwner and tblName parameters as they are not applicable to setting the defragmentation action.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.Example 7-12 Configuring action for fragmentation
In the following example, the action is set to CompactAndReclaim so that when the fragmentation ratio falls below the threshold, TimesTen defragments the change log tables and reclaims the space:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheConfig('AutoRefreshLogDeFragmentAction',,,'CompactAndReclaim');
< AutoRefreshLogDeFragmentAction, <NULL>, <NULL>, compactandreclaim >
1 row found.
To determine the current fragmentation threshold setting, call ttCacheConfig passing the AutoRefreshLogDeFragmentAction string to the Param parameter:
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.
To manually initiate a defragmentation of the change log tables, call the ttCacheAutoRefreshLogDeFrag built-in procedure as the cache manager user from any of the TimesTen databases that cache data from the Oracle database. Pass in one of the following strings as the parameter:
Compact: Defragment the change log tables.
CompactAndReclaim: Defragment the change log tables and reclaim the space.
Note:
When reclaiming space, the change log table is briefly locked, which temporarily suspends writing into the base table.Example 7-13 Manually defragmenting the change log tables
In the following example, the user calls the ttCacheAutoRefreshLogDeFrag built-in procedure with the CompactAndReclaim option:
% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> CALL ttCacheAutoRefreshLogDeFrag('CompactAndReclaim');
In order to avoid the tablespace becoming 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 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 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 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.
Example 7-14 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 Database 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.
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.
Example 7-15 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 autorefresh operation is performed on the cache table upon the next autorefresh cycle when an update operation is issued on the oratt.customer cached Oracle Database 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 Database 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 Database 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 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,
If you have enabled SNMP traps, a trap is thrown when an update operation is issued on a cached Oracle Database table and the cache administration user's tablespace is full.
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 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 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 fails 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 succeeds. This sequence is necessary to prevent a "split-brain" situation with grid members being unaware of each other's states.
Databases containing cache groups can be backed up with the ttBackup utility. However, 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.
If the restored database connects to the same backend Oracle database, then drop and recreate all cache groups in the restored TimesTen database. If they are static cache groups, you may be required to reload them. For dynamic cache groups, the reload is optional as data is pulled in from the Oracle database as it is referenced.
If the restored database connects to a different backend Oracle database than what it had originally connected with, then perform the following:
Specify the cache administrator user name and password with the ttCacheUidPwdSet built-in procedure.
Start the cache agent.
Drop all cache groups. You may see errors reported, which can be ignored.
Stop the cache agent.
Execute the cacheCleanUp.sql SQL*Plus script against the new Oracle database to remove all leftover objects. Specify the host and path for the restored TimesTen database.
Start the cache agent.
Recreate and, if required, reload the cache groups.
Note:
If the restored TimesTen database is not able to connect to any backend Oracle database, then you are not able to drop the cache groups or remove the cached data.If another TimesTen database used to connect to the original backend Oracle database but now no longer connects, and if all cache groups in the TimesTen database were not cleanly dropped, then execute the cacheCleanUp.sql SQL*Plus script against the original Oracle database to remove all leftover objects. Specify the host and path for the original TimesTen database.
|
 Copyright © 2012, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|