删除undotbs后,数据库无法启动

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     744
Current log sequence           746

 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/oracle/oradata/yoon/system01.dbf
/u01/oracle/oradata/yoon/sysaux01.dbf
/u01/oracle/oradata/yoon/users01.dbf
/u01/oracle/oradata/yoon/vpro.dbf
/u01/oracle/oradata/yoon/yoon01.dbf
/u01/oracle/oradata/yoon/svrmg1_oid.dbf
/u01/oracle/oradata/yoon/system02.dbf
/u01/oracle/oradata/yoon/system03.dbf
/u01/oracle/oradata/yoon/system04.dbf
/u01/oracle/oradata/yoon/undotbs_01.dbf

10 rows selected.

 

[oracle@yoon yoon]$ ls
control01.ctl      control03.ctl.bak  redo02.log      sysaux01.dbf  system03.dbf      system04.dbf.bak  undotbs_01.dbf  yoon01.dbf
control01.ctl.bak  control04.ctl      redo03.log      system01.dbf  system03.dbf.bak  temp01.dbf        users01.dbf
control03.ctl      redo01.log         svrmg1_oid.dbf  system02.dbf  system04.dbf      temp02.dbf        vpro.dbf

 

[oracle@yoon yoon]$ mv undotbs_01.dbf undotbs_01.dbf.bak

 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2432699776 bytes
Database Buffers          889192448 bytes
Redo Buffers               16326656 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/u01/oracle/oradata/yoon/undotbs_01.dbf'

 

SQL> shutdown abort;
ORACLE instance shut down.

 

SQL> startup mount
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2432699776 bytes
Database Buffers          889192448 bytes
Redo Buffers               16326656 bytes
Database mounted.

 

SQL> alter database datafile '/u01/oracle/oradata/yoon/undotbs_01.dbf' offline drop;

Database altered.

 

SQL> alter database open;

Database altered.

 

SQL> create undo tablespace undotbs1 datafile '/u01/oracle/oradata/yoon/undotbs01.dbf' size 1g;

Tablespace created.

 

SQL> alter system set undo_tablespace='undotbs1' scope=spfile;

System altered.

SQL> drop tablespace undotbs01 including contents and datafiles;

Tablespace dropped.

 

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

 

SQL> create pfile from spfile;

File created.

 

SQL> startup
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2232960 bytes
Variable Size            2432699776 bytes
Database Buffers          889192448 bytes
Redo Buffers               16326656 bytes
Database mounted.
Database opened.

 

SQL> show parameter undo;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_management                      string                            AUTO
undo_retention                       integer                           900
undo_tablespace                      string                            undotbs1

若:
select tablespace_name,segment_name,status from dba_rollback_segs;     有needs recovery

[oracle@yoon dbs]$ pwd
/u01/oracle/product/11.2.0/db_1/dbs

[oracle@yoon dbs]$ ls
hc_yoon.dat  init.ora  inityoon.ora  lkYOON  orapwyoon  snapcf_yoon.f  spfileyoon.ora

编辑inityoon.ora,添加隐含参数,如下:
*._corrupted_rollback_segments='_SYSSMU1_1240252155$','_SYSSMU2_111974964$','_SYSSMU3_4004931649$','_SYSSMU4_1126976075$'

SQL>startup pfile='/u01/oracle/product/11.2.0/db_1/dbs/inityoon.ora';

SQL>drop tablespace undotbs01 including contents and datafiles;

SQL>shutdown immediate

将*._corrupted_rollback_segments='_SYSSMU1_1240252155$','_SYSSMU2_111974964$','_SYSSMU3_4004931649$','_SYSSMU4_1126976075$'删除

重建pfile,spfile

SQL>startup