程序博客网 > 国家邮政局投诉知乎

TimesTen 数据库复制学习：17. 更改classic复制

来源：互联网 发布：国家邮政局投诉知乎 编辑：程序博客网 时间：2024/04/27 01:34

本文描述的是如何更改classic replication的scheme，table等

更改复制策略

以下的更改无需停止复制代理：
* Create, alter 或 drop 用户
* Grant or revoke 权限
* 添加subscriber
* 添加 PL/SQL 对象

其它的更改的过程如下：
1. 停止复制代理
2. alter replication语句，在master和subscriber上都需要执行
3. 启动复制代理

准备工作

在cachedb1和cachedb2上使用instance admin建立用户tthr和复制管理用户repadmin

create user tthr identified by timesten;grant create session, create table to tthr;create user repadmin identified by timesten;grant admin to repadmin;

在cachedb1和cachedb2上使用repadmin建立复制scheme

为了试验目的了，我们建立两种基准的复制scheme，这两者并不能并存，而是创建一种时，必须先将另一种删除掉。

  • 基于TABLE的基准复制scheme
create table tthr.a1(a int, primary key(a));CREATE REPLICATION repscheme    ELEMENT e TABLE tthr.a1    MASTER cachedb1 ON "timesten-hol"    SUBSCRIBER cachedb2 ON "timesten-hol"    RETURN RECEIPT;call ttrepstart;insert into tthr.a1 values(1)
  • 基于DATSTORE的基准复制scheme
call ttrepstop;CREATE REPLICATION repscheme1    ELEMENT e DATASTORE    MASTER cachedb1 ON "timesten-hol"    SUBSCRIBER cachedb2 ON "timesten-hol"    RETURN RECEIPT;call ttrepstart;Command> repschemes;Replication Scheme REPADMIN.REPSCHEME:  Element: E                                Type: Table TTHR.A1  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)1 replication scheme found.

增加表到已有的scheme

分两种情形.

  • 基于TABLE的基准复制scheme

在cachedb1和cachedb2上使用repadmin更改复制scheme，添加表a2

create table tthr.a2(a int, primary key(a));call ttrepstop;ALTER REPLICATION repscheme  ADD ELEMENT e1 TABLE tthr.a2    MASTER cachedb1 ON "timesten-hol"    SUBSCRIBER cachedb2 ON "timesten-hol"    RETURN RECEIPT;call ttrepstart;Command> repschemes;Replication Scheme REPADMIN.REPSCHEME:  Element: E                                Type: Table TTHR.A1  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Element: E1                               Type: Table TTHR.A2  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)1 replication scheme found.
  • 基于DATASTORE的基准复制scheme

如果建立的是DATASTORE的复制，那么建立时，数据库中所有满足条件的表都会复制。
但如果后续添加了表，则需要alter replication添加才可复制

Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)1 replication scheme found.create table tthr.a3(a int, primary key(a));Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Excluded Tables:    TTHR.A3  Included Tables:    TTHR.A1    TTHR.A2  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)1 replication scheme found.call ttrepstop;ALTER REPLICATION repscheme1  ALTER ELEMENT E INCLUDE TABLE tthr.a3;call ttrepstart;Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)1 replication scheme found.

添加DATASTORE到已有的scheme

感觉主要用于双向复制。或级联复制，例如db1->db2，同时db2->db3，这时db2既是master，也是subscriber

ALTER REPLICATION repscheme  ADD ELEMENT ds1 DATASTORE       MASTER cachedb1       SUBSCRIBER cachedb3; 8180: Full versus partial replication conflict with an existing replication scheme on data store CACHEDB1The command failed.

ALTER REPLICATION repscheme1
ADD ELEMENT ds1 DATASTORE
MASTER cachedb3
SUBSCRIBER cachedb4;

ALTER REPLICATION repscheme1
ADD ELEMENT ds1 DATASTORE
MASTER cachedb2
SUBSCRIBER cachedb4;
Command> repschemes;

Replication Scheme REPADMIN.REPSCHEME1:

Element: DS1
Type: Datastore
Master Store: CACHEDB2 on TIMESTEN-HOL Transmit Durable
Subscriber Store: CACHEDB4 on TIMESTEN-HOL

Excluded Tables:
None

Excluded Cache Groups:
None

Excluded sequences:
None

Element: E
Type: Datastore
Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable
Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt
Subscriber Store: CACHEDB3 on TIMESTEN-HOL

Excluded Tables:
None

Excluded Cache Groups:
None

Excluded sequences:
None

Store: CACHEDB1 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled

Store: CACHEDB2 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
Return Service Wait Time: 10 seconds
Return Service on Replication Stop: Disabled
Return Service Failure Policy: (none)

Store: CACHEDB3 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled

Store: CACHEDB4 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled

1 replication scheme found.

