Recovery Design part 4 - You need more than on ...

There was a good reaction to part 3 with sensible comments from people who've obviously 'been there, seen it, done it, bought the t-shirt' and handled their share of tricky system failures, so thanks to all. The comments are interesting reading for other perspectives and ideas.

Although the sub-title to this part is 'you need more than one database', I hope that the last part already convinced you that, if you really want to protect your data from the widest possible range of failures with the fastest possible recovery, some form of replication is a 'very good idea' (TM). I'll divide how you decide what approach to take into two groups of factors.

Technology

This is the easy bit. What particular technology you use is a matter for you, but there are options. I've worked at sites which used one or more of the following. (I haven't included the use of Oracle Streams here because I haven't used it personally, but anyone who has or does should feel free to comment.)

• User-configured Standby Databases. I include these for historical reasons, because it was the first approach I used on a 7.0.15 database. I vaguely recall that Oracle offered full instructions on Metalink for maintaining a Standby Database by shipping archived redo log files, but didn't officially support it. Nevertheless, it worked well for us although we only ever used the Standby for disaster recovery and as the source of the backups and didn't use it for user reporting.

• Oracle Dataguard. The modern name for a Standby Database and I tend to forget to use it because it sounds to me like a marketing tag for a basic concept. However, I must admit that the modern implementation is much improved and easier to use and people tend to be more familiar with the name 'Dataguard' these days, so I'm trying to use it

• Quest's Shareplex. I worked at one site that used this for everything - dozens of databases worldwide, some of them extremely active. We did run into one or two problems with it but my personal hunch is that it was no worse or better than Oracle's technology, it was just that this particular site had the hots for standby databases. It was used so heavily that we would have probably run into some problems whatever we used. (Although I know a few ex-colleagues who might want to leave a few curses in the comments!)

• Extra disk mirrors. By this I mean something like synchronising disks on a SAN so that updates to one set of disks are propagated to another set, either syncronously or asynchronously. We use a lot of this at my current site, but there are maximum distances if you want synchronous updates and unless you have the technology and the storage administrators who understand it, this won't be an option for many people.

Speaking of synchronous and asynchronous updates brings me on to the next area to consider.

Update lag (or currency)

There is a tension at the centre of any disaster recovery-related replication design.

On the one hand, we want the copy or standby database to be the most up-to-date copy possible because if we need to use it, we'll have less redo information to apply to bring it up to date, which will improve recovery time and we'd ideally like to implement a no data loss solution. i.e. Unless a transaction commits on the Standby, it won't commit on the Primary so the databases should always be identical. This is what Data Guard's Maximum Protection mode offers and it sounds like another excellent idea.

If you ask the users how much down-time they can stand and how much data they can afford to lose (and you absolutely have to ask), then their first answer will nearly always be - no down-time and no data loss.

On the other hand, that could cause a number of problems.

1. The Primary is now dependant on the Standby being operational. If the Standby is down, Oracle has to shutdown the primary or the Standby wouldn't be synchronised any more. That doesn't sound promising to me because it makes everything more fragile and failure-prone.
2. In the event of someone screwing up on the Primary, you can guarantee that the same error is going to exist on the Standby - that's the way it works!

The worst news is that I'm not going to give you any answers here - just more questions. I'm like that

In the old days there was a built in time lag of anything that was in the current online redo log file, plus how long it took to ship the archived copy over to the standby server and apply it. These days you have more options and need to think about them. For example, a time lag of several hours would give you time to notice someone screwed something up on the primary before allowing it to be applied to the standby but, then again, maybe flashback features are more useful for that kind of thing?

I think the next blog will probably be the last - wrapping up this mini-series, forming some unsatisfactory conclusions and offering some very satisfactory sources of further information.