Oracle 11g 資料庫恢復-場景1：所有的資料檔案損壞，OPEN狀態

--檢視資料檔案

sys@TESTDB11>select name from v$datafile;

NAME

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

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

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

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

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

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

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

6 rows selected.

--1. 刪除所有的資料檔案

[oracle@S1011:/backup/inconsistent_backup]$ cd /u01/app/oracle/oradata/TestDB11/

[oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ ls

control01.ctl  example01.dbf  redo01.log     redo02.log     redo03.log     rotbs01.dbf    sysaux01.dbf   system01.dbf   temp01.dbf     undotbs01.dbf  users01.dbf

[oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ rm *.dbf

[oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ ls

control01.ctl  redo01.log     redo02.log     redo03.log

--2. 資料庫處於OPEN狀態

sys@TESTDB11>select status from v$instance;

STATUS

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

OPEN

--3. 此時正常關庫是關不了的,只能shutdown abort了

sys@TESTDB11>startup 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.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/app/oracle/oradata/TestDB11/system01.dbf'

--4.檢視錯誤資訊

sys@TESTDB11>col error for a15

sys@TESTDB11>select * from v$recover_file;

     FILE#  ONLINE   ONLINE_  ERROR               CHANGE# TIME

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

           1 ONLINE  ONLINE   FILE NOT FOUND             0

           2 ONLINE  ONLINE   FILE NOT FOUND             0

           3 ONLINE  ONLINE   FILE NOT FOUND             0

           4 ONLINE  ONLINE   FILE NOT FOUND             0

           5 ONLINE  ONLINE   FILE NOT FOUND             0

           6 ONLINE  ONLINE   FILE NOT FOUND             0

6 rows selected.

--檢視資料檔案頭資訊, 因為檔案都已經被刪除,所以顯示的均為0

sys@TESTDB11>col name for a15

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME            CHECKPOINT_CHANGE#

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

                                 0

                                 0

                                 0

                                 0

                                 0

                                 0

6 rows selected.

--從非一致備份進行檔案的還原

oracle@S1011:/pooldisk02/backup01/inconsistent]$ cp *.dbf /u01/app/oracle/oradata/TestDB11/

--再次檢視錯誤的資訊，檢查點都不一致（因為當時做的是熱備份)

sys@TESTDB11>select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME

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

         1 ONLINE  ONLINE                     2654775 09-AUG-13

         2 ONLINE  ONLINE                     2654801 09-AUG-13

         3 ONLINE  ONLINE                     2654893 09-AUG-13

         4 ONLINE  ONLINE                     2654911 09-AUG-13

         5 ONLINE  ONLINE                     2654924 09-AUG-13

         6 ONLINE  ONLINE                     2654946 09-AUG-13

6 rows selected.

--此時開庫

sys@TESTDB11>alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/app/oracle/oradata/TestDB11/system01.dbf'

--執行完全恢復

sys@TESTDB11>recover database;

ORA-00279: change 2654775 generated at 08/09/2013 21:25:44 needed for thread 1

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

ORA-00280: change 2654775 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

auto

Log applied.

Media recovery complete.

--恢復完檢視效果,檢查點都恢復到一致的狀態

sys@TESTDB11>col name for a60

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

NAME                                                                    CHECKPOINT_CHANGE#

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

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

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

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

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

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

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

6 rows selected.

sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;

NAME                                                                    CHECKPOINT_CHANGE#

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

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

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

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

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

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

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

6 rows selected.

--此時可以進行正常的開庫

sys@TESTDB11>alter database open;

Database altered.