If you have detected the loss of the rollback datafile and the database is still up and running, DO NOT SHUT IT DOWN. In most cases, it is simpler to solve this problem with the database up than with it down.
Two approaches are possible in this scenario:
A) The first approach involves creating a new UNDO (or rollback segment) tablespace, altering the system to use the new and dropping the old.
B) The other approach involves offlining the lost datafile, restoring it from backup, and then applying media recovery to it to make it consistent with the rest of the database. This method can only be used if the database is in ARCHIVELOG mode.
In general, approach IIA is simpler to try first, however, depending on activity and undo usage, the drop of the older tablespace may result in error regarding active transactions. There are a few things to try to get the older tablespace to drop, but if all fail, approach IIB may be required.
Second Approach Steps:
Step 1 Offline the lost datafile.
SQL> ALTER DATABASE DATAFILE '' OFFLINE;
NOTE: Depending on the current amount of database activity, you may have to create additional rollback segments in a different tablespace to keep the database going while you take care of the problem.
Step 2 Restore the datafile from a backup.
Step 3 Issue the following query:
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE# FROM V$LOG V1, V$LOGFILE V2 WHERE V1.GROUP# = V2.GROUP# ;
This will list all your online redolog files and their respective sequence numbers.
Step 4 Recover the datafile:
SQL> RECOVER DATAFILE ''
Step 5 Confirm each of the logs that you are prompted for until you receive the message "Media recovery complete". If you are prompted for a non-existing archived log, Oracle probably needs one or more of the online logs to proceed with the recovery. Compare the sequence number referenced in the ORA-280 message with the sequence numbers of your online logs. Then enter the full path name of one of the members of the redo group whose sequence number matches the one you are being asked for. Keep entering online logs as requested until you receive the message "Media recovery complete".
Step 6 Bring the datafile back online.
SQL> ALTER DATABASE DATAFILE '' ONLINE;
First Approach Steps:
Step 1 Create a new undo tablespace:
SQL> CREATE UNDO TABLESPACE DATAFILE '' SIZE ....;
Step 2 Alter the system to use the new undo tablespace:
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='';
At this point, all new undo will be allocated in undo segments managed within the UNDO_TBS2 tablespace.
Step 3 Try dropping older undo tablespace:
SQL> DROP TABLESPACE INCLUDING CONTENTS;
If this succeeds, you are done. If it returns an error, please see "Handling errors dropping undo/rollback segments".
