topic Re: DROP Rollback Segment file in Operating System - HP-UX

DROP Rollback Segment file

Ali_11 — Thu, 30 Jan 2003 14:47:50 GMT

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.

Re: DROP Rollback Segment file

Brian Gebhard_1 — Thu, 30 Jan 2003 15:07:17 GMT

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.

Re: DROP Rollback Segment file

Brian Crabtree — Thu, 30 Jan 2003 23:58:42 GMT

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

Re: DROP Rollback Segment file

Julio Yamawaki — Fri, 31 Jan 2003 01:08:08 GMT

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.

Re: DROP Rollback Segment file

T G Manikandan — Fri, 31 Jan 2003 03:46:47 GMT

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

Re: DROP Rollback Segment file

T G Manikandan — Fri, 31 Jan 2003 03:48:46 GMT

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

It too requires some recovery