WG: DBMS_REFRESH and Redo-Logging

From: <Jan-Hendrik.Boll_at_dataport.de>
Date: Mon, 16 Feb 2009 14:29:12 +0100
Message-ID: <0FCF70745FD57F4F8259112EC5E800A50138323E_at_wscxpr18.fhhnet.stadt.hamburg.de>

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 - 07:29:12 CST