RE: tablespace point in time recovery for not self-contained tablespaces

IF they want the version from a year ago (not rolled forward), then you *might* have what you want already “because we anspshot the database and start it in another server” and then the question is whether the individual “as was” objects (table in this case) are small enough to reasonably move (by your choice of method, including CTAS to a truly transportable tablespace with just the objects you need and probably created just a little bigger than the objects to be moved. IF any of the objects to be recovered old and moved still exist in the destination, CTAS is nice because you inherently rename the table(s) on the way. Whether THIS is practical varies by the size of the objects to be moved as opposed to the size of the database.  

This is a much trickier shell game if they want some (perhaps all except a DROP) roll forward from the snapshot you have.  

If you have all the redo required AND you can a place to restore an old recoverable physical backup of SYSTEM, SYSAUX, and undo, then you should be able to do a point in time recovery at the database level, leaving out all the big tablespaces. Of course you’ll need the old version of the files underlying the tablespaces from a year ago. (Including SYSTEM, SYSAUX, undo, and any other tablespaces that might be needed to start the database). You don’t need all the tablespaces, because you are doing a partial recovery as opposed to a transport.  

After you do this to get the table back to the point in time you want, you will still have to copy the table you need back to the original database (either export-import back to your desired database or CTAS to a truly self-contained tablespace followed by TTS, or some other way).  

This is the old fashioned way. Until Oracle working with some customers realized that some integrity support at the database level made TTS possible, this is the shell game we always did.  

IF your SYSTEM, SYSAUX, UNDO, and any other tablespaces required to open the database are small enough to reasonably backup and restore it is tempting to do this in place. IF you attempt that make sure you get copies of your online redo logs before you do the partial database restore to a point in time. When I used to do this often (before it was affordable to routinely duplex disk drives), I usually did a start-up mount rename to open and recover the old database version. In fact you could do this to make a point in time (re-named) copy of your entire database for query off-load. This is the clunky progenitor of what has evolved into dataguard and active dataguard (notably not being read only but also not being up to date). This is all just recovery at the bottom line. With rename (and assuming sufficient horsepower) the rename makes this possible to do on the same host or by cloning on your recovery box after pausing recovery, being careful, and doing a start up resetlogs rename. For when you want a frozen in time copy, this is an interesting way to get a second isolated instance space and processes to execute the point in time queries without the overhead of RAC and/or point in time queries. Whether this is cost effective versus modern methods such as Delphix is of course interesting. As Mladen is quick to point out, copying your entire database is often not a reasonable operation any more just because of scale.  

IF the old version is what they need, this skips all the shell game required to do a temporary database level roll forward as recovery rather than as transport.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ls Cheng Sent: Tuesday, March 07, 2017 3:05 PM
To: Oracle Mailinglist
Subject: tablespace point in time recovery for not self-contained tablespaces  

Hi

Running 11.2.0.4 RAC in Linux x86-64.

One of my users asked us to restore a table from a year ago in a 8TB database. The tablespace where the table is stored hast 70 tables and around 120 indexes and a few LOB, the table itself has parents in other tablespaces and child in other tablespaces. The tablespace is not a self contained tablespace.

We would like to restore the table using tablespace point in time recovery and according to the doc

https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#BRADV009

We can make the tablespace self contained before run TSPITR which is what we did, we could do this because we anspshot the database and start it in another server. So far so good TRANSPORT_SET_VIOLATIONS is empty. But during TSPITR when it restored system sysaux, undo and the tablespace one of following step is EXPORT the tablespace, make it TTS, it failed

   EXPDP> FLASHBACK automatically enabled to preserve database integrity.
   EXPDP> Starting "SYS"."TSPITR_EXP_pfbm":  
   EXPDP> ORA-39123: Data Pump transportable tablespace job aborted

It is understandable because the backup is from 1 yea ago and obvisouly the backup contains all the relations, indexes and lobs we have removed to make TSPITR working. So this is sort of dead loop, we remove eveyrthing to make the current tablespace self-contaned but the backup it restores isnt so at the end of day we probably need to restore the entire 8TB database to export a table.

So my question is. Does TSPITR work only for real SELF-CONTAINED tablespaces? That is it is always self contained since the backups until now. In this experience it seems to me that TSPITR is not very useful at all. Or is there any workaround?

Thanks

--
http://www.freelists.org/webpage/oracle-l