I just don't see how it's realistic to "restore each backup, and re-do the backup without the undesired tablespace, and then delete the old backups" - I share your pessimism.
But as you point out, if you don't already have TDE (or something equivalent) in place for all the past backups, you can't implement the "lose the keys" approach for those backups. You can implement it now so that in the future it will be easier, and in the meantime figure out when it's safe to fully delete the oldest backups, and eventually you will no longer have any unencrypted backups. But that solves the problem for some point in the future, not for today...
-KJ -- Kevin Jernigan Senior Director Product Management Advanced Compression, Hybrid Columnar Compression (HCC), Database File System (DBFS), SecureFiles, Database Smart Flash Cache, Total Recall, Database Resource Manager (DBRM), Direct NFS Client (dNFS), Continuous Query Notification (CQN), Index Organized Tables (IOT), Information Lifecycle Management (ILM) +1-650-607-0392 (o) +1-415-710-8828 (m) On 9/25/14, 10:57 AM, Mark W. Farnham wrote:
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âEUR^(TM)m not sure whether that would create some checksum failure on the file. Also a tremendous amount of work.I canâEUR^(TM)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âEUR^(TM)s cool idea, which is sounding even more cool by the minute: you donâEUR^(TM)t have to enumerate the copies, you just lose the ability to decode them. Of course quantum computing is coming alongâEUR¦mwf*From:*oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Kevin Jernigan*Sent:* Thursday, September 25, 2014 10:28 AM *To:* jeremy.schneider@xxxxxxxxxxxxxx *Cc:* Oracle-L *Subject:* Re: delete one tablespace from all backupsIf 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 iPhoneOn Sep 25, 2014, at 6:12 AM, Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx <mailto:jeremy.schneider@xxxxxxxxxxxxxx>> 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