ITPub博客

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

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

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

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

点击(此处)折叠或打开

  1. SQL> select * from v$log;

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

  4.          1 1 2 4194304 1 NO CURRENT 889122 14-APR-14
  5.          2 1 0 4194304 1 YES UNUSED 0
  6.          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;

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

  6.          1 jack

  7.  

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

  9. 1 row created.

  10. SQL> commit ;

  11. Commit complete.
session 2

点击(此处)折叠或打开

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

  5. SQL> commit;

  6. Commit complete.
  7. SQL> select * from e01;

  8.         ID NAME
  9. ---------- ----------

  10.          2 scott
  11.          1 jack
  12.          3 jerry

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


点击(此处)折叠或打开

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

  2. 1 row created.

jk在sys用户连接的会话中

点击(此处)折叠或打开

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

  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 2 4194304 1 YES ACTIVE 889122 14-APR-14
  15.          2 1 3 4194304 1 NO CURRENT 892560 14-APR-14
  16.          3 1 1 4194304 1 YES INACTIVE 886456 14-APR-14

session 2
 提交

点击(此处)折叠或打开

  1. SQL> commit;

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

点击(此处)折叠或打开

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

  2. 1 row created.

  3. SQL> commit
  4.   2 ;

  5. Commit complete.

  6. SQL> select * from e01;

  7.         ID NAME
  8. ---------- ----------

  9.          2 scott
  10.          1 jack
  11.          3 jerry
  12.          4 lili
  13.          6 tom

在sys用户连接的会话中

点击(此处)折叠或打开

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

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

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

  6. Connected to an idle instance.

  7. SQL> startup
  8. ORACLE instance started.

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

点击(此处)折叠或打开

  1. rman target /

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

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

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

  5. RMAN>

  6. RMAN> restore database;

  7. Starting restore at 14-APR-14
  8. using target database control file instead of recovery catalog
  9. allocated channel: ORA_DISK_1
  10. channel ORA_DISK_1: sid=156 devtype=DISK

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

  23. RMAN> recover database;

  24. Starting recover at 14-APR-14
  25. using channel ORA_DISK_1

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

点击(此处)折叠或打开

  1. sqlplus / as sysdba

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

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


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

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


  11. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
  12. auto
  13. ORA-00308: cannot open archived log \'/arcora10g_dest10/1_3_844862062.dbf\'
  14. ORA-27037: unable to obtain file status
  15. Linux Error: 2: No such file or directory
  16. Additional information: 3


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

点击(此处)折叠或打开

  1. SQL> alter database open resetlogs;

  2. Database altered.

  3.  

  4. SQL> select * from v$log;

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

  7.          1 1 0 4194304 1 YES UNUSED 0
  8.          2 1 1 4194304 1 NO CURRENT 892561 14-APR-14
  9.          3 1 0 4194304 1 YES UNUSED 0

session 1
 

点击(此处)折叠或打开

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

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

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


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

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

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2014-04-14

  • 博文量
    7
  • 访问量
    18037