今天发现一套数据库，2节点归档失败
通过gv$instance视图确认，2节点归档状态为FAILED
SQL> select instance_number,archiver from gv$instance;
INSTANCE_NUMBER ARCHIVER
--------------- --------
1 STARTED
2 FAILED进一步查看alert log，我们看到报出GROUP 10和GROUP 13，Arch found corrupt blocks，即redo日志检测到坏块
Thu Aug 05 11:28:31 2021
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_10.433.1020011515'. Trying next member.
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_10.385.1020011519'. Trying next member.
ARC1: Log corruption near block 276184 change 18050169507448 time ?
CORRUPTION DETECTED: thread 2 sequence 346115 log 10 at block 276184. Arch found corrupt blocks
ARC1: All Archive destinations made inactive due to error 354
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/arch/2_346115_761058065.dbf' (error 354) (ccfasp2)
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_10.433.1020011515'. Trying next member.
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_10.385.1020011519'. Trying next member.
ARC0: Log corruption near block 276184 change 18050169507448 time ?
CORRUPTION DETECTED: thread 2 sequence 346115 log 10 at block 276184. Arch found corrupt blocks
ARC0: All Archive destinations made inactive due to error 354
ARC0: Closing local archive destination LOG_ARCHIVE_DEST_1: '/arch/2_346115_761058065.dbf' (error 354) (ccfasp2)
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_10.433.1020011515'. Trying next member.
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_10.385.1020011519'. Trying next member.
ARC1: Log corruption near block 276184 change 18050169507448 time ?
CORRUPTION DETECTED: thread 2 sequence 346115 log 10 at block 276184. Arch found corrupt blocks
ARC1: All Archive destinations made inactive due to error 354
ARC1: Closing local archive destination LOG_ARCHIVE_DEST_1: '/arch/2_346115_761058065.dbf' (error 354) (ccfasp2)
WARNING! CLEARING REDO LOG WHICH HAS NOT BEEN ARCHIVED. BACKUPS TAKEN
BEFORE 05/29/2021 03:33:05 (CHANGE 18050169798707) CANNOT BE USED FOR RECOVERY.
Clearing online log 10 of thread 2 sequence number 346115
ARC1: Log 10 mismatch, expected sequence 346115 found thread 2 sequence 0
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_13.440.1012046677'. Trying next member.
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_13.439.1012046683'. Trying next member.
ARC3: Log corruption near block 1723015 change 18058617326774 time ?
CORRUPTION DETECTED: thread 2 sequence 369957 log 13 at block 1723015. Arch found corrupt blocks
ARC3: All Archive destinations made inactive due to error 354
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '/arch/2_369957_761058065.dbf' (error 354) (ccfasp2)
Thu Aug 05 11:28:39 2021
Thread 2 advanced to log sequence 380616 (LGWR switch)
Current log# 9 seq# 380616 mem# 0: +DG_DATA/ccfasp/onlinelog/group_9.472.1027886349
Thu Aug 05 11:28:45 2021
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_13.440.1012046677'. Trying next member.
Incomplete read from log member '+DG_DATA/ccfasp/onlinelog/group_13.439.1012046683'. Trying next member.
ARC3: Log corruption near block 1723015 change 18058617326774 time ?
CORRUPTION DETECTED: thread 2 sequence 369957 log 13 at block 1723015. Arch found corrupt blocks
ARC3: All Archive destinations made inactive due to error 354
ARC3: Closing local archive destination LOG_ARCHIVE_DEST_1: '/arch/2_369957_761058065.dbf' (error 354) (ccfasp2)确认GROUP 10和13日志组的thread，正好都来自2节点，这也正是2节点归档失败的原因
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 352205 1073741824 512 2 YES ACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
2 1 352206 1073741824 512 2 NO CURRENT 1.8062E+13 05-AUG-21 2.8147E+14
3 2 380611 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
4 2 380614 1073741824 512 2 YES ACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
5 1 352199 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
6 2 380613 1073741824 512 2 YES ACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
7 1 352203 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
8 1 352202 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
9 2 380610 1073741824 512 1 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
10 2 346115 1073741824 512 2 NO INACTIVE 1.8050E+13 29-MAY-21 1.8050E+13 29-MAY-21
11 2 380615 1073741824 512 2 NO CURRENT 1.8062E+13 05-AUG-21 2.8147E+14
12 2 380612 1073741824 512 2 YES ACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
13 2 369957 1073741824 512 2 NO INACTIVE 1.8059E+13 15-JUL-21 1.8059E+13 15-JUL-21
14 1 352201 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
15 1 352204 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
16 1 352200 1073741824 512 2 YES INACTIVE 1.8062E+13 05-AUG-21 1.8062E+13 05-AUG-21
16 rows selected.处理redo日志坏块的问题，应该来说比数据块简单得多，因为redo log本来就是可循环使用、可覆盖的，所以只需要将其强制清除，删除后重建即可。
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 10;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 10;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ('+DG_DATA', '+DG_DATA') SIZE 1G;
Database altered.
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 13;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 13;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 13 ('+DG_DATA', '+DG_DATA') SIZE 1G;
Database altered.再次查询，恢复正常
SQL> select instance_number,archiver from gv$instance;
INSTANCE_NUMBER ARCHIVER
--------------- --------
1 STARTED
2 STARTED最后：由于有未归档的日志被清除，所以原来的全备，只能通过归档前滚到清除归档之前的时刻，为了以防万一，建议及时对数据库发起一次全备。
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/15031.html
