| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Corrupt rollback segments
"Mr_Lab" <t.o.b.y_at_btinternet.com> wrote in message
news:8ma8vt$3jn$1_at_neptunium.btinternet.com...
> Hi,
> I have an 8.1.5 instance that has suffered file corruption to my
> rollback tablespace. I've tried dropping the tablespace but keep getting:
>
> ORA-01548: active rollback segment 'R01' found, terminate dropping
> tablespace
>
You can't drop a rollback segment tablespace if Oracle still thinks one of the rollback segments inside it is active. You therefore have to offline the SEGMENTS first, and then drop the tablespace. Offline-ing an active rollback segment will put it into 'PENDING OFFLINE' mode, which means the current transaction(s) can complete, but no new transactions will be allowed in. When all current transactions have completed, the status goes to offline, and you're then ready for the drop.
So, I would suggest you offline your segments, then shutdown abort your Instance, then startup again, and hopefully you'll discover that the *tablespace* can be dropped and re-created (you could also use the v$rollstat and v$session views to try and determine what specific sessions are using the relevant rollback segments, and just terminate those sessions, rather than shutting down the entire database).
Whether any of this will work when you're dealing with corrupted segments is a moot point, of course, but give it a whirl, and let me know if it doesn't work. There's a whole bag of other tricks we can try if this doesn't do the deed.
Regards
HJR
> The current status off the problematic rollback tablespace is 'NEEDS
> RECOVERY'
>
> SEGMENT_NAME OWNER TABLESPACE_NAME
> SEGMENT_ID FILE_ID BLOCK_ID INITIAL_EX NEXT_EXTEN MIN_EXTENT
MAX_EXTENT
> PCT_INCREA STATUS INSTANCE_NUM
> RELATIVE_F
> ------------------------------ ------ ------------------------------ -----
-->
> --- ---------- ---------- ---------- ---------- ---------- ---------- ----
--
> ---- ---------------- ---------------------------------------- ----------
> SYSTEM SYS SYSTEM
> 0 1 2 57344 57344 2 505
> 0 ONLINE 1
> SYSTEM02 SYS SYSTEM
> 1 1 20209 57344 57344 2 505
> 0 ONLINE 1
> R01 SYS RBS
> 2 3 2 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R02 SYS RBS
> 3 3 42 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R03 SYS RBS
> 4 3 82 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R04 SYS RBS
> 5 3 122 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R05 SYS RBS
> 6 3 3826 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R06 SYS RBS
> 7 3 3858 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R07 SYS RBS
> 8 3 3890 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
> R08 SYS RBS
> 9 3 3922 131072 131072 2 2147483645
> 0 NEEDS RECOVERY 3
 |
 |