We have an application, developed by a 3rd party that makes use of DBMS_OBFUSCATION_TOOLKIT.DESdecrypt to decrypt encrypted data.

Prior to the 11g (11.2.0.3) upgrade (from 10.2) this worked as expected and successfully decrypted data (encrypted using DESencrypt).

Since the upgrade the data that is returned is basically garbage. Having reported this to the developers they suggested it was because the character set had changed on the database as part of the upgrade. This is not the case.

The character set of the database is WE8MSWIN1252 and wasn't changed from 10g (that I'm aware of).

1. Is it possible to determine what the previous character set was without access to the import log (to ascertain if it was changed)?
2. What else might cause this?
3. From what I understand the WE8MSWIN1252 character set only supports 256 characters, so IF it was changed, it was likely changed from UTF, which supports 90,000. This would mean that even if we converted back we would likely have lost data. Is that correct?

I'm guessing from the third party's response that they are storing encrypted data in VARCHAR2 columns rather than doing the proper thing and storing encrypted data in RAW columns.

How did you upgrade from 10.2 to 11.2? I'm guessing from your reference to the "import log" that you created a new 11.2 database and did an export and an import rather than doing an in-place upgrade.

If you did the upgrade via exporting and importing the data, character set conversion will happen on the export process if the character set of the client's NLS_LANG doesn't match the database character set. And then again during the import process if the client NLS_LANG doesn't match the database character set. An incorrect setting on either the export or import client's NLS_LANG or a difference in character set could cause encrypted data improperly stored in VARCHAR2 columns to be corrupted (which is why you would always want to use RAW columns instead).

Pl post the first 20 lines of the export and import logs (assuming the upgrade was done using export/import). If the NLS_LANG was not set correctly (as Justin states) during export/import, there will be telling warning messages in the first 20 lines of the logs

964818 wrote:
Our DBA is out of the office today, but I will get copies of both imp/exp logs when he returns (I'm unsure of their location).

You are correct though, the data is stored in varchar2. I knew as soon as I checked that it would be.

Can I assume that the data is unrecoverable without restoring or reimporting?

That's probably a safe assumption. Potentially, if you can figure out exactly where it got changed, and assuming that the changes weren't lossy, it should be possible to work out how to write a data slam that would fix the issue. But that is almost certainly way more work than simply re-exporting and/ or re-importing depending on where the character set conversion took place.

Unfortunately this is a production environment and the upgrade was done nearly 3 weeks ago. The issue wasn't highlighted until we ran month end reports and came to process them. It should have been picked up in the application/process testing, but evidently was totally overlooked.

Reimport/restoring is out of the question at this point.

There are very few encrypted columns in the database, so that may be some consolation - in that it might be possible to regenerate just that data - and - depending on how static that data is - reimport just that table/column...

I will try and get copies of the imp/exp logs and post the first 20 lines here so we can see how bad of an issue we are actually looking at.

10g character set was: WE8ISO8859P1
11g character set is: WE8MSWIN1252

So there was a change.

Fortunately the encrypted data is relatively static. We still have the 10g database (just shutdown), so we are going to fire it back up - decrypt the data from that table into a temp table and then pull that data into the 11g database and encrypt it on the way in.

The encryption is working correctly since the 11g upgrade, as anything added to that table since the upgrade seems to decrypt without an issue.

Hopefully this will solve our problem. I just have to look through some of the other tables to ensure there aren't any others like this.