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 :)
