Oracle 11g 数据库恢复:场景9-系统默认undo表空间数据文件损坏

场景描述：数据库处于CLOSE状态,但有>=2个undo表空间  高可用方式

与场景8的区别：直接脱机开库，而没有在开库之前将表空间切换到一个可用的undo表空间，这样当前无默认的undo表空间。

-- 0.1 一致关库

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--0.2 删除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'

 

--脱机

sys@TESTDB11>alter database datafile 3 offline;

 

Database altered.

--启库

sys@TESTDB11>alter database open;

 

Database altered.

 

--但此时会有问题（正常的查询操作没问题，但更新数据就会有问题）

scott@TESTDB11>conn scott/scott

Connected.

scott@TESTDB11>select * from dept;

 

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

scott@TESTDB11>update dept set dname = 'DNameD' where deptno = 40;

update dept set dname = 'DNameD' where deptno = 40

       *

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

 

--但在系统表空间下没问题

scott@TESTDB11>conn / as sysdba

Connected.

sys@TESTDB11>create table test_users as select * from dba_users;

 

Table created.

 

sys@TESTDB11>delete from  test_users;

 

39 rows deleted.

 

--切换一下undo表空间

sys@TESTDB11>alter system set undo_tablespace=newundotbs;

 

System altered.

 

--此时就可以操作了

scott@TESTDB11>delete from dept where deptno = 40;

 

1 row deleted.

 

--还原，恢复

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-1153191/，如需转载，请注明出处，否则将追究法律责任。

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