本文主要是介绍手工完全恢复（一般模式，只坏一个数据文件），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
实验环境：red hat 5.8
oracle环境：11.2.0.3
查看数据文件位置：
sys@TEST0910> col name for a50
sys@TEST0910> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/test0910/system01.dbf
2 /u01/app/oracle/oradata/test0910/sysaux01.dbf
3 /u01/app/oracle/oradata/test0910/undotbs01.dbf
4 /u01/app/oracle/oradata/test0910/users01.dbf
5 /u01/app/oracle/oradata/test0910/example01.dbf
6 /u01/app/oracle/oradata/test0910/testtb.dbf
6 rows selected.
1、首先建立一张测试用的表。
sys@TEST0910> conn scott/tiger
Connected.
scott@TEST0910> create table test2 as select * from emp;
Table created.
查看表所在的表空间。
scott@TEST0910> select table_name,tablespace_name from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST2 USERS
2、实验之前，使用脚本，冷备份和热备份
冷备份参考：http://blog.csdn.net/rlhua/article/details/11850445
热备份参考：http://blog.csdn.net/rlhua/article/details/11850629
3、分三次插入数据：
sys@TEST0910> select count(*) from scott.test2;
COUNT(*)
----------
14
1.插入，提交，归档
sys@TEST0910> insert into scott.test2 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.test2 select * from scott.emp;
14 rows created.
sys@TEST0910> commit;
Commit complete.
sys@TEST0910> select count(*) from scott.test2;
COUNT(*)
----------
42
3.插入，不提交，不归档。
sys@TEST0910> insert into scott.test2 select * from scott.emp;
14 rows created.
sys@TEST0910> select count(*) from scott.test2;
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、用热备份转储文件
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/users01.dbf /u01/app/oracle/oradata/test0910/users01.dbf
8、转储完，此时需要恢复数据库。
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 --此处选择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
Log applied.
Media recovery complete.
9、打开数据库，查询表。
sys@TEST0910> alter database open;
Database altered.
sys@TEST0910> select count(*) from scott.test2;
COUNT(*)
----------
42
这篇关于手工完全恢复（一般模式，只坏一个数据文件）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
