WIXLIB

Automatic passthrough of dynamic load to the Oracle database6Managing a Caching EnvironmentChecking the status of cache and replication agents6-1Checking the status of the cache agents in TimesTen Scaleout6-1Checking the status of the cache and replication agents in TimesTen Classic6-2Cache agent and replication connection recovery6-3Managing a cache environment with Oracle Database objects6-4Monitoring cache groups6-6Using the ttIsql utility cachegroups command6-7Monitoring autorefresh operations on cache groups6-8Monitoring AWT cache groups6-8Configuring a transaction log file threshold for AWT cache groups6-8Tracking DDL statements issued on cached Oracle Database tables6-9Dropping Oracle Database objects used by cache groups with autorefresh6-10Impact on cache groups when modifying the Oracle database schema6-12Impact of failed autorefresh operations on TimesTen databases6-12Monitoring the cache administration user's tablespace6-16Defragmenting change log tables in the tablespaceManually defragmenting the change log tables for cache groups with autorefresh6-166-18Receiving notification on tablespace usage6-19Recovering from a full tablespace6-20Backing up and restoring a TimesTen Classic database with cache groups6-21Backing up and restoring using the ttBackup and ttRestore utilities6-21Backing up and restoring TimesTen Classic database with the ttMigrate utility6-23Changing cache user names and passwords75-366-26Cache PerformanceDynamic load performanceManaging a cache connection pool to the Oracle database for dynamic load requests7-17-2Enable the cache connection pool7-3Size the cache connection pool7-5Use the ChildServer connection attribute to identify a child server process7-6Dynamically applying cache connection pool sizing modifications7-6Example demonstrating management of the cache connection pool7-7Limiting the number of connections to the Oracle database7-8Restrictions for the cache connection pool7-9Improving AWT throughputImproving AWT throughput with parallel propagation to the Oracle Database7-97-10vii

Table constraint restrictions when using parallel propagation for AWT cache groups7-12Manually initiate check for missing constraints for an AWT cache group7-15Configuring batch size for parallel propagation for AWT cache groups7-16Improving AWT throughput with SQL array processing7-17Improving performance for autorefresh operations7-18Minimizing delay for cached data with continuous autorefresh7-18Reducing contention for dynamic read-only cache groups with incremental autorefresh7-18Requirements for setting DynamicLoadReduceContention7-19Reducing lock contention for read-only cache groups with autorefresh and dynamicload7-20Options for reducing contention between autorefresh and dynamic load operations7-21Improving performance when reclaiming memory during autorefresh operations7-21Running large transactions with incremental autorefresh read-only cache groups7-22Using ttCacheAutorefreshXactLimit7-23Example of potential transactional inconsistency7-24Retrieving statistics to evaluate performance when a transaction limit is set7-28Configuring a select limit for incremental autorefresh for read-only cache groups89107-28How to determine which intervals have a particular select limit7-29Retrieving statistics to evaluate performance when using a select limit7-30Retrieving statistics on autorefresh transactions7-30Caching the same Oracle table on two or more TimesTen databases7-31Cleaning up the Caching EnvironmentStopping the replication agent8-1Dropping a cache group8-1Stopping the cache agent8-2Destroying the TimesTen databases8-3Dropping Oracle Database users and objects8-3Scheduling a shutdown of active standby pair with AWT cache groups8-4Using TimesTen Cache in an Oracle RAC EnvironmentHow TimesTen Cache works in an Oracle RAC environment9-1Restrictions on using TimesTen Cache in an Oracle RAC environment9-4Setting up TimesTen Cache in an Oracle RAC environment9-5Using TimesTen Cache with Data GuardComponents of MAA for TimesTen Cache10-1TimesTen Cache works with asynchronous Active Data Guard10-1Configuring the primary and standby Oracle databases10-3viii

