In the event of a datafile loss, which is not part of the SYSTEM, SYSAUX, or any other tablespace belonging to the Oracle data dictionary, you can execute the commands below to recover the datafile. Obviously, for this, you must ensure that your backups are up-to-date and intact.
The tablespace will remain inaccessible until the full restoration is completed.
In the example below, we will use a connection to the RMAN catalog, connecting to the ZDL Recovery Appliance catalog via wallet. If you are not using an RMAN repository or do not plan to use a catalog, simply remove the connection to the catalog.
Replace the <file_id> value with the number of the lost datafile. You can find the datafile in the database’s alert log or by running a simple select command on any table within the tablespace. This will cause Oracle to display an error on the screen, including the datafile number.
rman target / catalog /@zdl_ra01
run {
alter database datafile <file_id> offline;
restore datafile <file_id>;
recover datafile <file_id>;
alter database datafile <file_id> online;
}
From this point on, you will be able to use everything that is under this tablespace.
My Piece of Data 