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)           5

SQL> 

2、查看告警日志
2018-04-15 10:33:02.004000 +08:00
Suppressing further error logging of LOG_ARCHIVE_DEST_1.
Suppressing further error logging of LOG_ARCHIVE_DEST_1.
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl2 - Archival Error
ORA-16038: log 22 sequence# 274 cannot be archived
ORA-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 stopped
ARC4: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance orcl2 - Archival Error
ORA-16014: log 22 sequence# 274 not archived, no available destinations
ORA-00312: online log 22 thread 2: '+DATADG/orcl/onlinelog/redo22_01.log'

3、查看ASM磁盤組
[root@roidb2 ~]# su - grid
<roidb2:+ASM2:/home/grid>$asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  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)