Skip Headers
Oracle® TimesTen In-Memory Database Troubleshooting Guide
11g Release 2 (11.2.2)

E21636-11
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

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

3 Troubleshooting TimesTen Application-Tier Database Cache 

The following sections in this chapter describe how to troubleshoot problems you may encounter when using TimesTen Application-Tier Database Cache (TimesTen Cache):

If you are having problems with an AWT cache group, see Chapter 5, "Troubleshooting AWT Cache Groups".

Note:

Error log messages from the cache agent daemon are designated by CAC in the message, as shown in several examples in this chapter. These messages are enabled unless you disable the CACHE component of the ttDaemonLog utility. See "ttDaemonLog" in Oracle TimesTen In-Memory Database Reference for information about ttDaemonLog options for Windows and UNIX.

Unable to create a cache group

This section describes problems you might encounter when executing the CREATE CACHE GROUP statement.

Possible cause What to do
User does not have the correct Oracle database privileges to create the cache group type. See "Check the Oracle database privileges".
User has insufficient access to the database. You must have CACHE_MANAGER privilege to create a cache group.
The internal/external user does not match the Oracle database user. The TimesTen user name must be the same as the Oracle database user name.
Cannot connect to the Oracle database. See:

Check the network status.

Cache administration user ID or password is not set (when trying to create AWT or autorefresh cache groups). See "Set the cache administration user name and password".
Data type mapping is unsupported. See "Unsupported data type mapping".
Nullability setting in the Oracle database is different. See "Null constraint does not match Oracle Database".
Primary key in root table is not specified. The root table of a cache group must have a primary key. See "Defining Cache Groups" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Unable to start or stop the cache agent

This section describes problems you might encounter when starting or stopping the cache agent.

Possible cause What to do
Cache agent is already running. See "Check status of the cache agent", below.
Cannot locate Oracle Database libraries.
ORACLE_HOME is invalid. See "Check ORACLE_HOME environment variable", below.
Privileges are insufficient. You must have CACHE_MANAGER privilege to start or stop the cache agent.
OracleNetServiceName is incorrect. Ensure that the OracleNetServiceName set in your DSN definition matches the Oracle Service Name for the Oracle Database instance that contains the tables to cache in TimesTen.

Check status of the cache agent

Check the status of the cache agent by using the ttStatus utility as described in "Using the ttStatus utility" to check the status of the cache agent.

If the cache agent is not running, start it as described in "Starting the cache agent" in the Oracle TimesTen Application-Tier Database Cache User's Guide. If attempts to start the cache agent fail, then investigate the possible causes and restart the system before attempting to start the cache agent.

Check ORACLE_HOME environment variable

On UNIX or Linux platforms, check that the ORACLE_HOME environment variable is set correctly for the shell from which you are starting the cache agent and the TimesTen daemon. Use the ttmodinstall utility if you need to change the setting for ORACLE_HOME.

See "Environment variables" in Oracle TimesTen In-Memory Database Installation Guide.

Check NLS environment variables

NLS environment variables are set in the environment where the TimesTen application is running. Check to see if TimesTen is using the NLS environment variables.

Recovering cache grid after unexpected system shutdown

The server may experience a system failure or an unexpected restart, such as with a power outage. In this case, the cache grid exits unexpectedly without the normal shutdown procedure.

The following sections describe how to recover when the system unexpectedly shuts down for two scenarios:

A portion of the cache grid nodes are still running

When the server shuts down, some cache grid nodes exited unexpectedly, but others are still active. In this case, you must detach the dead nodes first by executing ttGridDetachList from an attached node, as follows.

  1. Connect to a surviving grid node and execute ttGridDetachList to force a detach of all dead nodes from the grid.

  2. Connect to the databases on the restarted server. Start the replication agent by executing ttRepStart.

  3. Attach the cache grid nodes by executing ttGridAttach.

  4. Resume normal database operations.

All cache grid nodes exited unexpectedly

If all cache grid nodes exited unexpectedly when the server shut down, perform the following tasks to recover the cache grid:

  1. Log on to each grid node by connecting to the databases on the restarted server. Start the replication agent by executing ttRepStart. The replication agent will flush the existing log, even if the log is current.

  2. Call ttGridAttach on each node, which will fail with a communication error because it cannot communicate with other members. The failed attach cleans up the node information.

  3. The last node on which you execute the ttGridAttach should succeed. At this point you have cleaned up all nodes, so execute ttGridAttach on all nodes again to attach each node to the grid.

  4. Resume normal database operations.

Unable to resolve Oracle Service Name

If you receive error ORA-12514 indicating "could not resolve service name":

  • Use the Oracle Database TNSPING utility to verify that the service can be reached.

  • Ensure that the OracleNetServiceName set in your DSN definition matches the Oracle Service Name for the Oracle Database instance that contains the tables to cache in TimesTen.

  • Ensure that there is a service name defined. If it is a Windows Oracle client, use Oracle Net Configuration Assistant to configure a service name. In Oracle Net Configuration Assistant, navigate to Oracle Net Configuration -> Local -> Service Naming, select your Oracle Database server and confirm that there is a service name or a SID that identifies the Oracle Database server. If you add or modify a service name, you may need to restart.

    Check the cache administration user name and password on the Oracle database with SQL*Plus to make sure this service name works. For example:

    %sqlplus cache_admin_user/cache_admin_pwd@OracleHost
    

    cache_admin_user is the cache administration user name, cache_admin_pwd is the cache administration user password, and OracleHost is the OracleNetServiceName specified in your DSN definition.

    Note:

    Your cache administration user may be different from your regular Oracle database user. See "Create the Oracle database users" in the Oracle TimesTen Application-Tier Database Cache User's Guide.
  • Ensure that there is only one copy of tnsnames.ora on your TimesTen system. Also check the permission on tnsnames.ora.

  • If you run TimesTen on a UNIX system, check that the ORACLE_HOME environment variable is correctly defined. For example:

    ORACLE_HOME=/products/oracle11g
    
  • Check the Oracle Database client and server versions. See "Incompatible Oracle Database Server and Client versions", following shortly.

Unable to resolve connect identifier

You may receive ORA-12154 "TNS:could not resolve the connect identifier specified" when you try to connect to a a database.

This can occur when you are trying to use TimesTen Cache and Oracle Database on the same system and the TNS_ADMIN environment variable does not point to the proper tnsnames.ora file for Oracle Database. For example, you may have several instances of the Oracle Database running on a laptop.

In a production environment, you typically have TimesTen and Oracle Database running on different systems. In this case, do not reset the TNS_ADMIN environment variable to point to a tnsnames.ora file on the system where TimesTen is running. The Oracle Database client uses the TNS_ADMIN setting to resolve the connection, but the TimesTen main daemon, the cache agent, the Web server, and the replication agent are unaware of the TNS_ADMIN setting. TimesTen Cache cannot operate properly when the Oracle Database client and TimesTen use different tnsnames.ora files.

On Windows, set the TNS_ADMIN environment variable as follows:

  1. Right-click My Computer and choose Properties.

  2. On the Advanced tab, choose Environment Variables.

  3. Add or edit TNS_ADMIN as a system environment variable so that it points to the directory that contains the tnsnames.ora file that you want to use. You can include other tnsnames.ora files with the INAME command inside the tnsnames.ora file.

Incompatible Oracle Database Server and Client versions

If you receive connection timeout errors such as ORA-12170 or ORA-12535, or if you receive ORA-03134 (server version not supported), verify that you are using an Oracle Database client and Oracle Database server whose versions are compatible.

Metalink Documentation Note 207303.1, "Client/Server/Interoperability Support Between Different Oracle Versions", lists the client/server combinations supported by Oracle.

See Oracle Database and TimesTen release notes for known problems with client/server versions.

Unable to validate the Oracle database username and password

This section describes problems you might encounter when using the Oracle database username and password.

Possible cause See...
The library environment variable is not set correctly. "Check library path environment variable", below
Oracle Database processes are not running. "Check status of TNS listener and Oracle Database Server", below
User does not have the correct Oracle database privileges. "Check the Oracle database privileges", below
DSN is incorrectly configured. "Check DSN definition"
There are problems with cache administration user ID or password. "Set the cache administration user name and password"
User and system environments are inconsistent. "Check user and system environment"
Dynamic libraries are not loading. "Verify the loaded dynamic libraries"

Check library path environment variable

Check the library path environment variable on your platform.

On this platform... Check this variable...
UNIX LD_LIBRARY_PATH
Windows PATH

The library path environment variable must include the following information:

TimesTen and platform bit combination Setting
64-bit TimesTen or 32-bit TimesTen on 32-bit platform $ORACLE_HOME/LIB and $ORACLE_HOME/NETWORK/LIB
32-bit TimesTen on 64-bit platform $ORACLE_HOME/LIB32 and $ORACLE_HOME/NETWORK/LIB32

