4 Troubleshooting Oracle In-Memory Database Cache 

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 In-Memory Database Cache User's Guide. If attempts to start the cache agent fail, then investigate the possible causes and reboot the machine 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 In-Memory Database Installation Guide.

Check library path environment variable

Check the library path environment variable on your platform.

The library path environment variable must include the following information:

Check status of TNS listener and Oracle Server

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

Check Oracle privileges

From an Oracle SQL*Plus command prompt, list the current Oracle 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 IMDB Cache operations that are specified in "Grant privileges to Oracle users" in the Oracle In-Memory Database Cache User's Guide. Contact your Oracle Administrator if you require additional privileges.

Check DSN definition

• Confirm you have correctly set the DSN attributes as described in "DSN for a TimesTen database that caches data from an Oracle database" in the Oracle In-Memory Database Cache User's Guide.

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

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

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

Reboot TimesTen machine

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

Set the cache administration user id and password

From a ttIsql session, connect to the data store and enter the following:

Command> call ttCacheUidPwdSet('scott','tiger');

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

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/ttsrv1121.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. Reboot the system and restart the TimesTen daemon.

Verify the loaded dynamic libraries

If you are running 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 ttora1121.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 4-1.

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.
...

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.

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.

1164260

2007-07-23 15:43:52.000000

850280

44

0

75464

528255

75464

310

110

6800

1890912

12439795

1890912

160020

InProgress

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 Oracle. The Oracle updates may be applied in TimesTen more than once, or multiple Oracle updates on the same row may be applied as one update in TimesTen.

Displaying information from the change log tables

TimesTen provides a SQL script that gathers information from the change log tables that exist on the Oracle database for autorefresh cache groups. See "Oracle objects used to manage cache groups" in the Oracle In-Memory Database Cache User's Guide for more information about change log tables.

The script displays the following information for each cached table:

****************************
 * Host name: my-pc
 * Timesten datastore name: c:\data\tt1121
 * Cache table name: USER1.TESTCACHE
 * Change log table name: tt_03_55555_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 log sequence number (logseq) acts as a marker for the autorefresh operation.

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

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: ORA:  5264: ora-5264-5676-refresh03918: Starting autorefresh number 43 for interval 10000ms

The message includes the following information:

• Timestamp (15:43:33.96)

• Cache agent process ID (5264)

• Thread ID (5676)

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: ORA:  5264: ora-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: ORA:  5264: ora-5264-5676-refresh04449: Autorefresh number 43 finished for interval 10000ms successfully
15:43:51.81 Info: ORA:  5264: ora-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.

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

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

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

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:

• ttCacheAutoRefQueFullTrap

• ttCacheIncAutoRefFailedTrap

• ttCacheValidationErrorTrap

• ttCacheValidationWarnTrap

• ttCacheValidationAbortedTrap

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

Reset autorefresh state

Incremental autorefresh does not work if the TRUNCATE statement is used on an Oracle base table. If TRUNCATE is used on an Oracle 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 oracle objects

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

1. Use ALTER CACHE GROUP to reset the autorefresh state to OFF on all cache groups on all data stores 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 data stores.

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 data stores.

Validate autorefresh Oracle objects

The cache agent automatically verifies that Oracle 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 Technical support.unless one of the following conditions has occurred:

• The TimesTen data store 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 needs to be re-created if one of the preceding conditions has occurred.

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: ORA: 22119: ora-22119-0015-refresh05652: A full autorefresh will be performed for Incremental autorefresh table USER1.READTAB because change log table T_03_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 table, change log tables for each Oracle 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 system tablespace.

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

Change log tables for each of the cached Oracle tables reside in the cache administration user tablespace. For each update on an Oracle table, one row (a change log record) is inserted into the change log table for that Oracle 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 table and on the autorefresh interval on TimesTen. Every 20 seconds, TimesTen removes change log records that have been applied to all data stores that cache the associated Oracle table.

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

16:32:26.73 Info: ORA: 5652: ora-5652-4756-ogTblGC01036: Garbage collector deleted 1 rows from TT_03_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 cache administration user's tablespace 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 data store being duplicated? Both of these operations temporarily stop clean-up operations on the change log table.

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

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

  • The data store is destroyed by ttDestroy -force.

  • The application connected to the data store with the Overwrite connection attribute set to 1, but the cache groups that were in the old data store are not re-created.

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

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

If the data stores 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 cache administration user's tablespace is full" 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 Oracle TimesTen In-Memory Database Reference.

Considerations when the cache administration user's tablespace is full

With Oracle 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 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 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 table is not configured for incremental automatic refresh, then no trigger executes.

  To set the operation to allow 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 Oracle TimesTen In-Memory Database Reference.

Unresponsive or dead TimesTen database degrades autorefresh performance

If any TimesTen databases containing autorefresh cache groups are destroyed or no longer in use, TimesTen continues to track autorefresh changes to the Oracle 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 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

• Configuring recovery method for certain cache groups

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

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