Symptoms:
BSLN_MAINTAIN_STATS_JOB fails with the following error:
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
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.
SQL> sqlplus / as sysdba
-- From the sqlplus execute the following:
-- Drop the DBSNMP user by executing catnsnmp.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql
-- Create the DBSNMP user by executing catsnmp.sql
SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql
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';
1 row deleted.
SQL> commit;
Commit complete.
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;