- 在ORACLE 中创建 timesten用户及内部表
[timesten@XAG8 conf]$ cd $TIMESTEN_HOME/install/oraclescripts
[timesten@XAG8 oraclescripts]$ pwd
/u01/timesten/tt181/install/oraclescripts
[timesten@XAG8 oraclescripts]$ ls
cacheCleanUp.sql checkRAC.sql initCacheAdminSchema.sql README.TXT
cacheInfo.sql grantCacheAdminPrivileges.sql RAC_README ttCacheADGSiteFailover.sql
[timesten@XAG8 admin]$ sqlplus sys/123456@PDB1 as sysdba
[timesten@XAG8 oraclescripts]$ sqlplus sys/123456@PDB1 as sysdba
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/MPAPEX/pdb1/system01.dbf
/u02/oradata/MPAPEX/pdb1/sysaux01.dbf
/u02/oradata/MPAPEX/pdb1/undotbs01.dbf
/u02/oradata/MPAPEX/pdb1/PDB1_UD1.dbf
/u02/oradata/MPAPEX/pdb1/PDB1_UD2.dbf
/u02/oradata/MPAPEX/pdb1/I_PDB1_UD1.dbf
/u02/oradata/MPAPEX/pdb1/I_PDB1_UD2.dbf
/u02/oradata/MPAPEX/pdb1/apex182.dbf
/u02/oradata/MPAPEX/pdb1/apex182_file.dbf
/u02/oradata/MPAPEX/pdb1/ords183.dbf
/u02/oradata/MPAPEX/pdb1/apextb.dbf
/u02/oradata/MPAPEX/pdb1/apextb2.dbf
SQL>
create tablespace cachetblsp datafile
'/u02/oradata/MPAPEX/pdb1/cachetblsp_1.dbf' size 64M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED,
'/u02/oradata/MPAPEX/pdb1/cachetblsp_2.dbf' size 64M AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
- 将oracle 下的ttuser 用户作为缓存表的宿主&创建测试用表
18.1 無默認測試表的腳本
- 创建缓存管理用户
[timesten@XAG8 oraclescripts]$ cd $TIMESTEN_HOME/install/oraclescripts
[timesten@XAG8 oraclescripts]$ sqlplus sys/123456@PDB1 as sysdba
SQL> create user cacheadm identified by oracle default tablespace cachetblsp quota unlimited on cachetblsp;
SQL> @grantCacheAdminPrivileges "cacheadm"
- 配置Timesten 缓存数据库的DSN
[timesten@XAG8 oraclescripts]$ sqlplus sys/123456@PDB1 as sysdba
SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8
[timesten@XAG8 oraclescripts]$ cd $TIMESTEN_HOME/conf
[timesten@XAG8 conf]$ pwd
/u01/timesten/tt181/conf
[timesten@XAG8 conf]$ sed -n '/\[TTDB\]/,/^$/p' sys.odbc.ini
[TTDB]
Driver=/u01/timesten/tt181/install/lib/libtten.so
DataStore=/u01/timesten/data/ttdb
LogDir=/u01/timesten/log
PermSize=128
TempSize=32
LogBufMB=64
LogBufParallelism=4
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8
PLSQL=1
OracleNetServiceName=PDB1
[timesten@XAG8 conf]$ ttisql ttdb
Command> call ttconfiguration('DataBaseCharacterSet');
< DataBaseCharacterSet, AL32UTF8 >
- 在TimesTen 数据库中创建用户(缓存管理用户cacheadm，缓存用户ttuser【已创建】)
[timesten@XAG8 conf]$ ttisql ttdb
Command> create user cacheadm identified by timesten;
Command> grant create session, cache_manager, create any table, drop any table to cacheadm;
- 在TimesTen 数据库中设置缓存管理员账户
#将oracle 数据库中缓存管理员的用户名和密码存储在timesten中
[timesten@XAG8 conf]$ ttisql "DSN=ttdb; uid = cacheadm; pwd = timesten;OraclePWD=oracle"
Command> call ttCacheUidPwdSet('cacheadm','oracle');
#确定缓存管理用户名设置
Command> call ttCacheUidGet();
< CACHEADM >
#Testing the connectivity between the TimesTen and Oracle databases
Command> passthrough 3;
Passthrough command has set autocommit off.
Command> SELECT * FROM V$VERSION;
< Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production, 0 >
< PL/SQL Release 12.2.0.1.0 - Production, 0 >
< CORE 12.2.0.1.0 Production, 0 >
< TNS for Linux: Version 12.2.0.1.0 - Production, 0 >
< NLSRTL Version 12.2.0.1.0 - Production, 0 >
Command> passthrough 0;
- 缓存与复制代理管理
# 查询代理运行状态
[timesten@XAG8 conf]$ ttAdmin -query ttdb | grep Started
Replication Manually Started : False
Cache Agent Manually Started : False
#启动缓存代理
[timesten@XAG8 conf]$ ttAdmin -cacheStart ttdb
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : True
#停缓存代理
[timesten@XAG8 conf]$ ttAdmin -cacheStop ttdb
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : False
#启动复制代理 (未定义和复制相关的缓存组，所以本次启动报错)
[timesten@XAG8 conf]$ ttAdmin -repStart ttdb
*** [TimesTen][TimesTen 11.2.2.8.47 ODBC Driver][TimesTen]TT8191:
This store (CACHEDB1_1122 on XAG816.COM) is not involved in a replication scheme
-- file "eeMisc.c", lineno 2768, procedure "sbEeGridErrorProc"
*** ODBC Error = S1000, TimesTen Error = 8191
#停复制代理
[timesten@XAG8 conf]$ ttAdmin -repStop ttdb
*** [TimesTen][TimesTen 11.2.2.8.47 ODBC Driver][TimesTen]TT12027:
The agent is already stopped for the data store.
*** ODBC Error = S1000, TimesTen Error = 12027
[timesten@XAG8 conf]$ ttAdmin -query ttdb | grep Started
Replication Manually Started : False
Cache Agent Manually Started : True
- 緩存組測試
[timesten@XAG8 conf]$ sqlplus sys/123456@PDB1 as sysdba
SQL> grant dba to ttuser;
[timesten@XAG8 conf]$ sqlplus ttuser/oracle@PDB1
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');
SQL> INSERT INTO readtab VALUES (2, 'World');
SQL> INSERT INTO writetab VALUES (100, 'TimesTen');
SQL> INSERT INTO writetab VALUES (101, 'CACHE');
SQL> COMMIT;
#将基础表的读取权限赋予oracle的缓存管理用户
GRANT SELECT ON readtab TO cacheadm;
#使用缓存管理用户登录TimesTen，启动缓存代理并建立只讀缓存组（下行表示已啟動）
[timesten@XAG8 conf]$ ttAdmin -query ttdb | grep Started
Replication Manually Started : False
Cache Agent Manually Started : True
[timesten@XAG8 ~]$ ttisql -connstr "dsn=ttdb; uid = cacheadm; pwd = timesten; oraclepwd = oracle" -e 'set prompt "CACHEADM>"'
Command>
create readonly cache group readcache
autorefresh mode incremental interval 5 seconds
state on
from ttuser.readtab
(keyval number not null primary key, str varchar2(32));
CACHEADM>cachegroups;
Cache Group CACHEADM.READCACHE:
Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds
Autorefresh Status: ok
Aging: No aging defined
Root Table: TTUSER.READTAB
Table Type: Read Only
#查询缓存表的数据
[timesten@XAG8 ~]$
ttisql -connstr "dsn=ttdb; uid = ttuser;pwd = timesten" -e 'set prompt "TTUSER>"'
TTUSER>select count(*) from readtab;
< 2 >
#验证只读缓存表的数据不允许修改
TTUSER>delete from readtab;
8225: Table JOBS is read only
The command failed.
#在oracle 端做测试数据验证
[timesten@XAG8 conf]$ sqlplus ttuser/oracle@PDB1
SQL> INSERT INTO readtab VALUES (3,'test');
SQL> commit;
TTUSER>select * from readtab;
< 1, Hello >
< 2, World >
< 3, test >
#寫測試
[timesten@XAG8 conf]$ sqlplus ttuser/oracle@PDB1
SQL> GRANT SELECT ON writetab TO cacheadm;
SQL> GRANT INSERT ON writetab TO cacheadm;
SQL> GRANT UPDATE ON writetab TO cacheadm;
SQL> GRANT DELETE ON writetab TO cacheadm;
CACHEADM>
CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP writecache
FROM ttuser.writetab
(pk NUMBER NOT NULL PRIMARY KEY, attr VARCHAR2(40));
CACHEADM> cachegroups WRITECACHE
Cache Group CACHEADM.WRITECACHE:
Cache Group Type: Asynchronous Writethrough (Dynamic)
Autorefresh: No
Aging: LRU on
Root Table: TTUSER.WRITETAB
Table Type: Propagate
[timesten@XAG8 ~]$ ttAdmin -repStart ttdb
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : True
Cache Agent Policy : manual
Cache Agent Manually Started : True
Database State : Open
[timesten@XAG8 ~]$ ttAdmin -query ttdb | grep Started
Replication Manually Started : True
Cache Agent Manually Started : True
[timesten@XAG8 ~]$
ttisql -connstr "dsn=ttdb; uid = ttuser; pwd = timesten; oraclepwd = oracle" -e 'set prompt "TTUSER>"'
TTUSER>select * from writetab;
0 rows found.
TTUSER>select * from writetab where pk=100;
< 100, TimesTen >
TTUSER>INSERT INTO writetab VALUES (102, 'Cache');
1 row inserted.
TTUSER>commit;
SQL> select * from writetab;
PK ATTR
---------- ----------------------------------------
100 TimesTen
101 CACHE
102 Cache
