Oracle 12.2 报错：ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_7458"

alert报错

2019-01-12t10:10:11.499130+08:00
errors in file /u01/app/oracle/diag/rdbms/rac1/rac112/trace/rac112_j000_119621.trc:
ora-12012: error on auto execute of job "sys"."ora$at_os_opt_sy_7458"
ora-20001: statistics advisor: invalid task name for the current user
ora-06512: at "sys.dbms_stats", line 47207
ora-06512: at "sys.dbms_stats_advisor", line 882
ora-06512: at "sys.dbms_stats_internal", line 20059
ora-06512: at "sys.dbms_stats_internal", line 22201
ora-06512: at "sys.dbms_stats", line 47197
查看trc文件

more /u01/app/oracle/diag/rdbms/rac1/rac112/trace/rac112_j000_23470.trc
trace file /u01/app/oracle/diag/rdbms/rac1/rac112/trace/rac112_j000_23470.trc
oracle database 12c enterprise edition release 12.2.0.1.0 - 64bit production
build label:    rdbms_12.2.0.1.0_linux.x64_170125
oracle_home:    /u01/app/oracle/product/12.2.0.1/dbhome_1
system name:    linux
node name:    exa02
release:    4.1.12-94.8.5.el6uek.x86_64
version:    #2 smp wed jul 11 12:03:41 pdt 2018
machine:    x86_64
storage:    exadata
instance name: rac112
redo thread mounted by this instance: 2
oracle process number: 512
unix process pid: 23470, image: oracle@exa02 (j000)


*** 2019-01-12t09:29:47.002259+08:00 (cdb$root(1))
*** session id:(3880.47911) 2019-01-12t09:29:47.002289+08:00
*** client id:() 2019-01-12t09:29:47.002296+08:00
*** service name:(sys$users) 2019-01-12t09:29:47.002303+08:00
*** module name:(dbms_scheduler) 2019-01-12t09:29:47.002309+08:00
*** action name:(ora$at_os_opt_sy_7436) 2019-01-12t09:29:47.002316+08:00
*** client driver:() 2019-01-12t09:29:47.002322+08:00
*** container id:(1) 2019-01-12t09:29:47.002328+08:00
 
ora-12012: error on auto execute of job "sys"."ora$at_os_opt_sy_7436"
ora-20001: statistics advisor: invalid task name for the current user
ora-06512: at "sys.dbms_stats", line 47207
ora-06512: at "sys.dbms_stats_advisor", line 882
ora-06512: at "sys.dbms_stats_internal", line 20059
ora-06512: at "sys.dbms_stats_internal", line 22201
ora-06512: at "sys.dbms_stats", line 47197

--解决方法

sqlplus / as sysdba

sql*plus: release 12.2.0.1.0 production on sat jan 12 10:17:11 2019

copyright (c) 1982, 2016, oracle.  all rights reserved.


connected to:
oracle database 12c enterprise edition release 12.2.0.1.0 - 64bit production
sql> select name, ctime, how_created
  from sys.wri$_adv_tasks
 where owner_name = 'sys'
   and name in ('auto_stats_advisor_task','individual_stats_advisor_task');
 
no rows selected
 
sql> exec dbms_stats.init_package();
 
pl/sql procedure successfully completed.
 
sql> select name, ctime, how_created
    from sys.wri$_adv_tasks
   where owner_name = 'sys'
     and name in ('auto_stats_advisor_task','individual_stats_advisor_task');
 
name
--------------------------------------------------------------------------------
ctime      how_created
--------- ------------------------------
auto_stats_advisor_task
12-jan-19 cmd

individual_stats_advisor_task
12-jan-19 cmd