Check status of TNS listener and Oracle Database Server

Try to connect to the Oracle database by using SQL*Plus or use Oracle Enterprise Manager to verify the status.

Check the Oracle database privileges

From an Oracle SQL*Plus command prompt, list the current Oracle database privileges granted to you by entering:

SELECT * FROM SESSION_ROLES;
SELECT * FROM SESSION_PRIVS;

Compare the privileges listed against the required privileges for the various TimesTen Cache operations that are specified in "Grant privileges to the Oracle database users" in the Oracle TimesTen Application-Tier Database Cache User's Guide. Contact your Oracle Database Administrator if you require additional privileges.

Check DSN definition

  • Confirm you have correctly set the DSN attributes, such as in the example "DSN for a TimesTen database that caches data from an Oracle database" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

  • Confirm that the DSN definition for TimesTen Cache is a system DSN.

  • Confirm that the DSN for TimesTen Cache is defined only once.

  • Confirm the Oracle database user name and password. Use SQLPlus and connect to the Oracle database using the same OracleNetServiceName and OraclePWD used in your DSN definition to confirm they are correct.

Reboot TimesTen machine

If the Oracle Database client was installed and the system has not been restarted, then the TimesTen daemon is still running under the "old" environment before the Oracle Database client install. Restart your system so the TimesTen can start under the "new" environment.

Set the cache administration user name and password

The cache administration user name and password must be set only once in a TimesTen database. However, they must be changed if the TimesTen database is destroyed and recreated or if the cache administration user name is dropped and recreated in the Oracle database.

The cache administration user name and password cannot be changed if the cache agent is running on the TimesTen database or there are cache groups in the database. The cache groups must be dropped before you can change the cache administration user name and password. You must also stop the cache agent before you change the cache administration user name and password, and then restart the cache agent after the user name and password have been changed.

From a ttIsql session, connect to the database as the cache manager user and call the ttCacheUidPwdSet built-in procedure to set the Oracle database cache administration user name and password, as follows:

Command> call ttCacheUidPwdSet('cacheuser','oracle');

If it returns an error, check the Oracle Database ID, the cache administration user ID and cache administration password. In addition, check whether the Oracle Database instance is running.

You can also set the user name and password by executing the ttAdmin -cacheUidPwdSet utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheUidPwdSet -cacheUid cacheuser -cachePwd oracle cachealone1

If you do not specify the -cachePwd option, the ttAdmin utility prompts for the cache administration user's password. For more information about the utility, see "ttAdmin" in Oracle TimesTen In-Memory Database Reference.

Check user and system environment

Test to see if the problem is due to differences in user and system environment. This procedure requires two session windows (Command Prompt windows in Windows or shell windows in UNIX).

  1. Stop the TimesTen daemon.

  2. In one session window, start the Timesten daemon as a regular user.

    On Windows:

    % install_dir/srv/ttsrv1122.exe -d -verbose
    

    On UNIX:

    % install_dir/srv/timestend -d verbose
    

    Some messages will flash by, and then it goes into a wait state.

  3. In another session window, try to restart the cache agent.

  4. If Step 3 succeeds, then use Ctrl-C on Windows or the kill command on UNIX to stop the TimesTen daemon you started for the other session in Step 2.

  5. Compare the user environment and system environment. For example, do both user and system see the same copy of oci.dll? Are there any differences in the path name to the oci.dll library between the user and system environments?

  6. If you detect differences, make the necessary modifications.

  7. Restart the system and restart the TimesTen daemon.

Verify the loaded dynamic libraries

If you run on a Windows system with Visual C++ installed, verify the loaded dynamic libraries. This works only if you can start the cache agent without autorefresh:

  1. Make sure TimesTen is started.

  2. Start the cache agent without autorefresh.

    Command> call ttCacheStart;
    Command> create cache group cg1 from t1(c1 int not null primary key);
    
  3. Open the Windows Task Manager, find process ttora1122.exe and highlight it. Right-click on it and select Debug. This brings you into Visual C++ and you should see the loaded DLL in the debug window, as described in "Unable to resolve Oracle Service Name".

  4. Load the cache group to force an cache connection from the cache agent:

    Command> load cache group cg1 commit every 100 rows;
    
  5. Compare the loaded DLL in your debug window with the partial list shown in Example 3-1.

Example 3-1 List of loaded dlls

This partial list was created with the Oracle Database client.

Loaded 'E:\TimesTen\tt1121_32\bin\timestenorad1121.exe', no matching symbolic 
information found.
Loaded 'C:\WINDOWS\SYSTEM32\ntdll.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\kernel32.dll', no matching symbolic information 
found.
Loaded 'E:\TimesTen\tt1121_32\bin\tten1121.dll', no matching symbolic information 
found.
Loaded 'E:\TimesTen\tt1121_32\bin\ttcommon1121.dll', no matching symbolic 
information found.
Loaded 'C:\WINDOWS\SYSTEM32\wsock32.dll', no matching symbolic information 
found.
Loaded 'C:\WINDOWS\SYSTEM32\ws2_32.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\msvcrt.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\ws2help.dll', no matching symbolic information 
found.
Loaded 'C:\WINDOWS\SYSTEM32\advapi32.dll', no matching symbolic information 
found.
Loaded 'C:\WINDOWS\SYSTEM32\rpcrt4.dll', no matching symbolic information found.
...

OCI initialization failed

Error 5105, "OCI initialization failed," may occur when an operation requires contact with the Oracle database. For example, the error might occur in the following situations:

  • Starting the cache agent

  • Setting the cache administration user ID or password

  • Entering a SQL statement in TimesTen when autocommit=0 and PassThrough=3

Error 5105 contains additional information about its cause:

Unsupported data type mapping

When you try to create a cache group, you may receive the following error:

5115: Unsupported type mapping for column name

For example, table tab on the Oracle database can be described as follows:

COL1     NUMBER(38) NOT NULL
COL2     NUMBER(38)

Try to create the cache group as follows:

CREATE CACHE GROUP cg FROM tab(col1 CHAR(10) NOT NULL PRIMARY KEY);

Error 5119 is displayed and the cache group is not created because the statement attempts to map a column of NUMBER data type to a column of CHAR data type.

See "Data type mappings allowed for key columns" in the Oracle TimesTen Application-Tier Database Cache User's Guide.

Null constraint does not match Oracle Database

When you try to create a cache group, you may receive the following warning:

Warning 5119: Column name has different nullability setting in Oracle

For example, table tab on the Oracle database can be described as follows:

COL1     NUMBER(38) NOT NULL
COL2     NUMBER(38)

Try to create the cache group as follows:

CREATE CACHE GROUP cg 
    FROM tab(col1 INTEGER NOT NULL PRIMARY KEY, col2 INTEGER NOT NULL);

Warning 5119 is displayed because col2 on the Oracle database does not have a NULL constraint, but col2 in the cache group is defined as NOT NULL.

DDL on cached Oracle database tables may cause cache group operations to fail

DDL operations that are performed on an Oracle database table that is being cached in TimesTen may cause a failure on the cache group. For example, the user drops a column on the Oracle database table that is being cached in TimesTen. When the cache group is propagated or flushed, TimesTen will update the column that no longer exists in the Oracle database table. When the cache group loads or refreshes, then TimesTen attempts to retrieve data from the column that has been dropped.

The following cache group operations may fail:

  • Autorefresh does not occur.

  • AWT cache group operations are not propagated or refreshed to or from the Oracle database.

  • Cache group load or propagate fails.

If you suspect the cache group operations are not working properly because of a DDL operation on the Oracle database base table, then use DDL tracking to diagnose the issue. DDL tracking saves the change history for all the cached Oracle database tables. The SQL statement and when it was executed are each written to a TimesTen table in the cache administrator user schema on the Oracle database.

For more information on how to create the DDL tracking objects and how to enable DDL tracking for the base table within the Oracle database, see "Tracking DDL statements issued on cached Oracle Database tables" in the Oracle TimesTen Application-Tier Database Cache User's Guide. For details on the built-in procedure used for enabling or disabling DDL tracking, see "ttCacheDDLTrackingConfig" in Oracle TimesTen In-Memory Database Reference.

Changes not visible after updating object in cache group

If you modify an object in a cache group and then the changes do not appear on a subsequent SQL statement, then one of the following may have occurred:

  • The object was dropped from the Oracle database or was somehow damaged.

  • Oracle database was restored or recovered to a time before the object was created.

  • The Oracle database was down.

  • The user modified the OracleNetServiceName DSN or connection attribute after creating the cache group, which points to an Oracle database other than the one that the cache group was created upon.

For example, if the user creates an AWT cache group. Then, the user added rows to a table. When the user performs a SELECT * FROM the table, the rows did not appear. The ttmesg.log error file does not display an error that the Oracle database is not available. Instead, it displays the following messages:

