实验环境
我们提供的服务有：成都网站设计、做网站、微信公众号开发、网站优化、网站认证、甘泉ssl等。为上千余家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的甘泉网站制作公司
数据源端： host1 ip 192.168.199.163
数据目标端： host2 ip 192.168.199.104
两台机器都安装 http://lqding.blog.51cto.com/9123978/1694971 文中描述安装配置好了Goldengate 。
要实现数据的同步，Oracle源端必须满足如下设置
Oracle需要运行在归档模式下
SQL> startup mount ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 574621728 bytes Database Buffers 255852544 bytes Redo Buffers 2412544 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.
2. 开启日志附加属性
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered.
ogg安装目录下提供了一些demo的sql
[oracle@localhost ogg]$ ls demo_ora_* demo_ora_create.sql demo_ora_insert.sql demo_ora_lob_create.sql demo_ora_misc.sql demo_ora_pk_befores_create.sql demo_ora_pk_befores_insert.sql demo_ora_pk_befores_updates.sql
我们使用demo_ora_create.sql在源和目的端都创建两张表，在源端使用demo_ora_insert.sql插入数据。
host1
SQL> alter user scott identified by tiger account unlock; User altered. SQL> grant resource to scott; #ggsci 登录数据库时需要该权限 Grant succeeded. SQL> grant select any dictionary to scott; # add trandata时需要该权限 Grant succeeded. SQL> conn scott/tiger Connected. SQL> @demo_ora_create.sql SQL> @demo_ora_insert.sql
host2
SQL> alter user scott identified by tiger account unlock; User altered. SQL> grant resource to scott; #ggsci 登录数据库时需要该权限 Grant succeeded. SQL> grant select any dictionary to scott; # add trandata时需要该权限 SQL> conn scott/tiger Connected. SQL> @demo_ora_create.sql
为需要同步的表，添加附加日志
GGSCI (localhost.localdomain) 1> dblogin userid scott, password tiger Successfully logged into database. GGSCI (localhost.localdomain) 2> add trandata scott.tcustmer Logging of supplemental redo data enabled for table SCOTT.TCUSTMER. GGSCI (localhost.localdomain) 3> add trandata scott.tcustord Logging of supplemental redo data enabled for table SCOTT.TCUSTORD.
一、初始化加载数据
在源端配置一个初始化Extract，用来同步表中现有数据
GGSCI (localhost.localdomain) 7> ADD EXTRACT EINILOAD, SOURCEISTABLE EXTRACT added.
ADD EXTRACT命令用来添加一个EXTRACT, EINILOAD为Extract的group name 。SOURCEISTABLE表示数据源为表。
查看Extract的信息
GGSCI (localhost.localdomain) 9> INFO EXTRACT *, TASKS EXTRACT EINILOAD Initialized 2015-09-11 15:25 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Not Available First Record Record 0 Task SOURCEISTABLE
配置初始化加载的捕获参数
GGSCI (localhost.localdomain) 10> edit params EINILOAD -- -- GoldenGate Initial Data Capture -- for TCUSTMER and TCUSTORD -- EXTRACT EINILOAD USERID system, PASSWORD "oracle" RMTHOST 192.168.199.104, MGRPORT 7809 RMTTASK REPLICAT, GROUP RINILOAD TABLE SCOTT.TCUSTMER; TABLE SCOTT.TCUSTORD;
在目标端，配置一个REPLICAT
GGSCI (localhost.localdomain) 2> ADD REPLICAT RINILOAD, SPECIALRUN REPLICAT added.
查看REPLICAT信息
GGSCI (localhost.localdomain) 4> info replicat *, tasks REPLICAT RINILOAD Initialized 2015-08-22 14:18 Status STOPPED Checkpoint Lag 00:00:00 (updated 00:02:50 ago) Log Read Checkpoint Not Available Task SPECIALRUN
配置Replicat参数
GGSCI (localhost.localdomain) 5> edit params riniload -- -- GoldenGate Initial Load Delivery -- REPLICAT RINILOAD ASSUMETARGETDEFS USERID system, PASSWORD "oracle" DISCARDFILE ./dirrpt/RINILOAD.dsc, PURGE MAP scott.*, TARGET scott.*;
启动Extract
GGSCI (localhost.localdomain) 11> start extract einiload Sending START request to MANAGER ... EXTRACT EINILOAD starting
查看日志
GGSCI (localhost.localdomain) 21> view report einiload
如果有报错，查找原因并解决
Processing table SCOTT.TCUSTMER Processing table SCOTT.TCUSTORD *********************************************************************** * ** Run Time Statistics ** * *********************************************************************** Report at 2015-09-11 16:23:40 (activity since 2015-09-11 16:23:33) Output to RINILOAD: From Table SCOTT.TCUSTMER: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 From Table SCOTT.TCUSTORD: # inserts: 2 # updates: 0 # deletes: 0 # discards: 0 REDO Log Statistics Bytes parsed 0 Bytes output 574
日志显示，已成功同步数据。
到目的库上验证
SQL> select count(*) from tcustmer; COUNT(*) ---------- 2 SQL> select count(*) from tcustord; COUNT(*) ---------- 2
二、配置数据实时同步
源端，配置一个实时Extract
GGSCI (localhost.localdomain) 22> ADD EXTRACT EORAKK, TRANLOG, BEGIN NOW, THREADS 1 EXTRACT added.
编辑Extract的参数文件
GGSCI (localhost.localdomain) 23> EDIT PARAMS EORAKK -- -- Change Capture parameter file to capture -- TCUSTMER and TCUSTORD Changes -- EXTRACT EORAKK USERID system, PASSWORD oracle RMTHOST 192.168.199.104, MGRPORT 7809 RMTTRAIL ./dirdat/KK TABLE SCOTT.TCUSTMER; TABLE SCOTT.TCUSTORD;
为Extract添加远端tail file，也就是说这个tail file是在目的端生成的。
GGSCI (localhost.localdomain) 24> ADD RMTTRAIL ./dirdat/KK, EXTRACT EORAKK, MEGABYTES 5 RMTTRAIL added.
验证结果
GGSCI (localhost.localdomain) 28> INFO RMTTRAIL * Extract Trail: ./dirdat/KK Extract: EORAKK Seqno: 0 RBA: 0 File Size: 5M
启动Extract 进程
GGSCI (localhost.localdomain) 29> start extract eorakk Sending START request to MANAGER ... EXTRACT EORAKK starting
验证结果
GGSCI (localhost.localdomain) 30> INFO EXTRACT EORAKK, DETAIL EXTRACT EORAKK Last Started 2015-09-11 17:07 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:01 ago) Log Read Checkpoint Oracle Redo Logs 2015-09-11 17:07:47 Thread 1, Seqno 25, RBA 36139008 SCN 0.1174781 (1174781) Target Extract Trails: Remote Trail Name Seqno RBA Max MB ./dirdat/KK 0 1050 5 Extract Source Begin End /u01/app/oracle/oradata/orcl/redo01.log 2015-09-11 16:58 2015-09-11 17:07 Not Available * Initialized * 2015-09-11 16:58 Current directory /u01/app/ogg Report file /u01/app/ogg/dirrpt/EORAKK.rpt Parameter file /u01/app/ogg/dirprm/eorakk.prm Checkpoint file /u01/app/ogg/dirchk/EORAKK.cpe Process file /u01/app/ogg/dirpcs/EORAKK.pce Stdout file /u01/app/ogg/dirout/EORAKK.out Error log /u01/app/ogg/ggserr.log
GGSCI (localhost.localdomain) 31> VIEW REPORT EORAKK
GGSCI (localhost.localdomain) 32> INFO ALL Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORAKK 00:00:00 00:00:06
目标端，配置REPLICAT
安装checkpoint表
配置checkpoint表的名称
GGSCI (localhost.localdomain) 33> EDIT PARAMS ./GLOBALS HECKPOINTTABLE system.ggschkpt
生成checkpoint表
GGSCI (localhost.localdomain) 1> DBLOGIN USERID system, PASSWORD oracle Successfully logged into database. GGSCI (localhost.localdomain) 2> ADD CHECKPOINTTABLE No checkpoint table specified, using GLOBALS specification (system.ggschkpt)... Successfully created checkpoint table system.ggschkpt. GGSCI (localhost.localdomain) 3>
添加Replicat
GGSCI (localhost.localdomain) 3> ADD REPLICAT RORAKK, EXTTRAIL ./dirdat/KK REPLICAT added.
创建Replicat的参数文件
GGSCI (localhost.localdomain) 4> EDIT PARAMS RORAKK -- -- Change Delivery parameter file to apply -- TCUSTMER and TCUSTORD Changes -- REPLICAT RORAKK USERID system, PASSWORD oracle HANDLECOLLISIONS ASSUMETARGETDEFS DISCARDFILE ./dirrpt/RORAKK.DSC, PURGE MAP scott.tcustmer, TARGET scott.tcustmer; MAP scott.tcustord, TARGET scott.tcustord;
启动Replicat
GGSCI (localhost.localdomain) 5> start replicat rorakk Sending START request to MANAGER ... REPLICAT RORAKK starting
验证结果
GGSCI (localhost.localdomain) 6> info replicat rorakk REPLICAT RORAKK Last Started 2015-08-22 15:49 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:08 ago) Log Read Checkpoint File ./dirdat/KK000000 First Record RBA 1050
验证数据同步
在源数据库，执行如下脚本，对两个表进行insert、update、delete操作
[oracle@localhost ogg]$ sqlplus scott/tiger SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 11 17:58:17 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @/u01/app/ogg/demo_ora_misc.sql
查看源表中的数据
SQL> select count(*) from tcustmer; COUNT(*) ---------- 5 SQL> select count(*) from tcustord; COUNT(*) ---------- 3
查看目的表的数据
SQL> select count(*) from tcustmer; COUNT(*) ---------- 5 SQL> select count(*) from tcustord; COUNT(*) ---------- 3
数据已同步
三、为同步添加data pump
如果没有data pump，Extract将Trail file写到远程机器上。如果为Extract配置了Data Pump。那么Extract将Trail file写到本地。然后Trail 文件由Data Pump传输到远端。
先修改Extract的配置
GGSCI (localhost.localdomain) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EORAKK 00:00:00 00:00:09 GGSCI (localhost.localdomain) 2> stop extract eorakk Sending STOP request to EXTRACT EORAKK ... Request processed. GGSCI (localhost.localdomain) 3> edit params eorakk -- -- Change Capture parameter file to capture -- TCUSTMER and TCUSTORD Changes -- EXTRACT EORAKK USERID system, PASSWORD oracle RMTHOST 192.168.199.104, MGRPORT 7809 EXTTRAIL ./dirdat/KK TABLE SCOTT.TCUSTMER; TABLE SCOTT.TCUSTORD;
将参数文件中的RMTTRAIL ./dirdat/kk 改为 EXTTRAIL ./dirdat/KK
2. 删除Extract的远程Trail
GGSCI (localhost.localdomain) 7> DELETE RMTTRAIL ./dirdat/KK Deleting extract trail ./dirdat/KK for extract EORAKK
3. 增加一个本地Trail
GGSCI (localhost.localdomain) 10> ADD EXTTRAIL ./dirdat/KK, EXTRACT eorakk EXTTRAIL added.
4. 新增Data pump
GGSCI (localhost.localdomain) 12> ADD EXTRACT EPMPKK, EXTTRAILSOURCE ./dirdat/KK EXTRACT added.
Data pump本质上也是一个Extract，只是类型不同而已
为data pump配置参数文件
GGSCI (localhost.localdomain) 14> edit params epmpkk -- -- Data Pump parameter file -- EXTRACT EPMPKK PASSTHRU RMTHOST 192.168.199.104, MGRPORT 7809 RMTTRAIL ./dirdat/KK TABLE SCOTT.TCUSTMER; TABLE SCOTT.TCUSTORD;
为Data Pump添加远端Trail
GGSCI (localhost.localdomain) 15> add rmttrail ./dirdat/KK, EXTRACT EPMPKK RMTTRAIL added.
启动Extract、Data Pump
GGSCI (localhost.localdomain) 16> start extract eorakk Sending START request to MANAGER ... EXTRACT EORAKK starting GGSCI (localhost.localdomain) 17> start extract epmpkk Sending START request to MANAGER ... EXTRACT EPMPKK starting
验证数据同步
在源端，删除两个表的内容
SQL> delete from tcustmer; 5 rows deleted. SQL> delete from tcustord; 3 rows deleted. SQL> commit; Commit complete.
查看目的端表内容
SQL> select * from tcustmer; no rows selected SQL> select * from tcustord; no rows selected