1. OGG 拆分EXTRACT程序

1.1. OGG 拆分EXTRACT程序

程序拆分注意事項：

—各程序間沒有同步機制，應盡量確定同一交易涉及表在一個程序，以業務或Schema進行區分。

—單個extract程序可處理日志一般為30-50G/小時，單個replicat程序一般隻能處理1G隊列/小時，可采用一個extract對多個replicat的模式。

—由于extract在catch up（追趕）模式需要讀取歸檔日志，速度慢且耗費資源高，建議extract一旦出現較大延遲則立即進行拆分。

—Replicat拆分可能臨時造成各程序間不同步，但是多個Replicat性能會得到很大提高，可以保證資料複制始終是實時的。

—當源端出現災難後，由于Extract可以保證源端抽取時資料的一緻性，而目标端多Replicat讀取的是同一個隊列，當它們應用隊列資料完畢後是可以達到資料一緻的。

1.1.1. 停止extract程序

GGSCI (cndba) 135> stop ext1

Sending STOP request to EXTRACT EXT1 ...
Request processed.

1.1.2. 檢查extact程序的checkpoint資訊

GGSCI (cndba) 5> info ext1,showch
記錄下其Write Checkpoint資訊中的sequence和RBA值，如下：
EXTRACT    EXT1      Last Started 2018-06-12 14:08   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:06:41 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2018-06-12 17:26:05  Thread 1, Seqno 54, RBA 14155264
                     SCN 0.1379893 (1379893)


Current Checkpoint Detail:

Read Checkpoint #1

  Oracle Threaded Redo Log

  Startup Checkpoint (starting position in the data source):
    Thread #: 1
    Sequence #: 54
    RBA: 7347728
    Timestamp: 2018-06-12 13:38:32.000000
    SCN: 0.1372723 (1372723)
    Redo File: /u01/app/oracle/oradata/cndba/redo03.log

  Recovery Checkpoint (position of oldest unprocessed transaction in the data source):
    Thread #: 1
    Sequence #: 54
    RBA: 14154768
    Timestamp: 2018-06-12 17:26:05.000000
    SCN: 0.1379893 (1379893)
    Redo File: /u01/app/oracle/oradata/cndba/redo03.log

  Current Checkpoint (position of last record read in the data source):
    Thread #: 1
    Sequence #: 54
    RBA: 14155264
    Timestamp: 2018-06-12 17:26:05.000000
    SCN: 0.1379893 (1379893)
    Redo File: /u01/app/oracle/oradata/cndba/redo03.log

Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 31
    RBA: 1350
    Timestamp: 2018-06-12 17:26:31.922124
    Extract Trail: ./dirdat/et

1.1.3. 檢查pump程序的checkpoint資訊

GGSCI (cndba) 9> info pump1,showch
若pump程序的Current Checkpoint資訊中的sequence和RBA值與step 2得到的值相等，則說明goldengate抓取的資訊都已被傳送到了target伺服器上，可繼續執行下一步驟。否則，等待并重複運作該指令。
Current Checkpoint (position of last record read in the data source):
    Sequence #: 31
    RBA: 1350
    Timestamp: 2018-06-12 14:16:43.000000
    Extract Trail: ./dirdat/et
記錄其Write Checkpoint資訊中的sequence和RBA值，如下：
Write Checkpoint #1

  GGS Log Trail

  Current Checkpoint (current write position):
    Sequence #: 32
    RBA: 1486
    Timestamp: 2018-06-12 17:37:44.140003
    Extract Trail: ./dirdat/et

1.1.4. 檢查replicat程序的checkpoint資訊

Current Checkpoint (position of last record read in the data source):
    Sequence #: 32
    RBA: 1486
    Timestamp: 2018-06-12 14:08:24.507621
    Extract Trail: ./dirdat/et
若replicat程序的Current Checkpoint資訊中的sequence和RBA值與step 3中得到的Write Checkpoint資訊相等，則說明goldengate抓取的資訊都已被apply到target 資料庫，可繼續執行下一步驟。否則，等待并重複運作該指令。

1.1.5. 停止pump程序和replicat程序

