分类目录归档：TimesTen

一、linux/unix环境配置
1.相关IP配置
客户端:192.168.1.105
服务端:192.168.1.10

2.服务端ODBC配置

$ more sys.odbc.ini
[ODBC Data Sources]
TT_1122=TimesTen 11.2.2 Driver
[TT_1122]
Driver=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/tt1122/info/TT_1122
DatabaseCharacterSet=ZHS16GBK
PermSize=64
OracleNetServiceName=XFF

3.服务端timesten监听端口

$ ttstatus|grep "TimesTen server"
TimesTen server pid 3792 started on port 53393

4.客户端配置

$ more sys.ttconnect.ini 
[tt_xifenfei_c_s]
Network_Address=192.168.1.10
TCP_PORT=53393

$ more sys.odbc.ini
[ODBC Data Sources]
tt_xifenfei_c=TimesTen 11.2.2 Client Driver
[tt_xifenfei_c]
TTC_SERVER=tt_xifenfei_c_s
TTC_SERVER_DSN=tt_1122
UID=chf
PWD=xifenfei

参考:在另一节点配置TT客户端的简明方法

二、win环境配置
1.在odbc中选择增加timesten client

2.点击services，增加ip和端口配置

3.配置关闭services，配置其他选项

三、补充说明
这里的配置，基本上都是最基础配置，在实际生产环境中，需要考虑其他相关环境变量

1.准备测试表和数据

SQL> CREATE TABLE customer
  2  (cust_num NUMBER(6) NOT NULL PRIMARY KEY,
  3   name     VARCHAR2(50)
  4  );

Table created.

SQL> CREATE TABLE orders
  2  (ord_num      NUMBER(10) NOT NULL PRIMARY KEY,
  3   cust_num     NUMBER(6) NOT NULL
  4  );

Table created.


SQL> insert into customer values(1,'wwww.xifenfei.com1');

1 row created.

SQL> insert into customer values(2,'wwww.xifenfei.com2');

1 row created.

SQL> insert into customer values(3,'wwww.xifenfei.com3');

1 row created.

SQL> insert into customer values(4,'wwww.xifenfei.com4');

1 row created.

SQL> insert into orders(cust_num,ord_num) values(1,1);

1 row created.

SQL> insert into orders (cust_num,ord_num) values(1,2);

1 row created.

SQL> insert into orders (cust_num,ord_num) values(3,5);

1 row created.

SQL> insert into orders (cust_num,ord_num) values(3,6);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from customer;

  CUST_NUM NAME
---------- --------------------------------------------------
         1 wwww.xifenfei.com1
         2 wwww.xifenfei.com2
         3 wwww.xifenfei.com3
         4 wwww.xifenfei.com4

SQL> select * from orders;

   ORD_NUM   CUST_NUM
---------- ----------
         1          1
         2          1
         5          3
         6          3

SQL> grant select on oratt.customer to cacheuser;

Grant succeeded.

SQL> grant select on oratt.orders to cacheuser;

Grant succeeded.

2.创建cache group

[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"

Command>  drop cache group cacheuser.customer_orders;
Command> CREATE READONLY CACHE GROUP customer_orders
       > AUTOREFRESH INTERVAL 5 SECONDS
       > STATE ON
       > FROM oratt.customer
       >  (cust_num NUMBER(6) NOT NULL,
       >   name     VARCHAR2(50),
       >   PRIMARY KEY(cust_num)),
       > oratt.orders
       >  (ord_num      NUMBER(10) NOT NULL,
       >   cust_num     NUMBER(6) NOT NULL,
       >   PRIMARY KEY(ord_num),
       >   FOREIGN KEY(cust_num) REFERENCES oratt.customer(cust_num));

Command> cachegroups;

Cache Group CACHEUSER.CUSTOMER_ORDERS:

  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: ORATT.CUSTOMER
  Table Type: Read Only


  Child Table: ORATT.ORDERS
  Table Type: Read Only

1 cache groups found.

3.TT中表访问授权

[oracle@xifenfei ~]$ ttisql tt_1122

Command> grant select on oratt.customer to cacheuser;
Command> grant select on oratt.orders to cacheuser;

4.测试数据初始化

[oracle@xifenfei ~]$ ttIsql "DSN=tt_1122;UID=cacheuser;PWD=timesten;OraclePWD=oracle"
Command> select * from oratt.customer;
< 1, wwww.xifenfei.com1 >
< 2, wwww.xifenfei.com2 >
< 3, wwww.xifenfei.com3 >
< 4, wwww.xifenfei.com4 >
4 rows found.
Command> select * from oratt.orders;
< 1, 1 >
< 2, 1 >
< 5, 3 >
< 6, 3 >
4 rows found.

5.ORACLE修改数据

SQL> update customer set name='xifenfei' where cust_num=2;

1 row updated.

SQL> insert into customer values(5,'wwww.xifenfei.com5');

1 row created.

SQL> delete from customer where cust_num=1;

1 row deleted.

SQL> commit;

Commit complete.

6.TT中验证数据

Command> select * from oratt.customer;
< 2, xifenfei >
< 3, wwww.xifenfei.com3 >
< 4, wwww.xifenfei.com4 >
< 5, wwww.xifenfei.com5 >
4 rows found.
Command> select * from oratt.orders;
< 5, 3 >
< 6, 3 >
2 rows found.

7.补充说明
7.1)在oracle中需要授权cacheuser有访问oratt中相关表权限，不然创建cache group失败
7.2)自动刷新数据需要设置AUTOREFRESH STATE ON，其他方法初始化关联表的数据暂未知
7.3)在TT中，关联表删除是级联的

