由于备机新升级了硬盘，一时还没把dg（data guard）装好，就断开了主机和备机的网络，今天在手工switch logfile的时候，当切出到低18个的时候，就挂住了，长时间没有相应。
检查alertlog中，发现在不断写出如下的报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
…… Tue Oct 16 23:09:36 2007 ARC1: Evaluating archive log 2 thread 1 sequence 323 ARC1: Archive destination LOG_ARCHIVE_DEST_1: Previously completed ARC1: Archiving not possible: No available destinations ARC1: Failed to archive log 2 thread 1 sequence 323 Tue Oct 16 23:09:36 2007 Errors in file d:\oracle\admin\oralocal\bdump\oralocal_arc1_260.trc: ORA-16014: log 2 sequence# 323 not archived, no available destinations ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG' ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG' ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG' Tue Oct 16 23:09:42 2007 ARC0: Evaluating archive log 2 thread 1 sequence 323 ARC0: Archive destination LOG_ARCHIVE_DEST_1: Previously completed ARC0: Archiving not possible: No available destinations ARC0: Failed to archive log 2 thread 1 sequence 323 Tue Oct 16 23:09:42 2007 Errors in file d:\oracle\admin\oralocal\bdump\oralocal_arc0_200.trc: ORA-16014: log 2 sequence# 323 not archived, no available destinations ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG' ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG' ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG' Tue Oct 16 23:09:47 2007 ARC1: Evaluating archive log 2 thread 1 sequence 323 ARC1: Archive destination LOG_ARCHIVE_DEST_1: Previously completed ARC1: Archiving not possible: No available destinations ARC1: Failed to archive log 2 thread 1 sequence 323 Tue Oct 16 23:09:47 2007 Errors in file d:\oracle\admin\oralocal\bdump\oralocal_arc1_260.trc: ORA-16014: log 2 sequence# 323 not archived, no available destinations ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO201.LOG' ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO202.LOG' ORA-00312: online log 2 thread 1: 'D:\ORACLE\LOGFILES\ORALOCAL\REDO203.LOG' |
检查保护模式和归档情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
sys@ORALOCAL(10.1.19.16)> select PROTECTION_MODE from v$database; PROTECTION_MODE -------------------- MAXIMUM PERFORMANCE sys@ORALOCAL(10.1.19.16)> select dest_name,status,error from v$archive_dest; DEST_NAME STATUS ERROR ------------------------------ ---------------------------------------- ----------------- LOG_ARCHIVE_DEST_1 VALID LOG_ARCHIVE_DEST_2 ERROR ORA-12535: TNS: 操作超时 LOG_ARCHIVE_DEST_3 INACTIVE LOG_ARCHIVE_DEST_4 INACTIVE LOG_ARCHIVE_DEST_5 INACTIVE LOG_ARCHIVE_DEST_6 INACTIVE LOG_ARCHIVE_DEST_7 INACTIVE LOG_ARCHIVE_DEST_8 INACTIVE LOG_ARCHIVE_DEST_9 INACTIVE LOG_ARCHIVE_DEST_10 INACTIVE 已选择10行。 |
发现确实由于网络断开，归档出现error。
检查等待事件：
|
1 2 3 4 5 6 7 8 9 10 11 |
sys@ORALOCAL(10.1.19.16)> select distinct event from v$session_wait; EVENT ---------------------------------------------------------------- ARCH wait on ATTACH null event pmon timer rdbms ipc message smon timer switch logfile command wakeup time manager |
检查arch的归档路径，发现dg的路径是设置了mandatory：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 log_archive_dest string log_archive_dest_1 string location=D:\oracle\arch\oraloc al log_archive_dest_10 string log_archive_dest_2 string SERVICE=standby MANDATORY REOPEN=60 log_archive_dest_3 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_2 string enable log_archive_dest_state_3 string enable log_archive_dest_state_4 string enable log_archive_dest_state_5 string enable log_archive_dest_state_6 string enable log_archive_dest_state_7 string enable log_archive_dest_state_8 string enable log_archive_dest_state_9 string enable log_archive_duplex_dest string log_archive_format string oralocal_%T_%S.arc log_archive_max_processes integer 2 log_archive_min_succeed_dest integer 1 log_archive_start boolean TRUE log_archive_trace integer 0 remote_archive_enable string true standby_archive_dest string %ORACLE_HOME%\RDBMS sys@ORALOCAL(10.1.19.13)> |
因此原因找到：是因为将远程的归档模式设置为强制模式，而网络中断，无法实现强制归档，因此挂起，
修改成optional后即恢复正常。
|
1 |
alter system set log_archive_dest_2='SERVICE=standby optional' scope=both; |