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

最新推荐文章于 2024-09-21 23:58:30 发布

原创最新推荐文章于 2024-09-21 23:58:30 发布 · 1.2k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

客户反馈断电后Oracle数据库启动报ORA - 00742和ORA - 00312无法正常open，远程尝试open库也报错。recover成功仍报ORA - 742，查询scn信息后判断可直接open成功，但又报ORA - 600 4194错误致数据库异常，需对undo进行处理。

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

有客户反馈,断电之后数据库启动报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

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进行处理即可.