UK Oracle User Group

Next Generation Database Archiving for Oracle Applications at Collaborate13

Next Generation Database Archiving for Oracle Applications at Collaborate13

16 April 2013

The OAUG hosted its annual convention, Collaborate13, this week in Denver, CO. The week started out with beautiful spring weather and turned quickly into frigid temperatures with a snow flurry bonus. The rapid change in weather didn’t stop 4,000 attendees from elevating their application knowledge in the mile high city. One topic that was very well attended from our perspective was the evolution of database archiving.

The Archive and Purge SIG held its semi-annual meeting at the event hosting a panel of database archiving vendor customers.  This was an incredibly interactive and dynamic discussion on the challenges Oracle E-Business Suite application owners are facing with large data volumes.  Most had tried using the archiving capability delivered from Oracle and were not successful.  Oracle’s archive and purge functionality prevents end users from accessing the data after it has been archived – this was cited as a major obstacle.

Business users in attendance stated that they did not want to lose access to data.  IT and Apps DBAs in attendance stated they were suffering from performance and cost issues and needed a solution other than a hardware upgrade.  Oracle suggests upgrading to Exadata – unfortunately, many organizations cannot justify that level of investment.

In the session ‘How to replace archiving with partitioning to improve Oracle Applications Performance’, we presented an alternative to traditional database archiving solutions.   It was an overview of several Informatica customer case studies who replaced their database archiving implementation with Smart Partitioning.  At the end of the session, attendees coined Smart Partitioning as ‘Next Generation Database Archiving’.   

Smart Partitioning uses native database partitioning to segment data based on the application (General Ledger, Inventory, etc.) and a business policy, such as date, business unit (org id), or status.  All related data is co-located on the same physical database partition and can be managed at that level of granularity.  For example, one year of General Ledger data can reside on a database partition and can be compressed, tiered, archived or dropped by simply issuing a command for that partition.  Smart partitioning replaces the need to issue row-level delete commands, archive to a separate database, manage or maintain a separate archive database.  All of these complexities are eliminated – and make upgrading to R12 that much simpler.

In the session, ‘Oracle Partitioning Delivers 6 Times Improvement in ERP Performance for Avago Technologies’, Micah Deriso presented on behalf of Avago who had previously implemented a database archiving solution. Avago was still facing performance and growing storage costs issues and were not looking forward to the complexities of upgrading both production and the archive database to R12.  In this case study, Avago decided to ‘de-archive’ their archive database into production, upgraded to R12, implemented Smart Partitioning and saw 6X improvement in performance even with all of the archive data back in production.  They stated that they got all of the benefits of archiving without all of the complexities.  They reduced 3 days of DBA time spent on monthly archive cycles down to 15 minutes of minor partition maintenance tasks.

Finally, in the session, ‘Cummins Engines Makes Divestitures From E-Business Suite Easy’, their lead Apps DBA presented an interesting use case of Smart Partitioning for a divestiture, Nelson Global Tube.  They needed to separate out the financial and manufacturing data for this business unit out of three tightly integrated and interconnected Oracle E-Business Suite implementations.  They created identical clones of the three environments and needed to remove everything except this business unit’s data.  Using an approach based on row-deletes was not considered an option.  By leveraging the Smart Partitioning approach, they were able to complete the task in just a matter of weeks.  This use case is coming up more often as we see organizations splitting and merging Oracle Applications to align with business organizational changes.

The weather remained cold, and snow was still on the ground as the conference concluded.  However, the Informatica ILM team’s message was white hot. Users are still challenged with managing data growth in their Oracle Applications and that with Smart Partitioning as the foundation for the next generation of database archiving, better weather is just around the corner.