度量快速开发平台-专业、快速的软件定制快开平台

标题: Oracle Redo log丢失几种情况的修复 [打印本页]

作者: caixuqad 时间: 2020-4-22 21:36
标题: Oracle Redo log丢失几种情况的修复
DATE:2014年6月17日
AUTHOR:chinaguanghui@gmail.com

NOTE:群里兄弟在讨论关于redo log丢失的几种情况，我这里实验一下。本次试验基于无备份且无归档的情况下模拟丢失重做日志的修复步骤。丢失重做日志的可能性有如下几种:

/*
首先是数据库是正常关闭还是非正常关闭。
其次要分析丢失的日志组是否为当前日志组
  对于非当前日志组的处理要容易些。那么就有四中可能。

当前日志，正常关闭。
当前日志，非正常关闭。
非当前日志，正常关闭。
非当前日志，非正常关闭。

这里我们先不讨论一组日志有多个成员的情况
*/

/*1.模拟丢失当前日志组,非正常关闭的修复*/
SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
freebase       OPEN

SQL> archive log list;
Database log mode             No Archive Mode
Automatic archival          Disabled
Archive destination          /u02/app/oracle/products/11.2.0.3/dbs/arch
Oldest online log sequence    3
Current log sequence          5

SQL> col member for a35
SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#
  4  ORDER BY l.GROUP#;

GROUP# ARC STATUS          MEMBER
---------- --- ---------------- -----------------------------------
      1 NO  CURRENT       /ORADATA/freebase/redo01.log
      2 NO  INACTIVE       /ORADATA/freebase/redo02.log
      3 NO  INACTIVE       /ORADATA/freebase/redo03.log

SQL> !mv /ORADATA/freebase/redo02.log /tmp

SQL> alter system switch logfile;

System altered.

SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#
  4  ORDER BY l.GROUP#;

GROUP# ARC STATUS          MEMBER
---------- --- ---------------- -----------------------------------
      1 NO  INACTIVE       /ORADATA/freebase/redo01.log
      2 NO  CURRENT       /ORADATA/freebase/redo02.log
      3 NO  INACTIVE       /ORADATA/freebase/redo03.log


SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
freebase       OPEN

SQL> !ls -l /ORADATA/freebase/redo02.log
ls: /ORADATA/freebase/redo02.log: No such file or directory

/*注意，这里实例并没有down掉，因为linux删除文件之后进程会继续持有语句柄*/
[root@vrhdg1 ~]# lsof | grep redo
oracle    405 oracle  258u    REG             8,1 52429312    655361 /ORADATA/freebase/redo01.log
oracle    405 oracle  259u    REG             8,1 52429312    655364 /ORADATA/freebase/redo02.log (deleted)
oracle    405 oracle  260u    REG             8,1 52429312    655362 /ORADATA/freebase/redo03.log

[root@vrhdg1 ~]# ps -ef | grep  -v grep | grep 405
oracle    405    1  0 14:07 ?       00:00:00 ora_lgwr_freebase

[root@vrhdg1 ~]# cd /proc/405/fd/
[root@vrhdg1 fd]# ls -l 259
lrwx------ 1 oracle oinstall 64 Jun 17 14:12 259 -> /ORADATA/freebase/redo02.log (deleted)

[root@vrhdg1 fd]# kill -9 405

SQL> startup mount;
SQL> col member for a35
SQL> SELECT l.GROUP#,l.ARCHIVED,l.STATUS,lf.MEMBER
  2  FROM V$LOG l,V$LOGFILE lf
  3  WHERE l.GROUP#=lf.GROUP#
  4  ORDER BY l.GROUP#;

GROUP# ARC STATUS          MEMBER
---------- --- ---------------- -----------------------------------
      1 NO  INACTIVE       /ORADATA/freebase/redo01.log
      2 NO  CURRENT       /ORADATA/freebase/redo02.log
      3 NO  INACTIVE       /ORADATA/freebase/redo03.log

SQL> !ls -l /ORADATA/freebase/redo02.log
ls: /ORADATA/freebase/redo02.log: No such file or directory

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/ORADATA/freebase/redo02.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance freebase (thread 1)
ORA-00312: online log 2 thread 1: '/ORADATA/freebase/redo02.log

SQL> recover database until cancel;
ORA-00279: change 1158338 generated at 06/17/2014 14:38:15 needed for thread 1
ORA-00289: suggestion :
/u02/app/oracle/products/11.2.0.3/dbs/arch1_5_850487771.dbf
ORA-00280: change 1158338 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ORADATA/freebase/system01.dbf'

作者: Adam丶鱼 时间: 2020-4-22 22:37

作者: fteair 时间: 2020-4-23 15:22

作者: caixuqad 时间: 2020-4-23 19:16

欢迎光临度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/)