Introduction
ORA-12012 error is related to Oracle Database and is part of the Oracle Job Scheduler (DBMS_SCHEDULER). It usually occurs when an automatic task or job execution encounters an issue. The error message you’re seeing, “ORA$AT_OS_OPT_SY_<number>,” is specific to the job causing the problem.
During the 12c database creation process , you can see ORA-12012 error in the alert log file when the
“SYS.ORA$AT_OS_OPT_SY_ ” auto job runs. To fix the error, it is necessary to drop the job and recreate it.
Errors will be as follows.
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_14524.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_767"
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 47197Cause
Originally reported for creating a database using DBCA 12.2.0.1 in Linux or Windows platform. 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 an Oracle known Bug and can appear when database is created with dbca in oracle 12.2
Solution
- Validate Invalid Objects
- Create or Drop and recreate the tasks correctly
- Check database alert log
1. Validate Invalid Objects
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
## If the is count above 0 then compile it by running below command in sql ##
SQL> @?/rdbms/admin/utlrp.sql2. Create or Drop and recreate the tasks correctly
First case when Stats Advisor Tasks are not available.
##Check availability of Stats Advisor Tasks by running below query##
SQL> set lines 999
col NAME for a45
col CTIME for a30
col HOW_CREATED for a20
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
##Create the package with following commands:##
SQL> EXEC dbms_stats.init_package();
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete
##Verify the package created##
SQL> set lines 999
col NAME for a45
col CTIME for a30
col HOW_CREATED for a20
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 01-OCT-23 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 01-OCT-23 CMD
Second case when Stats Advisor Tasks are available.
##Check availability of Stats Advisor Tasks by running below query##
SQL> set lines 999
col NAME for a45
col CTIME for a30
col HOW_CREATED for a20
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 01-OCT-23 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 01-OCT-23 CMD
##Drop the Stats Advisor tasks##
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.
## Verify the drop##
SQL> set lines 999
col NAME for a45
col CTIME for a30
col HOW_CREATED for a20
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 selectedRecreate the tasks
##Create the package with following commands:##
SQL> EXEC dbms_stats.init_package();
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete
##Verify the package created##
SQL> set lines 999
col NAME for a45
col CTIME for a30
col HOW_CREATED for a20
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 02-OCT-23 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 02-OCT-23 CMD3. Check database alert log
Once the Stats Advisor Tasks are available in database, the database alert log should no longer show the following errors:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_767"Conclusion
In conclusion, the ORA-12012 error can be a bit challenging to diagnose, but with systematic troubleshooting, you can identify and resolve the issue. Always ensure that you have proper backups before making any significant changes to your database, and consider testing changes in a non-production environment first.
