DROP Rollback Segment file

[Ali_11]

Hi,

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 ?

Thanks.

[Brian Gebhard_1]

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]

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.

Brian

[Julio Yamawaki]

Hi,

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]

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
$ORACLE_SID=
$export ORACLE_SID
$svrmgrl
connect internal
svrmgr>startup mount

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

create tablespace datafile ' size ;
like
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


Thanks

[T G Manikandan]

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

It too requires some recovery