A Procedure and Privileges for Caching Oracle Database Data in TimesTen

The following sections provide a quick reference on the steps for creating a cache environment as well as the privileges required to do so:

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

Quick reference to cache Oracle Database data in TimesTen

The following section provides a quick reference on the steps necessary when setting up an environment that caches Oracle Database data in a TimesTen in-memory database. For a detailed explanation and examples for each step, see Chapter 2, "Getting Started", Chapter 3, "Setting Up a Caching Infrastructure", and Chapter 4, "Defining Cache Groups".

Perform the following on the Oracle database:

Create a default tablespace to be used for storing TimesTen Application-Tier Database Cache management objects.
Create the timesten user, Oracle Database tables owned by the timesten user, and the TT_CACHE_ADMIN_ROLE role that defines privileges on these Oracle Database tables by running the SQL*Plus script TimesTen_install_dir/oraclescripts/initCacheGlobalSchema.sql as the sys user.
As the sys user, create one or more schema users to own the cached Oracle Database tables (may be existing users).
As the sys user, create the cache administration user that creates, owns, and maintains Oracle Database objects that store information used to manage a specific cache grid (if one is defined) and enforce predefined behaviors of particular cache group types. In the CREATE USER statement for the cache administration user, designate the tablespace that was created for the timesten user as the default tablespace for the cache administration user.

See "Create the Oracle database users" for more information about the Oracle Database users.
As the sys user, run the TimesTen_install_dir/oraclescripts/grantCacheAdminPrivileges.sql script to grant the cache administration user the privileges required to perform any cache grid operations (if one is defined), create the desired types of cache groups, and perform operations on the cache groups. Alternatively, you can manually create each Oracle Database object.

See "Automatically create Oracle Database objects used to manage data caching" or "Manually create Oracle Database objects used to manage data caching" to determine the appropriate script to run.

If you are manually creating the Oracle Database objects, you also need to run the TimesTen_install_dir/oraclescripts/initCacheGridSchema.sql script to create the Oracle Database tables used to store information about TimesTen databases that are associated with a particular cache grid.
Some privileges cannot be granted until the cached Oracle Database tables have been created. To grant these privileges, execute GRANT statements as the sys user.

See "Required privileges for the cache administration user and the cache manager user" for more information about the privileges that must be granted to the cache administration user to perform particular cache operations.

Perform the following on the TimesTen database:

Define a DSN that references the TimesTen database that is to be used to cache data from an Oracle database.
1. Set the OracleNetServiceName connection attribute to the Oracle Net service name that references the Oracle database instance.
2. Set the DatabaseCharacterSet connection attribute to the Oracle database character set. The TimesTen database character set must match the Oracle database character set.
3. Then, connect to the DSN to create the database if this is a standalone database or is to be an active database of an active standby pair.
See "Define a DSN for the TimesTen database" for more information about defining a DSN for a TimesTen database that is to be used to cache data from an Oracle database.
Create the following users in the TimesTen database:
- Cache manager user
  
  This user must have the same name as a companion Oracle Database user that can access the cached Oracle Database tables. The companion Oracle Database user can be the cache administration user, a schema user, or some other existing user. The password of the cache manager user and the Oracle Database user with the same name can be different.
- One or more cache table users who own the TimesTen cache tables
  
  These users must have the same name as the Oracle Database schema users who own the cached Oracle Database tables. The password of a cache table user and the Oracle Database user with the same name can be different.
Execute CREATE USER statements as the instance administrator.

See "Create the TimesTen users" for more information about the TimesTen users.
Grant the cache manager user the privileges required to perform the cache grid operations, create the desired types of cache groups, and perform operations on the cache groups. Execute GRANT statements as the instance administrator.

See "Required privileges for the cache administration user and the cache manager user" for more information about the privileges that must be granted to the cache manager user to perform particular cache operations.
Set the cache administration user name and password in the TimesTen database either by calling the ttCacheUidPwdSet built-in procedure as the cache manager user or running a ttAdmin -cacheUidPwdSet utility command as a TimesTen external user with the CACHE_MANAGER privilege.

