| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: read-only simple snapshot/materialised view refresh
Hi Jared,
Frequency is currently 15 minutes. We'll need to reduce it to about 5 I guess.
ORA-1555 is from the master. As master tables are being updated, snapshot fails with its read consistent image.
Once we get really far behind there's no way we can catch up. It fails with ORA-1555
Ta,
Leng.
> -----Original Message-----
> From: Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]
> Sent: Thursday, 15 January 2004 4:27 am
> To: ORACLE-L_at_fatcity.com
> Cc: Kaing, Leng
> Subject: Re: read-only simple snapshot/materialised view refresh
> Importance: High
>
>
> Leng,
>
> You didn't mention the frequency of the refresh.
>
> I also don't see mention of which database is generating
> the ora-1555 errors.
>
> Jared
>
>
>
>
> "Kaing, Leng" <Leng.Kaing_at_team.telstra.com>
> Sent by: ml-errors_at_fatcity.com
>
> 01/13/2004 09:34 PM
> Please respond to ORACLE-L
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:
> Subject: read-only simple snapshot/materialised view refresh
>
>
>
> Hello everyone,
>
> We've got read-only primary key snapshots in our 8.1.7.4 databases. 1 master. 1 slave. master and slave are on different servers. Snapshots are refreshed by the "FAST" method using dbms_refresh.refresh. However, do to the extremely high transaction rates on our database, we're getting ORA-1555 when trying to refresh the snapshots. The mlog$ tables builds up and the slave just keeps on falling behind. From what I can see, snapshots are refreshed as a single large transaction. So if there are 500K rows in the mlog$ table, all 500K will be processed in one go. There are no intermediate commits.
>
> So my question is: how do you specify a commit point with snapshots? I'm looking for parameters similar to that of the exp and sqlldr utility where you can specify commit points. I've logged an iTAR with Oracle Support and there answer is that it's not possible. ARGH!!
>
> Here's another crazy question is - has anyone updated the dbms_refresh package to add a commit point?
>
> Or, have you tried to interogate the mlog$ and write a PL/SQL procedure to process the rows in there, thereby having your own commit points? mlog$ provides the primary keys and the DML type. So surely it's just a matter of going through each one of the row and applying it to the slave?
>
>
> TIA,
>
> Leng,
>
>
> ----------------------------------------------------------
> Leng Kaing
> Email: leng.kaing_at_team.telstra.com
> Phone: +61-3-9203-7589
> Mobile: +61-417-371-348
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Kaing, Leng
> INET: Leng.Kaing_at_team.telstra.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng INET: Leng.Kaing_at_team.telstra.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jan 14 2004 - 17:29:25 CST
 |
 |