rbs tablespace missing datafile

You might be able to use the alter database drop datafile command in mount mode.

If that does not work, then see if you can drop and recreate the RBS tablespace in mount mode.

wietman,
I tried both of those but neither worked - when dropping the datafile it says it does but really does not.  cannot drop the tablespace in mount mode because the database is not open

Hi,

Why do you want to drop that datafile ? don't do that ! You need the undo tablespace in order to open the database !
Oracle has detected that there is a datafile that is needed, but that was missing in the controlfile (probably because you restored a controlfile that was backed-up before the createion fo the datafile)
If you have the datafile (oracle do not know the name, but you should - you can search in alert.log for the log of the add datafile statement) then just rename '/export/home/oracle/product/10_2/dbs/MISSING00052' to the real name.

But, I'm quite sure you have other datafiles missing... Don't do resky things if you do not understand your current situation. What has been done on that database ?

Regards,
Franck.

I copied this database from an existing one - I did not need all three rollback datafiles so only put two in the create control file script.  
I just tried........
 SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1744830464 bytes
Fixed Size                  2038640 bytes
Variable Size             397673616 bytes
Database Buffers         1330380800 bytes
Redo Buffers               14737408 bytes
Database mounted.
Database opened.

SQL> select file_name from dba_data_files where file_name like '%MIS%';
no rows selected
SQL> shutdown
ORA-00376: file 52 cannot be read at this time
ORA-01110: data file 52: '/oradata8/dev2/rbs_03.dbf'
 

Sorry!
My assumption was that he knew that and could not recover that file.
The only option I know of at that point is to recreate rollback tablespace or undo.

I have tried dropping the rbs tablespace but can't do that in mount or open mode so how do I recreate it?

I'll see what I can find on this.

This is on Oracle's support sight:

Undo Tablespace
   -------------------
   While handling situation with lost datafile of an undo tablespace one has
   to be extra cautious so as not to loose active transactions in the undo
   segments. If need be please call Oracle Support for assistance.

   The prefer option in this case is to restore the datafile from backup and
   perform media recovery.


      i.  Database was cleanly shutdown.
          Ensure that database was cleanly shutdown in NORMAL or IMMEDIATE mode.
          Update your init file with "undo_management=manual"
          Restart the database
          Drop and recreate the undo tablespace
          Update your init file with "undo_management=auto"
          Restart the database

      ii. Database was NOT cleanly shutdown.
          If the database was shutdown abort or had crashed last time, in this
          case it is possible that you will not be able to offline and drop
          the datafile as the undo segments contained in it could contain
         active transactions. You will have to restore the file from a backup
          and do a media recovery. If this can not be achieved, please contact
          Oracle Support for further assistance.

re:  I have tried dropping the rbs tablespace but can't do that in mount or open mode so how do I recreate it?

   

Did you have "undo_management=manual"?

Make sure you use the create this as an undo tablespace, not a regular user one.
i.e.  Something of this form.

CREATE UNDO TABLESPACE undotbs_02
     DATAFILE '/u01/oracle/rbdb1/undo0201.dbf' SIZE 2M REUSE AUTOEXTEND ON;

I have undo_management set to manual.
 1* select file_name, status from dba_data_files where tablespace_name = 'RBS'
SQL> /
FILE_NAME                                          STATUS
-------------------------------------------------- ---------
/oradata8/dev2/rbs_02.dbf                          AVAILABLE
/oradata8/dev2/rbs_01.dbf                          AVAILABLE
/oradata8/dev2/rbs_03.dbf                          AVAILABLE

SQL> select distinct status from v$datafile;
STATUS
-------
ONLINE
RECOVER
SYSTEM

SQL> alter tablespace rbs offline;
alter tablespace rbs offline
*
ERROR at line 1:
ORA-01546: tablespace contains active rollback segment 'RBS_01'

SQL> alter rollback segment rbs_01 offline;
alter rollback segment rbs_01 offline
*
ERROR at line 1:
ORA-00376: file 52 cannot be read at this time
ORA-01110: data file 52: '/oradata8/dev2/rbs_03.dbf'
 

OK.  I have a better idea.
Create a new undo tablespace.
The switch to it, and

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

then restart the database and drop the old tablespace.

I was able to drop the rbs tablesape and recreate it but now I cannot open the database as it does not have any rollback segments - I guess all I can do is recopy all the datafiles and start over unless you can think of something else.

ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1744830464 bytes
Fixed Size                  2038640 bytes
Variable Size             397673616 bytes
Database Buffers         1330380800 bytes
Redo Buffers               14737408 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
 

Did you Update your init file with "undo_management=auto"?

thanks

I hope this means you got it fixed?