HELP - Rollback segment NEEDS RECOVERY

From: Tommy Johnson <tommyj_at_netcom.com>
Date: 1995/05/03
Message-ID: <tommyjD80HB2.EuF_at_netcom.com>#1/1

I'm not sure how to fix this problem:

---

Connected to:
Oracle7 Server Release 7.1.4.1.0 - Production Release With the distributed option
PL/SQL Release 2.1.4.0.0 - Production  

SQL> select segment_name, tablespace_name, status from sys.dba_rollback_segs;  

SEGMENT_NAME                   TABLESPACE_NAME                STATUS


------------------------------ ------------------------------ ----------------


SYSTEM                         SYSTEM                         ONLINE
R04                            RBS                            NEEDS RECOVERY
R01                            RBS                            ONLINE
R02                            RBS                            ONLINE
R03                            RBS                            ONLINE
 

---

My machine (Sun SPARCstation 5, SunOS 4.1.3_U1) rebooted while a batch job was running last night. I couldn't even bring the database back up this morning until I removed R04 from the ROLLBACK_SEGMENTS line in the initXX.ora file. (the error message was ORA-1545 rollback segment 'R04' was not available, caused the "dbstart" script to fail to start the database)

Now I can start the database, but as you can see R04 "NEEDS RECOVERY".

I assume Oracle was in the middle of some transaction when a power outage or some other hardware failure caused the reboot (this has happened before and we're working on locating the cause). I believe this left one or more blocks in the tablespace in an inconsistent state (this was from trying to export the user running the batch job):

---

. exporting table                      RMSAMPL25

EXP-00014: error on row 9264 of table RMSAMPL25  

EXP-00222:
System error message 2

EXP-00008: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 2, block # 6396)
ORA-01110: data file 2: '/usr1/oracle/DB/PM15_ROLLBACK_sp1.dbf'

---

How can I recover a rollback segment? I have tried to bring in ONLINE, but get the error message saying it's already ONLINE. Tried to take it OFFLINE, but get the error saying that it isn't ONLINE. ???

Help! Any suggestions would be greatly appreciated.

Thanks,
-Tommy
--

---

Tommy Johnson                             tommyj_at_netcom.com
      ftp.netcom.com /pub/ar/arkham -or- /pub/to/tommyj 

Received on Wed May 03 1995 - 00:00:00 CEST