程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle GoldenGate 二、配置和使用，oraclegoldengate

Oracle GoldenGate 二、配置和使用，oraclegoldengate

編輯：Oracle教程

Oracle GoldenGate 二、配置和使用，oraclegoldengate

配置和使用GoldenGate的步驟

1 在源端和目標端配置數據庫支持GoldenGate
2 在源端和目標端創建和配置GoldenGate實例
3 在源端創建和配置主抽取進程(Primary Extract)
4 在源端創建和配置Data Pump進程(Secondly Extract)
5 在目標端創建和配置Replicat進程

1 配置數據庫支持GoldenGate

1.1 OGG用戶和權限分配

　　GoldenGate需要從在線日子或歸檔日志抽取捕獲系統的變更數據信息，這些信息可能來源於業務用戶，可能來源於系統用戶，為了使GoldenGate能夠抽取這些數據應為GoldenGate創建獨立的用戶和分配必要的權限以滿足系統運行需求，這些權限包括讀取業務用戶表數據的權限、讀取系統表的權限、執行某個系統包的權限等，以下腳步創建GoldenGate用戶ogg_owner(源用戶)、ogg_trg(目標用戶)和GoldenGate角色ogg_role:

[oracle@sywu ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 21 14:11:04 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SYS@sydb>create tablespace tbs01
   datafile '+oradata'
   size 10m
   autoextend on
   uniform size 2m
/
create user ogg_owner identified by ogg_owner default tablespace tbs01 quota unlimited on tbs01
/
create user ogg_trg identified by ogg_trg default tablespace tbs01 quota unlimited on tbs01
/
create role ogg_role
/

為易管理和維護統一將權限賦予角色ogg_role:

grant 
CREATE SESSION,
ALTER SESSION,
ALTER SYSTEM,
RESOURCE,
SELECT ANY DICTIONARY,
FLASHBACK ANY TABLE,
SELECT ANY TABLE,
SELECT ANY TRANSACTION,
insert any table,
update any table,
drop any table,
CREATE TABLE
to ogg_role;

grant SELECT on dba_clusters to ogg_role;
grant SELECT on V_$DATABASE to ogg_role;
grant select on sys.logmnr_buildlog to ogg_role;
grant EXECUTE on DBMS_FLASHBACK to ogg_role;
grant execute on DBMS_CAPTURE_ADM to ogg_role;
grant execute on DBMS_STREAMS to ogg_role;  
grant EXECUTE_CATALOG_ROLE to ogg_role;

然後再將ogg_role 角色賦予ogg_owner(源用戶)和ogg_trg(目標用戶):

grant ogg_role to ogg_owner;
grant ogg_role to ogg_trg;

源用戶測試表和數據:

SYS@sydb>create table ogg_owner.togg(id primary key,name,type,CREATED,update_date)
  2        as 
  3      select object_id,object_name,object_type,CREATED,sysdate from dba_objects
  4        where rownum<1001
  5     /

Table created.
Elapsed: 00:00:00.28

1.2 數據庫附加日志

附加日志級別分為:

數據庫級別的附加日志(必須附加日志)
用戶級別的附加日志(當使用GoldenGate DDL抽取功能時要啟用該級別)
表級別的附加日志(必須附加日志，除非你啟用了用戶級別的附加日志)

1.2.1 數據庫級別的附加日志

　　因為GoldenGate需要抽取捕獲變更數據信息和元數據信息，這些信息需要記錄日志，並且因為日志總是持續增長的，這些日志增長的信息對於GoldenGate是必須記錄的，所以必須在GoldenGate進程啟動前開啟數據庫級別的附加日志；
檢查數據庫是否開啟附加日志:

SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FOR
-------- ---
NO       NO

開啟數據庫級別的附加日志:

SYS@sydb>alter database add supplemental log data;
SYS@sydb>ALTER DATABASE FORCE LOGGING;

SYS@sydb>SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FOR
-------- ---
YES      YES
Elapsed: 00:00:00.00
SYS@sydb>alter system switch logfile;

System altered.
Elapsed: 00:00:00.09

1.2.2 用戶級別的附加日志

　　開啟用戶級別的附加日志的管理用戶必須具有執行DBMS_CAPTURE_ADM包的權限；可以在GoldenGate GGSCI命令行下登錄到數據庫管理用戶為其它的用戶開啟附加日志；
在GGSCI命令行下使用DBLOGIN命令登錄到數據庫管理用戶

GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner
Successfully logged into database.

為用戶啟用附加日志

GGSCI (sywu as ogg_owner@sydb) 2> add schematrandata sywu

2015-08-24 14:56:26  INFO    OGG-01788  SCHEMATRANDATA has been added on schema sywu.

2015-08-24 14:56:27  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema sywu.

取消用戶附加日志

GGSCI (sywu as ogg_owner@sydb) 6> delete schematrandata sywu

2015-08-25 20:14:49  INFO    OGG-01792  SCHEMATRANDATA has been deleted on schema sywu.

2015-08-25 20:14:49  INFO    OGG-01979  SCHEMATRANDATA for scheduling columns has been deleted on schema sywu.

1.2.3 表級別的附加日志

　　在沒有啟用用戶級別的附加日志的情況下表級別的主鍵或唯一索引附加日志對於GoldenGate是必須的；在某些情況下即使你啟用了用戶級別的附加日志，你也可以啟用表級別的附加日志使主鍵附加日志替代每一個在用戶級別為GoldenGate指定的鍵；必須在表沒有主鍵或唯一索引的情況下啟用表級別的附加日志。
在GGSCI命令行下使用dblogin登錄到數據庫管理用戶

GGSCI (sywu) 1> dblogin userid ogg_owner,password ogg_owner
Successfully logged into database.

為表啟用附加日志

GGSCI (sywu as ogg_owner@sydb) 3> add trandata ogg_owner.togg

Logging of supplemental redo data enabled for table OGG_OWNER.TOGG.
TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG'.

注意:僅當該表有主鍵或唯一索引時使用上面的命令啟用表級別的附加日志，如果表中沒有主鍵或唯一索引時則必須指定一個或多個或者全部列做為主鍵，在GoldenGate這些鍵的作用是過濾重復的數據。

SYS@sydb>create table ogg_owner.togg_nokey(id,name,type,CREATED,update_date) 
  2     as 
  3   select object_id,object_name,object_type,CREATED,sysdate from dba_objects
   where rownum<1001
  5  /

Table created.

Elapsed: 00:00:00.11

錯誤的啟用沒有主鍵的表級別附加日志:

GGSCI (sywu as ogg_owner@sydb) 4> add trandata ogg_owner.togg_nokey

2015-08-24 16:05:01  WARNING OGG-06439  No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY.
TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.

正確的啟用沒有主鍵的表級別附加日志:

GGSCI (sywu as ogg_owner@sydb) 26>  add trandata ogg_owner.togg_nokey,cols (ID),nokey

2015-08-24 16:33:19  WARNING OGG-06439  No unique key is defined for table TOGG_NOKEY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Logging of supplemental redo data enabled for table OGG_OWNER.TOGG_NOKEY.
TRANDATA for scheduling columns has been added on table 'OGG_OWNER.TOGG_NOKEY'.

2 配置GoldenGate

2.1 GoldenGate MGR進程

　　MGR進程管理啟動Oracle GoldenGate進程、啟動動態進程、分配端口給GoldenGate進程、管理trail file、創建事件，錯誤和診斷報告工作，必須在第一時間啟動；當某些原因導致GoldenGate崩潰或重啟機器時，默認情況MGR是沒有啟動的

[oracle@sywu ogg_src]$ tggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (sywu) 1> info mgr

Manager is DOWN!

2.1.1 配置GoldenGate MGR進程

　　可以通過直接編輯GoldenGate_home/dirprm/mgr.prm文件或進入GGSCI命令行後鍵入edit param mgr命令回車進入MGR配置文件vi編輯界面;

GGSCI (sywu as ogg_owner@sydb) 10> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7820
AUTOSTART ER E*
AUTORESTART ER P*,RETRIES 4, WAITMINUTES 4
STARTUPVALIDATIONDELAY 5

參數說明 PORT 表示MGR進程端口號 DYNAMICPORTLIST 表示MGR進程動態為其它進程如Extract進程、Replicat進程分配的端口 AUTOSTART、AUTORESTART 表示當MGR進程啟動後失敗時自動啟動或重啟的GoldenGate進程

2.1.2 啟動mgr進程

GGSCI (sywu) 2> start mgr
Manager started.

GGSCI (sywu) 3> info mgr

Manager is running (IP port sywu.7909, Process ID 17400).

啟動原理:通過讀取GoldenGate_home/dirprm/mgr.prm文件，然後根據該文件的配置信息啟動進程分配端口號，如果該進程啟動失敗，首先請檢查預使用的端口是否被占用:

netstat -lntup|grep 7809
tcp        0      0 :::7809                     :::*                        LISTEN      32426/./mgr

然後檢查相關的配置文件或重新配置MGR。

2.2 在源端創建和配置Extract進程

創建和配置Extract進程的工作有:

創建和配置主抽取進程(Primary Extract)
創建和配置Data Pump進程(Secondly Extract)

2.2.1 創建主抽取進程(Primary Extract)

進入GGSCI命令行使用add extract 命令創建主抽取進程

[oracle@sywu ~]$ sggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (sywu as ogg_owner@sydb) 13> add extract esydb001,tranlog,begin now
EXTRACT added.

因為主進程的作用是抽取捕獲系統變更數據並將這些數據保存到trail文件裡，所以必須為其配置trail文件目錄和trail文件名的兩個字符名，trail文件名共8個字符，其余6個字符由GoldenGate系列填充;

GGSCI (sywu as ogg_owner@sydb) 14> ADD EXTTRAIL /u01/app/product/ogg_src/dirdat/es, EXTRACT esydb001
EXTTRAIL added.

2.2.1.1 配置主抽取進程(Primary Extract)參數

GGSCI (sywu as ogg_owner@sydb) 15> edit param esydb001

extract esydb001
SETENV(ORACLE_SID="sydb")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner, password ogg_owner
EXTTRAIL /u01/app/product/ogg_src/dirdat/es
table ogg_owner.togg;

userid指定GoldenGate 抽取用戶的用戶名和密碼；
SETENV 設置環境變量，如實例名、數據庫字符集；
table 指定抽取的用戶和表名，如果指定多個以相同字符開頭或結尾的表名，可以使用“前綴* ”的方式代替，這裡僅以ogg_owner.togg表為測試案例;

2.2.2 創建和配置Data Pump進程(Secondly Extract)

添加Data Pump Extract進程時要注意，如果源端和目標端OGG物理路徑都相同，可以按照常規方式處理，但是如果不相同，就像我本例的一樣源端和目標端都在同一台機器上，但GoldenGate物理路徑不相同，請按如下方式處理

2.2.2.1 創建Data Pump Extract進程

GGSCI (sywu) 26> ADD EXTRACT PSYDB001, EXTTRAILSOURCE /u01/app/product/ogg_src/dirdat/es
EXTRACT added.

EXTTRAILSOUCE 指定源端的trail路徑，必須包含兩個字符，這個路徑和主抽取進程(Primary Extract)中指定的trail目錄和trail文件命名必須相同，因為Data Pump進程要讀取主抽取進程生成的trail文件;

2.2.2.2 配置Data Pump Extract進程

edit param psydb001

extract psydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg_owner,password ogg_owner
RMTHOST sywu,mgrport 7909
RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps  --這裡指定目標端trail文件位置，同樣包含兩個字符
table ogg_owner.togg;

RMTHOST 指定目標端地址和端口等信息；
RMTTRAIL 指定目標端保存trail文件的目錄和兩個字符文件名；
TABLE 指定同步的表，配置的方式同在主抽取進程(Primary Extract)的配置一樣，但這裡多了許多額外的功能，比如實現數據過濾和其它復雜操作；

2.2.2.3 將目標端trail文件添加到隊列中

　　源端可以配置多個主抽取進程，也可以配置多個Data Pump進程，但必須為每個要同步的目標端配置一個Data Pump進程；

GGSCI (sywu) 10> ADD RMTTRAIL /u01/app/product/ogg_trg/dirdat/ps, EXTRACT PSYDB001
RMTTRAIL added.

2.3 在目標端配置Replicat

目標端需要做的工作:

創建和配置Checkpoint Table
創建和配置Replicat進程

2.3.1 創建和配置Checkpoint Table

　　目標端Replicat進程監控Checkpoint Table，每一條接收到的checkpoint數據由Collector後台進程寫入到Checkpoint Table，因為對於事務來說每一條checkpoint數據要麼執行成功要麼執行失敗，所以Replicat進程確保每一條checkpoint事務數據只應用一次，即使其它進程錯誤或者數據庫錯誤。
在目標端GSSCI命令行下使用dblogin登錄到目標端OGG管理用戶

[oracle@sywu ogg_src]$ tggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (sywu) 1> dblogin userid ogg_trg,password ogg_trg
Successfully logged into database.

創建checkpoint table

GGSCI (sywu as ogg_trg@sydb) 2> add checkpointtable ogg_trg.ogg_chk

Successfully created checkpoint table ogg_trg.ogg_chk.

將checkpoint table 定義在GoldenGate配置文件裡

GGSCI (sywu) 1> edit param ./GLOBALS
GGSCHEMA OGG_TRG
CHECKPOINTTABLE OGG_TRG.OGG_CHK

首次編輯GoldenGate配置文件時，GLOBALS文件以大寫命名並且沒有擴展名創建在GoldenGate_home根目錄。

2.3.2 創建和配置Replicat進程

　　當GoldenGate運行時會產生一些與同步數據不相關的文件，這些文件記錄了GoldenGate進程錯誤、數據庫錯誤、GoldenGate操作等信息，這個文件叫做Discard file，配置Replicat進程時由DISCARDFILE參數定義，這個參數是可選的，但推薦創建相應的目錄並配置，方便診斷GoldenGate錯誤問題；
創建Discard file目錄

[oracle@sywu ogg_trg]$ mkdir /u01/app/product/ogg_trg/discrd
[oracle@sywu ogg_trg]$ chmod 775 /u01/app/product/ogg_trg/discrd/

目標端創建Replicat進程

GGSCI (sywu) 27> add replicat rsydb001,exttrail /u01/app/product/ogg_trg/dirdat/ps,checkpointtable OGG_TRG.OGG_CHK
REPLICAT added.

編輯Replicat進程配置文件

REPLICAT rsydb001
SETENV(ORACLE_SID="sydb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg_trg,password ogg_trg
DISCARDFILE /u01/app/product/ogg_trg/discrd/reptr.desc,append,megabytes 512
ALLOWNOOPUPDATES
ASSUMETARGETDEFS
MAP OGG_OWNER.TOGG,target OGG_TRG.TOGG;

到這裡簡單的源端和目標端的MGR、Extract、Replicat進程就配置完了，測試配置文件是否錯誤，進程啟動是否正常，如果進程有問題就使用“view report 進程名”查看錯誤；不要將所有進程啟動，因為有些工作還沒有完成。

3 初始化數據

為什麼要初始化數據？
　　因為DML操作包括INSERT、UPDATE、DELETE、SELECT操作，而在這些操作中UPDATE、DELETE操作Redo只記錄了變更的數據列以及行ID(ROWID)，GoldenGate抽取數據後將其轉換為自己的格式發送都目標端，假設源端有100條數據，從08:00 AM開始同步到目標端，而在同步開始前目標端沒有初始化數據(目標端為空數據)，那麼08:00 AM後事物產生的UPDATE、DELETE DML操作發送到目標端，目標端GoldenGate Replicat進程會因為找不到數據而報錯從而導致Replicat進程崩潰停止(ABENDED)，所以這就需要我們在同步前初始化數據，初始化完後再同步，這樣大大降低錯誤率。同步數據的方式可以通過DBLINK、EXP/IMP、SQLLDR或者表空間遷移等方式同步；我的測試都在同一個庫所以就直接創建表了。

SYS@sydb>create table ogg_trg.togg as select * from ogg_owner.togg
  2  /

Table created.

Elapsed: 00:00:00.24

4 使用GoldenGate

數據初始化後，分別啟動源端MGR進程、主抽取進程(Primary Extract)、Data Pump進程(Secondly Extract)以及目標端MGR進程、Replicat進程；
啟動源端GoldenGate進程

[oracle@sywu ~]$ sggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (sywu) 1> start mgr
Manager started.

GGSCI (sywu) 2> start PSYDB001

Sending START request to MANAGER ...
EXTRACT PSYDB001 starting

GGSCI (sywu) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     ESYDB001    00:00:00      00:00:02    
EXTRACT     RUNNING     PSYDB001    00:00:00      12:41:34

啟動目標端GoldenGate進程

[oracle@sywu ~]$ tggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.

GGSCI (sywu) 1> start mgr
Manager started.

GGSCI (sywu) 2> start RSYDB001

Sending START request to MANAGER ...
REPLICAT RSYDB001 starting

GGSCI (sywu) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     RSYDB001    00:00:00      00:00:07

進程都啟動完了，此時觀察源端和目標端trail文件生成情況

[oracle@sywu ~]$ ls -ltr /u01/app/product/ogg_src/dirdat
total 324
-rw-r-----. 1 oracle oinstall   1418 Aug 26 09:42 es000000
-rw-r-----. 1 oracle oinstall   1481 Aug 26 09:48 es000001
-rw-r-----. 1 oracle oinstall   1481 Aug 26 09:50 es000002
-rw-r-----. 1 oracle oinstall   1481 Aug 26 09:52 es000003
-rw-r-----. 1 oracle oinstall   1481 Aug 26 09:52 es000004
[oracle@sywu ~]$ ls -ltr /u01/app/product/ogg_trg/dirdat
total 324
-rw-r-----. 1 oracle oinstall 193469 Aug 25 13:00 ps000000
-rw-r-----. 1 oracle oinstall    891 Aug 25 13:00 ps000001
-rw-r-----. 1 oracle oinstall   1652 Aug 25 13:07 ps000002
-rw-r-----. 1 oracle oinstall    891 Aug 25 13:07 ps000003
-rw-r-----. 1 oracle oinstall 105884 Aug 25 13:49 ps000004
-rw-r-----. 1 oracle oinstall    891 Aug 25 13:49 ps000005
-rw-r-----. 1 oracle oinstall   2095 Aug 26 09:54 ps000006
-rw-r-----. 1 oracle oinstall   1201 Aug 26 09:54 ps000007
-rw-r-----. 1 oracle oinstall   2032 Aug 26 09:54 ps000008

源端進程信息

GGSCI (sywu) 4> info ESYDB001,detail

EXTRACT    ESYDB001  Last Started 2015-08-26 09:52   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:10 ago)
Process ID           14619
Log Read Checkpoint  Oracle Redo Logs
                     2015-08-26 09:58:11  Seqno 13, RBA 1942016
                     SCN 0.575310 (575310)

  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  /u01/app/product/ogg_src/dirdat/es                   4       1481        100 EXTTRAIL  

  Extract Source                          Begin             End             

  /u01/oradata/sydb/redo01.log            2015-08-26 09:52  2015-08-26 09:58
  /u01/oradata/sydb/redo01.log            2015-08-26 09:49  2015-08-26 09:52
  /u01/oradata/sydb/redo01.log            2015-08-26 09:43  2015-08-26 09:49
  /u01/oradata/sydb/redo01.log            2015-08-26 09:40  2015-08-26 09:43
  /u01/oradata/sydb/redo01.log            2015-08-25 20:43  2015-08-26 09:40
  Not Available                           * Initialized *   2015-08-25 20:43
  Not Available                           * Initialized *   2015-08-25 20:43

Current directory    /u01/app/product/ogg_src

Report file          /u01/app/product/ogg_src/dirrpt/ESYDB001.rpt
Parameter file       /u01/app/product/ogg_src/dirprm/esydb001.prm
Checkpoint file      /u01/app/product/ogg_src/dirchk/ESYDB001.cpe
Process file         /u01/app/product/ogg_src/dirpcs/ESYDB001.pce
Error log            /u01/app/product/ogg_src/ggserr.log

GGSCI (sywu) 5> info PSYDB001,detail

EXTRACT    PSYDB001  Last Started 2015-08-26 09:52   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Process ID           14648
Log Read Checkpoint  File /u01/app/product/ogg_src/dirdat/es000004
                     2015-08-26 09:52:50.035063  RBA 1481
  Target Extract Trails:

  Trail Name                                       Seqno        RBA     Max MB Trail Type

  /u01/app/product/ogg_trg/dirdat/ps                   0          0        100 EXTTRAIL  
  Extract Source                          Begin             End             

  /u01/app/product/ogg_src/dirdat/es000004  * Initialized *   2015-08-26 09:52
  /u01/app/product/ogg_src/dirdat/es000000  * Initialized *   First Record    
  /u01/app/product/ogg_src/dirdat/es000000  * Initialized *   First Record    
  /u01/app/product/ogg_src/dirdat/es000000  * Initialized *   First Record    
  /u01/app/product/ogg_src/dirdat/es000000  * Initialized *   First Record    

Current directory    /u01/app/product/ogg_src

Report file          /u01/app/product/ogg_src/dirrpt/PSYDB001.rpt
Parameter file       /u01/app/product/ogg_src/dirprm/psydb001.prm
Checkpoint file      /u01/app/product/ogg_src/dirchk/PSYDB001.cpe
Process file         /u01/app/product/ogg_src/dirpcs/PSYDB001.pce
Error log            /u01/app/product/ogg_src/ggserr.log

GGSCI (sywu) 6> stats ESYDB001

Sending STATS request to EXTRACT ESYDB001 ...

No active extraction maps.

GGSCI (sywu) 7> stats PSYDB001

Sending STATS request to EXTRACT PSYDB001 ...

No active extraction maps.

目標端進程信息

GGSCI (sywu) 5> info RSYDB001,detail

REPLICAT   RSYDB001  Last Started 2015-08-26 09:55   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:08 ago)
Process ID           15105
Log Read Checkpoint  File /u01/app/product/ogg_trg/dirdat/ps000008
                     First Record  RBA 2032

