oracle数据库主主复制
时间：2021-07-01 10:21:17
帮助过：3人阅读
/
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
oracle数据库主主复制
标签：打开 creat 规划 epc cond ted 名称 ica oracl
