solving the problem of wait for a undo record

troubleshooting database Hits: 100

A while ago, I encountered the "wait for a undo record" issue from the "Other" category with around 400 concurrent sessions in an Oracle database. This issue caused some of the Undo Segments to remain stuck and the regular database operations were disrupted.

Steps to Resolve the Issue

At first, I noticed that although the number of undo segments was shown in the v$rollname view, a join query with the v$transaction view yielded no results. Therefore, I decided to create a new undo tablespace.

Next, I realized that around 280,000 extents from the previous undo tablespace with the same undo segment number or USN remained unchanged. These undo segments were still active and had been extracted from the v$rollname view.

Before starting any operation, you can use the following query to check the status of the undo segments:

select segment_id, tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

In my case, the status remained Pending Offline, and no change in the number of undo segments was observed. Following the steps below helped to fully resolve the issue.

To resolve this issue, I followed these steps:

  1. Created a new PFILE with the following parameters:

     
    *.undo_management=manual
    *._offline_rollback_segments=(UNDO SEGMENT NAME)
    *.undo_tablespace=undo

    (UNDO SEGMENT NAME should be replaced with the name from the v$rollname view.)

  2. Restarted the database in restrict mode:

     
    sqlplus / as sysdba
    startup restrict pfile='NEW PFILE'

  3. Dropped the problematic rollback segment and the related tablespace:

     
    drop rollback segment "UNDO SEGMENT NAME";
    drop Tablespace undo including contents and datafiles;

  4. If you haven’t created the new undo tablespace yet, you can create it here and apply the necessary changes in the main SPFILE.

Suggestions for High-Load Databases

For high-traffic databases, it is recommended to use the following parameters in the configuration file to avoid further issues with UNDO:

job_queue_processes=0

Additionally, start the database in restrict mode to perform the operations without causing any further issues.