Re: DBMS_REFRESH and Redo-Logging

From: Bradd Piontek <piontekdd_at_gmail.com>
Date: Mon, 16 Feb 2009 08:05:43 -0600
Message-ID: <e9569ef30902160605g577d702cn40ea1fe93a7382b4_at_mail.gmail.com>



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.

Bradd Piontek
  "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
>
> Jan,
>
> 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,
> Eric.
>
> 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
>> archive-destination.
>> As result oracle deactivates the full destionation forcing the database to
>> halt.
>>
>> Error:
>> 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
>> archive_destionations?
>>
>> regards, jan
>>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 16 2009 - 08:05:43 CST

Original text of this message