Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Need help!

I've read Database Backup and Recovery User's Guide 11g but I can't uderstand why we need auxiliary instance to perform tspitr. We can do complete recovery of tablespace within same instance, why it's impossible for tspitr?

Here Hemant K Chitale says: "You cannot recover a tablespace to an SCN that is lower than the rest of the database.". Can anyone help me to uderstand why?

In this message is shown that when we turn tablespace offline, its SCN becomes lower than SCN of other tablespaces after a while, and when we turn it online "it will not apply redo it will just adopt current SCN".

So, why database cannot open recovered until time datafile adopting it's SCN to current? It'so due to technical aspects of OracleDB or there is another reason?

share|improve this question
up vote 1 down vote accepted

TL;DR to be able to restore data+undo without overwriting the entire current undo.

When you perform RMAN online backup, some transactions are in the middle of processing, they did change something but not yet performed their COMMIT. Transactional model of work ("atomicity") requires those transactions to be rolled back (completely undone) on recovery. Oracle engine does this automatically. Oracle engine does not allow onlining tablespace if this cannot be done, to protect user from seeing possibly inconsistent data. So do transactions save somehow the "consistent" data before they change it? Yes, in the undo tablespace (or a "rollback segment"). This is why, recovering a tablespace, you cannot "forget" it had undo, you need to recover undo from the same point in time (same SCN).

To sum up, Oracle engine needs to have undo data synchronized with from the same point in time as the main data.

Undo is, usually, put into a single tablespace despite it is being a mix of pieces of "consistent" information from many different data tablespaces. This is why you TSPITR to an auxiliary instance - to be able to restore data+undo without overwriting the entire current undo.

Once auxiliary is online, the auxiliary's data has been made consistent and auxiliary's undo is no longer needed.

share|improve this answer
how undo is synchronized when recovered tablespace is imported from auxiliary instance? – K.Esaulov Apr 24 '13 at 10:54
By "synchronized" I simply mean "to be from the same point in time" not to have any calculation/action/movement performed. – kubanczyk Apr 24 '13 at 14:38
when we import tablespace undo of target database is not "synchronized" with that tablespace? Or I'm wrong? – K.Esaulov Apr 25 '13 at 7:33
We're not on the same page. You asked "why?" and I answered "because X". Import (imp, impdp) has nothing to do with undo, and I didn't imply you need to tinker with it. Import has simply nothing to do with your question. – kubanczyk Apr 25 '13 at 11:43
Hypothetically, yes, I guess so. But Oracle doesn't give you possibility to have different undo tablespace for each data tablespace. – kubanczyk Apr 29 '13 at 14:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.