主体定义站点(源):eygle.test.com.cn
主体站点(目的):biti.test.com.cn
1)sys--创建复制管理员(以下像这样不写哪里做的,在两边都做,先eygle后biti)
SQL> create user repadmin identified by oracle default tablespace users;
SQL> create public database link eygle.test.com.cn connect to repadmin identified by oracle using 'eygle';
SQL> create table cjh (
2 id number not null,
3 name char(20),
4 content varchar2(512),
5 day date);
19)cjh--在biti上,数据变化
主体站点(目的):biti.test.com.cn
复制管理员:repadmin
应用用户:cjh
复制的对象:cjh数据表
复制组:rep_cjh
应用用户: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
------------------------------------ ----------- ------------------------------
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
------------------------------------ ----------- ------------------------------
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);
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 /
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这里的表手工同步数据
已创建100行。
SQL> commit;
提交完成。
12)repadmin--在eygle上,创建复制组
SQL> select * from global_name;
GLOBAL_NAME
----------------------------------------------------------------------------------------------------
EYGLE.TEST.COM.CN
----------------------------------------------------------------------------------------------------
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
------------------------------ - ---------
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
------------------------------ ------------------------------ ---------- ------------------------------
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
------------------------------ - ---------
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
------------------------------ ------------------------------ ---------- ------------------------------
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> 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
------------ ------------------------- ---------- ----------
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
---------------------------------
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
------------------------------ ------------------------------ ---------- ------------------------------
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
----------------------------------------------------------------------------------------------------
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
----------
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
----------
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/