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:
The tasks include:
Check the Oracle Database version. The Oracle In-Memory Database Cache supports the following Oracle server releases:
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
| Unix Systems | Environment variable | Directory to add |
|---|---|---|
| Solaris and Linux | LD_LIBRARY_PATH or SHLIB_PATH | TimesTen_Base/lib |
| AIX systems | LIBPATH | TimesTen_Base/lib |
| All | PATH | TimesTen_Base/bin |
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:
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:
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:
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.
The cache administration user owns all Oracle objects (tables and triggers) used to manage a caching environment.
Give the tablespace that was created for the timesten user as the default tablespace.
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:
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:
Additional privileges must be required for some operations.
For security purposes, if you do not want to grant the RESOURCE and CREATE ANY TRIGGER privileges to the cache administration user required to automatically create the Oracle objects, you can manually create these objects.
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;
A cached table must have a primary key or a unique index defined on non-nullable columns (indice).
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.
SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8
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:
The cache manager 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:
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:
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;
If you want to use the CACHE GROUP wizard of Sql Developer, the CACHE_MANAGER privilege is until now required. Bug ?
To perform operations on a cache group or cache table, you have to connect to the TimesTen database with the following connection attributes:
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.
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
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:
con1: Command> set passthrough 3;
Passthrough command has set autocommit off.
con1: Command> show passthrough;
PassThrough = 3
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.
-- The procedure ttGridCreate creates a cache grid called "samplegrid".
call ttGridCreate ('samplegrid');
commit;
-- 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: