TimesTen 数据库复制学习：2. 配置Active Standby Pair

本文为一个动手实验，配置Active Standby Pair，配置3个数据库, master, standby和一个subscriber。拓扑如下：

在本实验中，为简化，三个数据库皆位于同一主机。

创建DSN

[ODBC Data Sources]
master1=TimesTen 11.2.2 Driver
master2=TimesTen 11.2.2 Driver
subscriber1=TimesTen 11.2.2 Driver

[master1]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/tmp/master1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

[master2]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/tmp/master2
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

[subscriber1]
Driver=/home/oracle/TimesTen/tt1122/lib/libtten.so
DataStore=/tmp/subscriber1
DatabaseCharacterSet=AL32UTF8
ConnectionCharacterSet=AL32UTF8

在master库中创建表

连接master1

ttisql master1

执行以下SQL

    CREATE TABLE employees    ( employee_id NUMBER(6) PRIMARY KEY,    first_name VARCHAR2(20),    last_name VARCHAR2(25) NOT NULL,    email VARCHAR2(25) NOT NULL UNIQUE,    phone_number VARCHAR2(20),    hire_date DATE NOT NULL,    job_id VARCHAR2(10) NOT NULL,    salary NUMBER(8,2),    commission_pct NUMBER(2,2),    manager_id NUMBER(6),    department_id NUMBER(4)    ) ;

注意，这个表需要PRIMARY KEY或唯一索引,否则报错：

Command> create table t2(a int);17061: When DDLReplicationAction='INCLUDE' tables must be created with a primary key or a unique constraint on non-nullable column(s)The command failed.

定义active standby pair

连接master1

ttisql master1

执行以下语句

CREATE ACTIVE STANDBY PAIR master1, master2 SUBSCRIBER subscriber1;

若ASP位于不同主机，可参照以下语法：

     CREATE ACTIVE STANDBY PAIR master1 on "host1", master2 on "host2";

在master数据库上启动复制代理

Command> CALL ttRepStart;

将master数据库的状态设置为active

Command> CALL ttRepStateSet('ACTIVE');Command> CALL ttRepStateGet();< ACTIVE, NO GRID >

在active master中创建用户

此用户需要ADMIN权限，在下一步数据库初始化克隆时需要。

CREATE USER repadmin IDENTIFIED BY timesten;GRANT ADMIN TO repadmin;

克隆active master到active standby

$ ttRepAdmin -duplicate -from master1 -host $(hostname) -uid repadmin -pwd timesten master2$ hostnametimesten-hol

将$(hostname)替换为timesten-hol亦可

在standby master上启动复制代理

启动代理后，standby master的状态自动变为STANDBY。

$ ttisql master2Command> CALL ttRepStart;Command> CALL ttRepStateGet();< STANDBY, NO GRID >

从standby克隆只读的subscriber

这和从master克隆standby是类似的，只不过换了源和目标

$ ttRepAdmin -duplicate -from master2 -host $(hostname) -uid repadmin -pwd timesten subscriber1

在subscriber上启动复制代理

$ttisql subscriber1Command> CALL ttRepStart;Command> CALL ttRepStateGet;< IDLE, NO GRID >

IDLE也是一个正常的状态，表示subscriber

在active master中插入数据

在此拓扑中，只有active master是可写的，在master2和subscriber1中执行DML语句所报的错如下：

master2：16265: This store is currently the STANDBY.  Change to ORACLE.EMPLOYEES not permitted.The command failed.subscriber1： 8151: ORACLE.EMPLOYEES's replication role disallows the requested operationThe command failed.

在active master中执行DML:

ttisql master1

然后确认数据正常复制到standby master和subscriber:

$ ttisql master2Command> select * from employees;< 202, Pat, Fay, PFAY, 603-123-7777, 1997-08-17 00:00:00, MK_REP, 6000, <NULL>, 201, 20 >1 row found.Command> exit$ ttisql subscriber1Command> select * from employees;< 202, Pat, Fay, PFAY, 603-123-7777, 1997-08-17 00:00:00, MK_REP, 6000, <NULL>, 201, 20 >

监控Active Standby 环境

利用repschemes命令：

Command> repschemes;Replication Scheme Active Standby:  Master Store: MASTER1 on TIMESTEN-HOL  Master Store: MASTER2 on TIMESTEN-HOL  Subscriber Store: SUBSCRIBER1 on TIMESTEN-HOL   Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: MASTER1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: MASTER2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: SUBSCRIBER1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled1 replication scheme found.Command> 

从此命令可以看出完整的复制拓扑，并且是整库复制。

利用ttRepAdmin -showconfig命令，可以看出master1的peer是master2，subscriber是subscriber1

