ORA-20001: Statistics Advisor: Invalid task name for the current user

oracle版本：12.2.0.1

问题描述：

alert日志里面一直报出如下错误信息：

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_621"
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

看着像是统计信息的定时job执行失败了，然后看了下其他一些12.2.0.1的库，发现alert日志里面都有报这个错误，于是怀疑是oracle bug。

找到如下文章：

Oracle Database Configuration Assistant (DBCA) Known Bugs
This section describes known bugs for Oracle Database Configuration Assistant (DBCA).

Bug 25710407
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
This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database:

SQL> 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');  2  
 3    4
Workaround:

Connect to the created database using the SYSDBA privilege and run the following command:

SQL> 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');  2  
 3    4  

no rows selected

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.
Now there are rows in these queries:

SQL> 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
13-MAR-17 CMD

INDIVIDUAL_STATS_ADVISOR_TASK
13-MAR-17 CMD
Once the Stats Advisor Tasks are available in database, the database alert log no longer has:

ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

这个bug 号在metalink上暂时还没找到，按照上述步骤，我单独执行了exec dbms_stats.gather_database_stats_job_proc，可以成功(之前报上述错误)。

但在alert日志里面没有看到

ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

可以需要收集统计信息的定时任务执行成功一次才能报！

----------------------------------------------------------------------------------------------

目前在12.2.0.1上遇到的第一个小bug，已入坑！

---------------------------------------------------------------------------------------------------------------------------

ps：2017-10-17

这个报错是由于12.2新增了个 Optimizer Statistics Advisor的自动任务。这玩意跑起来还会有问题，sysaux表空间会增长的比较快。