In this phase of recovery, we are going to have a look on recover from a loss of the system critical UNDO tablespace on the original location. I hope this phase will be useful for you.

As we all know that UNDO tablespace stores undo segments, generally used to explicitly (ROLLBACK command) or implicitly (a failed transaction) rollback a transaction, to recreate a read-consistent image and for all kind of recovery purposes.

As well as the SYSTEM tablespace the database must be in the MOUNT state to recover UNDO tablespace.

Let’s begin simulating the loss of the UNDO tablespace: in my case the instance is running and…

… I simply remove its UNDO datafile.

As you can see when I try to insert a row in one table Oracle throws an error stating it’s not able to open undotbs01.dbf (data)file.

The same information is written in the alert log as well as in a trace file

If you try to recover your UNDO tablespace taking it offline you receive some errors: you cannot take offline that tablespace.

Then it’s time to shutdown the instance, but it doesn’t work.

You can at this time kill your instance or issue a SHUTDOWN ABORT command.

Your recovery process needs to be started while the database is in MOUNT mode.

Once the database is mounted you can restore your UNDO tablespace

recover it …

.. and after the recovery process finishes you can finally open your database.


For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

About The Author

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.