Skip Headers
Oracle® TimesTen In-Memory Database Troubleshooting Procedures Guide
Release 11.2.1

Part Number E13075-07
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

2 Troubleshooting TimesTen Applications and Databases

The following sections provide information to help you diagnose and remedy some of the problems encountered while using a TimesTen database:

Note:

If you are still having problems with your database after following the troubleshooting recommendations in this chapter, please contact Technical support.

Unable to start or stop TimesTen daemon

This section describes what to check if you are unable to start or stop the TimesTen main daemon.

Possible cause What to do
Incorrect privilege You need the ADMIN privilege to start or stop the TimesTen daemon. Ensure that you are using the ttDaemonAdmin utility to start the daemon. The output from ttDaemonAdmin shows whether you have the correct privilege.
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 operating system 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. Ensure that you are using the ttDaemonAdmin utility to start the daemon. The output from ttDaemonAdmin shows whether the daemon is already running.
Other problems Inspect the user error log produced by the daemon. See "Using the logs generated by the TimesTen daemon".

No response from TimesTen daemon or subdaemon

The following sections describe what to do if one or more of the TimesTen processes appears to be unavailable:

Check the TimesTen user error log

If you receive an error that indicates the TimesTen subdaemon has stopped, inspect the user error log, as described in "Using the logs generated by the TimesTen daemon".

If the TimesTen daemon crashes, it cannot send anything to the user error log, but the subdaemons send a 'main daemon vanished' message to the log before exiting:

09:24:13 Err : 4375 ------------------: Main daemon has vanished

Restart the daemon. The next connection to each database causes TimesTen to recover from the checkpoint and transaction log files. See "Working with the Oracle TimesTen Data Manager Daemon" in the Oracle TimesTen In-Memory Database Operations Guide.

Extract a stack trace from the core file

If you experience a crash by one of the TimesTen processes on a UNIX system and have exhausted all of the diagnostic options, check to see if TimesTen has generated a core file. Use the ttVersion utility to find the core file. Look for a line in the output that shows a path for the daemon home directory:

TimesTen Release (ttuser:40732)
2007-04-04T17:53:04Z
   Instance admin: ttuser
   Instance home directory:
/node1/ttuser/ttcur/TTBuild/linux86_dbg/install
   Daemon home directory:
/node1/ttuser/ttcur/TTBuild/linux86_dbg/install/info

After locating the core file, attach to the debugger on the system and extract the stack trace from the core file and send the trace results to Technical support.

On Windows systems you can obtain diagnostic information for a service failure by enabling the 'allow service to interact with desktop' option in the properties dialog for the TimesTen data manager in the Service menu. If a fatal fault occurs in the TimesTen data manager service, a pop-up asks if you would like to start the debugger. Contact Technical support and provide the stack trace.

Unable to create shared segment

You may receive an error that indicates that a shared segment could not be created:

4671: TT14000: TimesTen daemon internal error: Error 28 creating shared segment, KEY 0x0201f7eb
4671:  -- OS reports too many shared segments in use
4671:  -- Confirm using 'ipcs' and take appropriate action
4671: 18538 ------------------: subdaemon process exited

Using the Linux ipcs command may display information like this:

------ Shared Memory Segments --------
 key        shmid      owner     perms      bytes      nattch     status
 0x00000000 1098350592 user1     777        10624      2          dest
 0x00000000 1084817409 user1     777        2439680    2          dest
 0x911fc211 1098383362 user2     666        67108864   1
 0x2814afba 170721285  root      666        1048576    1

A status of dest means the memory segment is marked to be destroyed. nattch shows the number of processes still attached to the memory segment. The ipcrm command cannot free the shared memory until the processes detach from the segment or exit. If an application connects to TimesTen and then becomes inactive, nothing can free the shared memory until the user exits or stops the application.

Application unable to connect to database in direct mode

This section describes what to check if your application is unable to connect to a database in direct mode.

Possible cause See...
Mismatch between the release of TimesTen and database "Upgrading your database"
User does not have the CREATE SESSION privilege. "Privileges to connect to database"
Incorrect file permissions "Check file system permissions to access database"
TimesTen daemon or Data Manager service not running "Check that the TimesTen daemon is running"
Incompatible connection attributes or incorrect path name for database set in the DSN "Check DSN definition"
No available shared memory segment or maximum size of shared memory segment too small "Manage semaphores and shared memory segments"
Not enough swap space "Check available swap space (virtual memory)"
Inadequate number of file descriptors "Increase the number of available file descriptors"
Other possible causes "Using the logs generated by the TimesTen daemon"

Upgrading your database

A database is only guaranteed to be accessible by the same minor release of TimesTen that was used to create the database. When you upgrade the TimesTen software and you would like to use the new release to access a database that was previously created, create a database with the new release. Then use the ttMigrate utility to copy the tables, indexes, and table data from the old database to the new one.

