Some weeks ago a customer upgraded its databases from 10.2 to 11.2.0.3.<\/p>\n
Some time after this change there were GoldenGate errors:<\/p>\n
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.<\/span> 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6,2],"tags":[],"class_list":{"0":"post-16","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-goldengate","7":"category-oracle","8":"category-oracle-11g","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/16","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":1,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":81,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/posts\/16\/revisions\/81"}],"wp:attachment":[{"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/anjo.pt\/keyword-oracle\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n2013-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)].<\/span>
\n2013-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)].<\/span>
\n
<\/span>
\nAfter some investigation we found out this is due to the new “feature” Compression Advisor, from Oracle 11.2 (<\/span>How Does Compression Advisor Work? [1284972.1]<\/a>).<\/span>
\n
<\/span>
\nAs it does “create table as select” (CTAS) which GoldenGate does not yet support well, the replicat abended.<\/span>
\n
<\/span>
\nThere 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].<\/span><\/span>
\n
<\/span><\/span>
\nSo we added to the extract parameter files:<\/span><\/span>
\nTABLEEXCLUDE xxxxx.DBMS_TABCOMP_TEMP*<\/span><\/span>
\n
<\/span><\/span>
\n<\/p>\n