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

2022 年 03 月 16 日

317 次浏览

4101字数

刚接手的一个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
文章来源:潇湘隐者

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