goldengate（6）

goldengate（6）--对于表的字段具有unused columns的数据同步

1.示例表test.xy109

create table test.xy109 (billnum number,name varchar2(10),persion varchar2(20),till varchar2(5));

alter table test.xy109 add constraint pk_billnum primary key (billnum);

 

该表的字段till 设定为unused column
alter table test.xy109 set unused column till;

 

2.源端插入一条数据

insert into test.xy109 values(1000,'abc','quit');
commit;

 

查看：

SQL>
SQL> select * from test.xy109;
 
   BILLNUM NAME       PERSION
---------- ---------- --------------------
      1000 abc        quit

 

注意的是，因为字段till被设定为unused columns，所有该列并没有出现

.

源端extract抽取进程报错，状态为abended，因为该表xy109上有列till为unused，故而报错.

EXTRACT    S_EX_HR   Last Started 2013-03-12 02:01   Status ABENDED
Checkpoint Lag       00:05:46 (updated 00:00:03 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-03-12 01:55:31  Seqno 146, RBA 36920832

goldengate如何处理这种情况？

 

报错日志：

2013-03-12 02:06:22  ERROR   OGG-00751  Failed to validate table TEST.XY109. Likely due to existence of unused columns. It will cause data integrity
issue if you are not using sourcedefs in downstream Replicat or the target table doesn't have the same unused columns due to ASSUMETARGETDEFS or DDL
replication. Please use 'DBOPTIONS ALLOWUNUSEDCOLUMN' parameter to override this.

 

 

处理的方法如下：

在源端extract抽取进程加入DBOPTIONS参数带上ALLOWUNUSEDCOLUMN选项

 

源端操作：

 

GGSCI (dbhouse) 9> edit params s_ex_hr

EXTRACT s_ex_hr
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs, PASSWORD ggs
TRANLOGOPTIONS ASMUSER sys@asm, ASMPASSWORD grid
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/hr/tt
TABLE test.*;

 

注意的是：

DBOPTIONS must precede the
TARGETDB or SOURCEDB parameter statement and/or the USERID statement .

 

 

GGSCI (dbhouse) 11> start extract s_ex_hr

Sending START request to MANAGER ...
EXTRACT S_EX_HR starting

GGSCI (dbhouse) 12> info er *

EXTRACT    PUMP_HR   Last Started 2013-03-11 14:41   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint  File /u01/app/oracle/ggs/dirdat/trail/hr/tt000011
                     2013-03-12 02:11:59.996372  RBA 1045

EXTRACT    S_EX_HR   Last Started 2013-03-12 02:11   Status RUNNING
Checkpoint Lag       00:16:29 (updated 00:00:08 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-03-12 01:55:31  Seqno 146, RBA 36920832

 

源端extract 抽取进程正常了.

 

3.目标端查看数据

 

SQL> select * from test.xy109;
 
   BILLNUM NAME       PERSION
---------- ---------- --------------------
      1000 abc        quit

 

数据成功复制。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21266384/viewspace-756192/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21266384/viewspace-756192/