12:09:02.10 Err : REP: 29934: CACHE1:meta.c(904): TT5221: TT5221: Oracle syntax 
error in OCIStmtExecute(): ORA-00942: table or view does not exist rc = -1 -- 
file "bdbStmt.c", lineno 1535, procedure "getOraOutTypesNLengths()" 
12:09:02.27 Err : REP: 29934: CACHE1:receiver.c(1978): TT5250: Awt Initialization 
Failure. Could not compile meta data sql. 
12:09:02.27 Warn: REP: 29934: CACHE1:transmitter.c(6505): TT16060: Failed to read 
data from the network. select() timed out 

To recover, perform the following:

  1. Stop all updates to the cache group.

  2. If you are using an AWT cache group, then flush the cache group.

  3. Recreate the cache group with the drop and create.

Loading or refreshing fails

If the LOAD CACHE GROUP or REFRESH CACHE GROUP statement fails when you specify COMMIT EVERY n ROWS and n is greater than 0, the contents of the target cache group could be in an inconsistent state. Some cache instances may be partially loaded.

Unload the cache group and then load it again. In some situations, it may be easier to drop and recreate the cache group.

Monitoring autorefresh cache groups

This section includes the following topics:

Using the ttCacheAutorefreshStatsGet procedure

The ttCacheAutorefreshStatsGet procedure returns information about the last ten autorefresh operations on a specified cache group.

The ttCacheAutorefreshStatsGet procedure returns information only when the cache agent is running and the autorefresh state is ON or PAUSED. All of the return fields are set to 0 when the cache agent is restarted or the autorefresh state is changed to OFF.

Example 3-2 Calling ttCacheAutorefreshStatsGet

This example uses testcache, which is a READONLY cache group with one table and an incremental autorefresh interval of 10 seconds.

Command> call ttcacheautorefreshstatsget('user1','testcache');

< 1164260, 2007-07-23 15:43:52.000000, 850280, 44, 0, 75464, 528255, 75464, 310, 110, 6800, 
1890912, 12439795, 1890912, 160020, InProgress >
< 1164260, 2007-07-23 15:43:33.000000, 831700, 43, 13550, 108544, 759808, 108544, 1030, 230, 
12290, 1815448, 11911540, 1815448, 160020, Complete >
< 1164260, 2007-07-23 15:43:12.000000, 810230, 42, 17040, 115712, 809984, 115712, 610, 330, 
16090, 1706904, 11151732, 1706904, 146470, Complete >
< 1164260, 2007-07-23 15:42:52.000000, 790190, 41, 14300, 94208, 659456, 94208,560, 320, 
13410, 1591192, 10341748, 1591192, 129430, Complete >
< 1164260, 2007-07-23 15:42:32.000000, 770180, 40, 12080, 99328, 695296, 99328,450, 290, 
11340, 1496984, 9682292, 1496984, 115130, Complete >
< 1164260, 2007-07-23 15:42:12.000000, 750130, 39, 10380, 86016, 598368, 86016,430, 230, 
9720, 1397656, 8986996, 1397656, 103050, Complete >
< 1164260, 2007-07-23 15:41:52.000000, 730130, 38, 13530, 112640, 700768, 112640, 530, 220, 
12780, 1311640, 8388628, 1311640, 92670, Complete >
< 1164260, 2007-07-23 15:41:32.000000, 710120, 37, 9370, 56320, 326810, 56320, 310, 160, 
8900, 1199000, 7687860, 1199000, 79140, Complete >
< 1164260, 2007-07-23 15:41:22.000000, 700120, 36, 2120, 10240, 50330, 10240, 50, 200, 1870, 
1142680, 7361050, 1142680, 69770, Complete >
< 1164260, 2007-07-23 15:41:12.000000, 690110, 35, 0, 0, 0, 0, 0, 0, 0, 1132440, 7310720, 
1132440, 67650, Complete >
10 rows found.

Table 3-1 describes the results from the first row of output.

Table 3-1 ttCacheAutorefreshStatsGet results from last autorefresh operation

Result Field name Description
1164260

cgId

Cache group ID

2007-07-23 15:43:52.000000

startTimestamp

Timestamp when autorefresh started for this interval

850280

cacheAgentUpTime

Number of cache agent clock ticks in milliseconds at the time the autorefresh transaction started for this interval

This value is cumulative and is reset when the cache agent process starts.

44

autorefNumber

Autorefresh number

0

autorefDuration

Number of milliseconds spent in this autorefresh operation

It is zero because the operation is in progress.

75464

autorefNumRows

Number of rows autorefreshed in this autorefresh operation

This would include all rows in the root table and child tables if the cache group had child tables.

Note: This information is not provided for full autorefresh.

528255

numOracleBytes

Number of bytes transferred from the Oracle database in this autorefresh operation

Note: This information is not provided for full autorefresh.

75464

autorefNumRootTblRows

Number of root table rows autorefreshed in this autorefresh operation

310

autorefQueryExecDuration

Duration in milliseconds for the autorefresh query to execute on the Oracle database

Note: This information is not provided for full autorefresh.

110

autorefQueryFetchDuration

Duration in milliseconds for the autorefresh query to fetch rows from the Oracle database

Note: This information is not provided for full autorefresh.

6800

autorefTtApplyDuration

Duration in milliseconds for TimesTen to apply the updated rows to the cache group

Note: This information is not provided for full autorefresh.

1890912

totalNumRows

Total number of rows autorefreshed since the cache agent started

Note: This information is not provided for full autorefresh.

12439795

totalNumOracleBytes

The total number of bytes transferred from the Oracle database since the cache agent started

Note: This information is not provided for full autorefresh.

1890912

totalNumRootTblRows

Total number of root table rows autorefreshed since the cache agent started

160020

totalDuration

Total autorefresh duration in milliseconds since the cache agent started

InProgress

autorefreshStatus

Status

Status can also be Complete or Failed.


Note that the total number of autorefreshed rows (1890912) is the same as the total number of autorefreshed root table rows in this example because there are no child tables.

The number of autorefreshed rows in TimesTen does not necessarily reflect the number of rows updated on the Oracle database. The Oracle database updates may be applied in TimesTen more than once, or multiple Oracle database updates on the same row may be applied as one update in TimesTen.

Displaying information from the change log tables

TimesTen provides the cacheInfo SQL script that gathers information from the change log tables that exist on the Oracle database for autorefresh cache groups. See "Managing a caching environment with Oracle Database objects" in the Oracle TimesTen Application-Tier Database Cache User's Guide for additional information about change log tables.

Run the script as the cache administration user on the Oracle database using SQL*Plus. If you run the script as a different user, it reports that the change log tables do not exist.

The script is in the following location:

install_dir/oraclescripts/cacheInfo.sql

The cacheInfo script displays the following information for each cached table:

% 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_06_69245_L
Number of rows in change log table: 100000
Maximum logseq on the change log table: 38
Timesten has autorefreshed updates up to logseq: 38
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.

The log sequence number (logseq) acts as a marker for the autorefresh operation.

Understanding messages about autorefresh in the support log

The support log contains messages that show the progress of autorefresh. For example, testcache is a readonly cache group with an autorefresh interval of 10 seconds (10,000 milliseconds).

The support log shows when autorefresh starts:

15:43:33.96 Info: CAC: 5264: TT47118-5264-5676-refresh03918: Starting autorefresh
number 43 for interval 10000ms

The message includes the following information:

  • Message number: TT47118

  • Timestamp (15:43:33.96)

  • Cache agent process ID (5264)

  • Thread ID (5676)

You can look up the message number in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps. For example, in the log message above, TT47118 is error message number 47118.

The thread ID is important because autorefresh numbers are unique only for a specific interval. Always check both the thread ID and the autorefresh number when you are tracking a specific autorefresh operation.

The support log also contains a longer message that reports information similar to the ttCacheAutorefreshStatsGet procedure. 108544 rows were updated in this autorefresh interval, and 1815448 rows have been updated since the cache agent was started. Note that the total number of rows and the total number of root table rows are the same in this message because there is only one table in the cache group. Number refers to the autorefresh number. All times are expressed in milliseconds.

15:43:51.81 Info: CAC: 5264: TT47087-5264-5676-refresh04387: Cache agent 
refreshed cache group USER1.TESTCACHE: Number - 43, Duration - 13550, NumRows - 
108544, NumRootTblRows - 108544, NumOracleBytes - 759808, queryExecDuration - 
230, queryFetchDuration - 1030, ttApplyDuration - 12290, totalNumRows - 1815448,
totalNumRootTblRows - 1815448, totalNumOracleBytes - 11911540, totalDuration - 
160020

Additional messages show that the autorefresh operation completes successfully:

