Orace 12.2 ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_21”

一个测试环境的12.2.0.1数据库后台alert不断报出以下错误信息：

2018-05-14T01:33:23.571038+08:00
Errors in file /u01/oracle/diag/rdbms/marvin/marvin1/trace/marvin1_j000_23464.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_2764"
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
2018-05-14T01:43:26.782841+08:00
Errors in file /u01/oracle/diag/rdbms/marvin/marvin1/trace/marvin1_j000_25339.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_2786"
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
2018-05-14T02:00:00.515544+08:00
Closing Resource Manager plan via scheduler window
Clearing Resource Manager CDB plan via parameter

看了一下报错频率，大概是每隔10分钟报一次。该库是12.2.0.1的CDB，其中还有一个pdb。http://www.cndba.cn/Marvinn/article/2785http://www.cndba.cn/Marvinn/article/2785

从MOS找到一篇对应的文章：ORA-12012 Error on auto execute of job “SYS”.”ORA$ATOS_OPT_SY<NN> in 12.2 Database (Doc ID 2127675.1)http://www.cndba.cn/Marvinn/article/2785

分析可能是在建库从seed种子库复制建库的时候没有执行dbms_stats.init_package()

Run dbms_stats.init_package() in the container database to create the tasks correctly: http://www.cndba.cn/Marvinn/article/2785

$ sqlplus / as sysdba
SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

SQL> column name format A35
SQL> set linesize 120
SQL> 
SQL> select name, ctime, how_created
  2    from sys.wri$_adv_tasks
  3   where owner_name = 'SYS'
  4     and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                                CTIME     HOW_CREATED
----------------------------------- --------- ------------------------------
AUTO_STATS_ADVISOR_TASK             17-JUN-17 CMD
INDIVIDUAL_STATS_ADVISOR_TASK       17-JUN-17 CMD

执行结束后，观察日志，不再继续报错！http://www.cndba.cn/Marvinn/article/2785

版权声明：本文为博主原创文章，未经博主允许不得转载。