corrupted undo tablespace

Shouldn't you be able to do something along the lines of:

Create new UNDO tablespace
Alter existing UNDO tablespace offline
Set new undo tablespace to be the new default
Make sure all existing transactions are done in the old UNDO tablespace (There's a v$ view to tell you this I believe
Drop old UNDO tablespace.

I've done this before but it's after midnight and I'm too tired to look up specifics.

Those should be the basic steps, I'll research more tomorrow if someone else hasn't given a more detail answer by then.

The cause is that UNDO_RETENTION is set to a big value.

--reduce the retnetention to 5 minutes


ALTER SYSTEM SET UNDO_RETENTION = 5;

--create a new ts for a temp undo


CREATE UNDO TABLESPACE undotbs_02
DATAFILE '/u02/ora_data/oradata/dbeta/undo02.dbf' SIZE 2M REUSE AUTOEXTEND ON;


--set the undo ts to new ts
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;


--wait for original undo ts to be freed up (no online segments).

select segment_name,status,tablespace_name from dba_rollback_segs
where status='ONLINE' and tablespace_name='UNDOTBS1';


--offline old ts

alter tablespace UNDOTBS1 offline;


-- drop old ts

drop tablespace UNDOTBS1 including contents and datafiles;

--recreate new default ts

create undo tablespace UNDOTBS1
datafile '/u02/ora_data/oradata/dbeta/undotbs01.dbf' size 200m reuse autoextend on next 10m;

--repoint undo to default again
ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS1;


--wait for temp undo ts to be freed up (no online segments).
 
select segment_name,status,tablespace_name from dba_rollback_segs
where status='ONLINE' and tablespace_name='UNDOTBS_02';

--offline the temp undo tablespace

alter tablespace undotbs_02 offline;

--drop temp undo

drop tablespace undotbs_02 including contents and datafiles;

--reset retnetion to normal default (whatever you use in init.ora)

ALTER SYSTEM SET UNDO_RETENTION = 10800;

Hi jean

if it was actual undo corruption none of the above would have helped you... Let me know if still you have the issue...An interesting topic for me :)

hi,

when i was trying to drop/offline the old undo tbs, i'm getting this error:

ORA-01548 active rollback segment 'string' found, terminate dropping tablespace

when i checked the segments of the old undo tbs, they are all offline except for one that status = needs recovery.

i tried dropping the segment but i can't.

pradeepgv03

thanks. although, not really sure what you meant by 'actual undo corruption'?

i'd really appreciate any help you can extend.

jean,
Please try what schwertner  suggested first. If it is not due that issue(flash Back) and due to corruption in undo segment I can investigate further   if you wish.

Pradeep

thanks guys..

i used the undocumented parameter offline_rollback_segments to solve the issue.