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
> ---------------------------------------------------------------
> --
> Author: Oliver Artelt
> INET: oli_at_cubeoffice.de
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Tue Jun 20 2000 - 05:43:55 CDT