11gR2 Compression Advisor causing DDL errors in GoldenGate

After upgrading source database to 11gR2, GoldenGate extract causing DDL errors which are generated in below mentioned form:

2013-02-17 11:55:15  ERROR   OGG-00516  Oracle GoldenGate Delivery for Oracle, r01_salt.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 "XXXXX" compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ * from "XXXXXX".DBMS_TABCOMP_TEMP], due to explicit ABEND error handling and filter [include all (default)].

Tables referenced which cannot be created are:

DBMS_TABCOMP_TEMP_CMP

DBMS_TABCOMP_TEMP_UNCMP

Cause:

Compression advisor is a new advisor added in 11gR2. It is shipped with Segment Advisor, and will be executed whenever Segment Advisor task run. It can also be invoked manually.

Segments that are at least 10MB in size and with at least 3 indexes will be evaluated for compressibility (but not all the tables which meet the requirements will be evaluated, an internal algorithm will decide which segment should be evaluated). 

dbms_compression package will be invoked to estimate how much space can be saved. It will create temporary internal table either DBMS_TABCOMP_TEMP_UNCMP or DBMS_TABCOMP_TEMP_CMP) in the tablespace which hosts the segment. These two tables will be dropped immediately after the estimation.

If DDL replication is configured, Extract will capture DDL for these temporary tables which will be as follows:

create table "XXXXXX"."DBMS_TABCOMP_TEMP_UNCMP" tablespace "XXXXX" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("XXXXXX"."BATCH") */ * from "XXXXX"."BATCH"

Replicat is having following mapping parameter: 

MAP source_schema.*, TARGET dest_schema.*;

This ability to remap schemas in a Create Table As Select (CTAS) SQL statement is raised as an enhancement request in Bug:9106992. 

Note: in the case above , nologging is specified so it would not be relevant or appropriate to capture changes in this case.

Workaround: 

Exclude tables : 

DDL ..., EXCLUDE OBJNAME <schema>.DBMS_TABCOMP_TEMP_UNCMP, &

EXCLUDE OBJNAME <schema>.DBMS_TABCOMP_TEMP_CMP

Reference:

DDL / Remap Schema : ORA-942 Connected With DBMS_TABCOMP_TEMP_CMP & DBMS_TABCOMP_TEMP_UNCMP [ID 1505178.1]

Configuring GondenGate on RAC

Coming soon.....

GoldenGate ETROLLOVER Command

This article describes the use of ETROLLOVER command for extract.
ETROLLOVER causes the extract to increment to the next trail file in the trail sequence when it restarts. This command is commonly used when we have GoldenGate running on RAC databases where we encounter out of order SCN error.

Usage:  ALTER EXTRACT <group name>, ETROLLOVER

By issuing ETROLLOVER , Extract Process doesn’t write EOF of the trail file to the current file. The downstream Pump or Replicat process reading from these current file doesn’t read EOF of the trail as it has not been written, so it doesn’t automatically switch to new trail file. We have to manually modify downstream process to switch to next trail file.
In GoldenGate version

In version 10 and later, Goldengate trail contains auto-recovery feature.  ETROLLOVER command will disable this feature, and make the downstream replicat (or pump) hang at the end of existing trail without showing errors.  It will need to be stopped and its RBA positioned to the beginning of the next trail.
Etrollover is used to resolve following issues

OGG-01496 Failed to open target trail file.
Existing extract output trail files were unintentionally purged has caused the extract process to encounter this error.

OGG-01031 When Upgrading the Target Side
Upgrading target side GOldenGate Instance without stopping Data Pumps on source will cause the data pumps to abend with OGG-01031 error. Issue Etrollover on extract pumps. Verify that Replicat had processes previous trail file and there is no log, then modify replicat to start from new trail file sequence number and RBA.

ERROR 180 encountered commit SCN <scn> that is not greater than the highest SCN already processed
This error occurs in Oracle RAC environments where Extract has coordinated thread. This thread gathers the operations that are generated on each node of the RAC nodes inot SCN order. This error occurs shortly after a transaction is written to the idle node but does not yet appear in the redo log when the current transaction is processed. Before showing this error Extract waits number of milliseconds specified in its parameter file THREADOPTIONS MAXCOMMITPROPAGATIONDELAY (default 3 seconds). Waiting ensures that there are no new transactions on the idle node before writing the current transactions to the trail. This transaction will have a higher SCN than the previous transaction.

There can be number of possible number of reasons for this error.

• One of the threads is slower than the other.
• The redo logs are not flushed on time due to latency on Log writes.
• Network issues between Extract and one of the RAC nodes, if Extract is running on a system separate from RAC nodes.
• Long log write times due to a standby configuration, if any.
• Log file I/Os are taking unusually long times to complete.
• Time imperfections between the cluster nodes. All nodes in the RAC cluster must have synchronized system clocks. If Extract is running on a system other than any of the RAC nodes, that system clock must be in sync, too, because we compare the local system's time to the commit timestamp to make critical decisions. For information about synchronizing system clocks, consult www.ntp.org or your systems administrator
• The DB timezone setting is different than the OS timezone setting

Please refer Note ID ID 957112.1 for more details.