13 Replies Latest reply on Jun 18, 2016 7:03 PM by ramya3250

    moving tables out of OATM model

    ramya3250

      Hello,

       

      we would like to move two tables from OATM model to non-OATM model. i.e... to use vormetric encryption create a new table space and move two tables from OATM to new table space and encrypt.

       

      Can anyone tell what will be the impact on EBS applications by doing this way ? like any issues during patching or cloning or upgrade etc.....

       

      Thanks.

        • 1. Re: moving tables out of OATM model
          Bashar.

          Hi,

           

          AFAIK, the EBS system won't be affected by where you place the database objects as long as their characteristics are not changed.

          If you perform tablespace-level backup for OATM tablespaces then these objects won't be included.

           

          I suggest to check with Oracle to verify that this encryption solution is supported with EBS.

           

          Regards,

          Bashar

          • 2. Re: moving tables out of OATM model
            ramya3250

            Yes. I have opened an SR but still now we didn't get proper solution or advises from them.

             

            I do not find any documents even anywhere related to these tables moving out of OATM model.

             

            we don't change anything in tables other than moving tables completely out from existing table space to a new table space.

            • 3. Re: moving tables out of OATM model
              ramya3250

              is there a way that particular table was encrypted or not so as to confirm from some view or table ?

              • 4. Re: moving tables out of OATM model
                Bashar.

                Query the DBA_ENCRYPTED_COLUMNS dictionary view to find out encrypted columns.

                 

                https://docs.oracle.com/cd/E18283_01/server.112/e17110/statviews_1084.htm#I1020534

                 

                Regards,

                Bashar

                • 5. Re: moving tables out of OATM model
                  ramya3250

                  select * from DBA_ENCRYPTED_COLUMNS  -----> it gave me 0 rows.

                   

                  our DBA said he had encrypted two tables already.

                  • 6. Re: moving tables out of OATM model
                    Bashar.

                    What is your database version?

                     

                    The TDE may not be enabled properly. Please review the documentation.

                     

                    https://docs.oracle.com/cd/B28359_01/network.111/b28530/asotrans.htm

                     

                    Regards,

                    Bashar

                    1 person found this helpful
                    • 7. Re: moving tables out of OATM model
                      ramya3250

                      DB version 12.1.0.2 and EBS R12.1.3

                       

                      Our DBA saying encrypted these two tables

                       

                      IBY.IBY_CREDITCARD

                      IBY.IBY_EXT_BANK_ACCOUNTS

                       

                      I have compared with other instances for these two tables especially all columns data types nothing was changed from encryption to non-encryption.... i may be be wrong thinking here. like data types changed, etc... reason i was going in deep here bcoz i have seen a document says column level encryption will alter the data types which may impact when we are patching in feature which involves these two tables or related indexes, or dependencies.....

                       

                      Is this table level or column level encryption will be different... multiple columns will involve in one table of course...

                       

                      thanks again for all your help... please share if you have find something...

                       

                      also, i am looking some 'ad' patches for R12.1.3 in such a way these two tables may resize some columns or add new columns or even touching dependent indexes , so that to figure it out in future we will not have any issues.... do u think is there any way possible like that to find out some patches which involves these tables...

                      • 8. Re: moving tables out of OATM model
                        Bashar.

                        I do not think that the datatypes will be changed upon enabling TDE.

                        Where did you read that?

                         

                        The patching will be affected if the patch attempts to index the encrypted columns or increase their length.

                        There are some performance considerations and other limitations discussed in the below document:

                         

                        Using TDE Column Encryption with Oracle E-Business Suite Release 12 (Doc ID 732764.1)

                         

                        I am not aware of any patches that do that specific change but you can test it yourself in a test environment (alter table ..., create index ...).

                         

                        Regards,

                        Bashar

                        • 9. Re: moving tables out of OATM model
                          ramya3250

                          Yes. this document shows completely about columns that gets encrypted and error messages.

                          But in our case as from view dba_encrypted_coumns gives no rows.

                           

                          if someone encrypted complete table, is that something we can see from same view... wondering why no rows

                          • 10. Re: moving tables out of OATM model
                            Bashar.

                            How did your DBA encrypt the mentioned columns?

                             

                            Regards,

                            Bashar

                            • 11. Re: moving tables out of OATM model
                              ramya3250

                              Sorry to bother u with all these questions... bottomline i just want to clarify myself for knowledge purpose about this encryption.

                               

                              This is what our DBA said....

                              encrypted below two tables and their associated indexes using vormetric encryption....

                              IBY.IBY_EXT_BANK_ACCOUNTS

                              IBY.IBY_CREDITCARD


                              ***  This is what my understanding was ****

                              he moved these two tables from existing table space meaning (default TS from EBS R12.1.3 upgrade ... APPS_TS_***) to a newly created table space ENCRYPT_DATA_IBY

                              he moved indexes all the indexes associated to these two tables from again default to a newly created table space ENCRYPT_INDEX_IBY

                              Now what i was understanding my DBA has encrypted these two new table spaces itself..... so if encrypted tablespace means it will encrypt all the tables inside this TS and of course whatever inside the table space..... table space > table > columns ..... etc....

                              In future without even contacting DBA, i just want to find out so and so tables gets encrypted or columns from a view so that if something development tasks related issues then we can provide them an encrypted area...

                              hope u understood what i am looking for...

                              • 12. Re: moving tables out of OATM model
                                Bashar.

                                Since this discussion has been going for some time, I lost track of your original question!

                                The simple answer to your question is that you can just query the tables that are stored in the encrypted tablespaces.

                                 

                                select *

                                from dba_tables

                                where tablespace_name in (select tablespace_name from dba_tablespaces

                                                                              where encrypted = 'YES');

                                 

                                However, there is a procedure for using tablespace encryption in EBS. I do not know if it is supported the way you did it or not. You should log a SR for confirmation.

                                 

                                Using TDE Tablespace Encryption with Oracle E-Business Suite Release 12 (Database 12c) (Doc ID 1584458.1)

                                 

                                Regards,

                                Bashar

                                • 13. Re: moving tables out of OATM model
                                  ramya3250

                                  I have opened SR to find out patches to test on these encryption tables. Here is the update from them. I will have to check with development how it updating table structure.

                                   

                                  1) 22685107:R12.IBY.B => Which updates the table IBY_CREDITCARD

                                  2) 22392719:R12.IBY.B => Which updates the table IBY_EXT_BANK_ACCOUNTS