Datafile belonging to UNDO tablespace

This is a recovery scenario in which a datafile in a UNDO or ROLLBACK SEGMENT tablespace has been lost or damaged to a point that Oracle cannot recognize them anymore. Trying to startup the database will result in ORA-1157, ORA-1110, and possibly an operating system level error such as ORA-7360. Trying to shut down the database in normal or immediate mode will result in ORA-1116, ORA-1110, and possibly an operating system level error such as ORA-7368.

There are typically two scenarios under this:

Database was cleanly shut down

I.e. Datafile was accidentally dropped using operating system command, when the database was cleanly shutdown. If you are ABSOLUTELY POSITIVE that the database was cleanly shutdown, i.e., it was closed with either shutdown NORMAL or IMMEDIATE (DO NOT follow this procedure if the database was shut down ABORT or if it crashed), then the simplest solution is to offline drop the missing datafile, open the database in restricted mode, and then drop and recreate the rollback tablespace to which the file belonged the steps performed are:.

• Make sure the database was last cleanly shut down. Check the alert.log file for this instance. Go to the bottom of the file and make sure the last time you shut the database down you got the messages: "alter database dismount Completed: alter database dismount" This also includes the case of a clean shutdown followed by a failed attempt to startup the database. In that case, Oracle will issue error messages and shut itself down abort. For the purposes of this solution, though, this counts as a clean shutdown. If that is not the case, i.e., if the last time YOU shut the database down it was in abort mode, or the database crashed itself, it is NOT safe to proceed, the scenario for this type of recovery is discussed below.
• Mount the database in restricted mode. STARTUP RESTRICT MOUNT
• Offline drop the lost datafile.
  ALTER DATABASE DATAFILE OFFLINE DROP;
• Open the database.
  ALTER DATABASE OPEN.
• Drop the rollback tablespace to which the datafile belonged.
• Recreate the rollback tablespace with all its rollback segments. Remember to bring the rollbacks online after you create them.
• Make the database available to all users.
• ALTER SYSTEM DISABLE RESTRICTED SESSION;

Database was NOT cleanly shut down

I.e. Datafile was accidentally dropped using operating system command, when the database was not cleanly shutdown using SHUTDOWN ABORT COMMAND.

• Check the alert.log file for this instance.
• Comment out the ROLLBACK_SEGMENTS parameter and add the following line: _corrupted_rollback_segments = ( ,...., ) i.e., the above list should contain all the rollbacks originally listed in the ROLLBACK_SEGMENTS parameter.
• Mount the database in restricted mode. STARTUP RESTRICT MOUNT
• Offline drop the lost datafile.
  ALTER DATABASE DATAFILE OFFLINE DROP;
• Open the database.
  ALTER DATABASE OPEN.
• Drop the rollback tablespace to which the datafile belonged.
• Recreate the rollback tablespace with all its rollback segments. Remember to bring the rollbacks online after you create them.
• Make the database available to all users.
• ALTER SYSTEM DISABLE RESTRICTED SESSION;