See "Set the cache administration user name and password" for more information about setting the cache administration user name and password in a TimesTen database.
If you are going to use a cache grid for global cache groups, then perform the following:
1. Create a cache grid by calling the ttGridCreate built-in procedure in the TimesTen database as the cache manager user.
  
  See "Create a cache grid" for more information about creating a cache grid.
2. Associate the TimesTen database with the cache grid by calling the ttGridNameSet built-in procedure in the TimesTen database as the cache manager user.
  
  See "Associate a TimesTen database with a cache grid" for more information about associating a TimesTen database with a cache grid.
Start the cache agent on the TimesTen database either by calling the ttCacheStart built-in procedure as the cache manager user or running a ttAdmin -cacheStart utility command as a TimesTen external user with the CACHE_MANAGER privilege.

See "Managing the cache agent" for more information about starting a cache agent on a TimesTen database.
Design the schema for the cache groups by determining which Oracle Database tables to cache and within those tables, which columns and rows to cache. For multiple table cache groups, determine the relationship between the tables by defining which table is the root table, which tables are direct child tables of the root table, and which tables are the child tables of other child tables. For each cached column, determine the TimesTen data type to which the Oracle Database data type should be mapped.

See "Mappings between Oracle Database and TimesTen data types" for a list of valid data type mappings between the Oracle and TimesTen databases.

For each cache group, determine what type to create (read-only, SWT, AWT, user managed) based on the application requirements and objectives. Also, determine whether each cache group is to be explicitly loaded or dynamic, and local or global.

Then, create the cache groups.

See "Creating a cache group" for more information about creating a cache group.
If this TimesTen database is intended to be an active database of an active standby pair, create an active standby pair replication scheme in the database.
If the TimesTen database contains an active standby pair replication scheme or at least one AWT cache group, start the replication agent on the database either by calling the ttRepStart built-in procedure as the cache manager user or running a ttAdmin -repStart utility command as a TimesTen external user with the CACHE_MANAGER privilege.

See "Managing the replication agent" for more information about starting a replication agent on a TimesTen database.
If the TimesTen database contains at least one global cache group, attach the TimesTen database to the cache grid that the database associated with by calling the ttGridAttach built-in procedure as the cache manager user.

See "Attach a TimesTen database to a cache grid" for more information about attaching a TimesTen database to a cache grid.
Manually load the cache tables in explicitly loaded cache groups using LOAD CACHE GROUP statements, and load the cache tables in dynamic cache groups using proper SELECT, UPDATE or INSERT statements.

See "Loading and refreshing a cache group" for more information about manually loading cache tables in a cache group.

See "Dynamically loading a cache instance" for more information about dynamically loading cache tables in a dynamic cache group.
If using a cache grid, you can add subsequent standalone TimesTen databases can as members to an existing cache grid.

See "Creating and configuring a subsequent standalone TimesTen database" for details about creating another standalone TimesTen database and adding that database to an existing cache grid.
An active standby pair can be added as a member to an existing cache grid to achieve high availability by replicating the cache tables to another TimesTen database.

See "Create and configure the active database" for details about creating an active database and adding the database to an existing cache grid.

To create the standby database from the active database, create a DSN for the standby database, and then run a ttRepAdmin -duplicate utility command on the standby database system as a TimesTen external user with the ADMIN privilege. For the command to succeed, the cache manager user in the active database must be granted the ADMIN privilege. Then, configure the database and add it as a member to the grid.

See "Create and configure the standby database" for details about creating a standby database and adding the database to an existing cache grid.

To create an optional read-only subscriber database from the standby database, create a DSN for the subscriber database. Then, run a ttRepAdmin -duplicate utility command on the subscriber database system as a TimesTen external user with the ADMIN privilege. For the command to succeed, the cache manager user in the standby database must be granted the ADMIN privilege. Then, start the replication agent on the database.

See "Create and configure the read-only subscriber database" for details about creating a read-only subscriber database for an active standby pair.

Required privileges for the cache administration user and the cache manager user

The privileges that the Oracle Database 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 cache administration user are listed in the first column and the privileges required for the TimesTen cache manager user for each cache operation are listed in the second column in Table A-1.

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