Step 1: Create the DSNs for the master and the subscriber databases

[master1]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate//master1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

[master2]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate//master2
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

[subscriber1]
DRIVER=/u01/TimesTen/tt1122/lib/libtten.so
DataStore=/u01/TimesTen/replicate/subscriber1
DatabaseCharacterSet=ZHS16GBK
ConnectionCharacterSet=ZHS16GBK
PermSize=64

Step 2: Create a table in one of the master databases

[oracle@xifenfei info]$ ttIsql master1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master1";
Connection successful: DSN=master1;UID=oracle;DataStore=/u01/TimesTen/replicate//master1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CREATE TABLE tab (a NUMBER NOT NULL,
       >    b CHAR(18),
       >    PRIMARY KEY (a));

Step 3: Define the active standby pair

Command>  CREATE ACTIVE STANDBY PAIR master1, master2
       >    SUBSCRIBER subscriber1;

Step 4: Start the replication agent on a master database

Command>  CALL ttRepStart;

Step 5: Set the state of a master database to ‘ACTIVE’

Command>  CALL ttRepStateSet('ACTIVE');

Step 6. Create a user on the active database

Command> CREATE USER terry IDENTIFIED BY terry;

User created.

Command>  GRANT admin TO terry;

Step 7: Duplicate the active database to the standby database

[oracle@xifenfei info]$ ttRepAdmin -duplicate -from master1 -host xifenfei -uid terry -pwd terry  "dsn=master2"

Step 8: Start the replication agent on the standby database

[oracle@xifenfei info]$ ttIsql master2

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


connect "DSN=master2";
Connection successful: DSN=master2;UID=oracle;DataStore=/u01/TimesTen/replicate//master2;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CALL ttRepStart;

Step 9. Duplicate the standby database to the subscriber

[oracle@xifenfei info]$ ttRepAdmin -duplicate -from master2 -host xifenfei -uid terry -pwd terry  "dsn=subscriber1"

Step 10: Start the replication agent on the subscriber

[oracle@xifenfei info]$ ttIsql subscriber1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=oracle;DataStore=/u01/TimesTen/replicate/subscriber1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command>  CALL ttRepStart;

Step 11: Insert data into the table on the active database

[oracle@xifenfei info]$ ttIsql master1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master1";
Connection successful: DSN=master1;UID=oracle;DataStore=/u01/TimesTen/replicate//master1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> INSERT INTO tab VALUES (1,'Hello');
1 row inserted.
Command> commit;

Setp 12:Verify that the insert is replicated to master2 and subscriber1

[oracle@xifenfei info]$ ttIsql master2

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=master2";
Connection successful: DSN=master2;UID=oracle;DataStore=/u01/TimesTen/replicate//master2;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from tab;
< 1, Hello              >
1 row found.
Command> exit
Disconnecting...
Done.
[oracle@xifenfei info]$ ttIsql subscriber1

Copyright (c) 1996-2011, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=subscriber1";
Connection successful: DSN=subscriber1;UID=oracle;DataStore=/u01/TimesTen/replicate/subscriber1;DatabaseCharacterSet=ZHS16GBK;ConnectionCharacterSet=ZHS16GBK;DRIVER=/u01/TimesTen/tt1122/lib/libtten.so;PermSize=64;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from tab;
< 1, Hello              >
1 row found.

Configuring an active standby pair with one subscriber