学步园

-- tnsnames.ora 配置
DB2 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.49)(PORT = 1521))
            )
        (CONNECT_DATA =
            (SID = db2)
            (GLOBAL_NAME = mytest.com)
            (SERVER = DEDICATED)
        )
    )

DB1 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.201)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = db1.mytest.com)
        )
    )

conn system 

-- 修改 global name
alter database rename global_name to db1.mytest.com;

-- 建立公共 db link
create public database link db2.mytest.com using 'db2';
-- 检查
select * from global_name@db2.mytest.com;

-- 建立复制管理员并授权
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;

conn repadmin
-- 建立 db link
create database link db2.mytest.com connect to repadmin identified by repadmin;
-- 检查
select * from global_name@db2.mytest.com;

conn system
-- 建立测试用户并授权
create user testuser identified by testuser default tablespace users temporary tablespace temp; 
grant connect, resource to testuser;

DB1 =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.201)(PORT = 1521))
            )
        (CONNECT_DATA =
            (SID = db1)
            (GLOBAL_NAME = mytest.com)
            (SERVER = DEDICATED)
        )
    )

DB2 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.9.49)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = db2.mytest.com)
        )
    )

conn system

alter database rename global_name to db2.mytest.com;

create public database link db1.mytest.com using 'db1';
select * from global_name@db1.mytest.com;

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;

conn repadmin
create database link db1.mytest.com connect to repadmin identified by repadmin;
select * from global_name@db1.mytest.com;

conn system
create user testuser identified by testuser default tablespace users temporary tablespace temp; 
grant connect, resource to testuser;

-- 在复制组里加入复制对象
execute dbms_repcat.create_master_repobject(sname=>'testuser',oname=>'dept', type=>'table',use_existing_object=>true,gname=>'testuser_mg',copy_rows=>false); 
-- 检查
select sname,oname,status,gname from dba_repobject where gname='testuser_mg';

-- 对复制对象产生复制支持
execute dbms_repcat.generate_replication_support('testuser','dept','table');
-- 检查
select gname, master, status from dba_repgroup where gname='TESTUSER_MG';
select sname,oname,status,gname from dba_repobject where gname='TESTUSER_MG';

-- 添加主体复制节点
execute dbms_repcat.add_master_database (gname=>'testuser_mg',master=>'db2.mytest.com',use_existing_objects=>true, copy_rows=>false, propagation_mode => 'synchronous'); 
-- 检查
column masterdef format a10
column master format a10
column dblink format a25
column gname format a12
select gname, dblink, masterdef MASTERDEF, master MASTER from sys.dba_repsites where gname='TESTUSER_MG';

-- 启动复制
execute dbms_repcat.resume_master_activity('testuser_mg',true); 
-- 检查
select gname,master,status from dba_repgroup where gname='TESTUSER_MG';

commit;

-- 检查 db2 的数据变化
conn testuser
select * from dept;

-- 在 db1 上进行表结构修改（DDL）
EXECUTE DBMS_REPCAT.EXECUTE_DDL(gname => 'testuser_mg',ddl_text => 'truncate table testuser.dept');
EXECUTE DBMS_REPCAT.EXECUTE_DDL(gname => 'testuser_mg',ddl_text => 'ALTER TABLE TESTUSER.DEPT ADD c1 NUMBER');
EXECUTE DBMS_REPCAT.EXECUTE_DDL(gname => 'testuser_mg',ddl_text => 'ALTER TABLE TESTUSER.DEPT DROP (C1)');