Correction of Corrupted UNDO tablespace.

dbtut

6 years ago

If the Oracle UNDO table space is corrupted, it may be possible to get the error as follows.

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

When UNDO segments corrupted, the following should be done:

The undo_management parameter must be MANUAL.
A new UNDO tablespace must be created.
The problematic UNDO tablespace should be removed.
The undo_tablespace parameter must be modified to be the newly created UNDO tablespace.

Correction of corrupted UNDO tablespace step by step:

1- Identify the corrupted segments. The following query can be used to detect these segments.

select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name='<sorunlu undo tablo alanı>'
and
status = ‘NEEDS RECOVERY’;

SEGMENT_NAME STATUS
------------------------------ ----------------
_SYSSMU22$ NEEDS RECOVERY

select

segment_name,

status

from

dba_rollback_segs

where

tablespace_name='<sorunlu undo tablo alanı>'

and

status = ‘NEEDS RECOVERY’;

SEGMENT_NAME STATUS

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

_SYSSMU22$ NEEDS RECOVERY

2. Close the database.
3- You must change the corresponding pfile’s undo_management parameter to MANUAL and Change the value of the “_offline_rollback_segments” hidden parameter to be problematic segments.

Rows to add to the Pfile file:

UNDO_MANAGEMENT=MANUAL

1	UNDO_MANAGEMENT=MANUAL

# If there is one corrupted segment:

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$

1	_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU22$

# If there is more than one corrupted segment:

_OFFLINE_ROLLBACK_SEGMENTS=(‘_SYSSMU22$’,‘_SYSSMU23$’, ‘_SYSSMU24$’)

1	_OFFLINE_ROLLBACK_SEGMENTS=(‘_SYSSMU22$’,‘_SYSSMU23$’, ‘_SYSSMU24$’)

4- Open the database in MOUNT mode and with RESTRICT.

SQL > STARTUP RESTRICT MOUNT pfile=<ORACLE_HOME>/dbs/initORCL.ora

1	SQL > STARTUP RESTRICT MOUNT pfile=<ORACLE_HOME>/dbs/initORCL.ora

5- Remove all corrupted segments and undo tablespace.

SQL> drop rollback segment "_SYSSMU22$";

1	SQL> drop rollback segment "_SYSSMU22$";

Rollback segment dropped.

SQL > drop tablespace undotbs including contents and datafiles;

1	SQL > drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

6- Create new undo tablespace.

SQL > CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE ’/oradata/orcl/UNDOTBS01.DBF’ SIZE 100M ;

1	SQL > CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE ’/oradata/orcl/UNDOTBS01.DBF’ SIZE 100M ;

7- Close the database.

SOL> shutdown immediate

1	SOL> shutdown immediate

8- Delete the line _OFFLINE_ROLLBACK_SEGMENTS from the rows added in the Pfile, and UNDO_MANAGEMENT = AUTO.

If the name of the newly created undo tablespace is different from the deleted one, change it to undo_tablespace = <NEW_UNDOTBS1>.

9. Open the database normally from the pfile.

SQL > STARTUP pfile=<ORACLE_HOME>/dbs/initORCL.ora

1	SQL > STARTUP pfile=<ORACLE_HOME>/dbs/initORCL.ora

If you need to use Spfile after opening you can create the spfile with the help of the following script;

SQL > create spfile from pfile ;

1	SQL > create spfile from pfile ;