今天发现一套数据库，2节点归档失败，下面我们就来说一说关于oracle数据库关闭的四个选项?我们一起去了解并探讨一下这个问题吧!
oracle数据库关闭的四个选项
今天发现一套数据库，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
最后：由于有未归档的日志被清除，所以原来的全备，只能通过归档前滚到清除归档之前的时刻，为了以防万一，建议及时对数据库发起一次全备。
,
