Question: How do I make an
encrypted export job?
I'm using a data pump export (expdp) and I want to hide the
password and data.
Data Pump allows the export to be done in encrypted mode. This
mode allows having the encrypted columns maintained in the dump
file. There are different modes of the encryption available, i.e.
dual, transparent and password. Also, you can decide where the
encryption mode should be applied; for example, to the entire data
and metadata (all), just the metadata (metadata_only) or none.
encryption modes of dual and transparent require the Oracle Wallet
to be open and available and need the compatible mode of the
database to be set to 11.0.0. The simplest mode is the password
mode which needs the password to be supplied at the time of the
export and the same password is needed with the import. Failing
would lead to the import's failure. Encryption requires the
Advanced Security license option.
Data Pump has the following encryption parameters:
Is used to encrypt data before writing it to the dump file and
accepts five values
encryption for both data and metadata of the exported objects
Enables encryption for only data that is exported
Enables encryption only for encrypted columns
Enables encryption of the metadata of the exported objects
Disables encryption of the data and metadata of the exported
the algorithm for the encryption and accepts three algorithms:
encryption_mode: Specifies the type
of security and accepts the following values:
password: Required to provide a
password to encrypt the dump file
transparent: Requires a wallet to
dual: Creates a dump file which
might be imported using both the above mentioned modes
encryption_password: Is used to
provide a password for the dump file and differs between
Oracle 10g and 11g. If this parameter is used, then the
encryption parameter's default value becomes ALL. If both
parameters are omitted, then the encryption parameter is set
In the following example, use all parameters to export data
that is encrypted and password provided using the AES256 algorithm
dumpfile=dp_dir:test.dmp logfile=dp_dir:test.log encryption=all
When you try to import the dump file without providing any
password, you get the following error:
impdp usr2/usr2 remap_schema=usr1:usr2
ORA-39002: invalid operation
ORA-39174: Encryption password must be supplied.
However, by supplying the correct password, you are able to
import the object:
remap_schema=usr1:usr2 dumpfile=dp_dir:test.dmp logfile=dp_dir:log.log