expdp&impdp压缩与加密

• COMPRESS

1. EXPDP

Compression=[none|all|data_only|metadata_only(默认)]

注除了METADATA_ONLY 需要compatible最低为10.2，其它需要compatible最低为11.0.0

其中all|data_only需要启用Oracle Advanced Compression option

COMPRESSION_ALGORITHM= {BASIC默认| LOW | MEDIUM | HIGH}

注：使用要求compatibility最低12.0.0，且必须开启Oracle Advanced Compression option

BASIC — Offers a good combination of compression ratios and speed; the algorithm used is the same as in previous versions of Oracle Data Pump.

LOW — Least impact on export throughput and suited for environments where CPU resources are the limiting factor.

MEDIUM — Recommended for most environments. This option, like the BASIC option, provides a good combination of compression ratios and speed, but it uses a different algorithm than BASIC.

HIGH — Best suited for situations in which dump files will be copied over slower networks where the limiting factor is network speed.

1. IMPDP

• ENCRYPTION

1. EXPDP

ENCRYPTION=[ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE]

$ expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_enc.dmp JOB_NAME=enc1 ENCRYPTION=data_only ENCRYPTION_PASSWORD=foobar

如果开启加密需要设置ENCRYPTION 或ENCRYPTION_PASSWORD其中一个参数即可，如果只设置了ENCRYPTION_PASSWORD，则ENCRYPTION 默认设置为ALL。如果只设置了ENCRYPTION且Oracler wallet是打开的，则ENCRYPTION_MODE默认设置为TRANSPARENT. 如果只设置ENCRYPTION且wallet是关闭的，则会报错

限制：

To specify the ALL, DATA_ONLY, or METADATA_ONLY options, the COMPATIBLE initialization parameter must be set to at least 11.0.0.

Data Pump encryption features require that the Oracle Advanced Security option be enabled.

ALL — enables encryption for all data and metadata in the export operation.

DATA_ONLY — specifies that only data is written to the dump file set in encrypted format.

ENCRYPTED_COLUMNS_ONLY — specifies that only encrypted columns are written to the dump file set in encrypted format. 需要开启Oracle TDE，另外它与ENCRYPTION_ALGORITHM选项冲突

METADATA_ONLY —  specifies that only metadata is written to the dump file set in encrypted format.

另：SecureFiles Considerations for Encryption

If the data being exported includes SecureFiles that you want to be encrypted, then you must specify ENCRYPTION=ALL to encrypt the entire dump file set. Encryption of the entire dump file set is the only way to achieve encryption security for SecureFiles during a Data Pump export operation.

另：Oracle Database Vault Considerations for Encryption

When an export operation is started, Data Pump determines whether Oracle Database Vault is enabled. If it is, and dump file encryption has not been specified for the job, a warning message is returned to alert you that secure data is being written in an insecure manner (clear text) to the dump file set:

You can abort the current export operation and start a new one, specifying that the output dump file set be encrypted.

ENCRYPTION_ALGORITHM = [AES128默认| AES192 | AES256]

使用此选项还必须指定ENCRYPTION or ENCRYPTION_PASSWORD

它与 ENCRYPTION=ENCRYPTED_COLUMNS_ONLY冲突

$ expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_enc3.dmp ENCRYPTION_PASSWORD=foobar ENCRYPTION_ALGORITHM=AES128

ENCRYPTION_MODE = [DUAL | PASSWORD | TRANSPARENT]

If only the ENCRYPTION parameter is specified and the Oracle encryption wallet is open, then the default mode is TRANSPARENT. If only the ENCRYPTION parameter is specified and the wallet is closed, then an error is returned.

If the ENCRYPTION_PASSWORD parameter is specified and the wallet is open, then the default is DUAL. If the ENCRYPTION_PASSWORD parameter is specified and the wallet is closed, then the default is PASSWORD.

注：To use DUAL or TRANSPARENT mode, the COMPATIBLE initialization parameter must be set to at least 11.0.0.