Cache operation	Privileges required for Oracle Database cache administration user^Foot 1	Privileges required for TimesTen cache manager user^Foot 2
Initialize the cache administration user. The `grantCacheAdminPrivileges.sql` script grants these privileges to the cache administration user.	`CREATE SESSION` `TT_CACHE_ADMIN_ROLE` `EXECUTE ON SYS.DBMS_LOCK` `RESOURCE`^Footref 4 `CREATE PROCEDURE` `CREATE ANY TRIGGER`^Foot 3^,^Footref 4 `EXECUTE ON SYS.DBMS_LOB` `SELECT ON SYS.ALL_OBJECTS` `SELECT ON SYS.ALL_SYNONYMS` `CREATE TYPE` `SELECT ON SYS.GV_$LOCK` `SELECT ON SYS.GV_$SESSION` `SELECT ON SYS.DBA_DATA_FILES` `SELECT ON SYS.USER_USERS` `SELECT ON SYS.USER_FREE_SPACE` `SELECT ON SYS.USER_TS_QUOTAS` `SELECT ON SYS.USER_SYS_PRIVS` Permissions for the default tablespace
Set the cache administration user name and password with either: Call the `ttCacheUidPwdSet` built-in procedure. Run the `ttAdmin -cacheUidPwdSet` utility command.	`CREATE SESSION` `RESOURCE`^Foot 4	`CACHE_MANAGER`
Get the cache administration user name with either: 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 4	`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 with either: 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 with either: Call the `ttCachePolicySet` built-in procedure. Run the `ttAdmin -cachePolicy` utility command.	`CREATE SESSION`^Foot 5	`CACHE_MANAGER`
Return the cache agent start policy setting: Call the `ttCachePolicyGet` built-in procedure.	`CREATE SESSION`	None
Start the replication agent with either: Call the `ttRepStart` built-in procedure. Run the `ttAdmin -repStart` utility command.	None	`CACHE_MANAGER`
Stop the replication agent with either: 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 ACTIVE STANDBY PAIR` with `INCLUDE CACHE GROUP` when the cache group created is an AWT cache group	`CREATE TRIGGER`
Duplicate the database with `ttRepAdmin -duplicate` when using an AWT cache group within an active standby pair replication scheme	`CREATE TRIGGER`
`CREATE [DYNAMIC] READONLY CACHE GROUP` with `AUTOREFRESH MODE INCREMENTAL`	`CREATE SESSION` `SELECT ON` `table_name`^Foot 6 `RESOURCE`^Footref 4 `CREATE ANY TRIGGER`^Footref 4	`CREATE [ANY] CACHE GROUP`^Foot 7 `CREATE [ANY] TABLE`^Foot 8
`CREATE [DYNAMIC] READONLY CACHE GROUP` with `AUTOREFRESH MODE FULL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] ASYNCHRONOUS WRITETHROUGH [GLOBAL] CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6 `RESOURCE`^Footref 4	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] SYNCHRONOUS WRITETHROUGH CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` (see variants in following rows)	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `AUTOREFRESH MODE INCREMENTAL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6 `RESOURCE`^Footref 4 `CREATE ANY TRIGGER`^Footref 4	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `AUTOREFRESH MODE FULL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `READONLY`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`CREATE [DYNAMIC] USERMANAGED CACHE GROUP` with `PROPAGATE`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`CREATE [ANY] CACHE GROUP`^Footref 7 `CREATE [ANY] TABLE`^Footref 8
`ALTER CACHE GROUP SET AUTOREFRESH STATE PAUSED`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6^,^Foot 9 `RESOURCE`^Footref 4^, ^Footref 9 `CREATE ANY TRIGGER`^Footref 4 ^,^Footref 9	`ALTER ANY CACHE GROUP`^Foot 10
`ALTER CACHE GROUP SET AUTOREFRESH STATE ON`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6^, ^Footref 9 `RESOURCE`^Footref 4^, ^Footref 9 `CREATE ANY TRIGGER`^Footref 4^, ^Footref 9	`ALTER ANY CACHE GROUP`^Footref 10
`ALTER CACHE GROUP SET AUTOREFRESH STATE OFF`	`CREATE SESSION`	`ALTER ANY CACHE GROUP`^Footref 10
`ALTER CACHE GROUP SET AUTOREFRESH MODE FULL`	`CREATE SESSION`	`ALTER ANY CACHE GROUP`^Footref 10
`ALTER CACHE GROUP SET AUTOREFRESH MODE INCREMENTAL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6 `RESOURCE`^Footref 4 `CREATE ANY TRIGGER`^Footref 4	`ALTER ANY CACHE GROUP`^Footref 10
`ALTER CACHE GROUP SET AUTOREFRESH INTERVAL`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6^, ^Foot 11	`ALTER ANY CACHE GROUP`^Footref 10
`LOAD CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`LOAD {ANY CACHE GROUP \| ON` `cache_group_name)`^Footref 10
`REFRESH CACHE GROUP`	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`REFRESH {ANY CACHE GROUP \| ON` `cache_group_name`)^Footref 10
`FLUSH CACHE GROUP`	`CREATE SESSION` `UPDATE ON` `table_name`^Footref 6 `INSERT ON` `table_name`^Footref 6	`FLUSH {ANY CACHE GROUP \| ON` `cache_group_name)`^Footref 10
`UNLOAD CACHE GROUP`	None	`UNLOAD {ANY CACHE GROUP \| ON` `cache_group_name`)^Footref 10
`DROP CACHE GROUP`	`CREATE SESSION`	`DROP ANY CACHE GROUP`^Footref 10 `DROP ANY TABLE`^Foot 12
Synchronous writethrough or propagate	`CREATE SESSION` `INSERT ON` `table_name`^Footref 6^, ^Foot 13 `UPDATE ON` `table_name`^Footref 6^{, ^Footref 13} `DELETE ON` `table_name`^Footref 6^{, ^Footref 13}	`INSERT ON` `table_name`^Foot 14 `UPDATE ON` `table_name`^Footref 14 `DELETE ON` `table_name`^Footref 14
Asynchronous writethrough	`CREATE SESSION` `INSERT ON` `table_name`^Footref 6 `UPDATE ON` `table_name`^Footref 6 `DELETE ON` `table_name`^Footref 6	`INSERT ON` `table_name`^Footref 14 `UPDATE ON` `table_name`^Footref 14 `DELETE ON` `table_name`^Footref 14
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 Database `CLOB`, `BLOB` and `NCLOB` fields when the `CacheAWTMethod` connection attribute is set to 1	`EXECUTE` privilege on the Oracle Database `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 autorefresh	`SELECT ON` `table_name`^Footref 6	None
Full autorefresh	`SELECT ON` `table_name`^Footref 6	None
Dynamic load	`CREATE SESSION` `SELECT ON` `table_name`^Footref 6	`SELECT ON` `table_name`^Footref 14 `UPDATE ON` `table_name`^Footref 14 `DELETE ON` `table_name`^Footref 14 `INSERT ON` `table_name`^Footref 14
Aging	None	`DELETE {ANY TABLE \| ON` `table_name`)^Footref 14
Set the LRU aging attributes Call the `ttAgingLRUConfig` built-in procedure	None	`ADMIN`
Generate Oracle Database SQL statements to manually install or uninstall Oracle Database 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 the Oracle database Call the `ttCachePropagateFlagSet` built-in procedure	None	`CACHE_MANAGER`
Configure cache agent timeout and recovery method for autorefresh 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 Database 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 TYPE privilege.

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

^Footnote 3If the cache administration user will not create autorefresh cache groups, then you can grant the CREATE TRIGGER privilege instead of the CREATE ANY TRIGGER privilege.

^Footnote 4Not required if the Oracle Database objects used to manage the caching of Oracle Database data are manually created with the initCacheAdminSchema.sql script.

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

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

^Footnote 7The 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 8ANY is required if any of the cache tables are owned by a user other than the cache manager user.

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

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

^Footnote 11Required if the cache group's autorefresh mode is incremental.

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

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

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