ogg挖掘压缩表问题

故障现象

ogg同步稳定运行一段时间后 ，发现晚上 10 点进程经常会 abended ，比较频繁，报错如下：

2017-04-26 22:00:27  ERROR   OGG-01028  Oracle GoldenGate Capture for Oracle, ext_sm.prm:  Object with object number 95523 is compressed. Table compression is not supported.

2017-04-26 22:00:28  INFO    OGG-00991  Oracle GoldenGate Capture for Oracle, ext_sm.prm:  EXTRACT EXT_SM stopped normally.

分析解决

在前期配置ogg同步时，就已经检查过同步的表，可以确认同步表中没有压缩表

根据报错提示，查询对象号为 95523的对象信息：

select * from odc.GGS_DDL_HIST where OBJECTID=95523;        --odc 表示库中创建的 ogg管理 用户

查询结果为 DBMS_TABCOMP_TEMP_CMP，不是我们同步的表

进一步查资料后获取信息如下：

在  Oracle 11g R2  中，自动维护窗口会生成临时表  DBMS_TABCOMP_TEMP_UNCMP  和   DBMS_TABCOMBP_TEMP_CMP。

参考文档：

Streams Capture Aborting With ORA-26767 Due To Temp Tables Created By DBMS_COMPRESSION ( 文档  ID 1082323.1)

DBMS_COMPRESSION is a new utility introduce in 11GR2 which is used for Compression Advisory.

DBMS_COMPRESSION creates two temporary tables (namely,

DBMS_TABCOMP_TEMP_UNCMP &

DBMS_TABCOMP_TEMP_CMP)

while doing the analyze of the table in the table owner schema. These tables are compared to see what compression level can be achieved.

By default ddl for above mentioned tables has nologging option enabled.

Now if CAPTURE has schema level rule defined, then DDL/DML for these tables will be captured as well.Since ddl for above mentioned table has nologging option enabled., enough redo information for the capture process was not available, and hence CAPTURE failed with ORA-26767 .

In 11.2.0.4,  we see tables with names that include "CMP", ie CMP4$222224.

要避免出现该错误，可以直接通过  tableexclude  参数在出库进程中排除这两张表，如下：

tableexclude *.DBMS_TABCOMP_TEMP*;