Oracle® TimesTen In-Memory Database Troubleshooting Procedures Guide Release 11.2.
Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide, Release 11.2.1 E13075-05 Copyright © 2006, 2010, Oracle and/or its affiliates. All rights reserved. This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws.
Contents Preface ................................................................................................................................................................. ix Audience....................................................................................................................................................... Related documents...................................................................................................................................... Conventions ......
Extract a stack trace from the core file ............................................................................................ 2-2 Unable to create shared segment........................................................................................................... 2-3 Application unable to connect to data store in direct mode ............................................................ 2-3 Upgrading your data store .................................................................................
Running out of a resource.................................................................................................................... Operating system tools and shared memory .............................................................................. Check the amount of memory allocated to the data store ........................................................ Permanent segment filling up................................................................................................
Displaying information from the change log tables................................................................... 4-13 Understanding messages about autorefresh in the support log .............................................. 4-13 Diagnosing autorefresh failure ..................................................................................................... 4-14 Diagnosing autorefresh performance problems.........................................................................
Check host names ....................................................................................................................... 6-7 Check owner names........................................................................................................................... 6-8 Checking replication owner ...................................................................................................... 6-8 Checking table owner........................................................................
viii
Preface Oracle TimesTen In-Memory Database is a memory-optimized relational database. Deployed in the application tier, Oracle TimesTen In-Memory Database operates on databases that fit entirely in physical memory using standard SQL interfaces. High availability for the in-memory database is provided through real-time transactional replication. Audience This guide describes how to troubleshoot some of the problems users encounter when using the Oracle TimesTen In-Memory Database.
Convention Meaning italic monospace Italic monospace type indicates a variable in a code example that you must replace. For example: Driver=install_dir/lib/libtten.sl Replace install_dir with the path of your TimesTen installation directory. [] Square brackets indicate that an item in a command line is optional. {} Curly braces indicated that you must choose one of the items separated by a vertical bar ( | ) in a command line. | A vertical bar (or pipe) separates alternative arguments. ...
Accessibility of Code Examples in Documentation Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.
xii
What’s New This section summarizes the new features and functionality of Oracle TimesTen In-Memory Database Release 11.2.1 that are documented in this guide, providing links into the guide for more information. New features for release 11.2.1.
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 see "Changes not visible after updating object in cache group" on page 4-10. 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.
1 Tools for Troubleshooting TimesTen 1 This chapter describes how to use the TimesTen utilities and other tools that are used to diagnose problems with the TimesTen data store.
Using the ttStatus utility You can then execute SQL statements or ttIsql commands at the Command> prompt. "Using the ttIsql Utility" in the Oracle TimesTen In-Memory Database Operations Guide describes how to use the most common ttIsql commands. The following ttIsql commands are commonly used when troubleshooting: ■ monitor formats the contents of the SYS.MONITOR table. See "Displaying data store information" in the Oracle TimesTen In-Memory Database Operations Guide.
Using the ttStatus utility Example 1–3 ttStatus shows TimesTen daemon is running In this example, the output from ttStatus indicates that the TimesTen daemon is running. It recognizes one data store named demo. The first line indicates that the TimesTen daemon is running as process 884 on port 17000 for the TimesTen instance MYINSTANCE. The second line indicates the TimesTen Server is running as process 2308 on port 17002.
Using the ttStatus utility TimesTen webserver pid 4216 started on port 17004 -----------------------------------------------------------------------Data store c:\temp\subscriber1ds There are 12 connections to the data store Data store is in shared mode Shared Memory KEY Global\DBI45b9471c.2.SHM.
Using the ttCapture utility TimesTen webserver pid 1708 started on port 17004 ---------------------------------------------------------------Data store c:\data\rep1 There are 12 connections to the data store Data store is in shared mode Shared Memory KEY Global\DBI45ef98ac.1.SHM.
Using the logs generated by the TimesTen daemon On Windows platforms, running ttCapture also produces an XML file named ttcapture.date.time.nfo that contains output from the msinfo32 utility. When you experience a problem with a TimesTen data store, run ttCapture with the DSN option for the data store as soon as possible, either when you are encountering the problem or immediately afterward. Note: Always double-quote directory and file names in case there are spaces in the names.
Using the ttTraceMon utility AGING API ASYNCMV AUTOREFRESH CG CGRID CGRIDC CKPT DEADLOCK EE ERR FLOW HEAP LATCH LOCK LOG LOGF OPT ORACON PLOAD PT REPL SM SQL TEST TRACE XA XACT ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 0 0 0 0 0 0 0 0 1 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 The output for most TimesTen components is of interest only to Technical support.
Using the ttTraceMon utility Trace > level sql 3 At this point you can run your application and the TimesTen trace information is written to a trace buffer. There are two ways to read the contents of the trace buffer: ■ ■ From the Trace prompt, use the outfile command to direct the trace buffer data to a file. (You must do this before running your application.) When writing tracing information to a file, new trace information is concatenated to the existing contents of the file.
Using the ttTraceMon utility SQL tracing Using ttTraceMon with the SQL component provides information about the SQL being prepared or executed by the TimesTen engine. Table 1–1 describes the levels for SQL tracing. Each level with a ’+’ sign includes the trace information described for that level, plus all levels preceding it. Table 1–1 SQL tracing levels Level Output 2 SQL commands being prepared.
Using the ttTraceMon utility Trace > {press ENTER – blank line} API tracing API traces are generated for database operations such as connecting to a data store, changing a connection attribute, and committing a transaction. Table 1–2 describes the levels for API tracing. Each level with a ’+’ sign includes the trace information described for that level, plus all levels preceding it. Table 1–2 API tracing levels Level Output 1 All rollback attempts by the subdaemon.
Using the ttTraceMon utility Table 1–3 describes the DEADLOCK tracing levels. Each level with a ’+’ sign includes the trace information described for that level, plus all levels preceding it. Table 1–3 DEADLOCK tracing levels Level Output 1 Logs deadlock cycles as they are discovered. 4, 6 + Provides detail information about how the deadlock is detected. Example 1–10 DEADLOCK trace In this example, we execute ttTraceMon to do a DEADLOCK trace at level 1, which is the default, on myDSN data store.
Using the ttTraceMon utility The trace buffer contains the following information showing all level 1 deadlock traces, as evidenced by ’1L’.: Trace> dump 09:50:26.444 13 DEADLOCK 1L 2036C 3484P 0 cnt=1 , Tbl 'T1', SQL='update t1 09:50:26.455 14 DEADLOCK 1L 2036C 3484P 0 cnt=1 , Tbl 'T1', SQL='update t1 09:50:26.455 15 DEADLOCK 1L 2036C 3484P SQL='update t1 set y1=y1 where x1=1' edge 1: xid 3.
Using the ttTraceMon utility For the second connection to myDSN, we set autocommit off. We insert a row into table test. Because autocommit is off, the row is not inserted into the table until we commit. A lock is held until we commit or roll back the transaction. If we use the dump command to display the contents of the trace buffer, we see that there are no records in the trace buffer: Trace> dump 0 records dumped From the first connection, we try to drop the materialized view.
Using the ttTraceMon utility First we create a table: Command> create table test (id tt_integer); Next we turn on tracing at level 4. Rather than direct the trace output to a file as in the previous examples, we read it directly from the trace buffer. Before saving the ERR trace to the buffer, we use the flush command to empty the buffer. % ttTraceMon myDSN Trace monitor; empty line to exit Trace> level err 4 Trace> flush Now we execute a SQL script with three errors in it.
Using the ttTraceMon utility Table 1–6 describes the AGING tracing levels. Each level with a ’+’ sign includes the trace information described for that level, plus all levels preceding it. Table 1–6 AGING tracing levels Level Description 1 Displays messages about the following events: ■ ■ ■ 2 The aging subdaemon starts least recently used (LRU) or time-based aging. The aging subdaemon repeats LRU aging because the LRU threshold was not met. The aging subdaemon ends LRU or time-based aging.
Using the ttTraceMon utility 13:16:56.803 1248 AGING curTime=78, ltblid= 637140 13:16:56.804 1249 AGING remaining, tbl = TTUSER.MYTAB 13:16:56.804 1250 AGING curTime=78, reason = 'no more 13:16:56.804 1251 AGING 5 records dumped 2L 2045C 17373P Entering sbAgingOneTable(): 3L 2045C 17373P curTime=78, 4 deleted, 1 2L 2045C 17373P Exiting sbAgingOneTable(): rows', 4 deleted, 1 remaining, tbl = TTUSER.
Using the ttTraceMon utility Table 1–7 (Cont.
Using the ttTraceMon utility 08:56:57.471 19400 AUTOREFRESH 3L 5C 32246P Incremental autorefresh started for table TESTUSER.READTAB 08:56:57.471 19401 AUTOREFRESH 4L 5C 32246P Autorefresh Query: SELECT L."COL_10", X."COL_20", X.ft$NotDelete, Z.rowid FROM (SELECT DISTINCT "COL_10" FROM "TESTUSER"."TT_03_454854_L" WHERE logseq >:logseq AND ft_cacheGroup <> 100000000000*1844259679+-299200618) L,(SELECT "TESTUSER"."READTAB"."COL_10", "TESTUSER"."READTAB"."COL_20", 1 AS ft$NotDelete FROM "TESTUSER".
Using the ttXactAdmin utility Using the ttXactAdmin utility The ttXactAdmin utility displays ownership, status, log and lock information for each outstanding transaction. You can also use it to show all current connections to a data store. The ttXactAdmin utility is useful for troubleshooting problems with replication, XLA, and asynchronous writethrough cache groups. Example 1–15 Using ttXactAdmin to diagnose a lock timeout Use ttXactAdmin to diagnose a lock timeout.
Using ODBC tracing Using ODBC tracing On Windows, use the ODBC trace facility to verify the sequence and content of your commands. The ODBC trace facility works only if you have linked your application with the ODBC Driver Manager. Enable tracing by double-clicking ODBC in the Control Panel. This opens the ODBC Data Source Administrator. Choose the Tracing tab. On UNIX platforms, ODBC tracing is available only when using a driver manager. To turn on tracing, set the Trace and TraceFile attributes.
Using the query optimizer executing SELECT statements on the columns or by using the ttIsql showplan command, as described in "Viewing and changing query optimizer plans" in the Oracle TimesTen In-Memory Database Operations Guide. Using the query optimizer The query optimizer is an important tool for performance tuning.
Using the query optimizer 1-22 Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide
2 Troubleshooting TimesTen Applications and Data Stores 2 This chapter helps you diagnose and remedy some of the problems encountered while using a TimesTen data store. If you are still having problems with your data store after following the troubleshooting recommendations in this chapter, please contact Technical support.
No response from TimesTen daemon or subdaemon Possible cause What to do Another process is using the TimesTen daemon port. Use the ttVersion utility to verify what port number the TimesTen daemon is expected to use. Use an OS command like netstat to check whether another process is listening on the port. If there is a conflict, either change the port number used by the other process or use ttmodinstall to change the port used by TimesTen. TimesTen daemon is already running.
Application unable to connect to data store in direct mode data manager service, a pop-up asks if you would like to start the debugger. Contact Technical support and provide the stack trace.
Application unable to connect to data store in direct mode Upgrading your data store A data store is only guaranteed to be accessible by the same minor release of TimesTen that was used to create the data store. When you upgrade the TimesTen software and you would like to use the new release to access a data store that was previously created, create a data store with the new release. Then use the ttMigrate utility to copy the tables, indexes, and table data from the old data store to the new one.
Application unable to connect to data store in direct mode default and does not see any User DSNs. Make sure that you are not using a mapped drive in the data store path name. Manage semaphores and shared memory segments An error is generated if you attempt to connect to or create a shared data store whose size is larger than the maximum size of shared memory segments configured on your system. Also, an error is generated if the system cannot allocate any more shared memory segments.
Troubleshooting Client/Server problems Increase the number of available file descriptors Each process connected to a TimesTen data store keeps at least one operating system file descriptor open. Additional file descriptors may be opened for each connection if checkpoints are issued, and transactions are committed or rolled back. If you receive an error that all file descriptors are in use when attempting to connect to a data store, then increase the allowable number of file descriptors.
Troubleshooting Client/Server problems If you typed the hostname or network address directly into the Server Name field of the TimesTen Client DSN Setup, the Client tries to connect to the TimesTen Server using the default port. Note: If the Network Address and Port Number values are correct, the TimesTen Server may not be running.
Troubleshooting Client/Server problems connect. Verify that the dynamic library specified in the DRIVER attribute for the Server DSN exists and is executable. Application times out when accessing TimesTen Server The default TimeOut interval is 60 seconds. To increase this interval on UNIX, change the value of the TTC_Timeout attribute in the odbc.ini file.
Application becomes disconnected unexpectedly Increasing the server stack size decreases the number of concurrent connections that can be made before running out of swap space. See "Working with the TimesTen Client and Server" in the Oracle TimesTen In-Memory Database Operations Guide.
Application is slow This section describes what to check if your application unexpectedly disconnects from the data store. Possible cause See... Internal application error. "Check for ODBC or JDBC errors" on page 2-10 Failure of a concurrent application thread. "Check for ODBC or JDBC errors" on page 2-10 If using a client/server connection, the client may have disconnected from the application.
Application is slow Possible cause See...
Application is slow For more information about updating statistics, see "The TimesTen Query Optimizer" in the Oracle TimesTen In-Memory Database Operations Guide. Verify lock and isolation levels The manner in which multiple applications concurrently access the data store can have a major impact on performance. An application can acquire locks on the entire data store, individual tables, and individual rows.
Application unresponsive, appears hung the new partitions. A high partition count should be avoided. On replicated tables that have multiple partitions, additional space is used for each update on the subscriber side, proportional to the number of partitions. This can result in the subscribers using slightly more perm space than the master. The partition value for each table is tracked in the SYS16 column of the system table, SYS.TABLES.
Application unable to find previously created objects If the problem is repeatable, use ttTraceMon to generate a SQL trace to determine where the application is hanging. See "SQL tracing" on page 1-9 for details. In more extreme cases, it may be helpful to generate a level 4 ERR trace for the application and review all of the errors messages that are pushed in the TimesTen direct driver. See "ERR tracing" on page 1-13 for details.
Application unable to find previously created objects Specify object owner Tables, indexes and sequences can be created either with a single name, such as PARTS, or with a qualified name incorporating an owner and table name, such as STAN.PARTS. When accessing a table or index, if no owner is specified, TimesTen first assumes that the owner is the login ID of the user (the value of the UID attribute).
Troubleshooting OCI and Pro*C/C++ applications Troubleshooting OCI and Pro*C/C++ applications On Windows, the NLS_LANG setting is taken from the registry if it is not in the environment. If NLS_LANG is set to an unsupported value, such as NA, an OCI connection failed error or an ORA-12705 error is thrown. If your OCI or Pro*C/C++ program has trouble connecting to TimesTen, verify that the setting of HKEY_LOCAL_ MACHINE\Software\ORACLE\NLS_LANG is valid and indicates a character set supported by TimesTen.
Running out of a resource When the TimesTen data store fills up, it is important to determine whether it is the permanent or the temporary segment that is filling up. Use the ttIsql dssize command to list allocated, in-use, and high water mark sizes for the permanent and temporary data partitions. The dssize command selects the following values from SYS.
Running out of a resource been disconnected by using the ttStatus utility. That frees up all temporary space, but you must reprepare commands. Diagnose memory usage by queries. See "Check memory used by queries" on page 2-18. If the problem is chronic, monitor the data store to try to identify the source of the problem. Use the ttWarnOnLowMemory procedure to enable warnings in the user log that indicate that the data store is filling up.
Running out of a resource Transaction log files accumulate in the directory specified by the LogDir attribute and are only deleted when checkpoints are performed. If the LogDir attribute is not specified in the DSN, transaction log files accumulate in the directory specified by the DataStore attribute. The maximum size of your transaction log files is set by the LogFileSize attribute. When a disk fills up with TimesTen data, it is most often due to a build-up of transaction log files.
Duplicate results from a SELECT statement See "Size transactions appropriately" in the Oracle TimesTen In-Memory Database Operations Guide for more information on transaction length. The following attributes are related to disk use: ■ ■ The LogPurge attribute indicates whether transaction log files that no longer have a hold on them are purged (removed from the disk) or simply archived (renamed).
Cannot attach PL/SQL shared memory ■ User allocated memory already uses that address. ■ Some shared memory already uses that address. ■ A shared library already uses that address. To recover, specify a virtual address that is free in all processes that can connect to the database. If you have a program on a 32-bit operating system that allocates large amounts of memory before connecting to TimesTen, it may clash with the PL/SQL shared memory segment.
Cannot attach PL/SQL shared memory 2-22 Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide
3 Troubleshooting Installation, Upgrades and Downgrades 3 This chapter includes the following topics: ■ Installing 32-bit TimesTen on 64-bit Windows ■ Downgrading a data store with Oracle data types to TimesTen 6.0 Installing 32-bit TimesTen on 64-bit Windows The default ODBC Data Source Administrator on 64-bit Windows does not show TimesTen 32-bit drivers and DSNs. If Windows is installed in the default location (C:\WINDOWS), use C:\WINDOWS\SysWOW64\odbcad32.
Downgrading a data store with Oracle data types to TimesTen 6.0 6. In another terminal, with the environment set correctly for TimesTen 6.0, restore the data store as a TimesTen 6.0 data store using TimesTen 6.0 ttMigrate. ttMigrate -r datastore datastore.migrate Before restoring the data store with TimesTen 6.0 ttMigrate, you must modify the DSN attributes appropriately for using with TimesTen 6.0.
4 Troubleshooting Oracle In-Memory Database Cache 4 This chapter describes how to troubleshoot some of the problems you may encounter when using Oracle In-Memory Database Cache (IMDB Cache).
Unable to start or stop the cache agent Possible cause What to do User does not have the correct Oracle privileges to create the cache group type. See "Check Oracle privileges" on page 4-6. User has insufficient access to data store. You must have CACHE_MANAGER privilege to create a cache group. The internal/external user does not match the Oracle user. The TimesTen user name must be the same as the Oracle user name.
Recovering cache grid after unexpected system shutdown 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" on page 1-2 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.
Unable to resolve Oracle Service Name 1. Log on to each grid node by connecting to the datastores on the rebooted 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.
Unable to validate Oracle username and password This can occur when you are trying to use IMDB Cache and Oracle on the same machine and the TNS_ADMIN environment variable does not point to the proper tnsnames.ora file for Oracle. 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 running on different machines. In this case, do not reset the TNS_ADMIN environment variable to point to a tnsnames.
Unable to validate Oracle username and password Possible cause See... Dynamic libraries not loading "Verify the loaded dynamic libraries" on page 4-7. Check library path environment variable Check the library path environment variable on your platform. On this platform... Check this variable... UNIX except HP-UX LD_LIBRARY_PATH On 64-bit platforms, LD_LIBRARY_PATH64 takes precedence over LD_LIBRARY_PATH. Make sure that the library path is specified in LD_LIBRARY_PATH64.
Unable to validate Oracle username and password ■ 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.
OCI initialization failed 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" on page 4-4. 4. Load the cache group to force an cache connection from the cache agent: Command> load cache group cg1 commit every 100 rows; 5.
DDL operations on cached Oracle tables may cause cache group operations to fail 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 Oracle can be described as follows: COL1 COL2 NUMBER(38) NOT NULL 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 bec
Changes not visible after updating object in cache group and when it was executed are each written to a TimesTen table in the cache administrator user schema on Oracle. For more information on how to create the DDL tracking objects and how to enable DDL tracking for the base table within Oracle, see "Monitoring DDL operations on Oracle tables" in the Oracle In-Memory Database Cache User's Guide.
Monitoring autorefresh cache groups ■ Displaying information from the change log tables ■ Understanding messages about autorefresh in the support log ■ Diagnosing autorefresh failure ■ Diagnosing autorefresh performance problems ■ Using SNMP traps for alerts about autorefresh problems Using the ttCacheAutorefreshStatsGet procedure The ttCacheAutorefreshStatsGet procedure returns information about the last ten autorefresh operations on a specified cache group.
Monitoring autorefresh cache groups Table 4–1 (Cont.) ttCacheAutorefreshStatsGet results from last autorefresh operation Result Field name Description 44 autorefNumber Autorefresh number 0 autorefDuration The number of milliseconds spent in this autorefresh operation. It is zero because the operations is in progress. 75464 autorefNumRows The number of rows autorefreshed in this autorefresh operation.
Monitoring autorefresh cache groups 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.
Monitoring autorefresh cache groups 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.
Optimize Performance for IMDB Cache 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. The error message for thread ID 4724 shows that the change log table, TT_03_81799_L, is missing. The introduction to "Autorefresh not refreshing cache at the specified interval" on page 4-16 has a table entry that describes what to do in this situation.
Autorefresh not refreshing cache at the specified interval enabled, the parallel query optimizer generates a query plan that allows 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 machine. Then, experiment to understand what level of parallelism works best for their environment.
Autorefresh not refreshing cache at the specified interval Possible cause What to do 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 Oracle objects" on page 4-17. Check the user error log for messages about "fatal anomalies".
Incremental autorefresh not progressing 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.
Incremental autorefresh becomes full autorefresh ■ ■ ■ 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.
Incremental autorefresh becomes full autorefresh 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.
Incremental autorefresh becomes full autorefresh operations are successful. See "Using the ttCacheAutorefreshStatsGet procedure" on page 4-11. If the status is InProgress longer than seems reasonable, see "Poor autorefresh performance" on page 4-22. 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.
Poor autorefresh performance 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.
Poor autorefresh performance 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.
Poor autorefresh performance 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.
Poor autorefresh performance 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.
Poor autorefresh performance Table 4–2 (Cont.) Recovery for cache groups involved in active standby replication pair DeadDbRecovery Setting Active Master Standby Master Resulting Behavior 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 ttDesctroy utility.
Poor autorefresh performance Number of updates that has not been marked with a valid logseq: 0 **************************** Host name: conobar-tt Timesten datastore name: /scratch/ds/myDB Cache table name: SCOTT.
Poor autorefresh performance ■ The log table can be abnormally large if rows inserted into the 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.
5 Troubleshooting AWT Cache Groups 5 Creating an asynchronous writethrough (AWT) cache group automatically creates a replication scheme that allows the data store to communicate with the Oracle database. You must start the replication agent after you create an AWT cache group and start the cache agent. See "Creating an AWT cache group" in the Oracle In-Memory Database Cache User's Guide. Material in Chapter 6, "Troubleshooting Replication" is useful for troubleshooting AWT cache group problems.
Replication does not work Replication does not work If you are unable to get replication working, the problem may be one or more of the following: Possible Cause See...
Permanent Oracle errors reported by TimesTen ■ ■ ■ Total number of TimesTen row operations: The total number of rows updated in AWT cache groups since monitoring was enabled. Total number of TimesTen transactions: The total number of transactions in AWT cache groups since monitoring was enabled. Total number of flushes to Oracle: The total number of times that TimesTen data has been sent to Oracle.
Transient Oracle errors reported by TimesTen Example 5–1 Cache violation occurs when update is propagated to Oracle If a constraint violation occurs when a cache group update is propagated to Oracle, the message in the AWT error file is similar to the following: Error occurred 14:48:55 on 03-22-2007 Datastore: c:\temp\cgDSN Oracle Id: system1 Transmitting name: cgDSN Error message: TT5210: Oracle unique constraint violation error in OCIStmtExecute(): ORA-00001: unique constraint (GUSER.
Transient Oracle errors reported by TimesTen Some transient errors indicate an underlying problem on the Oracle database must be solved before AWT operations can continue. For example: ORA-01536: space quota exceeded for tablespace ORA-01034: ORACLE not available After the underlying problem has been fixed, AWT retries the operation. For more information about the Oracle errors, see Oracle Database Error Messages for the Oracle release you are using.
Transient Oracle errors reported by TimesTen ORA-01109: database not open ORA-01147: SYSTEM tablespace file %s is offline ORA-01154: database busy.
Transient Oracle errors reported by TimesTen ORA-06035: ORA-06036: ORA-06037: ORA-06039: ORA-06040: ORA-06108: ORA-06113: ORA-06114: ORA-06143: ORA-06315: ORA-06316: ORA-06317: ORA-06318: ORA-06319: ORA-06320: ORA-06404: ORA-06413: ORA-10435: ORA-10626: ORA-10906: ORA-12150: ORA-12151: ORA-12152: ORA-12153: ORA-12154: ORA-12155: ORA-12156: ORA-12157: ORA-12158: ORA-12159: ORA-12160: ORA-12161: ORA-12162: ORA-12163: ORA-12166: ORA-12168: ORA-12169: ORA-12170: ORA-12171: ORA-12196: ORA-12197: ORA-12198: ORA-
Transient Oracle errors reported by TimesTen ORA-12218: TNS:unacceptable network configuration data ORA-12219: TNS:missing community name from address in ADDRESS_LIST ORA-12221: TNS:illegal ADDRESS parameters ORA-12222: TNS:no such protocol adapter ORA-12223: TNS:internal limit restriction exceeded ORA-12224: TNS:no listener ORA-12225: TNS:destination host unreachable ORA-12226: TNS:operating system resource quota exceeded ORA-12227: TNS:syntax error ORA-12228: TNS:protocol adapter not loadable ORA-12229:
Transient Oracle errors reported by TimesTen ORA-12536: ORA-12537: ORA-12538: ORA-12539: ORA-12540: ORA-12541: ORA-12542: ORA-12543: ORA-12544: ORA-12545: ORA-12546: ORA-12547: ORA-12549: ORA-12550: ORA-12551: ORA-12552: ORA-12554: ORA-12555: ORA-12556: ORA-12557: ORA-12558: ORA-12560: ORA-12561: ORA-12562: ORA-12564: ORA-12566: ORA-12569: ORA-12570: ORA-12571: ORA-12574: ORA-12582: ORA-12583: ORA-12585: ORA-12589: ORA-12590: ORA-12591: ORA-12592: ORA-12593: ORA-12595: ORA-12596: ORA-12600: ORA-12602: ORA-
Transient Oracle errors reported by TimesTen ORA-16903: Unable to connect to database ORA-16914: Missing connect string. Try \"help\" ORA-18014: deadlock detected while waiting for resource %s ORA-21521: exceeded maximum number of connections in OCI (object mode only) ORA-21522: attempted to use an invalid connection in OCI (object mode only) ORA-23317: a communication failure has occurred ORA-24401: cannot open further connections ORA-24418: Cannot open further sessions.
6 Troubleshooting Replication 6 This chapter describes how to troubleshoot some of the problems you may encounter when replicating data stores.
Unable to alter a replication scheme Possible cause What to do Replication tables defined in the CREATE REPLICATION statement do not exist. The name, owner, and column definitions of the tables participating in the replication scheme must be identical on both the master and subscriber data stores. Use CREATE TABLE to create tables on the data store, or use the ttRepAdmin -duplicate utility or the ttRepDuplicateEx C function to duplicate the entire data store to be replicated.
Replication does not work Possible cause What to do Data store does not participate in a replication scheme. If a data store does not participate in a replication scheme, attempts to start a replication agent for that data store will fail. Use CREATE REPLICATION to create a replication scheme for the data store. Using SNMP traps for notification of replication events TimesTen can send SNMP traps for certain replication events to enable network management software to take immediate action.
Replication does not work "Starting and stopping the replication agents" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide. If neither the TimesTen daemon or replication agents are running, the output looks like that shown in Example 6–3. In this case, confirm you have correctly installed TimesTen and the Data Manager service is started, as described in "TimesTen Installation" in the Oracle TimesTen In-Memory Database Installation Guide.
Replication does not work Replication policy : Manual Cache agent policy : Manual -----------------------------------------------------------------------End of report Example 6–2 Replication agent is not running > ttStatus TimesTen status report as of Tue Oct 28 10:31:30 2006 Daemon pid 3396 port 15000 instance MYINSTANCE TimesTen server pid 3436 started on port 15002 ----------------------------------------------------------------Data store c:\temp\subscriberds There are no connections to the data store c
Replication does not work < SUBSCRIBERDS, MYHOST, 0, pause, 1, 10, REPSCHEME, REPL > 1 row found. To reset state to Start call the ttRepSubscriberStateSet procedure: Command> CALL ttRepSubscriberStateSet('REPSCHEME', 'REPL', 'SUBSCRIBERDS', 'MYHOST', 0) Command> CALL ttReplicationStatus ('subscriberds'); < SUBSCRIBERDS, MYHOST, 0, start, 1, 152959, REPSCHEME, REPL > 1 row found.
Replication does not work Table details ------------Table : REPL.TAB Master Name ----------MASTERDS Subscriber Name ------------SUBSCRIBERDS Check the TTREP.TTSTORES table Check the TTREP.TTSTORES table to confirm that replication associates the replication scheme with the local data store. Example 6–7 Confirm that the replication scheme is associated with the local data store Connect to the data store and enter: SELECT * FROM ttrep.ttstores WHERE is_local_store <> 0x0; Command> select * from ttrep.
Replication does not work Check owner names As described in "Table requirements and restrictions" and "Owner of the replication scheme and tables" in the Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide, the owner names of your replication scheme and your replicated tables must be consistent across all participating data stores.
Replication does not work Output for SYSTEM1 Output for SYSTEM2 SYS.CACHE_GROUP SYS.CACHE_GROUP SYS.COLUMNS SYS.COLUMNS SYS.COL_STATS SYS.COL_STATS SYS.INDEXES SYS.INDEXES SYS.MONITOR SYS.MONITOR SYS.PLAN SYS.PLAN SYS.TABLES SYS.TABLES SYS.TBL_STATS SYS.TBL_STATS SYS.TRANSACTION_LOG_API SYS.TRANSACTION_LOG_API REPL.TAB REPL.TAB TTREP.REPELEMENTS TTREP.REPELEMENTS TTREP.REPLICATIONS TTREP.REPLICATIONS TTREP.REPPEERS TTREP.REPPEERS TTREP.REPSTORES TTREP.REPSTORES TTREP.
Replication unresponsive, appears hung Output for SYSTEM1 Output for SYSTEM2 TTREP.REPTABLES TTREP.REPTABLES TTREP.TTSTORES TTREP.TTSTORES Check consistency between replicated tables Replicated tables on both master and subscriber data stores must be exactly the same. Example 6–12 Check consistency between replicated tables This output from the user error log shows a mismatch on the number of columns for the subscriber table TTUSER.MYDSN. 11:37:58.00 Info: REP: 9430: REP1:transmitter.
Poor replication or XLA performance Command> CALL ttReplicationStatus ('subscriberds'); < SUBSCRIBERDS, MYHOST, 0, failed, 1, 10, REPSCHEME, REPL > 1 row found. Check return receipt timeout setting Use the ttRepSyncGet procedure to check the return receipt timeout setting. A value of -1 indicates the application is to wait until it receives an acknowledgement from the subscriber. Network latency or other issues might delay receipt of the subscriber acknowledgment.
Poor replication or XLA performance Note: The performance degradation caused by return-receipt becomes less of an issue when multiple applications (or threads) are updating the data store. If you must use return-receipt in a transaction, you can improve the performance of your application by using multiple threads to update the data store. Though each thread must block for receipt confirmation, the other threads are free to make updates.
Poor replication or XLA performance % ttIsql -v1 -e "select log_fs_reads from monitor; quit;" -connStr dsn=MASTERDSN If the LOG_FS_READS counter is increasing, the log readers are falling behind or clearing out a backlog in the transaction log files.
Problems using ttRepAdmin Normally the 'Logs' value should be '1', as shown in Example 6–14. A steadily increasing 'Logs' value indicates latency is increasing and eventually log reads are satisfied from disk. If the LogBufMB is larger than the LogFileSize, an increase in the 'Logs' value does not necessarily mean the log readers are reading from the transaction log files.
Problems with conflict checking Problems using ttRepAdmin -duplicate If you connected to your new subscriber DSN before running ttRepAdmin -duplicate, the data store has already been created. In this situation, -duplicate returns: Error : Restore not done : The datastore already exists. Unable to restore datastore locally Confirm the existence of the data store by running ttStatus and checking to see if the data store is in the returned list.
Problems with conflict checking ■ Timestamp does not exist ■ Conflict reporting slows down replication Column cannot be used for replication timestamp When attempting to set CHECK CONFLICTS for an element in a CREATE REPLICATION statement, you may encounter an error similar to the following: 8004: Column REPL.TABS.TS cannot be used for replication timestamp checking if in an index or added by ALTER TABLE; and must be binary(8) with NULL values allowed.
Index A AgentTimeout parameter, 4-23 aging monitoring, 1-14 ALTER REPLICATION troubleshooting, 6-2 asynchronous writethrough cache group, see AWT cache group autorefresh diagnose, 4-22 Failed status, 4-14 fails, 4-9 full, 4-19 incremental, 4-19 interval too small, 4-28 monitoring change log tables, 4-13 SQL script, 4-13 support log, 4-13 monitoring the change log tables, 4-13 performance, 4-22, 4-23, 4-26, 4-28 setting FREELISTS, 4-26 setting INITRANS, 4-26 trace, 1-16 AUTOREFRESH trace, 4-22 AWT cache grou
D daemon support log, 1-6 user error log, 1-6 database dead, 4-23 recover, 4-23 datablock availability, 4-15 dbms_shared_pool.
SELECT gives duplicate results, 2-20 REFRESH CACHE GROUP failure, 4-10 replication agent unable to stop or start, 6-2 log buffer, 6-12 performance conflict reporting, 6-16 poor performance, 6-11 troubleshooting, 5-2, 6-1, 6-3 rollbacks tracing, 1-10 row lock waits, 4-26 S sar reporting tool, 2-16 sar tool, 2-16 SELECT statement duplicate results, 2-20 semaphore removal, 2-5 server failures, 2-7 maximum concurrent connections, 2-7 SGA datablock availability, 4-15 keep portion, 4-15 shared memory estimate si
using, 4-3 ttOptSetFlag procedure, 2-12 ttOptUpdateStats procedure, 2-11 ttRepAdmin utility troubleshooting, 6-14 ttRepStart utility, 4-3 ttSize utility estimate memory, 2-5 ttStatus utility using, 1-2 ttTraceMon utility AGING tracing, 1-14 API tracing, 1-10 AUTOREFRESH tracing, 1-16 DEADLOCK tracing, 1-10 error tracing, 1-13 LOCK tracing, 1-12 output format, 1-8 SQL tracing, 1-9 using, 1-6 ttXactAdmin utility using for troubleshooting, 1-19 U ulimit command, 2-5 UNIC ps command, 2-5 UNIX ipcrm command, 2-
