使用GoldenGate 实现Oracle for Oracle 单向数据同步
- WBOY原創
- 2016-06-07 14:59:131209瀏覽
使用GoldenGate 实现Oracle for Oracle 单向数据同步
实验环境
数据源端： host1 ip 192.168.199.163
数据目标端： host2 ip 192.168.199.104
两台机器都安装 文中描述安装配置好了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，用来同步表中现有数据
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;
在目标端，配置一个REPLICAT
1
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
--
-- 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
日志显示，已成功同步数据。
到目的库上验证