Configuring Oracle Database services through role based services10-3Configuring Oracle Database services through system triggers10-4Configuring the active standby pair with read-only cache groups10-7Recovery after failure when using asynchronous Active Data Guard10-8Failure of the standby Oracle database10-8Failure of the primary Oracle database10-9Failure of the primary siteTimesTen Cache works with synchronous Data Guard1110-12Configuring the Oracle databases for TimesTen Cache and synchronous Data Guard10-12Configuring the TimesTen database to work with synchronous Data Guard10-15Using GoldenGate as an alternative to native read-only cache groupsTimesTen and GoldenGate support for cache refresh11-2Considerations when using GoldenGate as the cache refresh mechanism11-2Configuring GoldenGate to provide cache refresh functionality for TimesTen11-3Choosing on-box or off-box for deployment of a GoldenGate Replicat process11-4Direct mode connectivity11-4Client-server connectivity11-5Installing and configuring TimesTen and the target database11-5Create TimesTen database users, tables and synonyms11-6Installing and configuring a TimesTen client instance (for off-box deployments only)11-7Configure GoldenGate data apply11-8Perform an initial load11-9Start GoldenGate continuous real-time replicationComplete end to end exampleA10-1011-1011-10Prepare TimesTen users and target tables11-12Prepare Oracle Database for GoldenGate replication11-13Prepare the TimesTen database for GoldenGate replication11-13Perform the initial data load11-14Start real-time replication11-15Verify that replication is working11-15Procedure and Privileges for Caching Oracle Database Data inTimesTenQuick reference to cache Oracle Database data in TimesTenA-1Required privileges for cache operationsA-3ix

BSQL*Plus Scripts for TimesTen CacheInstalled SQL*Plus scriptsCB-1Compatibility Between TimesTen and Oracle DatabasesSummary of compatibility issuesC-1Transaction semanticsC-1API compatibilityC-2JDBC API ultSetMetaDataC-4Stream supportC-4ODBC API compatibilityC-5SQL compatibilityC-5Schema objectsC-6Caching Oracle Database partitioned tablesC-6Nonschema objectsC-7Differences between Oracle Database and TimesTen tablesC-7Data type supportC-7SQL operatorsC-8SELECT statementsC-8SQL subqueriesC-9SQL functionsC-9SQL expressionsC-11INSERT/DELETE/UPDATE/MERGE statementsC-11TimesTen-only SQL and built-in proceduresC-12PL/SQL constructsC-13Mappings between Oracle Database and TimesTen data typesC-13Indexx

PrefaceOracle TimesTen In-Memory Database (TimesTen) is a relational database that is memoryoptimized for fast response and high throughput. The database resides entirely in memory atruntime and is persisted to the file system. Oracle TimesTen In-Memory Database in classic mode, or TimesTen Classic, refers tosingle-instance and replicated databases. Oracle TimesTen In-Memory Database in grid mode, or TimesTen Scaleout, refers to amultiple-instance distributed database. TimesTen Scaleout is a grid of interconnectedhosts running instances that work together to provide fast access, fault tolerance, andhigh availability for in-memory data. TimesTen alone refers to both classic and grid modes (such as in references to TimesTenutilities, releases, distributions, installations, actions taken by the database, andfunctionality within the database). TimesTen Cache refers to a set of features that together enable the caching of cachingperformance-critical subsets of an Oracle database into cache tables within a TimesTendatabase for improved response time in the application tier. Cache tables can be readonly or updatable. Applications read and update the cache tables using standardStructured Query Language (SQL) while data synchronization between the TimesTendatabase and the Oracle database is performed automatically. TimesTen Replication features, available with TimesTen Classic or TimesTen Cache,enable high availability.TimesTen supports standard application interfaces JDBC, ODBC, and ODP.NET; Oracleinterfaces PL/SQL, OCI, and Pro*C/C ; and the TimesTen TTClasses library for C .AudienceThis guide is for application developers who use and administer TimesTen, and for systemadministrators who configure and manage TimesTen databases that cache data from Oracledatabases. To work with this guide, you should understand how relational database systemswork. You should also have knowledge of SQL, and either Open Database Connectivity(ODBC), Java Database Connectivity (JDBC), or Oracle Call Interface (OCI).Related documentsTimesTen documentation is available on the TimesTen documentation website.Oracle Database documentation is also available on the Oracle documentation website. Thismay be especially useful for Oracle Database features that TimesTen supports but does notattempt to fully document, such as OCI and Pro*C/C .xi

