Re: How keep 8i standby no-more-than 30 minutes behind?

"Guy D. Kuhn" wrote:
>
> I fond on MetaLink that the interpretation of log_checkpoint_timeout and
> log_checkpoint_interval was changed for 8i. The documentation,
> appearently, was not changed. The new interpretation (.. lag the tail
> of the log...) is a subtlety the eludes me. The bottom line is, how do
> I ensure my standby is not more than 30 minutes behind the primary?

30 minutes behind the primary what? Behind the last committed transaction on the primary? Behind the last checkpoint on the primary? Behind the last log switch?

> Setting log_checkpoint_timeout does cause a checkpoint at
> no-greater-than 30 minutes.

In 8i, the standby database is fed by archived redo logs. Assuming you're in managed recovery mode (where the archives are automatically received and applied), you're in a constant mode of recovery. But you're always behind the primary by the contents of the primary's current online redo, plus the contents of any redo logs that the primary hasn't finished archiving. So you can checkpoint the primary until you're blue in the face, but you'll won't see the primary data in the standby until the primary archives a log and the standby receives *and* applies it.

So the notion of being a certain amount of time behind some sort of event on the primary is impossible for Oracle to guarantee. But so is the notion of being behind the primary by a certain amount of data - even if you resize your online redo's on the primary to a small amount of data, you can't have Oracle *guarantee* that those archives will be applied to the standby fast enough to keep up with the primary's log switching.

The best you can hope for on the standby is 1) to only use 2 online redo log groups on the primary so that the primary couldn't get more than one log ahead of its archiver process and 2) to set the log_archive_dest_n parameter for the standby to MANDATORY, guaranteeing delivery of the archive. Both of these can have major impacts on the primary that you should understand before implementing. But even if you do #1 and #2 all you're guaranteed is that the standby has *received* the log just behind the current log. But Oracle hasn't guaranteed that the standby actually *applied* it in any sort of time frame. Maybe your standby box is slower, and can't keep up applying redo as fast as the primary can generate it. Who knows. But once the primary Oracle delivers that archive, all bets are off - the primary Oracle stops caring about it.

Finally, don't forget to consider nologging transactions. If they occur on the primary, your standby will need special attention to get synched up again. Same goes for certain DDL operations, all of which can make the standby diverge temporarily from the primary.

All of this is a really long-winded way of saying you should upgrade to 9i Dataguard if you're serious about keeping the standby in sync with the primary. Dataguard introduces the notion of remote online redo log groups which can be configured to guarantee no data loss and no data divergence.

Regards,
Sean Received on Tue Mar 26 2002 - 22:29:41 CST