goldengate 问题

2012-12-05 10:59:59  ERROR   OGG-01028  Record on table  用户名.表名with rowid AAASk5ABzAAL7/YAAA from transaction 55.17.8178784 (0x0037.011.007ccc60) is compressed. Compression is not
 supported.

 

通过检查 此表不是压缩表也不是分区表，说明是goldengate的bug  ，需要给系统打补丁

 

In this Document

 Goal
 Fix
 References

--------------------------------------------------------------------------------

Applies to:
Oracle GoldenGate - Version 11.1.1.0.0 to 11.2.1.0.2 [Release 11.1.1 to 11.2]
Information in this document applies to any platform.

Goal
Oracle GoldenGate and compressed tables

Fix
Compressed tables are supported via Integrated Extract from OGG v11.2.x(It is still not supported in OGG v11.2.x classic extract).

OGG does not support compressed tables or partitions, neither does it handle it well with proper error messages until OGG v10.4.x. We have several cases reported by customers when it deals with compressed tables. Sometimes it abends without any error message and sometimes with wrong messages. (ex: BugDB 10267584)

From V11.1.1.0.0, we enhanced the error handling part in BugDB 9425542, which gives meaningful error message on the compressed record before Extract abend. It will list out the table name, rowid and etc

Example :
ERROR OGG-01028 Record on table QATEST1.TAB1 with rowid AAM4EkAAEAACBguAAA from transaction 5.24.270123 (0x0005.018.00041f2b) is compressed. Compression is not supported.

However, due to bug 10063108, sometimes the error message on compressed tables are not entirely correct. This problem has been fixed in 11.1.1.0.3 and above

A table created as compressed will cause all of the DML to go into compressed blocks on disk. If the user does an "alter table nocompress", every DML that goes into the table AFTER that point in time will be uncompressed. The query for compression will return "nocompress" now, but that simple "alter" does not change the already existing compressed blocks on disk that were created before the "alter". So to capture the records from a table which was compressed we need to do the following

SQL> alter table <tablename> move nocompress;

This will touch every single block on disk and will uncompress everything and so OGG should be all good.

Sometimes customer has partitioned tables, even though they have un-compressed partitions, as long as there is any partition that is compressed, we cannot support that. This could be verified by getting the full DDL for the table by running DBMS_METADATA.GET_DDL package. KM 965696.1.In case table partitions are compressed, Please run the below query and get the partition names & tablespace names.

SQL> SELECT partition_name, subpartition_name, tablespace_name, high_value FROM user_tab_subpartitions WHERE table_name = 'table_name';

Alter statement for partition to move nocompress.

SQL> ALTER TABLE <Table_name> MOVE PARTITION <partition_name> NOCOMPRESS TABLESPACE <tablespace>;

Please ensure that you are having enough disk space within tablespaces before running the ALTER statement.

We will be adding full support of compressed tables in future release, however, in current V10.4 and V11.1.1.x, the only option is to comment the compressed table or exclude them in Extract(if you cannot do a "move nocompress").

 
Listing some scenarios in which we have seen similar errors for compression --------------------------------------------------------------------------------------

1) The extract abends with the following error

GGS ERROR ZZ-0QY Failed to validate table <SCHEMA>.<TABLE NAME>. The table is compressed and extract will not be able to extract data from Oracle logs.

This bug happens when we have DDL enabled, the checking was not done correctly when table resides on Bigfile tablespace.

Issue is fixed in V10.4.0.93 and V11.1.1.0.4

Reference bugdb -10063075

2) When running with OGG version earlier than V11.1.1.0.10, Extract might abend with below error message, without telling the name of the table or the Objectid of the compressed table

Source Context :
SourceModule : [er.redo.ora.sr]
SourceID : [/mnt/ecloud/workspace/Build_OpenSys_r11.1.1.0.9_004
_[40043]/perforce/src/app/er/redo/oracle/redoorasr.c]
SourceFunction : [get_subrec(int32_t, mempool_t *, unsigned char *, u
nsigned short, subrec_info_t *, redo_thread_t *, BOOL, log_context_t *)]
SourceLine : [5434]

2010-11-10 11:50:26 ERROR OGG-01028 compressed tables are not supported.

This is because there is a temp table created as compressed, then followed directly by a direct load operation.

Try to exclude the table DBMS_TABCOMP_TEMP_UNCMP and DBMS_TABCOMP_TEMP_CMP, which was created by Compression advisory.

From OGG v11.1.1.0.10 onwards when the extract abends because of direct load inserts on tables with compressed partitions, the extract gives the table name in the error.

Reference bugdb - 10279456

However, if the table has been dropped immediately after creation, the Extract will only give out the object ID of the "compressed" table.  To find out exactly what was the compressed table name, you could query GGS_DDL_HIST table if DDL replication is enabled.  A sample query

select OBJECTID, OBJECTNAME, METADATA_TEXT FROM GGS_DDL_HIST WHERE OBJECTID = xxxxx

Once you find the table name, you have to exclude this table from both DDL and DML in Extract parameter file by using DDL EXCLUDE and TABLEEXCLUDE. Example

DDL EXCLUDE OBJNAME "TEST.DBMS_TABCOMP_TEMP_CMP"

TABLEEXCLUDE TEST.DBMS_TABCOMP_TEMP_CMP

Reference bugdb -- 10377296

3) There is another false positive warning in version V11.1.1.x. When we process regular table update followed immediately by a clustering table update. A bug in the code set the wrong flag for compressed table warning. This bug is being fixed in v11_1_1_0_11 and above.

Reference bugdb - 10308511

4) The extract getting abended with the following error when processing a cluster table.

ERROR OGG-01028 Record on table SCHEMA.TABLENAME with rowid AACprtAAIAAPPOYAB4 from transaction 50.5.8703098 (0x0032.005.0084cc7a) is compressed. Compression is not supported

The issue was resolved when doing a  - alter grant any cluster to gg_extract user

The issue is fixed in v11.1.1.0.29 (bug-12322319)

5) Extract generates large amount of .cm files

The extract might generate large amount of .cm files in the cachedirectory due to mis-parsing of compressed records which will be ignored anyway later. Issue had been fixed in bug 13440090 so that extract skips such compressed data rather than generating large amount of files in cachedirectory (default is /dirtmp).

If you encounter this issue, Please upgrade to a OGG build which have this fix bug 13440090 included.

6) OGG v11.1.1.1.2 abends even after excluding the table

When using v11.1.1.1.2 and if extract abends with similar error stated below even after excluding the table,
OGG-01028 Object with object number 121393 is compressed. Table compression is not supported

The following *may* help,

1) use DDLOPTIONS NOCROSSRENAME
2) use DDL INCLUDE MAPPED. If not exclude the offending table using EXCLUDE OBJNAME
3) Exclude the dml's using the following syntax
TABLEXCLUDE <schema>.<tablename> NORENAME

Reference bug- 13521638