【恢复】Redo日志文件丢失的恢复

2019独角兽企业重金招聘Python工程师标准>>>

第一章 Redo日志文件丢失的恢复

 

1.1  online redolog file 丢失

联机Redo日志是Oracle数据库中比较核心的文件，当Redo日志文件异常之后，数据库就无法正常启动，而且有丢失据的风险，强烈建议在条件允许的情况下，对Redo日志进行多路镜像。需要注意的是，RMAN不能备份联机Redo日志文件。所以，联机Redo日志一旦出现故障，则只能进行清除日志了。清除日志文件即表明可以重用该文件。

1.1.1  数据库归档/非归档模式下inactive redo异常ORA-00316 ORA-00327

1.1.1.1  例一

 

SQL> startup mount

ORACLE instance started.

 

Total System Global Area  260046848 bytes

Fixed Size                 1266896 bytes

Variable Size             83888944 bytes

Database Buffers         167772160 bytes

Redo Buffers               7118848 bytes

Database mounted.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00316: log 2 of thread 1, type  in header is not log file

ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log'

 

SQL> col member for a40

SQL> set lines 120

SQL>  SELECT thread#,

  2          a.sequence#,

  3          a.group#,

  4          TO_CHAR (first_change#, '9999999999999999') "SCN",

  5          a.status,

  6          MEMBER

  7     FROM v$log a, v$logfile b

  8    WHERE a.group# = B.GROUP#

  9  ORDER BY a.sequence# DESC;

 

   THREAD#  SEQUENCE#    GROUP# SCN              STATUS          MEMBER

---------- ---------- ---------- ----------------- ---------------- -----------------------------------

         1        15         3           665697 CURRENT         /u01/oracle/oradata/XFF/redo03.log

         1        14         2           645619 INACTIVE        /u01/oracle/oradata/XFF/redo02.log

         1        13         1           625540 INACTIVE        /u01/oracle/oradata/XFF/redo01.log

 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00327: log 2 of thread 1, physical size  less than needed

ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/XFF/redo02.log'

 

SQL> alter database drop logfile group 2;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> alter database add logfile group 2 ('/u01/oracle/oradata/XFF/redo02.log') size 50M reuse;

 Database altered.

 

 

 

1.1.1.2  例二

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:46:16 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> col member for a50

SQL> set lines 120

SQL>   SELECT thread#,

  2           a.sequence#,

  3           a.group#,

  4           TO_CHAR (first_change#, '9999999999999999') "SCN",

  5           a.status,

  6           MEMBER

  7      FROM v$log a, v$logfile b

  8     WHERE a.group# = B.GROUP#

  9  ORDER BY a.sequence# DESC;

 

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER

---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------

         1         16          3           1209020 CURRENT          /u02/app/oracle/oradata/oratest/redo03.log

         1         15          1           1209017 INACTIVE         /u02/app/oracle/oradata/oratest/redo01.log

         1         14          2           1209012 INACTIVE         /u02/app/oracle/oradata/oratest/redo02.log

 

SQL> ! rm -rf /u02/app/oracle/oradata/oratest/redo01.log

 

SQL>

SQL> startup force;

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             297799040 bytes

Database Buffers          100663296 bytes

Redo Buffers                8503296 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 15390

Session ID: 125 Serial number: 5

 

 

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

告警日志：

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_lgwr_15484.trc:

ORA-00313: open failed for members of log group 1 of thread 1

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

 

 

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:48:39 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             297799040 bytes

Database Buffers          100663296 bytes

Redo Buffers                8503296 bytes

Database mounted.

SQL> alter database clear logfile group 1;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> col member for a50

SQL> set lines 120

SQL>   SELECT thread#,

  2           a.sequence#,

  3           a.group#,

  4           TO_CHAR (first_change#, '9999999999999999') "SCN",

  5           a.status,

  6           MEMBER

  7      FROM v$log a, v$logfile b

  8     WHERE a.group# = B.GROUP#

  9  ORDER BY a.sequence# DESC;

 

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER

---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------

         1         17          1           1229024 CURRENT          /u02/app/oracle/oradata/oratest/redo01.log

         1         16          3           1209020 INACTIVE         /u02/app/oracle/oradata/oratest/redo03.log

         1         14          2           1209012 INACTIVE         /u02/app/oracle/oradata/oratest/redo02.log

 

SQL>

 

 

 

1.1.2  正常关闭数据库current redo异常ORA-00316 ORA-01623

1.1.2.1  例一

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00316: log 1 of thread 1, type  in header is not log file

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'

 

SQL>  SELECT thread#,

  2          a.sequence#,

  3          a.group#,

  4          TO_CHAR (first_change#, '9999999999999999') "SCN",

  5          a.status,

  6          MEMBER

  7     FROM v$log a, v$logfile b

  8    WHERE a.group# = B.GROUP#

  9  ORDER BY a.sequence# DESC;

 

   THREAD#  SEQUENCE#    GROUP# SCN              STATUS          MEMBER

---------- ---------- ---------- ----------------- ---------------- ----------------------------------

         1        16         1           685918 CURRENT         /u01/oracle/oradata/XFF/redo01.log

         1        15         3           665697 INACTIVE        /u01/oracle/oradata/XFF/redo03.log

         1         0         2                0 UNUSED          /u01/oracle/oradata/XFF/redo02.log

 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

ALTER DATABASE CLEAR LOGFILE GROUP 1

*

ERROR at line 1:

ORA-00316: log 1 of thread 1, type 0 in header is not log file

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'

 

 

SQL> ALTER DATABASE drop logfile group 1;

ALTER DATABASE drop logfile group 1

*

ERROR at line 1:

ORA-01623: log 1 is current log for instance XFF (thread 1) - cannot drop

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'

 

SQL> recover database until cancel;

Media recovery complete.

SQL> alter database open resetlogs;

 

Database altered.

 

1.1.2.2  例二

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:52:49 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> col member for a50

SQL> set lines 120

SQL>   SELECT thread#,

  2           a.sequence#,

  3           a.group#,

  4           TO_CHAR (first_change#, '9999999999999999') "SCN",

  5           a.status,

  6           MEMBER

  7      FROM v$log a, v$logfile b

  8     WHERE a.group# = B.GROUP#

  9  ORDER BY a.sequence# DESC;

 

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER

---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------

         1         20          1           1229346 CURRENT          /u02/app/oracle/oradata/oratest/redo01.log

         1         19          3           1229343 INACTIVE         /u02/app/oracle/oradata/oratest/redo03.log

         1         18          2           1229340 INACTIVE         /u02/app/oracle/oradata/oratest/redo02.log

 

SQL> ! rm -rf /u02/app/oracle/oradata/oratest/redo01.log

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             297799040 bytes

Database Buffers          100663296 bytes

Redo Buffers                8503296 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 15837

Session ID: 125 Serial number: 5

 

 

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 

告警日志：

Errors in file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_15949.trc:

ORA-00313: open failed for members of log group 1 of thread

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'

Wed May 06 13:53:47 2015

ARC1 started with pid=21, OS id=15976

USER (ospid: 15949): terminating the instance due to error 313

System state dump requested by (instance=1, osid=15949), summary=[abnormal instance termination].

System State dumped to trace file /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_diag_15919.trc

Dumping diagnostic data in directory=[cdmp_20150506135347], requested by (instance=1, osid=15949), summary=[abnormal instance termination].

Instance terminated by USER, pid = 15949

 

[oracle@orcltest ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Wed May 6 13:54:28 2015

 

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

 

Connected to an idle instance.

 

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  409194496 bytes

Fixed Size                  2228864 bytes

Variable Size             297799040 bytes

Database Buffers          100663296 bytes

Redo Buffers                8503296 bytes

Database mounted.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

ALTER DATABASE CLEAR LOGFILE GROUP 1

*

ERROR at line 1:

ORA-00350: log 1 of instance oratest (thread 1) needs to be archived

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/oratest/redo01.log'

 

 

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

 

Database altered.

 

SQL> ALTER DATABASE OPEN;

 

Database altered.

 

SQL> col member for a50

SQL> set lines 120

SQL>   SELECT thread#,

  2           a.sequence#,

  3           a.group#,

  4           TO_CHAR (first_change#, '9999999999999999') "SCN",

  5           a.status,

  6           MEMBER

  7      FROM v$log a, v$logfile b

  8     WHERE a.group# = B.GROUP#

  9  ORDER BY a.sequence# DESC;

 

   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER

---------- ---------- ---------- ----------------- ---------------- --------------------------------------------------

         1         21          2           1229347 CURRENT          /u02/app/oracle/oradata/oratest/redo02.log

         1         19          3           1229343 INACTIVE         /u02/app/oracle/oradata/oratest/redo03.log

         1          0          1           1229346 UNUSED           /u02/app/oracle/oradata/oratest/redo01.log

 

SQL>

 

 

1.1.3  数据库异常关闭current/active redo异常ORA-00316 ORA-01624 ORA-01194

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-00316: log 1 of thread 1, type 0 in header is not log file

ORA-00312: online log 1 thread 1: '/u01/oracle/oradata/XFF/redo01.log'

 

SQL>  SELECT thread#,

  2         &nbs