Showing results for 
Search instead for 
Did you mean: 

DROP Rollback Segment file

Occasional Advisor

DROP Rollback Segment file


it's an Oracle question : I drop unfornetly a file of a rollback segment tablespace and don't have backup. I shutdowned the database and try to open it but i have this error :
ORA-01157: cannot identify data file 2 - file not found
ORA-01110: data
file2: '/dev/ORADEV/rbs/rbs01.dbf'

can i open my databse without this file and recreate a rollback after ?



Re: DROP Rollback Segment file

You'll most likely have to mount the database but not open it. Then create another rollback tablespace. Drop the original rollback tablespace once you have created a 2nd one. Then drop the first one offline. You'll never be able to use that rollback tablespace again since you don't have a backup or use the name again. If this is a production database you'll want oracle support online.
Brian Crabtree
Honored Contributor

Re: DROP Rollback Segment file

Hmmm. It might be possible to do this. You will need comment out the "rollback_segments" line in the init.ora, then do the following:

startup mount
alter database datafile
'/dev/ORADEV/rbs/rbs01.dbf' offline drop;
alter database open;
- For each rollback segment:
drop rollback segment ;
- or -
drop tablespace rbs including contents;

Once complete, recreate the tablespace.

Julio Yamawaki
Esteemed Contributor

Re: DROP Rollback Segment file


to recovery from loast of a rbs datafile is not so simple.
So I'm sending you an attachment file, with two possibilities.
If you have some difficulties, let me know.

Good luck.
T G Manikandan
Honored Contributor

Re: DROP Rollback Segment file

During startup oracle checks to bring up the rollback segments mentioned in the init.ora file.

Remove the rollback segment names from the init.ora file.

Now the database will open.Then you can create a new rollback tablespace and then drop the lost file tablespace.
you can also do this
$export ORACLE_SID
connect internal
svrmgr>startup mount

svrmgr>alter database datafile '/dev/ORADEV/rbs/rbs01.dbf' offline drop
svrmgr>alter database open

create tablespace datafile ' size ;
create tablespace rollback datafile '' 500 M;

create rollback segment roll1 tablespace rollback online;

select segment_name,tablespace_name,status from dba_rollback_segs;

select name,TS# from v$tablespace;
select name from v$datafile where TS#='0';

check whether the datafile that was missing is in the list

select segment_name,tablespace_name,status from dba_rollback_segs where tablespace_name='

alter rollback segment (repeat this for the all the rollback segment that is in this tablespace)

drop tablespace ;

(the tablespace which had the lost file)

Also make sure that you update the init.ora file for the newly created rollback segments

T G Manikandan
Honored Contributor

Re: DROP Rollback Segment file

After you recover your database kindly recover the no.of points assigned for your questions.

It too requires some recovery