PrefaceConventionsTimesTen Classic is supported on multiple platforms. Unless otherwise indicated, theinformation in this guide applies to all supported platforms. The term Windows refers toall supported Windows platforms. The term UNIX applies to all supported UNIXplatforms. The term Linux is used separately. See "Platforms and Compilers" in OracleTimesTen In-Memory Database Release Notes (README.html) in your installationdirectory for specific platform versions supported by TimesTen.Note:In TimesTen documentation, the terms "data store" and "database" areequivalent. Both terms refer to the TimesTen database.This document uses the following text conventions:ConventionMeaningboldfaceBoldface type indicates graphical user interface elementsassociated with an action, or terms defined in text.italicItalic type indicates book titles, emphasis, or placeholder variablesfor which you supply particular values.monospaceMonospace type indicates commands within a paragraph, URLs,code in examples, text that appears on the screen, or text that youenter.italic monospaceItalic monospace type indicates a placeholder or a variable in acode example for which you specify or use a particular value. Forexample:LIBS -Ltimesten home/install/lib -lttenReplace timesten home with the path to the TimesTen instancehome directory.[]Square brackets indicate that an item in a command line isoptional.{}Curly braces indicate that you must choose one of the itemsseparated by a vertical bar ( ) in a command line. A vertical bar separates alternative arguments.An ellipsis (. . .) after an argument indicates that you may usemore than one argument on a single command line.% or The percent sign or dollar sign indicates the UNIX shell prompt,depending on the shell that is used.#The number (or pound) sign indicates the prompt for the UNIX rootuser.TimesTen documentation uses these variables to identify path, file and user names:xii

PrefaceConventionMeaninginstallation dirThe path that represents the directory where the current release ofTimesTen is installed.timesten homeThe path that represents the home directory of a TimesTen instance.release or rrThe first two parts in a release number, with or without the dot. The firsttwo parts of a release number represent a major TimesTen release. Forexample, 221 or 22.1 represents TimesTenRelease 22.1.DSNThe data source name.Note:TimesTen release numbers are reflected in items such as TimesTen utility output,file names and directory names. These details are subject to change with everyminor or patch release, and the documentation cannot always be up to date. Thedocumentation seeks primarily to show the basic form of output, file names,directory names and other code that may include release numbers. You can confirmthe current release number by looking at the Release Notes or running thettVersion utility.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the Oracle AccessibilityProgram website at http://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers that have purchased support have access to electronic support through MyOracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx acc&id infoor visit http://www.oracle.com/pls/topic/lookup?ctx acc&id trs if you are hearing impaired.Diversity and InclusionOracle is fully committed to diversity and inclusion. Oracle respects and values having adiverse workforce that increases thought leadership and innovation. As part of our initiative tobuild a more inclusive culture that positively impacts our employees, customers, andpartners, we are working to remove insensitive terms from our products and documentation.We are also mindful of the necessity to maintain compatibility with our customers' existingtechnologies and the need to ensure continuity of service as Oracle's offerings and industrystandards evolve. Because of these technical constraints, our effort to remove insensitiveterms is ongoing and will take time and external cooperation.xiii

What's NewWhat's NewThis section summarizes new features and functionality of TimesTen Release 22.1 thatare documented in this guide, providing links into the guide for more information.New features in Release 22.1.1.1.0 You can use TimesTen Cache in both TimesTen Classic and TimesTen Scaleout,although TimesTen Scaleout only supports static read-only cache groups withincremental autorefresh. See "Using Cache Groups in TimesTen Scaleout" for fulldetails. A hybrid cache group is a dynamic read-only cache group where the root table iscreated in the TimesTen database and does not exist in the Oracle database. See"Hybrid cache group" for details. You can set the TT DynamicPassthrough optimizer hint to notify TimesTen Classicto pass through qualified SELECT statements to the Oracle database. See"Automatic passthrough of dynamic load to the Oracle database" for details. You can dynamically load multiple cache instances, see "Dynamically loadingmultiple cache instances" for details. You may prefer to use Oracle GoldenGate to refresh data from the backend Oracledatabase to a TimesTen cache instead of using the built-in native cache refreshmechanism of TimesTen Cache. See "Using Oracle GoldenGate as an alternativecache refresh mechanism" for details. There are now two LRU aging policies for TimesTen Classic:–LRU aging based on set thresholds for the amount of permanent memory inuse.–LRU aging based on row thresholds for a specified root tables of your cachegroups.See "LRU aging in TimesTen Classic" for details. As a result of changes in the Oracle Database, the privileges required for cacheoperations have been updated. See "Required privileges for cache operations" fordetails. An additional table and trigger were added for cache operations. TheTT version CACHED COLUMNS table stores list of columns that are cached. Andinstead of a single trigger, there are now two triggers to handle different aspects ofautorefresh operations. See "Managing a cache environment with OracleDatabase objects" for details.xiv

