Thursday Nov 21, 2013

Can You Use Third-Party Tools to Modify Your EBS Database?

In short: no. But it's possible to give a more-nuanced answer for one scenario where they might be useful.

Our official policy on direct modifications to EBS databases

Every Oracle E-Business Suite manual in our documentation library contains this warning (emphasis added):

Do Not Use Database Tools to Modify Oracle E-Business Suite Data

Oracle STRONGLY RECOMMENDS that you never use SQL*Plus, Oracle Data Browser, database triggers, or any other tool to modify Oracle E-Business Suite data unless otherwise instructed.

Oracle provides powerful tools you can use to create, store, change, retrieve, and maintain information in an Oracle database. But if you use Oracle tools such as SQL*Plus to modify Oracle E-Business Suite data, you risk destroying the integrity of your data and you lose the ability to audit changes to your data.

Because Oracle E-Business Suite tables are interrelated, any change you make using an Oracle E-Business Suite form can update many tables at once. But when you modify Oracle E-Business Suite data using anything other than Oracle E-Business Suite, you may change a row in one table without making corresponding changes in related tables. If your tables get out of synchronization with each other, you risk retrieving erroneous information and you risk unpredictable results throughout Oracle E-Business Suite.

When you use Oracle E-Business Suite to modify your data, Oracle E-Business Suite automatically checks that your changes are valid. Oracle E-Business Suite also keeps track of who changes information. If you enter information into database tables using database tools, you may store invalid information. You also lose the ability to track who has changed your information because SQL*Plus and other database tools do not keep a record of changes.

Can data corruption really occur?

Yes. Even if you're using SQL*Plus, which is obviously not a third-party tool, you run the risk of corrupting your EBS database.  This is true for all tools that modify an EBS database directly.

What about third-party data archiving and purging tools?

Some third-party tools reduce the size of EBS databases by deleting data.  Some use database links, aliases, and other approaches to separate older data into other databases.

These tools can be useful for reducing the size of a production database for development purposes.  It generally doesn't matter if these smaller sandbox or testbed databases have referential integrity violations.

You should never use these tools to reduce the size of your production EBS database.

Why are these tools dangerous?  They claim that they're safe.

The E-Business Suite data model is partially documented here:

Unfortunately, that manual is not comprehensive. Third-party tools that rely on it to determine data relationships in the EBS data model will miss important dependencies.

EBS data dependencies are implemented through seed data, business logic, and other undocumented areas.  These hidden and undocumented dependencies mean that only current EBS Development staff are qualified to determine whether data can be safely removed. 

It is certain that third-party tools will violate EBS database referential integrity in some manner.  This is true even for third-party tools that are produced by people who used to work for Oracle EBS development.  EBS products continue to evolve and new dependencies are introduced on a regular basis.  These new dependencies may be undocumented.

What are the support implications of using third-party tools?

Oracle regards the use of third-party database tools to be customizations. Oracle's official policy is detailed here:

You should also review the support implications for using third-party tools.  They boil down to this:

  • Issues that are isolated to the E-Business Suite will be investigated by E-Business Suite Development.
  • Issues that are isolated to third-party tools should be directed to the third-party vendor.
  • E-Business Suite Development will provide E-Business Suite Development patches for issues that can be reproduced in environments built using E-Business Suite documented procedures and tools.
  • If the third-party vendor cannot resolve issues isolated to their tools, you should restore from a backup and revert to using Oracle’s documented tools and procedures.

Related Articles



« November 2013 »