联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle断电故障处理
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
异常断电导致数据库异常恢复文件报ORA-00283 ORA-00742 ORA-00312
D:\check_db>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on 星期二 5月 31 00:38:42 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> recover datafile 1; ORA-00283: 恢复会话因错误而取消 ORA-00742: 日志读取在线程 %d 序列 %d 块 %d 中检测到写入丢失情况 ORA-00312: 联机日志 3 线程 1: 'D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG'
这个错误比较明显是由于异常断电引起的写丢失导致.而且这种故障在没有备份的情况下，没有什么好处理方法,只能屏蔽一致性强制拉库,尝试强制拉库报错如下
SQL> startup mount pfile='d:/pfile.txt'
ORACLE 例程已经启动。
Total System Global Area 2.0310E+10 bytes
Fixed Size 2290000 bytes
Variable Size 3690991280 bytes
Database Buffers 1.6576E+10 bytes
Redo Buffers 40837120 bytes
数据库装载完毕。
SQL> recover database until cancel;
ORA-00279: 更改 18755939194213 (在 生成) 对于线程 1 是必需的
指定日志: {<RET>=suggested | filename | AUTO | CANCEL}
D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_3_HJ32KJD5_.LOG
ORA-00600: internal error code, arguments: [3020], [2], [78824], [8467432], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 78824, file
offset is 645726208 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: 'D:\ORADATA\ORCL\SYSAUX01.DBF'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 80834
ORA-01112: 未启动介质恢复
SQL> alter database open resetlogs;
alter database open resetlogs
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [krsi_al_hdr_update.15], [4294967295], [], [],[], [], [], [], [], [], [], []
ORA-600 krsi_al_hdr_update.15错误,主要是由于redo异常导致无法resetlogs成功,具体参考：Alter Database Open Resetlogs returns error ORA-00600: [krsi_al_hdr_update.15], (Doc ID 2026541.1)描述,处理这个问题之后,再次resetlogs库，报ORA-600 2662错误
SQL> alter database open resetlogs; alter database open resetlogs * 第 1 行出现错误: ORA-00603: ORACLE server session terminated by fatal error ORA-00600: internal error code, arguments: [2662], [4366], [4112122046], [4366], [4112228996], [12583040], [], [], [], [], [], [] ORA-00600: internal error code, arguments: [2662], [4366], [4112122045], [4366], [4112228996], [12583040], [], [], [], [], [], [] ORA-01092: ORACLE instance terminated. Disconnection forced ORA-00600: internal error code, arguments: [2662], [4366], [4112122040], [4366], [4112228996], [12583040], [], [], [], [], [], [] 进程 ID: 4644 会话 ID: 1701 序列号: 3
这个问题比较简单,通过修改scn即可绕过去,之后数据库open报ORA-600 4194等错误
SQL> alter database open ; alter database open * 第 1 行出现错误: ORA-00600: 内部错误代码, 参数: [4194], [
SMON: enabling tx recovery Database Characterset is ZHS16GBK Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc (incident=322982): ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322982\orcl_smon_5112_i322982.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE replication_dependency_tracking turned off (no async multimaster replication found) LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_3340.trc (incident=323030): ORA-00600: 内部错误代码, 参数: [4194], [ Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_323030\orcl_ora_3340_i323030.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Tue May 31 09:05:04 2022 Sweep [inc][322982]: completed ORACLE Instance orcl (pid = 13) - Error 600 encountered while recovering transaction (10, 33). Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc: ORA-00600: internal error code, arguments: [4137], [10.33.3070116], [0], [0], [], [], [], [], [], [], [], [] Checker run found 1 new persistent data failures Tue May 31 09:05:05 2022 Sweep [inc][323030]: completed Sweep [inc2][322982]: completed Tue May 31 09:05:14 2022 Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc (incident=322983): ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [0], [], [], [], [], [], [], [], [], [] Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\incident\incdir_322983\orcl_smon_5112_i322983.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Tue May 31 09:05:14 2022 ORA-600 signalled during: alter database open... Block recovery stopped at EOT rba 2.61.16 Block recovery completed at rba 2.61.16, scn 4366.4112429058 Block recovery from logseq 2, block 60 to scn 18755939643393 Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0 Mem# 0: D:\APP\ADMINISTRATOR\FAST_RECOVERY_AREA\ORCL\ONLINELOG\O1_MF_2_K9BSVC11_.LOG Block recovery completed at rba 2.61.16, scn 4366.4112429058 Dumping diagnostic data in directory=[cdmp_2022053],requested by(instance=1,osid=5112(SMON)),summary=[incident=322983]. Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_smon_5112.trc: ORA-01595: error freeing extent (3) of rollback segment (1)) ORA-00600: internal error code, arguments: [4193], [10.33.3070116], [3], [], [], [], [], [], [], [], [], []
对异常undo进行处理,数据库正常open成功
SQL> shutdown immediate; ORA-00600: 内部错误代码, 参数: [4193], [ SQL> shutdown abort; ORACLE 例程已经关闭。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 2.0310E+10 bytes Fixed Size 2290000 bytes Variable Size 3690991280 bytes Database Buffers 1.6576E+10 bytes Redo Buffers 40837120 bytes 数据库装载完毕。 SQL> alter database open; 数据库已更改。
hcheck检测有一些字典不一致,建议客户逻辑导出,然后导入到新库中
HCheck Version 07MAY18 on 31-5月 -2022 09:12:22
----------------------------------------------
Catalog Version 11.2.0.4.0 (1102000400)
db_name: ORCL
Catalog Fixed
Procedure Name Version Vs Release Timestamp Resul
t
------------------------------ ... ---------- -- ---------- -------------- -----
-
.- LobNotInObj ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- MissingOIDOnObjCol ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- SourceNotInObj ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- OversizedFiles ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- PoorDefaultStorage ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- PoorStorage ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- TabPartCountMismatch ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- OrphanedTabComPart ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- MissingSum$ ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- MissingDir$ ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- DuplicateDataobj ... 1102000400 <= *All Rel* 05/31 09:12:22 PASS
.- ObjSynMissing ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjSeqMissing ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedUndo ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndex ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexPartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndexSubPartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedTable ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedTablePartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedTableSubPartition ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- MissingPartCol ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedSeg$ ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- OrphanedIndPartObj# ... 1102000400 <= *All Rel* 05/31 09:12:23 FAIL
HCKE-0024: Orphaned Index Partition Obj# (no OBJ$) (Doc ID 1360935.1)
ORPHAN INDPART$: OBJ#=149167 BO#=6378 - no OBJ$ row
ORPHAN INDPART$: OBJ#=149168 BO#=6378 - no OBJ$ row
.- DuplicateBlockUse ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- FetUet ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- Uet0Check ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- SeglessUET ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadInd$ ... 1102000400 <= *All Rel* 05/31 09:12:23 FAIL
HCKE-0030: OBJ$ INDEX entry has no IND$ or INDPART$/INDSUBPART$ entry (Doc ID 13
60528.1)
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148278 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16462
OBJ$ INDEX PARTITION has no INDPART$ entry: Obj#=148920 SYS Name=WRH$_FILESTATXS
_PK PARTITION=WRH$_FILEST_1572571104_16678
.- BadTab$ ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadIcolDepCnt ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjIndDobj ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- TrgAfterUpgrade ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjType0 ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadOwner ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- StmtAuditOnCommit ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadPublicObjects ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadSegFreelist ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadDepends ... 1102000400 <= *All Rel* 05/31 09:12:23 WARN
HCKW-0016: Dependency$ p_timestamp mismatch for VALID objects (Doc ID 1361045.1)
[E] - P_OBJ#=6376 D_OBJ#=6765
.- CheckDual ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- ObjectNames ... 1102000400 <= *All Rel* 05/31 09:12:23 PASS
.- BadCboHiLo ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- ChkIotTs ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- NoSegmentIndex ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- BadNextObject ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- DroppedROTS ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- FilBlkZero ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- DbmsSchemaCopy ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- OrphanedObjError ... 1102000400 > 1102000000 05/31 09:12:24 PASS
.- ObjNotLob ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- MaxControlfSeq ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- SegNotInDeferredStg ... 1102000400 > 1102000000 05/31 09:12:24 PASS
.- SystemNotRfile1 ... 1102000400 > 902000000 05/31 09:12:24 PASS
.- DictOwnNonDefaultSYSTEM ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- OrphanTrigger ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
.- ObjNotTrigger ... 1102000400 <= *All Rel* 05/31 09:12:24 PASS
---------------------------------------
31-5月 -2022 09:12:24 Elapsed: 2 secs
---------------------------------------
Found 4 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output and trace file
to check if the above needs attention or not
PL/SQL 过程已成功完成。