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操作,
然後重新啟動進程就可以了,複製進程會正常進行。