ORA-12012: error on auto execute of job

I got ” ORA-12012: error on auto execute of job ” error in Oracle database.

 

ORA-12012: error on auto execute of job

 

Details of error are as follows.

 

ORA-12012: Error on Auto Execute of Job

Cause: An error was caught while doing an automatic execution of a job.

Action: Look at the accompanying errors for details on why the execute failed.
    

 

 

error on auto execute of job

This ORA-12012 errors are related with the error was caught while doing an automatic execution of a job.

 

Seed database was most likely not created right by package dbms_stats.init_package not being ran.

Dbms_stats.init_package creates statistics advisor. This procedure is executed during database creation. If something went wrong during database creation,(for example, init_package is not called for some reason), this kind of errors may be seen in alert log when auto job tries to execute.

 

To solve this error, Run dbms_stats.init_package()  in the container database to create the tasks correctly:

 

$ sqlplus / as sysdba

 EXEC dbms_stats.init_package();




column name format A35
set linesize 120

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');

 

 

 

Output of the query will look like this:

 

 

NAME                                 CTIME      HOW_CREATED
----------------------------------- ---------- ------------------------------
AUTO_STATS_ADVISOR_TASK              14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK        14-APR-16 CMD
 

 

If the query based on “where owner_name = ‘SYS'” condition does not show any rows but the error continues, please change the query as shown below to

see if a non-SYS user like SYSTEM owns those tasks for some reason:

 

$ sqlplus / as sysdba

select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

 

 

If the OWNER is a non-SYS user, you have to drop the tasks as that user first and then try to solution mention in the Note again.

This was a case for one customer.

For example:

— Connect as SYSTEM, for example, if that user owned the tasks and non SYS for some reason

 

SQL> conn system/&password


DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/


connect / as sysdba
EXEC DBMS_STATS.INIT_PACKAGE();

 

Second case is as follows:

 

  • Alert during auto optimizer stats collection shows following errors:
    ORA-12012: error on auto execute of job “SYS”.”ST$AUTO5548_312_B49″
    ORA-20005: object statistics are locked (stattype = ALL)
    ORA-06512: at “SYS.DBMS_STATS”, line 34850
    ORA-06512: at line 1

 

This is due to following bug that is still being investigated:

Bug 13969759 – AUTO STATS JOB GATHERING STATS ON LOCKED TABLES

The sql tuning advisor job which is likely causing the conflict here.
It is the stats verification query issued by autosqltune.

Auto sqltune, tunes top sqls in maintenance window and create sql profiles.
In this process, it checks if the objects in the sqls have accurate stats by
issuing the sqls mentioned in the beginning of bug description.

 

Disable auto tuning job and run manually when required:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 1 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

IT Tutorial