In-Memory Database Cache 入门配置

一、Oracle数据库创建相关用户和权限
1.创建timesten用户
store information about cache grids

SQL> CREATE TABLESPACE cachetblsp DATAFILE
  2  'E:\ORACLE\ORADATA\XFF\datfttuser.dbf'
  3  SIZE 10M autoextend on next 10m maxsize 30g;

表空间已创建。

SQL> @E:\oracle\timesten\oraclescripts\initCacheGlobalSchema "cachetblsp"

Please enter the tablespace where TIMESTEN user is to be created
The value chosen for tablespace is cachetblsp

******* Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE starts *******
1. Creating TIMESTEN schema
2. Creating TIMESTEN.TT_GRIDID table
3. Creating TIMESTEN.TT_GRIDINFO table
4. Creating TT_CACHE_ADMIN_ROLE role
5. Granting privileges to TT_CACHE_ADMIN_ROLE
** Creation of TIMESTEN schema and TT_CACHE_ADMIN_ROLE done successfully **

PL/SQL 过程已成功完成。

2.创建测试用户
the Oracle tables to be cached in a TimesTen database

SQL> create user xff identified by xifenfei;

用户已创建。

SQL> grant create session,resource to xff;

授权成功。

3.创建cache管理用户
creates and maintains Oracle objects that store information used to manage cache grids and enforce predefined behaviors of particular cache group types.

SQL> CREATE USER cacheuser IDENTIFIED BY oracle
  2  DEFAULT TABLESPACE cachetblsp QUOTA UNLIMITED ON cachetblsp;

用户已创建。

SQL>  @grantCacheAdminPrivileges "cacheuser"

Please enter the administrator user id
The value chosen for administrator user id is cacheuser

***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEUSER
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEUSER
2. Granting the DBMS_LOCK package privilege to CACHEUSER
3. Granting the RESOURCE  privilege to CACHEUSER
4. Granting the CREATE PROCEDURE  privilege to CACHEUSER
5. Granting the CREATE ANY TRIGGER  privilege to CACHEUSER
6. Granting the DBMS_LOB package privilege to CACHEUSER
7. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER
8. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER
9. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
11. Granting the CREATE ANY TYPE privilege to CACHEUSER
********* Initialization for cache admin user done successfully *********

二、TimesTen创建相关用户
1.cache管理用户
A cache manager user performs cache grid and cache group operations. The TimesTen cache manager user must have the same name as an Oracle user that can access the cached Oracle tables.

Command>  CREATE USER cacheuser IDENTIFIED BY timesten;

User created.

Command> GRANT CREATE SESSION, CACHE_MANAGER, CREATE ANY TABLE TO cacheuser;

2.cache对应用户
You must create a TimesTen cache table user with the same name as an Oracle schema user for each schema user who owns or will own Oracle tables to be cached in the TimesTen database

Command> create user xff identified by timesten;

User created.

三、配置DSN

Data Store Path + Name: E:\oracle\timesten\mytt_db\data
Permanent Data Size: 64
Oracle Net Service Name: XFF
Database Character Set: ZHS16GBK

四、在TT中设置cache管理用户名和密码

ttIsql "DSN=my_ttdb;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheuser','oracle');
Warning  5183: Function kollglid2 was not in the library. The function will not
be called.
Warning  5183: Function kollgsnp2 was not in the library. The function will not
be called.
Warning  5186: The OCI client library in use does not contain required routines
to support caching LOBS from Oracle.

--第一次调用相关函数因为没有编译出错，再次调用即可
Command> call ttCacheUidPwdSet('cacheuser','oracle');

五、创建测试表（Oracle db中）

SQL>  CREATE TABLE readtab (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));

表已创建。

SQL> CREATE TABLE writetab (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));

表已创建。

SQL> INSERT INTO readtab VALUES (1, 'Hello');

已创建 1 行。

SQL> INSERT INTO readtab VALUES (2, 'World');

已创建 1 行。

SQL> INSERT INTO writetab VALUES (100, 'XIFENFEI');

已创建 1 行。

SQL> INSERT INTO writetab VALUES (101, 'WWW.XIFENFEI.COM');

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> GRANT SELECT ON readtab TO cacheuser;

授权成功。

SQL> GRANT SELECT ON writetab TO cacheuser;

授权成功。

SQL> GRANT INSERT ON writetab TO cacheuser;

授权成功。

SQL> GRANT UPDATE ON writetab TO cacheuser;

授权成功。

SQL>  GRANT DELETE ON writetab TO cacheuser;

授权成功。

六、TimesTen相关配置
1.创建cache grid

Command> call ttGridCreate('myGrid');
Command>  call ttGridNameSet('myGrid');

2.Start the cache agent

Command> call ttCacheStart;

3.创建cache group

Command> call ttCacheStart;
Command> CREATE READONLY CACHE GROUP readcache
       > AUTOREFRESH INTERVAL 5 SECONDS
       > FROM XFF.readtab
       > (keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
Command> CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH  CACHE GROUP writecache
       > FROM XFF.writetab
       > (pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));

Command> cachegroups;

Cache Group CACHEUSER.READCACHE:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: Paused
  Autorefresh Interval: 5 Seconds
  Autorefresh Status: ok
  Aging: No aging defined

  Root Table: XFF.READTAB
  Table Type: Read Only

Cache Group CACHEUSER.WRITECACHE:

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

  Root Table: XFF.WRITETAB
  Table Type: Propagate

2 cache groups found.

4.Start the replication agent for the AWT cache group

Command> call ttRepStart;

5.Manually load the cache group

Command>  LOAD CACHE GROUP readcache COMMIT EVERY 256 ROWS;
2 cache instances affected.
Command> LOAD CACHE GROUP writecache COMMIT EVERY 256 ROWS;
2 cache instances affected.

七、TT授权
主要是为了直接在cacheuser中操作方便，无其他应意义

ttisql my_ttdb
Command> GRANT SELECT ON xff.readtab TO cacheuser;
Command> GRANT SELECT ON xff.writetab TO cacheuser;
Command> GRANT UPDATE ON xff.writetab TO cacheuser;
Command> GRANT DELETE ON xff.writetab TO cacheuser;
Command> GRANT INSERT ON xff.writetab TO cacheuser;

八、相关测试
1.测试READTAB表

Command> SELECT * FROM XFF.READTAB;
< 1, Hello >
< 2, World >
2 rows found.

SQL> INSERT INTO readtab VALUES (3, 'Welcome');

已创建 1 行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;
< 1, Hello >
< 2, World >
< 3, Welcome >
3 rows found.

SQL> update readtab set str='www.xifenfei';

已更新3行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;
< 1, www.xifenfei >
< 2, www.xifenfei >
< 3, www.xifenfei >
3 rows found.

SQL> delete from readtab where keyval=3;

已删除 1 行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;
< 1, www.xifenfei >
< 2, www.xifenfei >
2 rows found.

2.测试WRITETAB表

Command> SELECT * FROM XFF.writeTAB;
< 100, XIFENFEI >
< 101, WWW.XIFENFEI.COM >
2 rows found.
Command> update xff.writetab set attr='www.xifenfei.com' where pk=100;
1 row updated.
Command> commit;

SQL> select * from writetab;

        PK ATTR
---------- ----------------------------------------
       100 www.xifenfei.com
       101 WWW.XIFENFEI.COM

Command> insert into xff.writetab values(102,'xifenfei');
1 row inserted.
Command> commit;

SQL> select * from writetab;

        PK ATTR
---------- --------------------------------
       102 xifenfei
       100 www.xifenfei.com
       101 WWW.XIFENFEI.COM

相关文档