Before you can start caching Oracle Database data in a TimesTen database, perform these tasks for setting up the TimesTen Classic and Oracle Database systems:
Configuring your system to cache Oracle Database data in TimesTen Classic
Configuring the Oracle database to cache data in TimesTen Classic
Configuring a TimesTen database to cache Oracle Database data
Testing the connectivity between the TimesTen and Oracle databases
Note:
See the Platforms section in the Oracle TimesTen In-Memory Database Release Notes (README.htm) in your installation directory to find out which Oracle Database Server releases are supported by the TimesTen In-Memory Database.Configure the environment variables for your particular operating system, as described in "TimesTen Cache environment variables for UNIX or Linux" or "TimesTen Cache environment variables for Microsoft Windows".
Then, install TimesTen. Instructions for installing TimesTen are described in the Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.
Note:
From a product perspective, "TimesTen Cache" is used interchangeably with "TimesTen Classic" because the TimesTen Cache product option is included with TimesTen Classic.TimesTen Classic does not support Oracle Name Server for Windows clients.
The shared library search path environment variable such as LD_LIBRARY_PATH or SHLIB_PATH must include the timesten_home/install/lib directories. For more information, see "Shared library path environment variable" in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide.
The PATH environment variable must include the timesten_home/bin directory.
In the following example, the timesten_home directory is the /timesten/myinstance directory and TimesTen Classic is installed in the /timesten/myinstance/install directory:
LD_LIBRARY_PATH=/timesten/myinstance/install/lib PATH=/timesten/myinstance/bin
The PATH system environment variable must include the following directories:
Oracle_install_dir\bin
timesten_home\install\lib
timesten_home\bin
In the following example, Oracle Database is installed in the C:\oracle\ora112 directory and TimesTen Classic is installed in the C:\timesten\myinstance directory:
PATH=C:\oracle\ora112\bin;C:\timesten\myinstance\install\lib;C:\timesten\myinstance\bin
The following sections describe the tasks that must be performed on the Oracle database by the sys user:
Automatically create Oracle Database objects used to manage data caching
Manually create Oracle Database objects used to manage data caching
You must create a default tablespace and a user timesten that is to own the Oracle Database tables that store information about cache operations. This tablespace is used for storing TimesTen Cache management objects that should not be shared with other applications. While you may also store Oracle base tables that are cached in a TimesTen database, we strongly recommend that this tablespace be used solely by the TimesTen database for cache management.
See "Managing a caching environment with Oracle Database objects" for a list of Oracle Database 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_home/install/oraclescripts % sqlplus sys as sysdba Enter password: password SQL> CREATE TABLESPACE cachetblsp;
Create or designate one or more users to own Oracle Database tables that are to be cached in a TimesTen database. These users are 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, the following SQL*Plus example creates a schema user oratt.
SQL> CREATE USER oratt IDENTIFIED BY oracle;
Next, you must create a user that creates, owns, and maintains Oracle Database objects that store information used to manage the cache environment for a TimesTen database and enforce predefined behaviors of particular cache group types. We refer to this user as the cache administration user.
Note:
Each TimesTen database can be managed by only a single cache administration user on the Oracle database. However, a single cache administration user can manage multiple TimesTen databases. You can specify one or more cache administration users where each manages one or more TimesTen databases.For more details, see "Caching the same Oracle table on two or more TimesTen databases".
Designate the tablespace that was created for the timesten user as the default tablespace for the cache administration user. This user creates tables in this tablespace that are used to store information about the cache environment and its cache groups. Other Oracle Database objects (such change log tables, replication metadata tables, and triggers) are used to enforce the predefined behaviors of autorefresh cache groups and AWT cache groups are created in the same tablespace. To create and manage these objects, the cache administration user must have a high level of privileges.
Note:
If you create multiple cache administration users, each may use the same or different tablespace as their default tablespace.See "Managing a caching environment with Oracle Database objects" for a list of Oracle Database tables and triggers owned by the cache administration user.
Note:
An autorefresh cache group refers to a read-only cache group or a user managed cache group that uses theAUTOREFRESH 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 DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
The cache administration user must be granted a high level of privileges depending on the cache group types created and the operations performed on these cache groups. You can run the SQL*Plus script timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql as the sys user to grant the cache administration user the minimum set of privileges required to perform cache operations. For more information on this SQL script, see "Automatically create Oracle Database objects used to manage data caching".
The entire list of privileges required for this user for each cache operation are listed in "Required privileges for the cache administration user and the cache manager user".
TimesTen Classic can automatically create Oracle Database objects owned by the cache administration user, such as cache and replication metadata tables, change log tables, and triggers when particular cache environment and cache group operations are performed. Some of these objects are used to enforce the predefined behaviors of autorefresh cache groups and AWT cache groups.
These Oracle Database objects are automatically created if the cache administration user has been granted the required privileges by running the SQL*Plus script timesten_home/install/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.
Note:
Alternatively, you can manually create these objects as described in "Manually create Oracle Database objects used to manage data caching" before performing any cache group operations if, for security purposes, you do not want to grant theRESOURCE or CREATE ANY TRIGGER privileges to the cache administration user required to automatically create these tables and triggers.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 Database 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 "Required privileges for the cache administration user and the cache manager user" for a complete list of privileges that need to be granted to the cache administration user in order to perform particular cache group and cache table operations.
Example 3-4 Granting privileges to automatically create Oracle Database objects
As the sys user, run the grantCacheAdminPrivileges.sql script to grant privileges to the cache administration user to automatically create Oracle Database objects used to manage caching Oracle Database data in a TimesTen database. In the following example, the grantCacheAdminPrivileges.sql script requires the cache administration user name (cacheuser) as input.
Use SQL*Plus to run the grantCacheAdminPrivileges.sql script:
SQL> @grantCacheAdminPrivileges "cacheuser" SQL> exit
For example, with autorefresh cache groups, the Oracle Database 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 autorefresh state set to PAUSED or ON.
The cache group is created with its autorefresh state set to OFF and then altered to either PAUSED or ON.
The cache administration user requires the RESOURCE privilege to automatically create the Oracle Database objects used to:
Store information about TimesTen databases that are associated with a particular cache environment.
Enforce the predefined behaviors of autorefresh cache groups. In this case, the cache administration user also requires the CREATE ANY TRIGGER privilege to automatically create these Oracle Database objects.
Enforce the predefined behavior for 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 Database objects, you can manually create these objects.
To manually create the Oracle Database tables and triggers used to enforce the predefined behaviors of particular cache group types, run the SQL*Plus script timesten_home/install/oraclescripts/initCacheAdminSchema.sql as the sys user. These objects must be created before you can create autorefresh cache groups and AWT cache groups. The initCacheAdminSchema.sql script requires the cache administration user name as input.
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 Database 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 "Required privileges for the cache administration user and the cache manager user" for a complete list of privileges that need to be granted to the cache administration user in order to perform particular cache group and cache table operations.
Example 3-5 Manually creating Oracle Database objects used to manage caching data
As the sys user, run the initCacheAdminSchema.sql script to manually create Oracle Database objects used to enforce the predefined behaviors of autorefresh cache groups and AWT cache groups, and grant a limited set of privileges to the cache administration user. In the following example, the cache administration user name is cacheuser.
Use SQL*Plus to run the initCacheAdminSchema.sql script:
SQL> @initCacheAdminSchema "cacheuser" SQL> exit
Other Oracle Database objects associated with Oracle Database tables that are cached in an autorefresh cache group are needed to enforce the predefined behaviors of these cache group types. See "Manually creating Oracle Database objects for autorefresh cache groups" for details about how to create these additional objects after you create the cache group.
To view a list of the Oracle Database objects created and used by TimesTen Classic to manage the caching of Oracle Database 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.
The following sections describe the operations that must be performed on the TimesTen database by the instance administrator or the cache manager user:
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 Direct DSN or a connection string, unless otherwise stated.
PermSize specifies the allocated size of the database's permanent region in MB. Set this value to at least 32 MB.
OracleNetServiceName must be set to the net service name of the Oracle database instance.
For Microsoft Windows systems, the net service name of the Oracle database instance must be specified in the Oracle Net Service Name field of the TimesTen Cache tab within the TimesTen ODBC Setup dialog box.
DatabaseCharacterSet must match 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
WHERE parameter='NLS_CHARACTERSET';
UID specifies the name of a cache user, such as the cache manager user, that has the same name as a companion Oracle Database user who can access the cached Oracle Database tables. The UID connection attribute can be specified in a Direct 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 Direct DSN, a Client DSN, or a connection string.
OraclePWD specifies the password of the companion Oracle Database user that has the same name as the TimesTen user specified in the UID connection attribute and can access the cached Oracle Database tables.
Note:
See "Create the TimesTen users" for more details on the cache manager user and its companion Oracle Database user.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. See "Setting a passthrough level".
LockLevel must be set to its default of 0 (row-level locking) because TimesTen Cache does not support database-level locking.
ReplicationApplyOrdering and CacheAWTParallelism control parallel propagation of changes to TimesTen cache tables in an AWT cache group to the corresponding Oracle Database tables. See "Configuring parallel propagation to Oracle Database tables".
First, you must create a user who performs cache group operations. We refer to this user as the cache manager user. The TimesTen cache manager user must have the same name as a companion Oracle Database user that can access the cached Oracle Database tables. For example, the companion Oracle Database user must have privileges to select from and update the cached Oracle Database tables. The companion Oracle Database user can be the cache administration user, a schema user, or some other existing user. For ease of use, making the cache administration user be the companion Oracle Database user of the cache manager user is preferable; however, if you are concerned with the high level of privileges assigned to the cache administration user, then choose another Oracle Database user as the companion Oracle user. The password of the cache manager user can be different than the password of the companion Oracle Database user with the same name.
Note:
You can create multiple cache manager users on a TimesTen database, such as one for each TimesTen DBA. However, you can only define a single cache administration user on the Oracle database for this particular TimesTen database. (You can use the same cache administration user for all TimesTen databases that connect to the Oracle database or define a separate cache administration user for each TimesTen database.) If you create multiple cache manager users, one or more of these users can use the cache administration user as its companion Oracle user.The cache manager user creates the cache groups. It may perform operations such as loading or refreshing a cache group although these operations can be performed by any TimesTen user that has sufficient privileges. The cache manager user can also monitor various aspects of the caching environment, such as asynchronous operations that are performed on cache groups such as autorefresh.
Then, you must create a user with the same name as an Oracle Database schema user for each schema user who owns or will own Oracle Database tables to be cached in the TimesTen database. We refer to these users as cache table users, because the TimesTen cache tables are to 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 Database table. The password of a cache table user can be different than the password of the Oracle Database 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 a companion Oracle Database user that can select from and update the cached Oracle Database tables. Connect to the TimesTen database specifying that user's name in the UID connection attribute, and supply the corresponding TimesTen and Oracle Database 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 cache1 DSN as the instance administrator. Then create the cache manager user cacheuser whose name, in this example, is the same as the cache administration user, who will also act as the cache manager's companion Oracle user. Then, create a cache table user oratt whose name is the same as the Oracle Database schema user who is to own the Oracle Database tables to be cached in the TimesTen database.
% ttIsql cache1 Command> CREATE USER cacheuser IDENTIFIED BY timesten; Command> CREATE USER oratt IDENTIFIED BY timesten;
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. All of the privileges required for the TimesTen cache manager user for each cache operation are listed in "Required privileges for the cache administration user and the cache manager user".
Example 3-8 Granting privileges to the cache manager user
The cacheuser cache manager user requires privileges to perform the following operations:
Set the cache manager user and password (CACHE_MANAGER).
Start or stop the cache agent and replication agent processes on the TimesTen database (CACHE_MANAGER).
Set a cache agent start policy (CACHE_MANAGER).
Set a replication agent start policy (ADMIN)
Create cache groups to be owned by the cache manager user (CREATE [ANY] CACHE GROUP, inherited by the CACHE_MANAGER privilege; CREATE [ANY] TABLE to create the underlying cache tables which are to be owned by the oratt cache table user).
Alter, load, refresh, flush, unload or drop a cache group requires the appropriate privilege:
ALTER ANY CACHE GROUP
LOAD {ANY CACHE GROUP | ON cache_group_name
REFRESH {ANY CACHE GROUP | ON cache_group_name
FLUSH {ANY CACHE GROUP | ON cache_group_name
UNLOAD {ANY CACHE GROUP | ON cache_group_name
DROP ANY CACHE GROUP and DROP ANY TABLE
Required privileges for other cache operations, such as dynamic load, full autorefresh and asynchronous writethrough, are listed in "Required privileges for the cache administration user and the cache manager user".
As the instance administrator, use the ttIsql utility to grant the cacheuser cache manager user the required privileges:
Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser; Command> exit
You must set the cache administration user name and password in the TimesTen database before any cache group operation can be issued with the ttCacheUidPwdSet built-in procedure. The cache agent connects to the Oracle database as this user to create and maintain Oracle Database objects that store information used to enforce predefined behaviors of particular cache group types. In addition, both the cache and replication agents connect to the Oracle database with the credentials set with the ttCacheUidPwdSet built-in procedure to manage Oracle database operations.
Note:
When you connect to the TimesTen database to work with AWT or read-only cache groups, TimesTen Classic uses the credentials set with thettCacheUidPwdSet built-in procedure when connecting to the Oracle database on behalf of these cache groups.
When you connect to the TimesTen database to work with SWT or user managed cache groups or passthrough operations, TimesTen Classic connects to the Oracle database using the current user's credentials as the user name and the OraclePwd connection attribute as the Oracle password. Thus, the correct user name and Oracle database password that should be used for connecting to the Oracle database must be set correctly in the connection string or with the connection attributes.
The cache administration user name and password need to be set only once in each TimesTen database that caches Oracle Database data unless it needs to be changed. For example, if you modify the password of the cache administration user, if the TimesTen database is destroyed and re-created, or if 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 cannot be changed if there are cache groups in the database. The cache groups must be dropped before you can drop and recreate the cache administration user. See "Changing cache user names and passwords" for more details.
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 after connecting as the cache manager user:
% ttIsql "DSN=cache1;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 cache1
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.
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 database 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-10 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 cache1
The cache agent is a TimesTen Classic process that performs cache operations such as loading a cache group and autorefresh, as well as manages Oracle Database objects used to enforce the predefined behaviors of particular cache group types.
Example 3-11 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 cache1
Example 3-12 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 cache1
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 autorefresh cache group. Instead, wait for at least two minutes to allow the cache agent to clean up Oracle Database 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 TimesTen Cache. The start of any XA or JTA transaction fails if the cache agent is running.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.
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.
Note:
For more details, see "ttAdmin," "ttCachePolicySet," "ttCacheStart" and "ttCacheStop" in the Oracle TimesTen In-Memory Database Reference.Example 3-13 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 cache1