ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 当redo丢失时使用_allow_resetlogs_corruption=true打开数据库并验证事务一制性

当redo丢失时使用_allow_resetlogs_corruption=true打开数据库并验证事务一制性

原创 Oracle 作者：fflixiang 时间：2014-04-15 15:41:22 0 删除 编辑

1、查看redo信息

点击(此处)折叠或打开

  1. SQL> set line 200
  2. SQL> select * from v$log;

  4.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  5. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

  7.          1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
  8.          2 1 3 4194304 1 YES ACTIVE 943456 15-APR-14
  9.          3 1 4 4194304 1 NO CURRENT 944183 15-APR-14
 2、查看控制文件、数据文件的scn

点击(此处)折叠或打开

  1. SQL> select checkpoint_change# from v$database;

  3. CHECKPOINT_CHANGE#
  4. ------------------

  6.             944183

  8. SQL> select name,checkpoint_change# from v$datafile;

  10. NAME CHECKPOINT_CHANGE#
  11. -------------------------------------------------- ------------------

  13. /u01/oracle/oradata/ora10g/system01.dbf 944183
  14. /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
  15. /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
  16. /u01/oracle/oradata/ora10g/users01.dbf 944183
  17. /u01/oracle/oradata/ora10g/jk.dbf 944183

  19. SQL> select name,checkpoint_change# from v$datafile_header;

  21. NAME CHECKPOINT_CHANGE#
  22. -------------------------------------------------- ------------------

  24. /u01/oracle/oradata/ora10g/system01.dbf 944183
  25. /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
  26. /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
  27. /u01/oracle/oradata/ora10g/users01.dbf 944183
  28. /u01/oracle/oradata/ora10g/jk.dbf 944183
打一个新的会话
session 1

