oracle 百万记录 cache,学习笔记:通过案例深入学习In-Memory Database Cache 总结配置过程...

天萃荷净

详细记录关于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 oracle研究中心;

用户已创建。

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, 'oracle研究中心');

已创建 1 行。

SQL> INSERT INTO writetab VALUES (101, 'WWW.oracleplus.net');

已创建 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.oracle研究中心';

已更新3行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;

< 1, www.oracle研究中心 >

< 2, www.oracle研究中心 >

< 3, www.oracle研究中心 >

3 rows found.

SQL> delete from readtab where keyval=3;

已删除 1 行。

SQL> commit;

提交完成。

Command> SELECT * FROM XFF.READTAB;

< 1, www.oracle研究中心 >

< 2, www.oracle研究中心 >

2 rows found.

2.测试WRITETAB表

Command> SELECT * FROM XFF.writeTAB;

< 100, oracle研究中心 >

< 101, WWW.oracleplus.net >

2 rows found.

Command> update xff.writetab set attr='www.oracleplus.net' where pk=100;

1 row updated.

Command> commit;

SQL> select * from writetab;

PK ATTR

---------- ----------------------------------------

100 www.oracleplus.net

101 WWW.oracleplus.net

Command> insert into xff.writetab values(102,'oracle研究中心');

1 row inserted.

Command> commit;

SQL> select * from writetab;

PK ATTR

---------- --------------------------------

102 oracle研究中心

100 www.oracleplus.net

101 WWW.oracleplus.net

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:通过案例深入学习In-Memory Database Cache 总结配置过程