resetlogs失败故障恢复-ORA-01555---惜分飞

2024-01-03 18:57:05

客户数据库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-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-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)
?

这个库由于客户在resetlogs之前offline了数据文件,导致一些麻烦,先使用Oracle Recovery Tools修改resetlogs scn
?

然后重建ctl,修改scn,打开数据库
?

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,导出数据数据,完成本次数据库救援

文章来源:https://blog.csdn.net/xifenfei/article/details/135370025
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！