Re: Some more questions on Oracle Dataguard Logical Standby

Mark Bole <makbo_at_pacbell.net> wrote in message news:<jImXc.12471$RI4.10014_at_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

Mark, Howard, Hans:

Thanks a lot for your responses.

Prem Received on Thu Aug 26 2004 - 17:06:47 CDT