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

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

到目的库上验证