--源端操作
GGSCI (cndba) 10> stop pump1

Sending STOP request to EXTRACT PUMP1 ...
Request processed.
--目标端操作
GGSCI (cndba) 30> stop rep1

Sending STOP request to REPLICAT REP1 ...
Request processed.

1.1.6. 修改原extract,pump,replicat程序配置檔案

這兩個新的參數檔案與原有的參數檔案的主要差別是将其中的複制表按負載均分為了兩部分，分别放在單獨的參數檔案中。将要拆分的表，從參數檔案删除
--修改extract程序配置檔案
GGSCI (cndba) 15> edit params ext1
GGSCI (cndba) 18> view params ext1
EXTRACT ext1
SETENV (NLS_) --此處資料庫字元集設為一緻
--SETENV (ORACLE_SID = "cndba")
SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
--DDL INCLUDE ALL
--HANDLECOLLISIONS
tableexclude test.test2
TABLE test.*;
--修改pump程序配置檔案
GGSCI (cndba) 147> edit params pump1
GGSCI (cndba) 147> view params pump1

EXTRACT pump1
RMTHOST 192.168.1.86, MGRPORT 7809
RMTTRAIL ./dirdat/et
PASSTHRU
DYNAMICRESOLUTION
tableexclude test.test2;
TABLE test.*;
--修改replicat程序配置檔案
GGSCI (cndba) 105> edit params rep1
GGSCI (cndba) 106> view params rep1

REPLICAT rep1
setenv (ORACLE_SID=cndba)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
--HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
mapexclude test.test2;
MAP test.*, TARGET test.*;

1.1.7. 增加新的extract，pump和replicat 程序

配置檔案裡添加要拆出來的表
--添加extract程序
GGSCI (cndba) 150> add extract ext2, tranlog, begin now, threads 1
EXTRACT added.
GGSCI (cndba) 151> add exttrail ./dirdat/ex, extract ext2
EXTTRAIL added.
GGSCI (cndba) 152> edit params ext2
GGSCI (cndba) 156> view params ext2
EXTRACT ext2
--SETENV (NLS_) 
--SETENV (ORACLE_SID = "cndba")
--SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/ex
DYNAMICRESOLUTION
--DDL INCLUDE ALL
TABLE test.test2;
--添加pump 程序
GGSCI (cndba) 153> add extract pump2,exttrailsource ./dirdat/ex,begin now 
EXTRACT added.
GGSCI (cndba) 154> add rmttrail ./dirdat/ex,extract pump2
RMTTRAIL added.
GGSCI (cndba) 155> edit params pump2
GGSCI (cndba) 157> view params pump2
EXTRACT pump2
RMTHOST 192.168.1.86, MGRPORT 7809
RMTTRAIL ./dirdat/ex
PASSTHRU
DYNAMICRESOLUTION
TABLE test.test2;
--添加replicat程序
GGSCI (cndba) 107> add replicat rep2, exttrail ./dirdat/ex, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (cndba) 108> edit params rep2
GGSCI (cndba) 109> view params rep2
REPLICAT rep2
--setenv (ORACLE_SID=cndba)
--SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1")
USERID ogg,PASSWORD ogg
ASSUMETARGETDEFS
--HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/repr2.dsc,append,megabytes 100
--HANDLECOLLISIONS
MAP test.test2, TARGET test.test2;

1.1.8. 修改新增extract程序的檢查點（current和recovery檢查點都需要修改和第2步保持一緻）

根據step 2中得到的Current Checkpoint (position of last record read in the data source)資訊和和Recovery Checkpoint (position of oldest unprocessed transaction in the data source)修改建立extract程序的checkpoint資訊，讓其從舊extract程序停止的位置開始抓取新的資訊
GGSCI (cndba) 166> alter ext2 extseqno 54,extrba 14155264,thread 1
EXTRACT altered.
GGSCI (cndba) 167> alter ext2 ioextseqno 54, ioextrba 14154768 ,thread 1

2018-06-12 18:39:07  INFO    OGG-00988  WARNING: Unsupported operation. This might cause transactional inconsistency. Modifying input checkpoint #1, Oracle thread #1 of EXTRACT: ioseq = 54 iorba = 14154768.
Are you sure you want to continue? y
EXTRACT altered