When you use the ENCRYPTION_MODE parameter, you must also use either the ENCRYPTION or ENCRYPTION_PASSWORD parameter.

When you use the ENCRYPTION=ENCRYPTED_COLUMNS_ONLY, you cannot use the ENCRYPTION_MODE parameter. Otherwise, an error is returned.

DUAL  — creates a dump file set that can later be imported either transparently or by specifying a password that was used when the dual-mode encrypted dump file set was created. When you later import the dump file set created in DUAL mode, you can use either the wallet or the password that was specified with the ENCRYPTION_PASSWORD parameter. DUAL mode is best suited for cases in which the dump file set will be imported on-site using the wallet, but which may also need to be imported offsite where the wallet is not available.

PASSWORD — requires that you provide a password when creating encrypted dump file sets. You will need to provide the same password when you import the dump file set. PASSWORD mode requires that you also specify the ENCRYPTION_PASSWORD parameter. The PASSWORD mode is best suited for cases in which the dump file set will be imported into a different or remote database, but which must remain secure in transit.

TRANSPARENT — allows an encrypted dump file set to be created without any intervention from a database administrator (DBA), provided the required wallet is available. Therefore, the ENCRYPTION_PASSWORD parameter is not required, and will in fact, cause an error if it is used in TRANSPARENT mode. This encryption mode is best suited for cases in which the dump file set will be imported into the same database from which it was exported.

ENCRYPTION_PASSWORD = password

在11.1以前，expdp的加密只对设置encrypt类型的列有效，但从11.1开始，expdp可以对非ENCRYPT列的数据（即所有数据）进行加密。使用encryption_password来提供加密使用的key. 如果不希望以明文方式把key显示在命令行，可以使用ENCRYPTION_PWD_PROMPT=YES选项, 它会prompt 中提示输入key:

$ expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp ENCRYPTION_PWD_PROMPT=YES

Data Pump encryption functionality changed as of Oracle Database 11g release 1 (11.1). Before release 11.1, the ENCRYPTION_PASSWORD parameter applied only to encrypted columns. However, as of release 11.1, the new ENCRYPTION parameter provides options for encrypting other types of data.

This means that if you now specify ENCRYPTION_PASSWORD without also specifying ENCRYPTION and a specific option, then all data written to the dump file will be encrypted (equivalent to specifying ENCRYPTION=ALL). To re-encrypt only encrypted columns, you must now specify ENCRYPTION=ENCRYPTED_COLUMNS_ONLY in addition to ENCRYPTION_PASSWORD.

如果导出表包含加密列，但导出未提供encryption_password选项，则会产生warning并以明文方式导出到文件中 If the export operation involves encrypted table columns, but an encryption password is not supplied, then the encrypted columns are written to the dump file set as clear text and a warning is issued.

注：使用此选项需要把ENCRYPTION_MODE选项设置为PASSWORD or DUAL.

Example

$ expdp hr TABLES=employee_s_encrypt DIRECTORY=dpump_dir1 DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY ENCRYPTION_PASSWORD=123456

Encrypted columns in the employee_s_encrypt table will not be written as clear text in the dpcd2be1.dmp dump file.

Restrictions

The ENCRYPTION_PASSWORD parameter is required for the transport of encrypted tablespaces and tablespaces containing tables with encrypted columns in a full transportable export.

Data Pump encryption features require that the Oracle Advanced Security option be enabled.

The ENCRYPTION_PASSWORD parameter is not valid if the requested encryption mode is TRANSPARENT.

To use the ENCRYPTION_PASSWORD parameter if ENCRYPTION_MODE is set to DUAL, you must have Oracle Advanced Security Transparent Data Encryption (TDE) enabled.

For network exports, the ENCRYPTION_PASSWORD parameter in conjunction with ENCRYPTION=ENCRYPTED_COLUMNS_ONLY is not supported with user-defined external tables that have encrypted columns. The table will be skipped and an error message will be displayed, but the job will continue.

1. IMPDP

相似参数：

ENCRYPTION_PASSWORD, ENCRYPTION_PWD_PROMPT