Partitioning allows the segregation of data (tables/indexes) into smaller segments, while maintaining a seamless view of all the data as a whole.
Figuring out the best partitioning approach for your tables and indexes can take a considerable amount of analysis, but if implemented successfully, can potentially reap extensive performance gains and potential storage savings.
Older, not heavily engaged partitions can be ported to cheaper, lower-end storage solutions. Additionally, depending on the configuration of your partitions, when cloning to other instances, the partitions can be removed, thus reducing the storage needs on target servers.
Partitioning is standard out of the box for only a select set of Oracle Application modules and custom partitioning must have the database partitioning option licensed with Oracle.
This still doesn't address the data growth issue and takes a considerable amount of ongoing maintenance and support to maintain the partitions and performance of the partitions.
Database archiving provides the ability to archive data that is seldom accessed off to various storage options while retaining the ability to easily access that data and ensuring referential integrity, as well as providing the ability to easily remove that data once it's retention requirements have been met. First you need to determine what data you wish to archive and what your data retention and availability requirements are for that data. How long are you willing to wait to retrieve that historical data? Can you view that data through a different medium other than your current application? Are you at risk if you do not archive your data?
Several options for archiving data include:
- Backups/snapshots of the database, kept available for as long as you need the data. This unfortunately is just a single snapshot and does not address a rolling archive strategy.
- For certain data, you can export the data and keep as a .dmp file or extract the data into a CSV file.
- You can build a datamart, data warehouse or another reporting database and relocate the data.
- Third-party tools that will extract the pertinent data, file/store it and then provide the ability to remove it from the source database. Many third-party vendors provide these tools and are constantly improving their products to be able to execute the archiving capability against most modules in the E-Business Suite.
Make sure that if you back up data that you may not need for several years, you take into consideration the software and platform that it is currently on. You may want to keep a backup copy of the OS software and application/database binaries filed away safely. You may be forced to rebuild an environment to be able to retrieve that data and then find out you can't get that OS and software anymore.
In comparison to partitioning, this data, once it has been archived, can be fully removed from the source database to the media/format you have chosen (see "Hierarchical storage management" below). Depending on the complexity of your data, this can become a great deal more difficult to implement if you attempt to build a custom-developed solution to archiving. Again, it may be easier and more cost-effective to use the third-party vendors to implement your strategy.
Hierarchical storage management
It's time to realize that not all data is equal. Some data is business-critical and needs to be accessed in milliseconds. But much of the data we accumulate is not so critical nor does it require the same level of access. But ask yourself this hard question: How much of your data do you store on expensive, highly redundant storage which is rarely accessed and is not business-critical? That's where hierarchical storage management (HSM) comes in.
HSM views all data as being in some phase of its "lifecycle." Like most of us, a piece of data is born, serves some purpose and slowly declines in value to the organization. That's not a very happy thought for human beings, but for data we can be less emotional.
A typical data lifecycle would include points where it is transactional, referential, historical, auditable and finally disposable. Transactional data is business-critical and highly relevant to operations. It requires high-speed access and experiences high incidences of retrieval. On the other end of the lifecycle, auditable data requires lower access speeds and also low incidences of retrieval. Plus, it may be read-only at this point in its life. So why store both in the same storage environment and hassle with the performance degradation?
Here are some points to get you started.
- Evaluate your data store, categorizing various types of data into one of the data lifecycle phases. How often is it accessed? How fast is it needed? What value does it have? Who owns it? How many users require it?
- Consider where the data is on your storage platforms. Could it be more efficiently stored elsewhere? You might cringe at the thought, but there is probably some data that needs to be relegated to microfiche and much that can be archived to tape and stored.
- Evaluate your service-level agreements for data management with your stakeholders. Help them see the value of HSM.
- Consider the options for HSM tiers of data storage. Here are the most popular:
- Tape backups
- Secondary data storage (lower-cost and slower storage)
- Optical disk
- Delete it
- Explore the HSM options from storage vendors.
- Publish your HSM policy and ensure the buy–in of the data owners.
Start where you can achieve big storage wins quickly.
Database growth continues to be a challenge for most organizations. It may be driven by new regulations, new applications or the increasing need for the storage of non-traditional data types including video, graphics and sound, Regardless of the growth drivers every organization needs to have a clearly defined strategy that matches company policies and financial resources. In this document we've offered six options to consider as part of that strategy. As you build your strategy it will be critical to involve the owners, users and managers of each type of data in regularly scheduled discussions. With their input and approvals your organization will be in a better position to manage your data before it manages you.
About the author
John Stouffer has over 18 years experience with Oracle database administration with more than 14 years supporting Oracle's E-Business Suite Applications environments including architectural design, installation, implementations, upgrades, and system assessments in federal, public and commercial industry. John regularly presents and moderates at local, regional, national, and international conferences for Oracle Applications users and support staff. He is a co-author of two published books on installing, upgrading and maintaining Oracle 11i. John chairs the OAUG Upgrade SIG and serves as the chair for Oracle's Fusion Council, as well as actively participating in other GEO and SIG activities and was honored as OAUG Member of the Year for 2004. John is currently a Solution Architect and VP of National Practices at Solution Beacon, where he provides strategic technical expertise to clients.