There is note # 28812.1 on Oracle Metalink. That can be of use to resolve
this problem..
- Kirti
> -----Original Message-----
> From: ddorr.cs_at_clearstream.com [SMTP:ddorr.cs_at_clearstream.com]
> Sent: Tuesday, June 20, 2000 6:59 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: deleted rollback-seg. tablespace
>
> I would start with :
>
> backup my controlfile to trace
> shutdown the DB
> modify the init.ora file -> rollback_segments=()
> restart the DB
>
> ... see what happens :-)
>
> > ----------
> > From: Oliver Artelt[SMTP:oli_at_cubeoffice.de]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Tuesday, June 20, 2000 00:17
> > To: Multiple recipients of list ORACLE-L
> > Subject: deleted rollback-seg. tablespace
> >
> >
> > Hi everyone, could someone help me?
> >
> > I've deleted various tablespaces in a database (linux 2.2.14, EE
> > 8.1.5.0.2) -
> > some kind of big whoop. O.K., I use this database to prepare myself for
> > the
> > OCP-tests and so no backup were taken (Yes, I had to know that backups
> are
> > necessary but the backup/recovery exam follows later :-). Recreating the
> > tablespaces and copying the logs/controls from existing members was easy
> > but
> > I've got no plan how to restore/kill-rebuild the deleted tablespace with
> > the
> > rollback segs. Seems to me like an deadlock problem over various things:
> >
> >
> > SET TRANSACTION USE ROLLBACK SEGMENT system;
> >
> > Try to drop the tablespace:
> > drop tablespace rbs01 including contents;
> > -> ORA-01548: active rollback segment 'R05' found, terminate dropping
> > tablespace
> >
> > Try to kill the seg:
> > drop rollback segment r05;
> > -> ORA-01545: rollback segment 'R05' specified not available
> >
> > Try to set it offline:
> > alter rollback segment r05 offline;
> > -> ORA-01598: rollback segment 'R05' is not online
> >
> > Try to set up another tablespace to temporary solve the problem:
> > create tablespace rbs02 datafile '/ora/u03/oradata/stock1/rbs02.dbf'
> size
> > 20m;
> > -> ORA-00604: error occurred at recursive SQL level 1
> > ORA-00376: file 3 cannot be read at this time
> > ORA-01110: data file 3: '/ora/u03/oradata/stock1/rbs01.dbf'
> >
> > Recover tablespace (maybe resetting rbs)
> > recover tablespace rbs01;
> > ->ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
> >
> > alter tablespace rbs01 offline immediate;
> > ->no help.
> >
> > alter tablespace rbs01 add datafile'/ora/u03/oradata/stock1/rbs02.dbf'
> > size 200 m;
> > alter tablespace rbs01 online;
> > ->ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
> > ORA-01110: data file 3: '/ora/u03/oradata/stock1/rbs01.dbf'
> >
> > Examine views:
> > SELECT segment_name, tablespace_name, status FROM sys.dba_rollback_segs;
> >
> > SEGMENT_NAME |TABLESPACE_NAME |STATUS
> > ------------------------------|-----------------------------
> > SYSTEM |SYSTEM |ONLINE
> > R05 |RBS01 |NEEDS
> > RECOVERY
> > R01 |RBS01 |NEEDS
> > RECOVERY
> > R02 |RBS01 |NEEDS
> > RECOVERY
> > R03 |RBS01 |NEEDS
> > RECOVERY
> > R04 |RBS01 |NEEDS
> > RECOVERY
> > R06 |RBS01 |NEEDS
> > RECOVERY
> > R07 |RBS01 |NEEDS
> > RECOVERY
> > R08 |RBS01 |NEEDS
> > RECOVERY
> > R09 |RBS01 |NEEDS
> > RECOVERY
> > R10 |RBS01 |NEEDS
> > RECOVERY
> >
> > select * from v$rollstat;
> > -> only system-rbs is listed.
> >
> >
> > I've done RTFM, (server concepts, adminitration guide, backup guide and
> > several
> > books). I could not find any hint, they say to set the segs offline but
> I
> > couldn't do that. Has someone another plan?
> >
> > Thank you very much,
> > oli.
> >
> > Oliver Artelt, System- und Datenbankadministration
> > ---------------------------------------------------------------
> > cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
> > telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 10
> > email: oli@cubeoffice.de # web: http://www.cubeoffice.de
> > ---------------------------------------------------------------
Received on Tue Jun 20 2000 - 08:17:26 CDT
