ORACLE 11G EXPDP AND IMPDP FEATURE

COMPRESSION parameter in expdp

One of the big issues with Data Pump was that the dumpfile couldn't be compressed while getting created. In Oracle Database 11g, Data Pump can compress the dumpfiles while creating them by using parameter COMPRESSION in the expdp command line. The parameter has three options:

METDATA_ONLY - only the metadata is compressed, This is the default setting
DATA_ONLY - only the data is compressed; the metadata is left alone.
ALL - both the metadata and data are compressed.
NONE - this is the default; no compression is performed.

Datapump in 11g has a good feature to reduce size of exports and resources used on machines and tapes by compressing the dumps as and when the export happens.

In version 10g, datapump did not have the compress option for data – only the default option to compress METADATA only.

Example in 10g -

TEST schema using datapump -

expdp system/xxxxxx schemas=test directory=data_pump_dir dumpfile=test.dmp logfile=test.log

Export took 2 hours 10 minutes.

Export dump size was 91 gb.

The same data was exported in 11g -

Only difference was adding the following bit to the expdp command in 11g – > compression=all

expdp system/xxxx compression=all schemas=test directory=data_pump_dir dumpfile=test.dmp logfile=test.log

Export took 1 hour 15 minutes.

Export dump size – 13 gb

In 11g it took just over half the time and saved 7 times the space which will tremendously reduce the amount of data moved off to tape on a daily basis especially in some databases where exports are taken daily and moved to tape.

REMAP_TABLE

Allows you to rename tables during an import operation.
 
Example
The following is an example of using the REMAP_TABLE parameter to rename the employees table to a new name of emps:

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=hr.employees REMAP_TABLE=hr.employees:emps

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

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_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