Symptoms: BSLN_MAINTAIN_STATS_JOB fails with the following error: ORA-12012:
error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB" In Trace File Trace file /opt/oracle/diag/rdbms/test2/test2/trace/test2_j000_22448.trc Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /opt/oracle/product/11.2.0.3 System name: Linux Node name: db.test.com Release: 2.6.32-431.17.1.el6.x86_64 Version: #1 SMP Fri Apr 11 17:27:00 EDT 2014 Machine: x86_64 Instance name: test2 Redo thread mounted by this instance: 1 Oracle process number: 50 Unix process pid: 22448, image: oracle@db.test.com (J000)
*** 2017-01-21 23:00:07.961 *** SESSION ID:(70.53017) 2017-01-21 23:00:07.961 *** CLIENT ID:() 2017-01-21 23:00:07.961 *** SERVICE NAME:(SYS$USERS) 2017-01-21 23:00:07.961 *** MODULE NAME:(DBMS_SCHEDULER) 2017-01-21 23:00:07.961 *** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2017-01-21 23:00:07.961
ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB" ORA-06502: PL/SQL: numeric or value error ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073 ORA-06512: at line 1
Cause: This situation occur when table "DBSNMP.BSLN_BASELINES" contain inconsistent information. This table can be inconsistent when a database is created with a template from the old database or created as a clone from other database. The table is incontinent because existing records in the table "DBSNMP.BSLN_BASELINES" conflict with new baseline information inserted in the cloned database. Solution: This is a known bug, Please check MOSC note 1413756.1 for more information. This is a bug fixed in Oracle release 11.2.0.4 or for version 11.2.0.2 and 11.2.0.3 you may apply patch number 10110625. Workaround 1:
-- Login as sys user. Workaround 2: I would not recommend to follow this workaround as this workaroud has not been proven to work in different environment by Oracle. SQL> select * from DBSNMP.BSLN_BASELINES;
We can see there are two entries in the table. In this database, the current instance name is test1. So we need to remove the other entry instance_name test. SQL> DELETE FROM DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME= 'test';
SQL> select log_date,status from dba_scheduler_job_run_details where status='FAILED' order by log_date desc;
SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);
PL/SQL procedure successfully completed.
Now check again SQL> select log_date,status from dba_scheduler_job_run_details;
|