一、环境描述
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)
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。