本文主要是介绍手工完全恢复（所有数据文件丢失），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
实验环境：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 test1 as select * from emp;
Table created.
2、实验之前，使用脚本，冷备份和热备份
冷备份参考：http://blog.csdn.net/rlhua/article/details/11850445
热备份参考：http://blog.csdn.net/rlhua/article/details/11850629
3、分三次插入数据：
sys@TEST0910> select count(*) from scott.test1;
COUNT(*)
----------
14
1.插入，提交，归档
sys@TEST0910> insert into scott.test1 select * from scott.emp;
14 rows created.
sys@TEST0910> commit;
Commit complete.
sys@TEST0910> alter system archive log current;
System altered.
sys@TEST0910> select count(*) from scott.test1;
COUNT(*)
----------
28
2.插入，提交，不归档。
sys@TEST0910> insert into scott.test1 select * from scott.emp;
14 rows created.
sys@TEST0910> commit;
Commit complete.
sys@TEST0910> select count(*) from scott.test1;
COUNT(*)
----------
42
3.插入，不提交，不归档。
sys@TEST0910> insert into scott.test1 select * from scott.emp;
14 rows created.
sys@TEST0910> select count(*) from scott.test1;
COUNT(*)
----------
56
4、模拟断电，shutdown abort
sys@TEST0910> shutdown abort;
ORACLE instance shut down.
5、删除所有数据文件
[oracle@rtest ~]$ rm -rf /u01/app/oracle/oradata/test0910/users01.dbf
[oracle@rtest ~]$ ls /u01/app/oracle/oradata/test0910/users01.dbf
ls: /u01/app/oracle/oradata/test0910/users01.dbf: No such file or directory
[oracle@rtest ~]$ cd /u01/app/oracle/oradata/test0910/
[oracle@rtest test0910]$ ls
control01.ctl redo01.log redo03.log system01.dbf testtb.dbf
example01.dbf redo02.log sysaux01.dbf temp01.dbf undotbs01.dbf
[oracle@rtest test0910]$ rm -rf *.dbf
[oracle@rtest test0910]$ ls
control01.ctl redo01.log redo02.log redo03.log
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 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/test0910/system01.dbf'
sys@TEST0910> 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
6 rows selected.
7、用热备份转储文件
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/system01.dbf /u01/app/oracle/oradata/test0910/system01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/sysaux01.dbf /u01/app/oracle/oradata/test0910/sysaux01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/undotbs01.dbf /u01/app/oracle/oradata/test0910/undotbs01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/users01.dbf /u01/app/oracle/oradata/test0910/users01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/example01.dbf /u01/app/oracle/oradata/test0910/example01.dbf
[oracle@rtest bak]$ cp /u01/app/oracle/bak/hot_bak/testtb.dbf /u01/app/oracle/oradata/test0910/testtb.dbf
8、因为热备份时，是顺着来备份的，故数据文件的块头的scn不一致。
sys@TEST0910> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
1656909
sys@TEST0910> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1656909
2 1656909
3 1656909
4 1656909
5 1656909
6 1656909
6 rows selected.
sys@TEST0910> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1645562
2 1645575
3 1645599
4 1647795
5 1647968
6 1648619
6 rows selected.
9、转储完，此时需要恢复数据库。
sys@TEST0910> 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/test0910/system01.dbf'
sys@TEST0910> recover database;
ORA-00279: change 1645562 generated at 09/19/2013 20:13:51 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 1645562 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
Log applied.
Media recovery complete.
sys@TEST0910> alter database open;
Database altered.
sys@TEST0910> select count(*) from scott.test1;
COUNT(*)
----------
42
测试完毕。
这篇关于手工完全恢复（所有数据文件丢失）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
