Oralce 11g資料庫恢復-場景3：部分檔案損壞恢復,關庫狀態,高可用恢復方式

sys@TESTDB11>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

--刪除檔案

[oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ rm /u01/app/oracle/oradata/TestDB11/users01.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 4 - see DBWR trace file

ORA-01110: data file 4: '/u01/app/oracle/oradata/TestDB11/users01.dbf'

--檢視需要介質恢復的檔案的狀態

sys@TESTDB11>select * from v$recover_file;

      FILE# ONLINE  ONLINE_  ERROR                CHANGE#  TIME

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

           4 ONLINE  ONLINE   FILE NOT FOUND            0

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

NAME                                                         CHECKPOINT_CHANGE#

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

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

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

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

                                                                       0

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

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

6 rows selected.   

--高可用的恢復方式,是指可以先將需要介質恢復的資料檔案離線,這樣資料庫就可以開啟,然後再對該檔案進行恢復

sys@TESTDB11>alter database datafile 4 offline;

Database altered.

--檢查需要介質恢復的檔案的狀態

sys@TESTDB11>select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME

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

         4 OFFLINE OFFLINE FILE NOT FOUND           0

--開庫

sys@TESTDB11>alter database open;

Database altered.

--此時這個資料檔案對應的表空間無法訪問

scott@TESTDB11>conn scott/scott

Connected.

scott@TESTDB11>select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-00376: file 4 cannot be read at this time

ORA-01110: data file 4: '/u01/app/oracle/oradata/TestDB11/users01.dbf'

--此時其它的表空間是可以正常訪問的

sys@TESTDB11>select status from v$instance;

STATUS

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

OPEN

--從備份中還原資料檔案

[oracle@S1011:/u01/app/oracle/oradata/TestDB11]$ cp /backup/inconsistent_backup/users01.dbf /u01/app/oracle/oradata/TestDB11

--此時直接使該資料檔案online是不可以的

sys@TESTDB11>alter database datafile 4 online;

alter database datafile 4 online

*

ERROR at line 1:

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/u01/app/oracle/oradata/TestDB11/users01.dbf'

--進行介質恢復

sys@TESTDB11>recover datafile 4;

ORA-00279: change 2654911 generated at 08/09/2013 21:27:27 needed for thread 1

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

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

Log applied.

Media recovery complete.

--此時即可聯機

sys@TESTDB11>alter database datafile 4 online;

Database altered.

--聯機之後，資料可以查了

scott@TESTDB11>select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80       1800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       6000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       2600                    10

14 rows selected.