timesten针对复制器新增一张复制表

一、在tt中，新增一张表注意如下两点：

1，虽然复制器配置的是DS级别，但是新增的表默认不在复制内，需要手动添加

2，新建的表，如果主机已经插入数据，即使加入复制器，前面未同步的数据也不会同步。

二、一般新增一张表的步骤为：

1、停主备复制器

2、主备机新建表

3、修改复制器定义

ALTER REPLICATION OCS.REP_OCS 
ALTER ELEMENT E_1                 
INCLUDE TABLE 新建表名
ALTER ELEMENT E_2 

INCLUDE TABLE 新建表名；

4、启动复制器

5、检查复制器定义

6、观察对表的操作是否复制

三、下面进行实际操作：

1、在主备上停复制器

ttadmin -reppolicy manual ocs

ttadmin -repstop ocs

在节点ocs1上执行：

tt@ocs1[/tt]$ttadmin -reppolicy manual ocs
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : True
Cache Agent Policy              : manual
Cache Agent Manually Started    : False
tt@ocs1[/tt]$ttadmin -repstop ocs
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : False
Cache Agent Policy              : manual
Cache Agent Manually Started    : False

在节点ocs2上执行：

tt@ocs2[/tt/TimesTen/tt11/info]$ttadmin -reppolicy manual ocs
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : True
Cache Agent Policy              : manual
Cache Agent Manually Started    : False
tt@ocs2[/tt/TimesTen/tt11/info]$ttadmin -repstop ocs
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : False
Cache Agent Policy              : manual
Cache Agent Manually Started    : False

2、主备新建表：

create table TT.TEST02 (
        "ID"   NUMBER(38) NOT NULL,
        "NAME" VARCHAR2(10 BYTE) INLINE,
    primary key ("ID"));

在ocs1和ocs2上都要执行。

3、修改主备复制器定义：

可以通过命令ttschema -list all ocs查看原始复制器名称和定义：

tt@ocs2[/tt/TimesTen/tt11/info]$ttschema -list all ocs
create replication OCS.REP_OCS
    element E_1 datastore 
        master OCS on "OCS1"
        subscriber OCS on "OCS2"
        exclude table
            TT.TEST03
        exclude sequence
            TT.SEQ_PAY_ID,
            TT.SEQ_TEST01
    element E_2 datastore 
        master OCS on "OCS2"
        subscriber OCS on "OCS1"
        exclude table
            TT.TEST03
        exclude sequence
            TT.SEQ_PAY_ID,
            TT.SEQ_TEST01
    store OCS on "OCS1"
        port 17005
    store OCS on "OCS2"
        port 17006
    route master OCS on OCS1 subscriber OCS on OCS2
        masterip "193.169.1.1" priority 1
        masterip "193.169.2.1" priority 2
        masterip "192.168.254.135" priority 3
        subscriberip "193.169.1.2" priority 1
        subscriberip "193.169.2.2" priority 2
        subscriberip "192.168.254.136" priority 3
    route master OCS on OCS2 subscriber OCS on OCS1
        masterip "193.169.1.2" priority 1
        masterip "193.169.2.2" priority 2
        masterip "192.168.254.136" priority 3
        subscriberip "193.169.1.1" priority 1
        subscriberip "193.169.2.1" priority 2
        subscriberip "192.168.254.135" priority 3

;

然后修改复制器定义：

ALTER REPLICATION OCS.REP_OCS 
ALTER ELEMENT E_1
INCLUDE TABLE TEST02
ALTER ELEMENT E_2

INCLUDE TABLE TEST02 ;

在节点ocs1上执行：

tt@ocs1[/tt]$ttisql ocs

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=ocs";
Connection successful: DSN=ocs;UID=tt;DataStore=/tt/DS/ocs;DatabaseCharacterSet=TIMESTEN8;ConnectionCharacterSet=TIMESTEN8;AutoCreate=0;LogFileSize=40;DRIVER=/tt/TimesTen/tt11/lib/libtten.so;PermSize=32;TempSize=37;LockWait=1;CkptRate=40;CkptFrequency=300;CkptLogVolume=0;PrivateCommands=1;RecoveryThreads=3;TypeMode=0;LogBufMB=40;LogBufParallelism=5;
(Default setting AutoCommit=1)
Command> ALTER REPLICATION OCS.REP_OCS 
       > ALTER ELEMENT E_1
       > INCLUDE TABLE TEST02
       > ALTER ELEMENT E_2
       > INCLUDE TABLE TEST02 ;
Command> quit
Disconnecting...
Done.

在节点ocs2上同样执行，不在贴了。

4、起复制器：

ttadmin -repstart ocs

ttadmin -reppolicy always

