deleted rollback-seg. tablespace

From: Oliver Artelt <oli_at_cubeoffice.de>
Date: Mon, 19 Jun 2000 22:15:24 +0200
Message-Id: <10533.109783@fatcity.com>

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 Mon Jun 19 2000 - 15:15:24 CDT