- 确认oracle中基础表jobs
[timesten@XAG816 ~]$ sqlplus ttuser/oracle@DG
SQL> desc jobs;
Name Null? Type
----------------------------------------- -------- ----------------------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
SQL> select count(1) from jobs;
COUNT(1)
----------
19
- 将基础表的读取权限赋予oracle的缓存管理用户
SQL> grant select on jobs to cacheadm;
- 使用缓存管理用户登录TimesTen，启动缓存代理并建立缓存组
[timesten@XAG816 oraclescripts]$
ttisql -connstr "dsn=cachedb1_1122; uid = cacheadm; pwd = timesten; oraclepwd = oracle" -e 'set prompt "CACHEADM>"'
#启动缓存代理
CACHEADM>call ttcachestart;
[timesten@XAG816 ~]$ ttAdmin -query cachedb1_1122 | grep Started
Replication Manually Started : False
Cache Agent Manually Started : True
#建立缓存组
CACHEADM>
create dynamic readonly cache group "d_ro"
autorefresh mode incremental interval 5 seconds
state on
from
"ttuser"."jobs"
(
"job_id" VARCHAR2(10 byte) not null,
"job_title" VARCHAR2(35 byte) not null,
"min_salary" NUMBER(6),
"max_salary" NUMBER(6),
primary key("job_id")
)
aging lru on;
CACHEADM>cachegroups d_ro;
-------------------------------------------------
Cache Group CACHEADM.D_RO:
Cache Group Type: Read Only (Dynamic)
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: LRU on
Root Table: TTUSER.JOBS
Table Type: Read Only
1 cache group found.
-----------------------------------------------
#查询缓存表的数据
[timesten@XAG816 ~]$
ttisql -connstr "dsn=cachedb1_1122; uid = ttuser;pwd = timesten" -e 'set prompt "TTUSER>"'
#由于需要动态加载，缓存表最初没有数据
TTUSER>select count(1) from jobs;
< 0 >
1 row found.
#验证只读缓存表的数据不允许修改
TTUSER>delete from jobs;
8225: Table JOBS is read only
The command failed.
#使用带主键的等值查询时，自动加载缓存数据
TTUSER>select * from jobs where job_id = 'FI_MGR';
15022: OraclePwd connection attribute needs to be specified and has to be non-empty for using TimesTen Cache features
5109: Cache Connect general error: BDB connection not open.
0 rows found.
The command failed.
TTUSER>exit
[timesten@XAG816 ~]$ ttisql -connstr "dsn=cachedb1_1122; uid = ttuser;pwd = timesten; oraclepwd=oracle" -e 'set prompt "TTUSER>"'
TTUSER>select * from jobs where job_id = 'FI_MGR';
< FI_MGR, Finance Manager, 8200, 16000 >
1 row found.
#不带主键时，不会引发缓存数据的自动加载
TTUSER>select * from JOBS t where min_salary=3000;
0 rows found.
- 使用Load 和 Unload 命令手动加载和卸载缓存数据
CACHEADM>load cache group d_ro where min_salary > 10000 commit every 256 rows;
2 cache instances affected.
TTUSER>select * from jobs;
< AD_PRES, President, 20000, 40000 >
< AD_VP, Administration Vice President, 15000, 30000 >
< FI_MGR, Finance Manager, 8200, 16000 >
3 rows found.
CACHEADM>unload cache group d_ro where min_salary > 10000;
2 cache instances affected.
CACHEADM>load cache group d_ro commit every 256 rows;
18 cache instances affected.
CACHEADM>unload cache group d_ro;
19 cache instances affected.
TTUSER>select count(1) from jobs;
< 0 >
1 row found.
- 测试在Oracle 端插入数据(数据不会自动同步到 TimesTen端)
[timesten@XAG816 ~]$ sqlplus ttuser/oracle@DG
SQL> insert into jobs values('SA_EA','Enterprise Architect',10000,20000);
SQL> commit;
TTUSER>sleep 5;
TTUSER>select count(1) from jobs;
< 0 >
1 row found.
CACHEADM>alter cache group d_ro set autorefresh state paused;
CACHEADM>refresh cache group d_ro commit every 256 rows;
0 cache instances affected.
TTUSER>select count(1) from jobs;
< 0 >
1 row found.
- Load操作可以将oracle数据库中的新增数据同步到TimesTen
CACHEADM>load cache group d_ro where job_title='Enterprise Architect' commit every 256 rows;
1 cache instance affected.
TTUSER>select * from jobs;
< SA_EA, Enterprise Architect, 10000, 20000 >
1 row found.
#测试update 操作，先将刷新周期设为 1 小时=暂停自动刷新
CACHEADM>alter cache group d_ro set autorefresh interval 3600 seconds;
#在oracle 端更新数据，由于Load操作只用于缓存组中没有的缓存数据，所以update 不会引发同步
SQL> update jobs set min_salary=12000 where job_id='SA_EA';
SQL> commit;
CACHEADM>load cache group d_ro where job_id='SA_EA' commit every 256 rows;
0 cache instances affected.
TTUSER>select * from jobs;
< SA_EA, Enterprise Architect, 10000, 20000 >
1 row found.
TTUSER>select * from jobs where job_id = 'SA_EA';
< SA_EA, Enterprise Architect, 10000, 20000 >
1 row found.
#此时可以通过手动Refresh操作同步数据
CACHEADM>alter cache group d_ro set autorefresh state paused;
CACHEADM>refresh cache group d_ro commit every 256 rows;
1 cache instance affected.
TTUSER>select * from jobs;
< SA_EA, Enterprise Architect, 12000, 20000 >
1 row found.
CACHEADM>cachegroups d_ro; # refresh 后 state 自动变成 On
---------------------------------------------------------
Cache Group CACHEADM.D_RO:
Cache Group Type: Read Only (Dynamic)
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: LRU on
Root Table: TTUSER.JOBS
Table Type: Read Only
1 cache group found.
-------------------------------------------
#通过自动刷新实现数据同步
CACHEADM>alter cache group d_ro set autorefresh interval 5 seconds;
SQL> update jobs set min_salary=13000 where job_id='SA_EA';
SQL> commit;
TTUSER>sleep 5;
TTUSER>select * from jobs;
< SA_EA, Enterprise Architect, 13000, 20000 >
1 row found.
SQL> delete from jobs where job_id='SA_EA';
SQL> commit;
TTUSER>sleep 5;
TTUSER>select * from jobs;
0 rows found
- 测试后删除缓存组及停缓存代理
CACHEADM>drop cache group d_ro;
CACHEADM>call ttcachestop;
CACHEADM>exit
[timesten@XAG816 ~]$ ttAdmin -query cachedb1_1122 | grep Started
Replication Manually Started : False
Cache Agent Manually Started : False
