We can enable password based encryption for expdp dumps, by using different encryption related parameters in expdp/impdp command.
ENCRYPTION RELATED PARAMETERS:
ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.ENCRYPTION_PWD_PROMPT ——– >>>>>>> INTRODUCED IN ORACLE 12C
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.
ENCRYPTION_PASSWORD:
To encrypt any dumpfile, provide password to ENCRYPTION_PASSWORD in parfile.
dumpfile=emp_enc1.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PASSWORD=oracle ENCRYPTION_ALGORITHM=AES256
Export log
Export: Release 12.1.0.2.0 - Production on Tue Jan 24 12:25:15 2017 Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=emp_enc1.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PASSWORD=******** ENCRYPTION_ALGORITHM=AES256 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported "DBATEST"."EMP_TAB" 19.16 MB 175340 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ******************************************************************************
ENCRYPTION_PWD_PROMPT
With this parameter, No need to write the password in parfile or expdp command , we can pass the password, when it asks for input.
expdp dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES
expdp parfile=encyp.par Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Encryption Password: ------- > Here provide the password FLASHBACK automatically enabled to preserve database integrity. Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 23 MB . . exported "DBATEST"."EMP_TAB" 19.16 MB 175340 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ******************************************************************************
ENCRYPTION PARAMETER DURING IMPDP
If the export dump is generated using an encryption password, then while importing the dump, we need to pass the same password.
dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES
Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Encryption Password: ----- >>> PASS THE PASSWORD HERE --- > Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA dumpfile=emp_enc.dmp logfile=emp_tab.log directory=VEN tables=dbatest.EMP_TAB ENCRYPTION_PWD_PROMPT=YES Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "DBATEST"."EMP_TAB" 19.16 MB 175340 rows Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 24 12:23:38 2017 elapsed 0 00:01:52
If you try to import without encryption password, it will throw an error as ORA-39174: Encryption password must be supplied.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options ORA-39002: invalid operation ORA-39174: Encryption password must be supplied.