1.1.9. 啟動所有程序

GGSCI (cndba) 168> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXT1        00:00:00      01:15:56    
EXTRACT     STOPPED     EXT2        00:19:11      00:03:21    
EXTRACT     STOPPED     PUMP1       00:00:00      00:49:07    
EXTRACT     STOPPED     PUMP2       00:00:00      00:22:02    


GGSCI (cndba) 169> start ext1

Sending START request to MANAGER ...
EXTRACT EXT1 starting


GGSCI (cndba) 170> start ext2

Sending START request to MANAGER ...
EXTRACT EXT2 starting


GGSCI (cndba) 171> start pump1

Sending START request to MANAGER ...
EXTRACT PUMP1 starting


GGSCI (cndba) 172> start pump2

Sending START request to MANAGER ...
EXTRACT PUMP2 starting
GGSCI (cndba) 192> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT1        00:00:00      00:00:10    
EXTRACT     RUNNING     EXT2        00:00:00      00:00:07    
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:08    
EXTRACT     RUNNING     PUMP2       00:00:00      00:00:44

GGSCI (cndba) 111> start rep1

Sending START request to MANAGER ...
REPLICAT REP1 starting


GGSCI (cndba) 112> start rep2

Sending START request to MANAGER ...
REPLICAT REP2 starting


GGSCI (cndba) 113> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REP1        01:15:45      00:00:03    
REPLICAT    RUNNING     REP2        00:00:00      00:00:01

1.1.10. 測試

源端插入資料
SQL> select * from test2;

 ID NAME
---------- --------
  1 zhangsan
  3 test
  4 test
  5 test

SQL> select * from test; 

 ID NAME
---------- --------
  1 test

SQL> insert into test values(2,'test');

1 row created.
SQL> insert into test2 values (6,'test');

1 row created.
SQL> commit;

Commit complete.
--目标端檢視資料
SQL> select * from test;

 ID NAME
---------- --------
  2 test
  1 test

SQL> select * from test2;

 ID NAME
---------- --------
     6 test
  5 test
  1 zhangsan
  3 test
  4 test

OGG 拆分EXTRACT程序1. OGG 拆分EXTRACT程序

1. OGG 拆分EXTRACT程序

1.1. OGG 拆分EXTRACT程序

1.1.1. 停止extract程序

1.1.2. 檢查extact程序的checkpoint資訊

1.1.3. 檢查pump程序的checkpoint資訊

1.1.4. 檢查replicat程序的checkpoint資訊

1.1.5. 停止pump程序和replicat程序

1.1.6. 修改原extract,pump,replicat程序配置檔案

1.1.7. 增加新的extract，pump和replicat 程序

1.1.8. 修改新增extract程序的檢查點（current和recovery檢查點都需要修改和第2步保持一緻）

1.1.9. 啟動所有程序

1.1.10. 測試

繼續閱讀

GOLDENGATE安裝配置與複制流搭建_DG端抽取支援DDL版1、配置場景3、源端資料庫與表配置修改4、配置goldengate支援DDL4、OGG源端配置5、目标端OGG配置6、資料初始化7、啟動目标端的replicat程序8、驗證OGG能正常同步資料

goldengate dml同步程序目标表加字段處理測試

Oracle 的ADG備庫可以作為OGG的源頭資料庫

OGG基礎知識總結OGG基礎知識整理

通過Goldengate同步源端和目标端表主鍵不一緻的資料

OGG-01028 Error (22, Invalid argument) positioning into extract file in EOF check

designs\project\database-OGG

Bounded Recovery

ORA-1653: unable to extend table GGS.GGS_DDL_HIST

OGG DDL 操作詳解

OGG常見問題處理

#goldengatebridge

在Windows系統設定goldengate服務随系統啟動

收錄的一篇很詳細很全的OGG配置文檔1 GoldenGate簡要說明2 單機à單機複制配置3 RACà單機複制配置4 RACà單機下的HA配置5 常見錯誤及解決方法

GoldenGate Lag For Huge Insert