15:43:51.81 Info: CAC: 5264: TT47119-5264-5676-refresh04449: Autorefresh 
number 43 finished for interval 10000ms successfully
15:43:51.81 Info: CAC: 5264: TT47119-5264-5676-fresher01619: Autorefresh number
43 succeeded for interval 10000 milliseconds

Inspect the timestamps to determine whether autorefresh is progressing as expected.

See "Managing TimesTen daemon options" in the Oracle TimesTen In-Memory Database Operations Guide for information about setting the support log location.

Diagnosing autorefresh failure

If ttCacheAutorefreshStatsGet shows that the status of an autorefresh operation is Failed, check the support log for messages related to the autorefresh operation with number the number shown in the ttCacheAutorefreshStatsGet output. Look for errors that occurred after the autorefresh operation started.

Example 3-3 ttCacheAutorefreshStatsGet output shows autorefresh failure

This row of output from ttCacheAutorefreshStatsGet shows a failed autorefresh operation.

< 1164260, 2007-08-01 14:56:36.000000, 959350, 9, 0, 0, 0, 0, 0, 0, 0, 1, 7,
 1, 50, Failed >

The autorefresh number is 9.

The support log shows the start message for autorefresh number 9:

14:56:36.10 Info: CAC: 5988: TT47118-5988-4724-refresh03926: Starting 
autorefresh number 9 for interval 15000ms

The thread ID for autorefresh number 9 is 4724. Look for error messages with this thread ID.

The following messages appear in the support log:

14:56:36.10 Info: CAC: 5988: TT47117-5988-4724-refresh03953: Autorefresh thread 
for interval 15000ms is connected to instance inst1 on host host1. Server handle 
231976252
14:56:36.12 Err : CAC: 5988: TT40018-5988-4724-refresh07567: TimesTen error 
code:5901, msg The Oracle refresh log table, "USER2"."TT_06_81799_L", for base 
table, USER2.READTAB2, cannot be found.
14:56:36.12 Info: CAC: 5988: TT47055-5988-4724-refresh05559: Autorefresh rolled 
back.
14:56:36.12 Info: CAC: 5988: TT47119-5988-4724-refresh04458: Autorefresh number 
9 finished for interval 15000ms with error.
14:56:36.12 Err : CAC: 5988: TT40035-5988-4724-fresher01606: Autorefresh number 
9 failed for cache groups with interval 15000 ms after 10 retries.

The error message for thread ID 4724 shows that the change log table, TT_06_81799_L, is missing. The introduction to "Autorefresh not refreshing cache at the specified interval" has a table entry that describes what to do in this situation.

Diagnosing autorefresh performance problems

You can use the ttTraceMon utility to diagnose autorefresh performance problems. See "AUTOREFRESH tracing".

TimesTen tracing severely impacts application performance and consumes a great deal of disk space if trace output is directed to a file. When you are finished, reset tracing to the default values.

Using SNMP traps for alerts about autorefresh problems

Enable SNMP traps to alert you when autorefresh problems occur. The SNMP traps related to autorefresh include the following.

  • ttCacheAutoRefQueFullTrap

  • ttCacheIncAutoRefFailedTrap

  • ttCacheValidationErrorTrap

  • ttCacheValidationWarnTrap

  • ttCacheValidationAbortedTrap

See "Diagnostics through SNMP Traps" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

Optimize performance for TimesTen Cache

The following recommendations may optimize performance for the TimesTen Cache:

Note:

Each of these suggestions involve performance tradeoffs, which may not always be beneficial for optimal use. Consider and test each performance suggestion for your own configured environment.
  • Pin the TimesTen Cache meta tables and cache group base tables in the SGA. by executing the ALTER TABLE table_name CACHE statement to indicate to the Oracle database that these tables should be stored in the keep portion of the SGA buffer cache. Pinning TimesTen Cache tables in the SGA increases the probability that any given data block needed for a TimesTen Cache refresh operation will be available in the SGA when the refresh is performed and will not force a disk read. This minimizes physical disk reads executed during TimesTen cache refresh operations.

  • Pin TimesTen Cache triggers into the shared pool using the dbms_shared_pool.keep procedure. Pinning triggers into the shared pool for applications where updates to the cache group base tables are infrequent keeps the trigger from having to be reloaded and reparsed. This is not necessary for highly volatile tables where the trigger will be executed frequently and will remain in the shared pool under any circumstances.

  • Enable parallel query. For very large base tables with 10 million rows or more, consider using the Oracle database parallel query facility. The primary join query between the log table and the base table is the kind of query which the Oracle database parallel query is designed to handle. When parallel processing is enabled, the parallel query optimizer generates a query plan that enables the original query to be broken into sections to be worked concurrently by different parallel query slave processes. When using parallel query, users should assign a default degree of parallelism of (2*N) to the cache group base table, where "N" is the number of CPUs on the system. Then, experiment to understand what level of parallelism works best for their environment. Experiment with different table structures for base tables, as follows.

    • Use a standard heap table with default degree of parallelism assigned during table creation or by use of the ALTER TABLE PARALLEL command. Build an N-partition primary key index against the table.

    • Use an N-way partitioned table structure with partition range key based either on the table primary key or, in the case of a concatenated primary key, the high-order column of the primary key. The number of partitions should be set to the degree of parallelism. Use a local primary key index with the same number of partitions.

    • Use an N-way hashed partition structure using the primary key as the hash key, a local partitioned primary key index, and both index and table partitions equal to the degree of parallelism. The log table should not be partitioned, as the cardinalities of the log table should never be large enough that a partitioned log table would have any performance benefit. Further, given the continuously increasing value of the log table primary key column, range partitions cannot be used.

Avoid performance and memory problems for large batch jobs on Oracle database tables

Customers sometimes run large batch jobs at month-end or year-end on the Oracle database tables that are cached in read-only cache groups with incremental autorefresh. This can cause performance and memory problems for autorefresh operations and replication unless preventative steps are taken.

The tasks described in this section are supported for these components in an TimesTen Cache configuration:

  • Read-only cache groups that are not in a cache grid and are not replicated by an active standby pair

  • Cache grid

  • Active standby pair replication

  • Active standby pair with a return service specified

  • Active standby pair with a disaster recovery subscriber

  • Physical, synchronous Oracle Data Guard

  • Oracle RAC

Perform these tasks when large batch jobs must be run on the cached Oracle database tables:

  1. Set the autorefresh state to PAUSED for cache groups with the AUTOREFRESH attribute that are affected by the batch job. Perform this task on each standalone cache grid node and on the active node of an active standby pair grid member. For example:

    ALTER CACHE GROUP sampecg SET AUTOREFRESH STATE PAUSED;
    COMMIT;
    
  2. Set the autorefresh state to PAUSED for cache groups with the AUTOREFRESH attribute that are not directly affected by the batch job. This ensures that there is a consistent view of the data during batch processing. Perform this task on each standalone cache grid node and on the active node of an active standby pair grid member.

  3. Run the batch job on the cached Oracle database tables.

  4. Make sure all autorefresh change log records have been assigned a valid log sequence number (logseq). Call the cacheInfo.sql script:

    % cd TimesTen_install_dir/oraclescripts
    % sqlplus cacheuser/oracle
    SQL> @cacheInfo
    

    Look for the number of updates that have not been marked with a valid log sequence number. Ideally, the number should be zero or small (less than 100) for all tables in cache groups for which the autorefresh state was paused. Consider the following example.

    *************Autorefresh Objects Information  ***************
    Host name: host1
    Timesten datastore name: /scratch/ttuser/ds/mydsn
    Cache table name: TTUSER.NOTAFFECTED
    Change log table name: tt_05_460491_L
    Number of rows in change log table: 1
    Maximum logseq on the change log table: 1
    Timesten has autorefreshed updates up to logseq: 1
    Number of updates waiting to be autorefreshed: 0
    Number of updates that has not been marked with a valid logseq: 0
    ****************************
    Host name: host2
    Timesten datastore name: /scratch/ttuser/ds/mydsn
    Cache table name: TTUSER.AFFECTED
    Change log table name: tt_05_460489_L
    Number of rows in change log table: 100
    Maximum logseq on the change log table: 213
    Timesten has autorefreshed updates up to logseq: 213
    Number of updates waiting to be autorefreshed: 10000
    Number of updates that has not been marked with a valid logseq: 0
    ****************************
    
  5. If the configuration includes active standby pair grid members, verify that the standby state is set to PAUSED on all standby nodes. Use the ttIsql cachegroups command.

  6. For each cache group that was altered in step 1, manually refresh the cache group in parallel mode. Select appropriate values for the transaction size (number of rows committed at a time) and degree of parallelism. Perform this task on each standalone cache grid node and on the active node of an active standby pair grid member. For example:

    REFRESH CACHE GROUP samplecg
     COMMIT EVERY n ROWS PARALLEL m;
    COMMIT;
    

    Note that this operation automatically resets the autorefresh state to ON.

  7. For each cache group that was altered in step 2, set the autorefresh state to ON. Perform this task on each standalone cache grid node and on the active node of an active standby pair grid member. For example:

    ALTER CACHE GROUP sampecg2 SET AUTOREFRESH ON;
    COMMIT;
    

