Why we need auxiliary instance in TSPITR

oracle-11grecoveryrmantablespaces

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?

Best Answer

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.

Related Question