Timesten - How to set up the Timesten Cache ?

Unix

Example .bash_profile:

ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE

ORACLE_HOME=${ORACLE_BASE}/product/11.2.0/dbhome_1
export ORACLE_HOME

TIMESTEN_DIR=${ORACLE_BASE}/product/TimesTen/tt1122
export TIMESTEN_DIR

PATH=$PATH:$HOME/bin:${ORACLE_HOME}/bin:${ORACLE_HOME}/OPatch:$TIMESTEN_DIR/bin
export PATH

LD_LIBRARY_PATH=$TIMESTEN_DIR/lib
export LD_LIBRARY_PATH

Windows

The PATH system environment variable must include the following directories:

• Oracle_install_dir\bin
• TimesTen_install_dir\lib
• TimesTen_install_dir\bin

Example:

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

Default tablespace

Default tablespace for:

• the timesten_user
• and the cache_administration_user

CREATE TABLESPACE tt_cache DATAFILE 'datfttuser.dbf' SIZE 100M; -- Without ASM
CREATE TABLESPACE tt_cache DATAFILE '+MyDiskGroup' SIZE 100M; -- With ASM

Timesten User

An oracle user “timesten” owns all 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

Pass the previous created tablespace as an argument to the initCacheGlobalSchema.sql script.

[oracle@di-oradb-01 tt]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 14 14:40:49 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> @initCacheGlobalSchema.sql cache_tt

Please enter the tablespace where TIMESTEN user is to be created
The value chosen for tablespace is cache_tt

******* Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE starts *******
1. Creating TIMESTEN schema
2. Creating TIMESTEN.TT_GRIDID table
3. Creating TIMESTEN.TT_GRIDINFO table
4. Creating TT_CACHE_ADMIN_ROLE role
5. Granting privileges to TT_CACHE_ADMIN_ROLE
** Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE done successfully **

PL/SQL procedure successfully completed.

Cache administration user

The cache administration user owns all Oracle objects (tables and triggers) used to manage a caching environment.

SQL> CREATE USER tt_cache_admin IDENTIFIED BY tt_cache_admin DEFAULT TABLESPACE tt_cache QUOTA UNLIMITED ON tt_cache;

User created.

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

Oracle Database Privileges must be granted to the cache administration user depending on the types of cache groups you create, and the operations that you perform on the cache groups and their cache tables in order to create Cache Oracle objects.

To give to the cache administration user the possibility to automatically create Cache Oracle objects, run the script TimesTen_install_dir/oraclescripts/grantCacheAdminPrivileges.sql as the sys user.

SQL> @grantCacheAdminPrivileges "cache_tt_admin"

Please enter the administrator user id
The value chosen for administrator user id is cache_tt_admin

***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHE_TT_ADMIN
1. Granting the TT_CACHE_ADMIN_ROLE to CACHE_TT_ADMIN
2. Granting the DBMS_LOCK package privilege to CACHE_TT_ADMIN
3. Granting the RESOURCE  privilege to CACHE_TT_ADMIN
4. Granting the CREATE PROCEDURE  privilege to CACHE_TT_ADMIN
5. Granting the CREATE ANY TRIGGER  privilege to CACHE_TT_ADMIN
6. Granting the DBMS_LOB package privilege to CACHE_TT_ADMIN
7. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHE_TT_ADMIN
8. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHE_TT_ADMIN
9. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
11. Granting the CREATE ANY TYPE privilege to CACHE_TT_ADMIN
********* Initialization for cache admin user done successfully *********

For example, with TimesTen - Cache Group 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

Schema user

The schema users designate one or more users that own Oracle tables that will be cached in a TimesTen database.

SQL> CREATE USER OwnerTableToCache IDENTIFIED BY oracle;

For all table to cache, the oracle cache administration user or the oracle cache manager user must have the following privileges:

GRANT SELECT, INSERT, UPDATE, DELETE ON OwnerTableToCache.TableToCache TO tt_cache_admin;

Dsn

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.

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

SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
AL32UTF8

• 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.
• 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).
• ReplicationApplyOrdering and CacheAwtParallelism control parallel propagation of changes to TimesTen cache tables in an AWT cache group to the corresponding Oracle tables.

Example of a standalone TimesTen database

[DI_TT_CACHE_STORE]
Driver=/u01/app/oracle/product/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/app/oracle/product/cache_store/tt_cache_store/di_tt_cache_store
LogDir=/u01/app/oracle/product/cache_store/logs
PermSize=40
TempSize=32
PLSQL=1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
OracleNetServiceName=ORCL

Cache manager user

The cache manager user:

• performs cache grid and cache group operations.
• is responsible for creating and configuring the cache grid and creating the cache groups.
• can monitor the grid itself and various operations that are performed on the cache groups.

The cache manager user:

• name must be 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.
• password can be different than the password of the Oracle user.

In this example, the cache manager user is the same as the Oracle cache administration user.

% ttIsql cachealone1
Command> CREATE USER tt_cache_admin IDENTIFIED BY tt_cache_admin;

The cache manager user 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 cache_table_user)
• “Drop any table” to be able to “Drop a Cache Group” and then and all its table.

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

Cache table user

The cache table user will own the Oracle tables to be cached in the TimesTen database.

The cache table user:

• name is the same name as the Oracle schema/user who owns the tables to be cached
• password can be different than the password of the Oracle schema user with the same name.

Example if you want to cache the content of the OBIEE sample schema, the bisample is a cache table user.

% ttIsql myDSN
Command> CREATE USER DI_BISAMPLE IDENTIFIED BY DI_BISAMPLE;
Command> GRANT CREATE SESSION TO DI_BISAMPLE;

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.

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.

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. Oracle and TimesTen user privileges required for cache operations

Because we want that the cache user may create all type of cache group, we will gives all privileges excepted the ADMIN and CACHE_MANAGER privileges.

GRANT ALL PRIVILEGES TO DI_BISAMPLE;
REVOKE ADMIN, CACHE_MANAGER FROM DI_BISAMPLE;

To perform operations on a cache group or cache table, you have to connect to the TimesTen database with the following connection attributes:

• UID specifies the name of a cache 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.

connect "DSN=DI_TT_CACHE_STORE;UID=DI_BISAMPLE;Pwd=DI_BISAMPLE;OraclePWD=DI_BISAMPLE";