ORA-00474: SMON process terminated with error

ORA-00474: SMON process terminated with error

Applies to: 

Oracle Database 10gR2

Description:

I am going to clone database using RMAN backup. Backup has been successfully restored. When I am going to recover the database it ask me more archive logs to recover the database, then I did open resetlogs to but unable to open the database. Following error appear in the alert log file:

“ORA-00474: SMON process terminated with error” and database going to crash. After doing some research work I found the following solution:

Solution:

Shutdown the database and set the following parameters in init.ora parameter file:

*.undo_management='MANUAL'

#*.undo_tablespace='UNDOTBS2'                 -- comment the existing parameter

*._allow_resetlogs_corruption=true

*._allow_error_simulation=true

Mount the database in restrict mode using pfile:

startup restrict mount pfile='/d01/oracle/proddb/10.2.0/dbs/initEBSTEST.ora';

Drop the undo tablespace and datafiles:

alter database datafile '/data1/proddata/undotbs01.dbf' offline drop;

alter database datafile '/data1/proddata/undotbs02.dbf' offline drop;

alter database datafile '/data1/proddata/undotbs03.dbf' offline drop;

alter database datafile '/data1/proddata/undotbs04.dbf' offline drop;

alter database datafile '/data1/proddata/undotbs05.dbf' offline drop;

Now open the database:

alter database open;

Create a new undo tablespace:

CREATE UNDO TABLESPACE UNDOTBS3 DATAFILE '/data1/proddata/undotbs06.dbf' SIZE 10G reuse;

Shutdown the database:

shutdown immediate;

Now set the following parameters in init.ora parameter file:

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS3'

#*._allow_resetlogs_corruption=true                -- comment it

#*._allow_error_simulation=true                       -- comment it

Startup the database:

startup pfile='/d01/oracle/proddb/10.2.0/dbs/initEBSTEST.ora';

Drop tablespace undotbs2 including contents and datafiles;

SQL> drop tablespace undotbs2 including contents and datafiles;

drop tablespace undotbs2 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU31$' found, terminate dropping

Tablespace

Shutdown the database:

Shutdown immediate;

Set the following in init.ora parameter file as return by above statement:

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU31$

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU61$

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU69$

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU70$

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU97$

_OFFLINE_ROLLBACK_SEGMENTS=_SYSSMU101$

startup pfile='/d01/oracle/proddb/10.2.0/dbs/initEBSTEST.ora';

Now the database perfectly started and the issue has been resolved.

Your comments, especially which will help us improve the functionality, will be greatly appreciated :)