Configuring In-Memory Database Cache

The Oracle Database option In-Memory Database Cache (IMDB Cache) allows you to cache tables from an Oracle database into a TimesTen in-memory database. An application will typically choose to cache frequently-accessed data or performance-sensitive data. IMDB Cache uses the concept of a cache group, which is a collection of cached tables related to each other by foreign key constraints.  This is an important feature as the unit of caching and aging becomes a set of records related by foreign key constraints. Each cached table maps to either a full table or a subset of a table in the Oracle database.

A cache grid is a collection of TimesTen databases that collectively manage an application's cached data. Each of the in-memory cache databases participating in a cache grid (also known as grid members) contains one or more cache groups. Global cache groups are cache groups that are shared among cache grid members. Cache grid members use peer-to-peer communication to communicate among each other and to manage the coherence of global cache groups. New cache grid members may attach or detach from a cache grid dynamically.

The examples on this page use the HR sample schema in the Oracle database. The examples provide a step by step tutorial on how to set up In-Memory Database Cache in order to cache tables from the Oracle HR schema into a TimesTen database.

Required configuration on the Oracle database

Before you can cache tables from an Oracle database into In-Memory Database Cache, a cache administration user and TimesTen system tables must be created in the Oracle database. Here is an overview of the configuration steps required on the Oracle database:

1. Create a new tablespace Use the CREATE TABLESPACE statement to create a separate tablespace for managing the In-Memory Database Cache objects.
2. Create the TIMESTEN user Run the script initCacheGlobalSchema.sql to create the timesten user. This user owns the Oracle tables that store information about cache grids and its grid members.
3. Create a cache administration user Use the CREATE USER statement to create a cache administration user. This user is responsible for tracking the changes between the Oracle database and a cache database.
4. Grant system privileges to the cache administration user Run the script grantCacheAdminPrivileges.sql to grant system privileges to the cache administration user. This allows the objects to be created by the cache administration user, for managing the caching of Oracle tables.
5. Grant data access privileges to the cache administration user The cache administration user must have access to the tables being cached. Use the GRANT statement to grant the necessary object privileges to the cache administration user.

Follow the example below to configure an Oracle database for use with In-Memory Database Cache using the configuration steps
described above:

Required Configuration on the Oracle Database example


Setting up the cache database

Here are the steps for configuring a cache database where TimesTen is installed and run:

1. Create a cache database In order to cache Oracle tables, the DatabaseCharacterSet of the cache database must be identical to the database character set defined in the Oracle database. The DSN attribute OracleNetServiceName must contain a TNS service name pointing to the Oracle database.
2. Create a cache manager user Use the CREATE USER statement to create a cache manager user. This user must be granted either ADMIN or CACHE_MANAGER privilege. A cache manager user is responsible for setting up and managing cache grid, and cache group operations.
3. Create a cache table user The user who owns the Oracle tables to be cached must also exist in the cache database. Use the CREATE USER statement to create a cache table user.
4. Associate the Oracle cache administration user with the database Use the procedure ttCacheUidPwdSet to set the Oracle cache administration user name and password in the cache database.
5. Create a cache grid
  (One time only)
Use the procedure ttGridCreate to create a cache grid. Only the first database member of a cache grid needs to perform this step.
6. Associate the cache database with the cache grid Use the procedure ttGridNameSet to associate the current database with a cache grid.

Follow the example below to setup a TimesTen database for caching using the configuration steps described above:

Setting Up a Cache Database example


Adding cache groups to a cache database

Here are the steps for adding cache groups to a cache database where TimesTen is installed and run:

1. Start the cache agent Use the built-in procedure ttCacheStart to start the cache agent process, if not already started.
2. Define the cache groups Use the CREATE CACHE GROUP statement to define the cache groups and the cache tables.
3. Start the replication agent Use the built-in procedure ttRepStart to start the replication agent process. This step is needed if there are asynchronous writethrough cache groups in the database.
4. Attach the cache database to the cache grid Call the procedure ttGridAttach to add the current cache database to the cache grid. This step is required only if the database contains global cache groups, or if global cache grid operations are needed.
5. Preload data into cache groups Use the LOAD CACHE GROUP statement to preload the cache groups with data from the Oracle database.

Follow these examples to learn how to create and populate cache groups into a cache database using the above steps:

  1. Adding Cache Groups to a Cache Database
  2. Loading Data into Cache Groups
1 2 Next >