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

实验环境

数据源端：  host1 ip 192.168.199.163

数据目标端： host2 ip 192.168.199.104

要实现数据的同步，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

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

到目的库上验证

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.log

GGSCI (localhost.localdomain) 31> VIEW REPORT EORAKK

GGSCI (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

数据已同步