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

ORA-39173: Encrypted data has been stored unencrypted in dump file set

February 28, 2023

Hello Friend's ,

Welcome to a new post

Solution to error ORA-39173: Encrypted data has been stored unencrypted in dump file set.

So lets get started with the senario :

We got the error while taking an export as below : 


SQL> !expdp system@FlyDB directory=FlyEXP dumpfile=Fly_EXP27022023.dmp logfile=LOG_Fly2702.log schemas='BIGONE' compression=ALL

Export: Release 19.0.0.0.0 - Production on Mon Feb 27 15:39:54 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@FlyDB directory=FlyEXP dumpfile=Fly_EXP27022023.dmp logfile=LOG_Fly2702.log schemas=BIGONE compression=ALL
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "BIGONE"."BIGTAB"                           5.826 MB 2000000 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /data_mig1/FlyEXP/Fly_EXP27022023.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 27 15:40:34 2023 elapsed 0 00:00:32

Cause: The encrypted data was exported without a password for encryption. This is merely a warning that the dumpfile is insecure and that it could potentially jeopardise the encrypted data.

Solution: ENCRYPTION MODE or ENCRYPTION PASSWORD parameters must be used in order to secure the export dumpfile and protect the encrypted data while exporting the database.

Afterwards, you'll need this wallet or password to import the data from this export dump.

To fix the mistake Use the following technique to export the data for ORA-39173.


SQL> !expdp system@FlyDB directory=FlyEXP dumpfile=Fly_EXP27022.dmp logfile=LOG_Fly27.log schemas='BIGONE' compression=ALL ENCRYPTION_PASSWORD= MyPassword

Export: Release 19.0.0.0.0 - Production on Mon Feb 27 15:42:25 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@FlyDB directory=FlyEXP dumpfile=Fly_EXP27022.dmp logfile=LOG_Fly27.log schemas=BIGONE compression=ALL ENCRYPTION_PASSWORD=********
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "BIGONE"."BIGTAB"                           5.826 MB 2000000 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /data_mig1/FlyEXP/Fly_EXP27022.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Feb 27 15:42:58 2023 elapsed 0 00:00:29

No need for an encryption password when importing into the same database. 


SQL> !impdp system@FlyDB directory=FlyEXP dumpfile=Fly_EXP27022.dmp logfile=Import.log tables=BIGONE.BIGTAB

Import: Release 19.0.0.0.0 - Production on Mon Feb 27 15:52:01 2023
Version 19.18.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@FlyDB directory=FlyEXP dumpfile=Fly_EXP27022.dmp logfile=Import.log tables=BIGONE.BIGTAB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "BIGONE"."BIGTAB"                           5.826 MB 2000000 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Feb 27 15:52:31 2023 elapsed 0 00:00:19

Hope This Helps

Next
« Prev Post
Previous
Next Post »
Subscribe to: Post Comments (Atom)