Skip to Main Content
  • Questions

  • tablespace point in time recovery for a DB including partitioned tables

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Connor McDonald

Thanks for the question, Anupama.

Asked: October 05, 2016 - 10:41 am UTC

Last updated: October 07, 2016 - 3:25 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I would like to know is it possible to perform tablespace point in time recovery for a database having partitioned tables.
i.e, tables were list partitioned based on one column in each table. Also the partition is moved to its own tablespace.
So that each partition is on its own tablespace. At a glance, the data for the same table is scattered across different tablespaces.
In such a situation is it possible to rollback to a particular tablespace, without rollbacking the data for other tablespace.
Or how we can achieve the restore only for a part of each table?

Regards,
Anupama

and Connor said...

From http://docs.oracle.com/database/121/BRADV/rcmtspit.htm

"You cannot use TSPITR to recover tablespaces containing any of the following objects:

Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) unless all of the underlying or contained objects are in the recovery set. Additionally, if the partitions of a partitioned table are stored in different tablespaces, then you must either drop the table before performing TSPITR or move all the partitions to the same tablespace before performing TSPITR."


I would say you need to need to recover a logically consistent/complete set of tablespaces to an auxiliary database, and then perhaps use datapump to extract what you need.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.