多主体复制站点的配置主要包括以下几步:
a、创建复制站点
a、创建复制站点
b、创建组对象
c、配置冲突解决方案
下面我们举一个例子来说明各步具体需要完成的工作。在这个例子中我们采用多主控站点
复制方式,设有两个主控站点和一个数据表(class)。两个主控站点分别为:处理站点
(orcl.com)和解释站点(data.com);数据表为(class)。
STEP1 创建复制站点
(1)首先以SYSTEM身份登陆主站点数据库orcl.com:CONNECT system/sys@orcl
(2)创建用户—复制管理员,并为该用户授权复制管理员负责复制站点的创建和管理,每个复
制站点都必须创建复制管理员:
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => 'repadmin');
END;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => 'repadmin');
END;
(3)为本站点指定传播者
传播者负责将本地最新更新的数据传播到其他站点上:
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');
END;
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');
END;
(4)为本站点指定接收者
接收者负责接收其他站点上的传播者传送过来的数据:
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
在创建好站点orcl.com的复制用户之后,以同样的方式创建站点data.com的复制用户
CONNECT system/sys@data
CONNECT system/sys@data
CREATE USER repadmin IDENTIFIED BY repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => 'repadmin');
END;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (username => 'repadmin');
END;
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');
END;
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (username => 'repadmin');
END;
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
(5)确定清除时间
为了使传送过来事务队列不致过大,需要将成功加载的事务从事物队列里清除掉,这里设
定每小时清除一次。
CONNECT repadmin/repadmin@orcl
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
在建立好站点orcl.com后,以同样的方法创建站点data.com。
CONNECT repadmin/repadmin@data
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
(6)创建各主控站点之间的调度链接
创建各主控站点之间的调度链接需要先在各主控站点间建立数据库链接,之后为每个数据
库链接定义调度时间。
首先,在处理站点上建立与解释站点的数据库链接,这里需要先建立一个公用数据库链接
,供其他私有数据库链接来使用。
CONNECT system/sys@orcl
CREATE PUBLIC DATABASE LINK "data.com" USING 'data';
CONNECT repadmin/repadmin@orcl
CREATE DATABASE LINK "data.com" CONNECT TO repadmin IDENTIFIED BY repadmin;
CONNECT system/sys@orcl
CREATE PUBLIC DATABASE LINK "data.com" USING 'data';
CONNECT repadmin/repadmin@orcl
CREATE DATABASE LINK "data.com" CONNECT TO repadmin IDENTIFIED BY repadmin;
同样,在解释站点上建立与处理站点的数据库链接
CONNECT system/sys@data
CREATE PUBLIC DATABASE LINK "orcl.com" USING 'elite';
CONNECT repadmin/repadmin@data
CREATE DATABASE LINK "orcl.com" CONNECT TO repadmin IDENTIFIED BY repadmin;
CONNECT system/sys@data
CREATE PUBLIC DATABASE LINK "orcl.com" USING 'elite';
CONNECT repadmin/repadmin@data
CREATE DATABASE LINK "orcl.com" CONNECT TO repadmin IDENTIFIED BY repadmin;
创建完之后,用下列方法测试数据库链接是否创建成功:
CONNECT ocean/ocean@orcl
select * from class@data.com
CONNECT ocean/ocean@data
select * from class@orcl.com
CONNECT ocean/ocean@orcl
select * from class@data.com
CONNECT ocean/ocean@data
select * from class@orcl.com
STEP2 创建组对象
(1)创建主控组对象
CONNECT repadmin/repadmin@orcl
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'inte_repg');
END;
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'inte_repg');
END;
(2)向主控组中添加数据对象,将表class加入到组inte_repg中
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'inte_repg',
type => 'TABLE',
oname => 'class',
sname => 'ocean',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'inte_repg',
type => 'TABLE',
oname => 'class',
sname => 'ocean',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
(3)在主控组中添加其他参与复制的站点,数据库之间的同步方式在此指定
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'inte_repg',
master => 'data.com',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => 'inte_repg',
master => 'data.com',
use_existing_objects => TRUE,
copy_rows => FALSE,
propagation_mode => 'ASYNCHRONOUS');
END;
(4)如果可能出现冲突,则需要配置冲突解决方案。冲突解决方案将在后面介绍。
(5)为每个对象生成复制支持
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'ocean',
oname => 'class',
type => 'TABLE',
min_communication => TRUE);
END;
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'ocean',
oname => 'class',
type => 'TABLE',
min_communication => TRUE);
END;
(6)重新开始复制
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'inte_repg');
END;
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'inte_repg');
END;
(7)调度链接确定本站点上的事务向其他站点发送的频度,下面的代码为10分钟一次:
CONNECT repadmin/repadmin@orcl
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'data.com',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'data.com',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
CONNECT repadmin/repadmin@data
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'orcl.com',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'orcl.com',
interval => 'SYSDATE + (1/144)',
next_date => SYSDATE,
parallelism => 1,
execution_seconds => 1500,
delay_seconds => 1200);
END;