Oracle报错：OSD kill succeeded for process（转）

https://www.ssfiction.com/sqljc/796437.html

环境：oracle 12.2 单机rac + redhat 6.9

em13c报错：

alert log:

2020-03-15t23:23:40.022325+08:00
process 0x0x403c7de50 appears to be hung in auto sql tuning task
current time = 1584285819, process death time = 1584285804
attempting to kill process 0x0x403c7de50 with os pid = 309067
osd kill succeeded for process 0x403c7de50

错误原因：
这个问题发生在sys_auto_sql_tuning_task运行时，自动kill了 hung或者长时间运行（long running）的进程任务，防止系统超负荷运行（over-running ）。

解决办法：
给sys_auto_sql_tuning_task任何更多的运行时间，默认是1200秒（20分钟），可以修改成6个小时（21600秒）。

select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = ‘sys_auto_sql_tuning_task’
and parameter_name = ‘local_time_limit’;

begin
dbms_sqltune.set_tuning_task_parameter(‘sys_auto_sql_tuning_task’, ‘local_time_limit’, 21600);
end;
/

error at line 1:
ora-13647: setting of parameter local_time_limit is disallowed during task
execution.
ora-06512: at “sys.prvt_advisor”, line 4848
ora-06512: at “sys.prvt_advisor”, line 4809
ora-06512: at “sys.dbms_sys_error”, line 86
ora-06512: at “sys.prvt_advisor”, line 1094
ora-06512: at “sys.prvt_advisor”, line 4699
ora-06512: at “sys.prvt_advisor”, line 4827
ora-06512: at “sys.dbms_advisor”, line 409
ora-06512: at “sys.dbms_sqltune”, line 1150
ora-06512: at line 2

connect / as sysdba

begin
dbms_auto_task_admin.disable(
client_name => ‘sql tuning advisor’,
operation => null,
window_name => null);
end;
/

sql> select status from dba_advisor_tasks where task_name = ‘sys_auto_sql_tuning_task’;

status
———–
executing
sql> exec dbms_sqltune.cancel_tuning_task(‘sys_auto_sql_tuning_task’);

pl/sql procedure successfully completed.
sql> select status from dba_advisor_tasks where task_name = ‘sys_auto_sql_tuning_task’;

status
———–
cancelled
sql> begin
dbms_sqltune.set_tuning_task_parameter(‘sys_auto_sql_tuning_task’, ‘local_time_limit’, 21600);
end;
/

begin
dbms_auto_task_admin.enable(
client_name => ‘sql tuning advisor’,
operation => null,
window_name => null);
end;
/
sql> select parameter_name,parameter_value from dba_advisor_parameters where task_name=’sys_auto_sql_tuning_task’;

select parameter_name, parameter_value
from dba_advisor_parameters
where task_name = ‘sys_auto_sql_tuning_task’
and parameter_name = ‘local_time_limit’;

参考mos

doc id 1344499.1
doc id 1597819.1