Current Log BSN value: (requires database login)

Last Committed Transaction CSN value: (requires database login)

  Extract Source                          Begin             End             

  /u01/app/product/ogg_trg/dirdat/ps000008  2015-08-26 09:52  First Record    
  /u01/app/product/ogg_trg/dirdat/ps000008  2015-08-25 13:58  2015-08-26 09:52
  /u01/app/product/ogg_trg/dirdat/ps000006  2015-08-25 13:49  2015-08-25 13:58
  /u01/app/product/ogg_trg/dirdat/ps000006  2015-08-25 13:49  2015-08-25 13:49
  /u01/app/product/ogg_trg/dirdat/ps000006  2015-08-25 13:49  2015-08-25 13:49
  /u01/app/product/ogg_trg/dirdat/ps000006  * Initialized *   2015-08-25 13:49
  /u01/app/product/ogg_trg/dirdat/ps000006  * Initialized *   First Record    
  /u01/app/product/ogg_trg/dirdat/ps000000  * Initialized *   First Record    
  /u01/app/product/ogg_trg/dirdat/ps000000  * Initialized *   First Record    
  /u01/app/product/ogg_trg/dirdat/ps000000  * Initialized *   First Record    
  /u01/app/product/ogg_trg/dirdat/ps000000  * Initialized *   First Record    

Current directory    /u01/app/product/ogg_trg

Report file          /u01/app/product/ogg_trg/dirrpt/RSYDB001.rpt
Parameter file       /u01/app/product/ogg_trg/dirprm/rsydb001.prm
Checkpoint file      /u01/app/product/ogg_trg/dirchk/RSYDB001.cpr
Checkpoint table     OGG_TRG.OGG_CHK
Process file         /u01/app/product/ogg_trg/dirpcs/RSYDB001.pcr
Error log            /u01/app/product/ogg_trg/ggserr.log

GGSCI (sywu) 6> stats RSYDB001

Sending STATS request to REPLICAT RSYDB001 ...

No active replication maps.

在源端產生DML操作

OGG_OWNER@sydb>insert into togg(id,name)values(1003,'tt') ;

1 row created.

Elapsed: 00:00:00.00
OGG_OWNER@sydb>insert into togg(id,name,type)values(1004,'sywu','user');

1 row created.

Elapsed: 00:00:00.01
OGG_OWNER@sydb>commit;

Commit complete.

Elapsed: 00:00:00.01

再次查看源端進程狀態

GGSCI (sywu) 10> stats ESYDB001

Sending STATS request to EXTRACT ESYDB001 ...

Start of Statistics at 2015-08-26 10:04:54.

