3 Setting Up a Caching Infrastructure

This chapter describes the tasks for setting up the TimesTen and Oracle systems before you can start caching Oracle data in a TimesTen database. It includes the following topics:

Configuring your system to cache Oracle data in TimesTen
Configuring the Oracle database to cache data in TimesTen
Configuring a TimesTen database to cache Oracle data
Configuring a cache grid
Testing the connectivity between the TimesTen and Oracle databases
Managing the cache agent

Configuring your system to cache Oracle data in TimesTen

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

Oracle 11g Release 2
Oracle 11g Release 1
Oracle 10g Release 2 (10.2.0.4.0 or above)

Configure the environment variables for your particular operating system, as described in "Oracle In-Memory Database Cache environment variables for UNIX" or "Oracle In-Memory Database Cache environment variables for Microsoft Windows".

Then install TimesTen as described in Oracle TimesTen In-Memory Database Installation Guide.

Note:

From a product perspective, "Oracle In-Memory Database Cache" is used interchangeably with "TimesTen" because the Oracle In-Memory Database Cache product option includes the Oracle TimesTen In-Memory Database.

TimesTen does not support Oracle Name Server for Windows clients.

Oracle In-Memory Database Cache environment variables for UNIX

The shared library search path environment variable such as LD_LIBRARY_PATH or SHLIB_PATH must include the TimesTen_install_dir/lib directory.

For more information, see "Shared library path environment variable" in Oracle TimesTen In-Memory Database Installation Guide.

The PATH environment variable must include the TimesTen_install_dir/bin directory.

In the following example, TimesTen is installed in the /timesten/myinstance directory:

LD_LIBRARY_PATH=/timesten/myinstance/lib
PATH=/timesten/myinstance/bin

Oracle In-Memory Database Cache environment variables for Microsoft Windows

The PATH system environment variable must include the following directories:

Oracle_install_dir\bin
TimesTen_install_dir\lib
TimesTen_install_dir\bin

In the following example, Oracle is installed in the C:\oracle\ora112 directory and TimesTen is installed in the C:\timesten\myinstance directory:

PATH=C:\oracle\ora112\bin;C:\timesten\myinstance\lib;C:\timesten\myinstance\bin

Configuring the Oracle database to cache data in TimesTen

This section describes the tasks that must be performed on the Oracle database by the sys user. The topics include:

Create the Oracle users
Grant privileges to the Oracle users
Automatically create Oracle objects used to manage caching of Oracle data
Manually create Oracle objects used to manage caching of Oracle data

Create the Oracle users

First you must create a user timesten that will own Oracle tables that store information about cache grids. The SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGlobalSchema.sql is used to create:

The timesten user
The Oracle tables owned by the timesten user to store information about cache grids
The TT_CACHE_ADMIN_ROLE role that defines privileges on these Oracle tables

Create or designate a default tablespace for the timesten user and pass this tablespace as an argument to the initCacheGlobalSchema.sql script. See "Oracle objects used to manage a caching environment" for a list of Oracle tables owned by the timesten user.

Example 3-1 Creating the timesten user and its tables

In the following SQL*Plus example, the default tablespace that is created for the timesten user is cachetblsp:

% cd TimesTen_install_dir/oraclescripts
% sqlplus sys as sysdba
Enter password: password
SQL> CREATE TABLESPACE cachetblsp DATAFILE 'datfttuser.dbf' SIZE 100M;
SQL> @initCacheGlobalSchema "cachetblsp"

Then you must create or designate one or more users that will own Oracle tables that will be cached in a TimesTen database. We refer to these users as the schema users. These may be existing users or new users. The tables to be cached may or may not already exist.

Example 3-2 Creating a schema user

As the sys user, create a schema user oratt.

Use SQL*Plus to create the schema user:

SQL> CREATE USER oratt IDENTIFIED BY oracle;

Next you must create a user that will create, own and maintain Oracle objects that store information used to manage a specific cache grid and enforce predefined behaviors of particular cache group types. We refer to this user as the cache administration user.

Designate the tablespace that was created for the timesten user as the default tablespace for the cache administration user. This user will create tables in this tablespace that are used to store information about the cache grid and its cache groups. Other Oracle objects such change log tables, replication metadata tables, and triggers that are used to enforce the predefined behaviors of automatic refresh cache groups and AWT cache groups are created in the same tablespace.

See "Oracle objects used to manage a caching environment" for a list of Oracle tables and triggers owned by the cache administration user.

Note:

