您當前位置：首頁 > 數據庫 > 數據庫應用 > TimesTen 數據庫復制學習：2. 配置Active Standby Pair

TimesTen 數據庫復制學習：2. 配置Active Standby Pair

來源：程序員人生發布時間：2016-06-04 15:15:41 閱讀次數：3766次

本文為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個對應的復制代理

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
程序員人生