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

I have yet to encounter a database that ever got smaller.  Like waistlines, all databases seem destined to increase in size.  The E-Business Suite is no exception:  as we add more product capabilities and your business grows, so do your Apps databases.

Oracle-supplied solutions to managing Applications database size fall into two categories:  data growth control methods and data management methods.

Growth Control Methods

  • Archiving and purging
  • Database compression

Data Management Methods

A new Oracle whitepaper discussing these topics is now available:

Screenshot of Oracle Information Lifecycle Management Assistant used with E-Business Suite database tables

This whitepaper discusses how these approaches can be used either individually or together to meet your requirements.  Some examples scenarios:

  • Adopt a 100% pure archive and purge strategy where any important data is initially archived and then finally purged when it is no longer needed. The archived tables could be placed in a read-only schema and the datafiles could be resized to reduce their physical space. In addition, database compression feature could be used in conjunction with or as an alternative to resizing the datafiles.
  • Customers could adopt a combination of an archive/purge and ILM strategy, where inactive data is put onto cheaper storage, then after a period of time, this data is archived and eventually purged. Alternatively, customers could choose not to use ILM but instead use database partitioning.
  • Customers could fully implement ILM and. and place less frequently accessed data, that would ordinarily be archived and purged, onto cheaper and less expensive storage devices.
  • Customers could use a 3rd Party archiving product with Oracle’s ILM technologies.

Although there's a lot of conceptual and strategically-oriented material in this whitepaper, it also has concrete and practical information about:

  • The E-Business Suite Purge Portal
  • Archiving and purging programs and affected tables for the following products:
    • Oracle General Ledger (GL)
    • Oracle Payables/Oracle Purchasing (AP/PO)
    • Oracle Receivables (AR)
    • Oracle Cash Management (CE)
    • Oracle Fixed Assets (FA)
    • Oracle Cost Management (CST)
    • Order Management (ONT)
    • Oracle Application Object Library (FND) and Workflow (WF)
  • How to use the Oracle ILM Assistant with an E-Business Suite environment
    • Defining data classes
    • Creating storage tiers for data classes
    • Creating data migration policies
    • Defining and enforcing compliance policies
  • Considerations when creating an information lifecycle management framework for Apps data

Related Articles

Comments:

Steve/Mohsin,

I'm very very pleased to finally a document discussing this topic. Great information !

At Colorcon, we have been working very aggresively on this for the past few years and have seen dramatic results. Our year over year growth for the past 5 years has been 72%, 36%, 18%, 17% and 10%. Clearly the trend has been slower growth rate and has had a very positive impact on our overall ability to manage the environment. We have close to 25 copies of the production environment for test and development. Any amount of space we save in production, we save 25x overall. It also means faster backups, faster clones, better performance and lower storage costs. We ever able to achive all this by two simple techniques, drop unwanted custom/backup tables (both custom and seeded tables created during upgrades etc) and purging (and in some truncating) custom and seeded tables. We are looking to implement advanced compression and ILM techniques to further limit the growth.

Thanks a lot.

Posted by Naveen on December 04, 2008 at 11:04 AM PST #

Naveen,

Glad that you've found this whitepaper useful. I would be *very* interested to hear about your experiences with Advanced Compression and ILM in your EBS environments.

Regards,
Steven

Posted by Steven Chan on December 05, 2008 at 05:22 AM PST #

Steve,

Here are some stats on a test environment.

Table Name After Compression (MB) Before Compression (MB)

GL_ITEM_CST 253 1,120
GL_SUBR_TST 236 1,160
GL_BALANCES 419 1,284
IC_TRAN_PND 760 1,711
EGO_ITEM_TEXT_TL 364 1,727
MTL_SYSTEM_ITEMS_TL 550 2,187
GL_ITEM_DTL 799 3,787
MTL_ITEM_CATEGORIES 1,249 5,072
GL_SUBR_LED 944 5,108
CM_SCST_LED 2,081 8,462

So on an average, we saw 75% compression on these bug tables.

Posted by Naveen on December 11, 2008 at 12:21 AM PST #

Steve/Mohsin

I wanted to bring to your attention an issue that we discovered recently.

We have 5 languages enabled in our EBS environment. Each document attached in EBS, is getting stored for each language. Which means that we have 5 copies of each document in the production environment, one for each language. If you add the 25 clones we maintain of the production environment for various
test and development, we have 125 copies of each document in our storage system. This is a HUGE HUGE waste of space.

I have raised an SR (7245300.993) for this. Hopefully we'll get a resolution on this.

Naveen

Posted by Naveen on December 11, 2008 at 12:25 AM PST #

Naveen,

Thanks for posting your compression statistics. These are very useful. I've passed them on to our internal teams.

As for your second issue, I'm glad to see in your SR that this was due to errors on the part of your users, rather than an EBS bug.

Regards,
Steven

Posted by Steven Chan on December 30, 2008 at 06:21 AM PST #

Hi Naveen,

I am proposing this as a process improvement in my organization. we have been plagued by uncontrolled growth of our oracle database, currently at 4TB.

Would like to hear your experiences, on the mix and match that is followed to cut down your DB.

Thanks,
Sunil

Posted by Sunil Dhanemkula on September 14, 2010 at 05:57 AM PDT #

Is there any certified solution for Oracle e-Business Application Archiving available from Oracle? Advance Thanks.....

Posted by Venkat on August 20, 2013 at 03:14 AM PDT #

Venkat,

Thanks for the inquiry. Oracle E-Business Suite does not currently offer an archiving solution. There are third-party solutions available; however, we do not recommend using third-party archiving solutions in a production environment.

We recommend a combination of ILM (as described in this blog article), database partitioning, database compression and purge routines packaged with E-Business Suite.

Thanks.
Elke

Posted by Elke Phelps (Oracle Development) on August 20, 2013 at 10:39 AM PDT #

Hi Elke,

Thanks for the quick Update.

Venkat Krish

Posted by Venkat on August 20, 2013 at 08:58 PM PDT #

Hi, everyone,

See:

Can You Use Third-Party Tools to Modify Your EBS Database?
https://blogs.oracle.com/stevenChan/entry/3rd_party_ebs

Regards,
Steven

Posted by Steven Chan on January 09, 2014 at 11:55 AM PST #

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
22
23
24
25
26
27
28
29
30
   
       
Today