本文主要講述如何實現Timesten與Oracle的同步
要實現與Oracle的同步，需要先在Oracle中創建一個表空間和一個用戶
1、創建表空間
這個表空間將被timesten user和cache administration user使用，該表空間應該只用於存儲cache相關的對象，而不要與其他應用共享。
CREATE TABLESPACE TIMESTEN DATAFILE '/u01/app/oradata/timesten/tbs_timesten.dbf' SIZE 300M;
2、創建Timesten用戶
需要執行initCacheGlobalSchema.sql腳本，並將剛剛創建的表空間名傳遞進來。該腳本位於%TimesTen_install_dir%/oraclescripts
SQL> @E:/temp/oraclescripts/initCacheGlobalSchema.sql "TIMESTEN"
Cannot SET TRIMSPOOL
Cannot SET TAB
Please enter the tablespace where TIMESTEN user is to be created
The value chosen for tablespace is TIMESTEN
******* 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 **
3、創建cache administration user
這裡用戶名使用cacheuser，密碼使用oracle。運行%TimesTen_install_dir%/oraclescripts目錄下的腳本grantCacheAdminPrivileges.sql，並將cacheuser作為參數傳入。
執行這個腳本需要使用sys執行，有DBA權限的用戶執行會有部分權限無法授權
SQL>CREATE USER CACHEUSER IDENTIFIED BY ORACLE DEFAULT TABLESPACE TIMESTEN QUOTA UNLIMITED ON TIMESTEN;
uesr created
SQL> @E:/temp/oraclescripts/grantCacheAdminPrivileges.sql "CACHEUSER";
Cannot SET TRIMSPOOL
Cannot SET TAB
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
ORA-01031: insufficient privileges
3. Granting the CREATE SEQUENCE privilege to CACHEUSER
4. Granting the CREATE CLUSTER privilege to CACHEUSER
5. Granting the CREATE OPERATOR privilege to CACHEUSER
6. Granting the CREATE INDEXTYPE privilege to CACHEUSER
7. Granting the CREATE TABLE privilege to CACHEUSER
8. Granting the CREATE PROCEDURE privilege to CACHEUSER
9. Granting the CREATE ANY TRIGGER privilege to CACHEUSER
10. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEUSER
11. Granting the DBMS_LOB package privilege to CACHEUSER
ORA-01031: insufficient privileges
12. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEUSER
13. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEUSER
14. Checking if the cache administrator user has permissions on the default tablespace
Permission exists
16. Granting the CREATE TYPE privilege to CACHEUSER
17. Granting the SELECT on SYS.GV$LOCK privilege to CACHEUSER (optional)
ORA-01031: insufficient privileges
18. Granting the SELECT on SYS.GV$SESSION privilege to CACHEUSER (optional)
ORA-01031: insufficient privileges
19. Granting the SELECT on SYS.DBA_DATA_FILES privilege to CACHEUSER (optional)
ORA-01031: insufficient privileges
20. Granting the SELECT on SYS.USER_USERS privilege to CACHEUSER (optional)
21. Granting the SELECT on SYS.USER_FREE_SPACE privilege to CACHEUSER (optional)
22. Granting the SELECT on SYS.USER_TS_QUOTAS privilege to CACHEUSER (optional)
23. Granting the SELECT on SYS.USER_SYS_PRIVS privilege to CACHEUSER (optional)
** Initialization for cache admin user could not be successfully done **
我這裡用的是具有DBA權限的用戶執行的腳本，所以有部分權限無法授權，具體如下
[oracle@bogon ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 31 11:57:54 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO cacheuser;
Grant succeeded.
SQL> GRANT EXECUTE ON SYS.DBMS_LOB TO cacheuser;
Grant succeeded.
SQL> GRANT SELECT ON SYS.GV_$LOCK TO cacheuser;
Grant succeeded.
SQL> GRANT SELECT ON SYS.GV_$SESSION TO cacheuser;
Grant succeeded.
SQL> GRANT SELECT ON SYS.DBA_DATA_FILES TO cacheuser;
Grant succeeded.
將腳本整理到這裡，方便以後調用
GRANT EXECUTE ON SYS.DBMS_LOCK TO cacheuser;
GRANT EXECUTE ON SYS.DBMS_LOB TO cacheuser;
GRANT SELECT ON SYS.GV_$LOCK TO cacheuser;
GRANT SELECT ON SYS.GV_$SESSION TO cacheuser;
GRANT SELECT ON SYS.DBA_DATA_FILES TO cacheuser;
到此Oracle的工作已基本結束，以下是Timesten需要配置的內容
4、配置TNS_ADMIN
[timesten@bogon ~]$ ttModInstall -tns_admin /opt/oracle/TimesTen/tt1122/ttoracle_home/instantclient_11_2/network/admin;
TNS_ADMIN for the instance 'tt1122' is currently not set.
Would you like to change TNS_ADMIN for this instance? [ yes ] yes
Please enter a value for TNS_ADMIN (q=quit)? [ /opt/oracle/TimesTen/tt1122/ttoracle_home/instantclient_11_2/network/admin ]
Do you want to restart the daemon using the new configuration? [ yes ] yes
Restarting the daemon ...
TimesTen Daemon stopped.
TimesTen Daemon startup OK.
Instance tt1122 is now configured with TNS_ADMIN=/opt/oracle/TimesTen/tt1122/ttoracle_home/instantclient_11_2/network/admin
如果在安裝時配置了TNS_ADMIN 則此項忽略
5、設置cache administration user
NOTE:This procedure requires CACHE_MANAGER privilege.
ttCacheUidPwdSet(‘UID’, ‘PWD’)
ttCacheUidGet()
Command> call ttCacheUidPwdSet('cacheuser','oracle');
Command> call ttCacheUidGet();
< CACHEUSER >
1 row found.
6、創建cache grid
在創建cache group之前需要先建立cache grid，只有cache grid的第一個數據庫成員需要執行這個操作。當以cache manager user 登錄後，輸入如下命令創建一個cache grid “myGrid”
Command> call ttGridCreate('gjds_grid');
將timesten和剛建好的cache grid 關聯起來
Command> call ttGridNameSet('gjds_grid');
啟動cache代理，如果建立了read only cache，需要啟動該代理才能使用。該代理負責timesten database之間的溝通以及oracle和timesten cache database之間的數據流。
Command> call ttCacheStart;
7、創建cache groups
創建一個只讀cache group readcache用於高速緩存oracle中的表TEST.TT_CACHE_TEST
#首先需要在Oracle中將TT_CACHE_TEST的查詢權限授予CACHEUSER
SQL> GRANT SELECT ON TT_CACHE_TEST TO CACHEUSER;
然後在Timesten中執行下面的語句
Command> create readonly cache group TT_CACHE_TEST1 autorefresh interval 60 seconds from
TT_CACHE_TEST(
ID VARCHAR2(20) PRIMARY KEY,
ADDRESS VARCHAR2(40),
NAME VARCHAR2(20)
);
啟動復制agent，如果數據庫中有asynchronous writethrough cache groups就必須要啟動這個agent，這個進程負責TT數據庫之間，TT和oracle之間的數據復制。
Command> call ttrepstart;
8、使用read-only cache group
當以cache manager user 登錄後，需要先手動從oracle中載入readcache中相應cache表的內容
Command> LOAD CACHE GROUP TT_CACHE_TEST1 COMMIT EVERY 100 ROWS;
9 cache instances affected
9、重啟Cache代理
如果發現數據沒有同步可重啟cache代理
Command> call ttCacheStop();
Command> call ttCacheStart();
