Category Archives: Oracle Timesten

Configure the Oracle Timesten IMDB Cache

Posted on by
Reply

—Oracle Database
—Create a Cache Administrator User.

CREATE TABLESPACE cachetblsp DATAFILE ‘$ORACLE_BASE/oradata/$ORACLE_SID/datattuser.dbf’ SIZE 100M AUTOEXTEND ON;
@initCacheGlobalSchema “cachetblsp”

CREATE USER cacheuser IDENTIFIED BY oracle DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;

 —Create Schema User oratt

CREATE TABLESPACE oratttblsp DATAFILE ‘$ORACLE_BASE/oradata/$ORACLE_SID/orattuser.dbf’ SIZE 100M AUTOEXTEND ON;
CREATE USER oratt IDENTIFIED BY oracle DEFAULT TABLESPACE oratttblsp QUOTA UNLIMITED ON oratttblsp;
GRANT CREATE SESSION,RESOURCE TO oratt;
@grantCacheAdminPrivileges “cacheuser”

—Create a DSN for the timesten Database

SELECT value FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER=’NLS_CHARACTERSET’;

–ON Unix
Timesten_install_dir/info/sys.odbc.ini file

[ODBC Data Sources]
my_cachedb=TimesTen 11.2.2 Driver
elitecachedb=TimesTen 11.2.2 Driver

[scottcachedb]
Driver=/home/timesten/TimesTen/tt1122/lib/libtten.so
DataStore=/home/timesten/u02/ttdata/datastores/scottcachedb
LogDir=/home/timesten/u03/ttdata/logs
PermSize=40
TempSize=32
DatabaseCharacterSet=WE8MSWIN1252
OracleNetServiceName=orcl

ttisql DSN=scottcachedb

–Cache Manager User

CREATE USER cacheuser IDENTIFIED BY timesten;
GRANT CREATE SESSION,CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;

–Cache Table User (which is the same name as the Oracle Schema user that was created earlier.)

CREATE USER oratt IDENTIFIED BY timesten;

Set the Cache Administrator username and password in the timesten database. 1.)(built in procedure to set the oracle cache administration user name and password)

% ttIsql “DSN=scottcachedb;UID=cacheuser;PWD=timesten;OraclePWD=oracle”

command> call ttCacheUidPwdSet(‘cacheuser’,’oracle’);

2.)creating a cache grid

command>  call ttGridCreate(‘myGrid’);

3.)call the ttGirdNameSet

command> call ttGridNameSet(‘myGrid’);

— Create cache groups

Create the Oracle table to be cached.

sqlplus oratt/oracle

CREATE TABLE TEST_TT(
TTID                       NUMBER ,
TTDESCRIPTION        VARCHAR2(50) ,
primary key (TTID));
–Grant Access to CACHEUSER

GRANT SELECT,INSERT,UPDATE,DELETE ON TEST_TT TO CACHEUSER;

–Create the CacheGroup

% ttIsql “DSN=scottcachedb;UID=cacheuser;PWD=timesten;OraclePWD=oracle”

command>

create dynamic asynchronous writethrough cache group ORATT.TT_CACHE
from TEST_TT(
TTID                       NUMBER ,
TTDESCRIPTION        VARCHAR2(50) ,
primary key (TTID))  AGING LRU ON;

command> call ttCacheStart;
— Start the replication agent for the AWT cache group

command> call ttRepStart

–connect with Timeten

ttisql “DSN=scottcachedb;uid=oratt;pwd=timesten;OraclePwd=oracle”