Configuring the Oracle Database to Cache Data

Create the Oracle Database Users and Default Tablespace

Create a default tablespace to store meta information about cache operations. Create a cache administration 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.

Perform the following on the Oracle database:

Note:

You cannot use the Oracle Autonomous Database for transaction processing (ATP) as a source for caching data in TimesTen. In addition, if you are using a multitenant container database (CDB) or pluggable database (PDB), note the specific instructions below on how to create the cache administration user and grant this user privileges in a CDB or PDB.

Create a default tablespace that stores information about cache operations.
This tablespace is used for storing cache management objects that should not be shared with other applications. While you may also store Oracle database tables that are cached in a TimesTen database, we strongly recommend that this tablespace be used solely by the TimesTen database for cache management.

Note:

See Managing a Cache Environment With Oracle Database Objects for a list of Oracle database tables used by the cache administration user.

In the following SQL*Plus example, the default tablespace is cachetblsp and defines a 5 GB data file named tt_cache.f. Choose a size that is appropriate for your particular needs. Provide the SEGMENT SPACE MANAGEMENT AUTO clause so that the Oracle database automatically manages the free space of all segments in the tablespace (useful for monitoring autorefresh).

Note:

The use of the sys@tnsservicename as sysdba user in this example is applicable only for a test environment.
```
% cd timesten_home/install/oraclescripts
% sqlplus sys@tnsservicename as sysdba
Enter password: password
SQL> CREATE TABLESPACE cachetblsp DATAFILE 'tt_cache.f' SIZE 5G 
 SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
```
Create an Oracle cache administration 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.
If you are using a multitenant container database (CDB) or pluggable database (PDB), the Oracle cache administrator user can be one of the following:
- Local user: A local user is a database user that can operate only within a single PDB. You must assign cache privileges only within the PDB in which this user exists.
- Common user: A common user is a database user known in every container and has the same identity in the CBD root and in every existing and future PDB in the CDB. You must assign cache privileges within each PDB in the CDB in which you want to use cache.
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.

See Caching the Same Oracle Table on Two or More TimesTen Databases.

Designate the tablespace as the default tablespace for the Oracle 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 cache groups with autorefresh and AWT cache groups are created in the same tablespace. To create and manage these objects, the Oracle cache administration user must have a high level of privileges. A cache group with autorefresh refers to a read-only cache group or a user managed cache group that uses the AUTOREFRESH MODE INCREMENTAL cache group attribute.

See Managing a Cache Environment With Oracle Database Objects for a list of Oracle Database tables and triggers owned by the cache administration user.

Note:

If you create multiple cache administration users, each may use the same or different tablespace as their default tablespace.

As the sys user, use SQL*Plus to create the Oracle database cache administration user cacheadmin. In the following example, the default tablespace for the cacheadmin user is cachetblsp.
```
SQL> CREATE USER cacheadmin IDENTIFIED BY orapwd
    DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;
```
Identify one or more existing schemas (or create a new schema) with schema owners that own Oracle database tables that are to be cached in a TimesTen database. The tables to be cached may or may not already exist.

Grant Privileges to the Oracle Cache Administration User

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.

The main privileges required for the Oracle cache administration user can be granted in bulk by running the SQL*Plus script timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql as the sys user. This script grants the cache administration user the minimum set of privileges required to perform cache operations.

If you are using a multitenant container database (CDB) or pluggable database (PDB), run the SQL*Plus script timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql to assign cache privileges as follows:

If the cache administrator user is a local user: You must assign cache privileges only within the PDB in which this user exists. This is the preferred method.
If the cache administrator user is a common user: You must assign cache privileges within each PDB in the CDB in which you want to use cache. Do not run the SQL*Plus script to grant privileges to the common user in the CBD root.

See Create Oracle Database Objects Used to Manage Data Caching.

You also need to grant the Oracle cache administration user privileges based on the type of cache operation. The entire list of privileges required for this user for each cache operation are listed in Required Privileges for Cache Administration User for Cache Operations.

Create Oracle Database Objects Used to Manage Data Caching

You request TimesTen to 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 cache groups with autorefresh and AWT cache groups.

These Oracle database objects are automatically created if the cache administration user has been granted the required privileges with one of the following SQL*Plus scripts:

The grantCacheAdminPrivileges.sql Script: Run this script to grant all required privileges to the cache administration user that are required to create Oracle database objects used to manage the caching of Oracle database data when particular cache group operations are performed. The cache administration user then automatically creates Oracle database objects used to manage caching Oracle database data in a TimesTen database.
The initCacheAdminSchema.sql Script: Run this script to grant all required privileges except for the CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, and EXECUTE ON SYS.DBMS_LOB package privileges. For security reasons, you may not want to grant these privileges. The cache administration user then automatically creates all Oracle database objects used to manage caching Oracle database data in a TimesTen database, except for cache groups that use autorefresh.

If you want to check if the Oracle cache administration user has all of the necessary privileges that are required for cache operations, you can run the The checkAdminPrivileges.sql Script.

The grantCacheAdminPrivileges.sql Script

The grantCacheAdminPrivileges.sql script grants privileges to the cache administration user that are required to automatically create Oracle Database objects used to manage the caching of Oracle Database data when particular cache group operations are performed.

See Required Privileges for Cache Administration User for Cache Operations 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.

Run the timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql as the sys user. The cache administration user name is passed as an argument to the grantCacheAdminPrivileges.sql script.

Note:

Alternatively, you can create these objects as described in The initCacheAdminSchema.sql Script before performing any cache group operations if, for security purposes, you do not want to grant certain privileges to the cache administration user required to automatically create objects necessary for managing autorefresh.

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.

As the sys user, use SQL*Plus to run the grantCacheAdminPrivileges.sql script to grant privileges to the cache administration user. The cache administration user then automatically creates Oracle Database objects used to manage caching Oracle Database data in a TimesTen database.

The grantCacheAdminPrivileges.sql script requires the Oracle database cache administration user name as input, which is cacheadmin in this example.

SQL> @grantCacheAdminPrivileges cacheadmin
SQL> exit

For example, with cache groups with autorefresh, 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 initCacheAdminSchema.sql Script

The Oracle database cache administration user requires certain privileges to automatically create the Oracle database objects.

The cache administration user requires privileges used to:

Store information about TimesTen databases that are associated with a particular cache environment.
Enforce the predefined behaviors of cache groups with autorefresh. In this case, the cache administration user requires certain privileges 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 CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TABLE, and EXECUTE ON SYS.DBMS_LOB package privileges to the cache administration user required to automatically create the Oracle Database objects, you can use the initCacheAdminSchema.sql script. See Required Privileges for Cache Administration User for Cache Operations for a full list of privileges granted by this script.

To 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 cache groups with autorefresh and AWT cache groups. The initCacheAdminSchema.sql script requires the cache administration user name as input.

In addition to the privileges granted to the cache administration user by running the initCacheAdminSchema.sql script, you may need to grant the user 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.

As the sys user, use SQL*Plus to run the initCacheAdminSchema.sql script to create Oracle Database objects, which are used to manage caching data. These Oracle Database objects enforce the predefined behaviors of a cache group with autorefresh and AWT cache groups, and grant a limited set of privileges to the cache administration user. In the following example, the Oracle database cache administration user name is cacheadmin.

SQL> @initCacheAdminSchema "cacheadmin"
SQL> exit

Other Oracle database objects associated with Oracle database tables that are cached in a cache group with autorefresh are needed to enforce the predefined behaviors of these cache group types. See Manually Creating Oracle Database Objects for Cache Groups With Autorefresh for details about how to create these additional objects as part of the steps for creating a cache group with autorefresh.

To view a list of the Oracle database objects created and used by TimesTen to manage the caching of Oracle database data, run 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 the cache administration user.

The checkAdminPrivileges.sql Script

The checkAdminPrivileges.sql script checks that the cache administration user has been granted the required privileges to automatically create Oracle Database objects used to manage the caching of Oracle Database data when particular cache group operations are performed. This script checks that the user running the script has all of the privileges granted in the grantCacheAdminPrivileges.sql script.

See Required Privileges for Cache Administration User for Cache Operations 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.

Run the timesten_home/install/oraclescripts/checkAdminPrivileges.sql as the cache administration user.

Use SQL*Plus on the Oracle Database system from an operating system shell or command prompt, and connect to the Oracle database instance as the cache administration user that you want checked for privileges. The following example shows that the user has all of the required privileges.

SQL> @checkAdminPrivileges.sql 
**** Checking privileges for cache administrator user ****
**** User has all privileges for a cache administrator user ****

The following example shows the output if you have missing privileges needed as a cache administration user on an Oracle database:

SQL> @checkAdminPrivileges.sql
**** Checking privileges for cache administrator user **** 
Missing CREATE OPERATOR
Missing CREATE INDEXTYPE
Missing CREATE CLUSTER
Missing EXECUTE ON SYS.DBMS_LOCK
Missing EXECUTE ON SYS.DBMS_DDL
Missing EXECUTE ON SYS.DBMS_FLASHBACK
Missing EXECUTE ON SYS.DBMS_LOB
Missing SELECT on SYS.GV$LOCK
Missing SELECT on SYS.GV$SESSION
Missing SELECT on SYS.DBA_DATA_FILES
Missing SELECT on SYS.V$DATABASE
Missing SELECT on GV$PROCESS
Missing UNLIMITED TABLESPACE
Missing SELECT ANY TRANSACTION
Missing table ARDL_CG_COUNTER
**** User missing privileges. Missing privilege count: 15 ****