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


Comments:

What about using the APIs for updating data? I understood that using APIs kept the data dependencies.

Posted by Doug on November 22, 2013 at 01:06 PM PST #

Hi, Doug,

This is what's described above in this passage:

"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."

Using our published APIs is considered using Oracle E-Business Suite. This is completely safe -- and encouraged!

Regards,
Steven

Posted by Steven Chan on November 22, 2013 at 01:44 PM PST #

If third party tools are risky for archving, what is the recommended Oracle tool for archiving? Does Oracle ILM pose the same risk?

Posted by Chris on November 25, 2013 at 06:58 AM PST #

Chris,

Thank you for your questions. Our recommended methods for managing database growth are outlined in the following blog article and white paper:

• Oracle E-Business Suite Technology - New Whitepaper: Options for Reducing E-Business Suite Database Sizes

https://blogs.oracle.com/stevenChan/entry/whitepaper_options_for_reducing_ebs_database_size

• Reducing Your Oracle E-Business Suite Data Footprint using Archiving, Purging, and Information Lifecycle Management (Metalink Note 752322.1)

http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=752322.1

If followed as described in the article and white paper, Oracle's recommendations do not pose the same risk.

Regards,
Elke

Posted by Elke Phelps (Oracle Development) on November 25, 2013 at 10:10 AM PST #

Elke/Steven,

While your above post discourages archiving EBS data using 3rd party tools, there are references in My Oracle Support (eg. Note 1111390.1 - Any Available Method of Purging XLA Tables?) on 3rd party tools. For instance, the above note reads - "we are taking the exceptional step of giving the following advice...Customers should work with Oracle partners, for example Outerbay (HP Database Archiving) or Applimation (Informatica), who specialize in the archival and purging of data."

In your above advice, you do not recommend use of 3rd party tools against Production however. This would appear contracdictory, as MOS note does not clearly imply that.

For instance, there is a dire need to provide for archival/purge for XLA/SLA module data. For most sites, these tables bloat to an unmanageable size causing collateral issues around performance and maintenance. I cannot fathom how can a product be available with no feature to handle growth of vast amounts of data.

Regards,
Rakesh

Posted by guest on December 05, 2013 at 06:13 AM PST #

Hello, Rakesh,

Thank you for bringing that Note to our attention. The guidance in that Note was incorrect and has been removed.

We understand that there is strong demand for supporting archiving/purging for XLA/SLA modules. Customers should press their Oracle account manager to share this demand with the XLA/SLA Development teams.

I have examined a few XLA/SLA enhancement requests around this functionality. At present, the EBS Development teams have rejected those enhancement requests. This should tell you something.

If EBS Development thinks that this is too difficult for them to do safely, there is no way that a third-party can guarantee referential integrity for their third-party tools.

Regards,
Steven

Posted by guest on December 09, 2013 at 11:54 AM PST #

In your Dec 04, 2008 article New Whitepaper: Options for Reducing E-Business Suite Database Sizes, you state "Customers could use a 3rd Party archiving product with Oracle’s ILM technologies."

That appears to be in conflict with your statement in this blog, "You should never use these tools to reduce the size of your production EBS database." Is your earlier (2008) opinion now reversed or am I missing something?

Thanks,
Mike

Posted by guest on April 17, 2014 at 06:33 AM PDT #

Hi, Mike,

The older article does mention third-party tools. Blog articles do not set Oracle policy -- they're considered editorials. So there is no "opinion to be reversed," strictly speaking.

We're trying to have more-nuanced conversations about this now. Since the original article's publication, we've learned that customers have two different requirements: 1) Reducing the size of production clones for development purposes; 2) Reducing the size of production databases.

For #1: Customers may use third-party tools to reduce the size of clones used for development purposes; referential integrity does not matter for such testbeds. Customers can use whatever tools they wish, including third-party tools and Oracle-provided utilities.

For #2: Only Oracle-provided archive/purge routines are approved for this scenario. At OAUG Collaborate Vegas 2014, several vendors or third-party tools emphasized that some of their tools' functionality calls Oracle-provided archive/purge routines. Those functions may be safe for use in production environments.

Customers should review their requirements carefully with third-party vendors to determine the most-appropriate use of those tools in E-Business Suite environments.

Regards,
Steven

Posted by Steven Chan on April 17, 2014 at 11:19 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
23
24
25
26
27
28
29
30
   
       
Today