场景7：部分文件损坏(系统默认undo表空间数据文件)恢复,OPEN状态

sys@TESTDB11>show parameter undo_tablespace

NAME                                 TYPE        VALUE

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

undo_tablespace                      string      UNDOTBS1

sys@TESTDB11>select tablespace_name, contents from dba_tablespaces;

TABLESPACE_NAME                CONTENTS

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

SYSTEM                         PERMANENT

SYSAUX                         PERMANENT

UNDOTBS1                       UNDO

TEMP                           TEMPORARY

USERS                          PERMANENT

EXAMPLE                        PERMANENT

ROTBS                          PERMANENT

7 rows selected.

--删除数据文件

sys@TESTDB11>!rm /u01/app/oracle/oradata/TestDB11/undotbs01.dbf

--确定undo表示空间数据文件编号,为3号文件

sys@TESTDB11>select file#, name from v$datafile;

     FILE# NAME

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

         1 /u01/app/oracle/oradata/TestDB11/system01.dbf

         2 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf

         3 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf

         4 /u01/app/oracle/oradata/TestDB11/users01.dbf

         5 /u01/app/oracle/oradata/TestDB11/example01.dbf

         6 /u01/app/oracle/oradata/TestDB11/rotbs01.dbf

6 rows selected.

--不可以使undo表空间脱机

sys@TESTDB11>alter database datafile 3 offline;

alter database datafile 3 offline

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf'

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf'

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf'

Process ID: 8003

Session ID: 1 Serial number: 5

--尝试创建新的undo表空间,也不可以

 sys@TESTDB11>create tablespace undotbs2 datafile '/u01/app/oracle/oradata/TestDB11/undotbs201.dbf' size  200m;

ERROR:

ORA-03114: not connected to ORACLE

sys@TESTDB11>conn / as sysdba

Connected.

sys@TESTDB11>create tablespace undotbs2 datafile '/u01/app/oracle/oradata/TestDB11/undotbs201.dbf' size  200m;

create tablespace undotbs2 datafile '/u01/app/oracle/oradata/TestDB11/undotbs201.dbf' size  200m

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf'

-- 关库，重新启动到MOUNT状态

sys@TESTDB11>startup mount force;

ORACLE instance started.

Total System Global Area  855982080 bytes

Fixed Size                  2230792 bytes

Variable Size             641730040 bytes

Database Buffers          209715200 bytes

Redo Buffers                2306048 bytes

Database mounted.

--还原

sys@TESTDB11>!cp /backup/inconsistent_backup/undotbs01.dbf /u01/app/oracle/oradata/TestDB11

--恢复

sys@TESTDB11>recover datafile 3;

ORA-00279: change 2654893 generated at 08/09/2013 21:27:06 needed for thread 1

ORA-00289: suggestion : /archive2/1_98_813665348.dbf

ORA-00280: change 2654893 for thread 1 is in sequence #98

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 2660981 generated at 08/09/2013 22:19:48 needed for thread 1

ORA-00289: suggestion : /archive2/1_99_813665348.dbf

ORA-00280: change 2660981 for thread 1 is in sequence #99

ORA-00279: change 2667783 generated at 08/10/2013 00:00:55 needed for thread 1

ORA-00289: suggestion : /archive2/1_100_813665348.dbf

ORA-00280: change 2667783 for thread 1 is in sequence #100

ORA-00279: change 2679804 generated at 08/10/2013 03:00:28 needed for thread 1

ORA-00289: suggestion : /archive2/1_101_813665348.dbf

ORA-00280: change 2679804 for thread 1 is in sequence #101

ORA-00279: change 2699110 generated at 08/10/2013 08:29:58 needed for thread 1

ORA-00289: suggestion : /archive2/1_102_813665348.dbf

ORA-00280: change 2699110 for thread 1 is in sequence #102

ORA-00279: change 2725650 generated at 08/10/2013 10:27:18 needed for thread 1

ORA-00289: suggestion : /archive2/1_103_813665348.dbf

ORA-00280: change 2725650 for thread 1 is in sequence #103

ORA-00279: change 2726122 generated at 08/10/2013 10:29:03 needed for thread 1

ORA-00289: suggestion : /archive2/1_104_813665348.dbf

ORA-00280: change 2726122 for thread 1 is in sequence #104

ORA-00279: change 2726220 generated at 08/10/2013 10:32:28 needed for thread 1

ORA-00289: suggestion : /archive2/1_105_813665348.dbf

ORA-00280: change 2726220 for thread 1 is in sequence #105

Log applied.

Media recovery complete.

--开库

sys@TESTDB11>alter database open;

Database altered.

--但此时undo表空间显示为脱机（因为刚才做了一下脱机操作)

sys@TESTDB11>select name, status from v$datafile;

NAME                                               STATUS

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

/u01/app/oracle/oradata/TestDB11/system01.dbf      SYSTEM

/u01/app/oracle/oradata/TestDB11/sysaux01.dbf      ONLINE

/u01/app/oracle/oradata/TestDB11/undotbs01.dbf     OFFLINE

/u01/app/oracle/oradata/TestDB11/users01.dbf       ONLINE

/u01/app/oracle/oradata/TestDB11/example01.dbf     ONLINE

/u01/app/oracle/oradata/TestDB11/rotbs01.dbf       ONLINE

6 rows selected.

--使其联机

sys@TESTDB11>alter database datafile 3 online;

Database altered.