Advertisements

Data Pump Enhancements in Oracle Database 11g 

This article provides an overview of the main Data Pump enhancements in Oracle Database 11g Release 1, including:

COMPRESSION

The COMPRESSION parameter allows you to decide what, if anything, you wish to compress in your export. The syntax is shown below.
 

COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

The available options are:
 

• ALL: Both metadata and data are compressed.

• DATA_ONLY: Only data is compressed.

• METADATA_ONLY: Only metadata is compressed. This is the default setting.

• NONE: Nothing is compressed.

Here is an example of the COMPRESSION parameter being used.
 

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  compression=all

The COMPATIBLE initialization parameter should be set to "11.0.0" or higher to use these options, except for the METADATA_ONLY option, which is available with a COMPATIBLE setting of "10.2".

Encryption Parameters

Data pump encryption is an Enterprise Edition feature, so the parameters described below are only relevant for Enterprise Edition installations. In addition, the COMPATIBLE initialisation parameter must be set to "11.0.0" or higher to use these features.

ENCRYPTION and ENCRYPTION_PASSWORD

The use of encryption is controlled by a combination of the ENCRYPTION or ENCRYPTION_PASSWORD parameters. The syntax for the ENCRYPTION parameter is shown below.

ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}

The available options are:
 

• ALL: Both metadata and data are encrypted.

• DATA_ONLY: Only data is encrypted.

• ENCRYPTED_COLUMNS_ONLY: Only encrypted columns are written to the dump file in an encrypted format.

• METADATA_ONLY: Only metadata is encrypted.

• NONE: Nothing is encrypted.

If neither the ENCRYPTION or ENCRYPTION_PASSWORD parameters are set, it is assumed the required level of encryption is NONE. If only the ENCRYPTION_PASSWORD parameter is specified, it is assumed the required level of encryption is ALL. Here is an example of these parameters being used.
 

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  encryption=all encryption_password=password

ENCRYPTION_ALGORITHM

The ENCRYPTION_ALGORITHM parameter specifies the encryption algorithm to be used during the export, with the default being "AES128". The syntax is shown below.
 

ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

The ENCRYPTION_ALGORITHM parameter must be used in conjunction with the ENCRYPTION or ENCRYPTION_PASSWORD parameters, as shown below.
 

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  encryption=all encryption_password=password encryption_algorithm=AES256

ENCRYPTION_MODE

The ENCRYPTION_MODE parameter specifies the type of security used during export and import operations. The syntax is shown below.
 

ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

The allowable values and their default settings are explained below:
 

• DUAL: This mode creates a dump file that can be imported using an Oracle Encryption Wallet, or the the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there is an open wallet.

• PASSWORD: This mode creates a dump file that can only be imported using the ENCRYPTION_PASSWORD specified during the export operation. This is the default setting if the ENCRYPTION_PASSWORD parameter is set and there isn't an open wallet.

• TRANSPARENT: This mode creates an encrypted dump file using and open Oracle Encryption Wallet. If the ENCRYPTION_PASSWORD is specified while using this mode and error is produced. This is the default setting of only the ENCRYPTION parameter is set.

Wallet setup is described here

The ENCRYPTION_MODE requires either the ENCRYPTION or ENCRYPTION_PASSWORD parameter to be specified.
 

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  encryption=all encryption_password=password encryption_mode=password

TRANSPORTABLE

The TRANSPORTABLE parameter is similar to the TRANSPORT_TABLESPACES parameter available previously in that it only exports and imports metadata about a table, relying on you to manually transfer the relevent tablespace datafiles. The export operation lists the tablespaces that must be transfered. The syntax is shown below.
 

TRANSPORTABLE = {ALWAYS | NEVER}

The value ALWAYS turns on the transportable mode, while the default value of NEVER indicates this is a regular export/import.

The following restrictions apply during exports using the TRANSPORTABLE parameter:
 

• This parameter is only applicable during table-level exports.

• The user performing the operation must have the EXP_FULL_DATABASE privilege.

• Tablespaces containing the source objects must be read-only.

• The COMPATIBLE initialization parameter must be set to 11.0.0 or higher.

• The default tablespace of the user performing the export must not be the same as any of the tablespaces being transported.

Some extra restictions apply during import operations:
 

• The NETWORK_LINK parameter must be specified during the import operation. This parameter is set to a valid database link to the source schema.

• The schema performing the import must have both EXP_FULL_DATABASE and IMP_FULL_DATABASE privileges.

• The TRANSPORT_DATAFILES parameter is used to identify the datafiles holding the table data.

Examples of the export and import operations are shown below.
 

expdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  transportable=ALWAYS

impdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
  transportable=ALWAYS network_link=DB11G transport_datafiles='/u01/oradata/DB11G/test01.dbf'

PARTITION_OPTIONS

The PARTITION_OPTIONS parameter determines how partitions will be handled during export and import operations. The syntax is shown below.
 

PARTITION_OPTIONS={none | departition | merge}

The allowable values are:
 

• NONE: The partitions are created exactly as they were on the system the export was taken from.

• DEPARTITION: Each partition and sub-partition is created as a separate table, named using a combination of the table and (sub-)partition name.

• MERGE: Combines all partitions into a single table.

The NONE and MERGE options are not possible if the export was done using the TRANSPORTABLE parameter with a partition or subpartition filter. If there are any grants on objects being departitioned, an error message is generated and the objects are not loaded.
 

expdp test/test directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log tables=test.tab1
  partition_options=merge

REUSE_DUMPFILES

The REUSE_DUMPFILES parameter can be used to prevent errors being issued if the export attempts to write to a dump file that already exists.
 

REUSE_DUMPFILES={Y | N}

When set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.
 

expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  reuse_dumpfiles=y

REMAP_TABLE

This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE method. It can also be used to alter the base table name used during PARTITION_OPTIONS imports. The syntax is shown below.
 

REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename

An example is shown below.
 

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
  remap_table=TEST.TAB1:TAB2

Existing tables are not renamed, only tables created by the import.

DATA_OPTIONS

SKIP_CONSTRAINT_ERRORS

During import operations using the external table acces method, setting the DATA_OPTIONS parameter to SKIP_CONSTRAINT_ERRORS allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without this, the default action would be to roll back the whole operation. The syntax is shown below.
 

DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

An example is shown below.
 

impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
  data_options=SKIP_CONSTRAINT_ERRORS

This parameter has no impact on deferred constraints, which still cause the operation to be rolled back once a violation is detected. If the object being loaded has existing unique indexes or constraints, the APPEND hint will not be used, which may adversely affect performance.

XML_CLOBS

During an export, if XMLTYPE columns are currently stored as CLOBs, they will automatically be exported as uncompressed CLOBs. If on the other hand they are currently stored as any combination of object-relational, binary or CLOB formats, they will be exported in compressed format by default. Setting the DATA_OPTIONS parameter to XML_CLOBS specifies that all XMLTYPE columns should be exported as uncompressed CLOBs, regardless of the default action. The syntax is shown below.
 

DATA_OPTIONS=XML_CLOBS

An example is shown below.
 

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  version=11.1 data_options=XML_CLOBS

Both the export and import must use the same XML schema and the job version must be set to 11.0.0 or higher.

REMAP_DATA

During export and import operations, the REMAP_DATA parameter allows you to associate a remap packaged function that will accept the column value as a parameter and return a modified version of the data. The syntax is shown below.
 

REMAP_DATA=[schema.]tablename.column_name:[schema.]pkg.function

This can be used to mask sensitive data during export and import operations by replacing the original data with random alternatives. The mapping is done on a column-by-column basis, as shown below.
 

expdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
  remap_data:tab1.col1:remap_pkg.remap_col1
  remap_data:tab1.col2:remap_pkg.remap_col2

The remapping function must return the same datatype as the source column and it must not perform a commit or rollback.

Miscellaneous Enhancements

Worker processes that have stopped due to certain errors will now have a one-time automatic restart. If the process stops a second time, it must be restarted manually.