Oracle – OSD kill succeeded for process
公司NC库这几天晚上十一点多就会发送告警，Operational error (OSD kill succee…) detected in /u01/app/oracle/diag/rdbms/ncdb3/ncdb3/alert/log.xml
查看alert.log 和log.xml 日志，都有记录，显示0x0x970a9e428进程在自动SQL调优任务（Auto SQL Tuning task）运行过程中hung住，然后改进程被kill掉了。
alert.log
|
1 2 3 4 |
Process 0x0x970a9e428 appears to be hung in Auto SQL Tuning task Current time = 1553699422, process death time = 1553699401 Attempting to kill process 0x0x970a9e428 with OS pid = 302962 OSD kill succeeded for process 0x970a9e428 |
log.xml
|
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 |
<msg time='2019-03-27T23:10:22.561+08:00' org_id='oracle' comp_id='rdbms' msg_id='kesaiKillProcess:2489:3170456095' client_id='' type='ERROR' group='SQL_Tune' level='2' host_id='SL010A-NCDB3' host_addr='192.xx.xx.xx' module='' pid='103560'> <txt>Process 0x0x970a9e428 appears to be hung in Auto SQL Tuning task </txt> </msg> <msg time='2019-03-27T23:10:22.561+08:00' org_id='oracle' comp_id='rdbms' msg_id='kesaiKillProcess:2493:1670650406' client_id='' type='ERROR' group='SQL_Tune' level='2' host_id='SL010A-NCDB3' host_addr='192.xx.xx.xx' module='' pid='103560'> <txt>Current time = 1553699422, process death time = 1553699401 </txt> </msg> <msg time='2019-03-27T23:10:22.562+08:00' org_id='oracle' comp_id='rdbms' msg_id='kesaiKillProcess:2504:2480300341' client_id='' type='ERROR' group='SQL_Tune' level='2' host_id='SL010A-NCDB3' host_addr='192.xx.xx.xx' module='' pid='103560'> <txt>Attempting to kill process 0x0x970a9e428 with OS pid = 302962 </txt> </msg> <msg time='2019-03-27T23:10:22.562+08:00' org_id='oracle' comp_id='rdbms' msg_id='kesaiKillProcess:2518:234340873' client_id='' type='ERROR' group='SQL_Tune' level='2' host_id='SL010A-NCDB3' host_addr='192.xx.xx.xx' module='' pid='103560'> <txt>OSD kill succeeded for process 0x970a9e428 |
错误原因：
这个问题发生在SYS_AUTO_SQL_TUNING_TASK运行时，自动kill了 hung或者长时间运行（long running）的进程任务，防止系统超负荷运行（over-running ）。
解决办法：
给SYS_AUTO_SQL_TUNING_TASK任何更多的运行时间，默认是1200秒（20分钟），可以修改成6个小时（21600秒）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK' AND parameter_name = 'LOCAL_TIME_LIMIT'; PARAMETER_NAME PARAMETER_VALUE ------------------------------ --------------- LOCAL_TIME_LIMIT 1200 BEGIN DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 21600); END; / SELECT parameter_name, parameter_value FROM dba_advisor_parameters WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK' AND parameter_name = 'LOCAL_TIME_LIMIT'; PARAMETER_NAME PARAMETER_VALUE ------------------------------ --------------- LOCAL_TIME_LIMIT 21600 |