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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Mark Strickland
Sent: 17. júlí 2006 16:52
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx> 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

Other related posts: