ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_128403"

created at 12-08-2021 views: 65

ERROR

I just took over an Oracle 12C database. When configuring the job of monitoring the alarm log, I found a large number of the following errors in the alarm log, and these errors usually appear at 22:00 in the evening

Errors in file /home/u01/app/oracle/diag/rdbms/xxx/xxx/trace/xxx_j000_106602.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_128403"

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

database info

The database version information is as follows:

SQL> select * from v$version;

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

SQL>

Checked the official documents, combined with the analysis of the information currently obtained, it is very likely that you have encountered Bug 25710407, it is very likely that the DBA used DBCA to build the library at that time, and used General_Pupose.dbc or  Data_Warehouse.dbc this template.

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

solution

The root cause of this problem is that the Stats Advisor Tasks in the created database are not available.

set linesize 1080;
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');

I verified that using the above script under CDB$ROOT, there is indeed no record

SQL> set linesize 1080;
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

SQL>

Switch to the corresponding PDB and found that there are records

QL> set linesize 1080;
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');SQL> SQL> 2 3 4 5 6

NAME                            CTIME           HOW_CREATED
------------------------------ ------------- ---------------
AUTO_STATS_ADVISOR_TASK         26-JAN-17        CMD
INDIVIDUAL_STATS_ADVISOR_TASK   26-JAN-17        CMD

SQL>

Switch back to the container CDB$ROOT, after executing the following SQL

exec dbms_stats.init_package();

You can see the job record after execution. After observing for a few days, I found that there were no more errors in the alarm log. The problem is completely solved!

SQL> set linesize 1080;
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       03-DEC-21           CMD
INDIVIDUAL_STATS_ADVISOR_TASK 03-DEC-21           CMD
created at:12-08-2021
edited at: 12-08-2021: