RE: Nologging and partition exchange
Date: Wed, 9 Jun 2010 23:06:38 -0400
Message-ID: <C41F7E89E760411BBC7A8C83D7356785_at_rsiz.com>
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.
Regards,
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 3:19 PM
To: Paul.Baumgartel_at_ubs.com
Cc: mwf_at_rsiz.com; oracle-l_at_freelists.org
Subject: Re: Nologging and partition exchange
Paul,
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 => 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...
<snip>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 09 2010 - 22:06:38 CDT