手工完全恢复（高可用模式，即先开库，后恢复）

实验环境：red hat 5.8

oracle环境：11.2.0.3

 

先开库，后恢复损坏的数据文件，减少数据库对用户的影响。

限制：因为system和undo这两个表空间不能offline，故不适合此种模式的恢复。

 

1、首先建立一张测试用的表。

sys@TEST0910> conn scott/tiger

Connected.

scott@TEST0910> create table test3 as select * from emp;

Table created.

sys@TEST0910> select count(*) from scott.test3;

  COUNT(*)

----------

        14

 

2、实验之前，使用脚本，冷备份和热备份

冷备份参考：http://blog.csdn.net/rlhua/article/details/11850445

热备份参考：http://blog.csdn.net/rlhua/article/details/11850629

 

3、分三次插入数据：

sys@TEST0910> select count(*) from scott.test3;

  COUNT(*)

----------

        14

1.插入，提交，归档

sys@TEST0910> insert into scott.test3 select * from scott.emp;

14 rows created.

sys@TEST0910> commit;

Commit complete.

sys@TEST0910> alter system archive log current;

System altered.

2.插入，提交，不归档。

sys@TEST0910> insert into scott.test3 select * from scott.emp;

14 rows created.

sys@TEST0910> commit;

Commit complete.

sys@TEST0910> select count(*) from scott.test3;

  COUNT(*)

----------

        42

3.插入，不提交，不归档。

sys@TEST0910> insert into scott.test3 select * from scott.emp;

14 rows created.

sys@TEST0910> select count(*) from scott.test3;

  COUNT(*)

----------

        56

4、模拟断电

sys@TEST0910> shutdown abort

ORACLE instance shut down.

 

5、删除表所在的那个数据文件

[oracle@rtest bak]$ rm /u01/app/oracle/oradata/test0910/users01.dbf

[oracle@rtest bak]$ ls /u01/app/oracle/oradata/test0910/users01.dbf

ls: /u01/app/oracle/oradata/test0910/users01.dbf: No such file or directory

6、起库，报错，查询缺失的数据文件

sys@TEST0910> startup;

ORACLE instance started.

Total System Global Area 2505338880 bytes

Fixed Size                  2230952 bytes

Variable Size             587203928 bytes

Database Buffers         1895825408 bytes

Redo Buffers               20078592 bytes

Database mounted.

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

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

sys@TEST0910> select file#,error from v$recover_file;

     FILE# ERROR

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

         4 FILE NOT FOUND

 

7、在数据库没有打开的情况下，表空间不能offline，只能offline数据文件。然后将数据库打开。

 

sys@TEST0910> alter tablespace users offline;

alter tablespace users offline

*

ERROR at line 1:

ORA-01109: database not open

sys@TEST0910> alter database datafile 4 offline;

Database altered.

sys@TEST0910> alter database open;

Database altered.

 

先开库，减少影响。

 8、进行转储数据文件。

[oracle@rtest bak]$  cp /u01/app/oracle/bak/hot_bak/users01.dbf /u01/app/oracle/oradata/test0910/users01.dbf

 

9、进行数据文件恢复

sys@TEST0910> recover datafile 4;

ORA-00279: change 1647795 generated at 09/19/2013 20:16:10 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_19/o1_mf_1_39_93q4z4lt_.arc

ORA-00280: change 1647795 for thread 1 is in sequence #39

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 1648611 generated at 09/19/2013 20:16:35 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_19/o1_mf_1_40_93qc5g30_.arc

ORA-00280: change 1648611 for thread 1 is in sequence #40

ORA-00279: change 1656909 generated at 09/19/2013 22:02:21 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_20/o1_mf_1_41_93rysdc1_.arc

ORA-00280: change 1656909 for thread 1 is in sequence #41

ORA-00279: change 1682384 generated at 09/20/2013 12:43:24 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_20/o1_mf_1_42_93ryywvd_.arc

ORA-00280: change 1682384 for thread 1 is in sequence #42

ORA-00279: change 1682485 generated at 09/20/2013 12:46:20 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_20/o1_mf_1_43_93s0lg0r_.arc

ORA-00280: change 1682485 for thread 1 is in sequence #43

ORA-00279: change 1702534 generated at 09/20/2013 13:13:48 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_20/o1_mf_1_44_93s106lj_.arc

ORA-00280: change 1702534 for thread 1 is in sequence #44

ORA-00279: change 1703113 generated at 09/20/2013 13:21:10 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_20/o1_mf_1_45_93s1bwhd_.arc

ORA-00280: change 1703113 for thread 1 is in sequence #45

Log applied.

Media recovery complete.

 

10、使数据文件online

sys@TEST0910> alter database datafile 4 online;                                                                                                    

Database altered.