问题描述
情景:前段时间测试丢失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;
完成
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。