2011-03-23

package body "ORACLE_OCM.MGMT_DB_LL_METRICS" has errors

Invalid Objects After Revoking EXECUTE Privilege From PUBLIC [ID 797706.1]

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.
***Checked for relevance on 06-Oct-2010***

Symptoms

OEM recommends that EXECUTE privilege being revoked from PUBLIC. After revoking the privilege, the  following errors appeared in alert log:
ORA-12012: error on auto execute of job 66171
ORA-04063: package body "ORACLE_OCM.MGMT_DB_LL_METRICS" has errors
ORA-06508: PL/SQL: could not find program unit being called:  ORACLE_OCM.MGMT_DB_LL_METRICS"
ORA-06512: at line 1 has errors .
Also, the below query returns invalid rows count (approximately 46 rows)
SQL> select object_name, owner, object_type, status from dba_objects where status = 'INVALID';
Owners of invalid objects are ORACLE_OCM and WMSYS 

Cause

At the time of installation of the database, Oracle execute scripts that test to see if PUBLIC access is granted to all of the routines  that ORACLE_OCM and WMSYS need access to. If PUBLIC access is NOT available, Oracle scripts grant specific access rights. However, If EXECUTE privilege is revoked from PUBLIC after installation, then those specific access rights needs to be granted manually.

Solution

You will need to grant execute on UTL_FILE and DBMS_SCHEDULER to ORACLE_OCM and WMSYS. The below action plan should solve the issue:
SQL> grant execute on UTL_FILE to oracle_ocm;
SQL> grant execute on DBMS_SCHEDULER to oracle_ocm;
SQL> grant execute on UTL_FILE to wmsys;
SQL> grant execute on DBMS_SCHEDULER to wmsys;
SQL> shutdown immediate;
SQL> startup restrict;
SQL> @utlrp.sql /* $ORACLE_HOME/rdbms/admin/@utlrp.sql */
SQL> shutdown immediate;
SQL> startup;

Niciun comentariu:

Trimiteți un comentariu