版权声明:本文为博主原创文章,未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433249
一、环境
两台Oracle VM VirtualBox虚拟机
host:master1、master2
IP:192.168.1.1、192.168.1.2
OS:Linux 2.6.32
DB:Oracle 11.2.0.3,启用归档
OGG:ogg112101_fbo_ggs_Linux_x64_ora11g_64bit 2.2.3
二、安装OGG
在master1、master2两台上执行以下步骤:
cd /home/oracle/ogg
unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit
CREATE SUBDIRS
LD_LIBRARY_PATH=/home/oracle/ogg:$LD_LIBRARY_PATH export LD_LIBRARY_PATH
三、配置OGG支持sequence和DDL
CREATE USER ogg IDENTIFIED BY 123456;
GRANT CONNECT,RESOURCE,DBA TO ogg;
在OGG安装目录执行ggsci,在GGSCI下执行
EDIT PARAMS ./GLOBALS
GGSCHEMA ogg
在SQLPLUS里用SYSDBA执行OGG安装目录下的sequence.sql脚本
@sequence.sql
提示用户信息时输入ogg
在SQLPLUS里用SYSDBA执行以下命令,其中ogg是第1步建立的ogg用户,wxy是要复制的schema
在master1上执行
GRANT EXECUTE on ogg.updateSequence TO wxy;
在master2上执行
GRANT EXECUTE on ogg.replicateSequence TO wxy;
在master1上执行
alter table sys.seq$ add supplemental log data (primary key) columns;
GRANT EXECUTE ON utl_file TO ogg;
在SQLPLUS里用SYSDBA执行OGG安装目录下的marker_setup.sql脚本
@marker_setup.sql
提示用户信息时输入ogg
在SQLPLUS里用SYSDBA执行OGG安装目录下的ddl_setup.sql脚本
@ddl_setup.sql
提示用户信息时输入ogg
在SQLPLUS里用SYSDBA执行OGG安装目录下的role_setup.sql脚本
@role_setup.sql
提示用户信息时输入ogg
在SQLPLUS里用SYSDBA执行以下命令,其中wxy是要复制的schema
GRANT GGS_GGSUSER_ROLE TO wxy;
在SQLPLUS里用SYSDBA执行OGG安装目录下的ddl_enable.sql脚本
@ddl_enable.sql
在SQLPLUS里用SYSDBA执行OGG安装目录下的ddl_pin.sql脚本
@ddl_pin ogg
四、在源和目标库上配置OGG
EDIT PARAMS MGR
# 输入以下内容后,保存并关闭文件
PORT 7809
DYNAMICPORTLIST 7810-7820, 7830
AUTOSTART ER t*
AUTORESTART ER t*, RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS /home/oracle/ogg/dirdat/tt*, USECHECKPOINTS, MINKEEPHOURS 2
这步在master1、master2两台上执行,两个机器的MGR相同配置
EDIT PARAMS pri_ext
# 输入以下内容后,保存并关闭文件
EXTRACT pri_ext
USERID ogg, PASSWORD 123456
EXTTRAIL /home/oracle/ogg/dirdat/lt
DDL
SEQUENCE wxy.*;
TABLE wxy.*;
FLUSHCSECS 10
EOFDELAYCSECS 10
EDIT PARAMS pum_ext
# 输入以下内容后,保存并关闭文件
EXTRACT pum_ext
USERID ogg, PASSWORD 123456
RMTHOST 192.168.1.2, MGRPORT 7809
RMTTRAIL /home/oracle/ogg/dirdat/rt
SEQUENCE wxy.*;
TABLE wxy.*;
FLUSHCSECS 10
EOFDELAYCSECS 10
DBLOGIN USERID ogg, PASSWORD 123456
ADD CHECKPOINTTABLE ogg.cpt
EDIT PARAMS ./GLOBALS
# 添加以下内容后,保存并关闭文件
CHECKPOINTTABLE ogg.cpt
EDIT PARAMS rep
# 输入以下内容后,保存并关闭文件
REPLICAT rep
USERID ogg, PASSWORD 123456
DBOPTIONS SUPPRESSTRIGGERS, DEFERREFCONST
DDL
ASSUMETARGETDEFS
DISCARDFILE /home/oracle/ogg/disc
MAP wxy.*, TARGET wxy.*;
EOFDELAYCSECS 10
五、配置Oracle数据库
在master1、master2两台上,在SQLPLUS里用SYSDBA执行以下命令在库级添加补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER SYSTEM SWITCH LOGFILE;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
-- 输出应该为YES 或 IMPLICIT
exec dbms_goldengate_auth.grant_admin_privilege('WXY');
六、使用To direct bulk load to SQL*Loader方式实例化并开始OGG复制
-- master 1:
create table t1 (a int primary key);
CREATE SEQUENCE WXY.SEQ1 START WITH 0 MAXVALUE 99999999999 MINVALUE 0 NOCYCLE NOCACHE NOORDER;
insert into t1 select seq1.nextval from dual;
insert into t1 select seq1.nextval from dual;
insert into t1 select seq1.nextval from dual;
insert into t1 select seq1.nextval from dual;
commit;
-- master 2:
create table t1 (a int primary key);
CREATE SEQUENCE WXY.SEQ1 START WITH 0 MAXVALUE 99999999999 MINVALUE 0 NOCYCLE NOCACHE NOORDER;
START MANAGER
ADD EXTRACT init_ext, SOURCEISTABLE
EDIT PARAMS init_ext
# 输入以下内容后,保存并关闭文件
EXTRACT init_ext
USERID ogg, PASSWORD 123456
RMTHOST 192.168.1.2, MGRPORT 7809
RMTTASK replicat, GROUP init_rep
TABLE wxy.*;
ADD REPLICAT init_rep, SPECIALRUN
EDIT PARAMS init_rep
# 输入以下内容后,保存并关闭文件
REPLICAT init_rep
USERID ogg, PASSWORD 123456
BULKLOAD
ASSUMETARGETDEFS
MAP wxy.*, TARGET wxy.*;
DBLOGIN USERID ogg, PASSWORD 123456
REGISTER EXTRACT init_ext DATABASE
rman target /
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY
# 添加主抽取进程
ADD EXTRACT pri_ext, TRANLOG, BEGIN NOW
# 添加本地trail文件
ADD EXTTRAIL /home/oracle/ogg/dirdat/lt, EXTRACT pri_ext
# 添加数据泵进程
ADD EXTRACT pum_ext, EXTTRAILSOURCE /home/oracle/ogg/dirdat/lt
# 添加远程trail文件
ADD RMTTRAIL /home/oracle/ogg/dirdat/rt, EXTRACT pum_ext
# 添加复制进程
ADD REPLICAT rep, EXTTRAIL /home/oracle/ogg/dirdat/rt
START EXTRACT pri_ext
START EXTRACT pum_ext
DBLOGIN USERID ogg, PASSWORD 123456
FLUSH SEQUENCE wxy.seq1
START EXTRACT init_ext
VIEW REPORT init_ext
EDIT PARAMS rep
# 输入以下内容后,保存并关闭文件
HANDLECOLLISIONS
# 启动复制进程
START REPLICAT rep
INFO REPLICAT rep
# 初始化复制结束后,去掉HANDLECOLLISIONS
SEND REPLICAT rep, NOHANDLECOLLISIONS
EDIT PARAMS rep
# 删除HANDLECOLLISIONS,保存并关闭文件
七、测试
先查看一下master 1、master 2库里的数据,确认初始化结果
-- 启动复制前master 2上没有数据,初始化结束后两个机器上的以下查询结果应该一致
select * from t1;
select * from dba_sequences where sequence_name = 'SEQ2';
-- 进一步测试,在master 1的库里,用wxy用户执行创建用户、创建表,创建序列,查询序列,插入、修改、删除表数据,truncate table、drop table等一系列DML、DDL操作,查询master 2的库,确认复制是否正确。
-- master 1:
create table t2 (a int);
insert into t2 values (1);
commit;
select * from t2;
-- master 2:
select * from t2;
-- master 1:
insert into t2 values (2);
commit;
select * from t2;
-- master 2:
select * from t2;
-- master 1:
delete from t2 where a=1;
commit;
select * from t2;
-- master 2:
select * from t2;
-- master 1:
update t2 set a=10;
commit;
select * from t2;
-- master 2:
select * from t2;
-- master 1:
truncate table t2;
-- master 2:
select * from t2;
-- master 1:
drop table t2 purge;
-- master 2:
select * from t2;
-- master 1:
create user u1 identified by 123456;
grant connect to u1;
-- master 2:
conn u1
select * from user_role_privs;
-- master 1:
CREATE SEQUENCE WXY.SEQ2 START WITH 0 MAXVALUE 99999999999 MINVALUE 0 NOCYCLE NOCACHE NOORDER;
select seq2.nextval from dual;
select seq2.nextval from dual;
select seq2.nextval from dual;
select * from dba_sequences where sequence_name = 'SEQ2';
-- master 2:
select * from dba_sequences where sequence_name = 'SEQ2';