Re: delete one tablespace from all backups

This was just an innocent question from a manager about whether it was possible. FYI, the context wasn't about classified data - but rather about application consolidation into a single database. There may be a requirement for a particular application that part of the decommission process is completely removing all data from the infrastructure. I had already given my official answer: if you want the ability to completely remove a particular set of data and all copies of the data, then that data needs to be in its own database and can't be consolidated in this way.

I just posted here because it got my geeky side thinking about the technical angle, and I just wondered how close we could get. :)

Kevin's TDE idea is an interesting suggestion - however I don't think it would actually work. The TDE tablespace keys are stored in the header of the operating system file(s) that contain the tablespaces, so it's chicken-and-egg... you can't lose the keys unless you lose all backups of the datafiles. The tablespace keys are encrypted with the Oracle Advanced Security TDE master encryption key - and I'm pretty sure that you can only have a single master encryption key per database. So it's all-or-nothing for the database when it comes to TDE.

http://docs.oracle.com/database/121/ASOAG/asotrans.htm#ASOAG10139

Mark's suggestion about restoring and re-creating all backups is also interesting. Trying to decode, filter, and re-create redo log files... sounds very precarious! And the undo part may simply be impossible... but of course this is Mark's point. :) Really interesting line of thought though - thanks Mark. And definitely confirms the impracticability of it.

Two additional points:

1. Even if all full & incremental backups of the tablespace were removed, the rman restore/recover will still work perfectly fine if the tablespace was created within the retention period and archivelogs are still available. I've seen this - RMAN will simply keep failing back to older backups until it gets to the tablespace creation, at which point it will create empty datafiles and start rolling forward logs. Automatically. Quite nice for a DBA, and pretty much makes my curious manager's request technically impossible.
2. Connecting to another recent thread - I'm not sure of the implications for PDB/CDB but with shared redo and undo it's quite possible that the same problem applies. If an application has the requirement that all copies of data can be removed on decommission, then that may make the application ineligible for multi-tenant consolidation too. That's an angle on multi-tenant that I hadn't considered before!

-Jeremy

--
http://about.me/jeremy_schneider

On Thu, Sep 25, 2014 at 1:57 PM, Mark W. Farnham <mwf_at_rsiz.com> 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'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://www.freelists.org/webpage/oracle-l