美文网首页
Oracle 11.2.0.4应用缓存之四(缓存配置-动态加载只

Oracle 11.2.0.4应用缓存之四(缓存配置-动态加载只

作者: 轻飘飘D | 来源:发表于2020-02-13 11:46 被阅读0次
    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
    
    1. 将基础表的读取权限赋予oracle的缓存管理用户
    SQL> grant select on jobs to cacheadm;
    
    1. 使用缓存管理用户登录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.
    
    1. 使用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.
    
    1. 测试在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.
    
    1. 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
    
    1. 测试后删除缓存组及停缓存代理
    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
    

    相关文章

      网友评论

          本文标题:Oracle 11.2.0.4应用缓存之四(缓存配置-动态加载只

          本文链接:https://www.haomeiwen.com/subject/axdkfhtx.html