Oracle Error：ORA-00314: log 1 of thread 1, expected sequence# doesn’t match & ORA-03113
今天，客户的一个测试环境，在启动数据库的时候，从mount到open的时候，遇到了如题所示的错误。
后来，将该错误解决了；因此，下面从一个正常的库的问题重现到问题解法，梳理一下该问题。
打开日志跟踪：
先打开日志跟踪：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
[root@oracle-automatic ~]# su - oracle Last login: Thu Dec 13 14:36:21 CST 2018 on pts/2 [oracle@oracle-automatic ~]$ [oracle@oracle-automatic ~]$ cd $ORACLE_BASE/diag/rdbms [oracle@oracle-automatic rdbms]$ ls oggme orcl [oracle@oracle-automatic rdbms]$ cd oggme/ [oracle@oracle-automatic oggme]$ ls i_1.mif oggme [oracle@oracle-automatic oggme]$ cd oggme/ [oracle@oracle-automatic oggme]$ ls alert cdump hm incident incpkg ir lck log metadata metadata_dgif metadata_pv stage sweep trace [oracle@oracle-automatic oggme]$ [oracle@oracle-automatic oggme]$ tail -f trace/alert_oggme.log =========================================================== No patches have been applied =========================================================== 2018-12-13T14:07:44.641947+08:00 db_recovery_file_dest_size of 8016 MB is 7.62% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. 2018-12-13T14:07:45.398233+08:00 Resize operation completed for file# 4, old size 61440K, new size 66560K ====================== |
再看看我们当前的数据库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
[oracle@oracle-automatic ~]$ ps -ef | grep pmon oracle 4583 1 0 14:07 ? 00:00:01 ora_pmon_oggme oracle 6646 6380 0 14:36 pts/2 00:00:00 grep --color=auto pmon [oracle@oracle-automatic ~]$ [oracle@oracle-automatic ~]$ export ORACLE_SID=oggme [oracle@oracle-automatic ~]$ [oracle@oracle-automatic ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 13 14:36:46 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Current log sequence 1 SQL> SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 1 NO CURRENT 2 0 YES UNUSED 3 0 YES UNUSED SQL> SQL> col member for a72 SQL> set linesize 400 SQL> SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ------------------------------------------------------------------------ --- ---------- 3 ONLINE /oradata/OGGME/onlinelog/o1_mf_3_g13xmqn7_.log NO 0 3 ONLINE /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_3_g13xmsx8_.log YES 0 2 ONLINE /oradata/OGGME/onlinelog/o1_mf_2_g13xmqlr_.log NO 0 2 ONLINE /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_2_g13xmsw7_.log YES 0 1 ONLINE /oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log NO 0 1 ONLINE /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log YES 0 6 rows selected. SQL> |
可以看到：
1. 当前数据库是【非归档】
2. 组1，是当前组，其他的两个暂时没有使用
做切换，将其他两个组用起来：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 1 NO CURRENT 2 0 YES UNUSED 3 0 YES UNUSED SQL> SQL> alter system switch logfile; System altered. SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 1 NO ACTIVE 2 2 NO CURRENT 3 0 YES UNUSED SQL> alter system switch logfile; System altered. SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 1 NO INACTIVE 2 2 NO ACTIVE 3 3 NO CURRENT SQL> |
因为是非归档，所以ARC都是NO。
日志切换的日志：
|
1 2 3 4 5 6 7 8 |
2018-12-13T14:47:32.456480+08:00 Thread 1 advanced to log sequence 2 (LGWR switch) Current log# 2 seq# 2 mem# 0: /oradata/OGGME/onlinelog/o1_mf_2_g13xmqlr_.log Current log# 2 seq# 2 mem# 1: /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_2_g13xmsw7_.log 2018-12-13T14:47:53.336283+08:00 Thread 1 advanced to log sequence 3 (LGWR switch) Current log# 3 seq# 3 mem# 0: /oradata/OGGME/onlinelog/o1_mf_3_g13xmqn7_.log Current log# 3 seq# 3 mem# 1: /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_3_g13xmsx8_.log |
下面关库，并开始破坏：
注意，破坏状态为INACTIVE的。
关库：
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@oracle-automatic ~]$ [oracle@oracle-automatic ~]$ ps -ef | grep ora_ oracle 22879 6380 0 14:50 pts/2 00:00:00 grep --color=auto ora_ [oracle@oracle-automatic ~]$ |
破坏：
上面INACTIVE的是组1
组1的成员是：
/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log
就它了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
[oracle@oracle-automatic ~]$ cd /oradata/OGGME/onlinelog/ [oracle@oracle-automatic onlinelog]$ ls -ltr total 614412 -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:07 o1_mf_2_g13xmqlr_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:07 o1_mf_3_g13xmqn7_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:44 o1_mf_1_g13xmqjv_.log [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ mkdir /oradata/backup [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ cp /oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log /oradata/backup/ [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ pwd /oradata/OGGME/onlinelog [oracle@oracle-automatic onlinelog]$ ls -ltr total 614412 -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_1_g13xmqjv_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_2_g13xmqlr_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:49 o1_mf_3_g13xmqn7_.log [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ echo "broken" >> /oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ ls -ltr total 614412 -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:47 o1_mf_2_g13xmqlr_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:49 o1_mf_3_g13xmqn7_.log -rw-r----- 1 oracle oinstall 209715719 Dec 13 14:52 o1_mf_1_g13xmqjv_.log [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ echo "broken" >> /oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log [oracle@oracle-automatic onlinelog]$ |
然后，准备起库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> startup ORACLE instance started. Total System Global Area 5049942016 bytes Fixed Size 8631096 bytes Variable Size 1191185608 bytes Database Buffers 3841982464 bytes Redo Buffers 8142848 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 31396 Session ID: 497 Serial number: 2356 SQL> |
日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Completed: ALTER DATABASE MOUNT 2018-12-13T14:57:03.690517+08:00 ALTER DATABASE OPEN Ping without log force is disabled: instance mounted in exclusive mode. Buffer Cache Full DB Caching mode changing from FULL CACHING DISABLED to FULL CACHING ENABLED Endian type of dictionary set to little 2018-12-13T14:57:03.731962+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_31262.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log' ORA-27046: file size is not a multiple of logical block size Additional information: 1 2018-12-13T14:57:03.732294+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_31262.trc: ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log' ORA-27046: file size is not a multiple of logical block size Additional information: 1 2018-12-13T14:57:03.733555+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_ora_31396.trc: ORA-00313: open failed for members of log group 1 of thread ORA-00312: online log 1 thread 1: '/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log' ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log' 2018-12-13T14:57:03.758542+08:00 TT00: Gap Manager starting (PID:31400) 2018-12-13T14:57:03.785064+08:00 USER (ospid: 31396): terminating the instance due to error 313 2018-12-13T14:57:04.178348+08:00 System state dump requested by (instance=1, osid=31396), summary=[abnormal instance termination]. System State dumped to trace file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_diag_31238_20181213145704.trc 2018-12-13T14:57:05.513999+08:00 Dumping diagnostic data in directory=[cdmp_20181213145704], requested by (instance=1, osid=31396), summary=[abnormal instance termination]. 2018-12-13T14:57:07.004875+08:00 Instance terminated by USER, pid = 31396 |
这样破坏后，我们得到的是ORA-03113的错误，和我们预期的ORA-00314不符。
修复：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
SQL> startup mount; ORACLE instance started. Total System Global Area 5049942016 bytes Fixed Size 8631096 bytes Variable Size 1191185608 bytes Database Buffers 3841982464 bytes Redo Buffers 8142848 bytes Database mounted. SQL> SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 1 NO INACTIVE 3 3 NO CURRENT 2 2 NO INACTIVE SQL> SQL> alter database clear logfile group 1; Database altered. SQL> SQL> alter database clear logfile group 2; Database altered. SQL> SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 0 NO UNUSED 3 3 NO CURRENT 2 0 NO UNUSED SQL> SQL> alter database open; Database altered. SQL> SQL> select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- OGGME PRIMARY SQL> SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ oggme OPEN SQL> |
清理redolog的时候的日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
2018-12-13T15:09:16.296825+08:00 alter database clear logfile group 1 2018-12-13T15:09:16.344276+08:00 Clearing online log 1 of thread 1 sequence number 1 2018-12-13T15:09:16.346274+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_ora_12595.trc: ORA-00313: open failed for members of log group 1 of thread 1 2018-12-13T15:09:16.828893+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_m000_13589.trc: ORA-00312: online log 1 thread 1: '/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log' 2018-12-13T15:09:17.000217+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_m000_13589.trc: ORA-00312: online log 1 thread 1: '/oradata/fast_recovery_area/oggme/OGGME/onlinelog/o1_mf_1_g13xmst4_.log' ORA-00316: log 1 of thread 1, type 0 in header is not log file ORA-00312: online log 1 thread 1: '/oradata/OGGME/onlinelog/o1_mf_1_g13xmqjv_.log' Checker run found 2 new persistent data failures 2018-12-13T15:09:20.367827+08:00 Completed: alter database clear logfile group 1 2018-12-13T15:09:36.722299+08:00 alter database clear logfile group 2 2018-12-13T15:09:36.723037+08:00 Clearing online log 2 of thread 1 sequence number 2 2018-12-13T15:09:45.547008+08:00 Completed: alter database clear logfile group 2 |
这就修好了。
然后再来看模拟前面的ORA-00314的问题：
继续切：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 7 NO INACTIVE 2 8 NO CURRENT 3 6 NO INACTIVE SQL> SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> select group#,sequence#,archived,status from v$log; GROUP# SEQUENCE# ARC STATUS ---------- ---------- --- ---------------- 1 10 NO CURRENT 2 8 NO ACTIVE 3 9 NO ACTIVE SQL> |
切到组1为CURRENT
然后停掉：
|
1 2 3 4 5 |
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> |
用以前的一个版本破坏掉：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@oracle-automatic onlinelog]$ pwd /oradata/OGGME/onlinelog [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ ls -ltr total 614412 -rw-r----- 1 oracle oinstall 209715712 Dec 13 15:17 o1_mf_3_g13xmqn7_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 15:18 o1_mf_1_g13xmqjv_.log -rw-r----- 1 oracle oinstall 209715712 Dec 13 15:18 o1_mf_2_g13xmqlr_.log [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ ls -ltr /oradata/backup/ total 204804 -rw-r----- 1 oracle oinstall 209715712 Dec 13 14:51 o1_mf_1_g13xmqjv_.log [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ cp /oradata/backup/o1_mf_1_g13xmqjv_.log . [oracle@oracle-automatic onlinelog]$ [oracle@oracle-automatic onlinelog]$ cp /oradata/backup/o1_mf_1_g13xmqjv_.log /oradata/fast_recovery_area/oggme/OGGME/onlinelog/ [oracle@oracle-automatic onlinelog]$ |
最后，还是能起来，数据库还是可以OPEN。
不过ALERT日志中会有痕迹。
日志：
|
1 2 3 |
2018-12-13T15:27:22.375508+08:00 Errors in file /u01/app/oracle/diag/rdbms/oggme/oggme/trace/oggme_lgwr_3550.trc: ORA-00313: open failed for members of log group 1 of thread 1 |
ORA-00314，暂时重现不出来。
但是解决方式，和上面是差不多的。
——————————
Done。
