手工完全恢復

1、完全恢復基于三個級別

recover database：所有數據文件損壞，或包括大部分datafile丟失（大面積丟失）

recover tablespace：非關鍵表空間損壞，表空間下某些數據文件不能訪問recover datafile：單一或少數數據文件損壞 (如果是系統表空間損壞，可以在mount下，使用recover datafile恢復)

2、恢復過程可以查看的視圖：

v$recover_file   查看需要恢復的datafile

v$recovery_log  查看recover 需要的redo 日志

v$archvied_log  查看已經歸檔的日志

3、適用的場景

（1）recover database (所有或大部分數據文件損壞，mount或open下進行)

OS：使用cp 還原受損的dbf（不一定是全部，v$recover_file記錄的都需要還原）

SQLPLUS：

①recover database;

②alter database open;

（2）recover tablespace (針對表空間的非關鍵數據文件損壞，一般是open下進行)

OS:使用cp 還原該表空間XXX下的所有數據文件

SQLPLUS：

①alter tablespace XXX offline;

②recover tablespace XXX;

③alter tablespace XXX online;

（3）recover datafile (單個或幾個數據文件損壞，關鍵文件在mount下進行，非關鍵文件在open下進行)

第一種情形

OS:使用cp 還原相關的關鍵數據文件(mount)

SQLPLUS：

①recover datafile 6,8;

②alter database open;

第二種情形

OS:使用cp 還原相關的非關鍵數據文件(open)

SQLPLUS：

①alter database datafile 6,8 offline;

②recover datafile 6,8;

③alter database datafile 6,8 online;

目錄

示例一：recover database

示例二：recover tablespace

示例三：recover datafile

情況1：關鍵數據文件

情況2：非關鍵數據文件

實驗環境：

操作系統：CentOS7.1

數據庫：Oracle 11.2.0.4

示例一：recover database（介質失敗，丟失大量的數據文件）

1、模擬環境：

創建一個seiang表空間，在scott用戶下創建一張表test

SYS@seiang11g>create tablespace seiang datafile '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf' size 20M;

Tablespace created.

SYS@seiang11g>conn scott

Enter password:

Connected.

SCOTT@seiang11g>create table test(id number,name varchar2(10)) tablespace seiang;

Table created.

SCOTT@seiang11g>insert into test values(1,'wjq');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from test;

        ID NAME

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

         1 wjq

SYS@seiang11g>shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down

干凈的關閉數據庫之后，操作系統下對數據庫中的Datafile做一個完全冷備

