首页 / 数据库 / MySQL / 使用GoldenGate 实现Oracle for Oracle 单向数据同步
实验环境数据源端： host1 ip 192.168.199.163 数据目标端： host2 ip 192.168.199.104两台机器都安装 http://www.linuxidc.com/Linux/2015-09/123120.htm 文中描述安装配置好了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插入数据。host1SQL> 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.sqlhost2SQL> 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.
Logging of supplemental redo data enabled for table SCOTT.TCUSTORD.一、初始化加载数据在源端配置一个初始化Extract，用来同步表中现有数据1 2 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;在目标端，配置一个REPLICAT1 2 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
*********************************************************************** * ** Run Time Statistics ** * ***********************************************************************
Report at 2015-09-11 16:23:40 （activity since 2015-09-11 16:23:33）
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;
易网时代-编程资源站