實驗環境

資料來源端： host1 ip 192.168.199.163

資料目標端： host2 ip 192.168.199.104

兩臺機器都安裝文中描述安裝配置好了GoldenGate 。

要實現資料的同步，源端必須滿足如下設定

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

資料已同步

使用GoldenGate 實現Oracle for Oracle 單向資料同步（實現表的DML操作同步）

相關文章