9 Replies Latest reply on Oct 23, 2019 9:44 AM by Pravin Takpire

    Extract Data From EBS

    Soofi

      Hi All,

       

      I need to extract the data from Oracle EBS database to my local database (Oracle) and after processing the data in my local db, I need to push the data from my local db to a target database which is again Oracle EBS database.

       

      what will be the best practice to establish this connectivity?

      OR

      Is there any way we can create links between EBS DB with another Oracle DB?

        • 1. Re: Extract Data From EBS
          Maaz Khan

          Hello Soofi,

           

          >>I need to extract the data from Oracle EBS database to my local database (Oracle) and after processing the data in my local db<<

           

           

          Please let us know 'data' here implies a single table, or schema?

           

          >>I need to push the data from my local db to a target database which is again Oracle EBS database.<<

           

          Is your target database in which data will be pushed after processing same from where you extracted data initially?

          Always good to mention your environment details (os platform, db version) -

          1. EBS environment details.

          2. local database details.

           

           

          Regards,

          Maaz

          • 2. Re: Extract Data From EBS
            Soofi

            Thanks for your quick response.

             

            Please let us know 'data' here implies a single table, or schema?

            It is Module level.

             

            >>Is your target database in which data will be pushed after processing same from where you extracted data initially?

            No. Data will flow from different sources including Oracle EBS (which is different from target).

             

            Environment Details:

            Oracle EBS 11i

            Local DB : Oracle Enterprise 11gR2

            • 3. Re: Extract Data From EBS
              Maaz Khan

              Hello Soofi,

               

              Modules are mapped to schemas in ebs. Assuming a module say AP, you can try below approach -

              1. Create tns entry pointing to source ebs environment in your local Oracle DB/network/admin

              2. Create a db link based on tns entry created.

              3. Import schema using database link directly from source to destination(local db).

               

              The same method can be followed when pushing data from your local db 11gr2 to any other ebs environment after processing.

               

               

              You can refer below to get compatibility details for db link to work between different versions -

              Client / Server Interoperability Support Matrix for Different Oracle Versions (Doc ID 207303.1)

               

              I suggest, checking this approach with your dba and firstly noting down tables that will be processed instead of going for schema level/module-wise

               

               

              Regards,

              Maaz

              • 4. Re: Extract Data From EBS
                yoonas

                Soofi,

                 

                Is this for some testing purpose?

                 

                If its for production i am afraid oracle support this unless you are doing it for _interface tables

                 

                I need to extract the data from Oracle EBS database to my local database (Oracle) and after processing the data in my local db

                Just to understand what exactly you are trying to achieve, what kind of process you mean on the local database?

                 

                For data copy you may also have look at sqldeveloper option as well(especially if its one time task)

                https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/sqldev/r31/DatabaseCopy/DatabaseCopy.html

                 

                 

                Regards,

                Yoonas

                • 5. Re: Extract Data From EBS
                  Soofi

                  I am having three different Oracle EBS Instances installed. Now what I need is, to get all the data from three different EBS sources and migrate to one target EBS db, kind of ETL process.

                   

                  Is there any better way you suggest?

                  • 6. Re: Extract Data From EBS
                    yoonas

                    Soofii,

                     

                    All these three instances are with exact same setup?

                    These kind of consolidation is supported , did you check with support?

                     

                    I am not aware of any such consolidation so far, interesting though

                     

                    Just for an example, If we just take ap_invoices table, when you merge data from  the source instances to target , how will you take care of primary and foriegn key relationships

                     

                    Regards,

                    Yoonas

                    • 7. Re: Extract Data From EBS
                      Kanda-Oracle

                      Hi

                       

                      Can you share the purpose of 'target database'

                       

                      Is it for meant backup/Disaster recovery or some other reason (example reporting purpose) ?

                       

                      Thanks!

                      • 8. Re: Extract Data From EBS
                        Soofi

                        yoonas The Constraints in each modules will be a big challenge. I didn't checked with Oracle support so-far.

                         

                         

                        Kanda-Oracle The purpose is to export data from different EBS systems and merge them into a single EBS system. It is more or less an ETL operation.

                         

                         

                        Regards,

                        Soofi

                        • 9. Re: Extract Data From EBS
                          Pravin Takpire

                          If you are planning to merge 3 EBS instances in single instance, i would suggest you contact Oracle Support as this will involve many functional steps and there are lot many constraints. Database operations is just a small part of the project, major part is on Functional side ( setup). Better take help of consultants ( Functional and Technical) which can help you folks with setups/customization. Mostly data transfer can happen via ExpDP/ImpDP

                           

                          regards

                          Pravin