With the help of this article today we are going to learn about those steps which are going to use during the restore lost non-system datafiles on different location the database is closed. In our previous post, we have discussed the restore lost nonsystem datafiles on a different location while the database while the database is open.

We are going to lose two datafiles of two different non-system critical tablespaces and restore them, while the database is CLOSED, to a location other than the original one because I’m experiencing a serious and permanent media failure.

To simulate this scenario We want to remove a datafile from EXAMPLE and APEX tablespaces.
Let’s see first where are located those datafiles.

I’m going to remove example02.dbf and APEX02.dbf datafiles because I have a valid backup of EXAMPLE and APEX tablespaces taken days ago using RMAN.

Are you sure you have a valid backup ? Why don’t you verify it ?

Let’s see what id number have those datafiles querying V$DATAFILE view:

Now let’s verify if I have a valid backup using restore … preview command for datafiles with 9 and 10 as id number:

The original location of APEX and EXAMPLE datafiles is: /home/oracle/app/oracle/oradata/orcl/

During the restore operation I will instruct RMAN to recreate them on a new destination: /home/oracle/app/oracle/oradata/orcl/non_default_location

A media failure happened and I’ve lost those datafiles belonging to EXAMPLE and APEX tablespace:

The instance crashed.

If I try to startup the database it remains in MOUNT mode throwing the error “ORA-01157: cannot identify/lock data file 9 – see DBWR trace file”.

If I have a look at the alert log I can see also the same error for datafile 10:

Let’s shutdown the instance to use a RMAN script restoring and recovering our database while is closed:

As already said due to a permanent disk failure I won’t be able to restore example02.dbf and APEX02.dbf datafiles on the original location.

Like in the previous post I need to use set newname for datafile command to change the name of multiple files during the restore operation and then I have also to run switch datafile all command to update our controlfile with the renamed datafiles.

Note that those commands must be executed inside a run {…} block.
To identify your original datafiles you can use their absolute file numbers, full path or relative file names; to recreate them on a new location you have to specify their full path file names, using eventually some substitution variable like %U to specify a system-generated unique file name and avoid file name collisions.
We already know id numbers, but in this scenario and differently from the previous one, I want to use the full path file name of the missing datafiles.

To restore and recover them on a new location I have to execute the following run {…} block after connected to RMAN and issued a startup mount command:

Some considerations need to be made:
– the restore and recover operations were made while database was mounted so I did not need to put those datafiles offline;
– I could use switch datafile command in place of switch datafile all;
– I prefer to specify in these situations the id number, otherwise you have to use the original file name for the restore datafile command and the new file name for the recover datafile command.

This is always due to the fact that controlfile doesn’t have any information about the new location until you execute the switch datafile all command: from that moment the controlfile lose the information about the original location of those datafiles.

Just be careful to use the right file name if you prefer to specify full path file name instead querying V$DATAFILE or issuing report schema to know id numbers.
Let’s see on the new location the two datafiles:

Have a look at the new output produced by report schema command:

Few days later we are able to move back those datafiles on their original location because a new disk is ready to be used.
First thing to do is to copy your datafiles using the format clause, specifying you want to create your datafile copy to the original location.

Next step is to put the same datafiles offline.

Switch to your new datafiles location updating your controlfiles.

Recover your datafiles because some transactions could be occurred between backup as copy datafile command and putting datafiles offline.

Let datafiles be available to all the users, putting them online:

report schema command displays the new location of example02.dbf and APEX02.dbf datafiles.

We hope this information is useful for the reovery session.


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.