It is good practice to periodically coalesce the indexes on the autorefresh log tables. See "ALTER INDEX" in Oracle Database SQL Language Reference.

Autorefresh not refreshing cache at the specified interval

The following table shows possible causes for autorefresh problems.

Possible cause What to do
Cache agent is not started with a cache administration user. Specify a cache administration user ID and password when starting the cache agent, as shown in "Starting the cache agent" in the Oracle TimesTen Application-Tier Database Cache User's Guide. Also see "ttCacheStart" in Oracle TimesTen In-Memory Database Reference and "Set the cache administration user name and password" in Oracle TimesTen Application-Tier Database Cache User's Guide.
Object ID of the base table has changed. See "Recover and reset autorefresh for the Oracle database objects", below.
Autorefresh trigger not enabled. See "Recover and reset autorefresh for the Oracle database objects", below.
Current log sequence number recorded in the TT_version_USER_COUNT table is less than to the maximum log sequence number in the autorefresh log table. See "Recover and reset autorefresh for the Oracle database objects", below.
There is no row in the TT_version_USER_COUNT table with usercount > 0 for every active incremental autorefresh table. See "Recover and reset autorefresh for the Oracle database objects", below.
Change log table is empty. See "Recover and reset autorefresh for the Oracle database objects", below.
User count is less than 0 or any TT_version_USER_COUNT log sequence anomalies. See "Recover and reset autorefresh for the Oracle database objects", below.
Autorefresh log table, trigger, or sequence associated with a cached table does not exist or is not valid. Check whether the cache agent was started with the correct cache administration user ID. If the cache administration user ID is correct, follow the procedure described in "Recover and reset autorefresh for the Oracle database objects", below.

Check the user error log for messages about "fatal anomalies". This indicates corrupt or missing Oracle database objects.

TT_version_USER_COUNT table is missing. Check whether the cache agent was started with the correct cache administration user ID. If the cache administration user ID is correct, follow the procedure in "Recover and reset autorefresh for the Oracle database objects", below.

Check the user error log for messages about "fatal anomalies". This indicates corrupt or missing Oracle database objects.

Current log sequence number in the TT_version_USER_COUNT table changes and is different from the bookmark, and the associated cached table is not refreshed by the next committed autorefresh. Restart the cache agent. If that does not work, follow the procedure in "Recover and reset autorefresh for the Oracle database objects", below.
If autorefresh occurs at the same time you alter the autorefresh mode, state, or interval, you may potentially cause a lock timeout as both actions modify the same table. Stop the cache agent before altering the autorefresh mode, state, or interval. See "Avoid a lock timeout condition when modifying autorefresh mode, state, or interval".
There is a resource problem. Restart the cache agent.

Reset autorefresh state

Incremental autorefresh does not work if the TRUNCATE statement is used on an Oracle database base table. If TRUNCATE is used on an Oracle database base table, then you must reset autorefresh by using the ALTER CACHE GROUP statement to set the autorefresh state to OFF followed by another ALTER CACHE GROUP to reset the autorefresh state to ON.

Recover and reset autorefresh for the Oracle database objects

If you know or suspect the Oracle database objects used by autorefresh are the cause of the problem, use the following procedure to recreate the Oracle database objects.

  1. Use ALTER CACHE GROUP to reset the autorefresh state to OFF on all cache groups on all databases that have the affected cached table:

    ALTER CACHE GROUP cache_group_name SET AUTOREFRESH STATE OFF;
    
  2. Shut down all cache agents on all affected databases.

  3. Check if the user count is zero for each table in the cache group.

    On the Oracle database, execute the following statement:

    SELECT usercount FROM autorefresh_id.tt_version_user_count
        WHERE tablename ='owner.tablename';
    

    If the count is not zero, set the count to zero:

    UPDATE autorefresh_id.tt_version_user_count SET usercount = 0
        WHERE tablename ='owner.tablename';
    
  4. Start one of the cache agents. The cache agent performs a clean up operation. It displays the following message to the support log after it has completed the cleanup:

    Cleanup of the Oracle objects completed
    
  5. After the cache agent has completed the clean up, use ALTER CACHE GROUP to reset the autorefresh state back to ON:

    ALTER CACHE GROUP cache_group_name SET AUTOREFRESH STATE ON;
    
  6. Start all other cache agents.

  7. Use ALTER CACHE GROUP to reset the autorefresh state back to ON for all of the affected cache groups on all databases.

Avoid a lock timeout condition when modifying autorefresh mode, state, or interval

A lock timeout may occur if you modify the autorefresh mode, state, or interval with the ALTER CACHE GROUP statement at the same time when a scheduled autorefresh starts (scheduled because of the autorefresh interval).

  • Autorefresh locks the cache tables and its rows in the SYS.CACHE_GROUP table.

  • The ALTER CACHE GROUP SET AUTOREFRESH statement updates the cache group state in the SYS.CACHE_GROUP table.

This results in the following error:

Error TT6003: Lock request denied because of time-out. 

As a workaround, you can:

  • Increase the lock timeout with the ttLockWait built-in procedure.

  • Stop the cache agent before executing the ALTER CACHE GROUP SET AUTOREFRESH statement to modify the autorefresh mode, state, or interval. The following example demonstrates setting the autorefresh state to paused.

    1. Stop the cache agent.

      Command> call ttCacheStop;
      
    2. Alter the autorefresh state to the desired state. This example sets the autorefresh state to paused.

      Command> ALTER CACHE GROUP new_customers SET AUTOREFRESH STATE PAUSED;
      
    3. Start the cache agent.

      Command> call ttCacheStart;
      

    For more information on ALTER CACHE GROUP, see "ALTER CACHE GROUP" in the Oracle TimesTen In-Memory Database SQL Reference. For more information on the ttLockWait built-in procedure, see "ttLockWait" in the Oracle TimesTen In-Memory Database Reference.

Incremental autorefresh not progressing

If incremental autorefresh is not progressing, verify that:

  • Autorefresh state is ON.

  • Cache agent is running.

Inspect the support log for the conditions described in the following table:

Table summary is in the first heading cell.

Condition What to do
Oracle Database server connection errors or warnings See "Troubleshooting Client/Server problems" for information about resolving connection problems.
Lock timeout errors or warnings on TimesTen This usually occurs because of an open DDL transaction on the cache group. Commit the DDL transaction so that autorefresh can get the necessary locks.
Insufficient permanent memory region errors on TimesTen Increase PermSize.
Autorefresh Oracle Database object validations errors or warnings See "Recover and reset autorefresh for the Oracle database objects".
Cache agent unexpected exit Contact TimesTen technical support.
Core files in main daemon directory Contact TimesTen technical support.
Warnings about incremental autorefresh becoming full refresh See "Incremental autorefresh becomes full autorefresh".
Warnings that autorefresh has not finished for a long time The autorefresh transaction can take a long time if many transactions have occurred since the last autorefresh.

Note: Cache groups with the same autorefresh interval are autorefreshed in one transaction.


Validate autorefresh for the Oracle database objects

The cache agent automatically verifies that the Oracle database objects exist and that they are valid so that autorefresh can progress. In normal operation, you should not see object validation errors or warnings in the user error log. If you see object validation errors, contact TimesTen technical support unless one of the following conditions has occurred:

  • The TimesTen database has been destroyed without using the DROP CACHE GROUP statement.

  • A customer application inadvertently modifies the objects directly in the Oracle database.

  • A DDL operation occurs on the base table on the Oracle database. This disables the trigger that controls autorefresh operations.

The cache group must be recreated if one of the preceding conditions has occurred.

Incremental autorefresh becomes full autorefresh

Incremental autorefresh can become full autorefresh if the cache administration user tablespace becomes full.

This section includes the following topics:

Detecting when incremental autorefresh becomes full

You can detect when incremental autorefresh becomes full refresh by several methods:

  • Check for messages in the support log that indicate full autorefresh operations are occurring. For example:

    2007-08-08 08:06:51.35 Warn: CAC: 11384: TT47166-11384-1087179104-lMarker01403:
    A full autorefresh will be performed for Incremental autorefresh table 
    USER1.READTAB because change log table TT_06_55555_L on Oracle has been 
    truncated.
    
  • Use the ttCacheAutorefreshStatsGet procedure.

    • If autorefresh is InProgress for longer than usual, full autorefresh may be occurring.

    • If a much larger number of rows (autoRefNumRows) was autorefreshed than usual, full autorefresh may have occurred.

    Check the support log for messages about full autorefresh.

  • If SNMP traps are enabled, the ttCacheRecoveryAutorefreshTrap SNMP trap indicates a full autorefresh.

