本例涉及两个站点.
主体定义站点(masterdef): QASJS.REGRESS.RDBMS.DEV.US.ORACLE.COM , 192.168.153.128
主体站点(master): QASJSS.REGRESS.RDBMS.DEV.US.ORACLE.COM , 192.168.153.133
注:主体定义站点指配置复制工作的站点
准备
-- 需要为TRUE
select * from v$option where parameter ='Advanced replication';
-- 记下global_name, 可通过
alter database rename global_name to修改, 需保证在同一域下(其实通过hosts直接指向IP也行)
select * from global_name;
show parameter db_domain;
-- 需要大于10
show parameter job_queue_processes;
-- 需要大于0, 连接越多此值越大
show parameter open_links;
-- 在两台主机的tns上分别添加对方的连接(通过Net Manager或者直接修改tnsnames.ora), 后边创建数据连接按此名字进行
创建复制用户并授权
-- 创建用于复制用户
create user repadmin identified by repadmin default tablespace qasjs 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;
创建数据连接
-- 主体定义站点(主库)
CREATE DATABASE LINK "
QASJSS.REGRESS.RDBMS.DEV.US.ORACLE.COM" CONNECT TO
" repadmin"
IDENTIFIED BY " repadmin"
USING ' qasjss';
" repadmin"
IDENTIFIED BY " repadmin"
USING ' qasjss';
-- 主体站点(复制库)
CREATE DATABASE LINK "
QASJS.REGRESS.RDBMS.DEV.US.ORACLE.COM" CONNECT TO
"repadmin"
IDENTIFIED BY "repadmin"
USING 'qasjs';
IDENTIFIED BY "repadmin"
USING 'qasjs';
-- 通过以下方式测试数据连接正确性
-- 在主库运行以下语句:
select * from global_name@qasjss.regress.rdbms.dev.us.oracle.com;
-- 在复制库运行以下语句:
select * from global_name@qasjs.regress.rdbms.dev.us.oracle.com;
-- 手工从主库同步初始数据, 注意一定要commit, 不然会影响后边同步.
insert into r_common.t_cc_branch select * from r_common.t_cc_branch@qasjs.regress.rdbms.dev.us.oracle.com;
commit;
实施复制
--用复制帐号登录, 创建主体复制组
execute dbms_repcat.create_master_repgroup('REP_MYTEST');
-- 查看复制主体组信息
select gname,master,status from dba_repgroup where gname='
REP_MYTEST';
-- 为主体组添加复制对象, 如还有其它复制表, 则增加一表就加一条语句
execute dbms_repcat.create_master_repobject(sname=>'R_COMMON',oname=>'T_CC_BRANCH', type=>'TABLE', use_existing_object=>true,gname=>'REP_MYTEST',copy_rows=>false);
-- 查看复制主体组复制对象相关信息
select sname,oname,status,gname from dba_repobject where gname='
REP_MYTEST';
-- 为复制对象生成复制支持, 如还有其它复制表, 则增加一表就加一条语句
execute dbms_repcat.generate_replication_support('R_COMMON','T_CC_BRANCH','TABLE');
execute dbms_repcat.generate_replication_support('R_COMMON','T_CC_BRANCH','TABLE');
-- 再次察看复制主体组和对应的复制对象的相关信息:
select gname,master,status from dba_repgroup where gname=' REP_MYTEST';
select gname,master,status from dba_repgroup where gname=' REP_MYTEST';
GNAME DBLINK MASTERDEF MASTER
------------------------------ -------------------------------------------------------------------------------- --------- ------
REP_MYTEST QASJS.REGRESS.RDBMS.DEV.US.ORACLE.COM Y Y
------------------------------ -------------------------------------------------------------------------------- --------- ------
REP_MYTEST QASJS.REGRESS.RDBMS.DEV.US.ORACLE.COM Y Y
select sname,oname,status,gname from dba_repobject where gname='
REP_MYTEST';
SNAME ONAME STATUS GNAME
------------------------------ ------------------------------ ---------- ------------------------------
R_COMMON T_CC_BRANCH VALID REP_MYTEST
R_COMMON T_CC_BRANCH$RP VALID REP_MYTEST
R_COMMON T_CC_BRANCH$RP VALID REP_MYTEST
------------------------------ ------------------------------ ---------- ------------------------------
R_COMMON T_CC_BRANCH VALID REP_MYTEST
R_COMMON T_CC_BRANCH$RP VALID REP_MYTEST
R_COMMON T_CC_BRANCH$RP VALID REP_MYTEST
-- 为复制主体组添加数据库主站点, 此时系统通过数据连接对主体站点(复制库)进行设置. 如此前通过remove_master_databases删除主体站点的话, 则有可能由于主体站点已有同名主体组报已存在主体组的错误, 此时在主体站点通过execute dbms_repcat.drop_master_repgroup('REP_MYTEST') 删除对应的组再重新添加即可.
execute dbms_repcat.add_master_database(gname=>'REP_MYTEST',master=>'QASJSS.REGRESS.RDBMS.DEV.US.ORACLE.COM', use_existing_objects=>true,copy_rows=>false, propagation_mode => 'synchronous');
execute dbms_repcat.add_master_database(gname=>'REP_MYTEST',master=>'QASJSS.REGRESS.RDBMS.DEV.US.ORACLE.COM', use_existing_objects=>true,copy_rows=>false, propagation_mode => 'synchronous');
-- 在复制库察看复制站点信息:
select gname,dblink,masterdef,master from dba_repsites where gname=' REP_MYTEST';
select gname,dblink,masterdef,master from dba_repsites where gname=' REP_MYTEST';
GNAME DBLINK MASTERDEF MASTER
------------------------------ -------------------------------------------------------------------------------- --------- ------
REP_MYTEST QASJS.REGRESS.RDBMS.DEV.US.ORACLE.COM Y Y
REP_MYTEST QASJSS.REGRESS.RDBMS.DEV.US.ORACLE.COM N Y
------------------------------ -------------------------------------------------------------------------------- --------- ------
REP_MYTEST QASJS.REGRESS.RDBMS.DEV.US.ORACLE.COM Y Y
REP_MYTEST QASJSS.REGRESS.RDBMS.DEV.US.ORACLE.COM N Y
-- 在主启动复制进程
execute dbms_repcat.resume_master_activity('REP_MYTEST',true);
-- 再次在主库和复制库察看复制主体组相关信息, 状态均为NORMAL则正常启动
select gname,master,status from dba_repgroup where gname='REP_MYTEST';
GNAME MASTER STATUS
------------------------------ ------ ---------
REP_MYTEST Y NORMAL
------------------------------ ------ ---------
REP_MYTEST Y NORMAL
最后测试
在主库对相关表进行增删改后, 观察复制库与主库一致则配置成功.
参考文章: