1/21

Contents

Title and Copyright Information

Preface

• Audience
• Related documents
• Conventions
• Documentation Accessibility

What's New

• New features in Release 11.2.2.7.0
• New features in Release 11.2.2.6.0
• New features in Release 11.2.2.5.0
• New features in Release 11.2.2.4.0
• New features in Release 11.2.2.2.0
• New features in Release 11.2.2.1.0
• New features in Release 11.2.2.0.0

1 TimesTen Application-Tier Database Cache Concepts

• Data management design
  • Overview of a cache grid
• Overview of cache groups
  • Cache instance
  • Cache group types
  • Transmitting updates between the TimesTen and Oracle databases
  • Loading data into a cache group: Explicitly loaded and dynamic cache groups
  • Sharing data across a cache grid: Local and global cache groups
  • Summary of cache group types
• High availability caching solution

2 Getting Started

• Setting up the Oracle Database and TimesTen systems
  • Create users in the Oracle database
  • Create a DSN for the TimesTen database
  • Create users in the TimesTen database
  • Set the cache administration user name and password in the TimesTen database
• Creating a cache grid
• Creating cache groups
  • Create the Oracle Database tables to be cached
  • Start the cache agent
  • Create the cache groups
  • Start the replication agent for the AWT cache group
• Attaching the TimesTen database to the cache grid
• Performing operations on the read-only cache group
  • Manually load the cache group
  • Update the cached Oracle Database table
• Performing operations on the dynamic updatable global cache group
  • Dynamically load the cache group
  • Update the TimesTen cache table
• Cleaning up the TimesTen and Oracle Database systems
  • Detach the TimesTen database from the cache grid
  • Stop the replication agent
  • Drop the cache groups
  • Destroy the cache grid
  • Stop the cache agent and destroy the TimesTen database
  • Drop the Oracle Database users and their objects

3 Setting Up a Caching Infrastructure

• Configuring your system to cache Oracle Database data in TimesTen
  • TimesTen Application-Tier Database Cache environment variables for UNIX
  • TimesTen Application-Tier Database Cache environment variables for Microsoft Windows
• Configuring the Oracle database to cache data in TimesTen
  • Create the Oracle database users
  • Grant privileges to the Oracle database users
  • Automatically create Oracle Database objects used to manage data caching
  • Manually create Oracle Database objects used to manage data caching
• Configuring a TimesTen database to cache Oracle Database data
  • Define a DSN for the TimesTen database
  • Create the TimesTen users
  • Grant privileges to the TimesTen users
  • Set the cache administration user name and password
• Configuring data management
  • Managing data locally
  • Configuring a cache grid
    • Modify the PROCESSES system parameter for ten or more grid nodes
    • Create a cache grid
    • Associate a TimesTen database with a cache grid
• Testing the connectivity between the TimesTen and Oracle databases
• Managing the cache agent
  • Set a cache agent start policy

4 Defining Cache Groups

• Cache groups and cache tables
  • Single-table cache group
  • Multiple-table cache group
• Creating a cache group
  • Read-only cache group
    • Restrictions with read-only cache groups
  • Asynchronous writethrough (AWT) cache group
    • Managing the replication agent
    • Configuring parallel propagation to Oracle Database tables
    • What an AWT cache group does and does not guarantee
    • Restrictions with AWT cache groups
    • Reporting Oracle Database permanent errors for AWT cache groups
  • Synchronous writethrough (SWT) cache group
    • Restrictions with SWT cache groups
  • User managed cache group
    • READONLY cache table attribute
    • PROPAGATE cache table attribute
    • Examples of user managed cache groups
  • AUTOREFRESH cache group attribute
    • Altering a cache group to change the AUTOREFRESH mode, interval or state
    • Manually creating Oracle Database objects for autorefresh cache groups
  • Using a WHERE clause
    • Proper placement of WHERE clause in a CREATE CACHE GROUP statement
    • Referencing Oracle Database PL/SQL functions in a WHERE clause
  • ON DELETE CASCADE cache table attribute
  • UNIQUE HASH ON cache table attribute
• Caching Oracle Database synonyms
• Caching Oracle Database LOB data
• Implementing aging in a cache group
  • LRU aging
  • Time-based aging
  • Manually scheduling an aging process
  • Configuring a sliding window
• Dynamic cache groups
• Global cache groups
  • Dynamic global cache groups
  • Explicitly loaded global cache groups
  • Start the replication agent
  • Attach a TimesTen database to a cache grid

5 Cache Group Operations

• Transmitting updates between the TimesTen and Oracle databases
• Loading and refreshing a cache group
  • Loading and refreshing an explicitly loaded cache group with autorefresh
  • Loading and refreshing a dynamic cache group with autorefresh
  • Loading and refreshing a cache group using a WITH ID clause
  • Initiating an immediate autorefresh
  • Loading and refreshing a multiple-table cache group
  • Improving the performance of loading or refreshing a large number of cache instances
  • Example of manually loading and refreshing an explicitly loaded cache group
  • Example of manually loading and refreshing a dynamic cache group
• Dynamically loading a cache instance
  • Dynamic load configuration
  • Dynamic load guidelines
  • Examples of dynamically loading a cache instance
  • Return dynamic load errors
