Re: ROLLBACK tablespace needs media recovery when startup mount;

From: G.P. Singh <>
Date: Wed, 25 Aug 1999 15:29:45 -0400
Message-ID: <>


If u r running Archive mode , try dropping that offline datafile(make sure system rollback segment is not there), or first drop rollback segments in that tablespace ,shudown ,remove those rollback segments from init<sid> .ora file, and try
bringing up.
If u want to drop the tablespace, create two rollback segments in system tablespace,shutdown,edit init<sid>/ora
file for rollback segment parameter, startup mount , drop datafiles, open,drop tablespace,recreate tablespace, recreate rollback segments,shutdown,edit init<sid>.ora file and that's it.

Now if it does not allow u to drop rollback segments or rollback segment tablespace,edit init<sid>.ora ,put a
parameter _corrupted_rollback_segment=<the rollback segment causing drop failure> and bring up oracle
and then drop tablespace/roll back segment. This parameter should be first discussed with oracle/or u must have a backup.

Take Care

Raymond wrote:

> Hi,
> I am new to be database administrator. Currently running Oracle 8.0.3
> on NT4.0 SP3.
> 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+
Received on Wed Aug 25 1999 - 21:29:45 CEST

Original text of this message