高级复制 入门

分别创建用户repadmin及db link
create user repadmin identified by repadmin default tablespace users temporary
tablespace temp;
execute dbms_defer_sys.register_propagator('repadmin');
--Register the given user as the propagator for the local database. It also grants
--to the given user CREATE SESSION, CREATE PROCEDURE, CREATE DATABASE LINK, and
--EXECUTE ANY PROCEDURE privileges (so that the user can create wrappers).
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
--The purpose of this procedure is to grant the necessary privileges to the
--replication administrator to administer any replicated object group at the current site.
--grant_admin_repgroup
--The purpose of this procedure is to grant the necessary privileges to the
--replication administrator to administer a schema at the current site.
--This procedure is most useful if your object group does not span schemas.
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
--This procedure grants the necessary privileges to the replication administrator
--to administer any replicated object group at the current site.
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;

db link报异常 ora-02085  参考解决方案ora-02085.txt

分别创建复制表
create table  reptab (repid number,repvar varchar2(10));
alter table reptab add(constraint pk_id primary key(repid));

主体定义站点
创建复制组:
--The purpose of this procedure is to create a new, empty,
--quiesced master replication object group.
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
   gname             IN VARCHAR2,
   group_comemnt     IN VARCHAR2 : = ' ' ,
   master_comment    IN VARCHAR2 : =  ' '  )

SQL> execute dbms_repcat.create_master_repgroup('rep_ms');
PL/SQL procedure successfully completed.

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

GNAME                          M STATUS
------------------------------ - ---------
REP_MS                         Y QUIESCED
在复制组里加入复制对象
----The purpose of this procedure is to indicate that an object is a replicated object.
SQL> execute dbms_repcat.create_master_repobject
(sname=>'hr',--The name of the schema in which the object that you want to replicate is located
oname=>'reptab', --The name of the object that you are replicating
type=>'table',--The types supported are: TABLE, INDEX, SYNONYM, TRIGGER, VIEW, PROCEDURE, FUNCTION, PACAGE and PACKAGE BODY.
use_existing_object=>true,--Indicate TRUE if you want to reuse any objects of the same type and shape at the current master sites.
gname=>'rep_ms',
--The name of the object group in which you want to create the replicated object
copy_rows=>false
--Indicate TRUE if you want the initial contents of a newly replicated object to
--match the contents of the object at the master definition site.
);

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

SNAME                          ONAME                          STATUS
------------------------------ ------------------------------ ----------
GNAME
------------------------------
HR                             REPTAB                         VALID
REP_MS

对复制对象产生复制支持
--The purpose of this procedure is to generate the triggers, packages, and
--procedures needed to support replication. You must call this procedure from
--the master definition site.
SQL> execute dbms_repcat.generate_replication_support('hr','reptab','table');
--The type of the object. The types supported are: TABLE, PACKAGE and PACKAGE BODY.
PL/SQL procedure successfully completed.

查看复制对象
SQL> select sname,oname,status,gname from dba_repobject where gname='REP_MS';

SNAME      ONAME                STATUS     GNAME
---------- -------------------- ---------- ------------------------------
HR         REPTAB               VALID      REP_MS
HR         REPTAB$RP            VALID      REP_MS
HR         REPTAB$RP            VALID      REP_MS

测试：
切到hr 插入数据
SQL> conn hr/hr
Connected.
SQL> insert into reptab values(1,'hello');
insert into reptab values(1,'hello')
            *
ERROR at line 1:
ORA-23326: object group "PUBLIC"."REP_MS" is quiesced
切回主体定义站点
添加主体复制节点
--The purpose of this procedure is to add another master site to your replicated
--environment. This procedure regenerates all the triggers and their associated
--packages at existing master sites. You must call this procedure from the master
--definition site.
SQL> execute dbms_repcat.add_master_database(
gname=>'rep_ms',
master=>'rep2',
use_existing_objects=>true,
copy_rows=>false,
propagation_mode => 'synchronous');

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_MS';

GNAME        DBLINK                    MASTERDEF  MASTER
------------ ------------------------- ---------- ----------
REP_MS       REP1.COM                  Y          Y
REP_MS       REP2.COM                  N          Y

切到主体站点查看复制情况

SQL> column sname format a10;
SQL> column oname format a20;
SQL> select sname,oname,status,gname from dba_repobject where gname='REP_MS';

SNAME      ONAME                STATUS     GNAME
---------- -------------------- ---------- ------------------------------
HR         REPTAB               VALID      REP_MS
HR         REPTAB$RP            VALID      REP_MS
HR         REPTAB$RP            VALID      REP_MS

在主体定义站点启动复制:
--The purpose of this procedure is to resume normal replication activity after
--quiescing a replicated environment.
SQL> execute dbms_repcat.resume_master_activity('rep_ms',true);

验证
切到主体定义站点hr 插入数据
PL/SQL procedure successfully completed.
SQL> conn hr/hr
Connected.
SQL> insert into reptab values(1,'hello');

