TimesTen缓存功能测试

2021-06-23 15:37:01

################TimesTen缓存功能测试##############

1 Oracle的Schema用户
$ sqlplus sys@prod as sysdba
CREATE USER oratt IDENTIFIED BY oracle;
GRANT CREATE SESSION, RESOURCE TO oratt;
alter user oratt quota unlimited on users;

2TimesTen端的Schema用户,与Oracle数据库端一致
$ ttisql ttdb
create user oratt identified by timesten;
grant create session, create table to oratt;

2 TimesTen端的Cache管理员
CREATE USER cacheadm IDENTIFIED BY timesten;
GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE, DROP ANY TABLE TO, ALTER ANY TABLE, DELETE ANY TABLE ,SELECT ANY TABLE to cacheadm;

ttisql "uid=oratt;pwd=timesten;dsn=ttdb"
ttisql "uid=cacheadm;pwd=timesten;dsn=ttdb"
测试连接TimesTen数据库
[timesten@rac2 conf]$ ttisql "uid=oratt;pwd=timesten;dsn=ttdb"

Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "uid=oratt;pwd=********;dsn=ttdb";
Connection successful: DSN=TTDB;UID=oratt;DataStore=/u01/database/data/ttdb;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DRIVER=/home/oracle/tt181/install/lib/libtten.so;LogBufMB=256;LogBufParallelism=8;LogDir=/u01/database/log;PermSize=512;TempSize=64;OracleNetServiceName=prod;
(Default setting AutoCommit=1)
Command> exit
Disconnecting...
Done.
[timesten@rac2 conf]$ ttisql "uid=cacheadm;pwd=timesten;dsn=ttdb"
Copyright (c) 1996, 2021, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
connect "uid=cacheadm;pwd=********;dsn=ttdb";
Connection successful: DSN=TTDB;UID=cacheadm;DataStore=/u01/database/data/ttdb;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DRIVER=/home/oracle/tt181/install/lib/libtten.so;LogBufMB=256;LogBufParallelism=8;LogDir=/u01/database/log;PermSize=512;TempSize=64;OracleNetServiceName=prod;
(Default setting AutoCommit=1)
Command>

3 Oracle端的Cache管理员
此管理员用户名为cacheadm, 口令为oracle。该用户名与Timeten端cache管理员相同
先切换到指定目录，因为此目录下有需执行的SQL脚本：

cd $TIMESTEN_HOME/install/oraclescripts
sqlplus sys@prod as sysdba

执行以下SQL：
CREATE TABLESPACE cachetblsp DATAFILE 'cachetblsp.dbf' SIZE 100M;

CREATE USER cacheadm IDENTIFIED BY oracle DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;

@grantCacheAdminPrivileges "cacheadm"

4在TimesTen中缓存Oracle Cache管理员的口令
使用timesten cache管理员登录：
这个密码是Cache Agent后台用的，执行如autorefresh这样的操作，就得预先用ttCacheUidPwdSet存起来。
$ ttIsql "DSN=ttdb;UID=cacheadm;PWD=timesten;OraclePWD=oracle" <<<<<这里有两个密码，一个是TT端cacheadm,一个是Oracle数据库端cacheadm
call ttCacheUidPwdSet('cacheadm','oracle'); <<<<存储Oracle数据库端cacheadm的密码

注意：DNS文件中OracleNetServiceName的设置，必须与tnsnames.ora中保持一致，也就是这个到Oracle的自动任务需要使用网络连接，这个配置要一致才能找到
Oracle数据库，前面cacheadm和其密码都存好了，也就可以自动执行一些数据维护任务，比如自动定时刷新数据。

5缓存功能测试
（1）在Oracle库中创建测试表(基表）
$ sqlplus oratt/oracle@prod
CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));

插入数据：
INSERT INTO readtab VALUES (1, 'Hello');
COMMIT;

（2）赋权给缓存管理员cacheadm，该用户对表readtab有select对象权限，对writetab有select,insert,update,delete对象权限。

GRANT SELECT ON readtab TO cacheadm;

（3）启动缓存代理
$ ttAdmin -cacheStart ttdb
从ttstatus可以查看当前缓存代理进程，截取ttstatus的部分信息
Type PID Context Connection Name ConnID
Cache Agent 5991 0x0000000001251e10 Marker(140183514806016) 6
Cache Agent 5991 0x00007f7eb00a4ae0 LogSpaceMon(140183516911360) 7
Cache Agent 5991 0x00007f7eb801ec70 Timer 3
Cache Agent 5991 0x00007f7ebc0c8820 Refresher(S,5000) 4
Cache Agent 5991 0x00007f7ebc1fa330 BMReporter(140183512700672) 5
Cache Agent 5991 0x00007f7ec004a1f0 Refresher(S,5000)(140183510595 1
Cache Agent 5991 0x00007f7f0807e9d0 Handler 2

$ ttisql "uid=cacheadm;pwd=timesten;dsn=ttdb;oraclepwd=oracle"
CREATE READONLY CACHE GROUP readcache <<<<<<创建只读缓存组
AUTOREFRESH INTERVAL 5 SECONDS <<<<<<5秒自动刷新一次
FROM oratt.readtab <<<<<<刷新对象oratt.readtab表，这里页定义了该表
(keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));

缓存组信息如下：
Command> cachegroup

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

(4) 刷新操作，Oracle与TimesTen数据一致：

$ ttisql "uid=cacheadm;pwd=timesten;dsn=ttdb;oraclepwd=oracle"
LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
Oracle端查询
SQL> select * from oratt.readtab;

KEYVAL STR
---------- ----------------------------------------------------------------
2 World
1 Hello
3 Hallo
4 Four
5 Five
6 Six

6 rows selected.

TimesTen端查询
Command> select * from readtab;
< 1, Hello >
< 2, World >
< 3, Hallo >
< 4, Four >
< 5, Five >
< 6, Six >
6 rows found.

目前Oracle数据库端与TimesTen端数据同步，后续当Oracle数据库端表readtab数据变更，则TimesTen端数据自动安装同步。