Re: Logical Standby

I'm using Logical Standby with 10.1.0.5 (RAC, Solaris 5.9, Veritas, Hitachi SAN). I've hardly pushed the standby so I can't speak to performance implications. For me, it has been quite stable and there's rarely a SQL Apply crash. However, you have to be careful about large transactions in the primary, anything that will insert/update/delete more than, say, 100,000 rows. The redo stream contains row-level DML statements that correspond to the single DML executed in the primary. Over 100,000 such statements and SQL Apply will eventually slow to a crawl. A DML statement that affects 1,000,000 rows would likely finish sometime during the Obama Administration. His second term. So, if there are large transactions that have to be performed in production (add new column to large table, set column to default value for all rows, add not null constraint), we wrap them in a PL/SQL block using a cursor for loop with a commit every 10,000 rows. Works fine. The Logical Standby keeps right up. However, I can't 100% rely on folks to do large transactions correctly, so I can't 100% rely on the Logical Standby for any mission-critical reporting, ie, anything that couldn't wait 24 hours while I refresh a table or the entire database. I've had an SR open for 9 months and a bug has been filed. I did test this in 10.2.0.2 and the behavior is the same.

Regards,
Mark Strickland
Seattle, WA

On 5/7/07, Smith, Steven K - MSHA <Smith.Steven_at_dol.gov> wrote:
>
> I'm researching data guard with physical and logical standby databases.
>
> We currently are running standby database in 9.2 and are investigating
> options that data guard will give us after upgrading to 10.2.
>
> Question I have is - how reliable and what issues have people with
> experience using logical standby? I see that there advantages would be
> availability to report and view data with possibly additional reporting
> indexes on the 'standby' server.. I understand that having that available
> in physical mode is an option, but updates stop while the database is open
> in 'read only' mode.
>
> What is the performance hit? Assuming maximum performance mode. I don't
> trust the network to the standby site to recommend Max Protection or Max
> availability.
>
> Ongoing maintenance? I know with our current standby database, the
> maintenance on the standby site is minimal. The setup is just pretty
> reliable as long as the network is available and not saturated.
>
> Logical - large(r) bandwidth requirements?
>
> I am currently reading the manuals so anything that I'll 'get to' please
> don't tell me to RTM. I'm looking for more actual experience and lessons
> learned.
>
> Thanks
>
> Steve Smith
>
> Desk: 303-231-5499
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l