Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Oracle version: 11.2.0.1

I have the following error: "Transaction recovery: lock conflict caught and ignored"

I can't upgrade my database now.

I had a RAC. Removed one node, but the undo tablesapce of the removed node left. Because it contained active rollback segments.

Now I want to drop this undo tablespace manually.

I want to mention that, I tried to offline this undo tablesapce and some SQL-s were returning the error message:

ORA-00376: file string cannot be read at this time

When I made the undo tablespace online then SQLs were returning results successfully.

I found Burleson's post about "Repair UNDO log corruption" and decided to follow these steps.

  1. select
    segment_name,
    status from
    dba_rollback_segs
    where
    tablespace_name='UNDOTBS2' and
    status = 'NEEDS RECOVERY';

Return 0 rows

Segments in UNDOTBS2 tablespace has OFFLINE and PARTLY AVAILABLE statuses.

  1. Bounce the instance with the hidden parameter “_offline_rollback_segments”, specifying the segment names in UNDOTBS2:

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$.....

  1. Bounce database, nuke the corrupt segment and tablespace:

SQL> drop rollback segment "_SYSSMU22$";

....

....

Rollback segment

dropped.

SQL > drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

I wander to know what problems can I encounter If I delete these rollback segments manually?

share|improve this question
    
Have you followed all of the steps above, or are they just examples? –  Phil Feb 7 at 8:58
    
You will get inconsistent database. Manual delete of rollback segment is last resort operation and Oracle support will not support such database. You have to fetch data from it and recreate. I'll look how to check which objects are changed in that rollback segment. Had somewhat similar problem some time ago. –  Mindaugas Riauba Feb 7 at 8:59
    
@Phil I am going to follow, these are just examples. –  kupa Feb 7 at 9:01
    
@kupa don't do this. Have you tried to wait till those segments become not active? E.g. UNDO_RETENTION may be holding those segments active. –  Mindaugas Riauba Feb 7 at 9:03
    
@MindaugasRiauba I have undo retention 24 hours. It is already 1 week I've deleted node from the RAC. –  kupa Feb 7 at 9:17
show 12 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.