E Oracle Data Pump Settings for Zero Downtime Migration

Setting Advanced Data Pump Parameters

You might want to select specific schemas to migrate, rename tablespaces, or include or exclude specific objects from the as part of a migration.

The following are example parameter settings you can use to specify these selections or changes when you set DATAPUMPSETTINGS_JOBMODE=FULL or DATAPUMPSETTINGS_JOBMODE=SCHEMA job modes.

These parameters are set in the response file at $ZDM_HOME/rhp/zdm/template/zdm_logical_template.rsp.

To exclude specific object types:

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_EXCLUDETYPELIST=COMMENT,DOMAIN_INDEX,MATERIALIZED_VIEW_LOG,RLS_POLICY,TRIGGER

To exclude select SCHEMA objects for DATAPUMPSETTINGS_JOBMODE=FULL mode:

DATAPUMPSETTINGS_METADATAFILTERS-1=name:NAME_EXPR,value:'NOT IN(''SYSMAN'')',objectType:SCHEMA

DATAPUMPSETTINGS_METADATAFILTERS-3=name:NAME_EXPR,value:'NOT IN(''SH'')',objectType:SCHEMA

Note:

The SCHEMA name SYSMAN is surrounded by two single quotes and not a double quote.
To exclude select SCHEMA objects for DATAPUMPSETTINGS_JOBMODE=SCHEMA mode:

EXCLUDEOBJECTS-1=owner:SYSMAN

EXCLUDEOBJECTS-2=owner:SCOTT

By default, Zero Downtime Migration ignores Oracle Maintained Objects.

To include select SCHEMA objects for DATAPUMPSETTINGS_JOBMODE=SCHEMA mode:

INCLUDEOBJECTS-1=owner:SYSMAN

INCLUDEOBJECTS-2=owner:SCOTT

By default, Zero Downtime Migration ignores Oracle Maintained Objects.

To REMAP the tablespaces:

DATAPUMPSETTINGS_METADATAREMAPS-1=type:REMAP_TABLESPACE,oldValue:TS_DATA_X,newValue:DATA

DATAPUMPSETTINGS_METADATAREMAPS-2=type:REMAP_TABLESPACE,oldValue:DBS,newValue:DATA

Data Pump Parameter Default Settings

Zero Downtime Migration automatically sets optimal defaults for Data Pump parameters to achieve better performance and ensure security of data. The following table lists the Data Pump parameters set by Zero Downtime Migration, and the values they are set to.

If there is a Zero Downtime Migration response file parameter available to override the default, it is listed in the Optional Zero Downtime Migration Response File Parameter to Override column. The override parameters are set in the response file at $ZDM_HOME/rhp/zdm/template/zdm_logical_template.rsp.

Table E-1 Data Pump Parameter Defaults

Data Pump Parameter Default Value Optional ZDM Response File Parameter to Override

EXCLUDE

index (ADW-S)

cluster (ADB-D, ADB-S)

indextype (ADW-S)

materialized_view (ADW-S)

materialized_view_log (ADW-S)

materialized_zonemap (ADW-S)

db_link (ADB)

statistics (User managed Target and ADB)

Allows additional EXCLUDE entries to be specified

Note

Specifying invalid object types for EXCLUDE will lead to a Data Pump export error. Ensure that a valid object type is specified for the DATAPUMPSETTINGS_DATAPUMPPARAMETERS_EXCLUDETYPELIST parameter.

To see a list of valid object types, query the following views: DATABASE_EXPORT_OBJECTS for FULL mode, SCHEMA_EXPORT_OBJECTS for SCHEMA mode, and TABLE_EXPORT_OBJECTS for TABLE and TABLESPACE mode. The values listed in the OBJECT_PATH column are the valid object types.

For example, specifying the invalid object type parameter in the response file will lead to export error.

ORA-39038: Object path "<specified invalid>" is not supported for SCHEMA jobs.

PARALLEL

ZDM sets PARALLEL parameter by default as follows

For User managed DB :- (Sum of (2 x (no. of physical CPU) per node ) ) with Max 32 cap.

For ADB :- No. of OCPUs

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_IMPORTPARALLELISMDEGREE

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_EXPORTPARALLELISMDEGREE

CLUSTER

ZDM always sets the Cluster mode as default

DATAPUMPSETTINGS_DATAPUMPPARAMETERS_NOCLUSTER

COMPRESSION

COMPRESSION_ALGORITHM is set to BASIC(for 11.2) and MEDIUM (for 12.1+)

COMPRESSION is set to ALL

N/A

ENCRYPTION

ENCRYPTION is set to ALL

ENCRYPTION_ALGORITHM is set to AES128

ENCRYPTION_MODE is set to PASSWORD

N/A

FILESIZE

FILESIZE is set to 5G

N/A

FLASHBACK_SCN

For OFFLINE_LOGICAL ZDM set FLASHBACK_TIME System time now.

For ONLINE LOGICAL ZDM uses neither FLASHBACK_SCN not FLASHBACK_TIME

N/A

REUSE_DUMPFILES

Always set to YES

N/A

TRANSFORM

Always sets OMIT_ENCRYPTION_CLAUSE:Y for 19c+ targets

Always sets LOB_STORAGE:SECUREFILE

For ADB target, following transform is set by default

SEGMENT_ATTRIBUTES:N

DWCS_CVT_IOTS:Y

CONSTRAINT_USE_DEFAULT_INDEX:Y

Allows additional TRANSFORM to be specified

METRICS

Always set to Yes

N/A

LOGTIME

Always set to ALL

N/A

TRACE

Always set to 1FF0b00

N/A

LOGFILE

Always set to Data Pump job name and created under specified export or import directory object.

Say if Data Pump job is ZDM_2_DP_EXPORT_8417 and directory object used is DATA_PUMP_DIR, then the operation log is created by name ZDM_2_DP_EXPORT_8417.log under DATA_PUMP_DIR.

N/A

Data Pump Error Handling

Zero Downtime Migration ignores the following Data Pump errors by default which are determined by the property oracle.zdm.datapump.ignore_error_list specified in zdm_base/crsdata/zdm_service_host/rhp/conf/zdm.properties.

  • ORA-31684: XXXX already exists

  • ORA-39111: Dependent object type XXXX skipped, base object type

  • ORA-39082: Object type ALTER_PROCEDURE: XXXX created with compilation warnings

Zero Downtime Migration allows these ignorable errors to be configured via property update.

Upon completion of the Oracle Data Pump phase, Zero Downtime Migration parses the Data Pump logs for errors, and if there are more ORA-* errors reported then Zero Downtime Migration marks the phase as failed and reports ORA-20002 error with the number of errors seen.

For example:

ORA-20002: Oracle Data Pump operation completed with 8 error(s)

The above report indicates that there are 8 ORA-* error in addition those specified in the oracle.zdm.datapump.ignore_error_list property.

You must review the Data Pump import log, and if you intend to ignore the reported 8 ORA-* errors, then you can update the oracle.zdm.datapump.ignore_error_list property and the failed job can be resumed to completion.

Updating the oracle.zdm.datapump.ignore_error_list property does not require you to restart the Zero Downtime Migration service, and once updated, the ignore list is applicable for all Zero Downtime Migration jobs that are scheduled from that point.