-- 检查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