Oracle 11g Data Pump EXCLUDE Parameter Caveat When the FULL=Y Parameter is Used

During the course of my work, I migrate many databases from one host to another.  Occasionally, DBAs must create backup tables when diagnosing an issue or implementing a change and more frequently than not, these objects never get dropped.  Usually this is no problem except for when you need to move a database from one host to another and you do not want to move these old backup objects.  In my latest migration, I found over 300GB of backup tables which would certainly elongate the time needed to complete the data pump.  After obtaining approval that these tables did not need to be migrated, I began to determine how to exclude these tables from the data pump.  At first, I thought I had it all figured out, data pump everything and exclude only the objects I did not want to bring over.  High level the steps looked like this:

  1. Create a work table in the database which contained the list of excluded tables.
  2. Load the reference data which will be queried by data pump for the exclude list.
  3. Create the data pump parameter file
  4. Execute the data pump
Exclusion table DDL (this simplifies the parameter file):
CREATE TABLE GGATE.EXCLUDE_TABLE (
TABLE_NAME VARCHAR2(30),
OBSOLETE_FLG VARCHAR2(1)
)
TABLESPACE USERS;

INSERT INTO GGATE.EXCLUDE_TABLE VALUES ('TEMP_900008090_08122014', 'Y');
COMMIT;

Data pump parameter file:

USERID='/ as sysdba'
DUMPFILE=EXP_ASM_DIR:METADATA_EXP.DAT LOGFILE=EXP_LOG_DIR:METADATA_EXP.LOG
FULL=Y
CONTENT=METADATA_ONLY
REUSE_DUMPFILES=Y
JOB_NAME=EXP_METADATA_JOB
EXCLUDE=STATISTICS
EXCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM GGATE.EXCLUDE_TABLE WHERE OBSOLETE_FLG='Y')"
CLUSTER=N

You will have all objects except for the few tables which you excluded.  Right?  Well, almost.   You have everything you asked for except, the data pump left out ALL of the following paths:

DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER

So after many hours of research, I found this as a bug documented in Oracle Doc 1491557.1 and effects nearly all versions of 11g.  As a work around, you can do one of the following:

  1. Do another export of metadata without exclusions to get these object paths and import them one by one for each schema
  2. Do a FULL=Y with no exclusions and add the EXCLUDE parameter on the impdp parameter file like this:
    USERID='/ as sysdba'
    DUMPFILE=IMP_ASM_DIR:METADATA_EXP.DAT
    LOGFILE=IMP_LOG_DIR:METADATA_IMP.LOG
    CONTENT=METADATA_ONLY
    JOB_NAME=IMP_METADATA_JOB
    CLUSTER=N
    EXCLUDE=TABLE:"IN (SELECT TABLE_NAME FROM GGATE.EXCLUDE_TABLE)"
    EXCLUDE=STATISTICS
    
  3. Apply patch 14095143 and the EXCLUDE will work properly
  4. Upgrade to 11.2.0.4

Have fun!

To see a where exactly the data pump paths were left out, feel free to see a better representation here:
datapump export compare

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s