Oracle 11.2.0.4应用缓存之五(缓存配置-预加载AW

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的缓存管理用户

SQL> 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 asynchronous writethrough cache group 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)
);

CACHEADM>cachegroups awt;

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


#启动复制代理
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 count(*) from jobs;
< 0 >

CACHEADM>load cache group awt commit every 256 rows;
21 cache instances affected.

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

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

SQL> select * from jobs where job_id='A002';
JOB_ID     JOB_TITLE                   MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
A002       E2                       10000      2000

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

QL> alter table  jobs add job_name VARCHAR2(20);
Table altered.

TTUSER>insert into jobs values('A003','E3',10000,20000);

QL> select * from jobs where job_id='A003';

JOB_ID     JOB_TITLE    MIN_SALARY  MAX_SALARY       JOB_NAME
--------   -----------   ---------   ----------       --------
A003       E3            10000       20000

4.测试后清理

SQL> alter table jobs drop column job_name;

CACHEADM>call ttrepstop;

CACHEADM>drop cache group awt;

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