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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
ORA-39187: The transportable set is not self-contained, violation list is
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