See "Database Upgrades" in the Oracle TimesTen In-Memory Database Installation Guide for details.

Privileges to connect to database

The user must have the CREATE SESSION privilege to connect to the database. If you do not have access, the administrator must use the GRANT statement to grant you the CREATE SESSION privilege. See "Granting privileges to connect to the database" in the Oracle TimesTen In-Memory Database Operations Guide.

Check file system permissions to access database

A "permission denied" error is generated if you attempt to connect to a database and you do not have the proper permissions to access the checkpoint or transaction log files or the directory where those files reside. Check the file system permissions on the files located in the directory specified in the DataStore attribute in your DSN.

Check that the TimesTen daemon is running

If the TimesTen daemon or Data Manager service is not running, an attempt to connect to a database generates TimesTen error 799 "Unable to connect to daemon; check daemon status."

Use the ttStatus utility as described in "Check the TimesTen user error log" to check the status of the TimesTen daemon.

Check DSN definition

In your DSN description, perform the following:

Check DSN attributes

Certain connection options or DSN attribute settings combinations are not compatible. In cases where incompatible settings are used, an error is returned to the application when it attempts to connect to a database.

Check path name to database and transaction log directories

Confirm that you have specified the correct path names in the DataStore and LogDir attributes in your DSN. Also confirm that the path names are absolute path names, rather than relative. Otherwise, the path name will be relative to the directory where the application was started.

On Windows, be careful to distinguish between User and System DSNs in the ODBC Data Source Administrator. Do not create user DSNs because they are visible only to the user who defines them. System DSNs are visible to all users. In particular, if you run a TimesTen application as a Windows service, it runs as the user SYSTEM by default and does not see any User DSNs. Make sure that you are not using a mapped drive in the database path name.

Manage semaphores and shared memory segments

An error is generated if you attempt to connect to or create a shared database 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.

On UNIX systems, use commands similar to the following:

  • ipcs -ma to check if you have other shared memory segments using up memory, such as Oracle instances or other instances of TimesTen.

  • ipcrm to remove a message queue, semaphore set or shared memory segment identifier. Use ipcrm to clean up semaphores or shared memory segments after a faulty TimesTen shutdown, instance crash, daemon crash or other application issues that use shared memory segments and semaphores. Use -m to remove a shared memory segment. Use -s to remove a semaphore.

  • ps -eafl to see how much memory is being used by running processes.

  • ulimit -a to see if there are any limits on the maximum amount of memory one process can address, maximum file size, and the maximum number of open files.

If a shared memory segment is available but is too small to hold your database, use the ttSize utility to estimate the amount of memory required for your tables and then check the values of the PermSize and TempSize attributes to verify the amount of memory established for your database. "Monitoring PermSize and TempSize attributes" in the Oracle TimesTen In-Memory Database Operations Guide describes guidelines for setting the size of your permanent and temporary data partitions. If the amount of memory established for your database is too large, reset PermSize and TempSize to smaller values. See "Check the amount of memory allocated to the database" for more information. Another option is to increase the maximum size of the shared memory segment, as described below.

If a database becomes invalidated because of a system or application failure, a subsequent connection recovers the database. If recovery fails because you have run out of database space, then reconnect to the database with a larger PermSize and TempSize value than the ones that are currently in effect. If recovery fails because you do not have enough shared memory, then you should increase the maximum size of the shared memory segments for the system.

For more information on how to configure shared memory for TimesTen, see "Installation prerequisites" in the Oracle TimesTen In-Memory Database Installation Guide.

Check available swap space (virtual memory)

There must be enough swap space to back up shared memory.

On UNIX systems, use the swap command to check and add virtual memory to your system.

On Windows systems, check and reset the size of your virtual memory from the Advanced tab in your Computer Management Properties dialog window.

Increase the number of available file descriptors

Each process connected to a TimesTen database 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 database, then increase the allowable number of file descriptors. See your operating system documentation for limits on file descriptors and information about changing the number of file descriptors.

Troubleshooting Client/Server problems

This section includes the following topics:

Cannot connect to the TimesTen Server

You have not correctly identified the system where the TimesTen Server is running.

On a Windows client machine, select the TimesTen Server in the TimesTen Data Source Setup dialog that is displayed as part of the ODBC Data Source Administrator. To verify the TimesTen Server:

  1. On the Windows Desktop, choose Start > Settings > Control Panel.

  2. Double click the ODBC icon. This opens the ODBC Data Source Administrator.

  3. Click the System DSN tab. This displays the System Data Sources list.

  4. Select the TimesTen Client data source. This opens the TimesTen Client DSN Setup dialog.

  5. Click Servers. This opens the TimesTen Logical Server List.

  6. Select the TimesTen Server from the list. This opens the TimesTen Logical Server Name Setup dialog.

  7. Verify that the values for the Network Address and Port Number are correct. If necessary, change the values.

    Note:

    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.

