Oracle 12.2及以上版本 ORA-12012报错

alert日志中发现以下报错：

2018-11-22T22:00:00.010851+08:00
Setting Resource Manager plan SCHEDULER[0x4AC6]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
2018-11-22T22:00:40.060871+08:00
Errors in file /data/prd/oracle/database/diag/rdbms/tcp/tcp/trace/tcp_j001_108717.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_38"
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
2018-11-22T22:00:41.211387+08:00

查文档发现是12.2.0.1版本开始新增的错误

处理方法

select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
no rows selected

• 如果查询结果为空，执行

sqlplus / as sysdba
EXEC dbms_stats.init_package();

• 如果查询结果非空，对象owner又不是sys（比如是system），需要先删除非sys用户下的对象

SQL> conn system/&password
DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

再执行

sqlplus / as sysdba
exec dbms_stats.init_package();

column name format A35
set linesize 120
select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');
-- 正常输出如下:
NAME                                 CTIME      HOW_CREATED   OWNER_NAME
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 CMD

报错原因

种子数据库未正确创建，未成功运行 dbms_stats.init_package 

Seed database was most likely not created right by package dbms_stats.init_package not being ran. 

Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute. 

参考

ORA-12012 Error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_<NN> in 12.2.0 Database version or higher release (like 18c) (文档 ID 2127675.1)