RE: Logical Standby Questions

  • From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 17 Jul 2006 17:10:43 -0000

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 

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 

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:
> > 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 test environment 
> > and, when I issue
> >
> >
> > 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
> >


Other related posts: