<<Back to Oracle DATAPUMP Main Page
How to Perform Encrypted Database Export
To take encrypted export either use ENCRYPTION or ENCRYPTION_PASSWORD parameter, or both,Syntax:
ENCRYPTION = [ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE]
To use the ENCRYPTED_COLUMNS_ONLY option, you must have Oracle Advanced Security Transparent Data Encryption (TDE) enabled.
Example
$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
SCHEMAS=TEST
REUSE_DUMPFILES=Y
ENCRYPTION=DATA_ONLY
ENCRYPTION_PASSWORD=TESTENCRYPTION
$ expdp system@PDB01 parfile=exp_full_pdb01.par
Export: Release 12.2.0.1.0 - Production on Fri Oct 12 12:29:54 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB01 parfile=exp_full_pdb01.par
. . exported "TEST"."PEOPLE" 6.804 KB 1 rowsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB01 parfile=exp_full_pdb01.par
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/dbatst2/stage/dump/EXP_PDB01_FULL01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 12 12:49:11 2018 elapsed 0 00:19:13
If taking an encrypted export the dumpfile can be encrypted using any one of the supported algorithm by specifying ENCRYPTION_ALGORITHM parameter if not specified default is AES128
Syntax and DescriptionENCRYPTION_ALGORITHM = [AES128 | AES192 | AES256]
Note: The ENCRYPTION_ALGORITHM parameter cannot be used in conjunction with
ENCRYPTION=ENCRYPTED_COLUMNS_ONLY because columns that are already encrypted
cannot have an additional encryption format assigned to them.
While performing encrypted export you can define the method of encryption to be used by using ENCRYPTION_MODE parameter
Syntax and Description
ENCRYPTION_MODE = [ PASSWORD | TRANSPARENT | DUAL ]
PASSWORD: Password based encryption
TRANSPARENT: Wallet based encryption
DUAL: Both wallet as well as password (specified during export) based encryption
To perform password based encryption, if you have not configured wallet use ENCRYPTION_PASSWORD parameter.
Obviously entering the password in export parameter file (using ENCRYPTION_PASSWORD) or entering it in a clear text format at export prompt is not safe and therefore you can use ENCRYPTION_PWD_PROMPT instead ENCRYPTION_PASSWORD parameter to allow expdp let you securely enter the password for export file encryption
ENCRYPTION_PWD_PROMPT=[YES | NO]
Example:
Lets modify the parfile a little and run the export again.
$ cat exp_full_pdb01.par
DIRECTORY=DUMP
DUMPFILE=EXP_PDB01_FULL%U.DMP
LOGFILE=EXP_PDB01_FULL.LOG
SCHEMAS=TEST
REUSE_DUMPFILES=Y
ENCRYPTION=DATA_ONLY
ENCRYPTION_PWD_PROMPT=Y
ENCRYPTION_MODE=PASSWORD
ENCRYPTION_ALGORITHM=AES192
$ expdp system@PDB01 parfile=exp_full_pdb01.par
Export: Release 12.2.0.1.0 - Production on Fri Oct 12 13:01:19 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Encryption Password:
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB01 parfile=exp_full_pdb01.par
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@PDB01 parfile=exp_full_pdb01.par
. . exported "TEST"."PEOPLE" 6.804 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/dbatst2/stage/dump/EXP_PDB01_FULL01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 12 13:20:41 2018 elapsed 0 00:19:19
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/dbatst2/stage/dump/EXP_PDB01_FULL01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Oct 12 13:20:41 2018 elapsed 0 00:19:19
NOTE:- encrypted dumpfile can only be imported if proper key (password/wallet) is available during import
Comments
Post a Comment