GG Version: 12.1.2.1.170117
We have added a new table to the EXTRACT and REPLICATs called:
"EHR"."HOSPITAL_PERSON_DEMO"
This table is partitioned on both SOURCE and TARGET.
On the SOURCE, this table is involved in partition exchanges which causes the below error in the REPLICAT:
ERROR OGG-00519 Fatal error executing DDL replication: error [Error code [942], ORA-00942: table or view does not exist SQL alter table "EHR"."HOSPITAL_PERSON_DEMO" exchange partition HOSPITAL_PERSON_DEMO_HOSP_2 with table ehr.hospital_person_demo_2 including indexes without validation /* GOLDENGATE_DDL_REPLICATION */], no error handler present.
The temporary table EHR.HOSPITAL_PERSON_DEMO_2 being used for the partition exchange isn't replicated (and the temporary table name could vary depending on which partitions are being exchanged).
I need to know the following:
1. Are partition exchanges supported across Golden Gate?
2. How do I need to setup the extract and replicats to handle partition exchanges? (The mapping for the extract and replicats are shown below)
3. Should I just setup an error handler to ignore this particular DDL? (Somehow I don't think so but wanted to ask)
EXTRACT Mapping:
EXTRACT ext_all
USERID "redacted", PASSWORD "redacted"
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024)
EXTTRAIL ./dirdat/sd, MEGABYTES 1024
-- Sometimes we mark columns as unused in Oracle. This makes the replication
-- still work.
DBOPTIONS ALLOWUNUSEDCOLUMN
--Report parameters
REPORTCOUNT EVERY 15 MINUTES, RATE
REPORT AT 6:00
REPORTROLLOVER AT 01:00 ON SUNDAY
STATOPTIONS REPORTFETCH
STATOPTIONS RESETREPORTSTATS
-- Warning message for long running transactions
WARNLONGTRANS 1h, CHECKINTERVAL 10m
-- DDL replication (exclude grants, indexes and constraints)
DDL INCLUDE MAPPED &
EXCLUDE OBJTYPE 'INDEX' &
EXCLUDE OBJTYPE 'CONSTRAINT' &
EXCLUDE OBJTYPE 'TRIGGER' &
EXCLUDE OPTYPE GRANT
--DDLOPTIONS REPORT
TABLE ehr.hospital_person_demo;
REPLICAT:
REPLICAT rep_ehr
USERID "redacted", PASSWORD "redacted"
ASSUMETARGETDEFS
WILDCARDRESOLVE IMMEDIATE
DYNAMICRESOLUTION
-- BATCHSQL
-- Set NOHANDLECOLLISIONS after initial load
-- HANDLECOLLISIONS
-- This HANDLECOLLISIONS parameter is used for the automatic handling of Replicat errors.
-- The purpose of this option is to make sure the Replicat is able to keep on running with the understanding that the records may be skipped or some data might not be Replicatd.
-- HANDLECOLLISIONS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
-- Temporary CDT 2018-01-27
-- DBOPTIONS INTEGRATEDPARAMS(PARALLELISM 4, MAX_PARALLELISM 8, COMMIT_SERIALIZATION FULL)
-- Handle Deadlocks CDT 2017-11-15
-- Retry up to 50 times
-- Without this, we risk losing transactions if they don't get retried
REPERROR(60, TRANSABORT, MAXRETRIES 50, DELAYSECS 10)
REPERROR(1403,DISCARD)
-- Only handle mapped objects
DDL INCLUDE MAPPED
--DDLOPTIONS REPORT
-- Discard file
DISCARDROLLOVER AT 01:00 ON SUNDAY
--Report parameters
-- REPORTCOUNT EVERY 15 MINUTES, RATE
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORT AT 6:00
REPORTROLLOVER AT 01:00 ON SUNDAY
MAP ehr.hospital_person_demo, TARGET ehr.hospital_person_demo;