Understanding the cache administration user tablespace

TimesTen strongly recommends creating a separate tablespace for the cache administration user. This tablespace is used as the cache administration user's default tablespace. The tablespace contains autorefresh triggers for each Oracle database table, change log tables for each Oracle database table, and other objects that TimesTen needs for each cache administration user. If you do not specify a separate tablespace, then these objects are placed in the Oracle Database system tablespace.

Specify the tablespace when you create the cache administration user on the Oracle database. You can also specify the tablespace after user creation with the DEFAULT TABLESPACE clause of the Oracle Database ALTER USER statement.

Change log tables for each of the cached Oracle database tables reside in the cache administration user tablespace. For each update on an Oracle database table, one row (a change log record) is inserted into the change log table for that Oracle database table. The size of a change log record in bytes is as follows:

size of change log record = size of primary key on Oracle table + 250

The number of records in a change log table depends on the update rate on the Oracle database table and on the autorefresh interval on TimesTen. Every 20 seconds, TimesTen removes change log records that have been applied to all databases that cache the associated Oracle database table.

When change logs are removed, a message similar to the following is displayed in the support log:

16:32:26.73 Info: CAC: 5652: TT47112-5652-4756-ogTblGC01036: Garbage collector 
deleted 1 rows from TT_06_383270_L where logseq < 1

There are options on how to manage what happens when the cache administration user tablespace is filled. See "Considerations when the tablespace of the cache administration is full" for more information.

Diagnosing a full cache administration user tablespace

Check for the following conditions if the cache administration user tablespace is full:

  • Is the autorefresh state set to PAUSED? Change log records accumulate when the state is PAUSED.

  • Has the cache group been created but not loaded? The default autorefresh state for cache group creation is PAUSED.

  • Is a cache group being created or is a database being duplicated? Both of these operations temporarily stop clean-up operations on the change log table.

  • Are the cache agents on all TimesTen databases running? If a cache agent is not running, change log records accumulate.

  • Has a database been abandoned without dropping autorefresh cache groups in the database? Abandoned databases result from scenarios such as the following:

    • The database is destroyed by ttDestroy -force.

    • The application connected to the database with the Overwrite connection attribute set to 1, but the cache groups that were in the old database are not recreated.

    If the database still exists, connect to the abandoned database and drop the cache group.

Use the cacheInfo.sql script to find out how large the change log tables are for each cached Oracle database table. Use the output to verify that the databases are still in use. See "Displaying information from the change log tables".

If the databases are still in use, verify that the cache agents are running.

Compare the autorefresh progress on TimesTen to the maximum log sequence number on the change log table. If TimesTen is behind, then call the ttCacheAutorefreshStatsGet procedure to see whether the autorefresh operations are successful. See "Using the ttCacheAutorefreshStatsGet procedure".

If the status is InProgress longer than seems reasonable, see "Poor autorefresh performance".

You may need to decrease the autorefresh interval or increase the size of the cache administration user tablespace.

There are options on how to manage what happens when the cache administration user tablespace is filled. See "Considerations when the tablespace of the cache administration is full", following shortly, for more information.

Monitoring the usage of the cache administration user's tablespace

To monitor the cache administration user tablespace, you can use either Oracle Enterprise Manager alerts or set the TimesTen tablespace threshold parameter.

The cache agent can be configured to periodically monitor the tablespace usage and issue a warning when it exceeds a specified threshold. Set the tablespace threshold percentage with the TblspaceThreshold parameter of the ttCacheConfig built-in procedure. For example, if you set the TblspaceThreshold parameter to 80, then a warning is issued when more than 80% of the tablespace is used.

  • If the threshold is set to zero, then no warning is issued. This is the default.

  • If the threshold is set between 1 and 99, a warning is issued when the tablespace threshold exceeds that number.

  • If the threshold is set to 100, then a warning is issued when the tablespace is full.

For example, to configure for a warning to be issued if the tablespace exceeds 80%, execute the following:

call ttCacheConfig('TblspaceThreshold',,,'80');

For full details of the ttCacheConfig built-in procedure, see the "ttCacheConfig" section in the Oracle TimesTen In-Memory Database Reference.

Considerations when the tablespace of the cache administration is full

With Oracle database tables that are cached in a TimesTen database, you can configure them to use incremental automatic refresh. For these tables, you can specify which one of the following is to occur when the cache administration user's tablespace is full:

  • The application performing the DML is to fail. This is the default.

    The tablespace full recovery is set to none. The application receives an "Out of Tablespace" error from Oracle Database when the tablespace is full. At that point, the application will need to rollback the transaction.

    Setting the tablespace full recovery to none is configured when you set the Param parameter to TblSpaceFullRecovery and the Value parameter to None with the ttCacheConfig built-in procedure. For example, the following configures Param to TblSpaceFullRecovery and Value to None for the employees table that is owned by terry:

    call ttCacheConfig('TblSpaceFullRecovery','terry', 'employees','None');
    
  • Truncate the change log table to free up space and cause a full autorefresh.

    When the cache administration user's tablespace is full, any application that is executing DML statements on the autorefresh cached Oracle database tables continues to execute. A trigger executes to free up space for new change log records by deleting existing change log records. This can result in a full automatic refresh on cache groups that have the incremental automatic refresh mode configured. However, if the Oracle database table is not configured for incremental automatic refresh, then no trigger executes.

    To set the operation to enable the application to continue and cause an autorefresh, set the Param parameter to TblSpaceFullRecovery and the Value parameter to Reload with the ttCacheConfig procedure. The user will see stale data until the full autorefresh is complete.

    However, even if the user sets the cache configuration parameter TblSpaceFullRecovery with the value of Reload, the tablespace may not be able to be emptied enough to handle the case of a growing index. Deleting rows from the change log table may not free up enough space for the index that is on the change log table. If the index is growing so fast that it uses all the tablespace to the point where purging the change log tables does not help, then the user's application may receive the following error:

    ORA-01654: unable to extend index <index> by 128 in tablespace <tblspace>
    

For full details of the ttCacheConfig built-in procedure, see the "ttCacheConfig" section in the Oracle TimesTen In-Memory Database Reference.

Poor autorefresh performance

Poor autorefresh performance is usually the result of large autorefresh operations. Use the ttCacheAutorefreshStatsGet procedure to check the autorefresh duration and observe whether the status remains InProgress for a long time.

Factors that can cause large autorefresh operations include:

Enable an AUTOREFRESH trace to diagnose autorefresh performance problems. See "AUTOREFRESH tracing".

Unresponsive or dead TimesTen database degrades autorefresh performance

Note:

Automatic recovery for TimesTen cache groups only applies to read-only and user managed cache groups that use the AUTOREFRESH cache group attribute. In this section, all references to autorefresh cache groups are read-only and user managed cache groups that use the AUTOREFRESH cache group attribute.

If any TimesTen databases containing autorefresh cache groups are destroyed or no longer in use, TimesTen continues to track autorefresh changes to the Oracle database tables for the TimesTen database for which the cache agent is not running. This causes automatic refresh to cache groups in active TimesTen databases to slow down.

The cache agent is responsible for detecting if a database is unresponsive or no longer in use. You can specify if and how a dead TimesTen database is to be recovered. However, you cannot recover a TimesTen database if all of the Oracle database objects have been removed.

The following sections describe how you can avoid a degraded autorefresh performance for inactive TimesTen databases:

Setting cached TimesTen database timeout

You can instruct TimesTen to mark the database as dead and no longer accepting updates if the cache agent has not communicated with the Oracle Database server within a specific timeout period.

Set the timeout for the TimesTen database and the recovery method for each autorefresh cache group with the AgentTimeOut parameter in the ttCacheConfig built-in procedure. The timeout value applies to the all TimesTen databases that use the same cache administration user. You should set the timeout value greater than the time necessary to load the TimesTen database into memory on first connect and start the cache agent. Otherwise, the TimesTen database could be incorrectly marked as dead. For any planned maintenance for the TimesTen instance, you could temporarily set the AgentTimeOut value to zero to disable the timeout. For full details of the ttCacheConfig built-in procedure, see the "ttCacheConfig" section in the Oracle TimesTen In-Memory Database Reference.

For example, the following sets the timeout value for the TimesTen database to 6000 seconds or 100 minutes. If the cache agent does not contact the Oracle Database server within a 100-minute period, then the TimesTen database is marked as dead.

ttIsql> call ttCacheConfig('AgentTimeOut',,,'6000');

Configuring recovery method for certain cache groups

You can recover a TimesTen database and autorefresh cache groups if they are not synchronizing with the Oracle database. If there is no synchronization, then updates on the Oracle database tables are not automatically refreshed to the corresponding TimesTen cache tables.

