Topic
7 replies Latest Post - ‏2013-05-07T18:33:48Z by GlenSakuth
RachidKHAWLI
RachidKHAWLI
3 Posts
ACCEPTED ANSWER

Pinned topic InfoSphere CDC replication of crypted columns

‏2012-02-01T19:34:03Z |
Hello,
I'm installaing InfoSphere CDC Oracle Redo 6.5 on AIX 6.1, source and target databases are the same version 11gR2. the source database contains some tables with encrypted columns (Oracle uses a wallet to encrypt those columns). In an empty Schema on the target database we do an initial refresh, this operation ends with no errors, but when the status of the mapping on the management console passe to 'active' (capture of data changes begin to be performed on redologs), and when we do an insert on the source table, the replication end with an Error ORA-12899.
 Interpretation: when doing the first refresh, the CDC read values form tables using "select table", so before passing the values Oracle decrypts the crypted fields, and the CDC replicate the corrects values on the target databases. with mirroring regim, CDC monitor the redolog and not tables, so the CDC get the encrypted value from redolog and is not aware that is an encrypted one and it try to insert the result of the encryption on the target database then the operation failed.
 
Please can any one help!
 
Updated on 2012-03-21T21:52:44Z at 2012-03-21T21:52:44Z by GlenSakuth
  • RobertPhilo
    RobertPhilo
    129 Posts
    ACCEPTED ANSWER

    Re: InfoSphere CDC replication of crypted columns

    ‏2012-02-03T12:57:48Z  in response to RachidKHAWLI
     Hi
     
    I think your best course of action is to open a PMR. I think your diagnosis is  correct, that the redo log scraping cannot use the implicit decryption you would get from the database.
     
    Two further pointers:
     
    1) I was able to replicate an encrypted column for a customer by using an encoding which used code page 65535 to prevent any code page conversion. I am not sure if delivering the encrypted value as is is of benefit to you, however
    2) The CDC planning guide has the following comment - again I am not sure if this helps you
     
    "InfoSphere CDC supports the replication of encrypted columns to the target
    database as a binary but will not decode the columns. Other processes in the
    production environment must be in place to decode binary columns".
     
    Thanks
     
    Robert
      
    • RachidKHAWLI
      RachidKHAWLI
      3 Posts
      ACCEPTED ANSWER

      Re: InfoSphere CDC replication of crypted columns

      ‏2012-02-09T08:44:12Z  in response to RobertPhilo
      Hi Robert, 
      Please, can you tell me more why you think that doing a conversion encoding to binary wouldn't work in my case. Of course on mirroring mode!
      • RobertPhilo
        RobertPhilo
        129 Posts
        ACCEPTED ANSWER

        Re: InfoSphere CDC replication of crypted columns

        ‏2012-02-09T23:50:25Z  in response to RachidKHAWLI
         Hi
         
         1) I was not sure whether replicating the encrypted column as is would actually deliver any benefit on thoe target side - this will depend on the use for the target data
        2) We have two possible methods for replicating encrypted data as is - using a hex encoding or as a binary data type. Again I am not sure if either will work in your environment
         
        Thanks
         
        Robert
  • RachidKHAWLI
    RachidKHAWLI
    3 Posts
    ACCEPTED ANSWER

    Re: InfoSphere CDC replication of crypted columns

    ‏2012-02-03T13:30:31Z  in response to RachidKHAWLI
    Thank you Robert for you reply!
    I have juste open a PMR, and the answer of IBM support is that replication of encrypted columns is not supported by CDC!!!
    But in official documentationn, Oracle database 11gR2 is support but not the ecrypted tablespaces, and they say nothing on encrypted colums!
     
     
    Best Regards,
    Rachid
    • GlenSakuth
      GlenSakuth
      66 Posts
      ACCEPTED ANSWER

      Re: InfoSphere CDC replication of crypted columns

      ‏2012-02-10T23:10:11Z  in response to RachidKHAWLI
       Hi,
       
      As Robert mentioned, if you are doing Oracle to Oracle replication, then replicating in binary is a good option.  Here are a couple of other possibilities depending on your requirements (such as going into a db with different encryption etc):
       
      - %GETCOL could be used on the source, and it will retrieve the data unencrypted.  Note, there will be a performance penalty using this method
       
      - A user exit could be used on the target system.  The user exit could invoke the dbms crypto function to decrypt.
       
      • GlenSakuth
        GlenSakuth
        66 Posts
        ACCEPTED ANSWER

        Re: InfoSphere CDC replication of crypted columns

        ‏2012-03-21T21:52:44Z  in response to GlenSakuth
        There has been further investigation on the different options.  It turns out that it will not work to select and replicate as binary.  Additionally, there have been changes done to the product such that 6.5.1 onwards will not allow the %GETCOL to work in this scenario either.  There is more research underway, and I will provide an updated post when the potential options have been explored further.
        • GlenSakuth
          GlenSakuth
          66 Posts
          ACCEPTED ANSWER

          Re: InfoSphere CDC replication of crypted columns

          ‏2013-05-07T18:33:48Z  in response to GlenSakuth

          With IIDR 10.2, the %GETCOL will work for dealing with encrypted data (that is not one of the key columns).  The %GETCOL will select the data from the database (not process the data from the log).