点击(此处)折叠或打开

  1. SQL> conn scott/tiger;
  2. Connected.
  3. SQL> select * from e01;

  5.         ID NAME
  6. ---------- ----------

  8.          2 scott
  9.          1 jack
  10.          3 jerry

  12. SQL> insert into e01 values (4,\'lili\');

  14. 1 row created.

  16. SQL> commit
  17.   2 ;

  19. Commit complete.

  21. SQL> select * from e01;

  23.         ID NAME
  24. ---------- ----------

  26.          2 scott
  27.          1 jack
  28.          4 lili
  29.          3 jerry

  31. SQL> insert into e01 values (5,\'lucy\');

  33. 1 row created.

  35. SQL>
在  sys连接的会话中



点击(此处)折叠或打开

  1. SQL> alter system flush buffer_cache;

  3. System altered.

  5. SQL> select * from v$log;

  7.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  8. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

  10.          1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
  11.          2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
  12.          3 1 4 4194304 1 NO CURRENT 944183 15-APR-14

  14. SQL> alter system switch logfile;

  16. System altered.

  18. SQL> select * from v$log;

  20.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  21. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

  23.          1 1 5 4194304 1 NO CURRENT 944586 15-APR-14
  24.          2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
  25.          3 1 4 4194304 1 YES ACTIVE 944183 15-APR-14
再打一个会话
  session 2

点击(此处)折叠或打开

  1. SQL> conn scott/tiger;
  2. Connected.
  3. SQL> select * from e01;

  5.         ID NAME
  6. ---------- ----------

  8.          2 scott
  9.          1 jack
  10.          4 lili
  11.          3 jerry

  13. SQL> insert into e01 values (6,\'xiaoming\');

  15. 1 row created.

  17. SQL> commit;

  19. Commit complete.

  21. SQL> select * from e01;

  23.         ID NAME
  24. ---------- ----------

  26.          2 scott
  27.          6 xiaoming
  28.          1 jack
  29.          4 lili
  30.          3 jerry

在sys 连接的会话中

点击(此处)折叠或打开

  1. SQL> alter system checkpoint;

  3. System altered.

  5. SQL> select checkpoint_change# from v$database;

  7. CHECKPOINT_CHANGE#
  8. ------------------

  10.             944688

  12. SQL> select name,checkpoint_change# from v$datafile;

  14. NAME CHECKPOINT_CHANGE#
  15. -------------------------------------------------- ------------------

  17. /u01/oracle/oradata/ora10g/system01.dbf 944688
  18. /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
  19. /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
  20. /u01/oracle/oradata/ora10g/users01.dbf 944688
  21. /u01/oracle/oradata/ora10g/jk.dbf 944688

  23. SQL> select name,checkpoint_change# from v$datafile_header;

  25. NAME CHECKPOINT_CHANGE#
  26. -------------------------------------------------- ------------------

  28. /u01/oracle/oradata/ora10g/system01.dbf 944688
  29. /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
  30. /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
  31. /u01/oracle/oradata/ora10g/users01.dbf 944688
  32. /u01/oracle/oradata/ora10g/jk.dbf 944688

  34. SQL> select checkpoint_change# from v$database;

  36. CHECKPOINT_CHANGE#
  37. ------------------

  39.             944688


点击(此处)折叠或打开

  1. SQL> shutdown abort;
  2. ORACLE instance shut down.
  3. [oracle@ora10g ora10g]$ rm -rf redo02.log_bak
  4. [oracle@ora10g ora10g]$ rm -rf redo03.log_bak
  5. [oracle@ora10g ora10g]$ mv redo01.log redo01.log_bak
  6. [oracle@ora10g ora10g]$ mv redo03.log redo03.log_bak
  7. [oracle@ora10g ora10g]$ mv redo02.log redo02.log_bak
修改 pfile添加隐藏参数

点击(此处)折叠或打开

  1. ora10g.__db_cache_size=192937984

  3. ora10g.__java_pool_size=4194304

  5. ora10g.__large_pool_size=4194304

  7. ora10g.__shared_pool_size=75497472

  9. ora10g.__streams_pool_size=0

  11. *.audit_file_dest=\\\'/u01/oracle/admin/ora10g/adump\\\'

  13. *.background_dump_dest=\\\'/u01/oracle/admin/ora10g/bdump\\\'

  15. *.compatible=\\\'10.2.0.1.0\\\'

  17. *.control_files=\\\'/u01/oracle/oradata/ora10g/control01.ctl\\\',\\\'/u01/oracle/oradata/ora10g/control02.ctl\\\',\\\'/u01/oracle/oradata/ora10g/control03.ctl\\\'#Restore Controlfile

  19. *.core_dump_dest=\\\'/u01/oracle/admin/ora10g/cdump\\\'

  21. *.db_block_size=8192

  23. *.db_domain=\\\'\\\'

  25. *.db_file_multiblock_read_count=16

  27. *.db_name=\\\'ora10g\\\'

  29. *.dispatchers=\\\'(PROTOCOL=TCP) (SERVICE=ora10gXDB)\\\'

  31. *.job_queue_processes=10

  33. *.log_archive_dest_10=\\\'location=/arcora10g_dest10/\\\'

  35. *.open_cursors=300

  37. *.pga_aggregate_target=94371840

  39. *.processes=150

  41. *.remote_login_passwordfile=\\\'EXCLUSIVE\\\'

  43. *.sga_target=285212672

  45. *.undo_management=\\\'AUTO\\\'

  47. *.undo_tablespace=\\\'UNDOTBS1\\\'

  49. *.user_dump_dest=\\\'/u01/oracle/admin/ora10g/udump\\\'

  51. _allow_resetlogs_corruption=true
 使用 pfile开打数据库

点击(此处)折叠或打开

  1. sqlplus / as sysdba

  3. SQL*Plus: Release 10.2.0.1.0 - Production on Tue Apr 15 15:01:21 2014

  5. Copyright (c) 1982, 2005, Oracle. All rights reserved.

  7. Connected to an idle instance.

  9. SQL> startup mount pfile=/u01/initora10g.ora;
  10. ORACLE instance started.

  12. Total System Global Area 285212672 bytes
  13. Fixed Size 1218968 bytes
  14. Variable Size 83887720 bytes
  15. Database Buffers 192937984 bytes
  16. Redo Buffers 7168000 bytes
  17. Database mounted.
  18. SQL> recover database until cancel

  20. ORA-00279: change 944688 generated at 04/15/2014 14:36:17 needed for thread 1
  21. ORA-00289: suggestion : /arcora10g_dest10/1_5_844952420.dbf
  22. ORA-00280: change 944688 for thread 1 is in sequence #5


  25. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  26. ORA-00308: cannot open archived log \'/arcora10g_dest10/1_5_844952420.dbf\'
  27. ORA-27037: unable to obtain file status
  28. Linux Error: 2: No such file or directory
  29. Additional information: 3


  32. ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
  33. ORA-01194: file 1 needs more recovery to be consistent
  34. ORA-01110: data file 1: \'/u01/oracle/oradata/ora10g/system01.dbf\'


  37. SQL> alter database open resetlogs;

  39. alter database open resetlogs
  40. *
  41. ERROR at line 1:
  42. ORA-03113: end-of-file on communication channel

  44. alter database open;
  45. alter database open
  46. *
  47. ERROR at line 1:
  48. ORA-01034: ORACLE not available


  51. SQL> startup mount pfile=/u01/initora10g.ora;
  52. ORACLE instance started.

  54. Total System Global Area 285212672 bytes
  55. Fixed Size 1218968 bytes
  56. Variable Size 83887720 bytes
  57. Database Buffers 192937984 bytes
  58. Redo Buffers 7168000 bytes
  59. Database mounted.
  60. SQL> alter database open;

  62. Database altered.
在  session 1  中

点击(此处)折叠或打开

  1. SQL> conn scott/tiger;
  2. Connected.
  3. SQL> select * from e01;

  5.         ID NAME
  6. ---------- ----------

  8.          2 scott
  9.          6 xiaoming
  10.          1 jack
  11.          4 lili
  12.          3 jerry

总结：当使用隐藏参数_allow_resetlogs_corruption=true  打开数据库时，未提交的事务修改的数据未发生变化（由于互联网上有多篇文章说明dbwr 进程会将未提交的数据从buffer_cache 中写入到数据文件中，所以推测在resetlog打开数据库时做Instance Recovery时，使用undo 文件回滚了未提交的数据），此文未在产生检查点后未做事务并提交，如果有，还是会丢失。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/23748560/viewspace-1142590/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 在线日志文件丢失后事务的一制性验证
下一篇： 让完全like也走索引
请登录后发表评论 登录
全部评论
fflixiang

注册时间：2014-04-14

  • 博文量
    8
  • 访问量
    23375

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593