You can configure the DeadDbRecovery parameter of the ttCacheConfig built-in procedure to specify how to recover the synchronization for the TimesTen database and all autorefresh cache groups. The setting for DeadDbRecovery applies to all TimesTen databases that use the same cache administrator user. Set the DeadDbRecovery parameter to Normal, Manual or None to describe how TimesTen is to recover the database and all autorefresh cache groups. The DeadDbRecovery setting applies to all TimesTen databases that use the same cache administration user. While TimesTen is recovering the database and its autorefresh cache groups, there is an autorefresh status for the TimesTen database and the autorefresh cache groups that describes the recovery status for each of these entities. The TimesTen database can have an automatic refresh status of Alive, Dead or Recovering. The autorefresh cache groups can have an automatic refresh status of OK, Dead or Recovering. The TimesTen database status changes are linked to changes in the status for the autorefresh cache groups, as follows:

  • If the recovery method is set to Normal, then when TimesTen starts a full automatic refresh on an autorefresh cache group, the cache group's status is set to Recovering and the database's status is also set to Recovering.

  • The TimesTen database's status is only set to Alive when all of the autorefresh cache groups have either been recovered to OK or have been dropped.

  • When the database status is set to Dead, then all of its autorefresh cache groups are also set to Dead.

Note:

You can determine the autorefresh status of the TimesTen database and autorefresh cache groups with the ttCacheDbCgStatus built-in procedure, which is described in the "ttCacheDbCgStatus" section in the Oracle TimesTen In-Memory Database Reference.

When communication between the cache agent and the Oracle Database server is reestablished, TimesTen determines how to recover the autorefresh cache groups. TimesTen follows the recovery method you configured in the DeadDbRecovery parameter in the ttCacheConfig built-in procedure. This parameter can be set to one of the following:

  • Normal: This is the default. The autorefresh cache groups will each be recovered with a full automatic refresh. After the first full refresh, the cache group is recovered and will incrementally perform autorefresh.

    The autorefresh cache groups within the same automatic refresh interval will be transactionally consistent. Because it is a full refresh, it is not as performant as an incremental refresh.

    The autorefresh sets the status to Recovering. When the full autorefresh is completed successfully, the autorefresh cache group status is set to OK.

  • Manual: You must manually refresh an autorefresh cache group to recover it, or unload it if the cache group is dynamic.

  • None: The autorefresh cache group will never be recovered by a TimesTen autorefresh. Drop and recreate the cache group to recover it.

The database status changes as the first autorefresh cache group status changes. If there is at least one cache group that is in the process of recovery, then the database status is set to Recovering. Once all cache groups have been recovered, the status of the TimesTen database is marked as Alive.

The following example sets the DeadDbRecovery parameter to Normal for all autorefresh cache groups. The dead TimesTen database will be recovered when all of its autorefresh cache groups have each been recovered with a full automatic refresh.

ttIsql> call ttCacheConfig('DeadDbRecovery',,,'Normal');

When TimesTen databases participating in an active standby pair replication scheme contains cache groups, if the autorefresh status of the active master database is Dead and the autorefresh status of the standby master database is Alive, the standby master does not automatically assume the role of the active master. The recovery requires that you manually ensure that the cache and replication agents are executing. The specifics for each situation is as follows:

Table 3-2 Recovery for cache groups involved in active standby replication pair

DeadDbRecovery Setting Active Master Standby Master Resulting Behavior

Normal

Alive

Dead

Make sure that the cache and replication agents are executing on the standby master. Once the cache agent can connect to the Oracle Database, then the status of all autorefresh cache groups is set to Recovering. This sets the database to Recovering. Once a single cache group has received enough data to resume autorefresh, the status is set to OK. After all cache group are set to OK, the database is set to Alive.

Alternatively, you can fail the standby master and rollout a new standby master.

Normal

Dead

Alive

Make sure that the cache and replication agents are executing on the active master. Once the cache agent can connect to the Oracle Database, then the status of all autorefresh cache groups is set to Recovering. This sets the database to Recovering. Once a single cache group has received enough data to resume autorefresh, the status is set to OK. After all cache group are set to OK, the database is set to Alive.

Alternatively, you can fail the active master, switch the standby master as the new active and then rollout a new standby master.

Normal

Dead

Dead

Make sure that the cache and replication agents are executing on both masters. Once the cache agent can connect to the Oracle Database, then the status of all autorefresh cache groups is set to Recovering. This sets the database to Recovering. Once a single cache group has received enough data to resume autorefresh, the status is set to OK. After all cache group are set to OK, the database is set to Alive.

Alternatively, you can rollout new masters.

Manual

Alive

Dead

Make sure that the cache and replication agents are executing on the standby master. Once the cache agent can connect to the Oracle Database, then the status of all autorefresh cache groups is set to Recovering. This sets the database to Recovering. Once a single cache group has received enough data to resume autorefresh, the status is set to OK. After all cache group are set to OK, the database is set to Alive.

Alternatively, you can fail the standby master and rollout a new standby master.

Manual

Dead

Alive

Make sure that the cache and replication agents are executing on the active master. Use a manual refresh to recover the autorefresh cache groups on the active master. After all cache group are set to OK or have been dropped, the database is set to Alive.

Manual

Dead

Dead

Make sure that the cache and replication agents are executing on the active master. Use a manual refresh to recover the autorefresh cache groups on the active master. After all cache group are set to OK or have been dropped, the database is set to Alive. Changes are then replicated to the standby master.

None

Alive

Dead

Mark the standby master as failed. Execute ttDestroy utility for the standby master database. Duplicate the active master by executing ttRepAdmin -duplicate utility from the active master.

None

Dead

Alive

Destroy the dead active master with the ttDestroy utility. Recover the dead active master by duplicating the standby master with the ttRepAdmin -duplicate utility.

None

Dead

Dead

Roll out new masters.


Autorefresh cache group refresh with excessive waiting on resources

During an autorefresh cache group refresh, there can be excessive buffer busy waits, row lock waits, and deadlocks on updates in the Oracle database, which can negatively affect the throughput performance. When there are multiple deadlocks on updates in the Oracle database involving the autorefresh log tables, the following may appear in the support log:

Oracle native error code = 60, msg = ORA-00060: deadlock detected while waiting
for resource
An error occurred while preparing or executing the following Oracle sql 
statement: <some statement involving <cache admin user>.TT_##_#######_L  where 
the # is some number>

You can improve your performance by modifying the INITRANS and FREELISTS settings, which can affect the concurrent inserts into the autorefresh log table and internal maintenance of these tables. The application updating the base table that is being autorefreshed encounters a throughput performance hit when these settings are not appropriately configured.

You can automatically or manually manage these settings as follows:

  • Use ASSM tablespace, which automatically manages FREELISTS.

  • Manually adjust FREELISTS and INITRANS for the autorefresh log table on the Oracle database.

The following details how to manually modify INITRANS and FREELISTS for the autorefresh log table on the Oracle database:

  1. Retrieve the name of the autorefresh log table that is on the Oracle database.

    Under the cache administration user login, execute the SQL*Plus script cacheInfo.sql that lists the autorefresh change log table name, along with other items. The following example executes the cacheInfo.sql script that lists the autorefresh change log table name as tt_06_1216726_L, as shown in bold:

    SQL> @cacheInfo.sql
    *************Autorefresh Objects Information  ***************
    Host name: syst
    Timesten datastore name: /users/OracleCache/alone1
    Cache table name: ORATT.ORDERS
    Change log table name: tt_06_1216726_L 
    Number of rows in change log table: 1
    Maximum logseq on the change log table: 2
    Timesten has autorefreshed updates upto logseq: 1
    Number of updates waiting to be autorefreshed: 1
    Number of updates that has not been marked with a valid logseq: 0
    ****************************
    Host name: consyst
    Timesten datastore name: /users/OracleCache/alone1
    Cache table name: ORATT.ITEMS
    Change log table name: tt_06_1279699_L
    Number of rows in change log table: 7
    Maximum logseq on the change log table: 0
    Timesten has autorefreshed updates upto logseq: 0
    Number of updates waiting to be autorefreshed: 5
    Number of updates that has not been marked with a valid logseq: 5
    ****************************
     
    
  2. Manually alter the table on the Oracle database. The following example uses the table from the previous example. This example alters the INITRANS and FREELISTS settings for the bar.tt_06_1279699_L table.

    Note:

    See "INITRANS integer" and "FREELISTS" in the Oracle Database SQL Language Reference for details on what are the correct values for configuring these settings.
    ALTER TABLE BAR.TT_06_1279699_L INITRANS 10;
    ALTER TABLE BAR.TT_06_1279699_L STORAGE(FREELISTS 5);
    or
    ALTER TABLE BAR.TT_06_1279699_L MOVE STORAGE(FREELISTS 5);
    
  3. Alter the INITRANS and FREELISTS settings for the index for this table, which have the same name as the autorefresh change log table with an additional "L" at the end of it. For example, the index for table bar.tt_06_1279699_L is bar.tt_06_1279699_LL.

    These settings should be the same as what you set for the autorefresh change log table.

    ALTER INDEX BAR.TT_06_1279699_LL INITRANS 10;
    ALTER INDEX BAR.TT_06_1279699_LL STORAGE(FREELISTS 5);
    

