过程：插数据56--查日志--关库--删文件，归档和当前日志组--起库报错--查询--转出文件--恢复报错--关库--重新转储所有数据文件--mount--不完全恢复--开库resetlogs
1、首先做一个全备份
sys@TEST0910> @/u01/app/oracle/bak/cold_bak
2、插入三次数据，分别是提交归档，提交不归档，不提交不归档
sys@TEST0910> select count(*) from scott.test10;
COUNT(*)
----------
14
1 row selected.
sys@TEST0910> insert into scott.test10 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.test10;
COUNT(*)
----------
28
1 row selected.
sys@TEST0910> insert into scott.test10 select * from scott.emp;
14 rows created.
sys@TEST0910> commit;
Commit complete.
sys@TEST0910> select count(*) from scott.test10;
COUNT(*)
----------
42
1 row selected.
sys@TEST0910> insert into scott.test10 select * from scott.emp;
14 rows created.
sys@TEST0910> select count(*) from scott.test10;
COUNT(*)
----------
56
1 row selected.
3、查询当前日志组以及数据文件的位置
sys@TEST0910> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 1 52428800 512 1 YES ACTIVE 1886382 22-SEP-13 1888062 22-SEP-13
2 1 2 52428800 512 1 NO CURRENT 1888062 22-SEP-13 2.8147E+14
3 1 0 52428800 512 1 YES UNUSED 0 0
3 rows selected.
sys@TEST0910> col member for a50
sys@TEST0910> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
2 ONLINE /u01/app/oracle/oradata/test0910/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/test0910/redo01.log NO
3 ONLINE /u01/app/oracle/oradata/test0910/redo03.log NO
3 rows selected.
sys@TEST0910> select name from v$archived_log;
NAME
------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_3_93xzwjf2_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_4_93y4xkgl_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_5_93y8cd3d_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ybyg39_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_2_93ybyrlr_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_3_93yclq3y_.arc
/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ydsdf2_.arc
查看数据文件
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/disk1/users01.dbf
5 /u01/app/oracle/oradata/test0910/example01.dbf
6 /u01/app/oracle/oradata/test0910/testtb.dbf
7 /u01/app/oracle/oradata/test0910/lxtb01.dbf
7 rows selected.
4、模拟断电并且删除数据文件，当前日志文件，归档日志文件
sys@TEST0910> shutdown abort
ORACLE instance shut down.
当前日志没了，数据文件没了，归档日志没了
[oracle@rtest /]$ rm /u01/app/oracle/oradata/test0910/disk1/users01.dbf
[oracle@rtest /]$ rm /u01/app/oracle/oradata/test0910/redo02.log
[oracle@rtest /]$ rm /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ydsdf2_.arc
[oracle@rtest /]$ rm /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_3_93yclq3y_.arc
5、起库报错
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/disk1/users01.dbf'
sys@TEST0910> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
4 FILE NOT FOUND
1 row selected.
6，shutdown abort后转储所有冷备份的数据文件
sys@TEST0910> shutdown abort;
ORACLE instance shut down.
转储所有的数据文件
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/users01.dbf /u01/app/oracle/oradata/test0910/disk1/users01.dbf
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/example01.dbf /u01/app/oracle/oradata/test0910/example01.dbf
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/lxtb01.dbf /u01/app/oracle/oradata/test0910/lxtb01.dbf
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/sysaux01.dbf /u01/app/oracle/oradata/test0910/sysaux01.dbf
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/system01.dbf /u01/app/oracle/oradata/test0910/system01.dbf
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/testtb.dbf /u01/app/oracle/oradata/test0910/testtb.dbf
[oracle@rtest /]$ cp /u01/app/oracle/bak/cold_bak/undotbs01.dbf /u01/app/oracle/oradata/test0910/undotbs01.dbf
7、起库到mount状态，使用基于cancel的不完全恢复，最后resetlogs打开数据库。
sys@TEST0910> startup mount;
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.
sys@TEST0910> recover database until cancel;
ORA-00279: change 1887633 generated at 09/22/2013 14:06:32 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ydsdf2_.arc
ORA-00280: change 1887633 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ydsdf2_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ydsdf2_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
sys@TEST0910> recover database until cancel;
ORA-00279: change 1887633 generated at 09/22/2013 14:06:32 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/TEST0910/archivelog/2013_09_22/o1_mf_1_1_93ydsdf2_.arc
ORA-00280: change 1887633 for thread 1 is in sequence #1
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
sys@TEST0910> alter database open resetlogs;
Database altered.
sys@TEST0910> select count(*) from scott.test10;
COUNT(*)
----------
14
1 row selected.
