Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › ctl 的 CHECKPOINT_CHANGE# 小于 数据文件头的 CHECKPO ...
发新帖
lingzhiluoye

0

积分

0

好友

8

主题

[物理/逻辑坏块] ctl 的 CHECKPOINT_CHANGE# 小于 数据文件头的 CHECKPOINT_CHANGE# ,数...

1#
发表于 2013-3-18 22:56:22 | 查看: 5152| 回复: 9
本帖最后由 lingzhiluoye 于 2013-3-18 22:57 编辑
  1. 我做了个实验：ctl 的 CHECKPOINT_CHANGE#  小于 数据文件头的 CHECKPOINT_CHANGE# ,数据库需要恢复

  3. SQL>  select  CHECKPOINT_CHANGE#   from v$$$$database;

  5. CHECKPOINT_CHANGE#
  6. ------------------
  7.             954672

  9. SQL> select CHECKPOINT_CHANGE#,last_change# from v$$$$datafile;

  11. CHECKPOINT_CHANGE# LAST_CHANGE#
  12. ------------------ ------------
  13.             954672
  14.             954672
  15.             954672
  16.             954672
  17.             954672


  20. SQL> select CHECKPOINT_CHANGE# from v$$$$datafile_header;

  22. CHECKPOINT_CHANGE#
  23. ------------------
  24.             957729
  25.             957729
  26.             957729
  27.             957729
  28.             957729


  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


  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'


  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


  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


  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


  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


  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


  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


  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'


  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


  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'
复制代码
为何还需要恢复
2#
发表于 2013-3-19 09:55:39
用这个脚本跑下
http://www.xifenfei.com/wp-content/uploads/2013/03/check_recover_db.sql
结果传上来

回复 只看该作者 道具 举报

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


已上传附件

回复 只看该作者 道具 举报

4#
发表于 2013-3-19 11:15:15
db_recover_xifenfei.html

db_recover_xifenfei.html

41.14 KB, 下载次数: 926

回复 只看该作者 道具 举报

5#
发表于 2013-3-19 13:15:08
任何提问 先说版本！！

回复 只看该作者 道具 举报

6#
发表于 2013-3-19 13:23:28
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  

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

回复 只看该作者 道具 举报

7#
发表于 2013-3-19 13:25:47
为什么不需要 恢复？

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

回复 只看该作者 道具 举报

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

你原帖里有吗？

回复 只看该作者 道具 举报

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

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

回复 只看该作者 道具 举报

10#
发表于 2013-3-19 22:46:21
本帖最后由 lingzhiluoye 于 2013-3-19 22:48 编辑

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

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

SQL> alter database open resetlogs;

结贴

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 11:56 , Processed in 0.056386 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569