Re: read-only simple snapshot/materialised view refresh
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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/13/2004 09:34 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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. -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED] Sent: Thursday, 15 January 2004 4:27 am To: [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/13/2004 09:34 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
