GoldenGate and Oracle 11.2
Some weeks ago a customer upgraded its databases from 10.2 to 11.2.0.3.
Some time after this change there were GoldenGate errors:
2013-02-03 10:11:55 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, r_xxxx.prm: Fatal error executing DDL replication: error [Error code [922], ORA-00922: missing or invalid option, SQL create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_U], no error handler present.
2013-02-03 10:14:55 INFO OGG-00482 Oracle GoldenGate Delivery for Oracle, r_xxxx.prm: DDL found, operation [create table “XXXXXX”.DBMS_TABCOMP_TEMP_CMP organization heap tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab (size 209)].
2013-02-03 10:14:55 INFO OGG-00489 Oracle GoldenGate Delivery for Oracle, r_xxxx.prm: DDL is of mapped scope, after mapping new operation [create table “XXXXXX”.”DBMS_TABCOMP_TEMP_CMP” organization heap tablespace “TBS_MAGIC” compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from “XXXXXX”.DBMS_TABCOMP_TEMP_UNCMP mytab (size 211)].
After some investigation we found out this is due to the new “feature” Compression Advisor, from Oracle 11.2 (How Does Compression Advisor Work? [1284972.1]).
As it does “create table as select” (CTAS) which GoldenGate does not yet support well, the replicat abended.
There is already one Metalink note on this issue: DDL / Remap Schema : ORA-942 Connected With DBMS_TABCOMP_TEMP_CMP & DBMS_TABCOMP_TEMP_UNCMP [ID 1505178.1].
So we added to the extract parameter files:
TABLEEXCLUDE xxxxx.DBMS_TABCOMP_TEMP*