We are going to have a look on those steps which are required to restore a lost nonsystem datafile on a different location during the Oracle a database is in open stage.  We are going to lose datafiles of a non-system critical tablespace and restore them, while the database is open, to a location other than the original one because I’m experiencing a serious and permanent media failure.
Before proceeding, we add another datafile to EXAMPLE tablespace so it is now formed by 2 different datafiles.

Of course to restore a tablespace you need to have a valid backup so I’m going to execute a backup tablespace command for the EXAMPLE tablespace using RMAN:

The original location of EXAMPLE datafiles is:

During the restore operation I will instruct RMAN to recreate them on a new destination:

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

I’m not able to use objects created into EXAMPLE tablespace.

I discover also it is a permanent disk failure and I won’t be able to restore EXAMPLE’s datafiles on the original location, perhaps in a second moment in the next days, but now I have to solve this issue as soon as possible.

Within RMAN client we can use set new name for datafile command to change the name of multiple files during the restore operation:
after you specify the above command you have to run also switch datafile all command to update your controlfile with the renamed datafiles. If you don’t use the switch command RMAN records the restored files as datafile copy in RMAN repository.
An RMAN switch is equivalent to the SQL alter database rename file command.
It’s important to note that both 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.

While connected to your database you can query V$DATAFILE, V$DATAFILE_HEADER or V$DATAFILE_COPY to obtain file number of the missing datafile or run the report schema command from RMAN client.

My missing datafiles have 5 and 9 as file number. To restore and recover them on a new location I have to execute the following run {…} block:

Some considerations need to be made:
1. the restore and recover operations were made while database was open so we needed to put those datafiles offline;
2. because all missing datafiles belonged to the same tablespace we could use alter tablespace … offline immediate, restore tablespace and recover tablespace syntax, but I want to show their uses in another post;
3. We could use switch datafile command in place of switch datafile all.

On the new location are now available two files. File data_D-ORCL_TS-EXAMPLE_FNO-9 is that one created automatically by Oracle using %U as substitution variable.

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

Few days later we are able to move back our EXAMPLE datafiles on their original location because a new disk is ready to be used.
How can we proceed ?

We will follow the steps already described in this post.
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.

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

report schema command displays the new location of EXAMPLE datafiles.

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.