Oracle Notes

Sharing Oracle technology knowledge and experiences.

Oracle 11g GoldenGate and Compressed Tables

Posted by sdefilip on January 6, 2011

Oracle GoldenGate and Compressed Tables

OGG does not support compressed tables or partitions, neither does it handle it well with proper error messages until OGG v10.4. The abends may or may not produce any error message and sometimes produce wrong messages.

From V11.1.1.0.0, Oracle has 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, etc

Example :
ERROR OGG-01028 Record on table QATEST1.TAB1 with rowid AAM4EkAAEAACBguAAA from transaction 5.24.270123 (0×0005.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’s 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 the 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 move nocompress;

This will touch every single block on disk and will uncompress everything thereby causing OGG to work properly and not abend.

If there is even a single partition in a partitioned table that is compressed, it will cause an abend. Partition compression can be verified by getting the full DDL for the table by running the DBMS_METADATA.GET_DDL package. For table partitions that are compressed, 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 to nocompress:

SQL> ALTER TABLE MOVE PARTITION NOCOMPRESS TABLESPACE ;

Eensure that you have enough disk space within your tablespaces before running the ALTER statement.

Support of compressed tables will be in future releases of OGG, however, in current V10.4 and V11.1.1.x, the only option, if a “move nocompress” is not possible, is to comment the compressed table or exclude them from the Extract.

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 DDL is 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

 

 

About these ads

5 Responses to “Oracle 11g GoldenGate and Compressed Tables”

  1. sdefilip said

    Computers

  2. Thanks for this information! It wasn’t easily “find-able” elsewhere. We have assurances from Oracle that they are working on putting this into GoldenGate.

  3. [...] Oracle GoldenGate does not support reading from compressed tables.  During a recent demo call, Oracle assured us that they are working on it, but no timeline that we know of yet. [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: