RE: Nologging and partition exchange

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 4 Jun 2010 11:15:17 -0400
Message-ID: <FE03883BEC5848B3859FB09621C02B25_at_rsiz.com>

And if. (meaning I agree completely with what is written so far.)  

If you consider the partitioned object a live production object and the preparatory table loaded nologging to be a "staging" object, then preserving the recoverability of the staging object after loading but before the exchange is a practice to consider. When the source data for the "staging" table remains available at least through the preservation of the recoverability of the staging object, these leaves you with no point in time lacking a reasonable reprocessing workflow to follow when something goes bump. And at no time do you give access to users to an object that is not recoverable. While this does inject a lag between loading the staging object and making the contained data part of the production image, it does maintain the nologging advantage of minimizing the redo stream. Overall backing up the relevant incremental routinely produces less concurrency conflict amongst your hardware resources than the affect of injecting the extra redo.  

This may or may not fit with your real world requirements, but it is a practice worth consideration.    

mwf

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Friday, June 04, 2010 9:54 AM
To: Paul.Baumgartel_at_ubs.com
Cc: oracle-l_at_freelists.org
Subject: Re: Nologging and partition exchange  

Paul,

After any NOLOGGING operation, please perform an RMAN incremental level=1 backup as soon as possible. Of course, level=0 or full is OK too, but the minimum is a level=1. The time period between the completion of the load and completion of the backup is a window of risk of possible data loss in the event of media failure or corruption, and that window of risk is the tradeoff (i.e. speed vs recoverability) that must be consciously accepted using for the NOLOGGING operation.

Hope this helps...

Tim Gorman

consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    => http://www.EvDBT.com/
email      => Tim_at_EvDBT.com
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...

Paul.Baumgartel_at_ubs.com wrote:  

When a table is loaded with NOLOGGING and /*+ APPEND */ hint, then exchanged with a table partition, that partition remains unrecoverable, correct?

If you use this approach, how do you make the partition recoverable?

Paul Baumgartel
UBS AG
IB Accounting Solutions
400 Atlantic Street
Stamford, CT 06904

203.719.4368

paul.baumgartel_at_ubs.com
 <file:///\\www.ubs.com> www.ubs.com  

• http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jun 04 2010 - 10:15:17 CDT