If the Network Address and Port Number values are correct, the TimesTen Server may not be running. See "Starting and stopping the Oracle TimesTen Data Manager service on Windows" in the Oracle TimesTen In-Memory Database Operations Guide for information about starting the server manually. See "Testing connections" in the Oracle TimesTen In-Memory Database Operations Guide for more information about identifying this problem.

On UNIX, specify the TimesTen Server with the TTC_Server connection attribute in the odbc.ini file on the client machine. If the value specified for TTC_Server is an actual hostname or IP address, the client tries to connect to the TimesTen Server using the default port. In TimesTen, the default port is associated with the TimesTen release number. If the value specified for TTC_Server is a logical ServerName, this logical ServerName must be defined in the ttconnect.ini file. The ttconnect.ini entry for this ServerName needs to correctly define the hostname/IP address and port number on which the TimesTen Server is listening.

If the Network Address and Port Number values are correct, the TimesTen Server may not be running or did not start. See "Starting and stopping the daemon on UNIX" in the Oracle TimesTen In-Memory Database Operations Guide for information about starting the server manually. See "Testing connections" in the Oracle TimesTen In-Memory Database Operations Guide for more information about identifying this problem.

TimesTen Server failed

Check the server's log file. Server log messages are stored in the files specified by the -userlog and -supportlog options in the ttendaemon.options file. See "Creating and configuring Client DSNs on UNIX" and "Managing TimesTen daemon options" in the Oracle TimesTen In-Memory Database Operations Guide.

The maximum number of concurrent IPC connections to the Server of a particular TimesTen instance is 24,999. However, TimesTen has a limit of 2043 connections (direct or client/server) to a single DSN.

Client/server users can change the file descriptor limit to support a large number of connections. For an example, see "Installation prerequisites" in the Oracle TimesTen In-Memory Database Installation Guide.

Cannot find Server DSN

On UNIX, verify that the Server DSN is defined in the sys.odbc.ini file on the machine running the TimesTen Server.

On Windows, verify that the Server DSN is defined as a System DSN in the ODBC Data Source Administrator on the machine running the TimesTen Server. See "Creating and configuring a logical server name on Windows" in the Oracle TimesTen In-Memory Database Operations Guide.

TimesTen Server failed to load DRIVER

This error only occurs on UNIX platforms. Open the sys.odbc.ini file on the machine running the TimesTen Server and locate the Server DSN you are trying to 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.

To set the timeout interval on Windows, see the instructions in "Setting the timeout interval and authentication" in the Oracle TimesTen In-Memory Database Operations Guide.

TimesTen Client loses connection with TimesTen Server

Check to see if the error was due to the Client timing out. Check the TimesTen Server's log to see why the Server may have severed connection with the Client. Use ping to determine if your network is up or try using telnet to connect to the TimesTen Server port number.

Failed to attach to shared memory segment for IPC

While using shared memory segment (SHM) as IPC, the application may see the following error message from the TimesTen Client ODBC Driver if the application reaches the system-defined per-process file-descriptor-limit.

SQLState    = S1000,
Native Error = 0,
Message     = [TimesTen][TimesTen 11.2.1 CLIENT]Failed to attach to shared memory 
segment for IPC. System error: 24

This may happen during a connect operation to the Client DSN when the shmat system call fails because the application has more open file descriptors than the system-defined per-process file descriptor limit. To correct this problem, you must increase your system-defined per-process file descriptor limit. For more information about file descriptor limits, see "System Limits" in the Oracle TimesTen In-Memory Database System Tables and Limits Reference.

Increasing the maximum server connections on Windows XP

On Windows XP, by default, there can be approximately 47 child server processes. You can increase the number of connections by setting the MaxConnsPerServer connection attribute in the ttendaemon.options file or in the DSN. This increases the number of connections to 47 times the MaxConnsPerServer value.

Thread stack overflow when using multiple client connections

On Solaris, you may receive messages in the user error log about thread stack overflow. On other platforms, you may receive messages about a segmentation fault that mention a possible thread stack overflow.

If these messages occur, increase the server stack size by one of the following methods:

  • Specify the -ServerStackSize option in the ttendaemon.options file. The ttendaemon.options file applies to all DSNs in the TimesTen instance.

  • Specify the ServerStackSize connection attribute for a specific DSN. This takes precedence over the value in the ttendaemon.options file.

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.

Out of space when DSN specifies new database

You may receive "out of space" messages if you change a DSN to specify a new database while there are existing connections to the original database in a system with multiple client connections. This can happen on 32-bit platforms if either database is close to 2 GB.

