ORA-01548: active rollback segment ‘_SYSSMU1$’ found

情景：前段时间测试丢失undo数据文件，rm删除的undo1 的表空间数据文件，并建了个undo2设为数据库默认，今天删除一个用户报错

SQL> drop user icme cascade;
drop user icme cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf’

SQL> select file_name,tablespace from dba_data_files;
select file_name,tablespace from dba_data_files
                 *
ERROR at line 1:
ORA-00904: “TABLESPACE”: invalid identifier
 
 
SQL> select file_name,tablespace_name from dba_data_files;
 
FILE_NAME                                                                        TABLESPACE_NAME
——————————————————————————– ————————————————————
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_6cgckxds_.dbf                  USERS
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_6cgckx9p_.dbf                 SYSAUX
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6cgckx9x_.dbf               UNDOTBS1
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_6cgckx95_.dbf                 SYSTEM
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_6cgckxc7_.dbf                EXAMPLE
/u01/app/oracle/oradata/ORCL/datafile/tt.dbf                                     TT
+ASMDATA/asmts01.dbf                                                             ASMTS
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs4_6cgcmnfg_.dbf                   TBS4
/u01/app/oracle/oradata/ORCL/datafile/tt1.dbf                                    TT
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_tbs1_6okmoslq_.dbf                   TBS1
/u01/app/oracle/oradata/ORCL/datafile/undotbs2.dbf                               UNDOTBS2
 
11 rows selected.
 
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-01548: active rollback segment ‘_SYSSMU1$’ found, terminate dropping tablespace
 
 
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
 
SEGMENT_NAME                                                 STATUS                           TABLESPACE_NAME
———————————————————— ——————————– ————————————————————
SYSTEM                                                       ONLINE                           SYSTEM
_SYSSMU1$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU2$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU3$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU4$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU5$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU6$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU7$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU8$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU9$                                                    NEEDS RECOVERY                   UNDOTBS1
_SYSSMU10$                                                   NEEDS RECOVERY                   UNDOTBS1
 
SEGMENT_NAME                                                 STATUS                           TABLESPACE_NAME
———————————————————— ——————————– ————————————————————
_SYSSMU11$                                                   ONLINE                           UNDOTBS2
_SYSSMU12$                                                   ONLINE                           UNDOTBS2
_SYSSMU13$                                                   ONLINE                           UNDOTBS2
_SYSSMU14$                                                   ONLINE                           UNDOTBS2
_SYSSMU15$                                                   ONLINE                           UNDOTBS2
_SYSSMU16$                                                   ONLINE                           UNDOTBS2
_SYSSMU17$                                                   ONLINE                           UNDOTBS2
_SYSSMU18$                                                   ONLINE                           UNDOTBS2
_SYSSMU19$                                                   ONLINE                           UNDOTBS2
_SYSSMU20$                                                   ONLINE                           UNDOTBS2
 
21 rows selected.
 
SQL> ! ls /u01/app/oracle/oradata/ORCL/datafile/
o1_mf_example_6cgckxc7_.dbf  o1_mf_system_6cgckx95_.dbf  o1_mf_tbs4_6cgcmnfg_.dbf  o1_mf_users_6cgckxds_.dbf  temp2.tmp  tt.dbf
o1_mf_sysaux_6cgckx9p_.dbf   o1_mf_tbs1_6okmoslq_.dbf    o1_mf_temp_6cgcv90w_.tmp  tbsg1.gdbf                 tt1.dbf    undotbs2.dbf
 
sql>alter system set undo_management=manual;
sql>create pfile from spfile;
sql>shutdown immediate
修改启动参数initorcl.ora
*._CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
或
*._offline_rollback_segments=”_SYSSMU1$”,”_SYSSMU2$”,”_SYSSMU3$”,”_SYSSMU4$”,”_SYSSMU5$”,”_SYSSMU6$”,”_SYSSMU7$”,”_SYSSMU8$”,”_SYSSMU9$”,”_SYSSMU10$”
sql>starup pfile=’xxx’;

如果上面是配置的offline_rollback,就需要多做一下

sql>drop rollback segment _SYSSMU[N]$;…
sql>drop tablespace undotbs1 including contents;
sql>drop user icme cascade;

完成