Hello,
During the 12.2 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.
In alert log, the below errors happen
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
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.
This is Oracle known Bug in 12.2 and can appear when database is created with dbca.
Solution:
You must connect with sysdba privilege 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 must connect with sys and drop and recreate the tasks correctly.
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
Let me recreate it
SQL> EXEC DBMS_STATS.INIT_PACKAGE();
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Let me verify it
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
Talip Hakan Ozturk's ORACLE BLOG 