Close all connections to the original database. This causes a new server process to be created for connections to the database that is now specified in the DSN. Use the ttStatus utility to list the connections for the old database. Alternatively, you can restart the server by using the ttDaemonAdmin utility with the -restartServer option, which resets all client connections on all DSNs in the instance.

Application connects or disconnects are slow

This section describes what to check if you encounter slow connects and disconnects to a database.

Possible cause See...
Database is being recovered. "Check if database is being recovered"
ODBC tracing is enabled. "Check ODBC tracing"
Other possible causes "API tracing"

Check if database is being recovered

A slow connect may indicate that a TimesTen database is being recovered. This happens only for a first connect.

Check ODBC tracing

On Windows platforms, if ODBC tracing is enabled, it can slow connect and disconnect speeds. Double-click ODBC in the Control Panel to open the ODBC Data Source Administrator. Select the Tracing tab and confirm tracing is disabled. See "Using ODBC tracing".

Application becomes disconnected unexpectedly

If an application becomes disconnected from a TimesTen database, one of the following events occurs:

This section describes what to check if your application unexpectedly disconnects from the database.

Possible cause See...
Internal application error. "Check for ODBC or JDBC errors"
Failure of a concurrent application thread. "Check for ODBC or JDBC errors"

"Check the user error log"

If using a client/server connection, the client may have disconnected from the application. "Troubleshooting Client/Server problems"
An error in the TimesTen library Contact Technical support.

Check for ODBC or JDBC errors

Check for the following types of errors:

  • ODBC errors returned by the SQLError function

  • JDBC errors returned by the SQLException class

The application may have encountered a problem that caused it to exit prematurely, which in turn may have caused other connections to be forced to disconnect. Call SQLError after each ODBC call to identify error or warning conditions when they first happen. Examples of SQLError usage can be found in the demo programs and in "Retrieving errors and warnings" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

In more extreme cases, it may be helpful to use ttTraceMon 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" for details.

Check the user error log

If a TimesTen application disconnects without returning an ODBC error or any other warning, look through the user error log. See "Using the logs generated by the TimesTen daemon".

Application is slow

For details on how to maximize the performance of your application and TimesTen database, see:

This section describes some of the issues that impair performance.

Possible cause See...
Using client/server mode "Consider connection mode"
Outdated database statistics "Update statistics for your tables"
Committing transactions too frequently "Turn off autocommit mode" in the Oracle TimesTen In-Memory Database Operations Guide
DurableCommits attribute enabled "Use durable commits appropriately" in the Oracle TimesTen In-Memory Database Operations Guide
Not preparing SQL statements used more than once "Prepare statements in advance" in the Oracle TimesTen In-Memory Database Operations Guide
Wrong kind of index, too many indexes, wrong size for hash index "Select hash, range, or bitmap indexes appropriately" in the Oracle TimesTen In-Memory Database Operations Guide

"Size hash indexes appropriately" in the Oracle TimesTen In-Memory Database Operations Guide

Inefficient use of locks "Verify lock and isolation levels"
Improperly configured materialized view "Performance implications of materialized views" and "Materialized view tuning" in the Oracle TimesTen In-Memory Database Operations Guide
If replication is used, configuration of replication scheme or network environment may be impacting application. "Poor replication or XLA performance"
If IMDB Cache is used, IMDB Cache configuration or environment may be impacting application. "Poor autorefresh performance"
Too many table partitions "Check partition counts for the tables"
Tracing is unnecessarily enabled for one or more TimesTen components. "Check trace settings"

Consider connection mode

Client/server connections are slower than direct connections to TimesTen databases. Driver manager connections can also moderately impact performance. The performance overhead imposed by client/server connections can be significant because of the network latencies involved in all communication with the database.

If your application must run on a different machine from the one hosting the database, see "Client/Server tuning" in the Oracle TimesTen In-Memory Database Operations Guide.

Update statistics for your tables

The TimesTen query optimizer in general is very good at choosing the most efficient query plan. However, it needs additional information about the tables involved in complex queries in order to choose the best plan. By knowing the number of rows and data distributions of column values for a table, the optimizer has a much better chance of choosing an efficient query plan to access that table.

Before preparing queries that will access a TimesTen table, use the ttOptUpdateStats procedure to update the statistics for that table. When updating the statistics for a table, you get the best results if you update statistics on your tables after loading them with data, but before preparing your queries. For example, if you update statistics on a table before populating it with data, then your queries are optimized with the assumption that the tables contain no rows (or very few). If you later populate your tables with millions of rows and then execute the queries, the plans that worked well for the situation where your tables contained few rows may now be very 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 database can have a major impact on performance.

An application can acquire locks on the entire database, individual tables, and individual rows. Additionally, applications can set an isolation level that determines whether they hold read and update locks until their transactions commit or roll back.

