ORA-00742 ORA-00312 恢复---惜分飞

有客户反馈,断电之后数据库启动报ORA-00742和ORA-00312,无法正常open
 

---

我们远程上去尝试open库结果也报同样错误

[oracle@oldhis oradata]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 10 09:40:03 2024 Copyright (c) 1982, 2013, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover database; Media recovery complete. SQL> alter database open; alter database open * ERROR at line 1: ORA-00742: Log read detects lost write in thread %d sequence %d block %d ORA-00312: online log 3 thread 1: '/oradata/shrdh/redo03.log' SQL> select group#,status from v$log;     GROUP# STATUS ---------- ----------------          1 INACTIVE          3 CURRENT          2 INACTIVE

因为recover已经成功,但是依旧报ORA-742错误,尝试查询scn相关信息

SQL> set pages 10000 set numw 16 SELECT status, checkpoint_change#, checkpoint_time,last_change#, count(*) ROW_NUM FROM v$datafile GROUP BY status, checkpoint_change#, checkpoint_time,last_change# ORDER BY status, checkpoint_change#, checkpoint_time; set numw 16 col CHECKPOINT_TIME for a40 set lines 150 set pages 1000 SELECT status, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#, count(*) ROW_NUM FROM v$datafile_header GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy ORDER BY status, checkpoint_change#, checkpoint_time; SQL> SQL>   2    3    4    5    6    7  STATUS  CHECKPOINT_CHANGE# CHECKPOIN     LAST_CHANGE#          ROW_NUM ------- ------------------ --------- ---------------- ---------------- ONLINE          1279351848 26-MAR-24       1279351848               19 SYSTEM          1279351848 26-MAR-24       1279351848                1 SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6  STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM ------- ---------------------------------------- --- ------------------ ---------------- ONLINE  2024-03-26 00:05:45                      NO          1279351848               20

基于这样的情况,我们判断数据库直接open成功

SQL> recover database using backup controlfile; ORA-00279: change 1279351848 generated at 03/26/2024 00:05:45 needed for thread 1 ORA-00289: suggestion : /oradata/arch/shrdh/shrdh_1_12984_974767526.arc ORA-00280: change 1279351848 for thread 1 is in sequence #12984 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /oradata/shrdh/redo03.log Log applied. Media recovery complete. SQL> alter database open resetlogs; Database altered.

后面比较不幸，数据库报ORA-600 4194错误导致数据库异常

Wed Apr 10 09:43:08 2024 ALTER DATABASE RECOVER  database using backup controlfile  Media Recovery Start  started logmerger process Parallel Media Recovery started with 4 slaves ORA-279 signalled during: ALTER DATABASE RECOVER  database using backup controlfile  ... Wed Apr 10 09:43:24 2024 ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  Media Recovery Log /oradata/shrdh/redo03.log Media Recovery Complete (shrdh) Completed: ALTER DATABASE RECOVER    LOGFILE '/oradata/shrdh/redo03.log'  alter database open resetlogs RESETLOGS after complete recovery through change 1279351849 Clearing online redo logfile 1 /oradata/shrdh/redo01.log Clearing online log 1 of thread 1 sequence number 12982 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /oradata/shrdh/redo02.log Clearing online log 2 of thread 1 sequence number 12983 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /oradata/shrdh/redo03.log Clearing online log 3 of thread 1 sequence number 12984 Clearing online redo logfile 3 complete Resetting resetlogs activation ID 1820377766 (0x6c80c2a6) Online log /oradata/shrdh/redo01.log: Thread 1 Group 1 was previously cleared Online log /oradata/shrdh/redo02.log: Thread 1 Group 2 was previously cleared Online log /oradata/shrdh/redo03.log: Thread 1 Group 3 was previously cleared Wed Apr 10 09:43:34 2024 Setting recovery target incarnation to 2 Wed Apr 10 09:43:34 2024 Assigning activation ID 2011515185 (0x77e54931) Thread 1 opened at log sequence 1   Current log# 1 seq# 1 mem# 0: /oradata/shrdh/redo01.log Successful open of redo thread 1 Wed Apr 10 09:43:34 2024 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Wed Apr 10 09:43:34 2024 SMON: enabling cache recovery [25089] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:1273646224 end:1273646494 diff:270 (2 seconds) Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc  (incident=84296): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84296/shrdh_smon_21704_i84296.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Starting background process QMNC Wed Apr 10 09:43:35 2024 QMNC started with pid=24, OS id=25340 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Block recovery from logseq 1, block 61 to scn 1279351933 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0   Mem# 0: /oradata/shrdh/redo01.log Block recovery stopped at EOT rba 1.99.16 Block recovery completed at rba 1.99.16, scn 0.1279351933 Block recovery from logseq 1, block 61 to scn 1279351919 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0   Mem# 0: /oradata/shrdh/redo01.log Block recovery completed at rba 1.87.16, scn 0.1279351922 Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_smon_21704.trc: ORA-01595: error freeing extent (2) of rollback segment (7)) ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Completed: alter database open resetlogs Wed Apr 10 09:43:37 2024 Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_m000_25343.trc  (incident=84392): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84392/shrdh_m000_25343_i84392.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Apr 10 09:43:37 2024 Starting background process CJQ0 Wed Apr 10 09:43:37 2024 CJQ0 started with pid=29, OS id=25357 Starting background process SMCO Wed Apr 10 09:43:37 2024 SMCO started with pid=30, OS id=25360 Wed Apr 10 09:43:38 2024 Flush retried for xcb 0x115b42d28, pmd 0x1148dea70 Block recovery from logseq 1, block 61 to scn 1279351933 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0   Mem# 0: /oradata/shrdh/redo01.log Block recovery completed at rba 1.99.16, scn 0.1279351934 Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc  (incident=84208): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/shrdh/shrdh/incident/incdir_84208/shrdh_pmon_21679_i84208.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Errors in file /u01/app/oracle/diag/rdbms/shrdh/shrdh/trace/shrdh_pmon_21679.trc: ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] PMON (ospid: 21679): terminating the instance due to error 472 Wed Apr 10 09:43:47 2024 Instance terminated by PMON, pid = 21679

报错比较明显,对undo进行处理即可.