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系统上:
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>
完毕。