搞懂介质恢复-使用备份的控制文恢复数据库
[@more@]数据文件user01.dbf丢失，控制文件丢失
--restore controlfile;
--restore datafile 4;
--目前数据库为mount状态：
查看相关SCN:
--备份控制文件记录的CN
SQL> select checkpoint_change# from v$database;
CHECKPOINT_CHANGE#
------------------
5277885
--备份控制文件记录的SCN
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5277930
2 5277930
3 5277930
4 5277930
5 5277930
6 5277930
7 5277930
8 5277930
--当前数据库数据文件的SCN
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 5278823
2 5278823
3 5278823
4 5277930
5 5278823
6 5278823
7 5278823
8 5278823
--备份控制文件记录的redo SCN
SQL> select checkpoint_change# from v$thread;
CHECKPOINT_CHANGE#
------------------
5277885
--备份控制文件记录的联机重做日志的文件：
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 1 YES INACTIVE 5277883 08-DEC-12
3 1 3 52428800 1 YES INACTIVE 5277875 08-DEC-12
2 1 5 52428800 1 NO CURRENT 5277885 08-DEC-12
上述的SCN,很显然，备份控制文件记录的SCN比数据文件的SCN要旧。
--恢复数据库
SQL> recover database using backup controlfile;
ORA-00279: change 5277930 generated at 12/08/2012 21:44:58 needed for thread 1
ORA-00289: suggestion : /archivelog/1_5_801524425.dbf
ORA-00280: change 5277930 for thread 1 is in sequence #5
Specify log: {
ORA-00279: change 5278265 generated at 12/08/2012 21:54:42 needed for thread 1
ORA-00289: suggestion : /archivelog/1_6_801524425.dbf
ORA-00280: change 5278265 for thread 1 is in sequence #6
ORA-00278: log file '/archivelog/1_5_801524425.dbf' no longer needed for this
recovery
Specify log: {
ORA-00279: change 5278267 generated at 12/08/2012 21:54:43 needed for thread 1
ORA-00289: suggestion : /archivelog/1_7_801524425.dbf
ORA-00280: change 5278267 for thread 1 is in sequence #7
ORA-00278: log file '/archivelog/1_6_801524425.dbf' no longer needed for this
recovery
Specify log: {
ORA-00279: change 5278271 generated at 12/08/2012 21:54:50 needed for thread 1
ORA-00289: suggestion : /archivelog/1_8_801524425.dbf
ORA-00280: change 5278271 for thread 1 is in sequence #8
ORA-00278: log file '/archivelog/1_7_801524425.dbf' no longer needed for this
recovery
......
......
ORA-00279: change 5278817 generated at 12/08/2012 22:00:49 needed for thread 1
ORA-00289: suggestion : /archivelog/1_35_801524425.dbf
ORA-00280: change 5278817 for thread 1 is in sequence #35
ORA-00278: log file '/archivelog/1_34_801524425.dbf' no longer needed for this
recovery
Specify log: {
ORA-00279: change 5278819 generated at 12/08/2012 22:00:52 needed for thread 1
ORA-00289: suggestion : /archivelog/1_36_801524425.dbf
ORA-00280: change 5278819 for thread 1 is in sequence #36
ORA-00278: log file '/archivelog/1_35_801524425.dbf' no longer needed for this
recovery
Specify log: {
ORA-00279: change 5278821 generated at 12/08/2012 22:00:53 needed for thread 1
ORA-00289: suggestion : /archivelog/1_37_801524425.dbf
ORA-00280: change 5278821 for thread 1 is in sequence #37
ORA-00278: log file '/archivelog/1_36_801524425.dbf' no longer needed for this
recovery
Specify log: {
ORA-00279: change 5278823 generated at 12/08/2012 22:00:55 needed for thread 1
ORA-00289: suggestion : /archivelog/1_38_801524425.dbf
ORA-00280: change 5278823 for thread 1 is in sequence #38
ORA-00278: log file '/archivelog/1_37_801524425.dbf' no longer needed for this
recovery
Specify log: {
ORA-00308: cannot open archived log '/archivelog/1_38_801524425.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
最后显示
ORA-00308: cannot open archived log '/archivelog/1_38_801524425.dbf'
ORA-27037: unable to obtain file status
说明sequence为38是状态为CURRENT的联机重做日志文件.
--转储最新的日志文件：
SQL> alter system dump logfile '/u01/app/oracle/oradata/dbwdn/redo01.log';
System altered.
descrip:"Thread 0001, Seq# 0000000037, SCN 0x000000508c65-0x000000508c67"
Low scn: 0x0000.00508c65 (5278821) 12/08/2012 22:00:53
Next scn: 0x0000.00508c67 (5278823) 12/08/2012 22:00:55
SQL> alter system dump logfile '/u01/app/oracle/oradata/dbwdn/redo02.log';
System altered.
descrip:"Thread 0001, Seq# 0000000038, SCN 0x000000508c67-0xffffffffffff"
Low scn: 0x0000.00508c67 (5278823) 12/08/2012 22:00:55
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
SQL> alter system dump logfile '/u01/app/oracle/oradata/dbwdn/redo03.log';
System altered.
descrip:"Thread 0001, Seq# 0000000036, SCN 0x000000508c63-0x000000508c65"
Low scn: 0x0000.00508c63 (5278819) 12/08/2012 22:00:52
Next scn: 0x0000.00508c65 (5278821) 12/08/2012 22:00:53
从Low scn: 0x0000.00508c67 (5278823) 12/08/2012 22:00:55看，
sequence为38日志文件，对应的联机重做日志文件为：/u01/app/oracle/oradata/dbwdn/redo02.log
记录的SCN 5278823就是数据库要恢复的最后的SCN.这个SCN恢复之后，数据库就可以打开，数据没有
任何丢失，因为应用全部的归档日志，应用全部的联机重做日志。
尝试不应用/u01/app/oracle/oradata/dbwdn/redo02.log，能否打开数据库？
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: '/u01/app/oracle/oradata/dbwdn/system01.dbf'
打开不开数据库
--下面进行应用/u01/app/oracle/oradata/dbwdn/redo02.log，因为备份控制文件并没有该日志信息，故需要手工进入即可：
SQL> recover database using backup controlfile;
ORA-00279: change 5278823 generated at 12/08/2012 22:00:55 needed for thread 1
ORA-00289: suggestion : /archivelog/1_38_801524425.dbf
ORA-00280: change 5278823 for thread 1 is in sequence #38
Specify log: {
/u01/app/oracle/oradata/dbwdn/redo02.log
Log applied.
Media recovery complete.
--成功打开数据库
SQL> alter database open resetlogs;
Database altered.
因为应用了全部的归档日志，全部的联机重做日志，虽然已resetlogs打开数据库，但是没有丢失任何数据。
--立刻全备数据库。