Oracle高级复制的创建配置步骤-Step by Step

来自：http://www.eygle.com/archives/2005/06/oraclessoeaeaeo.html

本例涉及两个站点.
主体定义站点:AVATAR.COOLYOUNG.COM.CN
主体站点:AUTHAA.COOLYOUNG.COM.CN
注:主体定义站点指配置复制工作的站点

本例涉及的用户.
复制管理员:repadmin
应用用户:hawa
本例复制的对象:hw_test 数据表
本例的先决条件:你需要设置好相应的参数,job_queue_processes需要大于0,global_name=true,并且建立相应的db link.
这些具体可以参考:高级复制的初步研究一文.

1.在主体定义站点复制用户下创建复制对象

SQL> select * from global_name; GLOBAL_NAME ----------------------------------------------------------- AVATAR.COOLYOUNG.COM.CN SQL> connect hawa/password Connected. SQL> create table hw_test as select * from hw_online; Table created. SQL> select count(*) from hw_test; COUNT(*) ---------- 464 SQL> alter table hw_test add (constraint pk_userid primary key (numuserid)); Table altered.

2.在主体站点同样创建复制对象

SQL> select * from global_name; GLOBAL_NAME ------------------------------------------------------------------------ AUTHAA.COOLYOUNG.COM.CN SQL> connect hawa/password Connected. SQL> create table hw_test as select * from hw_online; Table created. SQL> select count(*) from hw_test; COUNT(*) ---------- 0

3.在主体站点手工同步数据

SQL> connect repadmin/password Connected. SQL> insert into hawa.hw_test select * from hawa.hw_test@avatar; 464 rows created. SQL> commit; Commit complete.

4.在主体定义站点开始操作
登陆主体定义站点

$ sqlplus repadmin/password SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 27 09:32:36 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> select * from global_name; GLOBAL_NAME ---------------------------------------------------------------------------- AVATAR.COOLYOUNG.COM.CN

创建复制组:

SQL> execute dbms_repcat.create_master_repgroup('rep_hh'); PL/SQL procedure successfully completed. SQL> select gname,master,status from dba_repgroup where gname='REP_HH'; GNAME M STATUS ------------------------------ - --------- REP_HH Y QUIESCED

在复制组里加入复制对象

SQL> execute dbms_repcat.create_master_repobject (sname=>'hawa',oname=>'hw_test', type=>'table',use_existing_object=>true,gname=>'rep_hh',copy_rows=>false); PL/SQL procedure successfully completed. SQL> select sname,oname,status,gname from dba_repobject where gname='REP_HH'; SNAME ONAME STATUS GNAME ------------------------------ ------------------------------ ---------- ------------------------------ HAWA HW_TEST VALID REP_HH

对复制对象产生复制支持

SQL> execute dbms_repcat.generate_replication_support('hawa','hw_test','table'); PL/SQL procedure successfully completed. SQL> select gname, master, status from dba_repgroup where gname='REP_HH'; GNAME M STATUS ------------------------------ - --------- REP_HH Y QUIESCED SQL> select sname,oname,status,gname from dba_repobject where gname='REP_HH'; SNAME ONAME STATUS GNAME ------------------------------ ------------------------------ ---------- ------------------------------ HAWA HW_TEST VALID REP_HH HAWA HW_TEST$RP VALID REP_HH HAWA HW_TEST$RP VALID REP_HH SQL>

添加主体复制节点

SQL> execute dbms_repcat.add_master_database (gname=>'rep_hh',master=>'AUTHAA.COOLYOUNG.COM.CN',use_existing_objects=>true, copy_rows=>false, propagation_mode => 'synchronous'); PL/SQL procedure successfully completed. SQL> column masterdef format a10 SQL> column master format a10 SQL> column dblink format a25 SQL> column gname format a12 SQL> select gname, dblink, masterdef MASTERDEF, master MASTER from sys.dba_repsites where gname='REP_HH'; GNAME DBLINK MASTERDEF MASTER ------------ ------------------------- ---------- ---------- REP_HH AVATAR.COOLYOUNG.COM.CN Y Y REP_HH AUTHAA.COOLYOUNG.COM.CN N Y

登陆主体站点，检查复制对象情况:

[oracle@www167 oracle]$ sqlplus repadmin/password SQL*Plus: Release 9.2.0.4.0 - Production on Mon Jun 27 09:34:49 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning option JServer Release 9.2.0.4.0 - Production SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- AUTHAA.COOLYOUNG.COM.CN SQL> set linesize 120 SQL> select sname,oname,status,gname from dba_repobject where gname='REP_HH'; SNAME ONAME STATUS GNAME ------------------------------ ------------------------------ ---------- ------------------------------ HAWA HW_TEST VALID REP_HH HAWA HW_TEST$RP VALID REP_HH HAWA HW_TEST$RP VALID REP_HH

在主体定义站点启动复制:

SQL> select * from global_name; GLOBAL_NAME ----------------------------------------------------------------------------- AVATAR.COOLYOUNG.COM.CN SQL> execute dbms_repcat.resume_master_activity('rep_hh',true); PL/SQL procedure successfully completed.

在主体定义站点删除数据测试:

SQL> connect hawa/password Connected. SQL> select count(*) from hw_test; COUNT(*) ---------- 464 SQL> delete from hw_test where rownum <20; 19 rows deleted. SQL> commit; Commit complete. SQL> select count(*) from hw_test; COUNT(*) ---------- 445

在主体站点观察变化:

SQL> select * from global_name; GLOBAL_NAME -------------------------------------------------------------------------------- AUTHAA.COOLYOUNG.COM.CN SQL> select count(*) from hw_test; COUNT(*) ---------- 445 SQL>

至此，同步复制配置完毕。