代码改变世界

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

  潇湘隐者  阅读(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

编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题: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.)
点击右上角即可分享
微信分享提示