Oracle 11g 資料庫恢復:場景9-系統預設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.