Re: DBMS_REFRESH and Redo-Logging
Date: Mon, 16 Feb 2009 08:05:43 -0600
I have you changed the default behavior of the refresh? In 10g, it will do a "DELETE" and then an INSERT unless you turn atomic refresh off. I belive you'll have to modify the job to use dbms_mview.refresh and pass "atomic_refresh => FALSE".
It may be that all your redo is coming from the DELETE and the INSERT is nologging. A 10046 trace when calling the refresh should be able to verify this.
"Next to doing a good job yourself,
the greatest joy is in having someone else do a first-class job under your direction."
- William Feather
On Mon, Feb 16, 2009 at 7:29 AM, <Jan-Hendrik.Boll_at_dataport.de> wrote:
> Actually there are no indexes lying on the view.
> *Von:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> *Im Auftrag von *Eric Buddelmeijer
> *Gesendet:* Montag, 16. Februar 2009 13:17
> *An:* Boll, Jan-Hendrik
> *Cc:* oracle-l_at_freelists.org
> *Betreff:* Re: DBMS_REFRESH and Redo-Logging
> You can reduce the amount of redo generation considerably by dropping any
> indexes before the refresh and recreate them (nologging) after the refresh.
> If you have any indexes on the MV at all and have not already done this.
> Kind regards,
> 2009/2/16 <Jan-Hendrik.Boll_at_dataport.de>
>> Hi folks,
>> i've got a mv created with nologging-clause on 10204. This view
>> is regularly refreshed *completly *using dbms_refresh.refresh. During the
>> refresh a plenty of archive-logs are generated, flooding our
>> As result oracle deactivates the full destionation forcing the database to
>> ARCH: Archival stopped, error occurred. Will continue retrying
>> ORACLE Instance dada - Archival Error
>> ORA-16014: log 1 sequence# 49531 not archived, no available destinations
>> After that the archiver is pausing for 2 or 3 minutes.
>> As i know that there is no way to prevent redo-log generation, do you know
>> a workaround for such a scenario? Expect increasing file-systemspace and
>> deactivating archivelog mode. :-)
>> And secondly: how am i able to prevent oracle from waiting for the
>> archiver. Is there any way to reenable the deacitvated
>> regards, jan