标签归档：ORA-00333

resetlogs失败故障恢复-ORA-01555

发表于 2023 年 12 月 29 日 由 惜分飞

客户数据库resetlogs报错

Tue Dec 19 15:21:23 2023
ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1683789043
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Tue Dec 19 15:22:01 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
ORA-1248 signalled during: alter database open resetlogs...
Tue Dec 19 16:16:26 2023
alter database datafile 83 offline
Completed: alter database datafile 83 offline
Tue Dec 19 16:19:13 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Archived Log entry 50 added for thread 1 sequence 3657135 ID 0x5d907698 dest 1:
Tue Dec 19 16:20:01 2023
Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc:
ORA-00333: 重做日志读取块 8806400 计数 16384 出错
ORA-00312: 联机日志 2 线程 1: '/data/oradata/orcl/redo2.log'
ORA-27072: 文件 I/O 错误
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 8806400
Additional information: 4325376
Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc:
ORA-00333: 重做日志读取块 8806400 计数 16384 出错
ARCH: All Archive destinations made inactive due to error 333
ARCH: Closing local archive destination LOG_ARCHIVE_DEST_1: '/data/arch/1_3657136_874715183.dbf' (error 333) (orcl)
Committing creation of archivelog '/data/arch/1_3657136_874715183.dbf' (error 333)
Tue Dec 19 16:20:46 2023
Archived Log entry 51 added for thread 1 sequence 3657132 ID 0x5d907698 dest 1:
Tue Dec 19 16:21:28 2023
Archived Log entry 52 added for thread 1 sequence 3657133 ID 0x5d907698 dest 1:
Tue Dec 19 16:22:13 2023
Archived Log entry 53 added for thread 1 sequence 3657134 ID 0x5d907698 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 161052517347
Resetting resetlogs activation ID 1569748632 (0x5d907698)
Tue Dec 19 16:23:43 2023
Setting recovery target incarnation to 3
Tue Dec 19 16:23:43 2023
Assigning activation ID 1683789043 (0x645c94f3)
LGWR: STARTING ARCH PROCESSES
Tue Dec 19 16:23:43 2023
ARC0 started with pid=40, OS id=5391 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 advanced to log sequence 2 (thread open)
Tue Dec 19 16:23:44 2023
ARC1 started with pid=41, OS id=5393 
Tue Dec 19 16:23:44 2023
ARC2 started with pid=42, OS id=5395 
ARC1: Archival started
Tue Dec 19 16:23:44 2023
ARC3 started with pid=43, OS id=5397 
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /data/oradata/orcl/redo2.log
Successful open of redo thread 1
Tue Dec 19 16:23:44 2023
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Tue Dec 19 16:23:44 2023
SMON: enabling cache recovery
Tue Dec 19 16:23:44 2023
NSA2 started with pid=44, OS id=5399 
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0025.7f7d42df):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 27 (名称为 "_SYSSMU27_4233559991$") 过小
Errors in file /oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_94696.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 27 (名称为 "_SYSSMU27_4233559991$") 过小
Error 704 happened during db open, shutting down database
USER (ospid: 94696): terminating the instance due to error 704
Instance terminated by USER, pid = 94696
ORA-1092 signalled during: alter database open resetlogs...
opiodr aborting process unknown ospid (94696) as a result of ORA-1092

通过以上信息,可以的出来以下结论:
1. 客户的硬件或者文件系统可能有问题,通过系统日志进一步确认底层异常

Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Sense Key : Medium Error [current] 
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Add. Sense: Unrecovered read error
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 47 bc ff c0 00 01 00 00
Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev sdb, sector 1203568576
Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 1203568576
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Sense Key : Medium Error [current] 
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Add. Sense: Unrecovered read error
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 47 bd 00 c0 00 01 00 00
Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev sdb, sector 1203568832
Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 1203568832
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Sense Key : Medium Error [current] 
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb]  Add. Sense: Unrecovered read error
Dec 19 08:28:38 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 47 bd 00 80 00 00 08 00
Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev sdb, sector 1203568768
Dec 19 08:28:38 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 1203568768
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Sense Key : Medium Error [current] 
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Add. Sense: Unrecovered read error
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 9b 1a 28 20 00 01 00 00
Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev sdb, sector 2602182688
Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 2602182688
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Sense Key : Medium Error [current] 
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Add. Sense: Unrecovered read error
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 9b 1a 29 20 00 01 00 00
Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev sdb, sector 2602182944
Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 2602182944
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Sense Key : Medium Error [current] 
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb]  Add. Sense: Unrecovered read error
Dec 19 16:20:01 tdb2 kernel: sd 7:0:0:0: [sdb] CDB: Read(10): 28 00 9b 1a 29 00 00 00 08 00
Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev sdb, sector 2602182912
Dec 19 16:20:01 tdb2 kernel: end_request: critical medium error, dev dm-3, sector 2602182912

