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?

1 Answer 1


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.

  • how undo is synchronized when recovered tablespace is imported from auxiliary instance?
    – K.Esaulov
    Apr 24, 2013 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, 2013 at 14:38
  • 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, 2013 at 11:43
  • thank you for answer, it seems I need to read more about how Oracle engine works. About "import tablespace", I mean the final step of tspitr is to transport the recovered tablespace from auxiliary instance to target.
    – K.Esaulov
    Apr 26, 2013 at 4:03
  • 1
    Hypothetically, yes, I guess so. But Oracle doesn't give you possibility to have different undo tablespace for each data tablespace.
    – kubanczyk
    Apr 29, 2013 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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