Check the SYS.MONITOR table or use the ttXactAdmin utility to detect whether an application is spending time waiting for locks. See "Check for deadlocks and timeouts" and "Using the ttXactAdmin utility".

If lock contention is high, you may be able to improve the overall performance of your system by implementing the following:

  • Set the LockLevel configuration attribute or use the ttLockLevel procedure to place locks on rows, rather than on the entire database. Row locking is the default.

  • Use the ttOptSetFlag procedure to prevent the query optimizer from placing locks on tables. Table locks are sometimes the default, particularly for updates that affect many rows.

  • Use read-committed isolation level (Isolation=1, the default) for those applications do not require serializable access to the transaction data.

If you see a lot of lock contention, but the above settings are all set to minimize contention, then the contention may be related to the application itself. For example, concurrent threads may be repeatedly accessing the same row. The ttXactAdmin utility can sometimes help you detect this sort of contention. Tracing can also be useful in this situation.

For more information about locks and isolation levels, see "Concurrency control through isolation and locking" in the Oracle TimesTen In-Memory Database Operations Guide.

Check trace settings

Use ttTraceMon -e show as described in "Using the ttTraceMon utility" to confirm tracing is off on all TimesTen components. ERR should be set to 1; all other components should be set to 0. Trace levels are preserved when a database is reloaded.

On Windows platforms, confirm that ODBC tracing is disabled. Double-click ODBC in the Control Panel to open the ODBC Data Source Administrator. Select the Tracing tab and confirm tracing is disabled. See "Using ODBC tracing".

Check partition counts for the tables

When a table is created, it has one partition. When you use ALTER TABLE ... ADD COLUMN to add new columns, a new partition is added to the table. Adding multiple columns with a single ALTER TABLE ... ADD COLUMN statement only adds one partition.

There is a limit of 255 partitions per table. Exceeding this number generates error 8204. An extra read for each new partition slightly degrades performance for each of 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. Obtain the partition counts for tables by using the following query:

SELECT tblname, sys16 FROM SYS.TABLES;

If you discover that a table has too many partitions, do one of the following:

  • Re-create the table

  • Save and restore the table. Use ttMigrate -c to create a migration file. Then restore the table without additional partitions by using ttMigrate -r -noRepUpgrade.

ALTER TABLE ... DROP COLUMN does not remove partitions from a table. On replicated systems, all master and subscriber databases must be migrated using the -noRepUpgrade option. Replication does not occur for tables that have different partition structures.

Application unresponsive, appears hung

This section describes what to check if your application is unresponsive and appears to be hung.

Possible cause See...
All causes "Check logs and gather trace information"
Internal application error "Check for ODBC errors"
Inconsistent connection attributes set in DSN "Consider connection mode"
Excessive lock contention "Check for deadlocks and timeouts"

Check logs and gather trace information

If your application hangs, check the transaction log by using the ttXactAdmin utility. See "Using the ttXactAdmin utility".

Also check the user error log for errors, as described in "Using the logs generated by the TimesTen daemon" .

You can also generate a trace log to detect the activities on various TimesTen components as described in "Using the ttTraceMon utility" .

Check for ODBC errors

Check the ODBC errors returned by the SQLError function in all applications to determine whether one of them has encountered a problem that caused it to hang. Call SQLError after each ODBC call to identify error or warning conditions when they first happen. Examples of SQLError usage can be found in the demo programs and in "Retrieving errors and warnings" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

If the problem is repeatable, use ttTraceMon to generate a SQL trace to determine where the application is hanging. See "SQL tracing" 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" for details.

Check for deadlocks and timeouts

If there is no connect problem, a deadlock or timeout may be the problem. The SYS.MONITOR table records information about deadlocks and timeouts. See "Monitoring the TimesTen system tables" for information on how view the contents of this table. You can also use the ttXactAdmin utility to detect the types of locks currently held by uncommitted transactions and the resources on which they are being held.

If a deadlock occurs, the TimesTen subdaemon negotiates the problem by having an application involved in the deadlock generate TimesTen error 6002, "Lock request denied because of deadlock." The error message contains the SQL that the lock holder is running, which can help you diagnose the cause of the deadlock. If your application encounters this error, it should roll back the transaction and then reissue the statements for that transaction. Deadlocks can be caused if your application issues statements in a particular order that results in a circular wait, and can sometimes be prevented by changing the order in which the statements are issued.

An application encounters TimesTen error 6003, "Lock request denied because of timeout," if it is unable to acquire a lock within the time period defined by the lock timeout interval set by the LockWait attribute in the DSN or by the ttLockWait procedure in your application. Upon encountering a timeout error, your application can reissue the statement. Keeping transactions short reduces the possibility of lock timeout errors.

System tables are a common source of lock contention. Reduce contention on the system tables by executing prepared statements, rather than executing the same statements directly each time.