2. 数据库在强制拉库的时候,很可能是屏蔽了一致性,导致文件头scn过小
3. 在resetlogs之前,先offline了83号文件,这个将导致该文件的reseltogs scn和其他文件不一致,通过Oracle数据库异常恢复检查脚本(Oracle Database Recovery Check)
20231229095533


这个库由于客户在resetlogs之前offline了数据文件,导致一些麻烦,先使用Oracle Recovery Tools修改resetlogs scn
20231229100250
然后重建ctl,修改scn,打开数据库
20231229102556
hcheck检测字典一切正常

HCheck Version 07MAY18 on 26-12月-2023 18:44:20
----------------------------------------------
Catalog Version 11.2.0.1.0 (1102000100)
db_name: ORCL
                                   Catalog       Fixed           
Procedure Name                     Version    Vs Release    Timestamp      Result
------------------------------ ... ---------- -- ---------- -------------- ------
.- LobNotInObj                 ... 1102000100 <=  *All Rel* 12/26 18:44:20 
PASS
.- MissingOIDOnObjCol          ... 1102000100 <=  *All Rel* 12/26 18:44:20 
PASS
.- SourceNotInObj              ... 1102000100 <=  *All Rel* 12/26 18:44:20 
PASS
.- IndIndparMismatch           ... 1102000100 <= 1102000100 12/26 18:44:21 
PASS
.- InvCorrAudit                ... 1102000100 <= 1102000100 12/26 18:44:21 
PASS
.- OversizedFiles              ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- PoorDefaultStorage          ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- PoorStorage                 ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- PartSubPartMismatch         ... 1102000100 <= 1102000100 12/26 18:44:21 
PASS
.- TabPartCountMismatch        ... 1102000100 <=  *All Rel* 12/26 18:44:21 

*** 2023-12-26 18:44:21.507
PASS
.- OrphanedTabComPart          ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- MissingSum$                 ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- MissingDir$                 ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- DuplicateDataobj            ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- ObjSynMissing               ... 1102000100 <=  *All Rel* 12/26 18:44:21 
PASS
.- ObjSeqMissing               ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedUndo                ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedIndex               ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedIndexPartition      ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedIndexSubPartition   ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedTable               ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedTablePartition      ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedTableSubPartition   ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- MissingPartCol              ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedSeg$                ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- OrphanedIndPartObj#         ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- DuplicateBlockUse           ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- FetUet                      ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- Uet0Check                   ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- ExtentlessSeg               ... 1102000100 <= 1102000100 12/26 18:44:22 
PASS
.- SeglessUET                  ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadInd$                     ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadTab$                     ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadIcolDepCnt               ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- ObjIndDobj                  ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- TrgAfterUpgrade             ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- ObjType0                    ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadOwner                    ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- StmtAuditOnCommit           ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadPublicObjects            ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadSegFreelist              ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadDepends                  ... 1102000100 <=  *All Rel* 12/26 18:44:22 

*** 2023-12-26 18:44:22.571
PASS
.- CheckDual                   ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- ObjectNames                 ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- BadCboHiLo                  ... 1102000100 <=  *All Rel* 12/26 18:44:22 
PASS
.- ChkIotTs                    ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- NoSegmentIndex              ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- BadNextObject               ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- DroppedROTS                 ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- FilBlkZero                  ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- DbmsSchemaCopy              ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- OrphanedObjError            ... 1102000100 >  1102000000 12/26 18:44:23 
PASS
.- ObjNotLob                   ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- MaxControlfSeq              ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- SegNotInDeferredStg         ... 1102000100 >  1102000000 12/26 18:44:23 
PASS
.- SystemNotRfile1             ... 1102000100 >   902000000 12/26 18:44:23 