Output to /u01/app/product/ogg_src/dirdat/es:

Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:

*** Total statistics since 2015-08-26 10:04:33 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2015-08-26 10:04:33 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Hourly statistics since 2015-08-26 10:04:33 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Latest statistics since 2015-08-26 10:04:33 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.

GGSCI (sywu) 11> stats PSYDB001

Sending STATS request to EXTRACT PSYDB001 ...

Start of Statistics at 2015-08-26 10:04:58.

Output to /u01/app/product/ogg_trg/dirdat/ps:

Extracting from OGG_OWNER.TOGG to OGG_OWNER.TOGG:

*** Total statistics since 2015-08-26 10:04:35 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2015-08-26 10:04:35 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Hourly statistics since 2015-08-26 10:04:35 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Latest statistics since 2015-08-26 10:04:35 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.

再次查看目標端進程狀態

GGSCI (sywu) 7> stats RSYDB001

Sending STATS request to REPLICAT RSYDB001 ...

Start of Statistics at 2015-08-26 10:05:03.

Replicating from OGG_OWNER.TOGG to OGG_TRG.TOGG:

*** Total statistics since 2015-08-26 10:04:45 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Daily statistics since 2015-08-26 10:04:45 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Hourly statistics since 2015-08-26 10:04:45 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Latest statistics since 2015-08-26 10:04:45 ***
        Total inserts                                      2.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

End of Statistics.

數據稽查

SYS@sydb>select count(*) from ogg_owner.togg;

  COUNT(*)
----------
      1002

Elapsed: 00:00:00.00
SYS@sydb>select count(*) from ogg_trg.togg;

  COUNT(*)
----------
      1002

Elapsed: 00:00:00.05

SYS@sydb>column name format a10
SYS@sydb>select id,name,type from ogg_owner.togg where name='sywu';

        ID NAME       TYPE
---------- ---------- -------------------
      1004 sywu       user

Elapsed: 00:00:00.00
SYS@sydb>select id,name,type from ogg_trg.togg where name='sywu';

        ID NAME       TYPE
---------- ---------- -------------------
      1004 sywu       user

Elapsed: 00:00:00.01

5 總結

　　GoldenGate通過自身的數據加工、處理、發送、應用以及借用RDBMS數據庫系統的優勢保證數據的可同步性和准確性，並在錯誤和故障發生的情況下以RDBMS系統特有的特性(如Oracle 內部同步變更號(SCN)和檢查點)保障了數據的可恢復性和一致性，為同步的數據實時性和准確性提供了准確性和可稽查性。

--The end(2015-08-26)