丢掉所有控制文件，还原所有数据文件，不还原控制文件，recover database until cancel
说明：创建表test03，切换日志，创建表test04，切换日志，删除所有数据文件，还原备份的所有数据文件，不需要还原控制文件；test03的归档日志没有丢失，test04的归档日志丢失，不能恢复test04
1、数据库所有控制文件丢失 2、还原备份之前的控制文件，数据文件没做还原 3、until cancel using bakup controlfile跑部分归档日志
（1）一致性的全备
SQL> shutdown immediate;
# mkdir -p /oradata/bossbak/20140609allbackup
# cp -rf $ORACLE_BASE/oradata/boss/*.dbf /oradata/bossbak/20140609allbackup
# cp -rf $ORACLE_BASE/oradata/boss/*.ctl /oradata/bossbak/20140609allbackup
# cp -rf $ORACLE_HOME/dbs/spfileboss.ora /oradata/bossbak/20140609
# cd /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/
# rm -rf *
（2）查看数据库的信息
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ ONLY
SQL> select table_name,status,tablespace_name from user_tables where tablespace_name like 'TESTTBS%';
TABLE_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
TEST01 VALID TESTTBS01
BOSS_NEW_TEST VALID TESTTBS01
（3）创建表test03，插入数据
SQL> create table test03 (id number, name varchar2(30)) tablespace testtbs01;
SQL> insert into test03 values(1, '11111');
SQL> insert into test03 values(2,'22222');
SQL> insert into test03 values(3,'33333');
SQL> insert into test03 values(1, '11111');
SQL> insert into test03 values(2,'22222');
SQL> insert into test03 values(3,'33333');
SQL> commit;
（4）进行日志切换
SQL> alter system switch logfile;
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 2 YES INACTIVE 675131
2 1 4 NO CURRENT 700583
3 1 3 YES ACTIVE 699759
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> create table test04 (id number, name varchar2(30)) tablespace testtbs01;
SQL> insert into test04 values(1, '11111');
SQL> insert into test04 values(2,'22222');
SQL> insert into test04 values(3,'33333');
SQL> commit;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select group#,members,sequence#,archived,status,first_change# from v$log;
GROUP# MEMBERS SEQUENCE# ARC STATUS FIRST_CHANGE#
---------- ---------- ---------- --- ---------------- -------------
1 1 8 YES INACTIVE 704201
2 1 7 YES INACTIVE 704199
3 1 9 NO CURRENT 704204
（5）备份全备后所有的归档日志
# pwd
/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09
# cp -rf *.arc /oradata/bossbak/20140609
（6）删除全部归档，删除全部数据文件
# rm -rf *.arc
（7）关闭数据库
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup open;
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
（8）还原备份的数据文件，不需要恢复控制文件
$ cp -rf *.dbf /oracle/oradata/boss
SQL> col "文件名" for a40
SQL> set linesize 150
SQL> select
2 ts.name "表空间名"
3 , df.file# "文件号"
4 , df.checkpoint_change# "检查点"
5 , df.name "文件名"
6 , df.status "在线状态"
7 , rf.error "恢复原因"
8 , rf.change# "系统变更号"
9 , rf.time
10 from v$tablespace ts,v$datafile df,v$recover_file rf
11 where ts.ts#=df.ts# and df.file#=rf.file#
12 order by df.file#;
#####最近一次数据库正常启动后的scn
表空间名 文件号 检查点 文件名 在线状 恢复原因 系统变更号 TIME
------------------------------ ---------- ---------- ---------------------------------------- ------- ------------------ ---------- ------------
SYSTEM 1 704204 /oracle/oradata/boss/system01.dbf SYSTEM FILE NOT FOUND 0
UNDOTBS1 2 704204 /oracle/oradata/boss/undotbs01.dbf ONLINE FILE NOT FOUND 0
SYSAUX 3 704204 /oracle/oradata/boss/sysaux01.dbf ONLINE FILE NOT FOUND 0
USERS 4 704204 /oracle/oradata/boss/users01.dbf ONLINE FILE NOT FOUND 0
EXAMPLE 5 704204 /oracle/oradata/boss/example01.dbf ONLINE FILE NOT FOUND 0
TESTTBS01 6 704204 /oracle/oradata/boss/testtbs01_01.dbf ONLINE FILE NOT FOUND 0
TESTTBS01 7 704204 /oracle/oradata/boss/testtbs01_02.dbf ONLINE FILE NOT FOUND 0
TESTTBS02 8 652783 /oracle/oradata/boss/testtbs02_01.dbf OFFLINE OFFLINE NORMAL 0
TESTTBS03 9 652799 /oracle/oradata/boss/testtbs03_01.dbf ONLINE FILE NOT FOUND 0
SQL> col name for a40
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header;
FILE# NAME STATUS CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
1 ONLINE 0
2 ONLINE 0
3 ONLINE 0
4 ONLINE 0
5 ONLINE 0
6 ONLINE 0
7 ONLINE 0
8 OFFLINE 0
9 ONLINE 0
（9）复制部分的归档日志，基于终止恢复
# cp -rf o1_mf_1_3_9sbbrfsx_.arc o1_mf_1_4_9sbbsn5j_.arc /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/
# cat boss_dbw0_8622.trc
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
ORA-27041: unable to open file
Linux Error: 13: Permission denied
SQL> col name for a40;
SQL> set linesize 120;
SQL> select file#,name,status,CHECKPOINT_CHANGE#,recover from v$datafile_header;
FILE# NAME STATUS CHECKPOINT_CHANGE# REC
---------- ---------------------------------------- ------- ------------------ ---
1 /oracle/oradata/boss/system01.dbf ONLINE 700222 YES
2 /oracle/oradata/boss/undotbs01.dbf ONLINE 700222 YES
3 /oracle/oradata/boss/sysaux01.dbf ONLINE 700222 YES
4 /oracle/oradata/boss/users01.dbf ONLINE 700222 YES
5 /oracle/oradata/boss/example01.dbf ONLINE 700222 YES
6 /oracle/oradata/boss/testtbs01_01.dbf ONLINE 700222 YES
7 /oracle/oradata/boss/testtbs01_02.dbf ONLINE 700222 YES
8 OFFLINE 0
9 /oracle/oradata/boss/testtbs03_01.dbf ONLINE 652799
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/oradata/boss/system01.dbf'
SQL> recover database until cancel;
ORA-00279: change 700222 generated at 06/09/2014 11:16:10 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_%u_.arc
ORA-00280: change 700222 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
# ll
总用量 1047064
-rw-r----- 1 oracle oinstall 7094272 6月 9 11:21 control02.ctl
-rw-r----- 1 oracle oinstall 7094272 6月 9 11:21 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 6月 9 11:19 example01.dbf
-rw-r----- 1 oracle oinstall 622080 6月 9 12:38 o1_mf_1_3_9sbbrfsx_.arc
-rw-r----- 1 oracle oinstall 11264 6月 9 12:38 o1_mf_1_4_9sbbsn5j_.arc
-rw-r----- 1 oracle oinstall 2560 6月 9 12:38 o1_mf_1_5_9sbbst96_.arc
-rw-r----- 1 oracle oinstall 4336640 6月 9 12:38 o1_mf_1_6_9sbgg5or_.arc
-rw-r----- 1 oracle oinstall 1024 6月 9 12:38 o1_mf_1_7_9sbgg862_.arc
-rw-r----- 1 oracle oinstall 2560 6月 9 12:38 o1_mf_1_8_9sbggfm7_.arc
# cp -rf o1_mf_1_3_9sbbrfsx_.arc o1_mf_1_4_9sbbsn5j_.arc /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/
SQL> recover database until cancel;
ORA-00279: change 700222 generated at 06/09/2014 11:16:10 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_%u_.arc
ORA-00280: change 700222 for thread 1 is in sequence #3
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 700583 generated at 06/09/2014 11:33:33 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_4_%u_.arc
ORA-00280: change 700583 for thread 1 is in sequence #4
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_3_9sbbrfsx_.arc' no longer needed
for this recovery
ORA-00279: change 700603 generated at 06/09/2014 11:34:12 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_%u_.arc
ORA-00280: change 700603 for thread 1 is in sequence #5
ORA-00278: log file '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_4_9sbbsn5j_.arc' no longer needed
for this recovery
ORA-00308: cannot open archived log '/oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_9sbbst96_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
SQL> recover database until cancel;
ORA-00279: change 700603 generated at 06/09/2014 11:34:12 needed for thread 1
ORA-00289: suggestion : /oracle/flash_recovery_area/BOSS/archivelog/2014_06_09/o1_mf_1_5_%u_.arc
ORA-00280: change 700603 for thread 1 is in sequence #5
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;
SQL> select * from test03;
ID NAME
---------- ----------------------------------------
1 11111
2 22222
3 33333
SQL> select * from test04;
select * from test04
*
ERROR at line 1:
ORA-00942: table or view does not exist