GoldenGate dml同步進程目標表加欄位處理測試

GoldenGate dml同步進程目標表加欄位處理測試

--實驗：對於僅有dml同步的goldengate進程，如果源端進行ddl加欄位操作，如何處理對應的進程

--源端orcl
--首先檢查進程狀態，running狀態：
[Oracle@localhost ogg]$ ./ggsci
GGSCI (localhost.localdomain) 1> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING                                         
EXTRACT    RUNNING    DPUMP1      00:00:00      00:00:08   
EXTRACT    RUNNING    EXT1        00:00:00      00:00:06   

--停止抽取進程及傳輸進程：
GGSCI (localhost.localdomain) 2> stop *

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

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

GGSCI (localhost.localdomain) 3> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING                                         
EXTRACT    STOPPED    DPUMP1      00:00:00      00:00:11   
EXTRACT    STOPPED    EXT1        00:00:00      00:00:09 

 

--目標端orcltest
--確認複製進程狀態為running，並停掉進程：
GGSCI (localhost.localdomain) 8> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING                                         
REPLICAT    RUNNING    REP1        00:00:00      00:00:06   

GGSCI (localhost.localdomain) 9> stop *

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

--源端orcl
--對同步的表添加一個欄位
[oracle@localhost ogg]$ sqlplus / as sysdba
SQL> alter table ggs.test add new_col varchar2(10);

Table altered.

SQL> exit

--目標端orcltest
--同樣添加欄位：
[oracle@localhost ogg]$ sqlplus / as sysdba
SQL> alter table ggs.test add new_col varchar2(10);

Table altered.

SQL> exit

--源端orcl
--啟動抽取，傳輸進程
[oracle@localhost ogg]$ ./ggsci
GGSCI (localhost.localdomain) 2> start *

Sending START request to MANAGER ...
EXTRACT DPUMP1 starting

Sending START request to MANAGER ...
EXTRACT EXT1 starting

GGSCI (localhost.localdomain) 4> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING                                         
EXTRACT    RUNNING    DPUMP1      00:40:12      00:00:04   
EXTRACT    RUNNING    EXT1        00:02:55      00:00:08 

--目標端orcltest
--啟動複製進程 
[oracle@localhost ogg]$ ./ggsci
GGSCI (localhost.localdomain) 2> start *

Sending START request to MANAGER ...
REPLICAT REP1 starting

GGSCI (localhost.localdomain) 5> info all

Program    Status      Group      Lag at Chkpt  Time Since Chkpt

MANAGER    RUNNING                                         
REPLICAT    RUNNING    REP1        00:00:00      00:00:01   

--源端orcl
--進行資料修改測試
[oracle@localhost ~]$ sqlplus / as sysdba
SQL> select count(*) from ggs.test;

  COUNT(*)
----------
        33

SQL> delete from ggs.test where rownum<5;

4 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into ggs.test (username,user_id,account_status,default_tablespace,temporary_tablespace,created,profile,new_col)
values ('a','7','open','one','temp',sysdate,'default','test');  2 

1 row created.

SQL> commit;

Commit complete.

SQL>

--目標端orcltest
--對應的測試結果：結果正確，說明同步進程是正常的
SQL> select count(1) from ggs.test;

  COUNT(1)
----------
        29
       
SQL> select count(*) from ggs.test;

  COUNT(*)
----------
        30

SQL> exit

實驗結論：對於正常進行的dml複製進程，如果目標表需要加欄位，通過停止進程，源端目標端同時進行ddl操作，
然後重新啟動進程就可以了，複製進程會正常進行。