This discussion is archived
9 Replies Latest reply: Oct 12, 2012 12:01 PM by Srini Chavali-Oracle RSS

Decrypting data after upgrade to 11g

967821 Newbie
Currently Being Moderated
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?

Thanks in advance for your help

EDIT: Character set details as follows:

NLS_CHARACTERSET = WE8MSWIN1252
NLS_NCHAR_CHARACTERSET = AL16UTF16

Edited by: 964818 on Oct 11, 2012 12:47 PM
  • 1. Re: Decrypting data after upgrade to 11g
    Justin Cave Oracle ACE
    Currently Being Moderated
    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).

    Justin
  • 2. Re: Decrypting data after upgrade to 11g
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    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

    HTH
    Srini
  • 3. Re: Decrypting data after upgrade to 11g
    967821 Newbie
    Currently Being Moderated
    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?
  • 4. Re: Decrypting data after upgrade to 11g
    Justin Cave Oracle ACE
    Currently Being Moderated
    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.

    Justin
  • 5. Re: Decrypting data after upgrade to 11g
    967821 Newbie
    Currently Being Moderated
    Thanks for the quick reply Justin.

    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.

    Thanks for your help so far!
  • 6. Re: Decrypting data after upgrade to 11g
    Justin Cave Oracle ACE
    Currently Being Moderated
    Does the old 10.2 database still exist somewhere? Or did it get deleted after the upgrade was completed?

    Justin
  • 7. Re: Decrypting data after upgrade to 11g
    967821 Newbie
    Currently Being Moderated
    I believe it still exists but is just shut down. DBA is back in this morning so will gather more info and post it when I can.

    Thanks
  • 8. Re: Decrypting data after upgrade to 11g
    967821 Newbie
    Currently Being Moderated
    This is what I now know.

    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.

    Thanks for all your help. I think we got this now!
  • 9. Re: Decrypting data after upgrade to 11g
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl do check the logs as stated above - if they show warnings, then there may be other as-yet-uncovered issues.

    HTH
    Srini

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points