1TimesTen Cache ConceptsTimesTen Cache improves the performance for your applications access to data. TimesTenCache is used to cache Oracle Database data and reduce the workload on the Oracledatabase. It provides the connection and transfer of data between an Oracle database and aTimesTen database, as well as facilitating the capture and processing of high-volume eventflows into a TimesTen database and subsequent transfer of data into an Oracle database.You can cache Oracle Database data in a TimesTen database within cache groups. A cachegroup in a TimesTen database can cache a single Oracle Database table or a group ofrelated Oracle Database tables.You can use TimesTen Cache in both TimesTen Classic and TimesTen Scaleout, althoughTimesTen Scaleout only supports a single cache group type. See "Cache group types" fordetails.This chapter includes the following topics: Overview of cache groups Cache instance Cache group types Transmitting changes between the TimesTen and Oracle databases Using Oracle GoldenGate as an alternative cache refresh mechanism High availability caching solutionOverview of cache groupsCache groups define the Oracle Database data to be cached in a TimesTen database. Acache group can be defined to cache all or part of a single Oracle Database table, or a set ofrelated Oracle Database tables.Figure 1-1 shows the target customers cache group that caches a subset of a single OracleDatabase table customer.1-1

Chapter 1Overview of cache groupsFigure 1-1Single-table cache groupTimesTenCache group target customerscustomerregionname.122WestJim Johnston.663MidWestPat Reed.cust num*Oracledatabasecustomer122WestJim John.342WestJane Stone.663MidWestPat Reed.942EastTerry Boone .You can cache multiple Oracle Database tables in the same cache group by defining aroot table and one or more child tables. A cache group can contain only one root table.The root table does not reference any table with a foreign key constraint. In a cachegroup with multiple tables, each child table must reference the root table or anotherchild table in the same cache group using a foreign key constraint. Although tables in amultiple-table cache group must be related to each other in the TimesTen databasethrough foreign key constraints, the corresponding tables in the Oracle database donot necessarily need to be explicitly related to each other. The tables on the Oracledatabase can be related: Explicitly through a foreign key constraint. Implicitly related by the user application (those that update the Oracle database).The user application could maintain a relationship between tables that is notenforced by the Oracle database.See "Multiple-table cache group" for more details about the characteristics of amultiple-table cache group.While you may have multiple TimesTen databases that synchronize with the sameOracle database, they each operate independently. Thus, any data cached in separateTimesTen databases each synchronize with the Oracle database independently.An Oracle Database table cannot be cached in separate cache groups within the sameTimesTen database. However, the table can be cached in separate cache groups1-2

Chapter 1Cache instancewithin different TimesTen databases. If the table is cached in separate cache groups and thesame cache instance is changed simultaneously on multiple TimesTen databases, there is noguarantee as to the order in which the changes are propagated to the cached OracleDatabase table. The contents of such cache groups in different TimesTen databases may notbe consistent at a given point in time.Cache instanceData is loaded from an Oracle database into a cache group within a TimesTen database inunits called cache instances. A cache instance is defined as a single row in the cache group'sroot table together with the set of related rows in the child tables.Figure 1-2 shows three tables in the customer orders cache group. The root table iscustomer. orders and order item are child tables. The cache instance identified by the rowwith the value 122 in the cust num primary key column of the customer table includes: The two rows with the value 122 in the cust num column of the orders table (whose valuein the ord num primary key column is 44325

Configuring a TimesTen database to cache Oracle Database data3-5. Specify database connection definition for TimesTen Cache3-6. Define a DSN for the TimesTen Classic database3-6. Define database definition and connectable in TimesTen Scaleout3-7. Set the Oracle Database Net Service Name in the tnsnames.ora file3-7. Create the TimesTen users3-8 .