内存数据库（TimesTen）cachegroup配置指南

TimesTen cachegroup的实现及注意事项

一、主数据库oracle和内存数据库TimesTen的配置

主数据库oracle系统：

以root用户登录，然后切到oracle用户：su - oracle

$ sqlplus sys as sysdba  （oracle系统管理用户）

 

SQL*Plus: Release11.2.0.3.0 Production on Fri Apr 13 14:05:58 2012

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

Enter password:(password)

Connected to:

Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With thePartitioning, Automatic Storage Management, OLAP, Data Mining

and RealApplication Testing options

SQL> CREATE TABLESPACE cache_noc DATAFILE'/oradata/noctest/cache_noc.dbf' SIZE 100M;

SQL> @initCacheGlobalSchema "cache_noc"

Please enter thetablespace where TIMESTEN user is to be created

The value chosenfor tablespace is cache_noc

 

******* Creationof TIMESTEN schema and TT_CACHE_ADMIN_ROLE starts *******

1. CreatingTIMESTEN schema

2. CreatingTIMESTEN.TT_GRIDID table

3. CreatingTIMESTEN.TT_GRIDINFO table

4. CreatingTT_CACHE_ADMIN_ROLE role

5. Grantingprivileges to TT_CACHE_ADMIN_ROLE

** Creation ofTIMESTEN schema and TT_CACHE_ADMIN_ROLE done successfully **

PL/SQL proceduresuccessfully completed.

SQL> CREATE USER pin74 IDENTIFIED BYoracle;   //若pin74用户已存在，此步骤就不需要了

SQL> GRANT CREATE SESSION, RESOURCE TO pin74;

Grant succeeded.

SQL> CREATE USER cache_user IDENTIFIED BYoracle DEFAULTTABLESPACEcache_noc QUOTAUNLIMITED ONcache_noc;

User created.

SQL> @grantCacheAdminPrivileges "cache_user";

Please enter theadministrator user id

The value chosenfor administrator user id is cache_user

*****************Initialization for cache admin begins ******************

0. Granting theCREATE SESSION privilege to CACHE_USER

1. Granting theTT_CACHE_ADMIN_ROLE to CACHE_USER

2. Granting theDBMS_LOCK package privilege to CACHE_USER

3. Granting theRESOURCE  privilege to CACHE_USER

4. Granting theCREATE PROCEDURE  privilege to CACHE_USER

5. Granting theCREATE ANY TRIGGER  privilege toCACHE_USER

6. Granting theDBMS_LOB package privilege to CACHE_USER

7. Granting theSELECT on SYS.ALL_OBJECTS privilege to CACHE_USER

8. Granting theSELECT on SYS.ALL_SYNONYMS privilege to CACHE_USER

9. Checking if thecache administrator user has permissions on the default

tablespace

     Permission exists

11. Granting theCREATE ANY TYPE privilege to CACHE_USER

*********Initialization for cache admin user done successfully *********

SQL> exit;

(注：initCacheGlobalSchema.sql、grantCacheAdminPrivileges.sql为TT环境TimesTen_install_dir/oraclescripts下的脚本。如果oracle和TimesTen在同一个系统下直接在

TimesTen_install_dir/目录下执行上面的操作就行了；不在同一个系统的话需要把这两个脚本拷到oracle系统上面(当然别忘了更改这两个文件的权限)，在此目录下执行上面的

操作。)

内存数据库TimesTen系统上：

1、TimesTen用户的环境变量配置，主要是ORACLE_HOME和TNS_ADMIN的配置。(注：其中TNS_ADMIN的值(最好)用ttmodinstall工具来调整，调整过之后再在.profile配置文件中调整成一致。)

2、创建DSN：编辑TimesTen_install_dir/info/目录下sys.odbc.ini文件

增加以下语句：

[tt_cachegroup]

Driver=/opt/TimesTen/tt1122_64/lib/libtten.so

DataStore=/opt/TimesTen/tt1122_64/info/tt_cachegroup

DatabaseCharacterSet=UTF8

