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

栏目：数据库应用时间：2016-06-04 15:15:41

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

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

创建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或唯1索引,否则报错：

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权限，在下1步数据库初始化克隆时需要。

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
$ hostname
timesten-hol

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

在standby master上启动复制代理

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

$ ttisql master2

Command> 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 subscriber1
Command> CALL ttRepStart;
Command> CALL ttRepStateGet;
< IDLE, NO GRID >

IDLE也是1个正常的状态，表示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 operation
The command failed.

在active master中履行DML:

ttisql master1

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

$ ttisql master2
Command> 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 subscriber1
Command> 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: Disabled

1 replication scheme found.
Command>

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

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

$ ttrepadmin -showconfig master1

Self host "TIMESTEN-HOL", port auto, name "MASTER1", LSN 0/17137928, timeout 120, threshold 0


List of subscribers
-------------------

Peer name         Host name                 Port    State  Proto Track
----------------  ------------------------ ------  ------- ----- -----
SUBSCRIBER1       TIMESTEN-HOL              Auto   Start      36     0

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS     
------------- ------------- ------- ------- ---------     
00:00:05      -               ⑴.00      ⑴        ⑴ 


Peer name         Host name                 Port    State  Proto Track
----------------  ------------------------ ------  ------- ----- -----
MASTER2           TIMESTEN-HOL              Auto   Start      36     0

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS     
------------- ------------- ------- ------- ---------     
00:00:05      00:00:05        ⑴.00      ⑴        ⑴ 




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 master1

Replication Agent Status as of: 2016-05-30 05:47:18

DSN                         : master1
Process ID                  : 3942 (Started)
Replication Agent Policy    : manual
Host                        : TIMESTEN-HOL
RepListener Port            : 33888 (AUTO)
Last write LSN              : 0.17406216
Last LSN forced to disk     : 0.17405952
Replication hold LSN        : 0.17400072

Replication 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   : 36

TRANSMITTER 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-2016

TRANSMITTER 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-2016

RECEIVER 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 master1
1 log file retained by replication

[oracle@timesten-hol info]$ ttRepAdmin -self -list master1
Self host "TIMESTEN-HOL", port auto, name "MASTER1", LSN 0/17707272
Operation successful

[oracle@timesten-hol info]$ ttRepAdmin -self -list master2
Self host "TIMESTEN-HOL", port auto, name "MASTER2", LSN 0/17891592
Operation successful

[oracle@timesten-hol info]$ ttRepAdmin -self -list subscriber1
Self host "TIMESTEN-HOL", port auto, name "SUBSCRIBER1", LSN -1/-1
Operation successful

[oracle@timesten-hol info]$ ttrepadmin -receiver -list master1
Peer name         Host name                 Port    State  Proto Track
----------------  ------------------------ ------  ------- ----- -----
MASTER2           TIMESTEN-HOL              Auto   Start      36     0

Last Msg Sent Last Msg Recv Latency TPS     RecordsPS Logs
------------- ------------- ------- ------- --------- ----
00:00:02      00:00:06        -1.00      -1        -1    1


Peer name         Host name                 Port    State  Proto Track
----------------  ------------------------ ------  ------- ----- -----
SUBSCRIBER1       TIMESTEN-HOL              Auto   Start      36     0

Last 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 master1
Replication has caught up after 0 seconds
[oracle@timesten-hol info]$ ttRepAdmin -wait -name subscriber1 master1
Replication has caught up after 0 seconds

[oracle@timesten-hol info]$ ttRepAdmin -bookmark master1
Replication hold LSN ...... 0/18417928
Last written LSN .......... 0/18430216
Last LSN forced to disk ... 0/18429952

增加复制的表

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

$ ttisql master1
Command> create table t1(a int, primary key(a));
Command> insert into t1 values(1);
1 row inserted.

[oracle@timesten-hol info]$ ttisql master2
Command> select * from t1;
< 1 >
1 row found.

[oracle@timesten-hol info]$ ttisql subscriber1
Command> 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                   SUBSCRIBER1

Table details
-------------
Table : ORACLE.EMPLOYEES   Timestamp updates : -

Master Name               Subscriber name
-----------               ---------------
MASTER2                   MASTER1    
MASTER2                   SUBSCRIBER1

Table details
-------------
Table : ORACLE.T1   Timestamp updates : -

Master Name               Subscriber name
-----------               ---------------
MASTER1                   MASTER2    
MASTER1                   SUBSCRIBER1

Table details
-------------
Table : ORACLE.T1   Timestamp updates : -

Master Name               Subscriber name
-----------               ---------------
MASTER2                   MASTER1
MASTER2                   SUBSCRIBER1

......

清算Active Standby Pair环境

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

$ ttisql -v1 master1
Command> 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 subscriber1
Command> call ttrepstop;
Command> call ttrepstateget;
< IDLE, NO GRID >

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

drop active standby pair;

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

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

$ ttisql subscriber1
Command> tables;
  ORACLE.EMPLOYEES
  ORACLE.T1
2 tables found.
Command> drop table employees;
Command> drop table t1;

总结

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

------分隔线----------------------------

上一篇 C#三十六三层架构的实现

下一篇 JVM理解其实并不难！

------分隔线----------------------------