ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1" 错误处理

今天，在alert log日志中发现如下错误。

Mon Apr 28 22:00:05 2014
Errors in file /u01/app/oracle/diag/rdbms/csdb/csdb1/trace/csdb1_j004_29347.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1

版本：

Newly installed 11.2.0.3 database, or recently upgraded to 11.2.0.3 from an earlier release.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

[oracle@rac1 ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.3 (Santiago)

原因：

Newer database releases are automatically instrumented for OCM collections. But here, the OCM instrumentation job is trying to write to a "state" directory which doesn't yet exists.MGMT_DB_LL_METRICS wants to write to ORACLE_OCM_CONFIG_DIR2, which is not created by the built-in instrumentation scripts.

SQL> select * from dba_directories where DIRECTORY_NAME like '%OCM_CONFIG%';

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS                            ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/db_1/ccr/state

解决方法：
A. 运行脚本重新配置OCM
1)在环境变量中添加如下内容

export ORACLE_CONFIG_HOME=$ORACLE_HOME

2)运行配置脚本

-->由于脚本installCCRSQL不存在，需要先运行setupCCR脚本
[oracle@rac1 ~]$ cd $ORACLE_CONFIG_HOME/ccr/admin/scripts
-bash: cd: /u01/app/oracle/product/11.2.0/db_1/ccr/admin/scripts: No such file or directory
[oracle@rac1 bin]$ pwd
/u01/app/oracle/product/11.2.0/db_1/ccr/bin
[oracle@rac1 bin]$ ./setupCCR 

-->由于我的系统无法联网，导致配置无法成功
[oracle@rac1 bin]$ ./setupCCR 
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  
Email address/User Name: 

You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y
Oracle Configuration Manager has been installed but not configured. OCM enables
Oracle to provide superior, proactive support for our customers. Oracle
strongly recommends customers configure OCM. To complete the configuration of
OCM, refer to the OCM Installation and Administration Guide
(http://www.oracle.com/technology/documentation/ocm.html).

-->下面只是我的模拟过程了
[oracle@rac1 bin]$ $ORACLE_HOME/ccr/admin/scripts/installCCRSQL.sh collectconfig  -s csdb1 -r oracle
-->参考文档，可以查看
http://docs.oracle.com/cd/E28601_01/doc.1037/e26167/install.htm#CBHFFGFJ

B.删除oracle_ocm账户或者停掉相关job
1）停止相关job

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB')

PL/SQL procedure successfully completed.

2)直接删除oracle_ocm账户

SQL> drop user oracle_ocm cascade;

User dropped.

参考文档

"ORA-12012: error on auto execute of job ORACLE_OCM.MGMT_CONFIG_JOB_2_1" And "ORA-29280: invalid directory path" In Database AlertLog (文档 ID 1453959.1)

In this Document
Symptoms
Changes
Cause
Solution
References

APPLIES TO:

Oracle Configuration Manager - Version 10.3.3.0.0 and later
Information in this document applies to any platform.
SYMPTOMS

Following error is reported every 24 hours in the alertLog file of a 11.2.0.3 database:

Errors in file /opt/oracle/diag/rdbms/db/trace/db_j001_26027.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1

CHANGES

Newly installed 11.2.0.3 database, or recently upgraded to 11.2.0.3 from an earlier release.

CAUSE

Newer database releases are automatically instrumented for OCM collections. But here, the OCM instrumentation job is trying to write to a "state" directory which doesn't yet exists.

To verify the OCM db directories, run as sysdba:
SQL>select * from dba_directories where DIRECTORY_NAME like '%OCM_CONFIG%';

MGMT_DB_LL_METRICS wants to write to ORACLE_OCM_CONFIG_DIR2, which is not created by the built-in instrumentation scripts.

SOLUTION

Problem is fixed with OCM Collector version 10.3.7

A. But out-of-box the 11.2 database includes earlier OCM version, therefore one must re-configure database for OCM collections by running the "ORACLE_HOME/ccr/admin/scripts/installCCRSQL" script. If script is missing, first configure OCM by running "ORACLE_HOME/ccr/bin/setupCCR".
Pay attention when on RAC database, with a non-shared file system environment, must set the ORACLE_CONFIG_HOME environment variable to $ORACLE_HOME prior to running the installCCRSQL.sh script. Reference: Oracle Configuration Manager Installation and Administration Guide

or

B. when not using the OCM Collector, remove the OCM objects from database:
SQL> drop user ORACLE_OCM cascade;
OCM is a standalone, separate tool and removing won't affect the database functionality.

Or could simply disable the OCM jobs by running:

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB')
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB')

REFERENCES


NOTE:859113.1 - How To Manually Remove ORACLE_OCM From Database

http://docs.oracle.com/cd/E28601_01/doc.1037/e26167/install.htm#CBHFFGFJ

This step is only needed if you are installing a version of Oracle Configuration Manager into a database release home that is prior to release 10.2.0.4 or if Oracle Configuration Manager is updated in the database home. As of Oracle Database release 10.2.0.4, the database is automatically instrumented.

To configure the database for configuration collection, perform the following steps:

Run the following script which will prompt for the SYSDBA-USER password:

<ocm_install_root>/ccr/admin/scripts/installCCRSQL.sh collectconfig -s <SID> -r <SYSDBA-USER>
The installCCRSQL.sh (UNIX) or installCCRSQL.exe (Windows) script creates an Oracle Configuration Manager user and loads the PL/SQL procedure into the database defined by the ORACLE_SID. You can also specify the database <SID> by using the -s option in the command line as in the following example where the <SID> is orcl:

<ocm_install_root>/ccr/admin/scripts/installCCRSQL.sh collectconfig -s orcl
By default, the connection to the database is through OS authentication, "/as sysdba." To specify a different SYSDBA user and password, you can use this option:

-r <SYSDBA-USER>: The login name of the SYSDBA user

You will be prompted for the corresponding password.

For Oracle Real Application Cluster (RAC) databases, you must run the database script against only one instance, but Oracle Configuration Manager must be installed in all instance homes. Oracle Configuration Manager does not support active/passive clusters.

Note:
If the Oracle Configuration Manager account already exists, when you run the installCCRSQL.sh script, it will be dropped and re-created.

If you upgrade from a 9.x database version to a 10.x version after the Oracle Configuration Manager installation, you must run the installCCRSQL.sh script again to instrument the upgraded version.

In a RAC configuration with a non-shared file system environment, it is possible to alter Oracle Configuration Manager configuration and installation such that the dbms_job output is not sent to a host-specific directory:

Set the ORACLE_CONFIG_HOME environment variable to $ORACLE_HOME prior to running the installCCRSQL.sh script. This causes the dbms_job output to go to the $ORACLE_HOME/ccr/state directory. The discovery scripts for databases account for this already, so the database and RAC database targets will be discovered and have their configuration information collected.

OCM是一个独立的工具，删除以后不会影响数据库其他功能的使用。因此可以采用停止job的方式来停止OCM的功能。