ttdestroycachedb4[oracle@timesten−holinfo] ttRepAdmin -verbosity 2 -duplicate -from cachedb2 -host timesten-hol -dsn cachedb4 -uid repadmin -pwd timesten
05:15:02 Contacting remote main daemon at 127.0.0.1 port 53392
05:15:02 Contacting the replication agent for CACHEDB2 ON TIMESTEN-HOL (127.0.0.1) port 19521
05:15:02 Beginning transfer from CACHEDB2 ON TIMESTEN-HOL to CACHEDB4 ON TIMESTEN-HOL
05:15:16 Checkpoint transfer 10 percent complete
05:15:16 Checkpoint transfer 20 percent complete
05:15:16 Checkpoint transfer 30 percent complete
05:15:16 Checkpoint transfer 100 percent complete
05:15:16 Checkpoint transfer phase complete
05:15:17 Log transfer 100 percent complete
05:15:17 Log transfer phase complete
05:15:18 Transfer complete
05:15:21 Duplicate Operation Ends
[oracle@timesten-hol info]$ ttisql “dsn=cachedb4;uid=repadmin;pwd=timesten”

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

connect “dsn=cachedb4;uid=repadmin;pwd=timesten”;
Connection successful: DSN=cachedb4;UID=repadmin;DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb4;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;LogFileSize=32;DRIVER=/home/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=32;TempSize=64;TypeMode=0;OracleNetServiceName=ttorcl;LogBufMB=32;
(Default setting AutoCommit=1)
Command> call ttrepstart;

Command> repschemes;

Replication Scheme REPADMIN.REPSCHEME1:

Element: DS1
Type: Datastore
Master Store: CACHEDB2 on TIMESTEN-HOL Transmit Durable
Subscriber Store: CACHEDB4 on TIMESTEN-HOL

Excluded Tables:
None

Excluded Cache Groups:
None

Excluded sequences:
None

Element: E
Type: Datastore
Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable
Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt
Subscriber Store: CACHEDB3 on TIMESTEN-HOL

Excluded Tables:
None

Excluded Cache Groups:
None

Excluded sequences:
None

Store: CACHEDB1 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled

Store: CACHEDB2 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled
Return Service Wait Time: 10 seconds
Return Service on Replication Stop: Disabled
Return Service Failure Policy: (none)

Store: CACHEDB3 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled

Store: CACHEDB4 on TIMESTEN-HOL
Port: (auto)
Log Fail Threshold: (none)
Retry Timeout: 120 seconds
Compress Traffic: Disabled

1 replication scheme found.

利用了classic replication，master库是可以读写的，只有在当前master库上产生的日志，才会复制到对方
例如cachedb1->cachedb2，但不会传递到cachedb4；例如cachedb2->cachedb4

增加PL/SQL对象到已有的scheme

  1. 在master库中创建PL/SQL对象，此create语句不会复制
  2. 在subscriber库中创建相同PL/SQL对象
  3. 在master库中为PL/SQL对象赋权，赋权语句被复制

从复制scheme中去除一个表或sequence

  • 基于DATASTORE的基准复制scheme
Command> drop table tthr.a3; 8002: Cannot drop replicated table, index, or sequenceThe command failed.ALTER REPLICATION repscheme1  ALTER ELEMENT E    EXCLUDE TABLE tthr.a3;DROP TABLE tthr.a3;
  • 基于TABLE的基准复制scheme
ALTER REPLICATION repscheme  DROP ELEMENT e1;

创建和添加subscriber

需要添加的subscriber为cachedb3, 确保cachedb3不存在
在cachedb1和cachedb2上皆执行，无需停复制代理

ALTER REPLICATION repscheme1  ALTER ELEMENT e    ADD SUBSCRIBER cachedb3;Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Subscriber Store: CACHEDB3 on TIMESTEN-HOL   Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)  Store: CACHEDB3 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled1 replication scheme found.$ ttRepAdmin -verbosity 2 -duplicate -from cachedb1 -host timesten-hol  -dsn cachedb3 -uid repadmin -pwd timesten05:06:57 Contacting remote main daemon at 127.0.0.1 port 5339205:06:57 Contacting the replication agent for CACHEDB1 ON TIMESTEN-HOL (127.0.0.1) port 1119305:06:57 Beginning transfer from CACHEDB1 ON TIMESTEN-HOL to CACHEDB3 ON TIMESTEN-HOL05:07:16 Checkpoint transfer 10 percent complete05:07:16 Checkpoint transfer 20 percent complete05:07:16 Checkpoint transfer 30 percent complete05:07:16 Checkpoint transfer 100 percent complete05:07:16 Checkpoint transfer phase complete05:07:17 Log transfer 100 percent complete05:07:17 Log transfer phase complete05:07:17 Transfer complete05:07:21 Duplicate Operation Ends[oracle@timesten-hol info]$ ttisql cachedb3Copyright (c) 1996, 2014, Oracle and/or its affiliates. All rights reserved.Type ? or "help" for help, type "exit" to quit ttIsql.connect "DSN=cachedb3";Connection successful: DSN=cachedb3;UID=oracle;DataStore=/home/oracle/TimesTen/tt1122/info/DemoDataStore/cachedb3;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;LogFileSize=32;DRIVER=/home/oracle/TimesTen/tt1122/lib/libtten.so;PermSize=32;TempSize=64;TypeMode=0;OracleNetServiceName=ttorcl;LogBufMB=32;(Default setting AutoCommit=1)Command> call ttrepstart;Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Subscriber Store: CACHEDB3 on TIMESTEN-HOL   Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)  Store: CACHEDB3 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled1 replication scheme found.Command>

