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