[oracle@seiang11g OraDB11g]$ cp ./* /u01/app/oracle/UMAN_Backup/

[oracle@seiang11g OraDB11g]$ ll /u01/app/oracle/UMAN_Backup/    

total 2123572

-rw-r----- 1 oracle oinstall   9748480 Jul 25 11:53 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 11:54 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 11:54 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 11:54 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 11:54 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 11:54 rman01.dbf

-rw-r----- 1 oracle oinstall  20979712 Jul 25 11:54 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 11:54 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 11:54 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 11:54 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 11:54 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 11:54 users01.dbf

啟動數據庫

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

Database opened.

SYS@seiang11g>conn scott

Enter password:

Connected.

SCOTT@seiang11g>insert into test values(2,'wjq1');  //注意該條數據提交

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>insert into test values(3,'wjq2');  //注意該條數據未提交

1 row created.

SCOTT@seiang11g>select * from test;

        ID NAME

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

         1 wjq

         2 wjq1

         3 wjq2

查看當前日志，第二、三條數據的插入記錄在redo2中；

SYS@seiang11g>select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS

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

         1         31          1 INACTIVE

         2         32          1 CURRENT

         3         30          1 INACTIVE

進行日志切換

SYS@seiang11g>alter system switch logfile;

System altered.

SYS@seiang11g>select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS

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

         1         31          1 INACTIVE

         2         32          1 ACTIVE

         3         33          1 CURRENT

SYS@seiang11g>conn scott

Enter password:

Connected.

SCOTT@seiang11g>insert into test values(4,'wjq3');   //注意該條記錄也為提交

1 row created.

SCOTT@seiang11g>select * from test;

        ID NAME

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

         1 wjq

         2 wjq1

         3 wjq2

         4 wjq3

2、模擬介質損壞

數據庫在打開的情況下刪除數據文件

[oracle@seiang11g OraDB11g]$ rm *.dbf    

[oracle@seiang11g OraDB11g]$ ll

total 163132

-rw-r----- 1 oracle oinstall  9748480 Jul 25 12:06 control01.ctl

-rw-r----- 1 oracle oinstall 52429312 Jul 25 11:56 redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:02 redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jul 25 12:06 redo03.log

換一個session關閉數據庫，然后重新啟動，數據庫只能啟動到mount狀態，open時報錯

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

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

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

SYS@seiang11g>select file#,error from v$recover_file;

     FILE# ERROR

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

         1 FILE NOT FOUND

         2 FILE NOT FOUND

         3 FILE NOT FOUND

         4 FILE NOT FOUND

         5 FILE NOT FOUND

         6 FILE NOT FOUND

         7 FILE NOT FOUND

查看控制文件和數據文件頭中記錄的SCN

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

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

         1            1501761

         2            1501761

         3            1501761

         4            1501761

         5            1501761

         6            1501761

         7            1501761

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

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

         1                  0

         2                  0

         3                  0

         4                  0

         5                  0

         6                  0

         7                  0

由于沒有數據文件，所以數據文件頭的SCN為0

從冷備的Datafile中還原丟失的數據文件

[oracle@seiang11g OraDB11g]$ cp /u01/app/oracle/UMAN_Backup/*.dbf ./

[oracle@seiang11g OraDB11g]$ ll

total 2123572

-rw-r----- 1 oracle oinstall   9748480 Jul 25 12:15 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 12:13 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 11:56 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 12:02 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 12:07 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 12:13 rman01.dbf

-rw-r----- 1 oracle oinstall  20979712 Jul 25 12:13 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 12:14 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 12:14 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 12:14 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 12:14 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 12:14 users01.dbf

再次查看控制文件和數據文件頭的SCN，發現數據文件頭的SCN比控制文件中記錄的SCN要小

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

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

         1            1501761

         2            1501761

         3            1501761

         4            1501761

         5            1501761

         6            1501761

         7            1501761

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

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

         1            1501758

         2            1501758

         3            1501758

         4            1501758

         5            1501758

         6            1501758

         7            1501758

執行手工完全恢復，并比較控制文件和數據文件頭的SCN，發現完全恢復后，控制文件和數據文件中記錄的SCN一致；

SYS@seiang11g>recover database;

Media recovery complete.

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

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

         1            1522474

         2            1522474

         3            1522474

         4            1522474

         5            1522474

         6            1522474

         7            1522474

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

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

         1            1522474

         2            1522474

         3            1522474

         4            1522474

         5            1522474

         6            1522474

         7            1522474

SYS@seiang11g>select * from v$recover_file;

no rows selected

打開數據庫，并進行驗證

SYS@seiang11g>alter database open;

Database altered.

SYS@seiang11g>select * from scott.test;

        ID NAME

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

         1 wjq

         2 wjq1

         3 wjq2

         4 wjq3

示例二：recover tablespace

針對的是非關鍵表空間的損壞恢復，基于表空間的完全恢復實際上還是對其下的datafile的恢復；模擬這種情形非常實用，通常某個非關鍵表空間下的數據文件受損，但并沒有造成Oracle崩潰，我們只需針對個別有問題的tablespace去做單獨的在線恢復操作，也就是說恢復時數據庫整體是online的，而局部表空間是offline的，數據庫不需要shutdown。

1、模擬環境

在scott用戶下創建一個表test1，并插入相應的數據

SCOTT@seiang11g>create table test1(id number,name varchar2(10)) tablespace seiang;

Table created.

SCOTT@seiang11g>

SCOTT@seiang11g>insert into test1 values(100,'wjq');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

查看當前redo信息

SYS@seiang11g>select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS

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

         1         34          1 CURRENT

         2         32          1 INACTIVE

         3         33          1 INACTIVE

進行日志的切換

SYS@seiang11g>alter system switch logfile;

System altered.

SYS@seiang11g>select group#,sequence#,members,status from v$log;

    GROUP#  SEQUENCE#    MEMBERS STATUS

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

         1         34          1 ACTIVE

         2         35          1 CURRENT

         3         33          1 INACTIVE

以下插入的兩條記錄未提交

SCOTT@seiang11g>insert into test1 values(200,'wjq2');

1 row created.

SCOTT@seiang11g>insert into test1 values(200,'wjq3');

1 row created.

SCOTT@seiang11g>select * from test1;

        ID NAME

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

       100 wjq

       200 wjq2

       200 wjq3

2、模擬表空間損壞

數據庫open下，直接刪除表空間下的數據文件

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g

total 2103124

-rw-r----- 1 oracle oinstall   9781248 Jul 25 14:14 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 12:20 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 14:10 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 14:14 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 12:20 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 12:20 rman01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 14:13 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 14:13 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 12:20 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 14:13 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 12:20 users01.dbf

清除data buffer cache的記錄

SYS@seiang11g>alter system flush buffer_cache;

System altered.

SCOTT@seiang11g>select * from test1;

select * from test1

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 5524

Session ID: 42 Serial number: 91

重新啟動數據庫，在數據庫open的時候出現報錯

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

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

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'

查看控制文件和數據文件頭的SCN

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

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

         1            1522477

         2            1522477

         3            1522477

         4            1522477

         5            1522477

         6            1522477

         7            1522477

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

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

         1            1527707

         2            1527707

         3            1527707

         4            1527707

         5            1527707

         6            1527707

         7                  0

丟失的數據文件7沒有SCN

SYS@seiang11g>recover database;

ORA-00279: change 1501758 generated at 07/25/2017 11:52:18 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/arch/arch_1_949237404_32.log

ORA-00280: change 1501758 for thread 1 is in sequence #32

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

打開數據庫，并進行驗證控制文件和數據文件頭的SCN一致

SYS@seiang11g>alter database open;

Database altered.

SYS@seiang11g>select file#,checkpoint_change# from v$datafile;

     FILE# CHECKPOINT_CHANGE#

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

         1            1548052

         2            1548052

         3            1548052

         4            1548052

         5            1548052

         6            1548052

         7            1548052

SYS@seiang11g>select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#

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

         1            1548052

         2            1548052

         3            1548052

         4            1548052

         5            1548052

         6            1548052

         7            1548052

SYS@seiang11g>select * from scott.test1;

        ID NAME

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

       100 wjqs

實驗發現：未提交的兩條數據被回滾掉了

示例三：recover datafile

情況1：關鍵數據文件損壞

1、模擬環境

同示例2不同的是模擬UNDO文件損壞: 因UNDO數據文件也是關鍵文件，所以只能在mount狀態下恢復。

SCOTT@seiang11g>insert into test1 values(200,'wjqgood');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from test1;

        ID NAME

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

       100 wjq

       200 wjqgood

SCOTT@seiang11g>select * from test1;

        ID NAME

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

       100 wjq

       200 wjqgood

刪除test1中的數據，但是沒有提交，老值記錄在UNDO中

SYS@seiang11g>delete scott.test1;

2 rows deleted.

在線備份UNDO數據文件

SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf /u01/app/oracle/backup_Temp

SYS@seiang11g>host ls -l /u01/app/oracle/backup_Temp

total 107528

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf

2、模擬UNDO數據文件丟失

備份完成后，在線UNDO數據文件

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g/

total 2016084

-rw-r----- 1 oracle oinstall   9781248 Jul 25 15:22 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 14:27 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 14:27 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:21 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 14:27 rman01.dbf

-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:21 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:21 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:20 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 12:20 temp01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 14:27 users01.dbf

干凈的關閉數據庫，并重新啟動數據庫

SYS@seiang11g>shutdown abort

ORACLE instance shut down.

SYS@seiang11g>startup

ORACLE instance started.

Total System Global Area 1252663296 bytes

Fixed Size                  2252824 bytes

Variable Size             788533224 bytes

Database Buffers          452984832 bytes

Redo Buffers                8892416 bytes

Database mounted.

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

ORA-01110: data file 3: '/u01/app/oracle/oradata/OraDB11g/undotbs01.dbf'

從備份中還原UNDO數據文件

SYS@seiang11g>host cp /u01/app/oracle/backup_Temp/undotbs01.dbf /u01/app/oracle/oradata/OraDB11g/

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g/

total 2123612

-rw-r----- 1 oracle oinstall   9781248 Jul 25 15:26 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 14:27 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 14:27 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 14:27 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:22 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 14:27 rman01.dbf

-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:21 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:23 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:23 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 12:20 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:26 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 14:27 users01.dbf

執行恢復操作

SYS@seiang11g>recover datafile 3;

Media recovery complete.

完成恢復操作后，打開數據庫，會完成UNDO表空間的數據回滾操作，并驗證恢復成功

SYS@seiang11g>alter database open;

Database altered.

SYS@seiang11g>select * from scott.test1;

        ID NAME

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

       100 wjq

       200 wjqgood

情況2：非關鍵數據文件損壞

1、模擬環境

模擬users和seiang表空間的數據文件損壞，這兩個表空間的數據文件是非關鍵數據文件

SYS@seiang11g>select FILE#,TS#,name,status from v$datafile;

     FILE#        TS# NAME                                               STATUS

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

         1          0 /u01/app/oracle/oradata/OraDB11g/system01.dbf      SYSTEM

         2          1 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf      ONLINE

         3          2 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf     ONLINE

         4          4 /u01/app/oracle/oradata/OraDB11g/users01.dbf       ONLINE

         5          6 /u01/app/oracle/oradata/OraDB11g/example01.dbf     ONLINE

         6          7 /u01/app/oracle/oradata/OraDB11g/rman01.dbf        ONLINE

         7          8 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf      ONLINE

對這兩個表空間的數據文件進行備份

SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/users01.dbf /u01/app/oracle/backup_Temp

SYS@seiang11g>host cp /u01/app/oracle/oradata/OraDB11g/seiang01.dbf /u01/app/oracle/backup_Temp

SYS@seiang11g>host ls -l /u01/app/oracle/backup_Temp

total 133144

-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:34 seiang01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:21 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 15:34 users01.dbf

在scott用戶下創建兩張表，wjq1隸屬于users表空間，wjq2隸屬于seiang表空間

SCOTT@seiang11g>create table wjq1(id number);

Table created.

SCOTT@seiang11g>insert into wjq1 values(111);

1 row created.

SCOTT@seiang11g>insert into wjq1 values(222);

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from wjq1;

        ID

----------

       111

       222

SCOTT@seiang11g>create table wjq2(name varchar2(10)) tablespace seiang;

Table created.

SCOTT@seiang11g>insert into wjq2 values('wjq100');

1 row created.

SCOTT@seiang11g>insert into wjq2 values('seiang200');

1 row created.

SCOTT@seiang11g>commit;

Commit complete.

SCOTT@seiang11g>select * from wjq2;

NAME

----------

wjq100

seiang200

SYS@seiang11g>select table_name,tablespace_name,status from dba_tables

  2  where table_name in ('WJQ1','WJQ2');

TABLE_NAME           TABLESPACE_NAME                STATUS

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

WJQ1                        USERS                          VALID

WJQ2                        SEIANG                         VALID

2、模擬users和seiang多對應的數據文件丟失

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/users01.dbf

SYS@seiang11g>host rm /u01/app/oracle/oradata/OraDB11g/seiang01.dbf

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g

total 2097996

-rw-r----- 1 oracle oinstall   9781248 Jul 25 15:44 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:44 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:27 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:27 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 15:27 rman01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:44 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:44 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 15:27 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:44 undotbs01.dbf

清除data buffer cache的記錄

SYS@seiang11g>alter system flush buffer_cache;

System altered.

SYS@seiang11g>select * from scott.wjq1;

select * from scott.wjq1

*

ERROR at line 1:

ORA-01116: error in opening database file 4

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

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SYS@seiang11g>select * from scott.wjq2;

select * from scott.wjq2

*

ERROR at line 1:

ORA-01116: error in opening database file 7

ORA-01110: data file 7: '/u01/app/oracle/oradata/OraDB11g/seiang01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

還原介質，將users和seiang對應的數據文件還原

SYS@seiang11g>host cp /u01/app/oracle/backup_Temp/users01.dbf /u01/app/oracle/oradata/OraDB11g

SYS@seiang11g>host cp /u01/app/oracle/backup_Temp/seiang01.dbf /u01/app/oracle/oradata/OraDB11g

SYS@seiang11g>host ls -l /u01/app/oracle/oradata/OraDB11g

total 2123612

-rw-r----- 1 oracle oinstall   9781248 Jul 25 15:51 control01.ctl

-rw-r----- 1 oracle oinstall 363077632 Jul 25 15:27 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:51 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:27 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Jul 25 15:27 redo03.log

-rw-r----- 1 oracle oinstall  31465472 Jul 25 15:27 rman01.dbf

-rw-r----- 1 oracle oinstall  20979712 Jul 25 15:51 seiang01.dbf

-rw-r----- 1 oracle oinstall 671096832 Jul 25 15:48 sysaux01.dbf

-rw-r----- 1 oracle oinstall 796925952 Jul 25 15:48 system01.dbf

-rw-r----- 1 oracle oinstall  30416896 Jul 25 15:27 temp01.dbf

-rw-r----- 1 oracle oinstall 110108672 Jul 25 15:48 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Jul 25 15:51 users01.dbf

offline這兩個數據文件

SYS@seiang11g>alter database datafile 4 offline;

Database altered.

SYS@seiang11g>alter database datafile 7 offline;

Database altered.

SYS@seiang11g>select file_id,file_name,online_status from dba_data_files;

FILE_ID          FILE_NAME                              ONLINE_

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

         4 /u01/app/oracle/oradata/OraDB11g/users01.dbf       RECOVER

         3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf     ONLINE

         2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf      ONLINE

         1 /u01/app/oracle/oradata/OraDB11g/system01.dbf      SYSTEM

         5 /u01/app/oracle/oradata/OraDB11g/example01.dbf     ONLINE

         6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf        ONLINE

         7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf      RECOVER

恢復這兩個數據文件

SYS@seiang11g>recover datafile 4,7;

Media recovery complete.

SYS@seiang11g>select file_id,file_name,online_status from dba_data_files;

   FILE_ID FILE_NAME                                          ONLINE_

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

         4 /u01/app/oracle/oradata/OraDB11g/users01.dbf       OFFLINE

         3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf     ONLINE

         2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf      ONLINE

         1 /u01/app/oracle/oradata/OraDB11g/system01.dbf      SYSTEM

         5 /u01/app/oracle/oradata/OraDB11g/example01.dbf     ONLINE

         6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf        ONLINE

         7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf      OFFLINE

online這兩個數據文件

SYS@seiang11g>alter database datafile 4 online;

Database altered.

SYS@seiang11g>alter database datafile 7 online;

Database altered.

SYS@seiang11g>select file_id,file_name,online_status from dba_data_files;

   FILE_ID FILE_NAME                                          ONLINE_

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

         4 /u01/app/oracle/oradata/OraDB11g/users01.dbf       ONLINE

         3 /u01/app/oracle/oradata/OraDB11g/undotbs01.dbf     ONLINE

         2 /u01/app/oracle/oradata/OraDB11g/sysaux01.dbf      ONLINE

         1 /u01/app/oracle/oradata/OraDB11g/system01.dbf      SYSTEM

         5 /u01/app/oracle/oradata/OraDB11g/example01.dbf     ONLINE

         6 /u01/app/oracle/oradata/OraDB11g/rman01.dbf        ONLINE

         7 /u01/app/oracle/oradata/OraDB11g/seiang01.dbf      ONLINE

驗證恢復的正確性

SYS@seiang11g>select * from scott.wjq1;

        ID

----------

       111

       222

SYS@seiang11g>select * from scott.wjq2;

NAME

----------

wjq100

seiang200

相關鏈接:
Oracle手工不完全恢復（一）：使用當前控制文件 

作者：SEian.G（苦練七十二變，笑對八十一難）

ITPUB：http://blog.itpub.net/31015730/

51CTO：http://seiang.blog.51cto.com/