1

I have 2 tablespaces which I want to backup when they are in a consistent state (let's say T0). When problems occur (T1), I want to be able to restore both tablespaces to their state at T0 without recovering all changes that occured between T0 and T1 (I want to ignore them all).
What I could do so far is backing up and recovering tablespaces (with changes that occured after the backup, which is not what I want).
I am using Oracle 11gR2 XE and RMAN.
I am looking for a solution compatible with Oracle 10 g and above.
Thank you for your time and help.

user3488996
  • 1,339
  • 2
  • 10
  • 13
  • how is T0 defined? Is it a particular time? Or the state before a recurrent operation? Or some arbitrary moment in the life of your database? Is this an ongoing requirement or a one-off? – APC May 26 '14 at 21:49
  • T0 is the time I backup the DB before launching an operation which I am not sure it will succed. To be more clear, I have an automated process which transform a class diagram into SQL queries and then execute those generated queries. Those queries should be executed within a transaction : either they succed or are cancelled. The problem is that some of the queries are of type DDL which autocommit in oracle. I want to backup my tablespaces, then launch the queries, and if they fail I will have to cancel all changes (DDLs) by restoring the backup (the restoring should be automated too). – user3488996 May 26 '14 at 22:58

2 Answers2

0

I think the best way to do that is transportable tablespace (http://www.oracle-base.com/articles/misc/transportable-tablespaces.php). You'll transport your tablespace in an empty database before your operation. After you have to do a full backup on your new database. If a problem occur during your migration you could do the inverse operation.

Of course, test this solution before!

eliatou
  • 744
  • 5
  • 12
  • I've just read the demo you posted. This solution doesn't suit my case because It needs a second database which is not always possible to have nor the time to configure it. – user3488996 May 27 '14 at 08:13
  • It's very easy to create an empty database. You could find many scripts on internet and after it will be easy to do. – eliatou May 27 '14 at 09:40
  • you are right but I want to minimise the required configuration. it's not a problem in the test enviromnent but I am thinking about the production enviromnent. – user3488996 May 27 '14 at 09:54
0

What about TABLESPACE POINT IN TIME recovery? Would it suit your needs?

PS: It has some prerequisites like NO FKs into tables in other tablespaces. And maybe it is not allowed in XE releases.

ibre5041
  • 4,903
  • 1
  • 20
  • 35