环境概述:
本次配置OnStar GoldenGate 双向复制测试环境,环境如下:
Primary 端
机器名 | machine1 |
IP | 113.52.185.27 |
操作系统 | SunOS oncnjqsvstph09lz00 5.10 Generic_147440-15 sun4u sparc SUNW,SPARC-Enterprise |
数据库版本 | Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production |
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 – Production是否RAC2节点RAC是否ASM使用ASM复制实例Onstga1 | onstga2Goldengate 安装目录/oracle/goldengateGoldengate 版本Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Solaris, sparc, 64bit (optimized), Oracle 11g on Apr 24 2012 08:33:54
Standby 端:
机器名 | oncnjqsvvv06 |
IP | 113.52.166.27 |
操作系统 | Linux oncnjqsvvv06 2.6.16.60-0.85.1-smp #1 SMP Thu Mar 17 11:45:06 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux |
数据库版本 | Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production |
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production是否RAC2节点RAC是否ASM使用ASM复制实例onggate1 | onggate2Goldengate 安装目录/oracle/goldengateGoldengate 版本Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
操作系统相关需求:
- 添加操作系统用户 goldengate 所属group 、权限参照oracle用户
- 配置NFS(或类似方案),使得goldengate所安装节点,能够访问RAC集群中各节点的归档日志
- 网络设置
OGG只需要复制两端的IP地址之间能够建立TCP连接,一个Goldengate复制链路需要10个TCP动态端口,具体端口建议使用7839~7949
- 提供GoldenGate本地存储空间(7*每天产生REDO LOG量 * 0.4)
Oracle数据库需求:
- 确认所需复制的Schema:
名称 | 说明 |
CCSOWNER | |
CDM | |
ECM | |
GAA | |
VCS | |
GIST | |
GISR | |
SOMP | |
BDIFSTAGE |
- 为了RMAN初始化顺利进行:
容灾中心数据库卷组的大小、卷组的命名、系统的环境变量、用户名等参数配置保持跟生产端一致,从而保证RMAN恢复后的环境和源生产端一致。
采用RMAN初始化能保证在海量数据环境初始化的工作量较少,实施难度小并且一次成功。建议超过100GB以上的数据库如果是在同操作系统平台数据库间做复制,就必须采用RMAN进行初始化。 在Goldengate初始化时需要做备份(RMAN/EXP/EXPDP),需要为每个数据库规划出足够的备份空间,因为备份空间必须要用文件系统,所以不能从当前RAC数据库的共享卷组中划空间。
- 修改数据库为归档模式
- 保留归档日志7天(可能需要修改备份策略)
- 添加数据库supplemental log
- 修改数据库为force logging方式
- 创建用户:
OGG_PRIMARY, OGG_STANDBY 或者其他名称, 所需权限:
CONNECT
RESOURCE
ALTER ANY TABLE
ALTER SESSION
CREATE SESSION
FLASHBACK ANY TABLE
SELECT ANY DICTIONARY
SELECT ANY TABLE
INSERT ANY TABLE
UPDATE ANY TABLE
DELETE ANY TABLE
GoldenGate 数据初始化:
源端数据导出(expdp):
expdp ogg_primary/ogg123456 DIRECTORY = DATA_PUMP_DIR DUMPFILE = onpdga_FULL_%U.dmp FULL=Y FLASHBACK_SCN=17629760288 COMPRESSION=all
目标端导入:
impdp system/oracle parfile=/oracle/goldengate/cscript/imp_par.prm
-- parfile FULL=Y DUMPFILE=onpdga_FULL_01.dmp EXCLUDE=SCHEMA:"='SYS'" EXCLUDE=SCHEMA:"='SYSTEM'" EXCLUDE=SCHEMA:"='DBSNMP'" EXCLUDE=SCHEMA:"='SYSMAN'" EXCLUDE=SCHEMA:"='OUTLN'" EXCLUDE=SCHEMA:"='FLOWS_FILES'" EXCLUDE=SCHEMA:"='MDSYS'" EXCLUDE=SCHEMA:"='ORDSYS'" EXCLUDE=SCHEMA:"='CTXSYS'" EXCLUDE=SCHEMA:"='ANONYMOUS'" EXCLUDE=SCHEMA:"='EXFSYS'" EXCLUDE=SCHEMA:"='WMSYS'" EXCLUDE=SCHEMA:"='XDB'" EXCLUDE=SCHEMA:"='ORDPLUGINS'" EXCLUDE=SCHEMA:"='APEX_030200'" EXCLUDE=SCHEMA:"='OLAPSYS'" EXCLUDE=SCHEMA:"='APEX_PUBLIC_USER'" EXCLUDE=SCHEMA:"='OGG_PRIMARY'"
logfile=import_mig.log
GoldenGate 双向复制拓扑:
GoldenGate 命名规则:
组件命名规则:
[E|D|R][P|T|R][P|S]_AAAA
共8位, 第一位标志组件(进程)类型:
E: Extract - goldengate抽取进程
D: Datapump - 网络传送进程
R: Replicat - 应用进程
第二位标志环境:
P: Production - 生产环境
T: Test - 测试
R: Trainning - 培训
⋯⋯
第三位:
P: Primary - 主链路
S: Standby - 备链路
AAAA:
四位字符描述系统信息
Trail 文件命名规则:
所有trail文件存储于<OGG_DIR>/dirdat/ 目录下
GoldenGate 安装、配置
软件安装:
goldengate软件使用静态编译方式,只需要将压缩安装包,在指定文件夹下解压缩。
本例goldengate安装目录为/oracle/goldengate,安装用户为oracle
解压缩后,执行./ggsci
GGSCI (oncnjqsvvv06) 1> create subdirs -- 创建goldengate运行所需子目录
配置Manager:
GGSCI (oncnjqsvvv06) 1> edit params mgr
PORT 7839 DYNAMICPORTLIST 7840-7849 PURGEOLDEXTRACTS ./dirdat/l*, USECHECKPOINTS, MINKEEPHOURS 12 ,MINKEEPFILES 30 LAGREPORTMINUTES 5 LAGCRITICALMINUTES 10 GGSCI (oncnjqsvvv06) 2>start mgr -- 启动manager进程
GGSCI (oncnjqsvvv06) 4> info mgr -- 查看manager 状态
Manager is running (IP port oncnjqsvvv06.7839). -- manager 应处于running状态
源端配置:
数据库连接用户配置:
goldengate需要连接Oracle数据库,该用户需要如下权限:
SQL> create user ogg_primary identified by ogg123456 default tablespace sysaux;
User created.
SQL> grant connect,resource to ogg_primary;
Grant succeeded.
SQL> grant alter any table to ogg_primary;
Grant succeeded.
SQL> grant alter session to ogg_primary;
Grant succeeded.
SQL> grant flashback any table to ogg_primary;
Grant succeeded.
SQL> grant select any dictionary to ogg_primary;
Grant succeeded.
SQL> grant select any table to ogg_primary;
Grant succeeded.
SQL> grant insert any table to ogg_primary;
Grant succeeded.
SQL> grant update any table to ogg_primary;
Grant succeeded. SQL> grant delete any table to ogg_primary;
Grant succeeded.
SQL>
SQL> exec dbms_goldengate_auth.grant_admin_privilege('OGG_PRIMARY');
PL/SQL procedure successfully completed.
主复制链路配置:
源端(onstga)执行==》
添加Force Logging:
SQL> alter database force logging;
Database altered.
添加supplemental log:
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
添加Trandata:
在goldengate ggsci中执行: dblogin userid ogg_primary@<TNS_NAME>, password ***** add trandata CCSOWNER.* add trandata CDM.* add trandata ECM.* add trandata GAA.* add trandata VCS.* add trandata GIST.* add trandata GISR.* add trandata SOMP.* add trandata BDIFSTAGE.*
配置Extract(Goldengate抽取)进程:
GGSCI (oncnjqsvstph09lz00) 71> edit params epp_ga01 EXTRACT epp_ga01
SETENV ( ORACLE_HOME = /orasoft/app/oracle/product/11.2.0/db_1 ) SETENV ( ORACLE_SID = onstga1 )
discardfile ./dirrpt/epp_ga01.dsc, purge exttrail ./dirdat/ls
userid ogg_primary , password ogg123456 reportcount every 1 minutes, rate numfiles 5000 dynamicresolution tranlogoptions convertucs2clobs, excludeuser ogg_primary TRANLOGOPTIONS LOGRETENTION DISABLED
TranlogOptions ASMUser sys@ASM1 , asmpassword oracle
WARNLONGTRANS 2H, CHECKINTERVAL 5m
TABLEEXCLUDE CCSOWNER.MLOG*, CCSOWNER.RUPD* TABLE CCSOWNER.*;
TABLEEXCLUDE CDM.MLOG*, CDM.RUPD* TABLE CDM.*;
TABLEEXCLUDE ECM.MLOG*, ECM.RUPD* TABLE ECM.*;
TABLEEXCLUDE GAA.MLOG*, GAA.RUPD* TABLE GAA.*;
TABLEEXCLUDE VCS.MLOG*, VCS.RUPD* TABLE VCS.*;
TABLEEXCLUDE GIST.MLOG*, GIST.RUPD* TABLE GIST.*;
TABLEEXCLUDE GISR.MLOG*, GISR.RUPD* TABLE GISR.*;
TABLEEXCLUDE SOMP.MLOG*, SOMP.RUPD* TABLE SOMP.*;
TABLEEXCLUDE BDIFSTAGE.MLOG*, BDIFSTAGE.RUPD* TABLE BDIFSTAGE.*;
GGSCI (oncnjqsvstph09lz00) 72> add extract epp_ga01, tranlog, threads 2, begin now GGSCI (oncnjqsvstph09lz00) 73> add exttrail ./dirdat/ls, extract epp_ga01 GGSCI> start epp_ga01
配置DataPump(网络传送)进程:
GGSCI (oncnjqsvstph09lz00) 13> edit params dpp_ga01
EXTRACT DPP_GA01 setenv ( ORACLE_SID=onstga1 )
Passthru
RMTHOST 113.52.166.27, MGRPORT 7839 RMTTRAIL ./dirdat/lt DISCARDFILE ./dirrpt/dpp_ga01.dsc, APPEND
REPORTCOUNT EVERY 1 HOURS, RATE
TABLE CCSOWNER.*;
TABLE CDM.*;
TABLE ECM.*;
TABLE GAA.*;
TABLE VCS.*;
TABLE GIST.*;
TABLE GISR.*;
TABLE SOMP.*;
TABLE BDIFSTAGE.*;
-- 创建Datapump GGSCI (oncnjqsvstph09lz00) 2> add extract dpp_ga01, exttrailsource ./dirdat/ls EXTRACT added. -- 创建Remote trail GGSCI (oncnjqsvstph09lz00) 3> add rmttrail ./dirdat/lt, extract dpp_ga01 RMTTRAIL added. GGSCI (oncnjqsvstph09lz00) 4> start dpp_ga01
备用复制链路配置(完成目标端备用链路配置后进行):
配置Replicat(应用|apply)进程:
GGSCI (oncnjqsvvv06) 2> edit params rps_ga02
REPLICAT RPS_GA02
SETENV ( ORACLE_SID=onstga1 )
USERID OGG_PRIMARY, password ogg123456
--exttrail ./dirdat/ly
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rps_ga02.dsc, APPEND
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
MAPEXCLUDE CCSOWNER.MV_* MAP CCSOWNER.*, TARGET CCSOWNER.*;
MAPEXCLUDE CDM.MV_* MAP CDM.*, TARGET CDM.*;
MAPEXCLUDE ECM.MV_* MAP ECM.*, TARGET ECM.*;
MAPEXCLUDE GAA.MV_* MAP GAA.*, TARGET GAA.*;
MAPEXCLUDE VCS.MV_* MAP VCS.*, TARGET VCS.*;
MAPEXCLUDE GIST.MV_* MAP GIST.*, TARGET GIST.*;
MAPEXCLUDE GISR.MV_* MAP GISR.*, TARGET GISR.*;
MAPEXCLUDE SOMP.MV_* MAP SOMP.*, TARGET SOMP.*;
MAPEXCLUDE BDIFSTAGE.MV_* MAP BDIFSTAGE.*, TARGET BDIFSTAGE.*; GGSCI >
添加CheckPoint Table:
GGSCI > dblogin userid ogg_primary@<TNS_ONSTGA>, password **** GGSCI > add checkpointtable ckpt_stdby
Successfully created checkpoint table ckpt_stdby.
创建Replicat:
GGSCI > add replicat RPS_GA02, exttrail ./dirdat/ly, checkpointtable ckpt_stdby
REPLICAT added.
目标端配置:
数据库连接用户配置:
用户名为ogg_stdby, 所需权限与源端一致
主复制链路配置:
配置Replicat(应用|apply)进程:
GGSCI (oncnjqsvvv06) 2> edit params rpp_ga01
REPLICAT RPP_GA01
SETENV ( ORACLE_SID=onggate1 )
USERID OGG_STDBY, password ogg123456
--exttrail ./dirdat/lt
DISCARDFILE ./dirrpt/rpp_ga01.dsc, APPEND
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST
ASSUMETARGETDEFS
MAPEXCLUDE CCSOWNER.MV_* MAP CCSOWNER.*, TARGET CCSOWNER.*;
MAPEXCLUDE CDM.MV_* MAP CDM.*, TARGET CDM.*;
MAPEXCLUDE ECM.MV_* MAP ECM.*, TARGET ECM.*;
MAPEXCLUDE GAA.MV_* MAP GAA.*, TARGET GAA.*;
MAPEXCLUDE VCS.MV_* MAP VCS.*, TARGET VCS.*;
MAPEXCLUDE GIST.MV_* MAP GIST.*, TARGET GIST.*;
MAPEXCLUDE GISR.MV_* MAP GISR.*, TARGET GISR.*;
MAPEXCLUDE SOMP.MV_* MAP SOMP.*, TARGET SOMP.*;
MAPEXCLUDE BDIFSTAGE.MV_* MAP BDIFSTAGE.*, TARGET BDIFSTAGE.*; GGSCI >
添加CheckPoint Table:
GGSCI > dblogin userid ogg_stdby@<TNS_ONGGATE>, password **** GGSCI > add checkpointtable ckpt_primary
Successfully created checkpoint table ckpt_primary.
创建Replicat:
GGSCI > add replicat RPP_GA01, exttrail ./dirdat/lt, checkpointtable ckpt_primary
REPLICAT added.
备用复制链路配置
添加Force Logging
SQL> alter database force logging;
Database altered.
添加supplemental log:
SQL> alter database add supplemental log data;
Database altered.
SQL> alter system switch logfile;
System altered.
添加Trandata:
在goldengate ggsci中执行: dblogin userid ogg_stdby@<TNS_NAME>, password ***** add trandata CCSOWNER.* add trandata CDM.* add trandata ECM.* add trandata GAA.* add trandata VCS.* add trandata GIST.* add trandata GISR.* add trandata SOMP.* add trandata BDIFSTAGE.*
配置Extract(Goldengate抽取)进程:
GGSCI (oncnjqsvstph09lz00) 71> edit params eps_ga02 EXTRACT eps_ga02
SETENV ( ORACLE_HOME = /orasoft/app/oracle/product/11.2.0/db_1 ) SETENV ( ORACLE_SID = onggate1 )
discardfile ./dirrpt/eps_ga02.dsc, purge exttrail ./dirdat/lx
userid ogg_stdby , password ogg123456 reportcount every 1 minutes, rate numfiles 5000 dynamicresolution tranlogoptions convertucs2clobs, excludeuser ogg_primary TRANLOGOPTIONS LOGRETENTION DISABLED
TranlogOptions ASMUser sys@ASM1 , asmpassword oracle
WARNLONGTRANS 2H, CHECKINTERVAL 5m
TABLEEXCLUDE CCSOWNER.MLOG*, CCSOWNER.RUPD* TABLE CCSOWNER.*;
TABLEEXCLUDE CDM.MLOG*, CDM.RUPD* TABLE CDM.*;
TABLEEXCLUDE ECM.MLOG*, ECM.RUPD* TABLE ECM.*;
TABLEEXCLUDE GAA.MLOG*, GAA.RUPD* TABLE GAA.*;
TABLEEXCLUDE VCS.MLOG*, VCS.RUPD* TABLE VCS.*;
TABLEEXCLUDE GIST.MLOG*, GIST.RUPD* TABLE GIST.*;
TABLEEXCLUDE GISR.MLOG*, GISR.RUPD* TABLE GISR.*;
TABLEEXCLUDE SOMP.MLOG*, SOMP.RUPD* TABLE SOMP.*;
TABLEEXCLUDE BDIFSTAGE.MLOG*, BDIFSTAGE.RUPD* TABLE BDIFSTAGE.*;
GGSCI (oncnjqsvstph09lz00) 72> add extract epp_ga01, tranlog, threads 2, begin now GGSCI (oncnjqsvstph09lz00) 73> add exttrail ./dirdat/ls, extract epp_ga01 GGSCI> start epp_ga01
配置DataPump(网络传送)进程:
GGSCI (oncnjqsvstph09lz00) 13> edit params dps_ga02
EXTRACT DPS_GA02 setenv ( ORACLE_SID=onggate1 )
Passthru
RMTHOST 113.52.185.27, MGRPORT 7839 RMTTRAIL ./dirdat/ly DISCARDFILE ./dirrpt/dps_ga02.dsc, APPEND
REPORTCOUNT EVERY 1 HOURS, RATE
TABLE CCSOWNER.*;
TABLE CDM.*;
TABLE ECM.*;
TABLE GAA.*;
TABLE VCS.*;
TABLE GIST.*;
TABLE GISR.*;
TABLE SOMP.*;
TABLE BDIFSTAGE.*;
-- 创建Datapump GGSCI (oncnjqsvstph09lz00) 2> add extract dps_ga02, exttrailsource ./dirdat/lx EXTRACT added. -- 创建Remote trail GGSCI (oncnjqsvstph09lz00) 3> add rmttrail ./dirdat/ly, extract dps_ga02 RMTTRAIL added. GGSCI (oncnjqsvstph09lz00) 4> start dps_ga02
Goldengate双向复制验证、测试
测试所用表结构:
CREATE TABLE "GAA"."OGG_TC1" ( "C1" NUMBER(10,0), "C2" VARCHAR2(100 BYTE), PRIMARY KEY ("C1") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING ; CREATE TABLE "GAA"."OGG_TC2" ( "C1" NUMBER(10,0), "C2" DATE, PRIMARY KEY ("C1") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 ENABLE, CONSTRAINT "OGG_TC2_OGG_TC1_FK1" FOREIGN KEY ("C1") REFERENCES "GAA"."OGG_TC1" ("C1") ON DELETE CASCADE ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING ; 创建trigger: CREATE OR REPLACE TRIGGER "GAA".TRIGGER1 AFTER INSERT OR DELETE OR UPDATE ON "GAA".OGG_TC1 FOR EACH ROW BEGIN if inserting then insert into OGG_TC2 values (:new.c1, sysdate); end if; if updating then update ogg_tc2 set c2 = sysdate where c1 = :new.c1; end if; END;
测试case 1:
目的:测试DDL操作是否被复制 期望结果: 不复制DDL 操作: 在Source端执行上述DDL语句。 结果检查: SQL> l 1* select owner from dba_tables where table_name='OGG_TC1' SQL> / no rows selected Case Result: 通过
测试Case 2:
目的: 测试dml操作能够复制 在primary,Standby端添加对应表的trandata: GGSCI (oncnjqsvstph09lz00) 4> dblogin userid ogg_primary@onstga, password ogg123456 Successfully logged into database. GGSCI (oncnjqsvstph09lz00) 5> GGSCI (oncnjqsvstph09lz00) 5> add trandata GAA.OGG* Logging of supplemental redo data enabled for table GAA.OGG_TC1. Logging of supplemental redo data enabled for table GAA.OGG_TC2. GGSCI (oncnjqsvstph09lz00) 6> 源端执行DML: SQL> begin 2 for i in 3 .. 300 3 loop 4 insert into GAA.OGG_TC1 values ( i, ' line ---'||i); 5 end loop; 6 commit; 7 end; 8 / PL/SQL procedure successfully completed. 源端检查结果: SQL> l 1* select count(*) from GAA.OGG_TC1 where C1 Between 3 AND 300 SQL> / COUNT(*) ---------- 298 目的端检查结果: SQL> select count(*) from GAA.OGG_TC1 where C1 Between 3 AND 300; COUNT(*) ---------- 298 SQL> 检查trigger 操作是否正常: 1* select count(*) from GAA.OGG_TC2 where C1 Between 3 AND 300 SQL> / COUNT(*) ---------- 298 case Result: 通过
测试case 3:
目的: 反向DML复制 standby端执行: SQL> delete from GAA.OGG_TC1; 299 rows deleted. SQL> commit; primary端检查: SQL> l 1* select count(*) from GAA.OGG_TC1 where C1 Between 3 AND 300 SQL> / COUNT(*) ---------- 0 SQL> c/TC1/TC2 1* select count(*) from GAA.OGG_TC2 where C1 Between 3 AND 300 SQL> / COUNT(*) ----------
0
注意事项:
- 对RAC环境,应严格保证时间同步(NTP)
- 连接ASM时,需要sysdba权限
- 由于是双向复制,trigger需要SUPPRESSTRIGGERS参数特别处理
- cascade 删除特性需要 DBOPTIONS DEFERREFCONST 参数处理
- Sequence 不能复制(在双向环境中)
- Materilized View不能复制(如需复制,需要将Materilized View修改为Updatable)
- 由于不复制DDL操作,任何DDL操作都应该在primary-standby环境都执行后,才能执行后续DML操作。