• Flushing a user managed cache group
• Unloading a cache group
  • Unloading a cache group across all grid members
• Determining the number of cache instances affected by an operation
• Setting a passthrough level
  • PassThrough=0
  • PassThrough=1
  • PassThrough=2
  • PassThrough=3
  • PassThrough=4
  • PassThrough=5
  • Considerations for using passthrough
  • Changing the passthrough level for a connection or transaction

6 Creating Other Cache Grid Members

• Creating and configuring a subsequent standalone TimesTen database
• Replicating cache tables
  • Create and configure the active database
  • Create and configure the standby database
  • Create and configure the read-only subscriber database
• Example of data sharing among the grid members
• Performing global queries on a cache grid
  • Performing global queries with local joins
  • Obtaining information about the location of data in the cache grid
• Adding other elements to a cache grid or grid member

7 Managing a Caching Environment

• Checking the status of the cache and replication agents
  • Cache agent and replication connections
• Monitoring cache groups and cache grids
  • Using the ttIsql utility's cachegroups command
  • Monitoring autorefresh operations on cache groups
  • Monitoring AWT cache groups
  • Configuring a transaction log file threshold for AWT cache groups
  • Obtaining information for a cache grid
  • Suspending global AWT cache group operations
  • Tracking DDL statements issued on cached Oracle Database tables
• Managing a caching environment with Oracle Database objects
• Impact of failed autorefresh operations on TimesTen databases
• Dropping Oracle Database objects used by autorefresh cache groups
• Monitoring the cache administration user's tablespace
  • Defragmenting change log tables in the tablespace
    • Manually defragmenting the change log tables for autorefresh cache groups
  • Receiving notification on tablespace usage
  • Recovering from a full tablespace
• Recovering after failure of a grid node
• Backing up and restoring a database with cache groups
  • Backing up and restoring using the ttBackup and ttRestore utilities
  • Backing up and restoring with the ttMigrate utility
• Changing cache user names and passwords

8 Cache Performance

• Dynamic load performance
• Improving AWT throughput
  • Improving AWT throughput with parallel propagation
  • Improving AWT throughput with SQL array execution
• Improving performance when using incremental autorefresh for read-only cache groups
  • Improving execution of large transactions when using incremental autorefresh for read-only cache groups
    • Using ttCacheAutorefreshXactLimit
    • Example of potential transactional inconsistency
    • Retrieving statistics to evaluate performance when a transaction limit is set
  • Configuring a select limit when using incremental autorefresh for read-only cache groups
    • How to determine the cache group name for a particular select limit
    • Retrieving statistics to evaluate performance when using a select limit
• Improving performance when reclaiming memory during autorefresh operations
• Retrieving statistics on autorefresh transactions
• Caching the same Oracle table on two or more TimesTen databases

9 Cleaning up the Caching Environment

• Detaching a TimesTen database from a cache grid
• Stopping the replication agent
• Dropping a cache group
• Destroying a cache grid
• Stopping the cache agent
• Destroying the TimesTen databases
• Dropping Oracle Database users and objects
• Recommended method for a scheduled shutdown of an active standby pair with AWT cache groups

10 Using the Cache Advisor

• Cache Advisor overview
• Setting up the Oracle Database and TimesTen host systems
  • Configure the target Oracle database
  • Configure the repository Oracle database
  • Configure the TimesTen database
  • Cache Advisor configuration options and usage guidelines
    • Supported configuration options for hosts and databases
    • Restrictions and assumptions
• Running a SQL workload application
• Running the Cache Advisor
  • Improve performance by using NFS or FTP for transferring data
• Viewing the Cache Advisor reports
• Cleaning up the Oracle and TimesTen databases and host systems
  • Clean up the target Oracle database and host system
  • Clean up the repository Oracle database and host system
  • Clean up the TimesTen database and host system

11 Using TimesTen Application-Tier Database Cache in an Oracle RAC Environment

• How TimesTen Cache works in an Oracle RAC environment
• Restrictions on using TimesTen Cache in an Oracle RAC environment
• Setting up TimesTen Cache in an Oracle RAC environment

12 Using TimesTen Application-Tier Database Cache with Data Guard

• Components of MAA for TimesTen Application-Tier Database Cache
• How TimesTen Cache works with Data Guard
  • Configuring the Oracle databases
  • Configuring the TimesTen database

A Procedure and Privileges for Caching Oracle Database Data in TimesTen

• Quick reference to cache Oracle Database data in TimesTen
• Required privileges for the cache administration user and the cache manager user

B SQL*Plus Scripts for TimesTen Application-Tier Database Cache

• Installed SQL*Plus scripts

C Compatibility Between TimesTen and Oracle Databases

• Summary of compatibility issues
• Transaction semantics
• API compatibility
• SQL compatibility
  • Schema objects
    • Caching Oracle Database partitioned tables
  • Nonschema objects
  • Differences between Oracle Database and TimesTen tables
  • Data type support
  • SQL operators
  • SELECT statements
  • SQL subqueries
  • SQL functions
  • SQL expressions
  • INSERT/DELETE/UPDATE/MERGE statements
  • TimesTen-only SQL and built-in procedures
  • PL/SQL constructs
• Mappings between Oracle Database and TimesTen data types

Glossary

Index