相关自动任务ORA-12012 2000106512报错处理

2021-01-29 10:12:59

之前巡检中发现有一套集群总是报错12012错误，之前对于job类错误我们都不再处理所以一直没有关注，但是近发现改错误再三个节点频繁发生，监控日志刷了好几屏，感觉还是要分析下具体问题。这里是简单的分析流程，做个记录。

xxxx-node1 xxxx-node2 xxxx-node3
xxxx-node1 AlertLog-----------------------------------------------------------------
Errors in file /oracle/diag/rdbms/xxxx/xxxx1/trace/xxxx1_j000_8121.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_92116"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47209
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 47199

[oracle@n-pc1 ~]$oerr ora 12012
12012, 00000, "error on auto execute of job %s"
// *Cause: An error was caught while doing an automatic execution of a job.
// *Action: Look at the accompanying errors for details on why the execute
// failed.

[oracle@n-pc-sr850-219 ~]$oerr ora 6512
06512, 00000, "at %sline %s"
// *Cause: Backtrace message as the stack is unwound by unhandled
// exceptions.
// *Action: Fix the problem causing the exception or write an exception
// handler for this condition. Or you may need to contact your
// application administrator or DBA.

分析：执行自动任务报错，并且这个自动任务为Auto_stats_Advisor报错，从接下来的报错看是06512
通过工具查看该错误号，我们知道这是内部追踪到有未处理的异常，明显这里是Oracle的advisor顾问工具代码由问题
很可能是Bug导致，我们在MOS找到如下文章。
Automatic Statistics Advisor Job Errors with Statistics Fatal Error(Doc ID 2448436.1)
影响的数据库版本是12.2.0-18.3.0.0

经确认为bug2798174导致
解决方法：
1. The bug is closed in 19.1
2. Apply Patch 27983174
3. Use following workaround:
sqlplus / as sysdba
exec dbms_stats.init_package();