OGG 拆分EXTRACT进程

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_LANG="AMERICAN_AMERICA.ZHS16GBK") --此处数据库字符集设为一致
--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_LANG="AMERICAN_AMERICA.ZHS16GBK") 
--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