使用Goldengate 实现Oracle for Oracle 单向数据同步
短信预约 -IT技能 免费直播动态提醒
实验环境
数据源端： 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.sqlhost2
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.logGGSCI (localhost.localdomain) 31> VIEW REPORT EORAKKGGSCI (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 EORAKK3. 增加一个本地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免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/22.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
