How to handle replication when expdp generates temp table? (Doc ID 1998385.1)

Last updated on JULY 17, 2017

Applies to:

Oracle GoldenGate - Version 11.2.1.0.1 and later
Information in this document applies to any platform.

Goal

When doing expdp, oracle generates temptable. if the login schema is same as replicated table owner and wildcard is used by extract and replicat, transactions related to above temp tables will be captured. this may cause replicat abend:

e.g.,
2015-04-10 15:33:02 INFO OGG-00506 Oracle GoldenGate Delivery for Oracle, sag_b1_r.prm: Both GETTRUNCATES and DDL replication are enabled.
2015-04-10 15:33:02 INFO OGG-01815 Oracle GoldenGate Delivery for Oracle, sag_b1_r.prm: Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/app/ggs/dirtmp.
2015-04-10 15:33:02 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, sag_b1_r.prm: REPLICAT SAG_B1_R started.
2015-04-10 15:33:02 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, sag_b1_r.prm: Setting current schema for DDL operation to [sagm].
2015-04-10 15:33:02 ERROR OGG-00519 Oracle GoldenGate Delivery for Oracle, sag_b1_r.prm: Fatal error executing DDL replication: error [Error code [8181], ORA-08181: specified number is not a valid system change number SQL CREATE TABLE sagm."ET$09E227220001"
( "TRANSACTION_ID",
"TIME_STAMP",
"MESSAGE_ID",
"PROCESSED",
"MESSAGE_TYPE",
"CONNECTOR_CLASS",
"START_TIMESTAMP",
"END_TIMESTAMP",
"TOTAL_TIME",
"LOGGING_ID",
"INCOMING_MSG",
"OUTGOING_MSG"
) ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY "SAG_DPUMP_DIR1" ACCESS PARAMETERS ( DEBUG = (0 , 47193

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms