环境：oracle 12.2 单机rac + redhat 6.9
em13c报错：
| 在以下时间/行号处的 /home/u01/app/oracle/diag/rdbms/db6/db61/alert/log.xml 中检测到操作错误 (osd kill succee…): sun mar 15 23:23:40 2020/67198。 |
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