Re: Some more questions on Oracle Dataguard Logical Standby

From: Mark Bole <makbo_at_pacbell.net>
Date: Thu, 26 Aug 2004 14:45:35 GMT
Message-ID: <jImXc.12471$RI4.10014@newssvr29.news.prodigy.com>

Howard J. Rogers wrote:

> Prem K Mehrotra wrote:
>
>

>>We will be using Oracle 9i (9.2.0.5) on a mission critical database
>>which
>>should be up 24x7 running on HP UNIX 11.
>>
>>We want to use logical standby database for reporting. My questions
>>are:
>>1. Is logical standby on 9.2 a stable product. I know there have been
>>many
>>bug fixes in 9.2.0.5. But is logical standby database in 9.2.0.5
>>really ready
>>for mission critical work.

>
>
> My first reaction was that "mission critical" and "9i logical standby" do
> not belong together in the same sentence. But you are aware of the many
> bugs afflicting the implementation, so that at least is something. That
> said, I'd still be inclined to wait until 10g before going logical. But
> you're asking in the right group for anyone with a working 9i logical to
> step forward and let us all know how it went/is going
>
>

>>2. Logical standby requires supplemental logging on primary key,
>>unique index columns. What kind of overhead this supplemental logging
>>will generate because performance is also critical. Also, will no of
>>archived log increase significantly because of additional redo
>>logging.

>
>
> The overhead could be very considerable. It depends on how sensible your
> primary keys are. The sort of multi-column natural keys I've seen would not
> translate happily into minor supplemental logging.
>
>

[...]
> Regards
> HJR
>
>

I have used logical standby successfully for the purpose you describe.

What we do is this: every night, the previous day's redo is SQL-applied, then the apply process is stopped until the next night (under the control of a script). Materialized view (MV) logs were created for various tables on the standby (not the primary), and then a different schema in the standby (also not present in the primary) contains MV's which are fast-refreshed every night and then used for reporting during the day (along with the base tables from the main schema).

Of course, the MV stuff has nothing to do with logical standby, it's just how we set it up to get the most usefulness out of it. You could run SQL apply continuously throughout the day to keep your reporting data closer to what's in the primary, and not use any additional schemas or MV's.

In other words, logical standby lets us easily replicate an entire schema (table objects) from the primary and then build whatever else we   want around it in a database opened for normal business. You could do the same thing with Advanced Replication but this is easier and cleaner overall, and does not require you to create any MV logs in the primary or make any connections to the primary.

Now the warnings and advice:

I would not use the 9iR2 version for disaster recovery (no switchover).   There are bugs that can cause the SQL apply process to come to a screeching halt. There was a bug with temporary tables and supplemental logging in the primary in 9.2.0.4 which forced us to upgrade to 9.2.0.5 (Solaris).

The primary database is small-ish by today's standards, still I did not notice any significant increase in redo log activity because of supplemental logging, so I think that will be the least of your problems. The speed of SQL apply at the standby is a different matter, if that becomes a problem Oracle does have some suggestions on how to speed it up -- be sure to get the Oracle white paper "DataGuard SQL Apply Best Practices" which documents various critical settings and diagnostics not included in the main guide.

Trying to SQL-apply everything, such as functions, packages, grants, and so on caused a number of conflicts (errors) when objects were dropped and re-created on the primary, so I ended up skipping a lot of things that weren't tables. (The nice thing is, you have a lot of control over what you want to apply and what you want to skip).

You can create a logical standby without taking an outage using the additional documentation from Oracle on how to create one from a hot backup (but you do have to quiesce your primary for a moment -- I think this requirement goes away in 10g).

I turned off archivelog mode in the standby, didn't seem any need for it in my scenario (if it died, it would just be re-created, not restored from a backup). (Note: in the standby, I set standby_archive_dest to a different location than log_archive_dest_1, makes things less confusing).

I stress-tested this by completely dropping the schema in the primary and re-importing it. Speed aside, it works (again, I am skipping the apply of many things that aren't tables).

We are running both the primary and logical standby in a two-node Veritas cluster, 24x7, which maximizes the return from our investment in Oracle licenses and production system support and maintenance. Again, this is for reporting, not disaster recovery.

--Mark Bole Received on Thu Aug 26 2004 - 09:45:35 CDT