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

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

--源端orcl

--首先檢查程序狀态，running狀态：

[[email protected] 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

--對同步的表添加一個字段

[[email protected] ogg]$ sqlplus / as sysdba

SQL> alter table ggs.test add new_col varchar2(10);

Table altered.

SQL> exit

--目标端orcltest

--同樣添加字段：

[[email protected] ogg]$ sqlplus / as sysdba

SQL> alter table ggs.test add new_col varchar2(10);

Table altered.

SQL> exit

--源端orcl

--啟動抽取，傳輸程序

[[email protected] 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

--啟動複制程序  

[[email protected] 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

--進行資料修改測試

[[email protected] ~]$ 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操作，

然後重新啟動程序就可以了，複制程序會正常進行。