Oracle?日志文件比控制文件新的处理（控制文件异常）
1.现象
今天有人说数据库不能访问，检查后发现数据库宕机。然后启动数据库失败：
SQL>?startup
ORACLE?例程已经启动。
Total?System?Global?Area?1.7103E+10?bytes
Fixed?Size?2298368?bytes
Variable?Size?4160753152?bytes
Database?Buffers?1.2918E+10?bytes
Redo?Buffers?21655552?bytes
数据库装载完毕。
ORA‐00338:?日志?2?(用于线程?1)?比控制文件更新
ORA‐00312:?联机日志?2?线程?1:?
‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO02.LOG‘
2.?原因检查
Alert.log 中昨天晚上报出如下错误
Mon?Apr?27?22:12:27?2020
minact‐scn:?useg?scan?erroring?out?with?error?e:204
Errors?in?file?
E:\APP\ADMINISTRATOR\diag\rdbms\hngsjgx\hngsjgx\trace\hngsjgx_ckpt_8036.trc??(incident=148100):
ORA‐07445:?出现异常错误:?核心转储?[PC:0x7FF8BF489345]?[IN_PAGE_ERROR]?[]?[PC:0x7FF8BF489345]?[]?[]
ORA‐00202:?控制文件:?‘‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\CONTROL02.CTL‘‘
ORA‐27070:?异步读取/写入失败
OSD‐04016:?异步?I/O?请求排队时出错。
O/S‐Error:?(OS?1117)?由于?I/O?设备错误，无法运行此项请求。
ORA‐00206:?写入控制文件时出错?(块?3,?#?块?1)
ORA‐00202:?控制文件:?‘‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\CONTROL01.CTL‘‘
ORA‐27070:?异步读取/写入失败
OSD‐04016:?异步?I/O?请求排队时出错。
O/S‐Error:?(OS?1117)?由于?I/O?设备错误，无法运行此项请求。
Incident?details?in:?
E:\APP\ADMINISTRATOR\diag\rdbms\hngsjgx\hngsjgx\incident\incdir_148100\hngsjgx_ckpt_8036_i148100.trc
3.?问题处理
根据如上错误日志可以看出，是磁盘I/O故障导致控制文件写入失败，那么就需要重建控制文件来处理。
获取控制文件重建语法：
SQL>?startup?mount;
SQL>?alter?database?backup?controlfile?to?trace;
SQL>?select?*?from?v$diag_info;
E:\APP\ADMINISTRATOR\diag\rdbms\hngsjgx\hngsjgx\trace\hngsjgx_ora_8288.trc
SQL>?shutdown?immediate;
--备份控制文件
trace?文件E:\APP\ADMINISTRATOR\diag\rdbms\hngsjgx\hngsjgx\trace\hngsjgx_ora_8288.trc中，包含有两种重建控制文件方式：
- Set?#1.?NORESETLOGS?case
‐‐
‐‐?以下命令将创建新的控制文件并使用它打开数据库。
‐‐?控制文件中的RMAN?相关信息将丢失?
‐‐?当前所有的online?logs?都可用的情况下使用该方法?
‐‐?使用该方式后，DG的保护模式将改为最大性能模式
‐‐?????Set?#1.?NORESETLOGS?case
‐‐
‐‐?The?following?commands?will?create?a?new?control?file?and?use?it
‐‐?to?open?the?database.
‐‐?Data?used?by?Recovery?Manager?will?be?lost.
‐‐?Additional?logs?may?be?required?for?media?recovery?of?offline
‐‐?Use?this?only?if?the?current?versions?of?all?online?logs?are
‐‐?available.
‐‐?After?mounting?the?created?controlfile,?the?following?SQL
‐‐?statement?will?place?the?database?in?the?appropriate
‐‐?protection?mode:
‐‐??ALTER?DATABASE?SET?STANDBY?DATABASE?TO?MAXIMIZE?PERFORMANCE
STARTUP?NOMOUNT
CREATE?CONTROLFILE?REUSE?DATABASE?"HNGSJGX"?NORESETLOGS??NOARCHIVELOG
????MAXLOGFILES?16
????MAXLOGMEMBERS?3
????MAXDATAFILES?100
????MAXINSTANCES?8
????MAXLOGHISTORY?5840
LOGFILE
??GROUP?1?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO01.LOG‘??SIZE?500M?
BLOCKSIZE?512,
??GROUP?2?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO02.LOG‘??SIZE?500M?
BLOCKSIZE?512,
??GROUP?3?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO03.LOG‘??SIZE?500M?
BLOCKSIZE?512,
??GROUP?4?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO04.LOG‘??SIZE?500M?
BLOCKSIZE?512
‐‐?STANDBY?LOGFILE
DATAFILE
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\SYSTEM01.DBF‘,
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\SYSAUX01.DBF‘,
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\UNDOTBS01.DBF‘,
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\USERS01.DBF‘,
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\THNGSJGX01.DBF‘,
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\TBS_HNSDSJJGX_01.DBF‘,
??‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\THNGSJGX02.DBF‘
CHARACTER?SET?ZHS16GBK
;
‐‐?Commands?to?re‐create?incarnation?table
‐‐?Below?log?names?MUST?be?changed?to?existing?filenames?on
‐‐?disk.?Any?one?log?file?from?each?branch?can?be?used?to
‐‐?re‐create?incarnation?records.
‐‐?ALTER?DATABASE?REGISTER?LOGFILE?
‘E:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\RDBMS\ARC0000000001_09664445
41.0001‘;
‐‐?Recovery?is?required?if?any?of?the?datafiles?are?restored?backups,
‐‐?or?if?the?last?shutdown?was?not?normal?or?immediate.
RECOVER?DATABASE
‐‐?Database?can?now?be?opened?normally.
ALTER?DATABASE?OPEN;
‐‐?Commands?to?add?tempfiles?to?temporary?tablespaces.
‐‐?Online?tempfiles?have?complete?space?information.
‐‐?Other?tempfiles?may?require?adjustment.
ALTER?TABLESPACE?TEMP?ADD?TEMPFILE?
‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\TEMP01.DBF‘?REUSE;
‐‐?End?of?tempfile?additions.
- Set?#2.?RESETLOGS?case
‐‐
‐‐?以下命令将创建新的控制文件并使用它打开数据库。
‐‐?控制文件中的RMAN?相关信息将丢失
‐‐?online?logs?中的内容将丢失，所有的备份都会不可用
‐‐?仅在online?logs?不可用的情况下使用此方式
‐‐?使用此方式后，DG的保护模式将改为最大性能模式
-- （略）
4.?重建控制文件过程
因为日志文件正常，所以我们使用?NORESETLOGS?方式恢复：
SQL>?STARTUP?NOMOUNT
ORACLE?例程已经启动。
Total?System?Global?Area?1.7103E+10?bytes
Fixed?Size??????????????????2298368?bytes
Variable?Size????????????4160753152?bytes
Database?Buffers?????????1.2918E+10?bytes
Redo?Buffers???????????????21655552?bytes
SQL>?CREATE?CONTROLFILE?REUSE?DATABASE?"HNGSJGX"?NORESETLOGS??NOARCHIVELOG
??2??????MAXLOGFILES?16
??3??????MAXLOGMEMBERS?3
??4??????MAXDATAFILES?100
??5??????MAXINSTANCES?8
??6??????MAXLOGHISTORY?5840
??7??LOGFILE
??8????GROUP?1?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO01.LOG‘??SIZE?
500M?BLOCKSIZE?512,
??9????GROUP?2?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO02.LOG‘??SIZE?
500M?BLOCKSIZE?512,
?10????GROUP?3?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO03.LOG‘??SIZE?
500M?BLOCKSIZE?512,
?11????GROUP?4?‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\REDO04.LOG‘??SIZE?
500M?BLOCKSIZE?512
?12??‐‐?STANDBY?LOGFILE
?13??DATAFILE
?14????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\SYSTEM01.DBF‘,
?15????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\SYSAUX01.DBF‘,
?16????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\UNDOTBS01.DBF‘,
?17????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\USERS01.DBF‘,
?18????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\THNGSJGX01.DBF‘,
?19????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\TBS_HNSDSJJGX_01.DBF‘,
?20????‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\THNGSJGX02.DBF‘
?21??CHARACTER?SET?ZHS16GBK
?22??;
控制文件已创建。
SQL>?RECOVER?DATABASE
完成介质恢复。
SQL>?ALTER?DATABASE?OPEN;
数据库已更改。
SQL>?ALTER?TABLESPACE?TEMP?ADD?TEMPFILE?
‘E:\APP\ADMINISTRATOR\ORADATA\HNGSJGX\TEMP01.DBF‘?REUSE;
表空间已更改。
如上已经恢复正常，下面重启数据库加以确认：
SQL>?shutdown?immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE?例程已经关闭。
SQL>?startup
ORACLE?例程已经启动。
Total?System?Global?Area?1.7103E+10?bytes
Fixed?Size??????????????????2298368?bytes
Variable?Size????????????4160753152?bytes
Database?Buffers?????????1.2918E+10?bytes
Redo?Buffers???????????????21655552?bytes
数据库装载完毕。
数据库已经打开。
SQL>
确认正常，恢复完毕。