1环境准备

SYS@test> show parameter undo_tablespace

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1

 

SYS@test> col file_name for a50

SYS@test> col tablespace_name for a20

SYS@test> set pagesize 999

SYS@test> select file_name,tablespace_name from dba_data_files where tablespace_name='UNDOTBS2';

 

FILE_NAME                                          TABLESPACE_NAME

-------------------------------------------------- --------------------

/home/oracle/undotbs2.dbf                          UNDOTBS2

 

SYS@test> ho rm /home/oracle/undotbs2.dbf

 

SYS@test> alter system checkpoint;

alter system checkpoint

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 68426

Session ID: 125 Serial number: 5

 

2发现数据库宕机了

 

SYS@test> startup

ORACLE instance started.

 

Total System Global Area  977076224 bytes

Fixed Size                  2259200 bytes

Variable Size             578815744 bytes

Database Buffers          390070272 bytes

Redo Buffers                5931008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

ORA-01110: data file 10: '/home/oracle/undotbs2.dbf'

 

查看告警日志：

[oracle@zyx ~]$ tail -n 100 /u01/app/oracle/diag/rdbms/orcl/test/trace/alert_test.log

Fri Apr 29 15:50:50 2016

ALTER DATABASE OPEN

Errors in file /u01/app/oracle/diag/rdbms/orcl/test/trace/test_dbw0_68992.trc:

ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

ORA-01110: data file 10: '/home/oracle/undotbs2.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

3处理方法

SYS@test> startup

ORACLE instance started.

 

Total System Global Area  977076224 bytes

Fixed Size                  2259200 bytes

Variable Size             578815744 bytes

Database Buffers          390070272 bytes

Redo Buffers                5931008 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

ORA-01110: data file 10: '/home/oracle/undotbs2.dbf'

 

SYS@test> alter database datafile 10 offline;

Database altered.

 

SYS@test> alter database open;

Database altered.

 

SYS@test> show parameter undo_table

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS2

SYS@test> create undo tablespace undotbs1 datafile '/home/oracle/undotbs01.dbf' size 2G;

Tablespace created.

 

SYS@test> alter system set undo_tablespace='undotbs1' scope=spfile;

System altered.

 

SYS@test> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SYS@test> startup

ORACLE instance started.

Total System Global Area  977076224 bytes

Fixed Size                  2259200 bytes

Variable Size             578815744 bytes

Database Buffers          390070272 bytes

Redo Buffers                5931008 bytes

Database mounted.

Database opened.

 

SYS@test> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC

---------- ------------------------------ --- --- --- ---

         0 SYSTEM                         YES NO  YES

         1 SYSAUX                         YES NO  YES

         2 UNDOTBS1                       YES NO  YES

         4 USERS                          YES NO  YES

         3 TEMP                           NO  NO  YES

         6 TEMP1                          NO  NO  YES

         7 HCT                            YES NO  YES

         9 base-data                      YES NO  YES

        10 BASE-DATA                      YES NO  YES

        11 TEMP2                          NO  NO  YES

         5 UNDOTBS2                       YES NO  YES

 

11 rows selected.

 

SYS@test> show parameter undo_table

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      undotbs1

 

4 undo表空间NEEDS RECOVERY处理

SYS@test> drop tablespace undotbs2;

drop tablespace undotbs2

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU22_4040055074$' found, terminate

dropping tablespace

 

SYS@test> drop tablespace undotbs2 including contents and datafiles;

drop tablespace undotbs2 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU22_4040055074$' found, terminate

dropping tablespace

 

SYS@test> select FILE#,STATUS,NAME from v$datafile;

 

     FILE# STATUS  NAME

         1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf

         2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf

         3 ONLINE  /home/oracle/undotbs01.dbf

         4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf

         5 ONLINE  /home/oracle/hct.dbf

         7 ONLINE  /home/oracle/base-data.dbf

         8 ONLINE  /home/oracle/zj.ora

        10 RECOVER /home/oracle/undotbs2.dbf

 

8 rows selected.

 

SYS@test> alter database datafile '/home/oracle/undotbs2.dbf' offline drop;

Database altered.

 

 

SYS@test> select segment_name,tablespace_name,status from dba_rollback_segs;

 

