RE: delete one tablespace from all backups
Date: Thu, 25 Sep 2014 13:57:41 -0400
Message-ID: <130a01cfd8ea$34b640e0$9e22c2a0$_at_rsiz.com>
That is a pretty cool idea. It probably is a useful practice, especially if separation of function type things are aligned with tablespaces.
But I suspect Jeremy would need a time machine for that approach.
Jeremy, are you discarding IRS emails again?
Now, supposing you could get your hands on all the backups you could delete the files for those tablespaces. (This would be a non-trivial amount of work. I am pessimistic about success of ever finding all copies of anything other than something someone accidentally deleted and now wants back.)
For redo, if you decoded the vector address and length stuff I *think* you could filter out the data block addresses for everything in a redo file, but I’m not sure whether that would create some checksum failure on the file. Also a tremendous amount of work.
I can’t think how to squish out undo that is pending without destroying those backups.
So I think the real answer would be to restore each of the referenced backups, back them up minus the tablespace you want to expunge from the spacetime continuum, and then destroy the original backups entirely. (Once again, see my message about pessimism.)
Which types of backups do you have, which could include any of old style physical backups, RMAN backups, and several different types of snapshot backups? A general answer is sounding nearly impossible to me.
Which brings us back to Kevin’s cool idea, which is sounding even more cool by the minute: you don’t have to enumerate the copies, you just lose the ability to decode them. Of course quantum computing is coming along…
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kevin Jernigan
Sent: Thursday, September 25, 2014 10:28 AM
To: jeremy.schneider_at_ardentperf.com
Cc: Oracle-L
Subject: Re: delete one tablespace from all backups
If you use TDE, and have a different encryption key for each tablespace, then you can "lose" the key for the tablespace you want to drop.
-KJ
Sent from my iPhone
On Sep 25, 2014, at 6:12 AM, Jeremy Schneider <jeremy.schneider_at_ardentperf.com> wrote:
someone asked me recently if it's possible to completely remove the data in one tablespace from all historical backups of a database. my knee-jerk response was simply "no" - thinking that even if you had the tablespace backups in their own backupsets, you couldn't remove data from undo and redo streams.
nonetheless I'm curious if anyone else on the list has ever thought about this and what you've come up with. if there was a business requirement to do this, then how close could you come?
-Jeremy
--
<http://about.me/jeremy_schneider> http://about.me/jeremy_schneider
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 25 2014 - 19:57:41 CEST