IMPDP EXPDP ENCRYPTION_PASSWORD Parameter ORA-39173: Encrypted data has been stored unencrypted in dump file set


Error Description
Data pump expdp export failed with following error.

Data pump expdp export failed with following error.
. . exported "SCOTT"."TABLE_ENCRPT"                      5.867 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scott/tde.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 00:28:16

Solution Description
This error is because the table TABLE_ENCRPT is having encrypted columns using Transparent Data Encryption (TDE). So when you try to export the table you should use the encryption_password option to get rid of this error.
Example for encryption_password Parameter for encrypted tables
This table is created with encrypted columns in the following way with TDE.
  1. Add an entry to sqlnet.ora file
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=D:\Admin\Wallet)))
  1. Create the wallet directory and check whether oracle user has read write and execute permission for the directory.
mkdir D:\Admin\Wallet
  1. Set the encryption key for the wallet.
SQL> alter system set encryption key authenticated BY "encr_pwd";
System altered.
  1. Verify the wallet directory whether the file is created properly or not in D:\Admin\Wallet directory.
  1. Table encryption example(Oracle 10g has introduced table encryption)
Create table table_encrpt with a column encrypt
SQL> create table table_encrpt(emp_no number(4),
   name varchar2(10),
   card_num varchar2(16) ENCRYPT);
Table created.
SQL> insert into table_encrpt values (11, 'John', '1234123412341234');
1 row created.
SQL> commit;
Commit complete.
Run the export job without encryption_password option
$  expdp scott/scott directory=exp_dir dumpfile=tde.dmp tables=table_encrpt
Export: Release 11.1.0.7.0 - 64bit Production on Saturday, 16 July, 2011 0:25:52
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=exp_dir dumpfile=tde.dmp tables=table_encrpt
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SCOTT"."TABLE_ENCRPT"                      5.867 KB       1 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scott/tde.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 00:28:16
Perform the expdp with encryption_password option and the error is removed.
expdp scott/scott encryption_password=Encr_pwd directory=exp_dir dumpfile=tde.dmp tables=table_encrpt
Export: Release 11.1.0.7.0 - 64bit Production on Saturday, 16 July, 2011 0:31:18
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** encryption_password=******** directory=exp_dir dumpfile=tde.dmp tables=table_encrpt
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "SCOTT"."TABLE_ENCRPT"                      5.875 KB       1 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/scott/tde.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 00:33:42
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google