Oracle 11.2.0.4应用缓存之六(缓存配置-动态加载A

1.确认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

2. 将基础表的读写权限赋予oracle的缓存管理用户

grant select,insert,update,delete  on jobs to cacheadm;

3.使用缓存管理用户登录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 asynchronous writethrough cache group d_awt
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_awt;

Cache Group CACHEADM.D_AWT:

  Cache Group Type: Asynchronous Writethrough (Dynamic)
  Autorefresh: No
  Aging: LRU on

  Root Table: TTUSER.JOBS
  Table Type: Propagate

1 cache group found.

Cache Group CACHEADM.AWT:

  Cache Group Type: Asynchronous Writethrough
  Autorefresh: No
  Aging: No aging defined

  Root Table: TTUSER.JOBS
  Table Type: Propagate

1 cache group found.

CACHEADM>repschemes

Replication Scheme TTREP._AWTREPSCHEME:

  Element: _1798032                       
  Type: Table TTUSER.JOBS
  Master Store: CACHEDB1_1122 on XAG816.COM Transmit Durable
  Subscriber Store: _ORACLE from XAG816.COM 

  Store: CACHEDB1_1122 on XAG816.COM
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled

  Store: _ORACLE from XAG816.COM
    Port: (auto)
    Log Fail Threshold: (none)
    Retry Timeout: 120 seconds
    Compress Traffic: Disabled

1 replication scheme found.

#启动复制代理
CACHEADM> call ttrepstart;

[timesten@XAG816 ~]$  ttAdmin -query cachedb1_1122 | grep Started
Replication Manually Started    : True
Cache Agent Manually Started    : True

#查询缓存表的数据
[timesten@XAG816 ~]$ 
ttisql -connstr "dsn=cachedb1_1122; uid = ttuser;pwd = timesten" -e 'set prompt "TTUSER>"'

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.


[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 >

TTUSER>select count(1) from jobs;
< 1 >

CACHEADM>load cache group d_awt commit every 256 rows;

TTUSER>select count(*) from jobs;
< 19 >

TTUSER>insert into jobs values('A001','E1',10000,20000);
1 row inserted.

SQL> select * from jobs where job_id='A001';
JOB_ID     JOB_TITLE                   MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
A001       E1                       10000      20000

TTUSER>alter table  jobs add job_name VARCHAR2(20);
 5026: Cannot alter cached tables

TTUSER>update jobs set min_salary = 8888 where job_id='A001';

SQL> select * from jobs where job_id='A001';
JOB_ID     JOB_TITLE                   MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
A001       E1                        8888      20000

4. 测试后清理

CACHEADM>call ttrepstop;

CACHEADM>drop cache group d_awt;

TTUSER>select * from jobs;
 2206: Table TTUSER.JOBS not found