Oracle内存数据库Timesten的使用(2)

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/jaune161/article/details/47255101

本文主要讲述如何实现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();

转载于:https://my.oschina.net/jaune161/blog/3015869