ctl 的 CHECKPOINT_CHANGE# 小于 数据文件头的 CHECKPOINT_CHANGE# ,数...

lingzhiluoye

1. 我做了个实验：ctl 的 CHECKPOINT_CHANGE#  小于 数据文件头的 CHECKPOINT_CHANGE# ,数据库需要恢复
   
2. 
   
3. SQL>  select  CHECKPOINT_CHANGE#   from v$$$$database;
   
4. 
   
5. CHECKPOINT_CHANGE#
   
6. ------------------
   
7.             954672
   
8. 
   
9. SQL> select CHECKPOINT_CHANGE#,last_change# from v$$$$datafile;
   
10. 
    
11. CHECKPOINT_CHANGE# LAST_CHANGE#
    
12. ------------------ ------------
    
13.             954672
    
14.             954672
    
15.             954672
    
16.             954672
    
17.             954672
    
18. 
    
19. 
    
20. SQL> select CHECKPOINT_CHANGE# from v$$$$datafile_header;
    
21. 
    
22. CHECKPOINT_CHANGE#
    
23. ------------------
    
24.             957729
    
25.             957729
    
26.             957729
    
27.             957729
    
28.             957729
    
29. 
    
30. 
    
31. SQL> alter database open;
    
32. alter database open
    
33. *
    
34. ERROR at line 1:
    
35. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
    
36. 
    
37. 
    
38. SQL> alter database open resetlogs;
    
39. alter database open resetlogs
    
40. *
    
41. ERROR at line 1:
    
42. ORA-01194: file 1 needs more recovery to be consistent
    
43. ORA-01110: data file 1: '/u01/oracle/oradata/ora10g/system01.dbf'
    
44. 
    
45. 
    
46. SQL> recover database;
    
47. ORA-00283: recovery session canceled due to errors
    
48. ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
    
49. 
    
50. 
    
51. SQL> recover database using backup controlfile;
    
52. ORA-00279: change 957662 generated at 03/18/2013 21:58:06 needed for thread 1
    
53. ORA-00289: suggestion : /archieve_log/ora10g/1_2_810147007.arc
    
54. ORA-00280: change 957662 for thread 1 is in sequence #2
    
55. 
    
56. 
    
57. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
58. auto
    
59. ORA-00279: change 957724 generated at 03/18/2013 22:07:10 needed for thread 1
    
60. ORA-00289: suggestion : /archieve_log/ora10g/1_3_810147007.arc
    
61. ORA-00280: change 957724 for thread 1 is in sequence #3
    
62. ORA-00278: log file '/archieve_log/ora10g/1_2_810147007.arc' no longer needed
    
63. for this recovery
    
64. 
    
65. 
    
66. ORA-00279: change 957726 generated at 03/18/2013 22:07:12 needed for thread 1
    
67. ORA-00289: suggestion : /archieve_log/ora10g/1_4_810147007.arc
    
68. ORA-00280: change 957726 for thread 1 is in sequence #4
    
69. ORA-00278: log file '/archieve_log/ora10g/1_3_810147007.arc' no longer needed
    
70. for this recovery
    
71. 
    
72. 
    
73. ORA-00279: change 957729 generated at 03/18/2013 22:07:17 needed for thread 1
    
74. ORA-00289: suggestion : /archieve_log/ora10g/1_5_810147007.arc
    
75. ORA-00280: change 957729 for thread 1 is in sequence #5
    
76. ORA-00278: log file '/archieve_log/ora10g/1_4_810147007.arc' no longer needed
    
77. for this recovery
    
78. 
    
79. 
    
80. ORA-00308: cannot open archived log '/archieve_log/ora10g/1_5_810147007.arc'
    
81. ORA-27037: unable to obtain file status
    
82. Linux Error: 2: No such file or directory
    
83. Additional information: 3
    
84. 
    
85. 
    
86. SQL> alter database open resetlogs;
    
87. alter database open resetlogs
    
88. *
    
89. ERROR at line 1:
    
90. ORA-01113: file 1 needs media recovery
    
91. ORA-01110: data file 1: '/u01/oracle/oradata/ora10g/system01.dbf'
    
92. 
    
93. 
    
94. SQL> recover database using backup controlfile;
    
95. ORA-00279: change 957729 generated at 03/18/2013 22:07:17 needed for thread 1
    
96. ORA-00289: suggestion : /archieve_log/ora10g/1_5_810147007.arc
    
97. ORA-00280: change 957729 for thread 1 is in sequence #5
    
98. 
    
99. 
    
100. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
     
101. cancel
     
102. Media recovery cancelled.
     
103. SQL>
     
104. SQL>
     
105. SQL> alter database open resetlogs;
     
106. alter database open resetlogs
     
107. *
     
108. ERROR at line 1:
     
109. ORA-01113: file 1 needs media recovery
     
110. ORA-01110: data file 1: '/u01/oracle/oradata/ora10g/system01.dbf'

复制代码

为何还需要恢复

lingzhiluoye

总结：
controlfile checkpoint_change# <  datafile header checkpoint_change# 的时候，定要恢复

SQL>recover database using backup controlfile;
恢复原理： 先应用归档日志，再需要手动指定 CURRENT 日志

SQL> alter database open resetlogs;

结贴

lingzhiluoye

Maclean Liu(刘相兵 发表于 2013-3-19 13:29
你原帖里有吗？

下次注意，放到附件里面了

Maclean Liu(刘相兵

lingzhiluoye 发表于 2013-3-19 13:23
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production  
PL/SQL Release 11.1 ...

你原帖里有吗？

Maclean Liu(刘相兵

为什么不需要 恢复？

controlfile checkpoint_change# <  datafile header checkpoint_change# 为什么不需要恢复？

照你这么说   我先shutdown abort一把， 然后restore一个老的控制文件，而老的控制文件的checkpoint_change< datafile里的， 那么这个场景满足你的条件也不用恢复咯？


深入了解Oracle前滚恢复rolling forward(一) http://www.askmaclean.com/archives/know-more-rolling-forward.html

lingzhiluoye

Maclean Liu(刘相兵 发表于 2013-3-19 13:15
任何提问 先说版本！！

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production  
PL/SQL Release 11.1.0.7.0 - Production  
CORE 11.1.0.7.0 Production  
TNS for Linux: Version 11.1.0.7.0 - Production  
NLSRTL Version 11.1.0.7.0 - Production  

附件里面有啊，大刘，冤枉我了啊。

Maclean Liu(刘相兵

任何提问 先说版本！！

lingzhiluoye

db_recover_xifenfei.html

lingzhiluoye

dbatravel 发表于 2013-3-19 09:55
用这个脚本跑下
http://www.xifenfei.com/wp-content/uploads/2013/03/check_recover_db.sql
结果传上来 ...

已上传附件

dbatravel

用这个脚本跑下
http://www.xifenfei.com/wp-content/uploads/2013/03/check_recover_db.sql
结果传上来