Fixing ORA-39173: Exporting Encrypted Data Without Encryption

When using Data Pump (expdp) to export data, you might encounter the warning:

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

This typically happens when the exported data contains encrypted columns, but the dump file does not retain that encryption due to missing encryption options in the export command. Let’s understand this better and resolve it.

Scenario: Exporting a Table with Encrypted Data

You attempt to export a table containing encrypted columns using the following command:

[oracle@test ~]$ expdp '"/ as sysdba"' tables=hr.employee_data

The export runs, but you see the following warning:

. . exported "HR"."EMPLOYEE_DATA"  500 MB 250000 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

This warning indicates that encrypted data in the table was exported without encryption in the dump file.

Why Does This Happen?

By default, if you do not specify encryption options in the expdp command, encrypted columns in the source database are saved unencrypted in the dump file. This is intentional and serves as a warning, not an error.

If encryption is required for compliance or security, you need to explicitly enable it in the export command.

Solution: Add Encryption Options to the Export Command

To ensure that encrypted columns remain encrypted in the dump file, use the encryption and encryption_mode parameters in the expdp command.

Example Command with Encryption

[oracle@test ~]$ expdp '"/ as sysdba"' tables=hr.employee_data encryption=all encryption_mode=transparent

Explanation:

• encryption=all: Ensures all data, including encrypted columns, is stored encrypted in the dump file.
• encryption_mode=transparent: Suitable when restoring the dump file to the same database.

If the dump file will be imported into a different database, use the PASSWORD mode instead:

[oracle@test ~]$ expdp '"/ as sysdba"' tables=hr.employee_data encryption=all encryption_mode=password

Important Notes

1. Wallet Requirements:
   • Ensure the Oracle wallet is open before exporting. The wallet contains the encryption key required to process encrypted data.
   • To check the wallet status, use:

SQL> SELECT * FROM v$encryption_wallet;

1. Scope Restrictions:
   • The ENCRYPTED_COLUMNS_ONLY option is valid only with encryption_mode=password. It cannot be used with encryption_mode=transparent.

Conclusion

The ORA-39173 warning is not a critical error but a reminder to secure sensitive data during export. By adding the appropriate encryption options, you can ensure compliance with security requirements and protect your data effectively.