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

首页 > 数据库 > Oracle > 在线日志文件丢失后事务的一制性验证

在线日志文件丢失后事务的一制性验证

原创 Oracle 作者：fflixiang 时间：2014-04-14 17:45:57 0 删除 编辑

1、查看当前在线日志文件

点击(此处)折叠或打开

  1. SQL> select * from v$log;

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

  6.          1 1 2 4194304 1 NO CURRENT 889122 14-APR-14
  7.          2 1 0 4194304 1 YES UNUSED 0
  8.          3 1 1 4194304 1 YES INACTIVE 886456 14-APR-14

 2、再打开两个会话，别分做交易
在session 1

点击(此处)折叠或打开

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

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

  8.          1 jack

  10.  

  12. SQL> insert into e01 values (2,\'scott\');

  14. 1 row created.

  16. SQL> commit ;

  18. Commit complete.
session 2

点击(此处)折叠或打开

  1. SQL> conn scott/tiger
  2. Connected.
  3. SQL> insert into e01 values (3,\'jerry\');
  4. 1 row created.

  6. SQL> commit;

  8. Commit complete.
  9. SQL> select * from e01;

  11.         ID NAME
  12. ---------- ----------

  14.          2 scott
  15.          1 jack
  16.          3 jerry

再插入一行，但不暂时不提交


点击(此处)折叠或打开

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

  3. 1 row created.

jk在sys用户连接的会话中

点击(此处)折叠或打开

  1. 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 NO CURRENT 889122 14-APR-14
  11.          2 1 0 4194304 1 YES UNUSED 0
  12.          3 1 1 4194304 1 YES INACTIVE 886456 14-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 2 4194304 1 YES ACTIVE 889122 14-APR-14
  24.          2 1 3 4194304 1 NO CURRENT 892560 14-APR-14
  25.          3 1 1 4194304 1 YES INACTIVE 886456 14-APR-14

session 2
 提交

点击(此处)折叠或打开

  1. SQL> commit;

  3. Commit complete.
再插入一行并提交

点击(此处)折叠或打开

  1. SQL> insert into e01 values (6,\'tom\');

  3. 1 row created.

  5. SQL> commit
  6.   2 ;

  8. Commit complete.

  10. SQL> select * from e01;

  12.         ID NAME
  13. ---------- ----------

  15.          2 scott
  16.          1 jack
  17.          3 jerry
  18.          4 lili
  19.          6 tom

在sys用户连接的会话中

点击(此处)折叠或打开

  1. mv redo02.log redo02.log_bak;
  2. mv redo03.log redo03.log_bak;
  3. sqlplus / as sysdba

  5. SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 14 14:51:15 2014

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

  9. Connected to an idle instance.

  11. SQL> startup
  12. ORACLE instance started.

  14. Total System Global Area 285212672 bytes
  15. Fixed Size 1218968 bytes
  16. Variable Size 83887720 bytes
  17. Database Buffers 192937984 bytes
  18. Redo Buffers 7168000 bytes
  19. Database mounted.
  20. ORA-00313: open failed for members of log group 2 of thread 1
  21. ORA-00312: online log 2 thread 1: \'/u01/oracle/oradata/ora10g/redo02.log\'
  22. ORA-27037: unable to obtain file status
  23. Linux Error: 2: No such file or directory
  24. Additional information: 3
使用之前的备份恢复

点击(此处)折叠或打开

  1. rman target /

  3. Recovery Manager: Release 10.2.0.1.0 - Production on Mon Apr 14 15:02:49 2014

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

  7. connected to target database: ORA10G (DBID=4131462994, not open)

  9. RMAN>

  11. RMAN> restore database;

  13. Starting restore at 14-APR-14
  14. using target database control file instead of recovery catalog
  15. allocated channel: ORA_DISK_1
  16. channel ORA_DISK_1: sid=156 devtype=DISK

  18. channel ORA_DISK_1: starting datafile backupset restore
  19. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  20. restoring datafile 00001 to /u01/oracle/oradata/ora10g/system01.dbf
  21. restoring datafile 00002 to /u01/oracle/oradata/ora10g/undotbs01.dbf
  22. restoring datafile 00003 to /u01/oracle/oradata/ora10g/sysaux01.dbf
  23. restoring datafile 00004 to /u01/oracle/oradata/ora10g/users01.dbf
  24. restoring datafile 00005 to /u01/oracle/oradata/ora10g/jk.dbf
  25. channel ORA_DISK_1: reading from backup piece /u01/oracle/bak/ora_09p5nco0_1_1.bak
  26. channel ORA_DISK_1: restored backup piece 1
  27. piece handle=/u01/oracle/bak/ora_09p5nco0_1_1.bak tag=TAG20140414T141320
  28. channel ORA_DISK_1: restore complete, elapsed time: 00:01:51
  29. Finished restore at 14-APR-14

  31. RMAN> recover database;

  33. Starting recover at 14-APR-14
  34. using channel ORA_DISK_1

  36. starting media recovery
  37. media recovery failed
  38. RMAN-00571: ===========================================================
  39. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  40. RMAN-00571: ===========================================================
  41. RMAN-03002: failure of recover command at 04/14/2014 15:05:24
  42. ORA-00283: recovery session canceled due to errors
  43. RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
  44.  start
  45. ORA-00283: recovery session canceled due to errors
  46. ORA-00313: open failed for members of log group 2 of thread 1
  47. ORA-00312: online log 2 thread 1: \'/u01/oracle/oradata/ora10g/redo02.log\'
  48. ORA-27037: unable to obtain file status
  49. Linux Error: 2: No such file or directory
  50. Additional information: 3

点击(此处)折叠或打开

  1. sqlplus / as sysdba

  3. SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 14 15:07:31 2014

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


  8. Connected to:
  9. Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
  10. With the Partitioning, OLAP and Data Mining options

  12. SQL> recover database until cancel;
  13. ORA-00279: change 892560 generated at 04/14/2014 14:38:52 needed for thread 1
  14. ORA-00289: suggestion : /arcora10g_dest10/1_3_844862062.dbf
  15. ORA-00280: change 892560 for thread 1 is in sequence #3


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


  26. ORA-00308: cannot open archived log \'/arcora10g_dest10/1_3_844862062.dbf\'
  27. ORA-27037: unable to obtain file status
  28. Linux Error: 2: No such file or directory
  29. Additional information: 3
resetlogs方式打开数据库

点击(此处)折叠或打开

  1. SQL> alter database open resetlogs;

  3. Database altered.

  5.  

  7. SQL> select * from v$log;

  9.     GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
  10. ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------

  12.          1 1 0 4194304 1 YES UNUSED 0
  13.          2 1 1 4194304 1 NO CURRENT 892561 14-APR-14
  14.          3 1 0 4194304 1 YES UNUSED 0

session 1
 

点击(此处)折叠或打开

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

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

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


总结：当redo丢失后，在使用备份恢复还原数据库中应用归档日志还原数据库时，如果归档中有未提交的事务是会正常回滚事务，本想做_allow_resetlogs_corruption=true 隐含参数打开数据库，今天不行，明天做这个。

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

0
0
上一篇： 没有了~
下一篇： 当redo丢失时使用_allow_resetlogs_corruption=true打开数据库并验证事务一制性
请登录后发表评论 登录
全部评论
fflixiang

注册时间：2014-04-14

  • 博文量
    8
  • 访问量
    23375

最新文章

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

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

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

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