ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_128403"
2021-12-08 22:12 潇湘隐者 阅读(1821) 评论(0) 编辑 收藏 举报刚接手的一个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

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2020-12-08 SQL Server授予了CREATE TABLE权限但是无法创建表浅析
2015-12-08 CREATE FILE encountered operating system error 5(Access is denied.)