We are going to have a look on the steps which are followed by DBA to recover a loss of a non-system tablespace on the same location while the database is open. When we lose a non-system tablespace you cannot access and query only objects that were created on their datafiles  Meanwhile users can continue to query and use all the others objects in the database and you can restore it while the database is open. Moreover because your database is running in ARCHIVELOG mode any committed transactions don’t need to be inserted again.

Let’s simulate a loss of the EXAMPLE tablespace, in this case formed by only one datafile:

The database is still open and I query for the very first time an object located on the EXAMPLE tablespace.

I receive an error stating the instance was not able to open the example01.dbf datafile

If you look at the alert log the same error and a trace file are generated

As you can see the objects located on EXAMPLE tablespace are no more available. So it’s time to recover our tablespace: I’d like to remind you that all the following steps are executed while the database is OPEN as you can see even from the screen log of rman console (connected to target database: ORCL (DBID=1229390655)):

Second step is to issue the restore command providing the name of your lost tablespace

After your tablespace was restored from your backup pieces it’s time to execute the recover command.

The tablespace is now recovered and you can put it again online and available for your users.

An extract of the alert log taken during the restore and recover process…

Thank you for giving your valuable time to read the above information.


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.