案例1：recover database （介质失败，丢失大部分数据文件）
解决方法：需要做介质恢复，先restore丢失的数据文件，然后recover database，起库。
（1）模拟环境
05:45:49 SQL> select * from test;
ID
----------
1
2
3
05:45:52 SQL> insert into test values (4);
1 row created.
05:46:01 SQL> commit;
Commit complete.
05:46:02 SQL> insert into test values (5);
1 row created.
05:46:32 SQL> commit;
Commit complete.
05:46:34 SQL> insert into test values (6);
1 row created.
05:46:48 SQL> commit;
Commit complete.
05:46:49 SQL> insert into test values (7);
1 row created.
05:47:15 SQL> commit;
Commit complete.
05:46:08 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 38 52428800 1 NO CURRENT 1187992 16-AUG-11
2 1 36 52428800 1 YES INACTIVE 1184326 16-AUG-11
3 1 37 52428800 1 YES INACTIVE 1187989 16-AUG-11
05:46:13 SQL> alter system switch logfile;
System altered.
05:46:43 SQL> alter system archive log current;
System altered.
05:46:58 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 38 52428800 1 YES ACTIVE 1187992 16-AUG-11
2 1 39 52428800 1 YES ACTIVE 1188675 16-AUG-11
3 1 40 52428800 1 NO CURRENT 1188689 16-AUG-11
05:47:03 SQL> alter system archive log current;
System altered.
05:47:25 SQL>
05:47:16 SQL> insert into test values (8);
1 row created.
05:47:29 SQL> commit;
Commit complete.
05:47:30 SQL> insert into test values (9);
1 row created.
05:47:32 SQL> select * from test;
ID
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
05:47:38 SQL>
（2）模拟介质失败
[oracle@work ~]$ rm /u01/app/oracle/oradata/prod/*.dbf
（3）启动database
05:48:57 SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1219184 bytes
Variable Size 79693200 bytes
Database Buffers 230686720 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/prod/system01.dbf'
05:49:03 SQL> select file#,error from v$recover_file;
FILE# ERROR
---------- -----------------------------------------------------------------
1 FILE NOT FOUND
3 FILE NOT FOUND
5 FILE NOT FOUND
6 FILE NOT FOUND
7 FILE NOT FOUND
（4） 启动失败，需要做介质恢复 ，首先restore
[oracle@work ~]$ cp /disk1/backup/prod/close_bak/*.dbf /u01/app/oracle/oradata/prod/
--------recover database
05:51:48 SQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME
---------- ---------- ---------
ARCHIVE_NAME
------------------------------------------------------------------------------------------------------------------------
1 38 16-AUG-11
/disk1/arch/prod/arch_38_1_758481658.log
---------查看恢复需要的归档日志
05:51:58 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1188700
2 1188700
3 1188700
4 1188700
5 1188700
6 1188700
7 1188700
7 rows selected.
05:52:42 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1188419
2 1188700
3 1188419
4 1188700
5 1188419
6 1188419
7 1188419
7 rows selected.
-----------控制文件记录的scn 应大于需恢复的数据文件头部的scn
（5） recover database（恢复数据库）
05:52:49 SQL> recover database;
ORA-00279: change 1188419 generated at 08/16/2011 05:43:18 needed for thread 1
ORA-00289: suggestion : /disk1/arch/prod/arch_38_1_758481658.log
ORA-00280: change 1188419 for thread 1 is in sequence #38
05:53:46 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
查看告警日志：
ALTER DATABASE RECOVER database
Tue Aug 16 05:53:46 2011
Media Recovery Start
ORA-279 signalled during: ALTER DATABASE RECOVER database ...
Tue Aug 16 05:54:13 2011
ALTER DATABASE RECOVER CONTINUE DEFAULT
Tue Aug 16 05:54:13 2011
Media Recovery Log /disk1/arch/prod/arch_38_1_758481658.log
Tue Aug 16 05:54:14 2011
Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo02.log
Tue Aug 16 05:54:14 2011
Recovery of Online Redo Log: Thread 1 Group 3 Seq 40 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo03.log
Tue Aug 16 05:54:14 2011
Recovery of Online Redo Log: Thread 1 Group 1 Seq 41 Reading mem 0
Mem# 0 errs 0: /u01/app/oracle/oradata/prod/redo01.log
Tue Aug 16 05:54:14 2011
Media Recovery Complete (prod)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
（6）验证：
05:54:17 SQL> alter database open;
Database altered.
05:55:31 SQL> select * from scott.test;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
05:55:40 SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1208722
2 1208722
3 1208722
4 1208722
5 1208722
6 1208722
7 1208722
7 rows selected.
05:57:58 SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1208722
2 1208722
3 1208722
4 1208722
5 1208722
6 1208722
7 1208722
7 rows selected.
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
