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