Oracle 11g 資料庫恢復-場景8：系統預設undo表空間資料檔案損壞,CLOSE狀態

--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.