搞懂介质恢复-使用当前的控制文件恢复数据库
[@more@]数据文件users01丢失
1.首先是restore数据文件
restore前：
比较SCN
控制文件记录的SCN
SQL> col checkpoint_change# for 9999999999
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5273633
2 5273633
3 5273633
4 5273633
5 5273633
6 5273633
7 5273633
8 5273633
数据文件记录的SCN
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5273633
2 5273633
3 5273633
4 0
5 5273633
6 5273633
7 5273633
8 5273633
日志文件记录的SCN：
SQL> select checkpoint_change#,checkpoint_time from v$thread;
CHECKPOINT_CHANGE# CHECKPOIN
------------------ ---------
5273633 08-DEC-12
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 70 52428800 1 YES INACTIVE 5273384 08-DEC-12
3 1 69 52428800 1 YES INACTIVE 5273380 08-DEC-12
2 1 71 52428800 1 NO CURRENT 5273386 08-DEC-12
restore后：
数据文件记录的SCN:
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5273633
2 5273633
3 5273633
4 5085599
5 5273633
6 5273633
7 5273633
8 5273633
要完全恢复数据库，必须控制文件的SCN、联机重做日志文件的SCN、数据库数据文件的SCN一致才能完全恢复数据库。
RESTORE之后，比较控制文件记录SCN 5273633、联机重做日志文件的SCN 5273633与数据文件记录的SCN 5085599,数据库呈现不一致状态，控制文件是新的，数据文件datafile 4是旧的，datafile 4 需要介质恢复。
恢复从SCN 5085599,数据库所做的一切操作恢复到SCN 5273633之前的一切操作,众所周知，数据库所做的一切操作是记录在日志文件里的，那么就从日志文件找出SCN 5085599落在哪个日志文件，截止到SCN 5273633落在哪个日志文件里，就能够恢复数据库，该日志文件既包括归档日志文件，也包括联机重做日志文件。
先搜索归档日志文件，前推该数据文件的SCN
SQL> recover database;
ORA-00279: change 5085599 generated at 12/05/2012 22:10:59 needed for thread 1
ORA-00289: suggestion : /archivelog/1_38_800989177.dbf
ORA-00280: change 5085599 for thread 1 is in sequence #38
Specify log: {
ORA-00279: change 5085652 generated at 12/05/2012 22:12:33 needed for thread 1
ORA-00289: suggestion : /archivelog/1_39_800989177.dbf
ORA-00280: change 5085652 for thread 1 is in sequence #39
ORA-00278: log file '/archivelog/1_38_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5085696 generated at 12/05/2012 22:14:01 needed for thread 1
ORA-00289: suggestion : /archivelog/1_40_800989177.dbf
ORA-00280: change 5085696 for thread 1 is in sequence #40
ORA-00278: log file '/archivelog/1_39_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5086088 generated at 12/05/2012 22:23:02 needed for thread 1
ORA-00289: suggestion : /archivelog/1_41_800989177.dbf
ORA-00280: change 5086088 for thread 1 is in sequence #41
ORA-00278: log file '/archivelog/1_40_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5106328 generated at 12/05/2012 22:32:39 needed for thread 1
ORA-00289: suggestion : /archivelog/1_42_800989177.dbf
ORA-00280: change 5106328 for thread 1 is in sequence #42
ORA-00278: log file '/archivelog/1_41_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5127758 generated at 12/05/2012 23:04:10 needed for thread 1
ORA-00289: suggestion : /archivelog/1_43_800989177.dbf
ORA-00280: change 5127758 for thread 1 is in sequence #43
ORA-00278: log file '/archivelog/1_42_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5149034 generated at 12/06/2012 20:41:25 needed for thread 1
ORA-00289: suggestion : /archivelog/1_44_800989177.dbf
ORA-00280: change 5149034 for thread 1 is in sequence #44
ORA-00278: log file '/archivelog/1_43_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5149687 generated at 12/06/2012 20:53:15 needed for thread 1
ORA-00289: suggestion : /archivelog/1_45_800989177.dbf
ORA-00280: change 5149687 for thread 1 is in sequence #45
ORA-00278: log file '/archivelog/1_44_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5149698 generated at 12/06/2012 20:53:33 needed for thread 1
ORA-00289: suggestion : /archivelog/1_46_800989177.dbf
ORA-00280: change 5149698 for thread 1 is in sequence #46
ORA-00278: log file '/archivelog/1_45_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5149718 generated at 12/06/2012 20:54:05 needed for thread 1
ORA-00289: suggestion : /archivelog/1_47_800989177.dbf
ORA-00280: change 5149718 for thread 1 is in sequence #47
ORA-00278: log file '/archivelog/1_46_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5149764 generated at 12/06/2012 20:55:28 needed for thread 1
ORA-00289: suggestion : /archivelog/1_48_800989177.dbf
ORA-00280: change 5149764 for thread 1 is in sequence #48
ORA-00278: log file '/archivelog/1_47_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5149772 generated at 12/06/2012 20:55:50 needed for thread 1
ORA-00289: suggestion : /archivelog/1_49_800989177.dbf
ORA-00280: change 5149772 for thread 1 is in sequence #49
ORA-00278: log file '/archivelog/1_48_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5150485 generated at 12/06/2012 20:59:13 needed for thread 1
ORA-00289: suggestion : /archivelog/1_50_800989177.dbf
ORA-00280: change 5150485 for thread 1 is in sequence #50
ORA-00278: log file '/archivelog/1_49_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5150487 generated at 12/06/2012 20:59:13 needed for thread 1
ORA-00289: suggestion : /archivelog/1_51_800989177.dbf
ORA-00280: change 5150487 for thread 1 is in sequence #51
ORA-00278: log file '/archivelog/1_50_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5171207 generated at 12/06/2012 21:39:16 needed for thread 1
ORA-00289: suggestion : /archivelog/1_52_800989177.dbf
ORA-00280: change 5171207 for thread 1 is in sequence #52
ORA-00278: log file '/archivelog/1_51_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5194247 generated at 12/06/2012 22:20:29 needed for thread 1
ORA-00289: suggestion : /archivelog/1_53_800989177.dbf
ORA-00280: change 5194247 for thread 1 is in sequence #53
ORA-00278: log file '/archivelog/1_52_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5194743 generated at 12/06/2012 22:25:30 needed for thread 1
ORA-00289: suggestion : /archivelog/1_54_800989177.dbf
ORA-00280: change 5194743 for thread 1 is in sequence #54
ORA-00278: log file '/archivelog/1_53_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5214846 generated at 12/06/2012 22:26:25 needed for thread 1
ORA-00289: suggestion : /archivelog/1_55_800989177.dbf
ORA-00280: change 5214846 for thread 1 is in sequence #55
ORA-00278: log file '/archivelog/1_54_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5235761 generated at 12/07/2012 22:10:38 needed for thread 1
ORA-00289: suggestion : /archivelog/1_56_800989177.dbf
ORA-00280: change 5235761 for thread 1 is in sequence #56
ORA-00278: log file '/archivelog/1_55_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237163 generated at 12/07/2012 22:47:49 needed for thread 1
ORA-00289: suggestion : /archivelog/1_57_800989177.dbf
ORA-00280: change 5237163 for thread 1 is in sequence #57
ORA-00278: log file '/archivelog/1_56_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237324 generated at 12/07/2012 22:49:01 needed for thread 1
ORA-00289: suggestion : /archivelog/1_58_800989177.dbf
ORA-00280: change 5237324 for thread 1 is in sequence #58
ORA-00278: log file '/archivelog/1_57_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237326 generated at 12/07/2012 22:49:02 needed for thread 1
ORA-00289: suggestion : /archivelog/1_59_800989177.dbf
ORA-00280: change 5237326 for thread 1 is in sequence #59
ORA-00278: log file '/archivelog/1_58_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237329 generated at 12/07/2012 22:49:06 needed for thread 1
ORA-00289: suggestion : /archivelog/1_60_800989177.dbf
ORA-00280: change 5237329 for thread 1 is in sequence #60
ORA-00278: log file '/archivelog/1_59_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237331 generated at 12/07/2012 22:49:07 needed for thread 1
ORA-00289: suggestion : /archivelog/1_61_800989177.dbf
ORA-00280: change 5237331 for thread 1 is in sequence #61
ORA-00278: log file '/archivelog/1_60_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237338 generated at 12/07/2012 22:49:07 needed for thread 1
ORA-00289: suggestion : /archivelog/1_62_800989177.dbf
ORA-00280: change 5237338 for thread 1 is in sequence #62
ORA-00278: log file '/archivelog/1_61_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5237485 generated at 12/07/2012 23:41:34 needed for thread 1
ORA-00289: suggestion : /archivelog/1_63_800989177.dbf
ORA-00280: change 5237485 for thread 1 is in sequence #63
ORA-00278: log file '/archivelog/1_62_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5263867 generated at 12/08/2012 11:28:29 needed for thread 1
ORA-00289: suggestion : /archivelog/1_64_800989177.dbf
ORA-00280: change 5263867 for thread 1 is in sequence #64
ORA-00278: log file '/archivelog/1_63_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5271879 generated at 12/08/2012 15:39:00 needed for thread 1
ORA-00289: suggestion : /archivelog/1_65_800989177.dbf
ORA-00280: change 5271879 for thread 1 is in sequence #65
ORA-00278: log file '/archivelog/1_64_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5271881 generated at 12/08/2012 15:39:01 needed for thread 1
ORA-00289: suggestion : /archivelog/1_66_800989177.dbf
ORA-00280: change 5271881 for thread 1 is in sequence #66
ORA-00278: log file '/archivelog/1_65_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5271884 generated at 12/08/2012 15:39:05 needed for thread 1
ORA-00289: suggestion : /archivelog/1_67_800989177.dbf
ORA-00280: change 5271884 for thread 1 is in sequence #67
ORA-00278: log file '/archivelog/1_66_800989177.dbf' no longer needed for this recovery
Specify log: {
ORA-00279: change 5273378 generated at 12/08/2012 16:11:31 needed for thread 1
ORA-00289: suggestion : /archivelog/1_68_800989177.dbf
ORA-00280: change 5273378 for thread 1 is in sequence #68
ORA-00278: log file '/archivelog/1_67_800989177.dbf' no longer needed for this recovery
Specify log: {
应用到归档日志68，应用完了最后一个归档日志文件，这个时候的该数据文件的SCN 5273378 与需要恢复到的SCN
5273633还是不一致的。
这个时候，就需要应用联机重做日志文件了，联机重做日志文件为69,70，71。为什么能找到联机重做日志文件，因为控制文件里记录了联机重做日志文件的信息，如下：
转储控制文件联机重做日志的内容：
*******REDO THREAD RECORDS********************************
(size = 256, compat size = 256, section max = 8, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 9, numrecs = 8)
THREAD #1 - status:0xe thread links forward:0 back:0
#logs:3 first:1 last:3 current:2 last used seq#:0x47
enabled at scn: 0x0000.004d128b 12/02/2012 16:59:37
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 12/08/2012 16:11:02 by instance dbwdn
Checkpointed at scn: 0x0000.00507821 12/08/2012 16:16:22
控制文件记录了redo 的SCN 0x0000.00507821 =5273633,说明数据库要apply联机重做日志文件到SCN 5273633，数据库才能完全恢复。
thread:1 rba:(0x47.140.10)
**************************************************
LOG FILE RECORDS
***************************************************
(size = 72, compat size = 72, section max = 16, section in-use = 3,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
(name #3) /u01/app/oracle/oradata/dbwdn/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x19000 seq: 0x00000046 hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00507724
Low scn: 0x0000.00507728 12/08/2012 16:11:40
Next scn: 0x0000.0050772a 12/08/2012 16:11:40
LOG FILE #2:
(name #2) /u01/app/oracle/oradata/dbwdn/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x19000 seq: 0x00000047 hws: 0x2 bsz: 512 nab: 0x140 flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00507728
Low scn: 0x0000.0050772a 12/08/2012 16:11:40
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
(name #1) /u01/app/oracle/oradata/dbwdn/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x19000 seq: 0x00000045 hws: 0x2 bsz: 512 nab: 0x5 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00507722
Low scn: 0x0000.00507724 12/08/2012 16:11:32
Next scn: 0x0000.00507728 12/08/2012 16:11:40
下面是apply:
SQL> recover database;
ORA-00279: change 5273378 generated at 12/08/2012 16:11:31 needed for thread 1
ORA-00289: suggestion : /archivelog/1_68_800989177.dbf
ORA-00280: change 5273378 for thread 1 is in sequence #68
Specify log: {
Log applied.
Media recovery complete.
根据redo logfile SCN高低，应用redo logfile 的顺序是redo logfile 69、redo logfile 70、redo logfile 71。
alert.log告警日志也显示redo logfile 恢复的顺序：
Sat Dec 08 16:43:52 EST 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 69 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/dbwdn/redo03.log
Sat Dec 08 16:43:52 EST 2012
Recovery of Online Redo Log: Thread 1 Group 1 Seq 70 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/dbwdn/redo01.log
Sat Dec 08 16:43:52 EST 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 71 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/dbwdn/redo02.log
Sat Dec 08 16:43:52 EST 2012
Media Recovery Complete (dbwdn)
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
应用完联机重做日志文件之后，数据库就处于一致状态，就完成了数据文件的完全恢复。
SQL> alter database open;
Database altered.
SQL>