今天上午刚回到公司，接到开发组同事电话，测试数据库主机被新来的小伙非正常关机了，现在数据库起不来了。
数据库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文件也有坏块。经询问开发组人员，他们尝试了进行恢复，包括重建控制文件，添加隐含参数。
停止数据库，修改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>重建控制文件
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/u01/oradata/orcl/system01.dbf', 14 '/u01/oradata/orcl/sysaux01.dbf', 15 '/u01/oradata/orcl/undotbs03.db', 16 '/u01/oradata/orcl/users01.dbf', 17 '/u01/oradata/orcl/test01.dbf' 18 -- ,'/u01/oradata/orcl/test_ind01.dbf' 19 CHARACTER SET WE8MSWIN1252 20 ; Control file created. SQL>以resetlog打开数据库，报ORA-00600错误，数据库挡掉了。
SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Process ID: 15912 Session ID: 1 Serial number: 3 SQL>alert日志文件中的报错信息如下：
Mon Jul 26 21:47:05 2021 SMON: enabling cache recovery Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc (incident=138313): ORA-00600: internal error code, arguments: [2256], [0], [2147483648], [0], [2147582696], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_138313/orcl_ora_15912_i138313.trc Mon Jul 26 21:47:13 2021 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc (incident=138314): ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_138314/orcl_ora_15912_i138314.trc Mon Jul 26 21:47:13 2021 Trace dumping is performing id=[cdmp_20210726214713] Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc: ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc: ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Error 600 happened during db open, shutting down database USER (ospid: 15912): terminating the instance due to error 600 Instance terminated by USER, pid = 15912 ORA-1092 signalled during: alter database open resetlogs... Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc (incident=138315): ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_138315/orcl_ora_15912_i138315.trc Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc (incident=138316): ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_138316/orcl_ora_15912_i138316.trc Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc: ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc: ORA-27300: OS system dependent operation:semctl failed with status: 22 ORA-27301: OS failure message: Invalid argument ORA-27302: failure occurred at: sskgpwpost1 ORA-27303: additional information: semid = 32805 ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Mon Jul 26 21:47:19 2021 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_15912.trc (incident=139361): ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_139361/orcl_ora_15912_i139361.trc Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_139361/orcl_ora_15912_i139361.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [0], [2147582701], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [0], [2147582700], [0], [2147583575], [33554720], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [0], [2147582699], [0], [2147583575], [33554720], [], [], [], [], [], []设置scn增进，再次拉起数据库。
本案中设置level为3，关于增进scn是设置level多大合适，请参考另一篇文章在Oracle中增进SCN.
SQL> alter session set events '10015 trace name adjust_scn level 3'; Session altered. 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 ; ORA-00283: recovery session canceled due to errors ORA-16433: The database must be opened in read/write mode. SQL>又报了ORA-16433错误，重建控制文件，再来一遍啊。。。
SQL> shutdown abort; ORACLE instance shut down. SQL> SQL> 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> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/u01/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512, 9 GROUP 2 '/u01/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512, 10 GROUP 3 '/u01/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512 11 -- STANDBY LOGFILE 12 DATAFILE 13 '/u01/oradata/orcl/system01.dbf', 14 '/u01/oradata/orcl/sysaux01.dbf', 15 '/u01/oradata/orcl/undotbs03.db', 16 '/u01/oradata/orcl/users01.dbf', 17 '/u01/oradata/orcl/test01.dbf' 18 -- ,'/u01/oradata/orcl/test_ind01.dbf' 19 CHARACTER SET WE8MSWIN1252 20 ; Control file created. SQL> alter session set events '10015 trace name adjust_scn level 3'; Session altered. SQL> recover database using backup controlfile until cancel; ORA-00279: change 2147582121 generated at 07/26/2021 21:47:05 needed for thread 1 ORA-00289: suggestion : /arch/1_1_1078955221.dbf ORA-00280: change 2147582121 for thread 1 is in sequence #1 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/orcl/system01.dbf' ORA-01112: media recovery not started SQL> alter database open resetlogs; Database altered. SQL>到此数据库已经正常打开了。但是日志文件被报错刷屏了。
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j001_20026.trc: ORA-19624: operation failed, retry possible ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01111: name for data file 6 is unknown - rename to correct file ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/db/dbs/MISSING00006'重建控制文件时，，丢了一个数据文件。所以在重建控制文件前，一定要检查，不要丢掉任何一个文件重命名数据文件，
SQL> ALTER DATABASE RENAME FILE 'MISSING00006' 2 TO '/u01/oradata/orcl/test_ind01.dbf'; Database altered. SQL>添加tempfile
修改参数文件，去掉隐含参数，重启数据库
参数改为如下：去掉隐含参数 undo_management="AUTO" undo_tablespace="SYSTEM" SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup 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 Database mounted. Database opened. SQL>此时数据库可以正常open，但是日志文件里面再次有ORA-00600报错。
Trace dumping is performing id=[cdmp_20210726230100] Doing block recovery for file 8 block 641 Resuming block recovery (PMON) for file 8 block 641 Block recovery from logseq 1, block 3207 to scn 3221226677 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0 Mem# 0: /u01/oradata/orcl/redo01.log Block recovery completed at rba 1.3209.16, scn 0.3221226679 Trace dumping is performing id=[cdmp_20210726230101] Doing block recovery for file 8 block 368 Resuming block recovery (PMON) for file 8 block 368 Block recovery from logseq 1, block 3207 to scn 3221226824 Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0 Mem# 0: /u01/oradata/orcl/redo01.log Block recovery completed at rba 1.3265.16, scn 0.3221226825 Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_21333.trc (incident=143234): ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_143234/orcl_m000_21333_i143234.trc Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_21333.trc: ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []说明undo表空间还是有问题的。创建新的undo表空间，删除旧的undo表空间
SQL> create undo tablespace UNDOTBS4 datafile '/u01/oradata/orcl/undotbs04.db' size 50M; Tablespace created. SQL> alter system set undo_tablespace='UNDOTBS4' ; System altered. SQL> drop tablespace UNDOTBS3 including contents and datafiles; Tablespace dropped. SQL>至此数据库恢复正常，至于漏掉的数据文件如何恢复，下回分解。