1 row created.

SQL> commit;

Commit complete.

查看主体站点查看hr
SQL> select * from hr.reptab;                                 

no rows selected

SQL> /

     REPID REPVAR
---------- ----------
         1 hello

分别测试insert delete  update  truncate
         有效   有效    有效     无效
适用于DML
分别调整表结构后
alter table reptab add repval varchar2(10);
dml操作依然生效


重启所有节点后
观察复制组状态
SQL> select gname,master,status from dba_repgroup where gname='REP_MS';

GNAME                          M STATUS
------------------------------ - ---------
REP_MS                         Y NORMAL
--说明复制组是处于激活状态
测试新增数据发现
上一次实验中新增的字段没有复制到目的地。
尝试 挂起复制组  重启装载复制对象

[1]挂起复制组
The purpose of this procedure is to suspend replication activity for an object group. You must call this procedure from the master definition site.
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY  ( gname IN VARCHAR2, execute_as_user   IN BOOLEAN : =  > FALSE,

SQL> exec DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY('rep_ms');

PL/SQL procedure successfully completed.

--观察定义站点变化
SQL> select gname,master,status from dba_repgroup where gname='REP_MS';

GNAME                          M STATUS
------------------------------ - ---------
REP_MS                         Y QUIESCED

--观察站点变化
SQL> select gname,master,status from dba_repgroup where gname='REP_MS';

GNAME                          M STATUS
------------------------------ - ---------
REP_MS                         Y QUIESCED

[2.1]尝试修改复制对象
The purpose of this procedure is to alter an object in your replicated environment. You must call this procedure from the master definition site.
DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
   sname      IN VARCHAR2,
   oname      IN VARCHAR2,
   type       IN VARCHAR2,
   ddl_text   IN VARCHAR2,
   comment    IN VARCHAR2 : = ' ',
   retry      IN BOOLEAN : = FALSE );


DBMS_REPCAT.ALTER_MASTER_REPOBJECT('hr','reptab','table','alter table reptab add repval1 varchar2(10)')

fail
[2.2]尝试重建复制对象
The purpose of this procedure is to drop a replicated object from a replicated object group. You must call this procedure from the master definition site.

Syntax

DBMS_REPCAT.DROP_MASTER_REPOBJECT (
   sname             IN VARCHAR2,
   oname             IN VARCHAR2,
   type              IN VARCHAR2,
   drop_objects      IN BOOLEAN : = FALSE )


定义站点
SQL> exec DBMS_REPCAT.DROP_MASTER_REPOBJECT('hr','reptab','table');

PL/SQL procedure successfully completed.

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

no rows selected
主体站点 同上

添加复制对象即支持

exec DBMS_REPCAT.CREATE_MASTER_REPOBJECT(sname=>'hr',oname=>'reptab',type=>'table',use_existing_object=>true,gname=>'rep_ms',copy_rows=>false);
execute dbms_repcat.generate_replication_support('hr','reptab','table');
select sname,oname,status,gname from dba_repobject where gname='REP_MS';

SQL> insert into reptab values(11,'no','man');
insert into reptab values(11,'no','man')
            *
ERROR at line 1:
ORA-23326: object group "PUBLIC"."REP_MS" is quiesced

[3]测试
SQL> conn repadmin/repadmin                                   
Connected.
SQL> execute dbms_repcat.resume_master_activity('rep_ms',true);

PL/SQL procedure successfully completed.

SQL> conn hr/hr
Connected.
SQL>  insert into reptab values(11,'no','man');

1 row created.

SQL> commit;

Commit complete.


主体站定

SQL> select * from hr.reptab;

     REPID REPVAR     REPVAL
---------- ---------- ----------
         7 hello
         8 no
        11 no         man
         1 hello
         3 world












附：

使用数据连接可以很方便的引用其它数据库的数据，但是设置不当可能遇到ORA-02085错误。
如果被连接对方的GLOBAL_NAMES参数设置成了TRUE，那么要求数据库连接与对方实例名有相同的
名称。这样就可以通过下面3中的任意一种方法来解决：
1.修改对方的GLOBAL_NAMES参数为FALSE
2.将对方的GLOBAL_NAME设置成与数据库连接相同的名称
验证：
select * from global_name;查看global_name

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
REP1.COM--该数据库的域名是com
保证双方的域名一致
建db link时数据库连接与对方实例名要一致 否则
SQL> CREATE   DATABASE LINK rac2 connect to repadmin identified by repadmin USING 'rep2';

Database link created.

SQL> select * from global_name@rac2;--对方实例是rep2
select * from global_name@rac2
                          *
ERROR at line 1:
ORA-02085: database link RAC2.COM connects to REP2.COM。

3.将数据库连接删掉重新创建成与对方实例名相同的数据库连接

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

转载于:http://blog.itpub.net/21993926/viewspace-673096/