環境：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 ...
環境：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
