`
- 浏览:
2125466 次
- 性别:
- 来自:
深圳
-
Oracle 多主(Master Replication)复制配置
一、环境
windows server 2003 sp1;10g 10.1.0.2主体定义站点:SID:db1 IP:10.1.8.201主体站点:SID:db2 IP:10.1.9.49二、配置
1. db1 上的操作
-- 确保下面参数配置
db_domain='mytest.com'
global_names=true
-- tnsnames.ora 配置
DB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.49)(PORT = 1521))
)
(CONNECT_DATA =
(SID = db2)
(GLOBAL_NAME = mytest.com)
(SERVER = DEDICATED)
)
)
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.201)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1.mytest.com)
)
)
conn system
-- 修改 global name
alter database rename global_name to db1.mytest.com;
-- 建立公共 db link
create public database link db2.mytest.com using 'db2';
-- 检查
select * from global_name@db2.mytest.com;
-- 建立复制管理员并授权
create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;
conn repadmin
-- 建立 db link
create database link db2.mytest.com connect to repadmin identified by repadmin;
-- 检查
select * from global_name@db2.mytest.com;
conn system
-- 建立测试用户并授权
create user testuser identified by testuser default tablespace users temporary tablespace temp;
grant connect, resource to testuser;
conn testuser
-- 建立测试用表
create table dept
(deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13) );
2. 在 db2 上进行与相似的操作
db_domain='mytest.com'
global_names=true
DB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.201)(PORT = 1521))
)
(CONNECT_DATA =
(SID = db1)
(GLOBAL_NAME = mytest.com)
(SERVER = DEDICATED)
)
)
DB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.49)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db2.mytest.com)
)
)
conn system
alter database rename global_name to db2.mytest.com;
create public database link db1.mytest.com using 'db1';
select * from global_name@db1.mytest.com;
create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;
conn repadmin
create database link db1.mytest.com connect to repadmin identified by repadmin;
select * from global_name@db1.mytest.com;
conn system
create user testuser identified by testuser default tablespace users temporary tablespace temp;
grant connect, resource to testuser;
conn testuser
create table dept
(deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13) );
3. 在 db1 上配置复制
conn repadmin
-- 创建复制组
execute dbms_repcat.create_master_repgroup('testuser_mg');
-- 检查
select gname,master,status from dba_repgroup where gname='TESTUSER_MG';
-- 在复制组里加入复制对象
execute dbms_repcat.create_master_repobject(sname=>'testuser',oname=>'dept', type=>'table',use_existing_object=>true,gname=>'testuser_mg',copy_rows=>false);
-- 检查
select sname,oname,status,gname from dba_repobject where gname='testuser_mg';
-- 对复制对象产生复制支持
execute dbms_repcat.generate_replication_support('testuser','dept','table');
-- 检查
select gname, master, status from dba_repgroup where gname='TESTUSER_MG';
select sname,oname,status,gname from dba_repobject where gname='TESTUSER_MG';
-- 添加主体复制节点
execute dbms_repcat.add_master_database (gname=>'testuser_mg',master=>'db2.mytest.com',use_existing_objects=>true, copy_rows=>false, propagation_mode => 'synchronous');
-- 检查
column masterdef format a10
column master format a10
column dblink format a25
column gname format a12
select gname, dblink, masterdef MASTERDEF, master MASTER from sys.dba_repsites where gname='TESTUSER_MG';
-- 启动复制
execute dbms_repcat.resume_master_activity('testuser_mg',true);
-- 检查
select gname,master,status from dba_repgroup where gname='TESTUSER_MG';
-- repadmin 用登录主体站点,检查复制对象情况
set linesize 120
select sname,oname,status,gname from dba_repobject where gname='TESTUSER_MG';
4. 测试
-- 在 db1 上增加数据
conn testuser
insert into dept values (1,'accounting','new york');
insert into dept values (2,'research','dallas');
insert into dept values (3,'sales','chicago');
insert into dept values (4,'operations','boston');
commit;
-- 检查 db2 的数据变化
conn testuser
select * from dept;
-- 在 db1 上进行表结构修改(DDL)
EXECUTE DBMS_REPCAT.EXECUTE_DDL(gname => 'testuser_mg',ddl_text => 'truncate table testuser.dept');
EXECUTE DBMS_REPCAT.EXECUTE_DDL(gname => 'testuser_mg',ddl_text => 'ALTER TABLE TESTUSER.DEPT ADD c1 NUMBER');
EXECUTE DBMS_REPCAT.EXECUTE_DDL(gname => 'testuser_mg',ddl_text => 'ALTER TABLE TESTUSER.DEPT DROP (C1)');
-- 检查 db2 的数据变化
conn testuser
desc dept
select * from dept;
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
一本关于Oracle Advanced Replication的书
Oracle 高级复制配置手册 Materialized View Replication 复制模式实现了单主机对多个复制站点的数据同步.在主站点(即被复 制主机)需要建立只读用户供复制站点来访问.
DB2 Q replication配置方法。经本人测试通过。 DB2 Q replication配置方法。经本人测试通过。 DB2 Q replication配置方法。经本人测试通过。 DB2 Q replication配置方法。经本人测试通过。
stream replication 双向复制安装配置文档
流复制主要是利用ORACLE的归档日志,进行增量备份来实现的,不仅可以配置只复制某些表,还可以配置仅复制某些表上的ddl或dml。可以复制到表,用户,数据库级别。 高级复制主要是基于触发器的原理来触发数据同步的...
安装Mysql mysql数据库一共两套集群,分别用于电子合同系统和流程引擎系统, 两套mysql数据库集群分别部署到两台服务器上(10.249.7.18,10.249.23.9)
oracle advanced replication operation
Veeam Backup & Replication几种备份方式详解,到此,几种备份方式讲解完毕,刚开始学习Veeam备份,记录下自己学习的内容。欢迎大家交流,如有错误还望指教。
mongoDB Replication 复制模式官网指导文档
mysql replication修改库名及复制单个表
主要介绍了mysql Multi-Master Replication Manager for MySQL的安装方法,需要的朋友可以参考下
oracle 10g stream双向数据复制,供有需要的参考。 不可多得的好资料。
我整理的oracle高级复制文档,包括advanced replication初步研究,Oracle 高级数据复制案例,Oracle数据库中高级复制的功能介绍,Oracle高级复制维护中要注意的方面,使用高级复制时清除Deferred Queue的办法
Replication Server 的DB2_Q复制配置说明
胖子摸索出来的,Ubuntu上MySQL的Replication配置,的简单记录步骤
详细描述mysql-replication配置,mysql主从库切换,验证等
在9iR2之前高级复制应用比较广泛,高级复制也分两种:多主复制和基于物化视图的复制,就我理解其最大的劣势是大数据量下效率堪优,并且对于 ddl的支持不够友好。从技术实现思路上与streams几无相同之处,倒是逻辑...
Oracle Stream功能是为提高数据库的高可用性而设计的,在Oracle 9i及之前的版本这个功 能被称为Advance Replication。Oracle Stream利用高级队列技术,通过解析归档日志,将归 档日志解析成DDL 及DML 语句,...
Oracle8i Replication Release 2 (8.1.6)