Just this week a few customers asked me the same question. “Should we back up our indexes?” As tradition warrants it, I answered with “it depends”. The choice to back up your indexes or not, should be a part of your overall recovery strategy.
The backup strategy of your table spaces and indexes should be recovery SLA based. Do you have a clearly defined recovery target? Is it the same for all tables? Probably not. Most systems have low, mid and high priority tables; the time these tables can be offline is probably very different. The expected recovery times should be explicitly defined in the company’s recovery SLA.
The foundation of any back up strategy should be your recovery SLA, and if not, you might be sub- optimal by taking too many backups on some tables and not enough on others. If the SLA for a low priority table is “needs to be back online in 24 hours,” and the table doesn’t change that often, why would you take daily full image copies?
On the other hand, when you look at your most important tables, are they big ? Are they frequently updated? How long does your business allow them to be offline? Let’s say the answers to these three questions are “yes, they are big; yes, they are updated all the time; they have to be recovered within the hour.” Now you have something to work with.
As a safety measure, I count 15 minutes of any recovery to be “think time” a.k.a “panic and regroup time.” That leaves me 45 minutes to do the actual recovery, which has four phases: the restore phase, the log apply phase, the rebuild index phase and the verify phase.
The time it takes to restore the table’s full image copy and apply the incremental image copies to it depends on the size and the type of media you used during the back up. Tape will be significantly slower than disk. The tape needs a mount and the restore happens sequentially rather than in parallel.
How many log entries need to be processed? A daily backup is probably not enough for your most updated, large, critical data, when you have a 1-hour recovery SLA. Most critical tables are updated quite a bit. Do you archive your logs to tape or do you have the needed log records still on disk? It will make a significant difference. Considering that you only have 45 minutes, can you get these first 2 phases completed in under 30 minutes? If not, you might want to consider taking online backups during your business hours, based on real-time statistics so you can avoid reading an entire day of log processing during a recovery.
With 15 minutes on the clock, you have to rebuild your indexes. Is this possible on your biggest indexes? A rebuild of huge non-partitioned index in 15 minutes? Very unlikely, think how long it takes to build your biggest index - this might use more than an hour by itself. This is the time to consider taking regular backups of your indexes and recover rather than rebuild.
The first step of any recovery strategy should be to define the recovery strategy. Once this has been defined, we should verify if our backup strategies are adequate and adapt if needed. So to answer the original question “should we back up our indexes?” It does not depend: it’s as simple as asking “Are you meeting your recovery SLA?”
Kurt Struyf - Suadasoft
IDUG Content Committee leader