今天测试log_archive_desc_n设置路径时链接朋友们的blog发现一个关于网络强制方面的应用，现在记录下来以后也许能够使用的到

由于备机新升级了硬盘，一时还没把dg（data guard）装好，就断开了主机和备机的网络，今天在手工switch logfile的时候，当切出到低18个的时候，就挂住了，长时间没有相应。
检查alertlog中，发现在不断写出如下的报错：

……
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:oracleadminoralocalbdumporalocal_arc1_260.trc:
ORA-16014: log 2 sequence# 323 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO201.LOG'
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO202.LOG'
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO203.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:oracleadminoralocalbdumporalocal_arc0_200.trc:
ORA-16014: log 2 sequence# 323 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO201.LOG'
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO202.LOG'
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO203.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:oracleadminoralocalbdumporalocal_arc1_260.trc:
ORA-16014: log 2 sequence# 323 not archived, no available destinations
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO201.LOG'
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO202.LOG'
ORA-00312: online log 2 thread 1: 'D:ORACLELOGFILESORALOCALREDO203.LOG'

检查保护模式和归档情况：

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 VALIDLOG_ARCHIVE_DEST_2 ERROR ORA-12535: TNS: 操作超时LOG_ARCHIVE_DEST_3 INACTIVELOG_ARCHIVE_DEST_4 INACTIVELOG_ARCHIVE_DEST_5 INACTIVELOG_ARCHIVE_DEST_6 INACTIVELOG_ARCHIVE_DEST_7 INACTIVELOG_ARCHIVE_DEST_8 INACTIVELOG_ARCHIVE_DEST_9 INACTIVELOG_ARCHIVE_DEST_10 INACTIVE

已选择10行。

发现确实由于网络断开，归档出现error。

检查等待事件：

sys@ORALOCAL(10.1.19.16)> select distinct event from v$session_wait;
EVENT
----------------------------------------------------------------
ARCH wait on ATTACHnull eventpmon timerrdbms ipc messagesmon timerswitch logfile commandwakeup time manager

检查arch的归档路径，发现dg的路径是设置了mandatory：

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------archive_lag_target integer 0log_archive_dest stringlog_archive_dest_1 string location=D:oraclearchoraloc
allog_archive_dest_10 stringlog_archive_dest_2 string SERVICE=standby MANDATORY
REOPEN=60log_archive_dest_3 stringlog_archive_dest_4 stringlog_archive_dest_5 stringlog_archive_dest_6 stringlog_archive_dest_7 stringlog_archive_dest_8 stringlog_archive_dest_9 stringlog_archive_dest_state_1 string enablelog_archive_dest_state_10 string enablelog_archive_dest_state_2 string enablelog_archive_dest_state_3 string enablelog_archive_dest_state_4 string enablelog_archive_dest_state_5 string enablelog_archive_dest_state_6 string enablelog_archive_dest_state_7 string enablelog_archive_dest_state_8 string enablelog_archive_dest_state_9 string enablelog_archive_duplex_dest stringlog_archive_format string oralocal_%T_%S.arclog_archive_max_processes integer 2log_archive_min_succeed_dest integer 1log_archive_start boolean TRUElog_archive_trace integer 0remote_archive_enable string truestandby_archive_dest string %ORACLE_HOME%RDBMSsys@ORALOCAL(10.1.19.13)>

因此原因找到：是因为将远程的归档模式设置为强制模式，而网络中断，无法实现强制归档，因此挂起，
修改成optional后即恢复正常。

alter system set log_archive_dest_2='SERVICE=standby optional' scope=both;

收藏。转载自 http://www.oracleblog.cn/working-case/dg-set-mandatory-machine-by-archiving-mainframe-can-not-cut-out-log/

感谢OracleBlog.cn -天堂向左 DBA向右

[@more@]

ITPub博客

学习MANDATORY 方式

最新文章