ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_128403"

刚接手的一个Oracle 12C数据库，配置监控告警日志的作业时，发现告警日志中有大量下面错误，而且这些错误一般是晚上22点出现

 

Errors in file /home/u01/app/oracle/diag/rdbms/xxx/xxx/trace/xxx_j000_106602.trc:

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_128403"

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

 

数据库版本信息如下所示：

 

SQL> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

SQL>

 

查了一下官方文档，结合当前获取的信息分析，很有可能是遇到了Bug 25710407，很有可能当时的DBA使用DBCA建库，而且使用的是General_Pupose.dbc或 Data_Warehouse.dbc这个模板。

 

When creating a database using the DBCA from the General_Pupose.dbc or Data_Warehouse.dbc templates (or from the Seed Database) using the 12.2.0.1 Production SH in Linux and Solaris platform, the following errors in the database alert log are returned:

 

ORA-12012 , ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

 

Platforms : Linux 12.2.0.1 Production SH

            Solaris 12.2.0.1 Production SH

The database alert log contents are:

 

Errors in file

/u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j000_73185.trc:

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_936"

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

2017-03-13T00:47:21.394481+00:00

 

此问题出现的根本原因是创建的数据库中的统计信息顾问任务（ Stats Advisor Tasks）不可用

 

set linesize 1080;
col name for a30;
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');

验证了一下，在CDB$ROOT下面使用上面脚本，确实没有记录

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> set linesize 1080;
col name for a30;
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>

 

切换到对应的PDB，发现有记录

 

SQL> set linesize 1080;
col name for a30;
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');SQL> SQL> 2 3 4 5 6

NAME                            CTIME           HOW_CREATED
------------------------------ ------------- ---------------
AUTO_STATS_ADVISOR_TASK         26-JAN-17        CMD
INDIVIDUAL_STATS_ADVISOR_TASK   26-JAN-17        CMD

SQL>

切换回容器CDB$ROOT下，执行下面SQL后

 

exec dbms_stats.init_package();

 

 

执行后可以见到作业记录了。过后观察了几天，发现告警日志中再也没有这些错误了。问题也算彻底解决了！

SQL> set linesize 1080;
col name for a30;
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       03-DEC-21           CMD
INDIVIDUAL_STATS_ADVISOR_TASK 03-DEC-21           CMD

 

 

 

参考资料：

  https://docs.oracle.com/database/122/READM/dbca-known-bugs.htm#READM-GUID-DBD9265E-2964-42AA-A534-FB4B9F35BB72