PermSize=100

TempSize=32

CkptFrequency=120

CkptLogVolume=0

PLSQL=1

OracleNetServiceName=noctest

(注：DatabaseCharacterSet的值一定要与oracle的一致，若oracle的为AL32UTF8则这里也应设为AL32UTF8)

 

3、由于我们是远程cachegroup，所以还要配TimesTen_install_dir/network/admin/ 下tnsnames.ora配置文件中远程连接属性。

####################################

noctest =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST =10.xx.xx.xx)(PORT = xxxx))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cdb)

    )

  ) 

##################################

(注：noctest的取名任意都可，只要在客户端连接时一致即可(sqlplus pin74/pin74@noctest)；cdb是服务器名，不可自己取名。)

配置以上后，开始进行内存数据库TimesTen数据用户的操作：

$ ttisql tt_cachegroup

Copyright (c)1996-2011, Oracle.  All rights reserved.

Type ? or"help" for help, type "exit" to quit ttIsql

connect "DSN=tt_cachegroup";

Connectionsuccessful:

DSN=tt_cachegroup;UID=ttadmin;DataStore=/opt/TimesTen/tt1122_64/info/tt_cachegroup;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/TimesTen/tt1122_64/lib/libtten.so;PermSize=100;TempSize=32;CkptFrequency=120;CkptLogVolume=0;TypeMode=0;OracleNetServiceName=noctest;

(Default settingAutoCommit=1)

Command> create user cache_user identified bytimesten;

User created.

Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATEANY TABLE TOcache_user;

Command> create user pin74 identified bytimesten;

User created.

Command> grant create session,create any table to pin74;

Command> exit

Disconnecting...

Done.

$ ttisql "dsn=tt_cachegroup;uid=cache_user;pwd=timesten;oraclepwd=oracle"

Copyright (c)1996-2011, Oracle.  All rights reserved.

Type ? or"help" for help, type "exit" to quit ttIsql.

connect"dsn=tt_cachegroup;uid=cache_user;pwd=timesten;oraclepwd=oracle";

Connectionsuccessful:

DSN=tt_cachegroup;UID=cache_user;DataStore=/opt/TimesTen/tt1122_64/info/tt_cachegroup;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/T

imesTen/tt1122_64/lib/libtten.so;PermSize=100;TempSize=32;CkptFrequency=120;CkptLogVolume=0;TypeMode=0;OracleNetServiceName=noctest;

(Default settingAutoCommit=1)

Command> call ttcacheuidpwdset('cache_user','oracle');

Command> call ttgridcreate('mygrid');

Command> call ttgridnameset('mygrid');

Command> call ttCacheStart;                //(注：此步现在也可不做，待开始cache时也不耽误。)

(注：TimesTen用户名必须和oracle的一致，cache_user、pin74。否则无法cache。)

二、配置完毕后开始进行cache：

主数据库oracle系统上：

$ sqlplus pin74/pin74    (或者先进入sys用户再conn pin74/pin74也可)

SQL> CREATE TABLE testcache  (keyvalNUMBER NOT NULL PRIMARY KEY, strVARCHAR2(32));

Table created.

SQL> INSERT INTO testcache VALUES (1, 'Hello');

1 row created.

SQL> INSERT INTO testcache VALUES (2, 'Word');

1 row created.

SQL> COMMIT;               //(注：此句很重要，不然会很麻烦。曾经我忘了这句，结果cachegroup到TT上时竟然没有数据，找了好久没找到原因...)

Commit complete.

SQL> SELECT * FROM testcache;

   KEYVAL STR

------------------------------------------

        1 Hello

        2 Word

SQL> GRANT ALL ON testcache TO cache_user;

 

内存数据库TimesTen系统上：

$ ttisql "dsn=tt_cachegroup;uid=cache_user;pwd=timesten;oraclepwd=oracle";

 

Copyright(c)1996-2011, Oracle.  All rights reserved.

Type ?or"help" for help, type "exit" to quit ttIsql.

 