*** 2023-12-26 18:44:23.779
PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- OrphanTrigger               ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
.- ObjNotTrigger               ... 1102000100 <=  *All Rel* 12/26 18:44:23 
PASS
---------------------------------------
26-12月-2023 18:44:23  Elapsed: 3 secs
---------------------------------------
Found 0 potential problem(s) and 0 warning(s)

然后增加temp,导出数据数据,完成本次数据库救援

发表在 Oracle备份恢复 | 标签为 , , , , , | 评论关闭

ORA-00333 ORA-01595 恢复

发表于 2022 年 7 月 5 日 由 惜分飞

客户反馈数据库异常，查看日志发现asm和db均发生hang住情况（由于环境原因部分日志没有拷贝出来)，基于现有情况,无法直接恢复，通过一些工具把asm磁盘组中的数据文件拷贝到文件系统，经过检测无坏块
20220705233329

修改相关路径,尝试recover库

Tue Jul 05 15:05:54 2022
ALTER DATABASE RECOVER  datafile 1  
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 2 Group 4 Seq 29973 Reading mem 0
  Mem# 0: E:\ORADATA\GROUP_4.266.822672441
Recovery of Online Redo Log: Thread 1 Group 2 Seq 38422 Reading mem 0
  Mem# 0: E:\ORADATA\GROUP_2.262.822672137
Incomplete read from log member 'E:\ORADATA\GROUP_2.262.822672137'. Trying next member.
Media Recovery failed with error 333
ORA-283 signalled during: ALTER DATABASE RECOVER  datafile 1  ...

ORA-00333

错误信息比较明显，在读入redo进行恢复的时候遭遇“ORA-00333: 重做日志读取块 11557 计数 731 出错”错误,从而无法继续恢复.这次故障运气比较好,通过分析v$datafile和v$datafile_header关系
20220705233008
进行一些操作,绕过redo block 11557,顺利recover成功,并且open库

ALTER DATABASE RECOVER  database  
Media Recovery Start
 started logmerger process
Tue Jul 05 15:17:46 2022
Parallel Media Recovery started with 32 slaves
Tue Jul 05 15:17:46 2022
Recovery of Online Redo Log: Thread 2 Group 4 Seq 29973 Reading mem 0
  Mem# 0: E:\ORADATA\GROUP_4.266.822672441
Recovery of Online Redo Log: Thread 1 Group 2 Seq 38422 Reading mem 0
  Mem# 0: E:\ORADATA\GROUP_2.262.822672137
Completed: ALTER DATABASE RECOVER  database

20220705232246

通过分析alert日志发现有ORA-600 4194错误

QMNC started with pid=58, OS id=15980 
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Tue Jul 05 15:18:24 2022
Tue Jul 05 15:18:24 2022
Block recovery from logseq 38423, block 152 to scn 16218380250500
Recovery of Online Redo Log: Thread 1 Group 1 Seq 38423 Reading mem 0
  Mem# 0: E:\ORADATA\GROUP_1.261.822672135
Block recovery stopped at EOT rba 38423.154.16
Block recovery completed at rba 38423.154.16, scn 3776.583740804
Block recovery from logseq 38423, block 152 to scn 16218380250497
Recovery of Online Redo Log: Thread 1 Group 1 Seq 38423 Reading mem 0
  Mem# 0: E:\ORADATA\GROUP_1.261.822672135