节点ocs1上：

tt@ocs1[/tt]$ttadmin -repstart ocs
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : True
Cache Agent Policy              : manual
Cache Agent Manually Started    : False
tt@ocs1[/tt]$ttadmin -reppolicy always
ttAdmin: No DSN or connection string given.
For a list of options, run "ttAdmin -help".
tt@ocs1[/tt]$ttadmin -reppolicy always ocs
RAM Residence Policy            : always
Replication Agent Policy        : always
Cache Agent Policy              : manual
Cache Agent Manually Started    : False

节点ocs2执行：

tt@ocs2[/tt/TimesTen/tt11/info]$ttadmin -repstart ocs
RAM Residence Policy            : always
Replication Agent Policy        : manual
Replication Manually Started    : True
Cache Agent Policy              : manual
Cache Agent Manually Started    : False
tt@ocs2[/tt/TimesTen/tt11/info]$ttadmin -reppolicy always ocs
RAM Residence Policy            : always
Replication Agent Policy        : always
Cache Agent Policy              : manual
Cache Agent Manually Started    : False

可以查看下心的复制器定义：

tt@ocs2[/tt/TimesTen/tt11/info]$ttschema -list all ocs
create replication OCS.REP_OCS
    element E_1 datastore 
        master OCS on "OCS1"
        subscriber OCS on "OCS2"
        exclude table
            TT.TEST03
        exclude sequence
            TT.SEQ_PAY_ID,
            TT.SEQ_TEST01
    element E_2 datastore 
        master OCS on "OCS2"
        subscriber OCS on "OCS1"
        exclude table
            TT.TEST03
        exclude sequence
            TT.SEQ_PAY_ID,
            TT.SEQ_TEST01
    store OCS on "OCS1"
        port 17005
    store OCS on "OCS2"
        port 17006
    route master OCS on OCS1 subscriber OCS on OCS2
        masterip "193.169.1.1" priority 1
        masterip "193.169.2.1" priority 2
        masterip "192.168.254.135" priority 3
        subscriberip "193.169.1.2" priority 1
        subscriberip "193.169.2.2" priority 2
        subscriberip "192.168.254.136" priority 3
    route master OCS on OCS2 subscriber OCS on OCS1
        masterip "193.169.1.2" priority 1
        masterip "193.169.2.2" priority 2
        masterip "192.168.254.136" priority 3
        subscriberip "193.169.1.1" priority 1
        subscriberip "193.169.2.1" priority 2
        subscriberip "192.168.254.135" priority 3

;

5、测试复制器：

ocs1：

tt@ocs1[/tt]$ttisql ocs

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=ocs";
Connection successful: DSN=ocs;UID=tt;DataStore=/tt/DS/ocs;DatabaseCharacterSet=TIMESTEN8;ConnectionCharacterSet=TIMESTEN8;AutoCreate=0;LogFileSize=40;DRIVER=/tt/TimesTen/tt11/lib/libtten.so;PermSize=32;TempSize=37;LockWait=1;CkptRate=40;CkptFrequency=300;CkptLogVolume=0;PrivateCommands=1;RecoveryThreads=3;TypeMode=0;LogBufMB=40;LogBufParallelism=5;
(Default setting AutoCommit=1)
Command> select * from test02 ;
0 rows found.
Command> desc test02 ;

Table TT.TEST02:
  Columns:
   *ID                              NUMBER (38) NOT NULL
    NAME                            VARCHAR2 (10) INLINE

1 table found.
(primary key columns are indicated with *)
Command> insert into test02 values(1,'haha') ;
1 row inserted.
Command> select * from test02;
< 1, haha >
1 row found.
Command> insert into test02 values(2,'wufan');
1 row inserted.

ocs2：

tt@ocs2[/tt/TimesTen/tt11/info]$ttisql ocs

Copyright (c) 1996, 2015, Oracle and/or its affiliates. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.



connect "DSN=ocs";
Connection successful: DSN=ocs;UID=tt;DataStore=/tt/DS/ocs;DatabaseCharacterSet=TIMESTEN8;ConnectionCharacterSet=TIMESTEN8;AutoCreate=0;LogFileSize=40;DRIVER=/tt/TimesTen/tt11/lib/libtten.so;PermSize=32;TempSize=37;LockWait=1;CkptRate=40;CkptFrequency=300;CkptLogVolume=0;PrivateCommands=1;RecoveryThreads=3;TypeMode=0;LogBufMB=40;LogBufParallelism=5;
(Default setting AutoCommit=1)
Command> select * from test02;
< 1, haha >
1 row found.
Command> select * from test02;
< 1, haha >
< 2, wufan >
2 rows found.
Command> 

生效。