In multithreaded applications, a thread that issues requests on different connection handles to the same database may encounter lock conflict with itself. TimesTen resolves these conflicts with lock timeouts.

Application unable to find previously created objects

This section describes what to check if your application is unable to locate previously created tables, indexes, sequences or views in the database.

Possible cause See...
No owner or incorrect owner specified "Specify object owner"
User does not have SELECT privileges to tables. "Check privilege to access tables"
Database is temporary. "Check temporary DSN attribute"
Overwrite attribute is enabled. "Check Overwrite DSN attribute"
Path name specified in DSN is relative. "Check path name to database"

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). If TimesTen cannot find the table or index under the user's login ID, it then assumes that the owner is user SYS.

If applications need to connect to a database as different users and share objects, explicitly specify the owners of the objects when they are created and referenced.

Check privilege to access tables

All privileges for the user can be viewed in the SYS.USER_SYS_PRIVS table, which contains all of the system-level privileges for a given user, and the SYS.USER_TAB_PRIVS table, which contains all of the object-level privileges for a given user. Check these tables to verify if you have SELECT privilege for the tables. If you do not have SELECT privilege for the tables, the privilege may be granted with the GRANT statement. The method for granting privileges is described in the "Managing Access Control" chapter in the Oracle TimesTen In-Memory Database Operations Guide.

Check temporary DSN attribute

Temporary databases (DSN attribute: Temporary=1) persist until all connections to the database have been removed. When attempting to access a table in a temporary database and the table does not exist, it is possible that the database in which the table resided in has been dropped.

Check Overwrite DSN attribute

If the Overwrite and AutoCreate DSN attributes are enabled and the database already exists, TimesTen drops that database and creates a new one. Any tables that were created in the old database are dropped.

Check path name to database

To ensure that you are always accessing the same database when connecting to a particular DSN, use an absolute database path name instead of a relative one. For example, if the demo database is in the datastore directory, specify:

DataStore=/datastore/demo

rather than:

DataStore=demo

In the latter case, the database path name is relative to the directory where the application was started. If you are unable to find a table and you are using a relative database path name, it is possible that the database in which the table resides in does exist but the database (checkpoint and log) files are in a different directory than the one that you are accessing.

See "Specifying Data Source Names to identify TimesTen databases" in the Oracle TimesTen In-Memory Database Operations Guide.

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. This is likely only an issue on machines that previously had Oracle9i or earlier Oracle versions installed.

Refer to the "Globalization support" section in the OCI chapter of the Oracle TimesTen In-Memory Database C Developer's Guide for more information on NLS_LANG.

Running out of a resource

This section describes what to check if TimesTen runs out of resources such as memory space, disk space, file descriptors, and semaphores.

Operating system tools and shared memory

Operating system tools such as top, vmstat, and sar provide statistics about processes and memory usage. The output from these tools can be misleading as an indicator of TimesTen memory consumption because they report shared memory usage for each process but do not report total shared memory usage. Adding together various memory statistics for TimesTen processes overestimates the amount of memory used by TimesTen because shared memory is by definition shared.

Check the amount of memory allocated to the database

TimesTen uses both permanent and temporary data partitions. The amount of memory allocated for these partitions is set by the PermSize and TempSize attributes in the DSN definition for the database.

When the TimesTen database 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.MONITOR:

  • PERM_ALLOCATED_SIZE

  • PERM_IN_USE_SIZE

  • PERM_IN_USE_HIGH_WATER

  • TEMP_ALLOCATED_SIZE

  • TEMP_IN_USE_SIZE

  • TEMP_IN_USE_HIGH_WATER

The permanent segment consists of table and index data, while the temporary segment consists of internal structures, such as locks, sorting areas, and compiled commands.

Keeping transactions short and making sure there is enough temporary space in the database prevents locks from occupying all of the remaining temporary space. You can also use table locks if transactions are acquiring tens of thousands of row locks.

For tips on how to estimate the size of your database, see "Size your database correctly" in the Oracle TimesTen In-Memory Database Operations Guide.

Permanent segment filling up

Consider whether you can drop any indexes. You may want to look at query plans to see which indexes are actually used. See "Viewing and changing query optimizer plans" in the Oracle TimesTen In-Memory Database Operations Guide. You can also use the ttRedundantIndexCheck procedure to discover redundant indexes. The procedure returns suggestions about which indexes to drop.

Use the ttSize utility to estimate the amount of memory used by each table in the database. If the amount of data you need to store is too big, you may need to reset the PermSize attribute for the database to increase the size of the permanent segment. Alternatively, you may need to partition your data into several different databases if, for example, you cannot shrink the temporary segment or create a bigger database because of limits on the memory segment size.

