RE: Logical Standby Questions

We had a logical production db at 10R1. After a few months we reverted back to physical standby exactly because of failing DML w/o appearent reason. It was typical that DML would fail for a highly update intensive though really small(1000's of records) table. Yet another source of failures could have been attributed to pl/sql invalidation on the primary site.

Brgds, Laimis N.  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark Strickland Sent: 17. júlí 2006 16:52
To: oracle-l_at_freelists.org
Subject: Re: Logical Standby Questions

Oracle Support verified that, even though the failed update statements as reported in v$logstdby_events include rowid in the WHERE clauses, SQL Apply drops that clause before it applies the statement. That's what I would have expected but seeing rowid in the SQL statement threw me off, so I thought that maybe SQL Apply actually does use rowid. I didn't think it did. So, my theory that the logical standby wasn't handling row migration properly was probably off-base.

Oracle Support has little help so far. I'm going to try to replicate the problem in a test environment. This is alarming. We're not yet reliant on the logical standby for reporting but we will be relying on it sometime in the next few months. I was under the impression that Logical Standby in 10g was ready for prime-time. I'm starting to question that assumption.

On the bright side, I was happy that the re-instantiation of a million-row, 450-Mb table only took a few minutes. Looks like the INSTANTIATE_TABLE procedure uses Data Pump, rather than just a straight select over the database link.

Regards,
Mark Strickland

On 7/15/06, Mark Strickland <strickland.mark_at_gmail.com> wrote:
> I figured out that I had to grant LOGSTDBY_ADMINISTRATOR to the schema
> owner in the primary database. I granted it only to the schema owner
> in the logical standby. I did the grant and was able to
> re-instantiate my test table.
>
> Regarding the other issues with failed transactions, I found document
> #5256179 which seems to match our problem but I didn't see any
> evidence that the customer got the problem resolved. I'm suspecting
> that the logical standby isn't handling row migration very well. In
> the failed update transactions, the rowids don't match between the
> primary and the logical standby for the given row (tables have primary
> keys).
>
> Mark
>
>
> On 7/15/06, Mark Strickland <strickland.mark_at_gmail.com> wrote:
> > 10.1.0.3 RAC with Data Guard on Solaris 9.
> >
> > Last week, we added columns to a couple of tables in Production and
> > set the column values to zero. All applications had been shut down.
> > No other DML was possible. When that DDL and DML reached the
> > logical standby it caused a major hairball in the standby. I've
> > opened an SR of course and done my due diligence by searching
> > Metalink and Google and the docs. Not coming up with any answers.
> > SQL Apply continued to fail after encountering failed updates to one
> > of the tables that got a new column. SQL Apply kept failing even if
> > I restarted it with "skip failed transaction". Finally, last night,
> > I set that table to be skipped and restarted SQL Apply and after a
> > few more hiccups with other tables and a few more restarts, SQL
> > Apply caught up with the primary which was 400 logs ahead by end of
> > day yesterday. I now need to re-instantiate the table that was
> > skipped. I'm testing the process in a 10.1.0.5 test environment
> > and, when I issue
> >
> > DBMS_LOGSTDBY.INSTANTIATE_TABLE,
> >
> > I get
> >
> > ORA-31631: privileges are requiried
> > ORA-06512: at "SYS.DBMS_LOGSTDBY", line 392
> > ORA-06512: at line 1
> >
> > I traced the session and didn't find a clear explanation in the
> > trace file. I did grant LOGSTDBY_ADMINISTRATOR to the schema owner
> > and even granted DBA and SYSDBA to the account. Has anyone
> > encountered a problem with INSTANTIATE_TABLE?
> >
> > Regards,
> > Mark Strickland
> > Next Online Technologies
> > Seattle, WA
> >
>

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


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l