TimesTen Classic系统及其与Oracle的用户缓存部署（下篇）

2022-04-07 00:00:00 创建 用户 缓存 写入 代理

相关阅读

刘传龙，公众号：IT那活儿TimesTen Classic系统及其与Oracle的用户缓存部署（上篇）


二. 配置Oracle与timesten的缓存


1. 创建一个Oracle实例

Oracle实例可以按照自己的需求准备，可以是rac也可以是单节点，若用Oracle11rac可参考《Oracle11G-rac-asm-双节点部署-刘传龙》。


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"
4）Oracle创建cacheadm作为cache用户管理员：
注：需要先进入指定用户然后执行脚本：
▼▼▼
cd $TIMESTEN_HOME/install/oraclescripts
sqlplus sys/system@ordb as sysdba
alter 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 id
The 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 CACHEADM
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEADM
2. Granting the DBMS_LOCK package privilege to CACHEADM
3. Granting the DBMS_DDL package privilege to CACHEADM
4. Granting the DBMS_FLASHBACK package privilege to CACHEADM
5. Granting the CREATE SEQUENCE privilege to CACHEADM
6. Granting the CREATE CLUSTER privilege to CACHEADM
7. Granting the CREATE OPERATOR privilege to CACHEADM
8. Granting the CREATE INDEXTYPE privilege to CACHEADM
9. Granting the CREATE TABLE privilege to CACHEADM
10. Granting the CREATE PROCEDURE privilege to CACHEADM
11. Granting the CREATE ANY TRIGGER privilege to CACHEADM
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEADM
13. Granting the DBMS_LOB package privilege to CACHEADM
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEADM
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEADM
16. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
18. Granting the CREATE TYPE privilege to CACHEADM
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEADM
20. Granting the SELECT on SYS.GV$SESSION privilege to CACHEADM
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege to CACHEADM
22. Granting the SELECT on SYS.USER_USERS privilege to CACHEADM
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege to CACHEADM
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege to CACHEADM
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege to CACHEADM
26. 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. 读取缓存组：

1）起读缓存代理：
ttAdmin -cacheStart sampledb

2）手动刷新数据：

LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;

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 status
Warning 5051: Commit message to cache agent failed. Cache agent must be restarted
4）验证：
▼▼▼
Command> cachegroups

Cache 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 Only

1 cache group found.

5）起缓存代理

▼▼▼
[ttuser@timesten1 conf]$ ttAdmin -cacheStart sampledb
RAM Residence Policy : manual
Manually Loaded In RAM : True
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy              : manual
Cache Agent Manually Started : True
Database 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 sampledb
RAM Residence Policy : manual
Manually Loaded In RAM : True
Replication Agent Policy : manual
Replication Manually Started : True
Cache Agent Policy              : manual
Cache Agent Manually Started : True
Database State : Open

注：必须先有写缓存然后起复制代理，否则报错：

▼▼▼
ttAdmin -repStart sampledb

[ttuser@timesten1 ~]$ ttAdmin -repStart sampledb
RAM Residence Policy : manual
Manually Loaded In RAM : True
Replication Agent Policy : manual
Replication Manually Started : True
Cache Agent Policy              : manual
Cache Agent Manually Started : True
Database 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. 删缓存组：

停缓存代理：

call ttcachestop;
删除：
例子：drop cache group CACHEADM.READCACHE

END



相关文章