Abnormally large change log and base tables degrade autorefresh performance

The cache thread SQL refresh joins the change log table and the base table, which identifies rows needed to be refreshed into TimesTen. The larger the cardinalities of the base table and the change log table, the longer the time necessary to perform this join. Performance degradation may occur if either the change log table or the base table is abnormally large.

The following describes scenarios where the change log table can become abnormally large.

  • If the change log table is never purged in configurations where cache groups from multiple DSNs all reference the same base table, it increases in size indefinitely. If one or more of the cache agents for these groups are turned off, those DSNs will not properly refresh their cache groups and the change log tables will not be purged. If the autorefresh state is turned to paused on one of multiple nodes, the other nodes may slow down.

  • The change log table can grow abnormally large if some cache agents have been shut down. Resolve this issue by restarting the cache, which will purge all of the backlogged log rows to be purged and all of the cache groups to be synchronized after the completion of the refresh cycle for all cache groups.

  • The change log table can be abnormally large if rows inserted into the change log table are never purged and can never be purged by normal processing. This occurs when one or more DSNs are destroyed or rebuilt without first removing the cache groups. The cache group tables on the Oracle database have no information that the cache groups have been destroyed, which corrupts the entire cache group. Rebuild and reinitialize all of the cache groups associated with this base table. Alternatively, never destroy a DSN with cache groups. Instead, always drop the cache groups before destroying a DSN.

Fragmented autorefresh change log table space

Change log tables can become fragmented when a high water mark occurs as the result of change logs building up when TimesTen is shut down, for example. If change log tables have become fragmented, you can:

  • Coalesce their indexes. This can be done without preventing DML changes to the base tables.

  • Perform an online segment shrink. This can be done without preventing DML changes to the base tables. See "Understanding Reclaimable Unused Space" in Oracle Database Administrator's Guide.

  • Rebuild the change log tables.

Check to see whether space is being wasted:

  1. Determine the name of the change log table by running the cacheInfo.sql script on the Oracle database.

  2. Calculate the size of the change log table. Call the result A. Adapt the name of the change log table in this example.

    SELECT table_name, ROUND((BLOCKS*8),2)||'KB' "size" 
     FROM user_tables
     WHERE table_name LIKE 'TT_05_%_L";
    
  3. Calculate the size of the data in the change log table. Call the result B. Adapt the name of the change log table in this example.

    SELECT table_name, ROUND((num_rows*avg_row_length/1024),2)|| 'KB' "size"
     FROM user_tables
     WHERE table_name LIKE 'TT_05_%_L';
    
  4. If (B/A)*100 is greater than 50 percent, then there is at least 40 percent space wasted (assuming a PCTFREE storage parameter set to 10). If there is at least 40 percent space wasted, defragmenting the change log table is recommended.

Perform these steps to defragment the change log table:

  1. Alter the cache group to set the autorefresh state to PAUSED.

  2. Copy the rows in the change log table to a temporary table.

  3. Truncate the change log table.

  4. Insert the rows from the temporary table to the change log table.

  5. Alter the cache group to set the autorefresh state to ON.

Performance degrades when autorefresh interval is small

When a relatively short refresh interval, such as a few hundred milliseconds, is combined with a large number of entries in the log table or in the base table, a cache refresh operation does not complete before the next refresh operation is scheduled to begin. In this case, the entries in the log table can be un-marked when the current autorefresh cycle finishes.

Thus, the same rows can be refreshed from the base table to the cache group in the next autorefresh cycle, by which time the rows will be marked. Make sure that the time it for the refresh is greater than the refresh interval. Set the refresh interval to a value where redundant refreshes will not occur.

Declaring NOVALIDATE on constraints causes cache group creation failure

If the Oracle database table on which you want to create the cache group declares NOVALIDATE on columns with primary key, UNIQUE or NOT NULL constraints, the creation of the cache group fails.

Note:

This does not apply to any foreign key constraints. However, TimesTen recommends that any matching foreign key is in the enabled VALIDATE state. Your workload performance may be affected when you alter a foreign key column to the enabled VALIDATE state.

TimesTen perceives a NOVALIDATE on a primary key or NOT NULL table column definition as a NULL and, therefore, not qualified as a column on which to build the cache group. Thus, all columns with the primary key, UNIQUE and NOT NULL column constraints must be enabled with the VALIDATE state when creating a cache group from the Oracle database table.

When you create a cache group from an Oracle database table with one or more of these constraints, the following errors are thrown:

5124: Autorefresh/propagate are not allowed on restricted cache group
5168: Restricted cache groups are deprecated
5120: No matching unique index with not null columns, unique key constraint
 with not null columns, or primary key constraint on table EVENTLOG, cache
 operations are restricted.

If you receive these errors, you can perform a SELECT statement to verify any existing NOVALIDATE constraints on the Oracle database table. The following SELECT statement shows all constraints on the MyTable table:

SQL> select constraint_name, constraint_type, validated, status from 
        all_constraints where table_name = 'MyTable';
 
CONSTRAINT_NAME                C VALIDATED     STATUS
------------------------------ - ------------- --------
REFID_CONSTRAINT               C VALIDATED     ENABLED
PKEY_CONSTRAINT                P NOT VALIDATED DISABLED

If the table column that is to be the primary key for the cache table is enabled as NOVALIDATE, perform the following steps to enable the column with the VALIDATE state:

  1. Enable the NOVALIDATE state for the primary key column.

    SQL> alter table MyTable modify constraint PKEY_CONSTRAINT 
               enable novalidate;
    Table altered.
     
    SQL> select constraint_name, constraint_type, validated, status 
              from all_constraints where table_name = 'MyTable';
     
    CONSTRAINT_NAME                C VALIDATED     STATUS
    ------------------------------ - ------------- --------
    REFID_CONSTRAINT               C VALIDATED     ENABLED
    PKEY_CONSTRAINT                P NOT VALIDATED ENABLED
    
  2. Enable the VALIDATE state for the primary key column.

    SQL> alter table MyTable modify constraint PKEY_CONSTRAINT validate;
    Table altered.
     
    SQL> select constraint_name, constraint_type, validated, status 
              from all_constraints where table_name = 'MyTable';
     
    CONSTRAINT_NAME                C VALIDATED     STATUS
    ------------------------------ - ------------- --------
    REFID_CONSTRAINT               C VALIDATED     ENABLED
    PKEY_CONSTRAINT                P VALIDATED     ENABLED
    

AWR report showing lock contention with DBMS_LOCK

There may be some concern about lock contention when seeing DBMS_LOCK in the Automated Workload Repository (AWR) Report. However, this DBMS_LOCK wait event does not affect the application performance in an TimesTen cache grid, even though the database time consumption in the AWR report seems high. This wait event is the garbage collector session trying to place a hold on a resource that another garbage collector session from another database has already locked. Thus, only the current garbage collector session waits. The wait for the garbage collector process does not block other processes, except other garbage collectors.

For example, the following shows a contention event in the AWR report:

AWR 
 
Top 5 Timed Events                                         Avg %Total
~~~~~~~~~~~~~~~~~~                                        wait   Call
Event                                 Waits    Time (s)   (ms)   Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
enq: UL - contention                  2,388       6,997   2930   72.0 Application
 

In addition, only a small amount of CPU time is used for the garbage collector, as shown in the "SQL ordered by CPU Time" section in the PERF AWR report.

SQL ordered by CPU Time              DB/Inst: REM0LNX/REM  Snaps: 14976-14977
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
 
    CPU      Elapsed                  CPU per  % Total
  Time (s)   Time (s)  Executions     Exec (s) DB Time    SQL Id
---------- ---------- ------------ ----------- ------- -------------
         0      3,508          120        0.00    36.1 0mt5pk2501gph
Module: timestenorad@etcpro01.oracle.com (TNS V1-V3)
DECLARE v_lockHandle VARCHAR2(200); BEGIN dbms_lock.allocate_unique(
'ORATT$ORA_GC1_CACHEADMIN', v_lockHandle); :retval := dbms_lock.request(
v_lockHandle, dbms_lock.x_mode, 30, FALSE); END;
         0      3,499          120        0.00    36.0 bb07h2a1v817x
Module: timestenorad@etcpro01.oracle.com (TNS V1-V3)
DECLARE v_lockHandle VARCHAR2(200); BEGIN dbms_lock.allocate_unique(
'ORATT$ORA_DDSMONITOR1_CACHEADMIN', v_lockHandle); :retval := 
dbms_lock.request(v_lockHandle, dbms_lock.x_mode, 30, FALSE); END;