Sometimes when the permanent segment fills up, copying the data out of the database, deleting all the data, and copying it back in frees up space. This can be done more efficiently by using the ttMigrate utility with the -noRepUpgrade option to migrate the data out, destroy and re-create the database, and migrate the data back in. This operation is described in "Reducing database size" in the Oracle TimesTen In-Memory Database Installation Guide.

Finally, you may have to configure the operating system to allow a larger amount of shared memory to be allocated to a process. You may also have to allocate more swap space for virtual memory. See "Check available swap space (virtual memory)".

Temporary segment filling up

Some commands may be allocating too much space because of out-of-date statistics. See "Update query optimizer statistics".

If updating the statistics does not reduce temporary segment memory usage, disconnect all connections and then reconnect them. Verify that all connections have 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".

If the problem is chronic, monitor the database to try to identify the source of the problem. Use the ttWarnOnLowMemory procedure to enable warnings in the user log that indicate that the database is filling up.

Update query optimizer statistics

If the database seems to have enough free space but runs out of database space when executing a query, make sure you have updated the optimizer statistics with the ttOptUpdateStats or ttOptEstimateStats procedure. To execute some queries, TimesTen needs to allocate temporary space. The amount of temporary space required is estimated from statistics about the tables used by the query. Without correct statistics, the temporary space required may be underestimated.

See "Using the query optimizer".

Check memory used by queries

You can check the memory that a query uses by observing the high water mark for temporary memory usage. The high water mark represents the largest amount of in-use temporary space used since the high water mark was initialized or reset.

Complete the following tasks:

  1. Use the ttIsql dssize command to check TEMP_IN_USE_SIZE and TEMP_IN_USE_HIGH_WATER. Alternatively, you can query the SYS.MONITOR table for these values.

  2. Call the ttMonitorHighWaterReset procedure to reset the TEMP_IN_USE_HIGH_WATER to the current value for TEMP_IN_USE_SIZE.

  3. Execute a query.

  4. Use dssize to check TEMP_IN_USE_HIGH_WATER for peak memory usage for the query.

Check available swap space (virtual memory)

If you receive an error indicating that you have run out of swap space, you may need to increase the amount of available swap space (also referred to as "virtual memory").

On UNIX systems, use the swap command to check and reset the amount of virtual memory currently established for your system.

On Windows systems, check and reset the size of your virtual memory by choosing Control Panel > System > Advanced.

Out of memory after fatal crash of the database

Fatal errors, such as errors 846 and 994, invalidate a TimesTen database. However, the database remains in memory, which is only freed after all users have disconnected from the database. If the database is restarted while users are connected to the invalidated database, both old and new instances exist in memory at the same time. In this case, users could receive out-of-memory conditions. To prevent an "Out of memory" error, disconnect all active connections at the time of the fatal error before reconnecting.

Check transaction log file use of disk space

TimesTen saves a copy of the database in one of two checkpoint files, which are stored in the directory specified by the DataStore attribute. Each checkpoint file can grow on disk to be equivalent to the size of the database in shared memory. For each permanent database, you must have enough disk space for the two checkpoint files and for transaction log files.

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. Transaction log files are used for numerous purposes in TimesTen, including checkpointing, backups, and replication. It is important to determine which operation is putting a "hold" on the transaction log files, so that appropriate action can be taken to allow the transaction log files to be purged. This can be done by using the ttLogHolds built-in procedure. There are six types of log holds. They are discussed in detail below.

  • Checkpoint - If a TimesTen application crashes and the database needs to be recovered, the checkpoint files and transaction log files are used to recover the data. The "most recent" transaction log files are used -- those written since the checkpoint was done. Transaction log files accumulate during the interval between checkpoints. Your application should periodically call the ttCkpt or ttCkptBlocking procedure to checkpoint the data and free up the space on the disk. If checkpoints are done very infrequently, a large number of transaction log files may accumulate, particularly if many changes are made to the database during that interval. See "Checkpoint Operations" in the Oracle TimesTen In-Memory Database Operations Guide.

  • Replication -TimesTen replication transmits changes to one database to one or more other databases. It does this by reading the log and sending any relevant changes. If replication is paused, the transaction log files build up. To prevent log build-up, avoid pausing replication for too long. Delete subscriptions entirely, and reset replication where appropriate. See "Setting the replication state of subscribers" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information on pausing and restarting or resetting replication.

  • Backup - TimesTen supports an incremental backup facility that uses transaction log files to augment a backup with changes made since the last backup. Transaction log files accumulate during the interval between incremental backups. To avoid a large log build-up, do incremental backups at relatively frequent intervals. If desired, disable incremental backups and do full backups instead. See "Copying, migrating, backing up and restoring a database" in the Oracle TimesTen In-Memory Database Operations Guide.

  • XLA - TimesTen's persistent XLA facility reports changes to the database by using transaction log files. Transaction log files are kept until the corresponding transactions have been acknowledged using the ttXlaAcknowledge C function. Call ttXlaAcknowledge frequently enough to prevent transaction log files building up. See "Retrieving update records from the transaction log" in the Oracle TimesTen In-Memory Database C Developer's Guide.

  • XA - TimesTen's XA support uses transaction log files to resolve distributed transactions. If these transactions are not resolved in a timely manner, transaction log files build up. See "Distributed Transaction Processing XA" in the Oracle TimesTen In-Memory Database C Developer's Guide.

  • Long-running transactions - TimesTen uses the transaction log to roll back transactions. A log hold is placed for the duration of a transaction. Transactions that are active for a long time result in log file building up if the transaction has written at least one log record. (That is, it is not a read-only transaction.) Commit write transactions with reasonable frequency to avoid significant log file build-up. 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). If the LogPurge attribute is set to the default value of 0, TimesTen renames transaction log files that it no longer needs by appending the string .arch to the name. Once renamed, you must delete the transaction log files manually when they are no longer needed. If transaction log files are not purged, they continue to accumulate space, even when no longer needed by TimesTen.

  • The Preallocate attribute indicates whether disk space should be reserved for checkpoint files at connect time. This is useful for big databases, to ensure that the disk always has room for the checkpoint files as data is added to the database.

