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

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:

  1. Create a default tablespace to be used for storing TimesTen Application-Tier Database Cache management objects.

  2. 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.

  3. As the sys user, create one or more schema users to own the cached Oracle Database tables (may be existing users).

  4. 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.

  5. 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.

  6. 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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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 userFoot 1  Privileges required for TimesTen cache manager userFoot 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

RESOURCEFootref 4

CREATE PROCEDURE

CREATE ANY TRIGGERFoot 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.

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

  • RESOURCEFootref 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 SESSIONFoot 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 [DYNAMIC] READONLY CACHE GROUP with AUTOREFRESH MODE FULL

CREATE [DYNAMIC] ASYNCHRONOUS WRITETHROUGH [GLOBAL] CACHE GROUP

CREATE [DYNAMIC] SYNCHRONOUS WRITETHROUGH CACHE GROUP

CREATE [DYNAMIC] USERMANAGED CACHE GROUP

(see variants in following rows)

CREATE [DYNAMIC] USERMANAGED CACHE GROUP with AUTOREFRESH MODE INCREMENTAL

CREATE [DYNAMIC] USERMANAGED CACHE GROUP with AUTOREFRESH MODE FULL

CREATE [DYNAMIC] USERMANAGED CACHE GROUP with READONLY

CREATE [DYNAMIC] USERMANAGED CACHE GROUP with PROPAGATE

ALTER CACHE GROUP SET AUTOREFRESH STATE PAUSED

ALTER ANY CACHE GROUPFoot 10 

ALTER CACHE GROUP SET AUTOREFRESH STATE ON

ALTER ANY CACHE GROUPFootref 10

ALTER CACHE GROUP SET AUTOREFRESH STATE OFF

CREATE SESSION

ALTER ANY CACHE GROUPFootref 10

ALTER CACHE GROUP SET AUTOREFRESH MODE FULL

CREATE SESSION

ALTER ANY CACHE GROUPFootref 10

ALTER CACHE GROUP SET AUTOREFRESH MODE INCREMENTAL

ALTER ANY CACHE GROUPFootref 10

ALTER CACHE GROUP SET AUTOREFRESH INTERVAL

ALTER ANY CACHE GROUPFootref 10

LOAD CACHE GROUP

LOAD {ANY CACHE GROUP | ON cache_group_name)Footref 10

REFRESH CACHE GROUP

REFRESH {ANY CACHE GROUP | ON cache_group_name)Footref 10

FLUSH CACHE GROUP

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

Synchronous writethrough or propagate

Asynchronous writethrough

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_nameFootref 6

None

Full autorefresh

SELECT ON table_nameFootref 6

None

Dynamic load

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 1 At minimum, the cache administration user must have the CREATE TYPE privilege.

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

Footnote 3 If 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 4 Not required if the Oracle Database objects used to manage the caching of Oracle Database data are manually created with the initCacheAdminSchema.sql script.

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

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

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

Footnote 9 Required 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 10 Required if the TimesTen user accessing the cache group does not own the cache group.

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

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

Footnote 13 The 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 14 Required if the TimesTen user accessing the cache table does not own the table.