标签归档：ORA-00742

客户一套运行在win平台，非归档的19c数据库,由于异常断电导致数据库启动报ORA-01113,进行recover操作之后报ORA-00742

2025-01-18T00:17:52.205669+08:00
alter database open
2025-01-18T00:17:53.417839+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
2025-01-18T00:17:53.436858+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_4428.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSTEM01.DBF'
2025-01-18T00:17:53.442863+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_ora_4428.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSTEM01.DBF'
ORA-1113 signalled during: alter database open...

recover database 数据库的alert日志报错Media Recovery failed with error 742和
ORA-01110 ORA-01208等错误

2025-01-18T00:20:10.196227+08:00
ALTER DATABASE RECOVER  database  
2025-01-18T00:20:10.221244+08:00
Media Recovery Start
 Started logmerger process
2025-01-18T00:20:10.459413+08:00
WARNING! Recovering data file 1 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 7 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 60 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 64 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 65 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 66 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
WARNING! Recovering data file 67 from a fuzzy backup. It might be an online
backup taken without entering the begin backup command.
2025-01-18T00:20:10.599512+08:00
Parallel Media Recovery started with 12 slaves
2025-01-18T00:20:10.664559+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 2097 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
2025-01-18T00:20:12.644962+08:00
Media Recovery failed with error 742
2025-01-18T00:20:12.759043+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_4036.trc:
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSTEM01.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
2025-01-18T00:20:13.135309+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_4036.trc:
ORA-01110: 数据文件 3: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\SYSAUX01.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
2025-01-18T00:20:13.455536+08:00
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\his\his\trace\his_mz00_4036.trc:
ORA-01110: 数据文件 4: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\UNDOTBS01.DBF'
ORA-01208: 数据文件是旧的版本 - 不能访问当前版本
2025-01-18T00:20:14.408212+08:00
ORA-283 signalled during: ALTER DATABASE RECOVER  database  ...

尝试recover datafile 1

SQL> RECOVER DATAFILE 1;
ORA-00283: 恢复会话因错误而取消
ORA-00742: 日志读取在线程 1 序列 2097 块 296728 中检测到写入丢失情况
ORA-00312: 联机日志 3 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG'

对于这种情况,比较明显是redo文件有写丢失,导致数据库无法正常的应用redo日志进行恢复,从而无法正常open.这种情况,只能只能选择屏蔽一致性,尝试强制打开数据库

C:\Users\Administrator\Desktop\check_db>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 18 00:59:28 2025
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> recover database using backup controlfile until cancel;
ORA-00279: ?? 10103231167 (? 01/17/2025 09:20:12 ??) ???? 1 ????
ORA-00289: ??:
D:\APP\ADMINISTRATOR\PRODUCT\19.0.0\DBHOME_1\RDBMS\ARC0000002097_1079211060.0001
ORA-00280: ?? 10103231167 (???? 1) ??? #2097 ?


指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
ORA-00283: ??????????????????????????????
ORA-00742: ????????????????????? 1 ?????? 2097 ??? 296960
??????????????????????????????
ORA-00334: ????????????: 'D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG'


ORA-01112: ???????


SQL> alter database open resetlogs;

Database altered.

alert日志对应的信息

2025-01-18T01:00:15.756033+08:00
alter database open resetlogs
2025-01-18T01:00:15.903141+08:00
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 10103247614 time 
.... (PID:4824): Clearing online redo logfile 1 D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG
.... (PID:4824): Clearing online redo logfile 2 D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO02.LOG
.... (PID:4824): Clearing online redo logfile 3 D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG
Clearing online log 1 of thread 1 sequence number 2098
Clearing online log 2 of thread 1 sequence number 2096
Clearing online log 3 of thread 1 sequence number 2097
2025-01-18T01:00:19.381697+08:00
.... (PID:4824): Clearing online redo logfile 1 complete
.... (PID:4824): Clearing online redo logfile 2 complete
.... (PID:4824): Clearing online redo logfile 3 complete
Resetting resetlogs activation ID 3599991024 (0xd69380f0)
Online log D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG: Thread 1 Group 1 was previously cleared
Online log D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO02.LOG: Thread 1 Group 2 was previously cleared
Online log D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO03.LOG: Thread 1 Group 3 was previously cleared
2025-01-18T01:00:19.550821+08:00
Setting recovery target incarnation to 2
2025-01-18T01:00:20.418458+08:00
Ping without log force is disabled:
  instance mounted in exclusive mode.
Initializing SCN for created control file
Database SCN compatibility initialized to 3
2025-01-18T01:00:25.466167+08:00
Endian type of dictionary set to little
2025-01-18T01:00:25.476174+08:00
Assigning activation ID 3711463735 (0xdd387137)
2025-01-18T01:00:25.491185+08:00
TT00 (PID:3332): Gap Manager starting
2025-01-18T01:00:25.507197+08:00
Redo log for group 1, sequence 1 is not located on DAX storage
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\HIS\REDO01.LOG
Successful open of redo thread 1
2025-01-18T01:00:26.162679+08:00
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
stopping change tracking
2025-01-18T01:00:26.183694+08:00
TT03 (PID:1896): Sleep 5 seconds and then try to clear SRLs in 2 time(s)
2025-01-18T01:00:27.465636+08:00
Undo initialization recovery: err:0 start: 3158125 end: 3158390 diff: 265 ms (0.3 seconds)
Undo initialization online undo segments: err:0 start: 3158390 end: 3158390 diff: 0 ms (0.0 seconds)
Undo initialization finished serial:0 start:3158125 end:3158406 diff:281 ms (0.3 seconds)
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying minimum file header compatibility for tablespace encryption..
Verifying file header compatibility for tablespace encryption completed for pdb 0
Database Characterset is AL32UTF8
2025-01-18T01:00:28.790609+08:00
No Resource Manager plan active
2025-01-18T01:00:30.086561+08:00
replication_dependency_tracking turned off (no async multimaster replication found)
2025-01-18T01:00:31.185369+08:00
TT03 (PID:1896): Sleep 10 seconds and then try to clear SRLs in 3 time(s)
2025-01-18T01:00:31.536626+08:00
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Starting background process AQPC
2025-01-18T01:00:31.638701+08:00
AQPC started with pid=38, OS id=4340 
2025-01-18T01:00:32.717495+08:00
Starting background process CJQ0
2025-01-18T01:00:32.726501+08:00
CJQ0 started with pid=40, OS id=1236 
Completed: alter database open resetlogs

数据库没有明显报错,直接resetlogs成功,直接逻辑导出数据,导入新库,完成本次恢复工作