Oracle® TimesTen In-Memory Database

1.1 Changes for Release 11.2.1.2.0 from Release 11.2.1.1.0

• When using TimesTen OCI, incorrect results could be returned when these actions occurred:

  • Execute a SELECT statement.

  • Fetch an array.

  • Execute the SELECT statement again with new define variables.

  This problem has been fixed. (BugDB #8592579)

• Dynamic read-only cache groups can be replicated only by using an active standby pair. If you attempt to use another type of replication scheme for a dynamic read-only cache group, TimesTen now returns an error. You must now detach a grid member from the grid before you drop an active standby pair replication scheme that is defined within the member database. (BugDB #8581905)

• The ttGridNodeStatus built-in procedure returned information about the status of a standby master data store even though the active standby pair had been dropped. This problem has been fixed. (BugDB #8392567)

• When a PL/SQL package procedure was called incorrectly, an incorrect error could be returned. This problem has been fixed. (BugDB #8541146)

1.2 Changes for Release 11.2.1.1.0 from Release 11.2.1.0.0

For details about behavior changes in Release 11.2.1.1.0, see the Behavior Changes document.

• Access Control has been changed completely to be more compatible with Oracle security standards. See "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide and "Privileges" in Oracle TimesTen In-Memory Database SQL Reference. There is new syntax for the ALTER USER, CREATE USER, DROP USER, GRANT and REVOKE SQL statements. See Oracle TimesTen In-Memory Database SQL Reference.

• By default, you must associate a TimesTen database with a cache grid before you can create cache groups within that database. See Oracle In-Memory Database Cache User's Guide.

• TimesTen integration with Oracle Clusterware has had significant changes. The syntax for the ttCWAdmin utility has changed. Attributes for the cluster.oracle.ini file have been changed. See "Clusterware Attributes for TimesTen" and "ttCWAdmin" in Oracle TimesTen In-Memory Database Reference and "Using Oracle Clusterware to Manage Active Standby Pairs" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

• The configuration for the transaction log buffer file size has been modified. See Oracle TimesTen In-Memory Database Operations Guide.

• JDBC now maps ODBC data types as follows:

  • SQL_FLOAT to Types.DOUBLE

  • SQL_DOUBLE to Types.VARCHAR

  • SQL_REAL to Types.FLOAT

1.3 Changes for Release 11.2.1.0.0 from Release 7.0.5.2.0

• The release numbering system has been changed. In the release number a.b.c.d.e, a.b.c represents the TimesTen release (11.2.1), d represents the patch release (0) and e represents the port patch (0).

• Demos are NOT added by default during installation. You must choose to include the demos at installation time.

• The TimesTen client library implementation of SQLPrepare performs a deferred prepare. The request is not sent to the server until required. See Oracle TimesTen In-Memory Database C Developer's Guide.

• Support for TimesTen has been added for these platforms: AIX 6.1, Asianux 3.0, Monta Vista 5.0, Windows Vista and Windows Server 2008.

5.1 Deprecated items in Release 11.2.1.1.0

• LogBuffSize attribute. Use LogBufMB instead. The units have changed from kilobytes to megabytes.

• Including sequence values in a CREATE VIEW statement is deprecated.

5.2 Removed items in Release 11.2.1.1.0

• Authenticate attribute

• It is not possible to set the Logging attribute to 0. The only possible value is 1.

• ttCommitLSN built-in procedure

• ttLogBufPrint built-in procedure

• ttuser built-in procedure

• ttUserPrivileges built-in procedure

• The -at and -af options of the ttXactLog utility have been removed.

5.3 Deprecated items in Release 11.2.1.0.0

• The GroupRestrict and Authenticate attributes are deprecated. They will be removed in a later release.

5.4 Removed items in Release 11.2.1.0.0

• SELECT FOR UPDATE ON ORACLE SQL statement

• DURATION clause in CREATE CACHE GROUP and ALTER CACHE GROUP SQL statements

• The ttCacheAgingMemoryThresholdSet and ttCacheAgingIntervalSet built-in procedures

• The TransparentLoad attribute and the TT_TRANSPARENT_LOAD ODBC option have been removed. Use dynamic cache groups instead.

• The OracleID attribute has been removed. Use OracleNetServiceName instead.

• The ExclAccess attribute has been removed.

• The Cache Administrator (web-based user interface) has been removed.

• TimesTen no longer supports these platforms: AIX 5.2, HP-UX 11i v1, RedHat 3, Solaris 8 (Sparc), Solaris 10 x86 32-bit, SuSE Linux 9 and TRU 64.

• TimesTen no longer supports Oracle9i or Oracle 10.1.0.x.

• TimesTen no longer supports JDK 1.4.x on all platforms. Also, classes15.jar and classes16.jar have been removed.

• TimesTen no longer supports the gcc 3.2.3 compiler, Visual C++ 6.0, Visual Studio .NET 2002 and Sun Studio 10.

5.5 Deprecated items in Release 7.0.3.0.0

• The ttCacheMonitor built-in procedure is deprecated. It is supported for backward compatibility only and will be removed in a later release.

5.6 Deprecated items in Release 7.0.1.0.0

These items are supported for backward compatibility only and will be removed in a later release.

• WITH LIMIT clause in CREATE CACHE GROUP and ALTER CACHE GROUP SQL statements

• ttCachePolicy built-in procedure. Use ttCachePolicySet instead.

• Restricted cache groups

• The ttRepPolicy built-in procedure is deprecated. Use ttRepPolicySet instead.

• The ttXlaDeleteBookmark built-in procedure is deprecated. Use ttXlaBookmarkDelete instead.

6.1 Installing and uninstalling TimesTen

• On Windows systems, you cannot perform a modified or incremental installation. For example, if you originally installed only the Oracle TimesTen Client and later wanted to install the Oracle TimesTen Data Manager, you need to uninstall TimesTen and reinstall all of the components you wish to have on your system.

• When re-installing TimesTen on UNIX, existing sys.odbc.ini and sys.ttconnect.ini files are optionally saved as sys.odbc.ini.old and sys.ttconnect.ini.old files and new demo files are installed. In this case, you must manually merge any additional DSNs that may have been defined after reinstalling.

• If you are installing Oracle TimesTen on a new 64-bit RedHat Linux system, you may find that it is not enabled for 32-bit applications. According to the Redhat Enterprise Linux installation guide: 'Users of AMD64, Intel(R) EM64T, and Itanium systems who want support for developing or running 32-bit applications are encouraged to select the Compatibility Arch Support and Compatibility Arch Development Support packages to install architecture specific support for their systems.'

• When uninstalling TimesTen on Windows, the oci.dll file shipped with the TimesTen installation sometimes cannot be removed because it is in use by a process that is not part of TimesTen.

6.2 PL/SQL

• PL/SQL is not available in TimesTen on the Windows operating system.

• Some subprograms in PL/SQL system packages are not supported. See "TimesTen Supplied PL/SQL Packages" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for details.

• PLSQL_CODE_TYPE=NATIVE can be specified, but it is implemented as INTERPRETED.

• JRockit on 32-bit Linux allocates a default heap that is 50 percent of available physical memory up to 1 gigabyte. Users of JRockit on 32-bit platforms may need to choose a value for PLSQL_MEMORY_ADDRESS other than the default (10000000). Consider values such as B0000000, which ensures that the PL/SQL shared memory is not part of the JRockit heap.

• Using q' (quoting syntax) is not supported.

• The EXECUTE IMMEDIATE statement cannot be used to execute a PL/SQL anonymous block.

• UTL_FILE access is limited to a temporary directory located in install_dir/plsql/utl_file_temp. The instance administrator can grant access to UTL_FILE to specific database users. Users can reference the directory using UTL_FILE if and only if they provide the string 'UTL_FILE_TEMP' or 'utl_file_temp' for the location parameter string.

6.3 TimesTen OCI support

See "Oracle Call Interface Support" in Oracle TimesTen In-Memory Database C Developer's Guide for detailed information about known problems and limitations.

• If NLS_LANG is set to a value that is not supported by TimesTen, spurious errors such as "Cannot connect" may result.

6.4 TimesTen support for Oracle Pro*C/C++ Precompiler

• When a variable is declared as "long long" on 32-bit Solaris and 32-bit HP-UX platforms, Pro*C generates its size as "long".

• When compiling a Pro*C/C++ demo, this message may appear: "System default option values taken from: install_dir/ttoracle_home/instantclient_11_1/precomp/admin/pcscfg.cfg." The path name may be incorrect.

See "TimesTen Support for Oracle Pro*C/C++ Precompiler" in Oracle TimesTen In-Memory Database C Developer's Guide for detailed information about known problems and limitations.

6.5 TimesTen implementation of Oracle Clusterware

• The TimesTen implementation of Oracle Clusterware is not available on these platforms: Solaris 32-bit, SPARC, HP-UX, PA-RISC, HP-UX Itanium 32-bit, AIX 32-bit.

• Spurious internal errors in the logs may be generated during TimesTen Clusterware operations. These errors are benign. Run ttCWAdmin -status to verify the status of the system.

6.6 JDBC

• In JDBC client/server mode, if you reuse a Statement with an open ResultSet for update, you may get an Invalid Cursor State error. To avoid this, use another Statement for update.

• On HPUX 11 systems, 32-bit JDBC client and direct drivers cannot co-exist in one JVM due to some problems with loading shared libraries.

• If a JDBC application running in a time zone that has Daylight Savings Time selects a non-existent time using ResultSet.getTimestamp, it gets a time that is an hour behind. For example, in Pacific Standard Time, on the day when the time changes from Standard to Daylight Savings Time, the time between 2:00 a.m. and 2:59 a.m. does not exist. So, if a JDBC application running in Standard Time selects a value of '2002-04-07 02:00:00' using getTimestamp it will get '2002-04-07 01:00:00'.

6.7 JMS/XLA

• JMS/XLA depends on the Oracle GDK for internationalization support. TimesTen distributions include a version of orai18n.jar in install_dir/lib that JMS/XLA is tested against. JMS/XLA may work with other versions of orai18n.jar but it is not supported. See Oracle TimesTen In-Memory Database Java Developer's Guide for other details.

• To achieve the highest throughput, JMS/XLA applications running on Linux should follow performance guidelines presented in the section entitled “Handling high event rates” in Oracle TimesTen In-Memory Database Java Developer's Guide to work around these issues.

6.8 IMDB Cache

• If DDL tracking is enabled on the Oracle database, destroying a data store that has cache groups may result in the DDL trigger on a cached table not being dropped from the Oracle database. The user must manually drop each of these triggers using the DROP TRIGGER statement on the Oracle database. The DDL trigger name of a table with an Oracle object with identifier X is TT_X_DDL_T. The object identifier of an Oracle table can be retrieved from the V$ALL_OBJECTS view on the Oracle database.

• Oracle In-Memory Database Cache supports the following Oracle server releases:

  • Oracle 11g Release 1 (All platforms except Windows x64)

  • Oracle 10g Release 2 (10.2.0.4.0 or above)

• Oracle server bug 7512227 causes TimesTen autorefresh to miss inserts and updates on the Oracle base table. Autorefresh does not miss deletes. The Oracle Server bug applies to Oracle releases 10.2.0.3 and above but does not occur in Oracle Database 11g. There are two solutions:

  • Set the CURSOR_SHARED Oracle server parameter to EXACT.

  • Install the appropriate Oracle patch for Oracle release 10.2.0.3 or above.

  See Metalink document 793948.1.

• Caching an Oracle synonym that is owned by a different user results in error 5140: Could not find owner.synonym in Oracle. May not have privileges. The workarounds are:

  • The owner of the synonym can create the cache group.

  • Create a new private synonym that points to the original synonym and then create the cache group. The user who owns the cache group that caches the synonym must match the owner of the synonym.

• Local dynamic asynchronous writethrough (AWT) cache groups have a problem when there are update or delete operations on a dynamically loaded row that is waiting to be propagated to Oracle. Such operations may result in dynamically loading the same row again.

  There are 3 scenarios in which such a situation can arise. For example:

  • A delete of a dynamically loaded row on TimesTen followed by a select operation results in a dynamic load because the row no longer exists in TimesTen. The application should use ttRepSubscriberWait to ensure that the changes are propagated to Oracle. The application should also use ttRepSubscriberWait for update operations to ensure data integrity.

  • When time-based aging is configured, a dynamically loaded row is deleted at the end of the time interval. To avoid reloading the same row while any changes made to this rows are propagated to Oracle, the aging interval should be set to a large enough interval to ensure that changes to dynamically loaded rows are propagated to Oracle.

  • When LRU aging is configured, the aging threshold should be set large enough so that any changes to the rows that are aging out are pushed to Oracle before they are dynamically loaded again.

  Use a global AWT cache group if it is appropriate for your application. Global AWT cache groups resolve these problems automatically.

• For IMDB Cache to work correctly, the value returned by the hostname command and the uname -n command (UUCP address) must be the same. If hostname and uname -n return different values, IMDB Cache may report constraint violation failures on Oracle. Alternatively, you can use uname -s to make sure that the internet and the UUCP addresses are the same.

• In a two-safe replication scheme that replicates a cache group, a transaction may fail in the following scenario:

  • The transaction that is being replicated contains an ALTER TABLE statement or an ALTER CACHE GROUP statement

  • The transaction contains an INSERT, UPDATE or DELETE statement to a replicated table, replicated cache group or an AWT cache group.

• Set SQLNET.SEND_TIMEOUT and SQLNET.RCV_TIMEOUT to a value large enough to avoid unnecessary timeouts during normal operations. For example, setting each parameter to 100 seconds forces any communication call from TimesTen to Oracle to time out within 200 seconds.

• With Passthrough=3, if a JDBC application calls setTimestamp on a passthrough statement when binding more than one field, the session NLS_TIMESTAMP_FORMAT is lost. You can work around this issue by changing the SQL statement to use TO_TIMESTAMP(?, 'syyyy-mm-dd hh24:mi:ss.ff9') and TO_DATE(?,'syyyy-mm-dd hh24:mi:ss').

• Nullable foreign keys can result in rows without a corresponding parent row. The rows may become stale and not be refreshed properly through a manual refresh or autorefresh operation. They may not get removed through unload or aging operations, and they may not be updated on Oracle through a flush statement.

• When using IMDB Cache, the following JDBC methods are not supported with Oracle pass-through modes 1 through 3: PreparedStatement.setAsciiStream, PreparedStatement.setUnicodeStream, PreparedStatement.setBinaryStream, PreparedStatement.setCharacterStream.

6.9 TimesTen to TimesTen Replication

• If the first operation in a two-safe user session is an ALTER TABLE operation, the ALTER TABLE will not be replicated. The workaround is to do some other operation on the table in the same session before altering the table.

• Under very rare circumstances, it is possible for a pair of replicated detail tables and their corresponding materialized view to diverge. This divergence can only happen if the materialized view satisfies both of the following conditions:

  • The view definition has 2 or more predicates.

  • One of the predicates contains an expression that can generate an exception such as numeric overflow, division by zero or string truncation.

  Replicating an update to a detail table can cause divergence if differences in the order of predicate evaluation on the two data stores cause a predicate to be evaluated and generate an exception only on the receiving data store. The store will then reject the update, resulting in divergence of the detail table and the materialized view. To prevent such divergence, users should avoid replicating materialized views that have predicates that can generate exceptions during expression evaluation. The SQL CAST operator can be used in some cases to avoid arithmetic overflow.

• Foreign key and uniqueness constraints cannot be checked at the statement level if ttXlaApply is used to implement replication.

• For replication to work efficiently, it must be able to quickly translate host names of peers into IP addresses. For this to happen efficiently on Windows, make sure that a Windows machine is set up to query either a valid WINS server or a valid DNS server that has correct information about the hosts on the network. In the absence of such servers, static HOST-to-IP entries can be entered in either a %windir%\system32\drivers\etc\hosts or %windir%\system32\drivers\etc\lmhosts file.

  Without any of these four options, a Windows machine resorts to broadcasting, which is extremely slow, to detect peer nodes. Also, if the Windows machine cannot communicate with a defined WINS server or DNS server, or if incorrect information is stored on those servers, the hostname resolution will be extremely slow. Use the ping command to test whether a host can be efficiently located. The ping command should immediately respond if host name resolution is set up properly.

• When changing the state of a replication receiver (that is, to “start” or “stop”) when replication has been configured to use a secondary IP address, a misconfiguration of the /etc/hosts file may cause ttRepAdmin to print the error: Alter replication with 'ALTER REPLICATION ... port 0' failed: TT0907: Unique constraint (REPSTORESIX) violated. This error is caused by replication not recognizing the local data store. This situation can be confirmed by the following query:

  SELECT * FROM ttrep.ttstores WHERE is_local_store <> 0x0;
  

  If this query returns either no rows, or a row with the data store's main host name set to the result of the hostname command rather than the host you specified, you have encountered a configuration problem with /etc/hosts.

  To correct the problem, you must make sure that the special host name you are using is defined in /etc/hosts and that there is an IP address in common between your special host name and the result of the hostname command.

  Example: your hostname command returns “softswitch” and your machine has two Ethernet cards with the addresses 10.10.15.136 and 192.168.15.136. Then the IP addresses defined for “softswitch” should include both IP addresses.

6.10 SQL*Plus

• TimesTen does not support SQL*Plus connections to TimesTen databases.

6.11 SQL, utilities and procedures

• There is a known issue with ttIsqlCS and prompted OUT parameters. When running a PL/SQL block that uses OUT parameters, ttIsql using the Client/Server Driver does not recognize that the parameters are OUT and does not display the updated values (autoprint). Use the variable command to declare values for these parameters before execution. Use the print command to print the parameters after execution.

• When using scalar functions that accept TIME, DATE or TIMESTAMP data types as arguments, you must prefix the function with a literal indicating the data type. For example:

  SELECT GREATEST (TIME '10:10:10', TIME '12:12:12' ) FROM DUAL; 
  

  If you do not specify the datetime literal, TimesTen treats the data type as CHAR. Functions that accept datetime literals as arguments are: GREATEST, LEAST, ROUND and ADD_MONTHS.

• ROUND(date) is supported only for Oracle DATE types.

• For ROUND (date), ADD_MONTHS, GREATEST, and LEAST FUNCTIONS: An assertion may occur during SQL statement preparation:

  • When using either a CASE expression or the DECODE function as one of the arguments to the function and the expected data type is datetime AND

  • The return values of either the CASE expression or the DECODE function are NULL or parameter binding (i.e.: decode (x,0,NULL, ?)).

  For example:

  SELECT GREATEST (CASE WHEN 'A' LIKE '__' THEN? END, TIME '11:11:11') FROM DUAL;
  

  As a workaround, CAST the bind variable or NULL value to the expected datetime data type:

  SELECT GREATEST (CASE WHEN 'A' LIKE '__' THEN CAST (? as TIME) END, TIME '11:11:11') FROM DUAL;
  

• The ORDER BY clause is not supported in a CREATE TABLE AS SELECT statement.

• The maximum sum of the total number of tables specified in a query and all temporary aggregates needed to handle the query is 32. A temporary aggregate is needed to handle scalar or aggregate subqueries. A query fails with the message “Statement that needs more than 31 nesting levels has not been implemented” when the sum of tables and temporary aggregates in a query is greater than 32.

• COUNT distinct with CHAR type uses binary sorting order and binary comparison semantics even when the NLSSORT attribute was set to a value different than binary.

• When the NLS_SORT session parameter is set to a multilingual sort (e.g. FRENCH_M), the LIKE operator may produce incorrect results when the pattern match wild-card symbols are applied to the space character.

• SQL query strings in JDBC applications should contain only characters from the database character set. Unicode characters not in the database character set are converted to replacement characters during parsing of the query. Potential workarounds include:

  • Using AL32UTF8 as the database character set.

  • Parameterizing the query so as to avoid non-database character set characters in the query text.

• The TO_CHAR SQL function provides inconsistent results (truncates or rounds) when applied to BINARY_FLOAT and BINARY_DOUBLE values. TO_CHAR also truncates some NUMBER values that should instead be rounded.

• The MERGE statement may not be used to insert into or update from a detail table of a materialized view.

• If an application process on HP-UX is connected to a TimesTen data store and if the application process calls the ttXactIdRollback utility API for a transaction associated with the same data store, the call may fail due to the inability to attach to the shared memory segment for the data store.

• The fourth argument (“stats”) to the ttOptSetColIntvlStats built-in procedure is a compound structure that cannot be parameterized using ODBC functions. This means that a statement such as the following will fail:

  SQLPrepare(hstmt, “call ttOptSetColIntvlStats('t1', 'c1', 1, ?)”, SQL_NTS);
  

  Similarly, the ttIsql describe command fails because the “stats” argument does not have one of the known types.

6.12 TTClasses

• A problem with TTClasses causes NCHAR / NVARCHAR2 parameters and columns to be bound incorrectly. This results in parameter and column data being transferred to/from the database without a null terminator.

6.13 Client/Server

• In the Windows ODBC Client DSN Setup dialog box, when the Server Name refers to a UNIX host, the Refresh button to get the list of DSNs on that server sometimes fails to obtain the list. Enter a known DSN in the Server DSN field to create the client DSN entry successfully. TimesTen connections from Windows using that client DSN will work normally.

• When setting multiple client connections to a TimesTen server, do not set the stack size (using the -ServerStackSize daemon option or ServerStackSize attribute) to less than 64KB on 64-bit systems and 48KB for 32-bit systems. If the stack size is too small, the TimesTen Server dies with SIGSEGV on most UNIX system, or SIGBUS on HP-UX. The default stack size is 128 KB on 32-bit systems and 256 KB on 64-bit systems.

• On UNIX, when using ttlocalhost, a client of one TimesTen instance cannot connect with a server of another TimesTen instance. For example, a 32-bit TimesTen client cannot connect to a 64-bit TimesTen server when using ttlocalhost. The workaround is to use ttShmHost (shared memory IPC) or localhost (127.0.0.1).

• While using shared memory as IPC, the application may see the error message 24 from the Client Driver if the application reaches the system-defined, per process file descriptor limit. 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.