Re: File needs media recovery - no can do!

If your rollback segments tablespace doesn't have any needed data, you can try to drop this tablespace at all. This algorithm can help:

1. Look at the description of 'CREATE CONTROLFILE ...' command and then fill all required fields very carefully. Don't forget specifying 'NORESETLOGS'. As another variant you can find any working database and issue command 'ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS. After that you can find script of 'CREATE CONTROLFILE ...' command in server trace directory. Edit it as you need. Exclude from this script information about your corrupted tablespaces' datafiles.
2. Keep current controlfile of your database in safe place and start your instance with 'STARTUP NOMOUNT pfile = ...'. Then run the script above specified.
3. New control file will be created. Shutdown database.
4. Start, mount database and open it with 'alter database open NORESETLOGS'. If you query view V$DATAFILE now you will see text 'MISSINGXXX' in place of your datafile.
5. Drop your tablespace.
6. Backup database as usual.

I tried this algorithm several times and I hadn't any problems.

Jack Jolly wrote ...
>I have a database that's complaining that "file #3 needs media recovery."
>This datafile is the one and only datafile for the rollback segments
>tablespace that has 20 public rbs. Any way I can "disable" this
>tablespace/datafile so I can get the db going w/o doing the media recovery?
>I've tried "alter offline drop" on the datafile, but v$logfile still shows
>it with status "recover" rather than "offline", an then "alter open" won't
>work.
>
>Ideas appreciated - JJ
>
>===========================================================
>
> Jack G. Jolly
> Intergraph PDS Support
> jgjolly_at_ingr.com, (256) 730-3100
>
>
Received on Tue Feb 23 1999 - 12:16:13 CST