oracle数据库主主复制

oracle 复制基于表复制配置过程；

规划：

检查oarcle数据库是否支持高级复制功能，true

>Select value from v$OPTION where parameter='advanced replicaiont'

2,是否支持global_names

(当GLOBAL_NAMES参数设置为TRUE时，使用DATABASE LINK时，DATABASE LINK的名称必须与被连接库的GLOBAL_NAME一致)

 

>show parameter global_names ;结果为true

查看global_name配置：

>select * from global_name;

3，查看job_queue_processes; 在0-1000之间

>show parameter job_queue_processes;

 

4，查看open_links

>show parameter open_links; default：4 不可更改

5,查看全局数据库名称：

>select * from global_name;

6，设置全局数据库名称（安装过程中由于字符串长度等原因，实际global_name配置不正确，安装完成更改即可。否则dblink连接不上）：

>alter database rename global_name to masterone.test.com;

>alter database rename global_name to mastertwo.test.com;

1，创建数据库，
    全局数据库名称分别为：masterone.test.com/mastertwo.test.com
    Serivce name 分别为：masterone.test.com/mastertwo.test.com
2，在两个数据库中tnsnames.ora配置客户访问：配置内容如下：
    MASTERTWO =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = mastertwo.test.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = mastertwo.test.com)
        )
      )
    MASTERONE =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = masterone.test.com)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = masterone.test.com)
        )
      )
    
3，打开SQL Plus测试链接（保证数据库可联通）
    >connect system@masterone;
    >connect system@mastertwo;
4，创建replication admin ，使用system连接masterone.test.com数据库；
5，创建repadmin 执行命令
     Create user repadmin identified by repadmin;
6，赋予repadmin用户可以创建和管理复制环境的权限
    Exec dbms_repcat_admin.grant_admin_any_schema(username=>'repadmin');
    Grant comment any table to repadmin;
    Grant lock any table to repadmin;
    如果使用OEM管理工具，还需执行如下命令：
    GRANT SELECT ANY DICTIONARY TO repadmin;
7，注册repadmin为传播函数
    Exec dbms_defer_sys.register_propagator(username=>'repadmin');
8，注册repadmin为接受函数
        BEGIN
           DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
              username => 'repadmin',
              privilege_type => 'receiver',
              list_of_gnames => NULL);
        END;
9，为了在检查时保持延迟事务队列的大小, 您应该清除已成功完成的延迟事务。SCHEDULE_PURGE 过程为您自动清除过程。使用repadmin登陆数据库
    Connect repadmin;
    
    BEGIN
       DBMS_DEFER_SYS.SCHEDULE_PURGE (
          next_date => SYSDATE,
          interval => 'SYSDATE + 1/24',
          delay_seconds => 0);
    END;
10，在mastertwo.mor.cr上执行相同操作4-9；

11，使用system在masterone.test.com上创建共有dblink
    Connect system@masterone
    Create public datebase link mastertwo.test.com using 'mastertwo';
    
    Connect repadmin@masterone
    Create database link mastertwo.test.com connect to repadmin identified by repadmin;
    
    使用system在mastertwo.test.com上创建共有dblink
    Connect system@mastertwo;
    Create public database link masterone.test.com using 'masterone';
    
    Connect repadmin@mastertwo
    Create database link masterone.mor.cr connect to repadmin identified by repadmin;
    
12，使用repadmin登陆masterone.test.com/mastertwo.test.com
    Connect repadmin@masterone
    BEGIN
       DBMS_DEFER_SYS.SCHEDULE_PUSH (
          destination => 'mastertwo.test.com',
          interval => 'SYSDATE + (1/144)',
          next_date => SYSDATE,
          parallelism => 1,
          execution_seconds => 1500,
          delay_seconds => 1200); /*延迟时间可以设置小一点*/
    END;
    
    Connect repadmin@mastertwo
    BEGIN
       DBMS_DEFER_SYS.SCHEDULE_PUSH (
          destination => 'masterone.test.com',
          interval => 'SYSDATE + (1/144)',
          next_date => SYSDATE,
          parallelism => 1,
          execution_seconds => 1500,
          delay_seconds => 1200);
    END;
    
13，创建master group
    
    在创建同步组之前应该确保复制环境的中的每个数据库中有相同的schema和表结构。例如在测试环境中定义的schema为hr，表结构为test
    
    只在master site上执行此操作：masterone.test.com作为master site
    Connect repadmin@masterone
    
    Exec dbms_repcat.create_master_repgroup('hrgroup')
14，向master group中添加object
    
    BEGIN
       DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
          gname => 'hrgroup',
          type => 'TABLE',
          oname => 'test',
          sname => 'hr',
          use_existing_object => TRUE,
          copy_rows => FALSE);
    END;
    
15，添加额外的master site
    BEGIN
       DBMS_REPCAT.ADD_MASTER_DATABASE (
          gname => 'hrgroup',
          master => 'mastertwo.test.com',
          use_existing_objects => TRUE,
          copy_rows => FALSE,
          propagation_mode => 'ASYNCHRONOUS');
    END;
    
    使用如下命令检查，如果出现两个数据库的连接则操作成功
    SELECT DBLINK FROM DBA_REPSITES WHERE GNAME = 'HRGROUP';
    mastertwo.test.com
    masterone.test.com
    
16，添加复制支持
    
    BEGIN 
        DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
          sname => 'hr',
          oname => 'test', 
          type => 'TABLE',
          min_communication => TRUE); 
    END;
    
    完成检查通过命令检查，返回0可继续执行
    SELECT COUNT(*) FROM DBA_REPCATLOG WHERE GNAME = 'HRGROUP';
17，启用复制
    BEGIN 
       DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
          gname => 'hrgroup'); 
    END;
18，测试
    在masterone.test.com上hr.test表中分别通过进行增删改操作
        Insert into hr.test values (1,'testuser1');
        Commit;(在SQL Plus上操作别忘提交)
    由于复制延迟为1200，毫秒所以不会很快同步到mastertwo.test.com上

参考地址：https://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmastergroup.htm#i1004613