Re: ROLLBACK tablespace needs media recovery when startup mount;

Raymond wrote:

> Is it possible to drop the whole tablespace for ROLLBACK segments?
> Any precautions? Actually, I want to delete the 2nd datafile as it is
> no longer used. The story behind is as follows.
>
> I have created rollback segments for both SYSTEM and user in the same
> ROLLBACK_DATA tablespace. The tablespace consists of 2 physical
> datafiles. One day, I found One of the datafile is in OFFLINE status
> one day for unknown reason.
>
> When I am trying to disable the archive log mode for importing data to
> another database, and trying to start the database in mount mode but
> failed.
>
> Checked the Alert.log and found the error ORA-01143: cannot disable
> media recovery - file name needs media recovery.
>
> According to Oracle8 Online document, cause of ORA-01143 is -
> "An attempt to disable media recovery found a file that needs media
> recovery. Therefore, media recovery cannot be disabled."
>
> According to Oracle8 Online document, action towards ORA-01143 is -
> "Recover the offending file or drop the tablespace to which it
> belongs, then retry the operation."
>
> Thanks in advance.
>
> Phoebe+

Does this mean that you dropped the SYSTEM rollback segment from the SYSTEM tablespace and then re-created it in another tablespace. I suspect that may not have been a very good idea. I was always taught that one should never make any changes to the SYSTEM rollback segment that is created when the database itself is created.

What I have always done during database creation is create an additional rollback segment in the SYSTEM tablespace and then go ahead and create a RBS tablespace for rollback segments, create additonal rollback segments there and then go back and take my extra rollback segment in the SYSTEM tablespace offline. That way if I ever want to drop the RBS tablespace I would just put my extra roolback segment back online, drop all of the rollback segments in the RBS tablespace and then drop the tablespace.

Ken