业务模型变更过程中忽略OGG造成的问题

1. 说明

以前的OGG如果抽取中开了DDL,可能会导致一些维护的问题,当然OGG目标端开DDL可能问题更多。
但实际上,如果不开DDL,也会有很多问题,这一般就是业务在做模型变更时,不考虑OGG存在的情况,
比如下面的这个例子。(整个测试过程源端Extract和目标端Replicat没有不重启,模拟业务模型变更时,未考虑OGG的情况)

2. OGG的配置

2.1 源端

GGSCI (test3) 12> view param ex_tt

extract ex_tt
setenv ( NLS_LANG = AMERICAN_CHINA.ZHS16GBk )
userid goldengate, password123456

discardfile  /data/ogg_source/dirrpt/tt.dsc,append,megabytes 10

exttrail /data/ogg_source/dirdat/tt

numfiles 8000
dynamicresolution
DBOPTIONS ALLOWUNUSEDCOLUMN
GETUPDATEBEFORES
BR BROFF

---include tables
TABLE TEST_SOURCE.*;

2.2 目标端

GGSCI (test4) 245> view param rp_tt

replicat rp_tt
setenv ( NLS_LANG = AMERICAN_CHINA.ZHS16gbk)
userid goldengate, password 123456
sqlexec "Alter session set constraints=deferred"
reperror default,abend
--REPERROR (-1, IGNORE)
--REPERROR (-1403, IGNORE)

discardfile ./dirrpt/tt.dsc,append, megabytes 1000
assumetargetdefs
allownoopupdates
dynamicresolution
batchsql

map TEST_SOURCE.T1,                     target TEST_TARGET.T1;

3. 过程

3.1 Step 1

  • 源端:
  • SQL> insert into t1 values(1,'a');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
  • 目标端:
  • SQL> select * from t1;
            ID NAME
    ---------- ----------
             1 a
    

    3.2 Step 2

  • 源端:
  • SQL> alter table t1 add name1 varchar2(10);
    
    Table altered.
    
    SQL> insert into t1(id, name) values(2,'b');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
  • 目标端:
  • SQL> select * from t1;
            ID NAME
    ---------- ----------
             1 a
             2 b
    

    3.3 Step 3

  • 源端:
  • SQL> insert into t1 values(3,'c','c');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
  • 目标端:
  • SQL> select * from t1;
    
            ID NAME
    ---------- ----------
             1 a
             2 b
             3 c
    

    从logdump最后一条记录来看,在extract没有重启的情况下,DDL新的字段没有被捕获到

    Logdump 10 >n
    ___________________________________________________________________ 
    Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)  
    UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
    RecLength  :    18  (x0012)   IO Time    : 2017/05/19 10:25:44.000.000   
    IOType     :     5  (x05)     OrigNode   :   255  (xff) 
    TransInd   :     .  (x03)     FormatType :     R  (x52) 
    SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
    AuditRBA   :        296       AuditPos   : 23313424 
    Continued  :     N  (x00)     RecCount   :     1  (x01) 
    
    2017/05/19 10:25:44.000.000 Insert               Len    18 RBA 1311 
    Name: TEST_SOURCE.T1 
    After  Image:                                             Partition 4   G  s   
     0000 0005 0000 0001 3300 0100 0500 0000 0163      | ........3........c  
    Column     0 (x0000), Len     5 (x0005)  
     0000 0001 33                                      | ....3  
    Column     1 (x0001), Len     5 (x0005)  
     0000 0001 63                                      | ....c  
      
    GGS tokens: 
    TokenID x52 'R' ORAROWID         Info x00  Length   20 
     4141 4156 7433 4141 4541 4141 4143 4d41 4143 0001 | AAAVt3AAEAAAACMAAC..  
    TokenID x4c 'L' LOGCSN           Info x00  Length    7 
     3836 3637 3436 37                                 | 8667467  
    TokenID x36 '6' TRANID           Info x00  Length    8 
     342e 332e 3133 3535                               | 4.3.1355
    

    # 4. 处理DDL
    Extract重启,DataPump不重启 #

    GGSCI (test3) 34> stop ex_tt
    
    Sending STOP request to EXTRACT EX_TT ...
    Request processed.
    
    
    GGSCI (test3) 35> start ex_tt
    
    Sending START request to MANAGER ...
    EXTRACT EX_TT starting
    

    重启extract,捕获到新增字段,DataPump不重启可以把含有新增字段的trail内容传到目标端,目标端replicat宕

    Logdump 24 >n
    ___________________________________________________________________ 
    Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)  
    UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)  
    RecLength  :    27  (x001b)   IO Time    : 2017/05/19 10:33:24.000.000   
    IOType     :     5  (x05)     OrigNode   :   255  (xff) 
    TransInd   :     .  (x03)     FormatType :     R  (x52) 
    SyskeyLen  :     0  (x00)     Incomplete :     .  (x00) 
    AuditRBA   :        296       AuditPos   : 23722000 
    Continued  :     N  (x00)     RecCount   :     1  (x01) 
    
    2017/05/19 10:33:24.000.000 Insert               Len    27 RBA 1102 
    Name: TEST_SOURCE.T1 
    After  Image:                                             Partition 4   G  s   
     0000 0005 0000 0001 3400 0100 0500 0000 0164 0002 | ........4........d..  
     0005 0000 0001 64                                 | ......d  
    Column     0 (x0000), Len     5 (x0005)  
     0000 0001 34                                      | ....4  
    Column     1 (x0001), Len     5 (x0005)  
     0000 0001 64                                      | ....d  
    Column     2 (x0002), Len     5 (x0005)  
     0000 0001 64                                      | ....d  
      
    GGS tokens: 
    TokenID x52 'R' ORAROWID         Info x00  Length   20 
     4141 4156 7433 4141 4541 4141 4143 4d41 4144 0001 | AAAVt3AAEAAAACMAAD..  
    TokenID x4c 'L' LOGCSN           Info x00  Length    7 
     3836 3638 3133 36                                 | 8668136  
    TokenID x36 '6' TRANID           Info x00  Length   11 
     3130 2e32 372e 3130 3038 37                       | 10.27.10087  
    

    目标端:

    SQL> alter table t1 add name1 varchar2(10);
    
    Table altered.
    
    GGSCI (test3) 17> start rp_tt
    
    Sending START request to MANAGER ...
    REPLICAT RP_TT starting
    

    目标端数据

    SQL> select * from t1;
    
            ID NAME       NAME1
    ---------- ---------- ----------
             1 a
             2 b
             3 c
             4 d          d
    

    源端数据

    SQL> select * from t1;
    
            ID NAME       NAME1
    ---------- ---------- ----------
             1 a
             2 b
             3 c          c
             4 d          d
    

    5. 总结

    OGG没有开DDL的情况下,业务模型变更不管OGG的情况下,是会造成源、目标端不一致的情况,主要原因是在不重启源端的抽取进程情况下,
    OGG捕获不到新增的字段,所以就造成了问题。

    关于紫砂壶

    感悟技术人生
    此条目发表在GoldenGate分类目录,贴了标签。将固定链接加入收藏夹。