Manage Learn to apply best practices and optimize your operations.

Hierarchical storage management for database growth, part 1

Hierarchical storage management is one of several possible solutions to uncontrolled database growth. Part one of this tip outlines some other options, such as data deletion and hardware upgrades, and their potential pitfalls.


With today's growing need to retain every little bit of data, be it to meet regulatory requirements, support end-user needs, feed business intelligence and trend analysis, accommodate overall business growth or just support the consolidation of data from various systems, data managers are looking for solutions to better address and manage data growth.

We see the negative effect this growth of data has on applications and databases in two areas. The first effect is the failure to meet service levels because of performance degradation. This is compounded by the increasing time required for backup and recovery, cloning and upgrades. The second negative effect is the increasing TCO (total cost of ownership) to maintain, support and plan for the constant data growth.

When you add it all up, not having a clear data growth management strategy has high costs. Here are several questions to answer:

  • Are you keeping the right amount data for legal and compliance requirements?
  • Are you keeping your data in the right formats based on its "lifecycle"?
  • Are the users getting the data and information they need or losing productivity trying to sort through it all?
  • Is it taking an unacceptable amount of time to retrieve the data or execute processes against the current volume of data?
  • Is the IT staff overwhelmed just trying to keep up with all the requirements and overhead to maintain it all? Aren't they already working 16- to 24-hour days?

So, what do we do about it? Get rid of the users? Delete all the data? Rally the government and try and change the legal requirements? Spend a lot of money and effort and just keep growing? It's not likely any of these will occur, so let's look at real solutions. Here are some of the viable solutions to consider and a brief explanation of each:

  • Data deletion/purging
  • Add capacity through hardware upgrades
  • Decentralize (do not consolidate) data
  • Database partitioning
  • Database archiving
  • Hierarchical storage management

Data deletion/purging

We would have a tough time removing all of the data, but that's not to say we can't remove some of it, including irrelevant, redundant or historical data that is no longer required. Apart from your database, take a look at your OS (operating system) and see where you have a tendency to waste space. This waste may not constitute a substantial amount in one day, but over a month, or cloned to several other environments without cleanup, miscellaneous data tends to accumulate in an enterprise-wide environment. Suggestions:

  • Keep only what you need from the concurrent request output and logs. Do you really need 60 days online, can you live with 30 or less?
  • Put the concurrent request out/logs onto a cheap storage solution/mount, especially for production instances, where you are typically running faster and more expensive high-availability-type storage. Look for other areas where you can move files to cheaper storage. In some cases, even your archive log destination can be on cheaper storage. Be sure you have some redundancy in place to ensure archive logs are not lost. Can you back up the concurrent request data to other tables so as not to impact daily OLTP performance?
  • Remove, or move, those patch files and logs. Go look at your patch repository and admin/log directory and see how much data you have. You'll be surprised at what you find, especially if you just did a major upgrade. Back up the files and then get rid of them. Caution: Ensure you get a backup of all of those patches you applied. Once the patches are superseded it may be difficult to get them again.
  • Remove the need for redundant online backup storage. Depending on your internal infrastructure and backup procedures and requirements, you may be able to run your backups directly to tape if you are currently running backups to disk and then off to tape. If not, then consider consolidating your backup storage to a single SAN/NAS solution so all servers can share the storage instead of each server having its own storage. If considering this option, please make sure this fits your individual needs, and ensure that you can meet your backup and recovery requirements.
  • Keep an eye on your support logs generated by alerts, traces, apache, jserv and forms processes. If they are not cleaning themselves out and/or are just making copies of themselves after reboots, you may want to get rid of the old ones.
  • Keep what you clone to a minimum and take over only what you need, especially not all of the logs. Reduce your temp and undo space. Typically, you don't need as large a footprint in your cloned environment as you do in a production instance. If you have the luxury, subset or purge the data as part of your clone. If you are partitioning, only take the partitions you need. There are many tools out there that can provide you with the ability to subset (take only a portion of the data) from your production instance, as opposed to all the historical data. HP RIM has a great subset product that can remove as much historical data as you want using parameters that you specify.
  • Keep an eye on temporary and interface tables to make sure they are being cleared out. You may want to develop an alert/trigger to monitor these for you.
  • Monitor your workflow tables and schedule the "Purge Obsolete Workflow Runtime Data." Note: If you have not kept up on the purging of your WF tables, after you have run the purge, you may want to rebuild the tables/indexes, as performance may, initially, be considerably worse. Also take a look at MetaLink Doc ID 277124.1 for good workflow purge items.
  • Take a look at your applications and review what concurrent purge jobs you may want to consider. Many modules have their own purge programs. Be proactive and prevent the data growth from occurring.

Remember, once the data is deleted, short of a recovery, the data is gone and cannot be retrieved. Ensure you have thorough backups, and if you are unsure, make an on-the-spot offline backup just in case. Just make sure you know what you are deleting and that the auditors and users are aware of it.

Add capacity through hardware upgrades

This isn't really a solution -- it reflects that you cannot remove any more data and that you have exhausted all means of controlling/minimizing your data growth. So now what?

Now is when you need to manage with what you have and plan for the future to control where you go. Barring that, at least provide enough time to plan for a new career elsewhere.

  • Perform some type of capacity planning effort to see how fast you are growing and how quickly you will outgrow or outperform what you currently have.
  • Start tracking data growth.
  • Start planning on scaling up with more/faster CPUs and additional RAM, or scaling out, with RAC solutions. You can plan years ahead and stretch the cost to ensure you can keep performance and availability in check.
  • Consider as many of the data deletion/purging activities as possible.

This doesn't solve the problem -- just delays the inevitable from occurring.

Decentralize (do not consolidate) data

Think twice before consolidating your databases into one large database. It is sometimes much easier to manage a few small to medium-sized instances than it is to manage a large growing one.

If one database is more or less static, stable and unlikely to grow and then you consolidate it with another instance that is growing rapidly and more likely to encounter performance and data issues, you have now caused that stable instance to be in a state of disarray as well. You now have twice as many unhappy users. Certainly this does not help in the control of IT resources, costs and overall manageability, but it may be the best solution for your enterprise.

Continue reading Hierarchical storage management for database growth, part 2.

Dig Deeper on Oracle database backup and recovery

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.