Check if tracing is enabled

When tracing to a file has been enabled, the file may grow so large that a process attempting an operation may exceed the file limits. Tracing always appends to an existing file.

On certain platforms, the file size is limited to 2G. If you reach this limit, the process is terminated unless you catch the SIGXFSZ signal. The error shown is the "FILESIZE LIMIT EXCEEDED" error. Ensure that you want tracing enabled when using environments with strict file size limits.

Check the semaphore limit

When creating multiple client/server connections to a TimesTen database configured to allow shared memory segment as IPC, you may encounter errors that indicate TimesTen was unable to create a semaphore.

Semaphore limits are platform-dependent. See your operating system documentation and "Increase number of semaphores" in the Oracle TimesTen In-Memory Database Installation Guide.

Duplicate results from a SELECT statement

Using read-committed isolation level can lead to duplicates in a result set. A SELECT statement selects more or fewer rows than the total number of rows in the table if some rows are added or removed and committed in the range in which the SELECT scan is occurring. This may happen when an UPDATE, INSERT or DELETE statement adds or deletes a value from an index and the SELECT scan is using this index. This can also happen when an INSERT or DELETE adds or deletes rows from the table and the SELECT operation is using an all-table scan.

Index values are ordered. An UPDATE of an index value may delete the old value and insert the new value into a different place. In other words it moves a row from one position in the index to another position. If an index scan sees the same row in both positions, it returns the row twice. This does not happen with a serial scan because table pages are unordered and rows do not need to be moved around for an UPDATE. Hence once a scan passes a row, it will not see that same row again.

The only general way to avoid this problem is for the SELECT statement to use Serializable isolation. This prevents a concurrent INSERT, DELETE or UPDATE operation. There is no reliable way to avoid this problem with INSERT or DELETE by forcing the use of an index because these operations affect all indexes. With UPDATE, this problem can be avoided by forcing the SELECT statement to use an index that is not being updated.

For more information about Serializable isolation, see "Concurrency control through isolation and locking" in the Oracle TimesTen In-Memory Database Operations Guide.

Cannot attach PL/SQL shared memory

The PLSQL_MEMORY_ADDRESS first connection attribute determines the virtual address at which the PL/SQL shared memory segment is loaded into each process that uses the TimesTen direct drivers. Since each operating system platform has different mappings for its address space, the default values for the PL/SQL address space defined in the PLSQL_MEMORY_ADDRESS connection attribute are different for each platform, which avoids conflict with operating system mapped address space.

However, if your application overlaps with the PL/SQL mapped address space, you may receive error 8517 "Cannot attach PL/SQL shared memory; PLSQL_MEMORY_ADDRESS not valid or already in use." In this case, modify the setting for the PLSQL_MEMORY_ADDRESS connection attribute to eliminate the overlap. The reasons for receiving error 8517 can be one of the following:

To recover, specify a virtual address that is free for all processes that may connect to the database. If you have a 32-bit program that allocates large amounts of memory before connecting to TimesTen, it may clash with the PL/SQL shared memory segment. In this case, either allocate memory after connecting to TimesTen or use a 64-bit application. In a 64-bit environment, the options for reassigning to another memory address are less complicated than for a 32-bit operating system, where options are limited and potential for overlap is more common.

If an application accesses two or more TimesTen databases at the same time, you must modify the default setting for the PLSQL_MEMORY_ADDRESS attribute in all but one of the TimesTen databases, since the default settings would map the PL/SQL memory address to the same address for all TimesTen databases.