FILE# STATUS
---------- -------
1 SYSTEM
2 ONLINE
3 OFFLINE
4 ONLINE
5 ONLINE
SQL> shutdown immediate;
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/u02/oracle/JINGYU/datafile/o1_mf_undotbs1_bwp19o3n_.dbf'
2.2.5 此时删除回滚段也会提示失败
目前数据库无有效备份，需要把这些NEEDS RECOVERY的undo rollback segs删除
SQL> drop rollback segment "_SYSSMU10_1695440836$";
drop rollback segment "_SYSSMU10_1695440836$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU10_1695440836$' (in undo tablespace) not allowed
2.3 undo数据文件3所在的undotbs1表空间删除方法
2.3.1 修改pfile文件这几行内容
其中_offline_rollback_segments参数中的回滚段的名字在上面查到过。
*.undo_management='MANUAL'
*._offline_rollback_segments=(_SYSSMU1_1401565358$,_SYSSMU2_3125365238$,_SYSSMU3_1538315859$,_SYSSMU4_1640924022$,_SYSSMU5_2892967416$,_SYSSMU6_3276341082$,_SYSSMU7_387283697$,_SYSSMU8_2299136685$,_SYSSMU9_909303715$,_SYSSMU10_1695440836$)
#*.undo_tablespace='undotbs2'
2.3.2 使用pfile文件启动数据库
SQL> startup pfile='/tmp/pfile.ora';
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.
2.3.3 删除回滚段成功
SQL> drop rollback segment "_SYSSMU10_1695440836$";
Rollback segment dropped.
2.3.4 删除回滚表空间undotbs1成功
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
2.3.5 此时查询数据库的相关信息
SQL> select segment_id, segment_name,status,tablespace_name from dba_rollback_segs where status not in ('ONLINE','OFFLINE')
2 ;
no rows selected
SQL> select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "MB", MAXBYTES/1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME MB GB AUT STATUS ONLINE_
---------- -------------------------------------------------------- ------------------------------ ---------- ---------- --- --------- -------
1 /u02/oracle/JINGYU/datafile/o1_mf_system_bwp198r7_.dbf SYSTEM 700 31.9999847 YES AVAILABLE SYSTEM
2 /u02/oracle/JINGYU/datafile/o1_mf_sysaux_bwp19hl8_.dbf SYSAUX 600 31.9999847 YES AVAILABLE ONLINE
4 /u02/oracle/JINGYU/datafile/o1_mf_undotbs2_bwqxbnxo_.dbf UNDOTBS2 100 31.9999847 YES AVAILABLE ONLINE
5 /u02/oracle/JINGYU/datafile/o1_mf_dbs_d_ji_bwp4r7cm_.dbf DBS_D_JINGYU 100 31.9999847 YES AVAILABLE ONLINE
发现此时一切数据文件正常,此时已经可以正常关库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2.3.6 以spfile正常启动数据库，检查一切正常
SQL> startup
ORACLE instance started.
Total System Global Area 1620115456 bytes
Fixed Size 2253704 bytes
Variable Size 989858936 bytes
Database Buffers 620756992 bytes
Redo Buffers 7245824 bytes
Database mounted.
Database opened.
SQL> show parameter undo