欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2023-12-27 17:40:58
...

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

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

到目的库上验证

上一篇:

下一篇: