log file switch (archiving needed) 等待事件一例

一、环境描述
Oracle 11g RAC

二、问题描述
客户执行批处理长时间无法完成。

三、问题处理过程

1、查看等待事件SQL> select inst_id,event,count(*) from gv$session where wait_class<>'Idle' group by inst_id,event order by 1,2;   INST_ID EVENT                                 COUNT(*)---------- ----------------------------------- ----------         2 SQL*Net message to client                    1         2 buffer busy waits                            4         2 log file switch (archiving needed)           5SQL> 2、查看告警日志2018-04-15 10:33:02.004000 +08:00Suppressing further error logging of LOG_ARCHIVE_DEST_1.Suppressing further error logging of LOG_ARCHIVE_DEST_1.ARCH: Archival stopped, error occurred. Will continue retryingORACLE Instance orcl2 - Archival ErrorORA-16038: log 22 sequence# 274 cannot be archivedORA-19504: failed to create file ""ORA-00312: online log 22 thread 2: '+DATADG/orcl/onlinelog/redo22_01.log'Archiver process freed from errors. No longer stoppedARC4: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2ARCH: Archival stopped, error occurred. Will continue retryingORACLE Instance orcl2 - Archival ErrorORA-16014: log 22 sequence# 274 not archived, no available destinationsORA-00312: online log 22 thread 2: '+DATADG/orcl/onlinelog/redo22_01.log'3、查看ASM磁盘组[root@roidb2 ~]# su - grid$asmcmd -pASMCMD [+] > lsdgState    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  NameMOUNTED  NORMAL  N         512   4096  1048576     12288    11362             4096            3633              0             Y  CRSDG/MOUNTED  EXTERN  N         512   4096  1048576      6144       76                0              76              0             N  DATADG/ASMCMD [+] > 4、通过以上信息发现ASM磁盘组空间已被耗尽，无法进行归档操作，清理部分归档日志即可解决问题。这里我们通过查看等待事件很容易定位到问题。

四、参考官方文档

Resolving Issues Where 'log file switch (archiving needed)' Waits Occur Because Log has not yet been Archived (文档 ID 1476444.1)