DBSNMP.BSLN_INTERNAL invalid ORA-12012 ORA-04063

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
ORA-04063: package body "DBSNMP.BSLN_INTERNAL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DBSNMP.BSLN_INTERNAL"
ORA-06512: at line 1

Tested fix on Oracle version  11.2.0.3.7


I've been getting the error in the alert log since the database was built using an rman clone.

I could force the error by running:

As sys:
     exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

     select log_date,status from dba_scheduler_job_run_details
     where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date;

I tried the recommended work around from Oracle to recreate the dbsnmp user:

@?/rdbms/admin/catnsnmp.sql
@?/rdbms/admin/catsnmp.sql

This did not fix it.

Oracle recommended I apply a patch for bug 13637859

This did not fix it.

I rolled back the patch, restored dbsnmp as it was and started a rethink.  There were 2 distinct problems happening:


The first one is that the package DBSNMP.BSLN_INTERNAL is invalid:

select substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type,
 status from dba_objects where object='BSLN_INTERNAL';

DBSNMP BSLN_INTERNAL PACKAGE BODY INVALID
   
alter package DBSNMP.BSLN_INTERNAL compile;

Warning: Package altered with compilation errors.

select * from dba_errors where OWNER='DBSNMP';

DBSNMP   BSLN_INTERNAL    PACKAGE BODY   1       1910          7
PLS-00201: identifier 'DBMS_JOB' must be declared
ERROR                201

DBSNMP  BSLN_INTERNAL PACKAGE BODY   2       1910          7
PL/SQL: Statement ignored
ERROR                  0

So DBMS_JOB has a problem, well execute was revoked from public on DBMS_JOB on this database.

grant execute on sys.dbms_job to dbsnmp;
alter package DBSNMP.BSLN_INTERNAL compile;
revoke execute on sys.dbms_job from dbsnmp;

select substr(owner,1,12) owner, substr(object_name,1,30) object, substr(object_type,1,30) type,
 status from dba_objects where object_name='BSLN_INTERNAL';

DBSNMP BSLN_INTERNAL PACKAGE BODY VALID
   

     exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

     select log_date,status from dba_scheduler_job_run_details
     where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date;

Job Failed

So I have a valid package now but my job is still failing.  If your database was not cloned you might find this grant and recompile will fix your problem. (The revoke of the privilege again afterwards was fine, it seemed to only need it to recompile, not to actually run)

My second issue was caused when cloning a new db using rman.

select * from DBSNMP.BSLN_BASELINES;
nnnnnnnn OLDSOURCEDBNAME  0 xxxxxxxxxxxxxxxxxxxx Y ACTIVE dd/mm/yyyy

There was only one row in my table and that had the name of the old database in it, I removed it:

delete from DBSNMP.BSLN_BASELINES WHERE INSTANCE_NAME='OLDSOURCEDBNAME ';
commit;

exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);
select log_date,status from dba_scheduler_job_run_details
     where job_name='BSLN_MAINTAIN_STATS_JOB' order by log_date;
succeeded

This removal of one line is going to be how the Oracle recommended work around for this problem fixes it, you should probably use the Oracle way not my hack.  But for me Oracle Enterprise Grid/Cloud/OEM/yet another name change for OEM used DBSNMP and I didn't want to upset it on my production system, which is why I deleted the row rather than doing:

@?/rdbms/admin/catnsnmp.sql
@?/rdbms/admin/catsnmp.sql

I found at least 3 blogs with pieces of all this information in but none with it together that solved my issue.

Remember here we have considered:

• An invalid package caused by revoking execute on DBMS_JOB fixed by granting execute directly to DBSNMP.
• An entry in DBSNMP.BSLN_BASELINES table carried over from an old source database on clone build.
• The Oracle Workaround covered in Oracle Support Doc ID 1413756.1 ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"
• An Oracle Patch for Bug 13637859

Please speak to Oracle Support if you are in anyway unsure that you have the same issue and always make sure you have a good backup before you change anything.