删除subscriber库

ALTER REPLICATION repscheme1
ALTER ELEMENT E
DROP SUBSCRIBER cachedb3;

更改 Table element名字

ALTER REPLICATION r1  ALTER ELEMENT Eelem_westleads    SET NAME newelname;

更换master数据库???

ALTER REPLICATION repscheme1
ALTER ELEMENT * IN cachedb1
SET MASTER cachedb3;
ALTER ELEMENT * IN FullStoreName
SET { MASTER | PROPAGATOR } FullStoreName

去除return service

Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: DS1                              Type: Datastore  Master Store: CACHEDB2 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB4 on TIMESTEN-HOL   Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL Return Receipt  Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled    Return Service Wait Time: 10 seconds    Return Service on Replication Stop: Disabled    Return Service Failure Policy: (none)  Store: CACHEDB4 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled1 replication scheme found.ALTER REPLICATION repscheme1  ALTER ELEMENT E    ALTER SUBSCRIBER cachedb2      SET NO RETURN;Command> repschemes;Replication Scheme REPADMIN.REPSCHEME1:  Element: DS1                              Type: Datastore  Master Store: CACHEDB2 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB4 on TIMESTEN-HOL   Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Element: E                                Type: Datastore  Master Store: CACHEDB1 on TIMESTEN-HOL Transmit Durable  Subscriber Store: CACHEDB2 on TIMESTEN-HOL   Excluded Tables:    None  Excluded Cache Groups:    None  Excluded sequences:    None  Store: CACHEDB1 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB2 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled  Store: CACHEDB4 on TIMESTEN-HOL    Port: (auto)    Log Fail Threshold: (none)    Retry Timeout: 120 seconds    Compress Traffic: Disabled1 replication scheme found.

添加return service

ALTER REPLICATION repscheme1  ALTER ELEMENT E    ALTER SUBSCRIBER cachedb2      SET RETURN RECEIPT;

更改复制表

唯一支持复制的命令： ALTER TABLE … ADD COLUMN or ALTER TABLE … DROP COLUMN
如果是双向复制，就必须保证在更改期间没有数据写入
如果有多个alter table语句，就必须保证一个完成后再做下一个

truncate复制表

truncate table是可以复制的

删除复制策略

call ttrepstop;
drop replication repscheme1;

0 0
国家邮政局投诉知乎 国家邮政局投诉知乎
原创粉丝点击
热门IT博客
用手充电软件下载用手充电软件下载
全景图软件全景图软件
中国移动数据漫游
广安门中医院挂号软件
用户生命周期算法
杭州淘宝收费拍摄地点
最好网络理财
mac air 怎么设置壁纸
淘宝折叠椅
win7禁止安装任何软件
c语言中求绝对值的代码
网络arp检测工具
希捷维修软件
java手机游戏51
mac电脑卡机怎么解决
二手车交易发票软件
淘宝售前售后哪个轻松
点云稀释软件
windows能开发ios吗
黑道圣徒4优化
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 尿十项 求主人赐尿含金茎 皇帝瘫痪尿不出含玉 请主人赐尿含金茎 男主尿不出含玉小受 寻男主瘫痪尿不出含玉 少主赐尿含金茎 尿含蛋白是怎么回事 关于血尿 尿隐血高 尿含血 尿里含血是怎么回事 除尿味 尿味臭 尿味很臭 尿味臭是什么原因 小便尿味很臭什么原因 氟尿嘧啶注射剂 尿嘧啶 5氟尿嘧啶 5氟尿嘧啶说明书 氟尿嘧啶说明书 氟尿嘧啶注射液 氟尿嘧啶价格 尿嘧啶替加氟片 氟尿嘧啶乳膏 5-氟尿嘧啶 氟尿嘧啶软膏价格 氟尿嘧啶 奥沙利铂 复方尿囊素 尿囊素乳膏 尿囊素是激素吗 尿囊素维e乳膏 复方肝素钠尿囊素 尿囊素是什么 硫酸锌尿囊素滴眼液 尿囊素长期使用有什么危害 尿囊素维e乳膏的作用 复方肝素纳尿囊素凝胶 复方肝素钠尿囊素凝胶价格 尿囊素对皮肤的作用

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里