SEGMENT_NAME                   TABLESPACE_NAME                STATUS

------------------------------ ------------------------------ ----------------

SYSTEM                         SYSTEM                         ONLINE

_SYSSMU21_530738420$           UNDOTBS1                       ONLINE

_SYSSMU20_340940289$           UNDOTBS1                       ONLINE

_SYSSMU19_2984358614$          UNDOTBS1                       ONLINE

_SYSSMU17_864280657$           UNDOTBS1                       ONLINE

_SYSSMU16_3128204265$          UNDOTBS1                       ONLINE

_SYSSMU15_4124051312$          UNDOTBS1                       ONLINE

_SYSSMU14_1335307170$          UNDOTBS1                       ONLINE

_SYSSMU13_2136535059$          UNDOTBS1                       ONLINE

_SYSSMU12_1296073816$          UNDOTBS1                       ONLINE

_SYSSMU11_3432232028$          UNDOTBS1                       ONLINE

_SYSSMU31_245157227$           UNDOTBS2                       NEEDS RECOVERY

_SYSSMU30_2655736830$          UNDOTBS2                       NEEDS RECOVERY

_SYSSMU29_731680127$           UNDOTBS2                       NEEDS RECOVERY

_SYSSMU28_2504105475$          UNDOTBS2                       NEEDS RECOVERY

_SYSSMU27_175497172$           UNDOTBS2                       NEEDS RECOVERY

_SYSSMU26_159549991$           UNDOTBS2                       NEEDS RECOVERY

_SYSSMU25_1509643266$          UNDOTBS2                       NEEDS RECOVERY

_SYSSMU24_17393810$            UNDOTBS2                       NEEDS RECOVERY

_SYSSMU23_1078616206$          UNDOTBS2                       NEEDS RECOVERY

_SYSSMU22_4040055074$          UNDOTBS2                       NEEDS RECOVERY

 

21 rows selected.

 

SYS@test> create pfile='/home/oracle/pfiletest.ora' from spfile;

File created.

 

SYS@test> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

修改pfile，增加一行：

SYS@test> ho vim pfiletest.ora

_corrupted_rollback_segments='_SYSSMU31_245157227$','_SYSSMU30_2655736830$','_SYSSMU29_731680127$','_SYSSMU28_2504105475$','_SYSSMU27_175497172$','_SYSSMU26_159549991$','_SYSSMU25_1509643266$','_SYSSMU24_17393810$','_SYSSMU23_1078616206$','_SYSSMU22_4040055074$'

 

SYS@test> startup pfile='pfiletest.ora'

ORACLE instance started.

Total System Global Area  977076224 bytes

Fixed Size                  2259200 bytes

Variable Size             578815744 bytes

Database Buffers          390070272 bytes

Redo Buffers                5931008 bytes

Database mounted.

Database opened.

SYS@test>

 

SYS@test> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

 

 

SYS@test> select segment_name,tablespace_name,status from dba_rollback_segs;

SEGMENT_NAME                   TABLESPACE_NAME                STATUS

------------------------------ ------------------------------ ----------------

SYSTEM                         SYSTEM                         ONLINE

_SYSSMU21_530738420$           UNDOTBS1                       ONLINE

_SYSSMU20_340940289$           UNDOTBS1                       ONLINE

_SYSSMU19_2984358614$          UNDOTBS1                       ONLINE

_SYSSMU17_864280657$           UNDOTBS1                       ONLINE

_SYSSMU16_3128204265$          UNDOTBS1                       ONLINE

_SYSSMU15_4124051312$          UNDOTBS1                       ONLINE

_SYSSMU14_1335307170$          UNDOTBS1                       ONLINE

_SYSSMU13_2136535059$          UNDOTBS1                       ONLINE

_SYSSMU12_1296073816$          UNDOTBS1                       ONLINE

_SYSSMU11_3432232028$          UNDOTBS1                       ONLINE

11 rows selected.

SYS@test> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@test> startup

ORACLE instance started.

Total System Global Area  977076224 bytes

Fixed Size                  2259200 bytes

Variable Size             578815744 bytes

Database Buffers          390070272 bytes

Redo Buffers                5931008 bytes

Database mounted.

Database opened.

SYS@test>