ITPub博客

首页 > 数据库 > 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;

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

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

点击(此处)折叠或打开

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

  2. CHECKPOINT_CHANGE#
  3. ------------------

  4.             944183

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

  6. NAME CHECKPOINT_CHANGE#
  7. -------------------------------------------------- ------------------

  8. /u01/oracle/oradata/ora10g/system01.dbf 944183
  9. /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
  10. /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
  11. /u01/oracle/oradata/ora10g/users01.dbf 944183
  12. /u01/oracle/oradata/ora10g/jk.dbf 944183

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

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

  16. /u01/oracle/oradata/ora10g/system01.dbf 944183
  17. /u01/oracle/oradata/ora10g/undotbs01.dbf 944183
  18. /u01/oracle/oradata/ora10g/sysaux01.dbf 944183
  19. /u01/oracle/oradata/ora10g/users01.dbf 944183
  20. /u01/oracle/oradata/ora10g/jk.dbf 944183
打一个新的会话
session 1

点击(此处)折叠或打开

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

  4.         ID NAME
  5. ---------- ----------

  6.          2 scott
  7.          1 jack
  8.          3 jerry

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

  10. 1 row created.

  11. SQL> commit
  12.   2 ;

  13. Commit complete.

  14. SQL> select * from e01;

  15.         ID NAME
  16. ---------- ----------

  17.          2 scott
  18.          1 jack
  19.          4 lili
  20.          3 jerry

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

  22. 1 row created.

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



点击(此处)折叠或打开

  1. SQL> alter system flush buffer_cache;

  2. System altered.

  3. SQL> select * from v$log;

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

  6.          1 1 2 4194304 1 YES INACTIVE 941153 15-APR-14
  7.          2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
  8.          3 1 4 4194304 1 NO CURRENT 944183 15-APR-14

  9. SQL> alter system switch logfile;

  10. System altered.

  11. SQL> select * from v$log;

  12.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  13. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

  14.          1 1 5 4194304 1 NO CURRENT 944586 15-APR-14
  15.          2 1 3 4194304 1 YES INACTIVE 943456 15-APR-14
  16.          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;

  4.         ID NAME
  5. ---------- ----------

  6.          2 scott
  7.          1 jack
  8.          4 lili
  9.          3 jerry

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

  11. 1 row created.

  12. SQL> commit;

  13. Commit complete.

  14. SQL> select * from e01;

  15.         ID NAME
  16. ---------- ----------

  17.          2 scott
  18.          6 xiaoming
  19.          1 jack
  20.          4 lili
  21.          3 jerry

在sys 连接的会话中

点击(此处)折叠或打开

  1. SQL> alter system checkpoint;

  2. System altered.

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

  4. CHECKPOINT_CHANGE#
  5. ------------------

  6.             944688

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

  8. NAME CHECKPOINT_CHANGE#
  9. -------------------------------------------------- ------------------

  10. /u01/oracle/oradata/ora10g/system01.dbf 944688
  11. /u01/oracle/oradata/ora10g/undotbs01.dbf 944688
  12. /u01/oracle/oradata/ora10g/sysaux01.dbf 944688
  13. /u01/oracle/oradata/ora10g/users01.dbf 944688
  14. /u01/oracle/oradata/ora10g/jk.dbf 944688

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

  16. NAME CHECKPOINT_CHANGE#
  17. -------------------------------------------------- ------------------

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

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

  24. CHECKPOINT_CHANGE#
  25. ------------------

  26.             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

  2. ora10g.__java_pool_size=4194304

  3. ora10g.__large_pool_size=4194304

  4. ora10g.__shared_pool_size=75497472

  5. ora10g.__streams_pool_size=0

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

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

  8. *.compatible=\\\'10.2.0.1.0\\\'

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

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

  11. *.db_block_size=8192

  12. *.db_domain=\\\'\\\'

  13. *.db_file_multiblock_read_count=16

  14. *.db_name=\\\'ora10g\\\'

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

  16. *.job_queue_processes=10

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

  18. *.open_cursors=300

  19. *.pga_aggregate_target=94371840

  20. *.processes=150

  21. *.remote_login_passwordfile=\\\'EXCLUSIVE\\\'

  22. *.sga_target=285212672

  23. *.undo_management=\\\'AUTO\\\'

  24. *.undo_tablespace=\\\'UNDOTBS1\\\'

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

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

点击(此处)折叠或打开

  1. sqlplus / as sysdba

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

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

  4. Connected to an idle instance.

  5. SQL> startup mount pfile=/u01/initora10g.ora;
  6. ORACLE instance started.

  7. Total System Global Area 285212672 bytes
  8. Fixed Size 1218968 bytes
  9. Variable Size 83887720 bytes
  10. Database Buffers 192937984 bytes
  11. Redo Buffers 7168000 bytes
  12. Database mounted.
  13. SQL> recover database until cancel

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


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


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


  25. SQL> alter database open resetlogs;

  26. alter database open resetlogs
  27. *
  28. ERROR at line 1:
  29. ORA-03113: end-of-file on communication channel

  30. alter database open;
  31. alter database open
  32. *
  33. ERROR at line 1:
  34. ORA-01034: ORACLE not available


  35. SQL> startup mount pfile=/u01/initora10g.ora;
  36. ORACLE instance started.

  37. Total System Global Area 285212672 bytes
  38. Fixed Size 1218968 bytes
  39. Variable Size 83887720 bytes
  40. Database Buffers 192937984 bytes
  41. Redo Buffers 7168000 bytes
  42. Database mounted.
  43. SQL> alter database open;

  44. Database altered.
在  session 1  中

点击(此处)折叠或打开

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

  4.         ID NAME
  5. ---------- ----------

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

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

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

请登录后发表评论 登录
全部评论

注册时间:2014-04-14

  • 博文量
    7
  • 访问量
    18038