在线重做日志文件丢失的故障分析及解决过程

最新推荐文章于 2025-05-09 14:45:58 发布

cucany584825

最新推荐文章于 2025-05-09 14:45:58 发布

阅读量388

点赞数

本文记录了一次在线重做日志文件丢失的故障排查与解决过程，通过增加日志组并重新配置解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在线重做日志文件丢失的故障分析及解决过程

告警日志报错信息如下：

Fri Oct 28 23:57:05 2016

Errors in file /u01/app/oracle/diag/rdbms/hnxdb/hnxdb/trace/hnxdb_m000_7897.trc:

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

ORA-00312: online log 1 thread 1: '/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_1_c4bwxsbp_.log'

ORA-27037: unable to obtain file status

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

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/hnxdb/hnxdb/trace/hnxdb_m000_7897.trc:

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

ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_2_c4bwxsfg_.log'

ORA-27037: unable to obtain file status

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

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/hnxdb/hnxdb/trace/hnxdb_m000_7897.trc:

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

ORA-00312: online log 3 thread 1: '/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_3_c4bwxsj0_.log'

ORA-27037: unable to obtain file status

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

Additional information: 3

告警信息提示很明显，数据库找不到位于'/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/’目录下的日志文件。我们首先进入数据库中，查询数据库中关于日志状态及日志文件的记录信息。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC

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

STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 4174 52428800 512 2 NO

ACTIVE 253979892 28-OCT-16 253985370 28-OCT-16

2 1 4175 52428800 512 2 NO

ACTIVE 253985370 28-OCT-16 253985446 28-OCT-16

3 1 4176 52428800 512 2 NO

CURRENT 253985446 28-OCT-16 2.8147E+14

3 rows selected.

SQL> col member for a60

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /app/oradata/HNXDB/onlinelog/o1_mf_1_c4bwxs95_.log NO

1 INVALID ONLINE /u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_1_c YES

4bwxsbp_.log

2 ONLINE /app/oradata/HNXDB/onlinelog/o1_mf_2_c4bwxsd1_.log NO

2 INVALID ONLINE /u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_2_c YES

4bwxsfg_.log

3 ONLINE /app/oradata/HNXDB/onlinelog/o1_mf_3_c4bwxsgo_.log NO

3 INVALID ONLINE /u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_3_c YES

4bwxsj0_.log

GROUP# STATUS TYPE MEMBER IS_

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

6 rows selected.

可以看到日志切换状态正常，日志文件为双路冗余，其中’ /u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/’目录下的日志文件被标记为无效状态，在操作系统层面查看该目录下是否存在对应的日志文件。

[oracle@localhost onlinelog]$ pwd

/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog

[oracle@localhost onlinelog]$ ls

o1_mf_1_co5f05kn_.log o1_mf_2_co5f06fj_.log o1_mf_3_co5f07ty_.log

[oracle@localhost onlinelog]$

可以看到，这里的文件与查询出来的文件名是不一样的，也就是说数据库查询到的该路径下的日志文件确实不存在。遇到这种问题呢，我们一般的解决方法就是增加在线重做日志组，通过切换，将有问题的日志组删除并重建，此处我们就采取这种思路进行解决。对日志文件大小重定义，也可以参考此方法，详细过程如下。

SQL> archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 4174

Current log sequence 4176

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 4174 52428800 512 2 NO INACTIVE 253979892 28-OCT-16 253985370 28-OCT-16

2 1 4175 52428800 512 2 NO INACTIVE 253985370 28-OCT-16 253985446 28-OCT-16

3 1 4176 52428800 512 2 NO CURRENT 253985446 28-OCT-16 2.8147E+14

SQL> alter database add logfile group 4 ('/app/oradata/HNXDB/onlinelog/redo41.log','/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/redo42.log') size 150M;

alter database add logfile group 4 ('/app/oradata/HNXDB/onlinelog/redo41.log','/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/redo42.log) size 150M

ERROR at line 1:

ORA-01756: quoted string not properly terminated

SQL> alter database add logfile group 4 ('/app/oradata/HNXDB/onlinelog/redo41.log','/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/redo42.log') size 200M;

Database altered.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 4178 524288000 512 1 NO INACTIVE 253987550 29-OCT-16 253987587 29-OCT-16

2 1 4179 524288000 512 1 NO INACTIVE 253987587 29-OCT-16 253987696 29-OCT-16

3 1 4180 524288000 512 1 NO CURRENT 253987696 29-OCT-16 2.8147E+14

4 1 0 209715200 512 2 YES UNUSED 0 0

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 ('/app/oradata/HNXDB/onlinelog/redo11.log','/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/redo12.log') size 200M;

Database altered.

SQL> alter database add logfile group 2 ('/app/oradata/HNXDB/onlinelog/redo21.log','/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/redo22.log') size 200M;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> alter database drop logfile group 3;

alter database drop logfile group 3

ERROR at line 1:

ORA-01624: log 3 needed for crash recovery of instance hnxdb (thread 1)

ORA-00312: online log 3 thread 1: '/app/oradata/HNXDB/onlinelog/o1_mf_3_c4bwxsgo_.log'

ORA-00312: online log 3 thread 1: '/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/o1_mf_3_c4bwxsj0_.log'

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 4185 209715200 512 2 NO CURRENT 253988519 29-OCT-16 2.8147E+14

2 1 4182 209715200 512 2 NO INACTIVE 253988384 29-OCT-16 253988481 29-OCT-16

3 1 4184 524288000 512 1 NO ACTIVE 253988498 29-OCT-16 253988519 29-OCT-16

4 1 4183 209715200 512 2 NO INACTIVE 253988481 29-OCT-16 253988498 29-OCT-16

--稍等几分钟，让status状态变成INACTIVE，在删除group 3。这里我等了1分钟左右。

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 ('/app/oradata/HNXDB/onlinelog/redo31.log','/u01/app/oracle/fast_recovery_area/HNXDB/onlinelog/redo32.log') size 200M;

Database altered.

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

1 1 4185 209715200 512 2 NO ACTIVE 253988519 29-OCT-16 253988611 29-OCT-16

2 1 4186 209715200 512 2 NO CURRENT 253988611 29-OCT-16 2.8147E+14

3 1 0 209715200 512 2 YES UNUSED 0 0

4 1 4183 209715200 512 2 NO INACTIVE 253988481 29-OCT-16 253988498 29-OCT-16

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

1 ONLINE /app/oradata/HNXDB/onlinelog/redo11.log NO