An automatic refresh cache group refers to a read-only cache group or a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL cache group attribute.

Example 3-3 Creating the cache administration user

As the sys user, create a cache administration user cacheuser. In the following example, the default tablespace for the cacheuser user is cachetblsp.

Use SQL*Plus to create the cache administration user:

SQL> CREATE USER cacheuser IDENTIFIED BY oracle
  2  DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;

Grant privileges to the Oracle users

The privileges that the Oracle users require depends on the types of cache groups you create and the operations that you perform on the cache groups. The privileges required for the Oracle cache administration user and the TimesTen cache manager user for each cache operation are listed in Table 3-1.

Table 3-1 Oracle and TimesTen user privileges required for cache operations

Cache operation	Privileges required for Oracle cache administration user^Foot 1	Privileges required for TimesTen cache manager user^Foot 2
Set the cache administration user name and password Call the `ttCacheUidPwdSet` built-in procedure Run the `ttAdmin -cacheUidPwdSet` utility command	`CREATE SESSION` `RESOURCE`^Foot 3	`CACHE_MANAGER`
Get the cache administration user name Call the `ttCacheUidGet` built-in procedure Run the `ttAdmin -cacheUidGet` utility command	None	`CACHE_MANAGER`
Create a cache grid Call the `ttGridCreate` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role `RESOURCE`^Footref 3	`CACHE_MANAGER`
Associate a TimesTen database with a cache grid Call the `ttGridNameSet` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`
Attach a TimesTen database to a cache grid Call the `ttGridAttach` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`
Detach a TimesTen database from a cache grid Call the `ttGridDetach` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`
Detach a list of nodes from a cache grid Call the `ttGridDetachList` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`
Destroy a cache grid Call the `ttGridDestroy` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`
Start the cache agent Call the `ttCacheStart` built-in procedure Run the `ttAdmin -cacheStart` utility command	`CREATE SESSION`	`CACHE_MANAGER`
Stop the cache agent Call the `ttCacheStop` built-in procedure Run the `ttAdmin -cacheStop` utility command	None	`CACHE_MANAGER`
Set a cache agent start policy Call the `ttCachePolicySet` built-in procedure Run the `ttAdmin -cachePolicy` utility command	`CREATE SESSION`^Foot 4	`CACHE_MANAGER`
Return the cache agent start policy setting Call the `ttCachePolicyGet` built-in procedure	`CREATE SESSION`	None
Start the replication agent Call the `ttRepStart` built-in procedure Run the `ttAdmin -repStart` utility command	None	`CACHE_MANAGER`
Stop the replication agent Call the `ttRepStop` built-in procedure Run the `ttAdmin -repStop` utility command	None	`CACHE_MANAGER`
Set a replication agent start policy Call the `ttRepPolicySet` built-in procedure Run the `ttAdmin -repPolicy` utility command	None	`ADMIN`
`CREATE [DYNAMIC] READONLY CACHE GROUP` with `AUTOREFRESH MODE INCREMENTAL`	`CREATE SESSION` `SELECT ON` `table_name`^Foot 5 `RESOURCE`^Footref 3 `CREATE ANY TRIGGER`^Footref 3	`CREATE [ANY] CACHE GROUP`^Foot 6 `CREATE [ANY] TABLE`^Foot 7
`CREATE [DYNAMIC] READONLY CACHE GROUP` with `AUTOREFRESH MODE FULL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] ASYNCHRONOUS WRITETHROUGH [GLOBAL] CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5 `RESOURCE`^Footref 3	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] SYNCHRONOUS WRITETHROUGH CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` (see variants in following rows)	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `AUTOREFRESH MODE INCREMENTAL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5 `RESOURCE`^Footref 3 `CREATE ANY TRIGGER`^Footref 3	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `AUTOREFRESH MODE FULL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `READONLY`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `PROPAGATE`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`CREATE [ANY] CACHE GROUP`^Footref 6 `CREATE [ANY] TABLE`^Footref 7
`ALTER CACHE GROUP SET AUTOREFRESH STATE PAUSED`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5^,^Foot 8 `RESOURCE`^Footref 3^,^Footref 8 `CREATE ANY TRIGGER`^Footref 3^,^Footref 8	`ALTER ANY CACHE GROUP`^Foot 9
`ALTER CACHE GROUP SET AUTOREFRESH STATE ON`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5^,^Footref 8 `RESOURCE`^Footref 3^,^Footref 8 `CREATE ANY TRIGGER`^Footref 3^,^Footref 8	`ALTER ANY CACHE GROUP`^Footref 9
`ALTER CACHE GROUP SET AUTOREFRESH STATE OFF`	`CREATE SESSION`	`ALTER ANY CACHE GROUP`^Footref 9
`ALTER CACHE GROUP SET AUTOREFRESH MODE FULL`	`CREATE SESSION`	`ALTER ANY CACHE GROUP`^Footref 9
`ALTER CACHE GROUP SET AUTOREFRESH MODE INCREMENTAL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5 `RESOURCE`^Footref 3 `CREATE ANY TRIGGER`^Footref 3	`ALTER ANY CACHE GROUP`^Footref 9
`ALTER CACHE GROUP SET AUTOREFRESH INTERVAL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5^,^Foot 10	`ALTER ANY CACHE GROUP`^Footref 9
`LOAD CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`LOAD {ANY CACHE GROUP \| ON` `cache_group_name)`^Footref 9
`REFRESH CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`REFRESH {ANY CACHE GROUP \| ON` `cache_group_name`)^Footref 9
`FLUSH CACHE GROUP`	`CREATE SESSION` `UPDATE ON` `table_name`^Footref 5 `INSERT ON` `table_name`^Footref 5	`FLUSH {ANY CACHE GROUP \| ON` `cache_group_name)`^Footref 9
`UNLOAD CACHE GROUP`	None	`UNLOAD {ANY CACHE GROUP \| ON` `cache_group_name`)^Footref 9
`DROP CACHE GROUP`	`CREATE SESSION`	`DROP ANY CACHE GROUP`^Footref 9 `DROP ANY TABLE`^Foot 11
Synchronous writethrough or propagate	`CREATE SESSION` `INSERT ON` `table_name`^Footref 5^,^Foot 12 `UPDATE ON` `table_name`^Footref 5^{,^Footref 12} `DELETE ON` `table_name`^Footref 5^{,^Footref 12}	`INSERT ON` `table_name`^Foot 13 `UPDATE ON` `table_name`^Footref 13 `DELETE ON` `table_name`^Footref 13
Asynchronous writethrough	`CREATE SESSION` `INSERT ON` `table_name`^Footref 5 `UPDATE ON` `table_name`^Footref 5 `DELETE ON` `table_name`^Footref 5	`INSERT ON` `table_name`^Footref 13 `UPDATE ON` `table_name`^Footref 13 `DELETE ON` `table_name`^Footref 13
Asynchronous writethrough when the `CacheAWTMethod` connection attribute is set to 1	`CREATE PROCEDURE` Note: This privilege is an addition to the privileges needed for any asynchronous writethrough cache group.	No additional privileges
Asynchronous writethrough cache for Oracle `CLOB`, `BLOB` and `NCLOB` fields when the `CacheAWTMethod` connection attribute is set to 1	`EXECUTE` privilege on the Oracle `DBMS_LOB` PL/SQL package Note: This privilege is an addition to the privileges needed for any asynchronous writethrough cache group.	No additional privileges
Incremental automatic refresh	`SELECT ON` `table_name`^Footref 5	None
Full automatic refresh	`SELECT ON` `table_name`^Footref 5	None
Dynamic load	`CREATE SESSION` `SELECT ON` `table_name`^Footref 5	`SELECT ON` `table_name^Footref 13` `UPDATE ON` `table_name^Footref 13` `DELETE ON` `table_name`^Footref 13 `INSERT ON` `table_name^Footref 13`
Aging	None	`DELETE {ANY TABLE \| ON` `table_name`)^Footref 13
Set the LRU aging attributes Call the `ttAgingLRUConfig` built-in procedure	None	`ADMIN`
Generate Oracle SQL statements to manually install or uninstall Oracle objects Run the `ttIsql` utility's `cachesqlget` command Call the `ttCacheSQLGet` built-in procedure	`CREATE SESSION`	`CACHE_MANAGER`
Disable or enable propagation of committed cache table updates to Oracle Call the `ttCachePropagateFlagSet` built-in procedure	None	`CACHE_MANAGER`
Configure cache agent timeout and recovery method for automatic refresh cache groups Call the `ttCacheConfig` built-in procedure	`CREATE SESSION`	`CACHE_MANAGER`
Set the AWT transaction log file threshold Call the `ttCacheAWTThresholdSet` built-in procedure	None	`CACHE_MANAGER`
Enable or disable monitoring of AWT cache groups Call the `ttCacheAWTMonitorConfig` built-in procedure	None	`CACHE_MANAGER`
Enable or disable tracking of DDL statements issued on cached Oracle tables Call the `ttCacheDDLTrackingConfig` built-in procedure	`CREATE SESSION`	`CACHE_MANAGER`
Return information about cache grids Call the `ttGridInfo` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`
Return information about cache grid nodes Call the `ttGridNodeStatus` built-in procedure	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` role	`CACHE_MANAGER`

^Footnote 1At minimum, the cache administration user must have the CREATE ANY TYPE privilege.

^Footnote 2At minimum, the cache manager user must have the CREATE SESSION privilege.

^Footnote 3Not required if the Oracle objects used to manage the caching of Oracle data are manually created.

^Footnote 4Required if the cache agent start policy is being set to always or norestart.

^Footnote 5Required on all Oracle tables cached in the TimesTen cache group except for tables owned by the cache administration user.

^Footnote 6The CACHE_MANAGER privilege includes the CREATE [ANY] CACHE GROUP privilege. ANY is required if the cache manager user creates cache groups owned by a user other than itself.

^Footnote 7ANY is required if any of the cache tables are owned by a user other than the cache manager user.

^Footnote 8Required if the cache group's automatic refresh mode is incremental and initial automatic refresh state is OFF, and the Oracle objects used to manage the caching of Oracle data are automatically created.

^Footnote 9Required if the TimesTen user accessing the cache group does not own the cache group.

^Footnote 10Required if the cache group's automatic refresh mode is incremental.

^Footnote 11Required if the TimesTen user accessing the cache group does not own all its cache tables.

^Footnote 12The privilege must be granted to the Oracle user with the same name as the TimesTen cache manager user if the Oracle user is not the cache administration user.

^Footnote 13Required if the TimesTen user accessing the cache table does not own the table.

Automatically create Oracle objects used to manage caching of Oracle data

TimesTen can automatically create Oracle objects owned by the cache administration user, such as cache and replication metadata tables, change log tables, and triggers when particular cache grid and cache group operations are performed. Some of these objects are used to store information about TimesTen databases that are associated with a particular cache grid. Other objects are used to enforce the predefined behaviors of automatic refresh cache groups and AWT cache groups.

These Oracle objects are automatically created if the cache administration user has been granted the required privileges by running the SQL*Plus script TimesTen_install_dir/oraclescripts/grantCacheAdminPrivileges.sql as the sys user. The set of required privileges include CREATE SESSION, RESOURCE, CREATE ANY TRIGGER, and the TT_CACHE_ADMIN_ROLE role. The cache administration user name is passed as an argument to the grantCacheAdminPrivileges.sql script.

In addition to the privileges granted to the cache administration user by running the grantCacheAdminPrivileges.sql script, this user may also need to be granted privileges such as SELECT or INSERT on the cached Oracle tables depending on the types of cache groups you create, and the operations that yo perform on the cache groups and their cache tables. See Table 3-1 for a complete list of privileges that need to be granted to the cache administration user in order to perform particular cache grid, cache group, and cache table operations.

Example 3-4 Granting privileges to automatically create Oracle objects

As the sys user, run the grantCacheAdminPrivileges.sql script to grant privileges to the cache administration user to automatically create Oracle objects used to manage caching of Oracle data in a TimesTen database. In the following example, the cache administration user name is cacheuser.

Use SQL*Plus to run the grantCacheAdminPrivileges.sql script:

SQL> @grantCacheAdminPrivileges "cacheuser"
SQL> exit

For example, with automatic refresh cache groups, the Oracle objects used to enforce the predefined behaviors of these cache group types are automatically created if the objects do not already exist and one of the following occurs:

The cache group is created with its automatic refresh state set to PAUSED or ON
The cache group is created with its automatic refresh state set to OFF and then altered to either PAUSED or ON

Manually create Oracle objects used to manage caching of Oracle data

The cache administration user requires the RESOURCE privilege to automatically create the Oracle objects used to:

Store information about TimesTen databases that are associated with a particular cache grid
Enforce the predefined behaviors of automatic refresh cache groups. In this case, the cache administration user also requires the CREATE ANY TRIGGER privilege to automatically create these Oracle objects.
Enforce the predefined behaviors of AWT cache groups

For security purposes, if you do not want to grant the RESOURCE and CREATE ANY TRIGGER privileges to the cache administration user required to automatically create the Oracle objects, you can manually create these objects.

To manually create the Oracle tables and triggers used to enforce the predefined behaviors of particular cache group types, run the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheAdminSchema.sql as the sys user. These objects must be created before you can create automatic refresh cache groups and AWT cache groups. The cache administration user name is passed as an argument to the initCacheAdminSchema.sql script.

The initCacheAdminSchema.sql script also grants a minimal set of required privileges including CREATE SESSION and the TT_CACHE_ADMIN_ROLE role to the cache administration user. In addition to the privileges granted to the cache administration user by running the initCacheAdminSchema.sql script, this user may also need to be granted privileges such as SELECT or INSERT on the cached Oracle tables depending on the types of cache groups you create and the operations that you perform on the cache groups and their cache tables. See Table 3-1 for a complete list of privileges that need to be granted to the cache administration user in order to perform particular cache grid, cache group, and cache table operations.

To manually create the Oracle tables used to store information about TimesTen databases that are associated with a particular cache grid, run the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGridSchema.sql as the sys user. These tables must be created before you can create a cache grid. The cache administration user name and the name of the cache grid that you create are passed as arguments to the initCacheGridSchema.sql script.

Example 3-5 Manually creating Oracle objects used to manage caching of Oracle data

As the sys user, run the initCacheAdminSchema.sql script to manually create Oracle objects used to enforce the predefined behaviors of automatic refresh cache groups and AWT cache groups, and grant a limited set of privileges to the cache administration user. Then run the initCacheGridSchema.sql script to manually create Oracle objects used to store information about TimesTen databases associated with a particular cache grid. In the following example, the cache administration user name is cacheuser and the cache grid name is ttGrid.

Use SQL*Plus to run the initCacheAdminSchema.sql and initCacheGridSchema.sql scripts:

SQL> @initCacheAdminSchema "cacheuser"
SQL> @initCacheGridSchema "cacheuser" "ttGrid"
SQL> exit

Other Oracle objects associated with Oracle tables that are cached in an automatic refresh cache group are needed to enforce the predefined behaviors of these cache group types. See "Manually creating Oracle objects for automatic refresh cache groups" for details about how to create these additional objects after you create the cache group.

To view a list of the Oracle objects created and used by TimesTen to manage the caching of Oracle data, execute the following query in SQL*Plus as the sys user:

SQL> SELECT owner, object_name, object_type FROM all_objects WHERE object_name 
  2  LIKE 'TT\___%' ESCAPE '\';

The query returns a list of tables, indexes and triggers owned by either the timesten user or the cache administration user.

Configuring a TimesTen database to cache Oracle data

This section describes the operations that must be performed on the TimesTen database by the instance administrator or the cache manager user. The topics include:

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

Define a DSN for the TimesTen database

A TimesTen database that caches data from an Oracle database can be referenced by either a system DSN or a user DSN. See "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide for more information about creating TimesTen DSNs.

When creating a DSN for a TimesTen database that caches data from an Oracle database, pay special attention to the settings of the following connection attributes. All of these connection attributes can be set in a Data Manager DSN or a connection string, unless otherwise stated.

PermSize specifies the allocated size of the database's permanent partition in MB. Set this value to at least 32 MB.
OracleNetServiceName must be set to the net service name of the Oracle database instance.

On Microsoft Windows systems, the net service name of the Oracle database instance is specified in the Oracle Net Service Name field of the IMDB Cache tab within the TimesTen ODBC Setup dialog box.
DatabaseCharacterSet must be set to the Oracle database character set.

You can determine the Oracle database character set by executing the following query in SQL*Plus as any user:
```
SQL> SELECT value FROM nls_database_parameters
   2 WHERE parameter='NLS_CHARACTERSET';
```
UID specifies the name of a cache user, such as the cache manager user, that has the same name as an Oracle user who can access the cached Oracle tables. The UID connection attribute can be specified in a Data Manager DSN, a Client DSN, or a connection string.
PWD specifies the password of the TimesTen user specified in the UID connection attribute. The PWD connection attribute can be specified in a Data Manager DSN, a Client DSN, or a connection string.
OraclePWD specifies the password of the Oracle user that has the same name as the TimesTen user specified in the UID connection attribute and can access the cached Oracle tables.
PassThrough can be set to control whether statements are to be executed in the TimesTen database or passed through to be executed in the Oracle database, as described in "Setting a passthrough level".
LockLevel must be set to its default of 0 (row-level locking) because Oracle In-Memory Database Cache does not support database-level locking.
TypeMode must be set to its default of 0 (Oracle type mode).

Example 3-6 DSN for a TimesTen database that caches data from an Oracle database

The following example is the definition of the cachealone1 DSN that references the first standalone TimesTen database that will become a member of the ttGrid cache grid:

[cachealone1]
DataStore=/users/OracleCache/alone1
PermSize=64
OracleNetServiceName=orcl
DatabaseCharacterSet=WE8ISO8859P1

Create the TimesTen users

First you must create a user who performs cache grid and cache group operations. We refer to this user as the cache manager user. This user must have the same name as an Oracle user that can select from and update the cached Oracle tables. The Oracle user can be the cache administration user, a schema user, or some other existing user. The password of the cache manager user can be different than the password of the Oracle user with the same name.

The cache manager user is responsible for creating and configuring the cache grid and creating the cache groups. This user can also monitor the grid itself and various operations that are performed on the cache groups.

Then you must create a user with the same name as an Oracle schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database. We refer to these users as cache table users because the TimesTen cache tables will be owned by these users. Therefore, the owner and name of a TimesTen cache table is the same as the owner and name of the corresponding cached Oracle table. The password of a cache table user can be different than the password of the Oracle schema user with the same name.

Operations on a cache group or a cache table such as loading a cache group or updating a cache table can be performed by any TimesTen user that has sufficient privileges. In the examples throughout this guide, the cache manager user performs these types of operations although these operations can be performed by another user, such as a cache table user, that has the required privileges. If these operations are to be performed by a TimesTen user other than the cache manager user, the other user must have the same name as an Oracle user that can select from and update the cached Oracle tables. Connect to the TimesTen database specifying that user's name in the UID connection attribute, and supply the corresponding TimesTen and Oracle passwords in the PWD and OraclePWD connection attributes, respectively, to perform operations on a cache group or cache table.

Example 3-7 Creating the TimesTen users

In the following ttIsql utility example, create the TimesTen database by connecting to the cachealone1 DSN as the instance administrator. Then create the cache manager user cacheuser whose name, in this example, is the same as the Oracle cache administration user. Then create a cache table user oratt whose name is the same as the Oracle schema user who will own the Oracle tables to be cached in the TimesTen database.

% ttIsql cachealone1
Command> CREATE USER cacheuser IDENTIFIED BY timesten;
Command> CREATE USER oratt IDENTIFIED BY timesten;

Grant privileges to the TimesTen users

The privileges that the TimesTen users require depend on the types of cache groups you create and the operations that you perform on the cache groups. The privileges required for the TimesTen cache manager user and the Oracle cache administration user for each cache operation are listed in Table 3-1.

Example 3-8 Granting privileges to the cache manager user

The cache manager user cacheuser requires privileges to perform the following operations:

Set the cache administration user and password (CACHE_MANAGER)
Create and associate the TimesTen database with a cache grid (CACHE_MANAGER)
Start the cache agent and replication agent processes on the TimesTen database (CACHE_MANAGER)
Attach the TimesTen database to the cache grid (CACHE_MANAGER)
Create cache groups to be owned by the cache administration user (CREATE CACHE GROUP, inherited by the CACHE_MANAGER privilege; CREATE ANY TABLE to create the underlying cache tables which will be owned by the oratt cache table user)

As the instance administrator, use the ttIsql utility to grant the cache manager user cacheuser the required privileges:

Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;
Command> exit

Set the cache administration user name and password

You must set the cache administration user name and password in the TimesTen database before any cache grid or cache group operation can be issued. The cache agent connects to the Oracle database as this user to create and maintain Oracle objects that store information used to manage a cache grid and enforce predefined behaviors of particular cache group types.

The cache administration user name and password need to be set only once in each TimesTen database that will cache Oracle data unless it needs to be changed. For example, if the TimesTen database is destroyed and re-created, or the cache administration user name is dropped and re-created in the Oracle database, the cache administration user name and password must be set again.

The cache administration user name and password cannot be changed if the cache agent is running on the TimesTen database or there are cache groups in the database. The cache groups must be dropped before you can change the cache administration user name and password. You must also stop the cache agent before you change the cache administration user name and password, and then restart the cache agent after the user name and password have been changed.

Example 3-9 Setting the cache administration user name and password

The cache administration user name and password can be set programmatically by calling the ttCacheUidPwdSet built-in procedure as the cache manager user:

% ttIsql "DSN=cachealone1;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheuser','oracle');

It can also be set from a command line by running a ttAdmin -cacheUidPwdSet utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheUidPwdSet -cacheUid cacheuser -cachePwd oracle cachealone1

If you do not specify the -cachePwd option, the ttAdmin utility prompts for the cache administration user's password.

For more information about the utility, see "ttAdmin" in Oracle TimesTen In-Memory Database Reference.

Configuring a cache grid

An Oracle table cannot be cached in separate cache groups within the same TimesTen database. However, the table can be cached in separate cache groups within different TimesTen databases.

A TimesTen cache grid provides users with Oracle databases a means to horizontally scale out cache groups across multiple systems with read and write data consistency across the TimesTen databases and predictable latency for database transactions. A cache grid contains one or more grid members that collectively manage application data using the relational data model. A grid member is either a standalone TimesTen database or an active standby pair that consists of at least two replicated TimesTen databases.

Each database of a grid member is called a grid node. A node is a single TimesTen database that is either a standalone database, or the active master database or standby master database of an active standby pair. Therefore, a grid member is composed of one or two nodes.

See "Administering an Active Standby Pair with Cache Groups" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for more information about replicating cache tables.

Grid members can reside on the same system or on different systems. If the grid members reside across different systems, the systems must be connected to each other in a TCP/IP private network. Each system must have the same machine architecture, operating system version, platform and bit version. The TimesTen major release number of all grid members must be the same.

A TimesTen database that is or is part of a grid member can contain local and global cache groups as well as explicitly loaded and dynamic cache groups.

See "Dynamic cache groups" for more information about dynamic cache groups.

See "Global cache groups" for more information about global cache groups.

A cache grid can be associated with only one Oracle database. A TimesTen database can be a member of only one cache grid. An Oracle database can be associated with more than one cache grid and each grid can be administered by a different cache administration user. A cache grid has no association with other cache grids.

This section describes the operations that must be performed on the TimesTen database by the cache manager user. The topics include:

Modify the PROCESSES Oracle system parameter for ten or more grid nodes
Create a cache grid
Associate a TimesTen database with a cache grid

Modify the PROCESSES Oracle system parameter for ten or more grid nodes

If you are planning a grid with ten or more nodes, modify the PROCESSES Oracle system parameter. Use this guideline:

PROCESSES >= 10*GridMembers + DLConnections + OraBackgroundProcesses

where

GridMembers = number of grid members
DLConnections=number of dynamic load connections
OraBackgroundProcesses=number of Oracle background processes

The number of dynamic load connections is determined by how many sessions will have dynamic cache group operations.

For more information about modifying an Oracle system parameter, see "Changing Parameter Values in a Parameter File" in Oracle Database Reference. For more information about Oracle background processes, see "Background Processes" in Oracle Database Reference.

Create a cache grid

In the examples used throughout the rest of this guide, you will create a cache grid ttGrid that contains three grid members: two standalone TimesTen databases and an active standby pair consisting of three TimesTen databases. This chapter shows how to associate one of the standalone databases with the cache grid. Subsequent chapters show how to create the other standalone database and the active standby pair, and how to associate those members with the grid.

See Example 3-6 for the DSN definition of the first standalone TimesTen database.

You can create a cache grid from any of the standalone databases, or from either the active or standby master database of the active standby pair. A cache grid is created only once from any one of the grid members.

Example 3-10 Creating a cache grid

Create the ttGrid cache grid from the first standalone database by calling the ttGridCreate built-in procedure as the cache manager user:

Command> call ttGridCreate('ttGrid');

All the databases in these examples, except for the read-only subscriber database of the active standby pair, will be associated with the ttGrid cache grid.

If you manually created the Oracle objects used to store information about TimesTen databases that are associated with a particular cache grid as described in "Manually create Oracle objects used to manage caching of Oracle data", you do not need to call ttGridCreate because the grid, in effect, was created by running the initCacheGridSchema.sql script.

By default, you must associate a TimesTen database with a cache grid before you can create cache groups in that database. For backward compatibility, you can set the CacheGridEnable connection attribute to 0 so that you do not have to create a cache grid and associate the TimesTen database with the grid before cache groups can be created within that database. However, regardless of the setting of CacheGridEnable, you must create a cache grid and associate the TimesTen database with the grid before you can create global cache groups within that database. See "Global cache groups" for more information about global cache groups.

CacheGridEnable is set to 1 by default.

Associate a TimesTen database with a cache grid

All standalone databases, and the active and standby master databases of the active standby pair must be associated with a cache grid before you can create cache groups within those databases.

Example 3-11 Associating a TimesTen database with a cache grid

Associate the first standalone database to the ttGrid cache grid by calling the ttGridNameSet built-in procedure as the cache manager user:

Command> call ttGridNameSet('ttGrid');

Testing the connectivity between the TimesTen and Oracle databases

To test the connectivity between the TimesTen and Oracle databases, set the passthrough level to 3 and execute the following query, to be processed on the Oracle database, as the cache manager user:

Command> passthrough 3;
Command> SELECT * FROM V$VERSION;
Command> passthrough 0;

If connectivity has been successfully established, the query returns the version of the Oracle database. If it does not, check the following for correctness:

The Oracle net service name set in the OracleNetServiceName connection attribute and the state of the Oracle server
The settings of the shared library search path environment variable such as LD_LIBRARY_PATH or SHLIB_PATH
The setting of the cache administration user name in the TimesTen database

Example 3-12 Determining the cache administration user name setting

The cache administration user name setting can be returned programmatically by calling the ttCacheUidGet built-in procedure as the cache manager user:

Command> call ttCacheUidGet;

It can also be returned from a command line by running a ttAdmin -cacheUidGet utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheUidGet cachealone1

Managing the cache agent

The cache agent is a TimesTen process that performs cache operations such as loading a cache group and automatic refresh, as well as manages Oracle objects used to enforce the predefined behaviors of particular cache group types.

Example 3-13 Starting the cache agent

The cache agent can be manually started programmatically by calling the ttCacheStart built-in procedure as the cache manager user:

Command> call ttCacheStart;

It can also be started from a command line by running a ttAdmin -cacheStart utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheStart cachealone1

Example 3-14 Stopping the cache agent

The cache agent can be manually stopped programmatically by calling the ttCacheStop built-in procedure as the cache manager user:

Command> call ttCacheStop;

It can also be stopped from a command line by running a ttAdmin -cacheStop utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheStop cachealone1

The ttCacheStop built-in procedure has an optional parameter and the ttAdmin -cacheStop utility command has an option -stopTimeout that specifies how long the TimesTen main daemon process waits for the cache agent to stop. If the cache agent does not stop within the specified timeout period, the TimesTen daemon stops the cache agent. The default cache agent stop timeout is 100 seconds. A value of 0 specifies to wait indefinitely.

Do not stop the cache agent immediately after you have dropped or altered an automatic refresh cache group. Instead, wait for at least two minutes to allow the cache agent to clean up Oracle objects such as change log tables and triggers that were created and used to manage the cache group.

Note:

The TimesTen X/Open XA and Java Transaction API (JTA) implementations do not work with Oracle In-Memory Database Cache. The start of any XA or JTA transaction fails if the cache agent is running.

Set a cache agent start policy

A cache agent start policy determines how and when the cache agent process starts on a TimesTen database. The cache agent start policy can be set to:

manual
always
norestart

The default start policy is manual, which means the cache agent must be started manually by calling the ttCacheStart built-in procedure or running a ttAdmin -cacheStart utility command. To manually stop a running cache agent process, call the ttCacheStop built-in procedure or run a ttAdmin -cacheStop utility command.

When the start policy is set to always, the cache agent starts automatically when the TimesTen main daemon process starts. With the always start policy, the cache agent cannot be stopped when the main daemon is running unless the start policy is first changed to either manual or norestart. Then issue a manual stop by calling the ttCacheStop built-in procedure or running a ttAdmin -cacheStop utility command.

With the manual and always start policies, the cache agent automatically restarts when the database recovers after a failure such as a database invalidation. If the database was attached to a cache grid when the failure occurred, it is automatically reattached to the grid when the database recovers.

Setting the cache agent start policy to norestart means the cache agent must be started manually by calling the ttCacheStart built-in procedure or running a ttAdmin -cacheStart utility command, and stopped manually by calling the ttCacheStop built-in procedure or running a ttAdmin -cacheStop utility command.

With the norestart start policy, the cache agent does not automatically restart when the database recovers after a failure such as a database invalidation. You must restart the cache agent manually by calling the ttCacheStart built-in procedure or running a ttAdmin -cacheStart utility command. If the database was attached to a cache grid when the failure occurred, it is not automatically reattached to the grid when the database recovers. You must call the ttGridAttach built-in procedure to reattach the database to the grid.

Example 3-15 Setting a cache agent start policy

The cache agent start policy can be set programmatically by calling the ttCachePolicySet built-in procedure as the cache manager user:

Command> call ttCachePolicySet('always');

It can also be set from a command line by running a ttAdmin -cachePolicy utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cachePolicy norestart cachealone1