Related to Bug 25710407

Originally reported for creating a database using DBCA 12.2.0.1 in Linux and Solaris platform, we also encountered the same error on Windows having used a scripted database creation based on the DBCA scripts. The workaround for this bug worked in our Windows environment.

The database alert log contents are:

Errors in file
x:\app\oracle\diag\rdbms\xxxx\xxxx\trace\xxxx_j000_xxxxx.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_xxxx"
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

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');  
no rows selected

Workaround

If there are rows returned then you should drop and recreate the tasks correctly.

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘AUTO_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

PL/SQL procedure successfully completed.

DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := ‘INDIVIDUAL_STATS_ADVISOR_TASK’;
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

Then check and create the package

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'); 

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-20 CMD

INDIVIDUAL_STATS_ADVISOR_TASK
13-MAR-20 CMD

Once the Stats Advisor Tasks are available in database, the database alert log should no longer show the following errors:

ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX