ORA-16433: The database must be opened in read/write mode.

今天上午刚回到公司，接到开发组同事电话，测试数据库主机被新来的小伙非正常关机了，现在数据库起不来了。

数据库open时，提示需要介质恢复，恢复时就会报ORA-16433错误。

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/oradata/orcl/system01.dbf'


SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.


SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.

由于时测试数据库，没有任何备份，于是决定重建控制文件。

重建控制文件的语句如下：

CREATE CONTROLFILE REUSE DATABASE ORCL RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
DATAFILE
  '/u01/oradata/orcl/system01.dbf',
  '/u01/oradata/orcl/sysaux01.dbf',
  '/u01/oradata/orcl/undotbs03.db',
  '/u01/oradata/orcl/users01.dbf',
  '/u01/oradata/orcl/test01.dbf'
CHARACTER SET WE8MSWIN1252
;

进行recover操作。将所有的redo日志文件都进行了应用，应用第一组redo日志时有报错，根据报错信息，判断redo文件也有坏块。经询问开发组人员，他们尝试了进行恢复，包括重建控制文件，添加隐含参数。

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 2147582117 generated at 07/26/2021 05:47:47 needed for thread
1
ORA-00289: suggestion : /arch/1_1_1078897661.dbf
ORA-00280: change 2147582117 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oradata/orcl/redo01.log
ORA-00283: recovery session canceled due to errors
ORA-00399: corrupt change description in redo log
ORA-00353: log corruption near block 3 change 2147582696 time 07/26/2021
05:47:48
ORA-00334: archived log: '/u01/oradata/orcl/redo01.log'


ORA-01112: media recovery not started


SQL>

停止数据库，修改pfile文件，修改undo_management、undo_tablespace，添加隐含参数_allow_resetlogs_corruption

*.undo_management='MANUAL'
*.undo_tablespace='SYSTEM'
*._allow_resetlogs_corruption=TRUE

使用pfile启动数据库

SQL> shutdown immediate;
ORA-01109: database not open


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

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             192942072 bytes
Database Buffers          427819008 bytes
Redo Buffers                3350528 bytes
SQL>

重建控制