使用GoldenGate 实现Oracle for Oracle 单向数据同步

使用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

日志显示，已成功同步数据。

到目的库上验证