$ ttrepadmin -showconfig master1Self host "TIMESTEN-HOL", port auto, name "MASTER1", LSN 0/17137928, timeout 120, threshold 0List of subscribers-------------------Peer name         Host name                 Port    State  Proto Track----------------  ------------------------ ------  ------- ----- -----SUBSCRIBER1       TIMESTEN-HOL              Auto   Start      36     0Last Msg Sent Last Msg Recv Latency TPS     RecordsPS     ------------- ------------- ------- ------- ---------     00:00:05      -               -1.00      -1        -1 Peer name         Host name                 Port    State  Proto Track----------------  ------------------------ ------  ------- ----- -----MASTER2           TIMESTEN-HOL              Auto   Start      36     0Last Msg Sent Last Msg Recv Latency TPS     RecordsPS     ------------- ------------- ------- ------- ---------     00:00:05      00:00:05        -1.00      -1        -1 List of objects and subscriptions---------------------------------Table details-------------Table : ORACLE.EMPLOYEES   Timestamp updates : -  Master Name               Subscriber name         -----------               ---------------         MASTER1                   MASTER2                 MASTER1                   SUBSCRIBER1             Table details-------------Table : ORACLE.EMPLOYEES   Timestamp updates : -  Master Name               Subscriber name         -----------               ---------------         MASTER2                   MASTER1                 MASTER2                   SUBSCRIBER1             Datastore details-----------------Master Name               Subscriber name         -----------               ---------------         MASTER1                   MASTER2                 MASTER1                   SUBSCRIBER1             Datastore details-----------------Master Name               Subscriber name         -----------               ---------------         MASTER2                   MASTER1                 MASTER2                   SUBSCRIBER1             

ttrepadmin -showstatus命令

$ ttrepadmin -showstatus master1Replication Agent Status as of: 2016-05-30 05:47:18DSN                         : master1Process ID                  : 3942 (Started)Replication Agent Policy    : manualHost                        : TIMESTEN-HOLRepListener Port            : 33888 (AUTO)Last write LSN              : 0.17406216Last LSN forced to disk     : 0.17405952Replication hold LSN        : 0.17400072Replication Peers:   Name                     : SUBSCRIBER1   Host                     : TIMESTEN-HOL   Port                     : 54620 (AUTO) (Connected)   Replication State        : STARTED   Communication Protocol   : 36   Name                     : MASTER2   Host                     : TIMESTEN-HOL   Port                     : 59256 (AUTO) (Connected)   Replication State        : STARTED   Communication Protocol   : 36TRANSMITTER thread(s): For                     : SUBSCRIBER1 (track 0)   Start/Restart count   : 2   Send LSN              : 0.17400072   Transactions sent     : 0   Total packets sent    : 213   Tick packets sent     : 192   MIN sent packet size  : 64   MAX sent packet size  : 154   AVG sent packet size  : 65   Last packet sent at   : 05:47:14   Total Packets received: 211   MIN rcvd packet size  : 64   MAX rcvd packet size  : 128   AVG rcvd packet size  : 115   Last packet rcvd'd at : 05:47:14   TXNs Allocated        : 6   TXNs In Use           : 0   ACTs Allocated        : 4   ACTs In Use           : 0   ACTs Data Allocated   : 0   Most recent errors (max 5):     TT16290 in transmitter.c (line 8411) at 05:32:14 on 05-30-2016     TT16999 in repagent.c (line 1276) at 05:32:14 on 05-30-2016     TT16025 in repagent.c (line 1227) at 05:32:17 on 05-30-2016     TT16285 in transmitter.c (line 1020) at 05:32:17 on 05-30-2016     TT16999 in transmitter.c (line 1340) at 05:32:17 on 05-30-2016TRANSMITTER thread(s): For                     : MASTER2 (track 0)   Start/Restart count   : 2   Send LSN              : 0.17400072   Transactions sent     : 1   Total packets sent    : 263   Tick packets sent     : 248   MIN sent packet size  : 64   MAX sent packet size  : 1699   AVG sent packet size  : 71   Last packet sent at   : 05:47:14   Total Packets received: 261   MIN rcvd packet size  : 64   MAX rcvd packet size  : 128   AVG rcvd packet size  : 118   Last packet rcvd'd at : 05:47:14   TXNs Allocated        : 3   TXNs In Use           : 0   ACTs Allocated        : 1   ACTs In Use           : 0   ACTs Data Allocated   : 0   Most recent errors (max 5):     TT16290 in transmitter.c (line 8411) at 05:28:02 on 05-30-2016     TT16999 in repagent.c (line 1276) at 05:28:02 on 05-30-2016     TT16025 in repagent.c (line 1227) at 05:28:05 on 05-30-2016     TT16285 in transmitter.c (line 1020) at 05:28:05 on 05-30-2016     TT16999 in transmitter.c (line 1340) at 05:28:05 on 05-30-2016RECEIVER thread(s): For                     : MASTER2 (track 0)   Start/Restart count   : 1   Transactions received : 0   Total packets sent    : 252   Tick packets sent     : 0   MIN sent packet size  : 64   MAX sent packet size  : 120   AVG sent packet size  : 119   Last packet sent at   : 05:47:15   Total Packets received: 253   MIN rcvd packet size  : 64   MAX rcvd packet size  : 154   AVG rcvd packet size  : 64   Last packet rcvd'd at : 05:47:15   rxWaitCTN             : 0.0   prevCTN               : 0.0   STA Blk Data Allocated: 0   STA Data Allocated    : 0   Most recent errors (max 5):     TT16025 in repagent.c (line 1227) at 05:28:02 on 05-30-2016     TT16999 in meta.c (line 3166) at 05:28:02 on 05-30-2016

