相关阅读
刘传龙，公众号：IT那活儿TimesTen Classic系统及其与Oracle的用户缓存部署（上篇）
1. 创建一个Oracle实例
2. 创建用户
1）oratt作为oracle的schema用户：
▼▼▼sqlplus sys/system@ordb as sysdba CREATE USER oratt IDENTIFIED BY oracle; GRANT CREATE SESSION, RESOURCE TO oratt; alter user oratt quota unlimited on users;
2）oratt作为TT 的schema用户：
▼▼▼ttisql sampledb create user oratt identified by timesten; grant create session, create table to oratt;
3）cacheadm作为TT的cache用户：
▼▼▼CREATE USER cacheadm IDENTIFIED BY timesten; GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheadm;
验证登录：
▼▼▼ttisql "uid=cacheadm;pwd=timesten;dsn=sampledb"
▼▼▼cd $TIMESTEN_HOME/install/oraclescriptssqlplus sys/system@ordb as sysdbaalter session set container=PDBORDB;CREATE TABLESPACE cachetblsp DATAFILE 'cachetblsp.dbf' SIZE 100M;CREATE USER cacheadm IDENTIFIED BY oracle DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;@grantCacheAdminPrivileges "cacheadm"SQL> SQL> @grantCacheAdminPrivileges "cacheadm"Please enter the administrator user idThe value chosen for administrator user id is cacheadm***************** Creation of TT_CACHE_ADMIN_ROLE starts ******************0. Creating TT_CACHE_ADMIN_ROLE role** Creation of TT_CACHE_ADMIN_ROLE done successfully ******************* Initialization for cache admin begins ******************0. Granting the CREATE SESSION privilege to CACHEADM1. Granting the TT_CACHE_ADMIN_ROLE to CACHEADM2. Granting the DBMS_LOCK package privilege to CACHEADM3. Granting the DBMS_DDL package privilege to CACHEADM4. Granting the DBMS_FLASHBACK package privilege to CACHEADM5. Granting the CREATE SEQUENCE privilege to CACHEADM6. Granting the CREATE CLUSTER privilege to CACHEADM7. Granting the CREATE OPERATOR privilege to CACHEADM8. Granting the CREATE INDEXTYPE privilege to CACHEADM9. Granting the CREATE TABLE privilege to CACHEADM10. Granting the CREATE PROCEDURE privilege to CACHEADM11. Granting the CREATE ANY TRIGGER privilege to CACHEADM12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEADM13. Granting the DBMS_LOB package privilege to CACHEADM14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEADM15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEADM16. Checking if the cache administrator user has permissions on the defaulttablespace Permission exists18. Granting the CREATE TYPE privilege to CACHEADM19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEADM20. Granting the SELECT on SYS.GV$SESSION privilege to CACHEADM21. Granting the SELECT on SYS.DBA_DATA_FILES privilege to CACHEADM22. Granting the SELECT on SYS.USER_USERS privilege to CACHEADM23. Granting the SELECT on SYS.USER_FREE_SPACE privilege to CACHEADM24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege to CACHEADM25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege to CACHEADM26. Granting the SELECT on SYS.V$DATABASE privilege to CACHEADM (optional)27. Granting the SELECT ANY TRANSACTION privilege to CACHEADM********* Initialization for cache admin user done successfully *********
3. 在TimesTen中缓存Oracle Cache管理员的口令
▼▼▼ttIsql "DSN=sampledb;UID=cacheadm;PWD=timesten;OraclePWD=oracle"call ttCacheUidPwdSet('cacheadm','oracle');Command> call ttCacheUidPwdSet('cacheadm','oracle'); 5220: Permanent Oracle connection failure error in OCIServerAttach: ORA-12154: TNS: ???????????? rc = -1 5935: Could not validate Oracle login: uid = CACHEADM, pwd = HIDDEN, OracleNetServiceName = pdbordb, TNS_ADMIN = "/timesten/ttuser/tt181/conf", ORACLE_HOME= "/timesten/ttuser/tt181/install/ttoracle_home/instantclient_12_1"The command failed.
注：此报错是TT的用户配置文件sys.odbc.ini中OracleNetServiceName=ordb参数与tns连接串不一致导致。
4. 在Oracle里创建基础表：
注：此步骤必须在缓存管理员用户下创建：sqlplus oratt/oracle@orclpdb
1）创建：
▼▼▼CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));CREATE TABLE writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
2）插入测试数据：
▼▼▼INSERT INTO readtab VALUES (1, 'Hello');INSERT INTO readtab VALUES (2, 'World');INSERT INTO writetab VALUES (100, 'TimesTen');INSERT INTO writetab VALUES (101, 'CACHE');COMMIT;
3）赋权给缓存管理员：
▼▼▼GRANT SELECT ON readtab TO cacheadm;GRANT SELECT ON writetab TO cacheadm;GRANT INSERT ON writetab TO cacheadm;GRANT UPDATE ON writetab TO cacheadm; GRANT DELETE ON writetab TO cacheadm;
5. 读取缓存组：
2）手动刷新数据：
3）创建只读缓存组
▼▼▼ttisql "uid=cacheadm;pwd=timesten;dsn=sampledb;oraclepwd=oracle"CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM oratt.readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));Command> CREATE READONLY CACHE GROUP readcache AUTOREFRESH INTERVAL 5 SECONDS FROM oratt.readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));Warning 5002: Unable to connect to the cache agent for /timesten/ttuser/datads/sampledb; check agent statusWarning 5051: Commit message to cache agent failed. Cache agent must be restarted
▼▼▼Command> cachegroupsCache Group CACHEADM.READCACHE: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: Paused Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: ORATT.READTAB Table Type: Read Only1 cache group found.
5）起缓存代理
▼▼▼[ttuser@timesten1 conf]$ ttAdmin -cacheStart sampledbRAM Residence Policy : manualManually Loaded In RAM : TrueReplication Agent Policy : manualReplication Manually Started : FalseCache Agent Policy : manualCache Agent Manually Started : TrueDatabase State : Open
6. 写入缓存组：
1）创建写入缓存组：
▼▼▼ttisql "uid=cacheadm;pwd=timesten;dsn=sampledb;oraclepwd=oracle"CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache FROM oratt.writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
2）起复制代理：
▼▼▼ttAdmin -repStart sampledb[ttuser@timesten1 ~]$ ttAdmin -repStart sampledbRAM Residence Policy : manualManually Loaded In RAM : TrueReplication Agent Policy : manualReplication Manually Started : TrueCache Agent Policy : manualCache Agent Manually Started : TrueDatabase State : Open
注：必须先有写缓存然后起复制代理，否则报错：
▼▼▼ttAdmin -repStart sampledb[ttuser@timesten1 ~]$ ttAdmin -repStart sampledbRAM Residence Policy : manualManually Loaded In RAM : TrueReplication Agent Policy : manualReplication Manually Started : TrueCache Agent Policy : manualCache Agent Manually Started : TrueDatabase State : Open
7. 测试读写：
1）读：
▼▼▼ttisql "uid=oratt;pwd=timesten;dsn=sampledb;oraclepwd=oracle"
Oracle插入数据：
▼▼▼sqlplus oratt/oracle@ordb INSERT INTO readtab VALUES (3, 'Welcome'); DELETE FROM readtab WHERE keyval=2; UPDATE readtab SET str='Hi' WHERE keyval=1; COMMIT;
验证：
▼▼▼Command> select * from oratt.readtab; < 1, Hello > < 2, World > 2 rows found. Command> select * from oratt.readtab; < 1, Hi > < 3, Welcome > 2 rows found. GRANT SELECT ON readtab TO cacheadm; GRANT SELECT ON writetab TO cacheadm; GRANT INSERT ON writetab TO cacheadm; GRANT UPDATE ON writetab TO cacheadm; GRANT DELETE ON writetab TO cacheadm;
2）写：
▼▼▼Command> cachegroups Cache Group CACHEADM.READCACHE: Cache Group Type: Read Only Autorefresh: Yes Autorefresh Mode: Incremental Autorefresh State: On Autorefresh Interval: 5 Seconds Autorefresh Status: ok Aging: No aging defined Root Table: ORATT.READTAB Table Type: Read Only Cache Group CACHEADM.WRITECACHE: Cache Group Type: Asynchronous Writethrough (Dynamic) ------------异步写入（动态） Autorefresh: No Aging: LRU on Root Table: ORATT.WRITETAB Table Type: Propagate 2 cache groups found. Command> 异步写入（动态） ttisql "uid=oratt;pwd=timesten;dsn=sampledb;oraclepwd=oracle" Command> SELECT * FROM writetab; 0 rows found. Command> SELECT * FROM writetab where pk=100; < 100, TimesTen >
在timesten写入数据：
▼▼▼INSERT INTO oratt.writetab VALUES (102, 'Cache'); DELETE FROM oratt.writetab WHERE pk=101; UPDATE oratt.writetab SET attr='Oracle' WHERE pk=100; COMMIT;
验证：
▼▼▼SQL> select * from writetab; Command> select * from writetab; < 100, Oracle > < 102, Cache > 2 rows found. SQL> select * from writetab; PK ATTR ---------- ---------------------------------------- 100 Oracle 102 Cache
7. 删缓存组：
停缓存代理：
更多精彩干货分享
点击下方名片关注
IT那活儿
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
