一、主数据库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; 完毕。 |
|
来自: goodwangLib > 《ORACLE》