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

During the 12c database creation process , you can see ORA-20001 error in the alert log file when the “SYS.ORA $ AT_OS_OPT_SY_ <NN>” auto job runs. To fix the error, it is necessary to drop the job and recreate it. Errors will be as follows.

In alert log i am getting below error:

Error
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_1200"
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
2020-06-24T23:21:43.781403-04:00

Cause:
Issue occurred while creating database with DBCA, the advisory package is not created properly. This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database.

It is Oracle known Bug and can appear when database is created with dbca in oracle 12.2

Solution:

connect with sysdba privilege user and run following query:

SQL> 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

Initialize the package with following commands:

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Verify the package create. It will fixed the issue

SQL> 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        26-JUN-20 CMD

INDIVIDUAL_STATS_ADVISOR_TASK  26-JUN-20 CMD

In this case, you should connect with sys via sqlplus and drop and recreate the tasks correctly.
Drop operations can be done as follows.

SQL> 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        26-JUN-20 CMD

SYS

INDIVIDUAL_STATS_ADVISOR_TASK  26-JUN-20 CMD

SYS

SQL> 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.

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.

SQL> 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

It should then be re-created as follows.

SQL> EXEC DBMS_STATS.INIT_PACKAGE();

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Verify the package create. It will fixed the issue

SQL> 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        26-JUN-20 CMD

INDIVIDUAL_STATS_ADVISOR_TASK  26-JUN-20 CMD

Recheck issue in alert log.