My Oracle Support Banner

Problems with (Importing) Encrypted Data After Character Set Change Using Other NLS_CHARACTERSET Database or Upgrading the (client) Oracle Version (Doc ID 1297507.1)

Last updated on DECEMBER 25, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Goal

There is encrypted data stored in a CHAR, VARCHAR2, LONG or CLOB datatype in your database and after
* or changing the NLS_CHARACTERSET using alter database/Csalter or the DMU tool
* or using export/import into a new database that uses a different NLS_CHARACTERSET
* or moving the data using dblinks into a new database that uses a different NLS_CHARACTERSET
* or upgrading the database to a newer version
* or using a newer client side connection method (start using or upgrading ODBC, JDBC etc)

Your encrypted data is "corrupted" or in other words the values stored in the new database do not match the values stored in the new database.
So the encrypted data is not recognised any more by the application - typically this is used to store user passwords (not related to actual database users) for an application and the application will not allow (some) users to login any more after one of above changes.

All below is actually also valid when using CHAR, VARCHAR2 , LONG or CLOB columns to store other binary data like PDF , doc, docx, jpeg, png , etc files.

Note that with encryption we do not mean TDE here, the Transparent Data Encryption (TDE) feature introduced in Oracle 10g Database Release 2 allows sensitive data to be encrypted within the datafiles to prevent access to it from the operating system.
It's mainly to avoid someone poking around in (copies of) the data files on OS level, any user who has permissions to decrypt the data sees the data unencrypted.
If you have a table that is encrypted with TDE but, when using a user who has permissions to decrypt the data, you see ORA-29275 for some rows the problem is NOT with TDE but with the data itself. TDE is one layer "lower".

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.