These steps are for recovery in which we will know about how to recover from a loss of a non-system tablespace on the same location while the database is closed. Normally our database is always in open mode. In some cases we can face error ORA-01157: cannot identify/lock data file %s – see DBWR trace file” to solve this issues we can follow these steps.

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

The database is not open and if I try to execute startup command it signals ORA-01157 error. That error means instance was not able to open the example01.dbf (data)file.

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

Even if I know how to solve this kind of problem, I would like to take some of your time and use a new RMAN feature, the Data Recovery Advisor.
It can detect and show current restore and recovery problems occurring in your database, advices you about their resolution and even execute for you all the RMAN commands to fix problems.

When I remember to use Data Recovery Advisor I usually perform the following four commands:

Let’s see what Data Recovery Advisor shows us executing my previous commands. The instance is in MOUNT state and this is the output executing LIST FAILURE command:

As you can see one database failure exists from “11-01-2013 03:15:46” and it has a failure id as 363, it’s status is still OPEN having also a HIGH priority.
We want now to have more details on that specific failure id: simply execute the following command to know example01.dbf datafile is missing and because it contains some objects they are not available.

To obtain advices on how to solve your current failure run the following command.
It will show the manual or automated actions required to repair your database. The commands you can use to solve your issue automatically are contained into the repair script.

You can now open with a text editor your repair script to look at the suggested commands, but why not use again RMAN client and the REPAIR FAILURE PREVIEW command ?
As you can see RMAN displays the repair commands without actually running them.

So it’s time to recover our tablespace executing the restore command and the name of your lost tablespace:

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

The tablespace is now recovered we can try to open our database. 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.