$ ttRepAdmin -log master11 log file retained by replication[oracle@timesten-hol info]$ ttRepAdmin -self -list master1Self host "TIMESTEN-HOL", port auto, name "MASTER1", LSN 0/17707272Operation successful[oracle@timesten-hol info]$ ttRepAdmin -self -list master2Self host "TIMESTEN-HOL", port auto, name "MASTER2", LSN 0/17891592Operation successful[oracle@timesten-hol info]$ ttRepAdmin -self -list subscriber1Self host "TIMESTEN-HOL", port auto, name "SUBSCRIBER1", LSN -1/-1Operation successful[oracle@timesten-hol info]$ ttrepadmin -receiver -list master1Peer name         Host name                 Port    State  Proto Track----------------  ------------------------ ------  ------- ----- -----MASTER2           TIMESTEN-HOL              Auto   Start      36     0Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs------------- ------------- ------- ------- --------- ----00:00:02      00:00:06        -1.00      -1        -1    1Peer name         Host name                 Port    State  Proto Track----------------  ------------------------ ------  ------- ----- -----SUBSCRIBER1       TIMESTEN-HOL              Auto   Start      36     0Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs------------- ------------- ------- ------- --------- ----00:00:02      -               -1.00      -1        -1    1[oracle@timesten-hol info]$ ttRepAdmin -wait -name master2 master1Replication has caught up after 0 seconds[oracle@timesten-hol info]$ ttRepAdmin -wait -name subscriber1 master1Replication has caught up after 0 seconds[oracle@timesten-hol info]$ ttRepAdmin -bookmark master1Replication hold LSN ...... 0/18417928Last written LSN .......... 0/18430216Last LSN forced to disk ... 0/18429952

增加复制的表

由于此实验定义的是整库复制，即针对整个Data Store，因此添加新的表时，会自动的添加到复制的scheme中。见下例：

$ ttisql master1Command> create table t1(a int, primary key(a));Command> insert into t1 values(1);1 row inserted.[oracle@timesten-hol info]$ ttisql master2Command> select * from t1;< 1 >1 row found.[oracle@timesten-hol info]$ ttisql subscriber1Command> select * from t1;< 1 >

使用ttRepAdmin -showconfig可以显示增加的复制表：

$ ttrepadmin -showconfig master1 ......List of objects and subscriptions---------------------------------Table details-------------Table : ORACLE.EMPLOYEES   Timestamp updates : -Master Name               Subscriber name-----------               ---------------MASTER1                   MASTER2    MASTER1                   SUBSCRIBER1Table details-------------Table : ORACLE.EMPLOYEES   Timestamp updates : -Master Name               Subscriber name-----------               ---------------MASTER2                   MASTER1    MASTER2                   SUBSCRIBER1Table details-------------Table : ORACLE.T1   Timestamp updates : -Master Name               Subscriber name-----------               ---------------MASTER1                   MASTER2    MASTER1                   SUBSCRIBER1Table details-------------Table : ORACLE.T1   Timestamp updates : -Master Name               Subscriber name-----------               ---------------MASTER2                   MASTER1MASTER2                   SUBSCRIBER1......

清理Active Standby Pair环境

先分别在所有库中停止复制代理，可以看到这时所有的rep state是不变的

$ ttisql -v1 master1Command> call ttrepstop;Command> call ttrepstateget;< ACTIVE, NO GRID >1 row found.$ ttisql -v1 master2 Command> call ttrepstop;Command> call ttrepstateget;< STANDBY, NO GRID >$ ttisql -v1 subscriber1Command> call ttrepstop;Command> call ttrepstateget;< IDLE, NO GRID >

然后在所有的库中删除ASP:

drop active standby pair;

这时所有库的复制状态变为IDLE。

这时还需要在所有的库中删除复制的表，例如：

$ ttisql subscriber1Command> tables;  ORACLE.EMPLOYEES  ORACLE.T12 tables found.Command> drop table employees;Command> drop table t1;

总结

• TimesTen配置ASP非常简单，利用ttRepAdmin从主库克隆即可。使用ttRepStateGet得到的状态分别为ACTIVE, STANDBY和IDLE。
• 复制的表需要主键和唯一索引，这和缓存组的要求是一样的。
• ASP通常用于整库复制，这时在active master执行的DDL自动复制到对方。
• 复制中每一个库都需要启动一个对应的复制代理