My Piece of Data

In a situation where a datafile from the SYSTEM tablespaces is lost, you should execute the following commands to return the database to an operational state. Obviously, for this, you should have up-to-date and intact backups.

The database will be inaccessible until a full restoration is completed.

In the example below, we will use an Oracle Real Application Cluster (RAC) environment, a connection to the RMAN catalog, connecting to the ZDL Recovery Appliance catalog, and performing the connection via wallet. If you are not using an RMAN repository or not using a catalog, simply remove the catalog connection.

Replace the values of <database_name> with the database name (db_unique_name) and <instance_name> with the instance name (instance_name or ORACLE_SID).

First, stop the database and start only one instance in mount mode, as shown below:

srvctl stop database -d <database_name> -o immediate
srvctl start instance -d <database_name> -i <instance_name> -o mount

You can perform 3 types of restore:

restore database; (It will restore all the datafiles and archive logs.)
restore tablespace system; (It will restore all the datafiles of the tablespace.)
restore datafile <file_id>; (It will restore only the problematic datafile.)

Replace the values of <file_id> with the number of the lost datafile. You can find the datafile in the database alert log if you choose to restore the datafile.

We will use the restore tablespace as an example:

rman target / catalog /@zdl_ra01

run {
   restore tablespace system;
   recover tablespace system;
}

At the end, open the database on both instances:

sqlplus sys as sysdba
alter database open;