需要进行不完全恢复的场景：
a.因归档日志丢失而导致完全恢复失败。
b.所有未归档的重做日志文件和数据文件均丢失。
c.用户错误
1.某个重要的表被删除。 2.在表中提交了无效数据。
d.当前控制文件丢失，必须使用备份控制文件才能打开数据库。
模拟场景：
1.1干净关闭数据库
1 SQL> conn /as sysdba2 Connected.3 SQL> shutdown immediate4 Database closed.5 Database dismounted.6 ORACLE instance shut down.7 SQL>
1.2 对数据库冷备
1 bash-3.00$ ls -l 2 total 2215712 3 -rw-r----- 1 oracle oinstall 7389184 Jan 25 21:18 control01.ctl 4 -rw-r----- 1 oracle oinstall 7389184 Jan 25 21:18 control02.ctl 5 -rw-r----- 1 oracle oinstall 7389184 Jan 25 21:18 control03.ctl 6 -rw-r----- 1 oracle oinstall 104865792 Jan 25 21:18 example01.dbf 7 -rw-r----- 1 oracle oinstall 52429312 Jan 25 20:31 redo01.log 8 -rw-r----- 1 oracle oinstall 52429312 Jan 25 21:18 redo02.log 9 -rw-r----- 1 oracle oinstall 52429312 Jan 25 20:31 redo03.log10 -rw-r----- 1 oracle oinstall 10493952 Jan 25 21:18 sun01_1.dbf11 -rw-r----- 1 oracle oinstall 10493952 Jan 25 21:18 sun02_1.dbf12 -rw-r----- 1 oracle oinstall 5251072 Jan 25 21:18 sun03_1.dbf13 -rw-r----- 1 oracle oinstall 262152192 Jan 25 21:18 sysaux01.dbf14 -rw-r----- 1 oracle oinstall 503324672 Jan 25 21:18 system01.dbf15 -rw-r----- 1 oracle oinstall 20979712 Jan 23 04:03 temp01.dbf16 -rw-r----- 1 oracle oinstall 31465472 Jan 25 21:18 undotbs01.dbf17 -rw-r----- 1 oracle oinstall 5251072 Jan 25 21:18 users01.dbf18 bash-3.00$ cp * /u01/backup/cold19 bash-3.00$
1.3 在t表中插入数据
1 SQL> conn user1/user1 2 Connected. 3 4 SQL> select * from t; 5 6 ID NAME 7 ---------- ---------------- 8 1 oracle 9 2 oracle10 0 oracle11 3 oracle12 4 oracle13 5 oracle14 15 6 rows selected.16 17 SQL> insert into t values(6,'oracle');18 19 1 row created.20 21 SQL> commit;22 23 Commit complete.24 25 SQL>
1.4查看当前时间
1 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;2 3 TO_CHAR(SYSDATE,'YY4 -------------------5 2013-01-25 21:30:356 7 SQL>
1.5 模拟失误现场
1 SQL> drop table t;2 3 Table dropped.4 5 SQL>
恢复
2.1 关机1 SQL> shutdown abort2 ORACLE instance shut down.3 SQL>
2.2还原以前备份的数据文件
1 bash-3.00$ pwd 2 /u01/oradata/sunbak 3 bash-3.00$ ls -l 4 total 2215712 5 -rw-r----- 1 oracle oinstall 7389184 Jan 25 21:33 control01.ctl 6 -rw-r----- 1 oracle oinstall 7389184 Jan 25 21:33 control02.ctl 7 -rw-r----- 1 oracle oinstall 7389184 Jan 25 21:33 control03.ctl 8 -rw-r----- 1 oracle oinstall 104865792 Jan 25 21:21 example01.dbf 9 -rw-r----- 1 oracle oinstall 52429312 Jan 25 21:21 redo01.log10 -rw-r----- 1 oracle oinstall 52429312 Jan 25 21:33 redo02.log11 -rw-r----- 1 oracle oinstall 52429312 Jan 25 21:21 redo03.log12 -rw-r----- 1 oracle oinstall 10493952 Jan 25 21:30 sun01_1.dbf13 -rw-r----- 1 oracle oinstall 10493952 Jan 25 21:21 sun02_1.dbf14 -rw-r----- 1 oracle oinstall 5251072 Jan 25 21:21 sun03_1.dbf15 -rw-r----- 1 oracle oinstall 262152192 Jan 25 21:33 sysaux01.dbf16 -rw-r----- 1 oracle oinstall 503324672 Jan 25 21:33 system01.dbf17 -rw-r----- 1 oracle oinstall 20979712 Jan 23 04:03 temp01.dbf18 -rw-r----- 1 oracle oinstall 31465472 Jan 25 21:33 undotbs01.dbf19 -rw-r----- 1 oracle oinstall 5251072 Jan 25 21:21 users01.dbf20 bash-3.00$ rm -f *.dbf21 bash-3.00$ cd /u01/backup/cold/22 bash-3.00$ cp *.dbf /u01/oradata/sunbak/23 bash-3.00$
2.3将数据库开到mount
1 SQL> conn /as sysdba 2 Connected to an idle instance. 3 SQL> startup mount 4 ORACLE instance started. 5 6 Total System Global Area 289406976 bytes 7 Fixed Size 1279820 bytes 8 Variable Size 92276916 bytes 9 Database Buffers 192937984 bytes10 Redo Buffers 2912256 bytes11 Database mounted.12 SQL>
2.4对比控制文件与数据文件的SCN
1 #最新的控制文件 2 SQL> select file#,checkpoint_change# from v$datafile; 3 4 FILE# CHECKPOINT_CHANGE# 5 ---------- ------------------ 6 1 690924 7 2 690924 8 3 690924 9 4 69092410 5 69092411 6 69092412 7 69092413 8 69092414 15 8 rows selected.16 17 SQL> 18 #备份的数据文件19 SQL> select file#,checkpoint_change# from v$datafile_header;20 21 FILE# CHECKPOINT_CHANGE#22 ---------- ------------------23 1 69092324 2 69092325 3 69092326 4 69092327 5 69092328 6 69092329 7 69092330 8 69092331 32 8 rows selected.33 34 SQL>
2.5 恢复
1 SQL> recover database until time '2013-01-25:21:30:35'; 2 Media recovery complete.3 SQL> 4 SQL> alter database open resetlogs;5 6 Database altered.7 8 SQL>
3.查看t表
1 SQL> select * from user1.t order by 1; 2 3 ID NAME 4 ---------- ---------------- 5 0 oracle 6 1 oracle 7 2 oracle 8 3 oracle 9 4 oracle10 5 oracle11 6 oracle12 13 7 rows selected.14 15 SQL>