-- 检查source 与 target 数据库参数


SQL> select name,db_unique_name from v$database;


NAME      DB_UNIQUE_NAME

--------- ------------------------------

PREMIUMI  PREMIUMIT


SQL> show parameter global_name


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     TRUE

SQL> select * from global_name;


GLOBAL_NAME

--------------------------------------------------------------------------------

PREMIUMI


SQL> show parameter job_queue_processes


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

job_queue_processes                  integer     1000




--在source 与 target 数据库创建同步账号


SQL> create user repadmin identified by repadmin;


User created.



SQL> execute dbms_repcat_admin.grant_admin_any_schema('REPADMIN');


PL/SQL procedure successfully completed.


SQL> execute dbms_repcat_admin.grant_admin_any_schema('REPADMIN');


PL/SQL procedure successfully completed.


SQL> grant comment any table to repadmin;


Grant succeeded.


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


PL/SQL procedure successfully completed.



SQL> grant execute any procedure to REPADMIN;


Grant succeeded.


-- 创建测试用户


SQL> create user gp identified by gp default tablespace users;


User created.


SQL> grant connect,resource to gp;


Grant succeeded.


SQL> conn gp/gp

Connected.


SQL> create table test (id int primary key);


Table created.


SQL> desc test

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------


ID                                        NOT NULL NUMBER(38)



-- 配置监听


  --source db



target_db =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.55)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = TESTTCB)

   )

 )


 -- target db


source_db =

 (DESCRIPTION =

   (ADDRESS_LIST =

     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.3.73)(PORT = 1521))

   )

   (CONNECT_DATA =

     (SERVICE_NAME = PREMIUMIT)

   )

 )


-- 创建dblink

--source db

SQL> conn repadmin/repadmin

Connected.

SQL> create database link rep_link connect to repadmin identified by repadmin using 'target_db';


Database link created.


SQL> conn repadmin/repadmin

Connected.

SQL>  select sysdate from dual@rep_link;


SYSDATE

---------

07-DEC-13



-- target db


SQL> create database link rep_link connect to repadmin identified by repadmin using 'source_db';


Database link created.



SQL> select sysdate from dual@rep_link;


SYSDATE

---------

07-DEC-13




以上操作在两台数据库同步配置



-- 在source db 创建复制组



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


PL/SQL procedure successfully completed.


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


GNAME                          M STATUS

------------------------------ - ---------

REP                            Y QUIESCED



-- 添加复制对象

execute dbms_repcat.create_master_repobject(sname=>'gp',oname=>'test',type=>'table',use_existing_object=>true,gname=>'rep',copy_rows=>false);


-- 在源端启动复制支持



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


PL/SQL procedure successfully completed.


-- 查看启动状态


SQL> select sname,oname,status,gname from dba_repobject;


SNAME      ONAME      STATUS     GNAME

---------- ---------- ---------- ----------

GP         TEST       VALID      REP

GP         TEST$RP    VALID      REP

GP         TEST$RP    VALID      REP


-- 在源端添加复制节点

SQL> execute dbms_repcat.add_master_database(gname=>'rep',master=>'TESTTCB' ,use_existing_objects=>true ,copy_rows=>false ,propagation_mode=>'synchronous');


PL/SQL procedure successfully completed.


SQL> select gname ,dblink ,masterdef ,master from dba_repsites where gname='REP';


GNAME                          DBLINK     M M

------------------------------ ---------- - -

REP                            PREMIUMI   Y Y

REP                            TESTTCB    N Y


-- 在源端启动复制



execute dbms_repcat.resume_master_activity('rep',true);



-- 测试

--source db


SQL> insert  into test values (1);


1 row created.


SQL> commit;


Commit complete.


SQL> select * from test;


       ID

----------

        1


--target db


SQL> conn gp/gp

Connected.

SQL> select *from test;


no rows selected


SQL> commit;


Commit complete.


SQL> select * from test;


       ID

----------

        1