高階複製-3、主體定義站點上的操作

1、建立repadmin使用者，用於管理高階複製
create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
execute dbms_defer_sys.register_propagator('repadmin');
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;

SQL> desc dba_tablespaces;      
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 BLOCK_SIZE                                NOT NULL NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                               NOT NULL NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 MIN_EXTLEN                                         NUMBER
 STATUS                                             VARCHAR2(9)
 CONTENTS                                           VARCHAR2(9)
 LOGGING                                            VARCHAR2(9)
 FORCE_LOGGING                                      VARCHAR2(3)
 EXTENT_MANAGEMENT                                  VARCHAR2(10)
 ALLOCATION_TYPE                                    VARCHAR2(9)
 PLUGGED_IN                                         VARCHAR2(3)
 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)
 DEF_TAB_COMPRESSION                                VARCHAR2(8)
 RETENTION                                          VARCHAR2(11)
 BIGFILE                                            VARCHAR2(3)

SQL> select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                                              CONTENTS
------------------------------------------------------------ ------------------
SYSTEM                                                       PERMANENT
UNDOTBS1                                                     UNDO
SYSAUX                                                       PERMANENT
TEMP                                                         TEMPORARY
USERS                                                        PERMANENT
EXAMPLE                                                      PERMANENT
TEST                                                         PERMANENT

7 rows selected.

SQL> create user repadmin identified by repadmin default tablespace users temporary tablespace temp;

User created.

SQL> execute dbms_defer_sys.register_propagator('repadmin');                                                                     
grant execute any procedure to repadmin;
execute dbms_repcat_admin.grant_admin_any_repgroup('repadmin');
execute dbms_repcat_admin.grant_admin_any_schema(username => '"REPADMIN"');
grant comment any table to repadmin;
grant lock any table to repadmin;
grant select any dictionary to repadmin;

PL/SQL procedure successfully completed.

SQL>
Grant succeeded.

SQL>
PL/SQL procedure successfully completed.

SQL>
PL/SQL procedure successfully completed.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

SQL>
Grant succeeded.

2、登入snap 資料庫，重複上面的操作，建立public dblink 以及repadmin 使用者（這個在非主體定義站點實現）

3、建立公共的dblink連線到複製站點
CREATE PUBLIC DATABASE LINK SNAP_PUBLIC_COM connect to repadmin identified by repadmin  USING 'REPLICATION_SITE';

SQL> CREATE PUBLIC DATABASE LINK SNAP_PUBLIC_COM connect to repadmin identified by repadmin  USING 'REPLICATION_SITE';

Database link created.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORCL.COM

SQL> select * from global_name@snap_public_com;
select * from global_name@snap_public_com
                          *
ERROR at line 1:
ORA-02085: database link SNAP_PUBLIC_COM.COM connects to CHRIST.COM

SQL> select * from user_tables@snap_public_com;
select * from user_tables@snap_public_com
                          *
ERROR at line 1:
ORA-02085: database link SNAP_PUBLIC_COM.COM connects to CHRIST.COM

錯誤原因為：
當global名設定為TRUE時，DB LINK的名稱必須與GLOBAL NAME保持一致

CREATE PUBLIC DATABASE LINK SNAP.COM connect to repadmin identified by repadmin  USING 'REPLICATION_SITE';

4、建立主體複製組，建立主體複製組，新增複製物件，操作的資料庫將稱為主體定義站點
新增複製組
execute dbms_repcat.create_master_repgroup('REP_MYTEST');

SQL> set linesize 200;
SQL> select gname,master,status from dba_repgroup where gname='REP_MYTEST';

GNAME                                                        MA STATUS
------------------------------------------------------------ -- ------------------
REP_MYTEST                                                   Y  QUIESCED

5、新增主體複製站點支援：
execute dbms_repcat.add_master_database  (gname=>'REP_MYTEST',master=>'SNAP.COM',  use_existing_objects=>true,copy_rows=>false, propagation_mode => 'synchronous');

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

PL/SQL procedure successfully completed.

SQL> col gname for a20;
SQL> select gname,master,status from dba_repgroup where gname='REP_MYTEST';

GNAME                MA STATUS
-------------------- -- ------------------
REP_MYTEST           Y  QUIESCED

SQL> col dblink for a10;
SQL> col gname for a20;
SQL> select gname,dblink,masterdef,master from dba_repsites where gname='REP_MYTEST';

GNAME                DBLINK     MA MA
-------------------- ---------- -- --
REP_MYTEST           MASTER.COM Y  Y
REP_MYTEST           SNAP.COM   N  Y

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12350275/viewspace-683776/，如需轉載，請註明出處，否則將追究法律責任。