Oracle Goldengate Windows平台Oracle-Oracle单向复制
实验目的
Goldengate最基本的从源端一对一的单向复制，注意其中Goldengate版本取决于Oracle的版本。单向复制一般适用于保持目标数据库的实时更新，且目标数据库用来检索，如报表或者分析使用。
Source DB
操作系统：Windows 10 64 Oracle版本:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production GoldenGate版本：Version 11.2.1.0.1
Target DB
操作系统：Windows 7 64 Oracle版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production GoldenGate：Version 11.2.1.0.1
配置GoldenGate软件
分两种，一种是免安装，一种是通过exe文件进行安装；这里我使用的是免安装，把压缩包解压到c:\ggs目录即可，目标端和源端路径一致。
1.设置环境变量
我的电脑右键属性，选择高级系统设置，高级里面找到环境变量，添加系统变量
完成后，进入DOS命令窗口(win+r)输入：echo %ORACLE_HOME%，echo %ORACLE_SID%
Microsoft Windows [版本 10.0.16299.726] (c) 2017 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>echo %ORACLE_HOME% D:\app\Administrator\product\11.2.0\dbhome_1 C:\Users\Administrator>echo %ORACLE_SID% orcl C:\Users\Administrator>
2.开始配置GoldenGate
将GoldenGate解压至c:\ggs，进入ggs目录，运行ggsci，随后创建子目录
Microsoft Windows [版本 10.0.16299.726] (c) 2017 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>cd \ggs C:\ggs>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Windows x64 (optimized), Oracle 11g on Apr 23 2012 04:55:02 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (DESKTOP-8NP7VO0) 1> Create subdirs Creating subdirectories under current directory C:\ggs Parameter files C:\ggs\dirprm: already exists Report files C:\ggs\dirrpt: created Checkpoint files C:\ggs\dirchk: created Process status files C:\ggs\dirpcs: created SQL script files C:\ggs\dirsql: created Database definitions files C:\ggs\dirdef: created Extract data files C:\ggs\dirdat: created Temporary files C:\ggs\dirtmp: created Stdout files C:\ggs\dirout: created
主目录介绍
dirchk：存放检查点（Checkpoint）文件
dirdat：存放Trail与Extract文件，以后详述
dirdef：通过DEFGEN工具生成的源或目标的数据定义文件
dirpcs：存放进程状态文件
dirprm：存放参数文件
dirrpt：存放进程报告文件
dirsql：存放SQL脚本文件
dirtmp：当事务所需要的内存超过已分配内存时，缺省存储于此。
3.添加manager进程到Windows系统服务
edit params ./GLOBALS命令（注意./GLOBALS为大写），进入编辑文件，输入下面内容：
MGRSERVNAME GGMGR
保存退出后，用install addservice命令添加服务，如下所示
GGSCI (DESKTOP-8NP7VO0) 2> edit params ./GLOBALS GGSCI (DESKTOP-8NP7VO0) 3> exit C:\ggs>install addservice Service 'GGMGR' created. Install program terminated normally.
4.命令解释
GGSCI (DESKTOP-8NP7VO0) 1> help GGSCI Command Summary: Object: Command: SUBDIRS CREATE ER INFO, KILL, LAG, SEND, STATUS, START, STATS, STOP EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, REGISTER, SEND, START, STATS, STATUS, STOP UNREGISTER EXTTRAIL ADD, ALTER, DELETE, INFO GGSEVT VIEW MANAGER INFO, SEND, START, STOP, STATUS MARKER INFO PARAMS EDIT, VIEW REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO, KILL, LAG, SEND, START, STATS, STATUS, STOP REPORT VIEW RMTTRAIL ADD, ALTER, DELETE, INFO TRACETABLE ADD, DELETE, INFO TRANDATA ADD, DELETE, INFO SCHEMATRANDATA ADD, DELETE, INFO CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO Commands without an object: (Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD, FLUSH SEQUENCE MININGDBLOGIN (DDL) DUMPDDL (Miscellaneous) FC, HELP, HISTORY, INFO ALL, OBEY, SET EDITOR, SHELL, SHOW, VERSIONS, ! (note: you must type the word COMMAND after the ! to display the ! help topic.) i.e.: GGSCI (sys1)> help ! command For help on a specific command, type HELP <command> <object>. Example: HELP ADD REPLICAT GGSCI (DESKTOP-8NP7VO0) 2>
配置Oracle数据库
1.进入数据库SYS用户创建GoldenGate管理用户，并授予相应的权限，测试环境直接给DBA即可，生产环境请严格控制
CREATE USER GGS IDENTIFIED BY "ggs" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT DBA TO GGS_SOURCE;
2.在源端打开归档模式，我这里已经配置好了，按照下面的命令执行即可
SQL>archive log list; SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>startup force;
3.查看是否打开数据库级别的补充日志,返回结果为Y即可
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
如果查询结果不是Y，需要打开数据库级别的补充日志
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
4.增加表级最小日志
AlTER TABLE BJXXJGXT.RYJBXXB ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
AlTER TABLE BJXXJGXT.KSSZPB ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
5.在源端库中关闭回收站，如果是oracle 10.1版本使用alter system set "_recyclebin=off"
SQL> show parameter recyclebin NAME TYPE VALUE ------------- ----------- ------------------------------ recyclebin string on SQL> alter system set recyclebin=off; System altered.
6.在源端添加表级的Trandata，表名可以使用通配符，如add trandata bjxxjgxt.*
GGSCI (DESKTOP-8NP7VO0) 1> dblogin userid ggs_test,password 123456 Successfully logged into database. GGSCI (DESKTOP-8NP7VO0) 2> add trandata bjxxjgxt.ryjbxxb Logging of supplemental redo log data is already enabled for table BJXXJGXT.RYJBXXB.
配置goldengate进程组-目标端
在源端：mgr进程，extract（抽取）进程，pump进程
在目标端：mgr进程，replicat（复制）
1.配置源端mgr管理进程
GGSCI (DESKTOP-8NP7VO0) 3> edit params mgr GGSCI (DESKTOP-8NP7VO0) 4> view params mgr port 7500 #MGR进程通信端口 dynamicportlist 7501-7505 #Manager进程可以为源端和目标端的动态的指定端口 autorestart extract *,waitminutes 2,retries 5 #autorestart extract表示自动重启Extract进程组,每次尝试的时间间隔为2秒，最多尝试5次，如果没有成功则放弃
启动mgr
GGSCI (DESKTOP-8NP7VO0) 5> start mgr Starting Manager as service ('GGMGR')... Service started. GGSCI (DESKTOP-8NP7VO0) 6> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
2.配置源端Extract抽取进程组
编辑添加extract进程配置文件
GGSCI (DESKTOP-8NP7VO0) 7> edit params eora
添加如下内容
extract eora #表一个名为eora的extract进程 dynamicresolution #GoldenGatex动态解析源端表名 userid ggs_test,password 123456 #ogg专用用户密码 --setenv(ORACLE_SID=ORCL) #设置环境变量 exttrail c:\ggs\dirdat\et #文件目录和标识（类似于et*这样文件)，命名只能是2个字符。 table bjxxjgxt.ryjbxxb; #同步那些表,表名可以用通配符*代替,*代表该用户下所有表
添加extract进程
GGSCI (DESKTOP-8NP7VO0) 8> add extract eora,tranlog,begin now EXTRACT added.
创建本地trail文件，extract进程组负责写这部分文件，pump进程负责读取它
GGSCI (DESKTOP-8NP7VO0) 9> add exttrail c:\ggs\dirdat\et,extract eora EXTTRAIL added.
启动eora进程，使用start eora或start ext eora或start extract eora
GGSCI (DESKTOP-8NP7VO0) 10> start eora Sending START request to MANAGER ('GGMGR') ... EXTRACT EORA starting GGSCI (DESKTOP-8NP7VO0) 11> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA 00:00:25 00:00:01
3.配置源端pump进程组
编辑添加pump进程配置文件
GGSCI (DESKTOP-8NP7VO0) 12> edit params pump
添加如下内容
extract pump #一个名为pump的extract进程 Dynamicresolution #GoldenGatex动态解析源端表名 userid ggs_test,password 123456 #ogg用户和密码 rmthost 192.168.11.91, mgrport 7500 #指定目标端IP和mgr端口 --Setenv(ORACLE_SID=ORCL) #设置环境变量 rmttrail c:\ggs\dirdat\et # table bjxxjgxt.ryjbxxb;
添加pump进程
GGSCI (DESKTOP-8NP7VO0) 13> add extract pump,exttrailsource c:\ggs\dirdat\et EXTRACT added.
添加该rmttrail
GGSCI (DESKTOP-8NP7VO0) 14> add rmttrail c:\ggs\dirdat\et,extract pump RMTTRAIL added.
启动pump进程，最终结果如下图所示，如有异常请查看进程组日志
GGSCI (DESKTOP-8NP7VO0) 15> start pump Sending START request to MANAGER ('GGMGR') ... EXTRACT PUMP starting GGSCI (DESKTOP-8NP7VO0) 16> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORA 00:00:00 00:00:03 EXTRACT RUNNING PUMP 00:00:00 00:00:24
配置目标端MGR管理进程
1.依据源端的配置，配置目标端mgr进程；在目标机器上编辑GLOBALS文件，添加一行，checkpointtable ggs.checkpoint
Microsoft Windows [版本 6.1.7601] 版权所有 (c) 2009 Microsoft Corporation。保留所有权利。 C:\Users\Administrator>cd \ggs C:\ggs>ggsci Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230 Windows x64 (optimized), Oracle 11g on Apr 23 2012 04:55:02 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved. GGSCI (Roobbin-PC) 1> create subdirs Creating subdirectories under current directory C:\ggs Parameter files C:\ggs\dirprm: already exists Report files C:\ggs\dirrpt: created Checkpoint files C:\ggs\dirchk: created Process status files C:\ggs\dirpcs: created SQL script files C:\ggs\dirsql: created Database definitions files C:\ggs\dirdef: created Extract data files C:\ggs\dirdat: created Temporary files C:\ggs\dirtmp: created Stdout files C:\ggs\dirout: created GGSCI (Roobbin-PC) 2> edit params ./GLOBAL
添加如下内容
MGRSERVNAME GGMGR
checkpointtable ggs.checkpoint
添加系统服务
GGSCI (Roobbin-PC) 3> exit C:\ggs>install addservice Service 'GGMGR' created. Install program terminated normally.
增加检测机制
GGSCI (Roobbin-PC) 1> dblogin userid ggs_test,password 123456 Successfully logged into database. GGSCI (Roobbin-PC) 2> add checkpointtable ggs_test.checkpoint Successfully created checkpoint table ggs_test.checkpoint.
2.依据源端的配置，配置目标端mgr进程
GGSCI (Roobbin-PC) 3> edit params mgr
添加如下内容
port 7500 dynamicportlist 7501-7505 autostart er * autorestart extract *,waitminutes 2,retries 5 lagreporthours 1 laginfominutes 3 lagcriticalminutes 5 purgeoldextracts c:\gg\dirdat\et*,usecheckpoints,minkeepdays 3
3.配置目标端Replicat复制进程组
编辑添加Replicat进程配置文件
GGSCI (Roobbin-PC) 5> edit params repl
添加如下内容
replicat repl userid ggs_test,password 123456 assumetargetdefs reperror default,discard discardfile ./dirrpt/repl.dsc,append,megabytes 50 dynamicresolution MAP bjxxjgxt.ryjbxxb, target bjxxjgxt.ryjbxxb;
添加replicat进程
GGSCI (Roobbin-PC) 6> add replicat repl,exttrail c:\ggs\dirdat\et,checkpointtable ggs_test.checkpoint REPLICAT added.
启动mgr，repl
GGSCI (Roobbin-PC) 19> start mgr Starting Manager as service ('GGMGR')... Service started. GGSCI (Roobbin-PC) 20> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING REPL 00:00:00 00:00:08
随后就是验证DDL操作结果。这个就自行去操作把，这里不多讲了，有问题就查日志
