ora-742 无法启动

一台长时间没用的测试机，今天启动的时候报错，ora-742 无法启动，相关报错如下：

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 18 23:24:24 2024
Version 19.3.0.0.0

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

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 352321536 bytes
Database Buffers 838860800 bytes
Redo Buffers 7880704 bytes
Database mounted.
ORA-00742: Log read detects lost write in thread 1 sequence 29 block 930953
ORA-00312: online log 2 thread 1: '/u01/oradata/SINGLE/redo02.log'
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

检查发下，操作系统空间满了，清理了部分文件，然后考虑解决此报错问题

尝试recover database 问题依旧
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00742: Log read detects lost write in thread 1 sequence 29 block 930953
ORA-00312: online log 2 thread 1: '/u01/oradata/SINGLE/redo02.log'



尝试不完全恢复：
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 9444271 generated at 04/28/2024 18:49:55 needed for thread 1
ORA-00289: suggestion : /u01/oradata/arch/1_29_1115905472.dbf
ORA-00280: change 9444271 for thread 1 is in sequence #29


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/SINGLE/system01.dbf'

尝试启动
alter database open resetlogs

第 1 行出现错误:
ORA-01152: 文件 1 没有从过旧的备份中还原 ORA-01110:              
数据文件 1: '/u01/oradata/SINGLE/system01.dbf'


修改参数文件加上如下参数，再次启动。
_allow_resetlogs_corruption= TRUE


[oracle@single19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 18 23:59:00 2024
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> shutdown immediate

ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/home/oracle/pfile';
ORACLE instance started.

Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 9444271 generated at 04/28/2024 18:49:55 needed for thread 1
ORA-00289: suggestion : /u01/oradata/arch/1_29_1115905472.dbf
ORA-00280: change 9444271 for thread 1 is in sequence #29


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/SINGLE/system01.dbf'


ORA-01112: media recovery not started


SQL>
SQL> alter database open resetlogs;
Database altered.