针对某个表使用高级复制进行数据同步

1.  初台化参数设置（源/目标）：

a)   global_names和job_queue_processes：

SQL> alter system setglobal_names=true;

 

System altered.

 

SQL> alter system setjob_queue_processes=20;

 

System altered.

 

b)   global_name设置为带后缀的名称：

 

源站点：

SQL> alterdatabase rename global_name to ORCL1.COM;

 

Databasealtered.

 

目标站点：

SQL> alterdatabase rename global_name to ORCL2.COM;

 

Databasealtered.

 

c)   配置tnsnames(源/目标):

ORCL132 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

ORCL131 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

 

d)   创建用户（源/目标）：

 

   创建管理用户

SQL> createuser repadmin identified by repadmin default tablespace users temporarytablespace temp;

 

SQL> executedbms_defer_sys.register_propagator('repadmin');

SQL> grantexecute any procedure to repadmin;

SQL> executedbms_repcat_admin.grant_admin_any_repgroup('repadmin');

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

SQL> grantselect any dictionary to repadmin;

SQL> grantcomment any table to repadmin;

SQL> grantlock any table to repadmin;

 

 创建使用用户

SQL> createuser gp identified by gp;

 

SQL> grantdba to gp;

 

SQL> conngp/gp

 

SQL> createtable t(x int primary key);

 

 

e)   配置dblink（源/目标）

源库：

SQL> connrepadmin/repadmin

    SQL> create database link ORCL2.COMconnect to REPADMIN identified by REPADMIN using 'ORCL132';

目标库：

SQL> connrepadmin/repadmin

SQL>  create database link ORCL1.COM connect toREPADMIN identified by REPADMIN using 'ORCL131';

 

 

2.  创建复制组（源）

 

SQL>  executedbms_repcat.create_master_repgroup('rep');

 

3.  加入复制对象（源）

 

SQL>executedbms_repcat.create_master_repobject(sname=>'gp',oname=>'t',type=>'table',use_existing_object=>true,gname=>'rep',copy_rows=>false);

 

SQL> selectsname,oname,status,gname from dba_repobject where gname='REP';

 

SNAME                ONAME                STATUS     GNAME

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

GP                   T                    VALID      REP

 

4.  对复制对象启动复制支持（源）

 

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

 

 

SQL> selectsname,oname,status,gname from dba_repobject where gname='REP';

 

SNAME                ONAME                STATUS     GNAME

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

GP                   T                    VALID      REP

GP                   T$RP                 VALID      REP

GP                   T$RP                 VALID      REP

 

 

5.  添加复制节点（源）

 

SQL>  executedbms_repcat.add_master_database(gname=>'rep',master=>'orcl2.com',use_existing_objects=>true,copy_rows=>false,propagation_mode=>'synchronous');

 

 

SQL> columnmasterdef format a10

SQL> columnmaster format a10

SQL> columndblink format a25

SQL> columngname format a12

SQL> selectgname,dblink,masterdef,master from sys.dba_repsites where gname='REP';

 

GNAME        DBLINK                    MASTERDEF  MASTER

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

REP          ORCL1.COM                 Y          Y

REP          ORCL2.COM                 N          Y

 

6.  启动复制（源）

 

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

 

7.  测试一下：

SQL> insertinto gp.t  values(1);

 

1 row created.

 

SQL> commit;

 

Commitcomplete.

 

目标库查询一下：

SQL> conngp/gp

Connected.

SQL> select* from t;

 

         X

----------

            1