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

Symptoms:

BSLN_MAINTAIN_STATS_JOB fails with the following error:

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;