Data Pump Encryption in Oracle

Encryption parameters is a  new feature introduced in oracle 10g data-pump . The only parameter used in 10g is encryption password  . In Oracle 11g , three more encryption parameter was introduced in data-pump .The three parameter are  encryption , encryption_algorithm  and  encryption_mode .

To enable encryption, either the ENCRYPTION or ENCRYPTION_PASSWORD parameter, or both, must be specified. If only the ENCRYPTION_PASSWORD parameter is specified, then the ENCRYPTION parameter defaults to ALL. If neither ENCRYPTION nor ENCRYPTION_PASSWORD is specified, then ENCRYPTION defaults to NONE. Data pump encryption is specified by the encryption parameter, the algorithm of the encryption and the mode of the encryption

Here is a demo which includes all the encryption command.

C:\>expdp system/ramtech@noida directory=dpump  dumpfile=hr_schema.dmp logfile=exp_hrlog.log  schemas=hr encryption=all  encryption_password=india encryption_algorithm=AES256 ENCRYPTION_MODE=password

Export: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 17:18:32

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@noida directory=dpump dumpfile=hr_schema.dmp logfile=exp_hrlog.log schemas=hr encryption=all encryption_password=******** encryption_algorithm=AES256 ENCRYPTION_MODE=password

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "HR"."COUNTRIES"                            6.382 KB      25 rows

. . exported "HR"."DEPARTMENTS"                          7.023 KB      27 rows

. . exported "HR"."EMPLOYEES"                            16.81 KB     107 rows

. . exported "HR"."JOBS"                                 6.992 KB      19 rows

. . exported "HR"."JOB_HISTORY"                          7.062 KB      10 rows

. . exported "HR"."LOCATIONS"                            8.281 KB      23 rows

. . exported "HR"."REGIONS"                              5.492 KB       4 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  D:\DPUMP\HR_SCHEMA.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:21:29

Now we will import the above dump file without specifying the password and check the error.

C:\>impdp system/ramtech@noida directory=dpump dumpfile= hr_schema.dmp logfile=imp_hr.log remap_schema=hr:harry

Import: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 17:30:17

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-39174: Encryption password must be supplied.

Now  we use encryption_password command to successfully completed the above task

C:\>impdp system/ramtech@noida directory=dpump dumpfile= hr_schema.dmp logfile=imp_hr1.log remap_schema=scott:harry  encryption_password=india 

Import: Release 11.1.0.6.0 - Production on Friday, 15 April, 2011 17:36:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@noida directory=dpump dumpfile= hr_schema.dmp logfile=imp_hr1.log remap_schema=hr:harry encryption_password=********

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"HARRY" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "HARRY"."COUNTRIES"                         6.382 KB      25 rows

. . imported "HARRY"."DEPARTMENTS"                       7.023 KB      27 rows

. . imported "HARRY"."EMPLOYEES"                         16.81 KB     107 rows

. . imported "HARRY"."JOBS"                              6.992 KB      19 rows

. . imported "HARRY"."JOB_HISTORY"                       7.062 KB      10 rows

. . imported "HARRY"."LOCATIONS"                         8.281 KB      23 rows

. . imported "HARRY"."REGIONS"                           5.492 KB       4 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 17:37:12

Enjoy      J J J