connect"dsn=tt_cachegroup;uid=cache_user;pwd=timesten;oraclepwd=oracle";

Connectionsuccessful:

DSN=tt_cachegroup;UID=cache_user;DataStore=/opt/TimesTen/tt1122_64/info/tt_cachegroup;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/T

imesTen/tt1122_64/lib/libtten.so;PermSize=100;TempSize=32;CkptFrequency=120;CkptLogVolume=0;TypeMode=0;OracleNetServiceName=noctest;

(DefaultsettingAutoCommit=1)

Command> CREATE READONLY CACHE GROUP testcachegroup

       >AUTOREFRESHINTERVAL 5 SECONDS

       > FROM pin74.testcache

       >(keyval NUMBERNOT NULL PRIMARY KEY, str VARCHAR2(32));

（注：主库多表cachegroup：http://blog.csdn.net/lyn_bigdream/article/details/7513218）

Command> cachegroups

CacheGroupCACHE_USER.TESTCACHEGROUP:

 

  Cache GroupType: Read Only

 Autorefresh: Yes

  AutorefreshMode: Incremental

  AutorefreshState: Paused

  AutorefreshInterval: 5 Seconds

  AutorefreshStatus: ok

  Aging: Noaging defined

 

  Root Table: PIN74.TESTCACHE

  Table Type:Read Only

 

1 cachegroupsfound.

Command> load cache group TESTCACHEGROUP commitevery 10rows;            //(注：此步忘记的话TT里也没有数据，即没有数据更新。)

2 cacheinstancesaffected.

Command> exit;

Disconnecting...

Done.

$ttisql"dsn=tt_cachegroup;uid=pin74;pwd=timesten"

Copyright(c)1996-2011, Oracle.  All rights reserved.

Type ?or"help" for help, type "exit" to quit ttIsql.

connect"dsn=tt_cachegroup;uid=pin74;pwd=timesten";

Connectionsuccessful:

DSN=tt_cachegroup;UID=pin74;DataStore=/opt/TimesTen/tt1122_64/info/tt_cachegroup;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/TimesT

en/tt1122_64/lib/libtten.so;PermSize=100;TempSize=32;CkptFrequency=120;CkptLogVolume=0;TypeMode=0;OracleNetServiceName=noctest;

(DefaultsettingAutoCommit=1)

Command> tables;

 PIN74.TESTCACHE

1 table found.

Command>select* from testcache;

< 1, Hello >

< 2, Word >

2 rows found.

 

删除cachegroup：

Command> drop cache group CACHE_SOL_SERVICE;
15100: User CACHE_USER lacks privilege DROP ANY TABLE//没有权限
The command failed.
Command> quit
Disconnecting...
Done.
$ ttisql tt_cachegroup
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "DSN=tt_cachegroup";
Connection successful: DSN=tt_cachegroup;UID=ttadmin;DataStore=/opt/TimesTen/tt1122_64/info/my_datastore/tt_cachegroup;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/TimesTen/tt1122_64/lib/libtten.so;PermSize=3072;TempSize=32;CkptFrequency=120;CkptLogVolume=0;TypeMode=0;OracleNetServiceName=noctest;
(Default setting AutoCommit=1)
Command> grant drop any table to cache_user;
Command> exit;
Disconnecting...
Done.
$ ttisql "dsn=tt_cachegroup;uid=cache_user;pwd=timesten;oraclepwd=oracle"
Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "dsn=tt_cachegroup;uid=cache_user;pwd=timesten;oraclepwd=oracle";
Connection successful: DSN=tt_cachegroup;UID=cache_user;DataStore=/opt/TimesTen/tt1122_64/info/my_datastore/tt_cachegroup;DatabaseCharacterSet=UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/TimesTen/tt1122_64/lib/libtten.so;PermSize=3072;TempSize=32;CkptFrequency=120;CkptLogVolume=0;TypeMode=0;OracleNetServiceName=noctest;
(Default setting AutoCommit=1)
Command> drop cache group CACHE_SOL_SERVICE;
Command> 

完毕。