Undo tablespace datafile has been corrupted with active rollback segments

by Snehashish Ghosh

Scenario


 Undo Tablespace has been corrupted with active rollback segment present in undo tablespace. 

 Recovery

Issue Identification


 The database has been started using services.msc

 But after logging into the database as sysdba privilege and issue of a command with dba_data_files and subsequent database status checking reveals that database has not been opened and is in mount state.

 Shutdown immediate and startup from the command prompt throws the following error:

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             629146064 bytes

Database Buffers          436207616 bytes

Redo Buffers                4603904 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 – see DBWR trace file

ORA-01110: data file 3: ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’

Steps to Recover the database from loss of undo data file with active rollback segment


 1. Check the undo_management parameter. make it manual if it is set to auto

SQL> show parameter undo

 NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_management=manual scope = spfile;

System altered.

2. To enable the manual undo tablespace parameter Shutdown & startup the database

shutdowm immediate;

startup;

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

——— ——————–

ORCL      MOUNTED

3. Drop the undo datafile so that the information about the missing undo file is removed from control file as a mandatory file to open the database.

SQL> alter database datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS01.DBF’ offline drop;

Database altered.

4. As the undo datafile is dropped the database can now be opened.

SQL> alter database open;

Database altered.

 5. check the name of the undo tablespace and drop  and recreate a new undo tablespace.

SQL> show parameter undo

NAME                                 TYPE        VALUE

———————————— ———– —————————–

undo_management                      string      MANUAL

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

SQL> drop tablespace undotbs1;

drop tablespace undotbs1

*

ERROR at line 1:

ORA-01548: active rollback segment ‘_SYSSMU1_1518548437$’ found, terminate

dropping tablespace

6. Check the details of the active rollback segments

SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status=’NEEDS RECOVERY’;

 

SEGMENT_NAME                   STATUS           TABLESPACE_NAME

—————————— —————- ——————————

_SYSSMU1_1518548437$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU2_2082490410$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU3_991555123$            NEEDS RECOVERY   UNDOTBS1

_SYSSMU4_2369290268$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU5_1018230376$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU6_1834113595$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU7_137577888$            NEEDS RECOVERY   UNDOTBS1

_SYSSMU8_1557854099$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU9_1126410412$           NEEDS RECOVERY   UNDOTBS1

_SYSSMU10_3176102001$          NEEDS RECOVERY   UNDOTBS1

 10 rows selected.

7. Create pfile from spfile

SQL> create pfile=’c:\pfile.ora’ from spfile;

File created.

8. Edit the pfile

       8.1  Find the name of the active rollback segments in the corrupted undo tablespace

SQL> select ”” || segment_name || ”’,’ from dba_rollback_segs where status=’NEEDS RECOVERY’;

_corrupted_rollback_segments =(‘_SYSSMU1_1518548437$’,

‘_SYSSMU2_2082490410$’,

‘_SYSSMU3_991555123$’,

‘_SYSSMU4_2369290268$’,

‘_SYSSMU5_1018230376$’,

‘_SYSSMU6_1834113595$’,

‘_SYSSMU7_137577888$’,

‘_SYSSMU8_1557854099$’,

‘_SYSSMU9_1126410412$’,

‘_SYSSMU10_3176102001$’)

9. startup pfile=’c:\pfile.ora’

10. Drop the corrupted rollback segments

SQL> select ‘drop rollback segment “‘ || segment_name || ‘”;’ from dba_rollback_segs where status=’NEEDS RECOVERY’;

drop rollback segment “_SYSSMU1_1518548437$”;

drop rollback segment “_SYSSMU2_2082490410$”;

drop rollback segment “_SYSSMU3_991555123$”;

drop rollback segment “_SYSSMU4_2369290268$”;

drop rollback segment “_SYSSMU5_1018230376$”;

drop rollback segment “_SYSSMU6_1834113595$”;

drop rollback segment “_SYSSMU7_137577888$”;

drop rollback segment “_SYSSMU8_1557854099$”;

drop rollback segment “_SYSSMU9_1126410412$”;

drop rollback segment “_SYSSMU10_3176102001$”;

11.  drop and recreate the undo tablespace undotbs1.

SQL> drop tablespace undotbs1;

 Tablespace dropped.

SQL> create undo tablespace undotbs1 datafile ‘E:\app\Administrator\oradata\orcl\undotbs01.dbf’ size 60M autoextend on next 2M maxsize 2048M;

12. create spfile from the temporary pfile created.

 SQL> create spfile from pfile=’c:\pfile.ora’;

File created.

13. shutdown,startup, set undo_management to auto.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

 

Total System Global Area 1071333376 bytes

Fixed Size                  1375792 bytes

Variable Size             629146064 bytes

Database Buffers          436207616 bytes

Redo Buffers                4603904 bytes

Database mounted.

Database opened.

SQL> alter system set undo_management=auto scope=spfile;

 

System altered.

14. Shutdown immediate and startup the database in automatic undo management mode.

Conclusion


 If undo datafile gets corrupted when active rollback segments are present in the undo tablespace the following high level steps need to be performed to recover the undo tablespace.

  • Switch the database undo management to manual.
  • Find out the list of active rollback segments as registered in the database control file.
  • Edit the pfile to include the _corrupted_rollback_segments information
  • use the pfile to start the database
  • drop all the corrupted rollback segments
  • drop the undo tablespace
  • recreate the undo tablespace
  • convert the database in automatic undo management mode 

 

2 comments to Undo tablespace datafile has been corrupted with active rollback segments

  • Henri Charles  says:

    Nice post and thanks for mentioning the detailed steps.

  • mohit jain  says:

    Thanks..great post…had one query…does it means all the undo data that was needed for media recovery will be lost and the sessions which needed to be rollbacked won’t happen because of dropping all the corrupted blocks that possessed those information.

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*