Timesten - How to set up the Timesten Cache ?

Steps to set up the Timesten Cache to cache data of an Oracle Database.

Before you cache Oracle data in a TimesTen database, you must perform certain tasks:

• either outside SQL Developer using command-line interfaces such as SQL*Plus and the ttIsql utility
• or within SQL Developer using the SQL Worksheet.

The tasks include:

• Creating the repository cache (the timesten user and the cache administration user) in the oracle database
• Granting privileges required to the cache administration user on the object to cache
• Creating the cache manager user and the cache table users in the TimesTen database
• Granting cache privileges to the TimesTen users

Check the Oracle Database version. The Oracle In-Memory Database Cache supports the following Oracle server releases:

• Oracle 11g Release 2 (11.2.0.2.0 or above)
• Oracle 11g Release 1 (11.1.0.7.0 or above)
• Oracle 10g Release 2 (10.2.0.5.0 or above)

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

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

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

The following task must be performed on the Oracle database by the sys user.

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

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.

[[email protected] 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.

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

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;

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

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

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";

You must set ONCE by TT database the oracle cache administration user name and password for the cache agent. Any cache grid or cache group operation cannot be issued before.

• Stop the cache agent
• Drop the cache groups in the TT database.
• Connect to the server over direct connection because this operation is not permitted over client-server connections
• Change the cache administration user name and password

As the cache manager user or admin:

% ttIsql "DSN=myDSN;UID=cache_manager_user;PWD=cache_manager_user;OraclePWD=cache_tt_admin"
Command> call ttCacheUidPwdSet('cache_tt_admin','cache_tt_admin');

OR

with the ttAdmin utility or as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheUidPwdSet -cacheUid cache_manager_user -cachePwd ThePassword myDSN

• Restart the cache agent

The cache administration user name setting can be returned programmatically by calling the ttCacheUidGet built-in procedure as a cache manager user:

Command> call ttCacheUidGet;

It can also be returned from a command line by running a ttAdmin utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheUidGet myDsn

With ttISql, Connect as a cache user:

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

and perform the following command:

• Set the passthrough value

con1: Command> set passthrough 3;
Passthrough command has set autocommit off.

• Show the passthrough value

con1: Command> show passthrough;
PassThrough = 3

• Perform a SELECT against the Oracle Database

con1: Command> SELECT * FROM V$VERSION;
< Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production >
< PL/SQL Release 11.2.0.3.0 - Production >
< CORE  11.2.0.3.0      Production >
< TNS for Linux: Version 11.2.0.3.0 - Production >
< NLSRTL Version 11.2.0.3.0 - Production >
5 rows found.

• Configure the process parameter of the oracle database
• Timesten - Cache Grid a cache grid

-- The procedure ttGridCreate creates a cache grid called "samplegrid".  
call ttGridCreate ('samplegrid');
commit;

• Associate the Timesten database to the cache grid

-- ttGridNameset associates the current database to "samplegrid". 
call ttGridNameSet ('samplegrid');

[TimesTen][TimesTen 11.2.2.2.0 ODBC Driver]
[TimesTen]TT5220: Permanent Oracle connection failure error in OCIServerAttach(): 
ORA-12154: TNS:could not resolve the connect identifier specified rc = -1 
-- file "bdbConnect.c", lineno 2870, procedure "ttBDbConnect()"

Set the Timesten - TNSNAMES.ORA for the daemon

[TimesTen][TimesTen 11.2.2.2.0 ODBC Driver]
[TimesTen]TT5213: Bad Oracle login error in OCISessionBegin(): 
ORA-01017: invalid username/password; logon denied rc = -1 
-- file "bdbConnect.c", lineno 2969, procedure "ttBDbConnect()"

Timesten Cache is unable to connect to the Oracle database, verify that you can connect to the database with the value of the following attribute:

• UID as login
• and OraclePWD as password

• Oracle® In-Memory Database Cache User's Guide 11g Release 2 (11.2.2) Part Number E21634-04 - 3 Setting Up a Caching Infrastructure
• Oracle® SQL Developer Oracle TimesTen In-Memory Database Support User's Guide - Release 3.1 Part Number E25286-01 - 2 Prerequisites to Creating and Accessing a Cache Environment