Oracle 11g 数据库恢复-场景8：系统默认undo表空间数据文件损坏,CLOSE状态

场景8：系统默认undo表空间数据文件损坏,CLOSE状态,但有>=2个undo表空间  低可用

--0.1 创建新的undo表空间（此时数据库是OPEN状态） sys@TESTDB11>create undo tablespace newundotbs datafile '/u01/app/oracle/oradata/TestDB11/newundotbs01.dbf' size 300m;   Tablespace created.   --0.2 查看undo表空间 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 NEWUNDOTBS                     UNDO --0.3 关库 sys@TESTDB11>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. --0.4 删除默认undo表空间数据文件 sys@TESTDB11>!rm /u01/app/oracle/oradata/TestDB11/undotbs01.dbf --尝试启动 sys@TESTDB11>startup 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. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3: '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf'   --修改默认的undo表空间（此时不可以在实例上直接修改） sys@TESTDB11>alter system set undo_tablespace='NEWUNDOTBS'; alter system set undo_tablespace='NEWUNDOTBS' * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-01219: database not open: queries allowed on fixed tables/views only     sys@TESTDB11>alter system set undo_tablespace = NEWUNDOTBS scope=spfile;   System altered. --再次重启 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. --查看默认的undo表空间 sys@TESTDB11>show parameter undo_tablespace   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_tablespace                      string      NEWUNDOTBS   --脱机开库 sys@TESTDB11>alter database datafile 3 offline;   Database altered.   sys@TESTDB11>alter database open;   Database altered.   --还原，恢复 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     ORA-00279: change 2726249 generated at 08/10/2013 10:33:07 needed for thread 1 ORA-00289: suggestion : /archive2/1_106_813665348.dbf ORA-00280: change 2726249 for thread 1 is in sequence #106     Log applied. Media recovery complete.   --联机 sys@TESTDB11>alter database datafile 3 online;   Database altered.   --再切换回来 sys@TESTDB11>alter system set undo_tablespace = undotbs1;   System altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1151914/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1151914/