advertisements
_____________________________________________________________________________________________________________________
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.
- Add an entry to sqlnet.ora file
ENCRYPTION_WALLET_LOCATION =
(SOURCE=
(METHOD=file)
(METHOD_DATA=
(DIRECTORY=D:\Admin\Wallet)))
- Create the wallet directory and check whether oracle user has read write and execute permission for the directory.
mkdir D:\Admin\Wallet
- Set the encryption key for the wallet.
SQL> alter system set encryption key authenticated BY "encr_pwd";
System altered.
- Verify the wallet directory whether the file is created properly or not in D:\Admin\Wallet directory.
- 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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment