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

原创 2016年05月30日 21:28:43

本文为一个动手实验,配置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
$ 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也是一个正常的状态,表示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      -               -1.00      -1        -1 


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        -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 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。
  • 复制的表需要主键和唯一索引,这和缓存组的要求是一样的。
  • ASP通常用于整库复制,这时在active master执行的DDL自动复制到对方。
  • 复制中每一个库都需要启动一个对应的复制代理
版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

TimesTen 数据库复制学习:8. 管理Active Standby Pair(带缓存组)

带缓存组的Active standby pairs(ASP)在不带缓存组的ASP中,复制发生在TimesTen的表间;而在带缓存组的ASP中,复制发生在cache table之间。带缓冲组的复制仅支持...

TimesTen 数据库复制学习:1. TimesTen复制概述

本文为TimesTen数据库复制系列的第一篇。什么是复制复制就是在多个数据库中存在多份数据拷贝,对性能影响最小的同时保证数据高可用,除了数据恢复外,还可以均衡工作负载,以最大化性能和实现滚动升级和维护...

TimesTen 数据库复制学习:3. 配置Classic Replication单表复制

本文为一个动手实验,配置传统复制模式中的单表复制(非复制整库),配置2个数据库, master和一个subscriber。拓扑如下:为简化,master和subscriber位于同一主机。同时,为和上...

TimesTen 数据库复制学习:7. 管理Active Standby Pair(无缓存组)

Active Standby Pair是TimesTen复制的一种固定模式,就是1个active到1个standby,再到0个或127个subscriber,如下图:配置 Active Standby...

TimesTen 数据库复制学习:9. 更改Active Standby Pair

在Active Standby Pair中复制DDL语句在以下的例子中,active master为cachedb2, standby master为cachedb1在Active Standby P...

TimesTen 数据库复制学习:4. 定义Active Standby Pair复制策略

master数据库状态可以使用ttRepStateGet 得到数据库的复制状态/角色 ACTIVE - active master,主库,可读写 STANDBY - active standby,...

TimesTen 应用层数据库缓存学习:8. 配置Sliding Window(滑动窗口)

Sliding Window(滑动窗口)是Cache Group常用的一种场景。 本文介绍滑动窗口的基本概念以及如何配置滑动窗口。滑动窗口的描述如下: In a sliding window c...

TimesTen 应用层数据库缓存学习:3. 环境准备 - DB 11gR2版本

本文通过一个例子讲述在建立Cache Group之前的准备工作。 准备工作包含4个部分。 1. 在Oracle数据库中创建用户 2. 为TimesTen数据库创建DSN 3. 在TimesTe...

Oracle DataGuard学习笔记(2)物理Standby的配置

Oracle DataGuard学习笔记(2)物理Standby的配置     DataGuard是Oracle数据库软件集成的功能,不需要另外安装软件,只需要配置即可.     物理St...
  • gyming
  • gyming
  • 2017年01月08日 20:38
  • 469

TimesTen 数据库复制学习:15. 监控复制系统

复制系统的监控主要通过ttStatus 和 ttRepAdmin命令,也可以从系统表中得到信息。 以下的输出,基于cachedb1为复制源,cachedb2为复制目标显示复制代理的状态ttstatu...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:TimesTen 数据库复制学习:2. 配置Active Standby Pair
举报原因:
原因补充:

(最多只允许输入30个字)