RE: DBMS_REFRESH and Redo-Logging

From: Yavor Ivanov <Yavor_Ivanov_at_stemo.bg>
Date: Mon, 16 Feb 2009 16:25:45 +0200
Message-ID: <BD17E2E69E17C64A9684C940EB580E03011198A6E1A5_at_stemodc1.stemo.local>

                You may try refreshing using atomic_refresh:

exec dbms_mview.refresh('TEST_MV',method => 'C', atomic_refresh=>false);

This will do truncate/insert (as it was in 9i) instead of delete/insert. But be careful; there will be a point when you have no data in the MV (after the truncate and before the end of insert)

Regards,
Yavor Ivanov

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jan-Hendrik.Boll_at_dataport.de Sent: Monday, February 16, 2009 2:06 PM
To: oracle-l_at_freelists.org
Subject: DBMS_REFRESH and Redo-Logging

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:25:45 CST