Message-Id: <10533.109783@fatcity.com> From: Oliver Artelt Date: Mon, 19 Jun 2000 22:15:24 +0200 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