Block recovery completed at rba 38423.154.16, scn 3776.583740804
Errors in file F:\APP\ADMINISTRATOR\diag\rdbms\xff\xff1\trace\xff1_smon_5660.trc:
ORA-01595: 释放区 (2) 回退段 (8) 时出错
ORA-00600: 内部错误代码, 参数: [4194], [], [
                                      
Completed: alter database open

这比较简单，对于异常的undo进行处理即可,然后使用hcheck检查字典一致性

SQL> @e:/oradata/txt/11.txt
HCheck Version 07MAY18 on 05-7月 -2022 16:30:18
----------------------------------------------
Catalog Version 11.2.0.3.0 (1102000300)
db_name: xff

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release    Timestamp
Result
------------------------------ ... ---------- -- ---------- --------------
------
.- LobNotInObj                 ... 1102000300 <=  *All Rel* 07/05 16:30:18 PASS
.- MissingOIDOnObjCol          ... 1102000300 <=  *All Rel* 07/05 16:30:19 PASS
.- SourceNotInObj              ... 1102000300 <=  *All Rel* 07/05 16:30:19 PASS
.- OversizedFiles              ... 1102000300 <=  *All Rel* 07/05 16:30:19 PASS
.- PoorDefaultStorage          ... 1102000300 <=  *All Rel* 07/05 16:30:19 PASS
.- PoorStorage                 ... 1102000300 <=  *All Rel* 07/05 16:30:19 PASS
.- TabPartCountMismatch        ... 1102000300 <=  *All Rel* 07/05 16:30:20 PASS
.- OrphanedTabComPart          ... 1102000300 <=  *All Rel* 07/05 16:30:20 PASS
.- MissingSum$                 ... 1102000300 <=  *All Rel* 07/05 16:30:20 PASS
.- MissingDir$                 ... 1102000300 <=  *All Rel* 07/05 16:30:20 PASS
.- DuplicateDataobj            ... 1102000300 <=  *All Rel* 07/05 16:30:20 PASS
.- ObjSynMissing               ... 1102000300 <=  *All Rel* 07/05 16:30:20 PASS
.- ObjSeqMissing               ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedUndo                ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedIndex               ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedIndexPartition      ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedIndexSubPartition   ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedTable               ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedTablePartition      ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedTableSubPartition   ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- MissingPartCol              ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedSeg$                ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- OrphanedIndPartObj#         ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- DuplicateBlockUse           ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- FetUet                      ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- Uet0Check                   ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- SeglessUET                  ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- BadInd$                     ... 1102000300 <=  *All Rel* 07/05 16:30:21 PASS
.- BadTab$                     ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- BadIcolDepCnt               ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- ObjIndDobj                  ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- TrgAfterUpgrade             ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- ObjType0                    ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- BadOwner                    ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- StmtAuditOnCommit           ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- BadPublicObjects            ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- BadSegFreelist              ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- BadDepends                  ... 1102000300 <=  *All Rel* 07/05 16:30:22 PASS
.- CheckDual                   ... 1102000300 <=  *All Rel* 07/05 16:30:23 PASS
.- ObjectNames                 ... 1102000300 <=  *All Rel* 07/05 16:30:23 WARN

HCKW-0018: OBJECT name clashes with SCHEMA name (Doc ID 2363142.1)
Schema=BSHRP INDEX=XFF.XFF

.- BadCboHiLo                  ... 1102000300 <=  *All Rel* 07/05 16:30:23 PASS
.- ChkIotTs                    ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- NoSegmentIndex              ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- BadNextObject               ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- DroppedROTS                 ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- FilBlkZero                  ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- DbmsSchemaCopy              ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- OrphanedObjError            ... 1102000300 >  1102000000 07/05 16:30:24 PASS
.- ObjNotLob                   ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- MaxControlfSeq              ... 1102000300 <=  *All Rel* 07/05 16:30:24 PASS
.- SegNotInDeferredStg         ... 1102000300 >  1102000000 07/05 16:30:25 PASS
.- SystemNotRfile1             ... 1102000300 >   902000000 07/05 16:30:25 PASS
.- DictOwnNonDefaultSYSTEM     ... 1102000300 <=  *All Rel* 07/05 16:30:25 PASS
.- OrphanTrigger               ... 1102000300 <=  *All Rel* 07/05 16:30:25 PASS
.- ObjNotTrigger               ... 1102000300 <=  *All Rel* 07/05 16:30:25 PASS
---------------------------------------
05-7月 -2022 16:30:25  Elapsed: 7 secs
---------------------------------------
Found 0 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 过程已成功完成。

有一个SCHEMA和对象名一样,这个不影响属于正常情况(客户创建了一个用户叫做XFF,然后有创建了一个XFF的对象),该数据库恢复至此基本上晚上,业务可以直接运行,不用做逻辑迁移

发表在 Oracle备份恢复 | 标签为 , | 评论关闭

ORA-00333 故障恢复

发表于 2018 年 4 月 14 日 由 惜分飞

数据库启动报错

SQL> startup
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                  1219136 bytes
Variable Size             218105280 bytes
Database Buffers         1006632960 bytes
Redo Buffers               15556608 bytes
Database mounted.
ORA-00333: redo log read error block 48641 count 8192

数据库启动报ORA-00333错误，官方解释为读redo log发生错误.

00333, 00000, "redo log read error block %s count %s"
// *Cause:  An IO error occurred while reading the log described in the
//          accompanying error.
// *Action: Restore accessibility to file, or get another copy of the file.

alert日志

Sat Apr 14 00:39:13 2018
 alter database open
Sat Apr 14 00:39:13 2018
Beginning crash recovery of 1 threads
 parallel recovery started with 7 processes
Sat Apr 14 00:39:13 2018
Started redo scan
Sat Apr 14 00:39:14 2018
Errors in file /oracle/admin/oa/udump/oa_ora_5659.trc:
ORA-00333: redo log read error block 54785 count 2048
ORA-00312: online log 1 thread 1: '/oracle/oradata/oa/redo01.log'
ORA-27072: File I/O error
Linux Error: 5: Input/output error
Additional information: 4
Additional information: 54785
Additional information: 957952
Sat Apr 14 00:39:14 2018
Errors in file /oracle/admin/oa/udump/oa_ora_5659.trc:
ORA-00333: redo log read error block 48641 count 8192
ORA-00312: online log 1 thread 1: '/oracle/oradata/oa/redo01.log'
ORA-27091: unable to queue I/O
ORA-27072: File I/O error
Linux Error: 5: Input/output error
Additional information: 4
Additional information: 54785
Additional information: 957952
Sat Apr 14 00:39:14 2018
Aborting crash recovery due to error 333
Sat Apr 14 00:39:14 2018
Errors in file /oracle/admin/oa/udump/oa_ora_5659.trc:
ORA-00333: redo log read error block 48641 count 8192
ORA-333 signalled during:  alter database open...

由于硬件异常,数据库在启动的时候读取redo异常,从而使得数据库无法正常启动

检查系统日志

Apr 14 11:14:58 oa kernel: Info fld=0x0, Current sda: sense key Hardware Error
Apr 14 11:14:59 oa kernel: Additional sense: Internal target failure
Apr 14 11:14:59 oa kernel: end_request: I/O error, dev sda, sector 190500041
Apr 14 11:14:59 oa kernel: SCSI error : <0 0 0 0> return code = 0x8000002
Apr 14 11:14:59 oa kernel: Info fld=0x0, Current sda: sense key Hardware Error
Apr 14 11:14:59 oa kernel: Additional sense: Internal target failure
Apr 14 11:14:59 oa kernel: end_request: I/O error, dev sda, sector 190500049
Apr 14 11:14:59 oa kernel: SCSI error : <0 0 0 0> return code = 0x8000002
Apr 14 11:14:59 oa kernel: Info fld=0x0, Current sda: sense key Hardware Error
Apr 14 11:14:59 oa kernel: Additional sense: Internal target failure
Apr 14 11:14:59 oa kernel: end_request: I/O error, dev sda, sector 190500057
Apr 14 11:14:59 oa kernel: SCSI error : <0 0 0 0> return code = 0x8000002
Apr 14 11:14:59 oa kernel: Info fld=0x0, Current sda: sense key Hardware Error
Apr 14 11:14:59 oa kernel: Additional sense: Internal target failure
Apr 14 11:14:59 oa kernel: end_request: I/O error, dev sda, sector 190500065
Apr 14 11:14:59 oa kernel: SCSI error : <0 0 0 0> return code = 0x8000002

大量类似I/O error, dev sda, sector错误,很可能是由于硬件方面异常导致.

损坏redo为当前redo
redo

针对这样的情况,由于是硬件故障,先要通过dbv或者rman检查其他数据文件是否正常,如果有数据文件不能读,那需要对数据文件进行特殊处理.本次恢复的中,客户相对比较幸运,所有数据文件全部可以正常访问,只是当前redo异常,通过隐含参数强制拉库,然后导出数据,重建库解决.类似文章:又一起存储故障导致ORA-00333 ORA-00312恢复

发表在 ORA-xxxxx, Oracle备份恢复 | 标签为 , | 评论关闭