RE: Nologging and partition exchange

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 9 Jun 2010 23:06:38 -0400

I believe Tim very astutely relayed the pros and cons. Note also that if you
are planning partition exchange (a good idea), then all the tablespaces are
"staging" tablespaces, because you don't relocate the segments you exchange
in, and that is part and parcel of why it is so effective. If you do the
swap without putting a hitch for backup in the ETL process, all that means
is that you need to be sure the source data for doing the load again remains
available for a process restart.


The key is to understand what you need to keep and how long you need to keep
it and do a structured walk through of your process to make sure that you
have the pieces available to re-execute the load and can replay any
transactions allowed against the swapped in partition until such time as the
swapped in partition is recoverable.


A key consideration is whether transactions can in fact be done on the
partitions that are swapped in. If they are essentially inert, query only
objects, then skipping the hitch in the ETL before the partition exchange is
nearly risk free. If you're swapping in something that is then heavily
manipulated, then waiting for the backup allows you to avoid having to have
a way to replay the transactions. Some kinds of transactions, such as
formulaic calculations, may be trivially replayable. Other transactions,
especially web form style interactive updates, can be arbitrarily difficult
to replay unless a harness to capture the changes is included in your
application. That last bit is rarely the case.


Tim's notation of "it depends" is no mere consultant gratuity in this case.







From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Tim Gorman
Sent: Friday, June 04, 2010 3:19 PM
To: Paul.Baumgartel@xxxxxxx
Cc: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Nologging and partition exchange



Once NOLOGGING is used, then you accept the risk of losing data and having
to restart in the event of media failure.  There is no way to eliminate this
risk of data loss, so all you can do is minimize the risk of data loss using
backups.  If the requirements to restart and re-run a load are too great,
then the best approach is not to use NOLOGGING.  As the saying goes, "it
depends" and additionally, "it is not as bad as it sounds".

Usually, I find an incremental backup scheduled on the tablespace(s) being
loaded to be sufficient protection, but that's just me.  Bear in mind that
the "old" data in the partition being exchanged (if any) has not changed
status, and it should be recoverable.  It is only the "new" data newly
exchanged into the partition that is at risk for that window of time.

As an example, I wrote a PL/SQL procedure that is used by a customer to
perform migration to lower-tier storage, using exchange partition (of
course).  Procedures in this package handle the entire migration life-cycle
from higher-tier to lower-tier storage, including the final step of removing
the tablespaces containing the now-redundant and no-longer-used "old" data
located on higher-tier storage.  The package will not do this until it
connects to the RMAN recovery catalog database to verify that newly-loaded
(and compressed) data on lower-tier storage has been backed-up, and even
then it schedules the DROP TABLESPACE command to be executed a further 5
days later, just to be certain.

So, this customer's backup process executes independently of this, and it
polls periodically to detect whether UNRECOVERABLE/NOLOGGING activity has
occurred since the last backup of a specific datafile.  If this is so, an
incremental backup is scheduled and performed.  Independent of that, our
package for tiered-storage migration is doing its work, using direct-path
loads (and table compression), exchange partition, and NOLOGGING.  If media
failure occurs, our data is protected until the newly-loaded (and
compressed) data on lower-tier storage is backed up, by the
fully-recoverable "old" set of data on higher-tier storage.  Once we
determine that the newly-loaded data has been backed up, then (and only
then) we consider dropping the older set of uncompressed data.

Hope this helps... 

Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => P.O. Box 630791, Highlands Ranch CO  80163-0791
website    =>
email      => Tim@xxxxxxxxx
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => for info about DUDE...



Other related posts: