高级复制 <一>

主体定义站点（源）：eygle.test.com.cn
主体站点（目的）：biti.test.com.cn

复制管理员:repadmin
应用用户:cjh
复制的对象:cjh数据表
复制组:rep_cjh

1)sys－－创建复制管理员(以下像这样不写哪里做的，在两边都做，先eygle后biti)

SQL> create user repadmin identified by oracle default tablespace users;

User created.

SQL> create user repadmin identified by oracle default tablespace users;

用户已创建

 

2)sys－－给复制管理员权限

SQL> execute dbms_defer_sys.register_propagator('repadmin');

PL/SQL procedure successfully completed.

SQL> grant execute any procedure to repadmin;

Grant succeeded.

SQL> execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');

PL/SQL procedure successfully completed.

SQL> execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');

PL/SQL procedure successfully completed.

SQL> grant comment any table to repadmin;

Grant succeeded.

SQL> grant lock any table to repadmin;

Grant succeeded.

SQL> grant select any dictionary to repadmin;

Grant succeeded.

 

SQL> execute dbms_defer_sys.register_propagator('repadmin');

PL/SQL 过程已成功完成。

SQL> grant execute any procedure to repadmin;

授权成功。

SQL> execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');

PL/SQL 过程已成功完成。

SQL> execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');

PL/SQL 过程已成功完成。

SQL> grant comment any table to repadmin;

授权成功。

SQL> grant lock any table to repadmin;

授权成功。

SQL> grant select any dictionary to repadmin;

授权成功。

 

3)sys－－改global_names的value为true

SQL> alter system set global_names=true;

System altered.

SQL> show  parameter  global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
global_names                         boolean     TRUE

 

SQL> alter system set global_names=true;

系统已更改。

SQL> show  parameter  global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
global_names                         boolean     TRUE

 

4)sys－－改global_name为SID+域名

SQL> alter database rename global_name to eygle.test.com.cn;

Database altered.

SQL> alter database rename global_name to biti.test.com.cn;

数据库已更改。

 

5)sys－－建公用dblink

SQL> create public database link biti.test.com.cn connect to repadmin identified by oracle using 'biti';

Database link created.

SQL> create public database link eygle.test.com.cn connect to repadmin identified by oracle using 'eygle';

数据库链接已创建。

 

6)sys－－建私有dblink

SQL> create database link biti.test.com.cn connect to repadmin identified by oracle;

Database link created.

SQL> create database link eygle.test.com.cn connect to repadmin identified by oracle;

数据库链接已创建。

 

7)sys－－创建用户

SQL> create user cjh identified by oracle default tablespace users;

User created.

SQL> create user cjh identified by oracle default tablespace users;

用户已创建

 

8)sys－－给用户权限

SQL> grant connect,resource to cjh;

Grant succeeded.

SQL> grant connect,resource to cjh;

授权成功。

 

9)cjh－－建表，加主键

SQL> conn cjh/oracle
Connected.
SQL> create table cjh (
  2  id          number        not null,
  3  name        char(20),        
  4  content     varchar2(512),
  5  day         date);

Table created.

SQL> alter table cjh add(constraint pk_id primary key (id));

Table altered.

SQL> create table cjh (
  2  id          number        not null,
  3  name        char(20),
  4  content     varchar2(512),
  5  day         date);

表已创建。

SQL> alter table cjh add(constraint pk_id primary key (id));

表已更改。

 

10)cjh－－给eygle这里的表cjh插入数据

SQL> begin
  2  for i in 1 .. 100 loop
  3  insert into cjh values(i,'eygle','this is a test'||to_char(i),sysdate);
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

 

11)repadmin－－给biti这里的表手工同步数据

SQL> connect repadmin/oracle
已连接。
SQL> insert into cjh.cjh select * from cjh.cjh@eygle;

已创建100行。

SQL> commit;

提交完成。

 

12)repadmin－－在eygle上，创建复制组

SQL> select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
EYGLE.TEST.COM.CN

SQL> execute dbms_repcat.create_master_repgroup('rep_cjh');

PL/SQL procedure successfully completed.

SQL> select gname,master,status from dba_repgroup where gname='REP_CJH';

GNAME                          M STATUS
------------------------------ - ---------
REP_CJH                        Y QUIESCED

 

13)repadmin－－在eygle上，往复制组里加入复制对象:

SQL> execute dbms_repcat.create_master_repobject(sname=>'cjh',oname=>'cjh', type=>'table',use_existing_object=>true,gname=>'rep_cjh',copy_rows=>false);

PL/SQL procedure successfully completed.

SQL> select sname,oname,status,gname from dba_repobject where gname='REP_CJH';

SNAME                          ONAME                          STATUS     GNAME
------------------------------ ------------------------------ ---------- ------------------------------
CJH                            CJH                            VALID      REP_CJH

 

14)repadmin－－在eygle上，对复制对象产生复制支持:

SQL> execute dbms_repcat.generate_replication_support('cjh','cjh','table');

PL/SQL procedure successfully completed.

SQL> select gname,master,status from dba_repgroup where gname='REP_CJH';

GNAME                          M STATUS
------------------------------ - ---------
REP_CJH                        Y QUIESCED

SQL> select sname,oname,status,gname from dba_repobject where gname='REP_CJH';

SNAME                          ONAME                          STATUS     GNAME
------------------------------ ------------------------------ ---------- ------------------------------
CJH                            CJH                            VALID      REP_CJH
CJH                            CJH$RP                         VALID      REP_CJH
CJH                            CJH$RP                         VALID      REP_CJH

 

15)repadmin－－在eygle上，添加主体复制节点，这里选择同步synchronous模式，实时的同步：

SQL> execute dbms_repcat.add_master_database(gname=>'rep_cjh',master=>'BITI.TEST.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_CJH';

GNAME        DBLINK                    MASTERDEF  MASTER
------------ ------------------------- ---------- ----------
REP_CJH      EYGLE.TEST.COM.CN         Y          Y
REP_CJH      BITI.TEST.COM.CN          N          Y

 

16)repadmin－－在biti上，检查复制对象情况

SQL> select * from global_name;

GLOBAL_NAME
---------------------------------
BITI.TEST.COM.CN

SQL> select sname,oname,status,gname from dba_repobject where gname='REP_CJH';

SNAME                          ONAME                          STATUS     GNAME
------------------------------ ------------------------------ ---------- ------------------------------
CJH                            CJH                            VALID      REP_CJH
CJH                            CJH$RP                         VALID      REP_CJH
CJH                            CJH$RP                         VALID      REP_CJH

 

17)repadmin－－在eygle上，启动复制

SQL> select * from global_name;

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
EYGLE.TEST.COM.CN

SQL> execute dbms_repcat.resume_master_activity('rep_cjh',true);

PL/SQL procedure successfully completed.

 

18)cjh－－在eygle上，删除数据测试

SQL> select count(*) from cjh.cjh;

  COUNT(*)
----------
       100

SQL> delete from cjh.cjh where rownum<20;

19 rows deleted.

SQL> commit;

Commit complete.

19)cjh－－在biti上，数据变化

SQL> select count(*) from cjh.cjh;

  COUNT(*)
----------
        81

 

20)同时，在biti这里改变数据，eygle这里也会同步过来，测试通过。

 

以上步骤参考eygle的blog： http://www.eygle.com/archives/2005/06/oraclessoeaeaeo.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11417069/viewspace-683902/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11417069/viewspace-683902/