oracle11g 约束条件,ORACLE11g重建EM时报唯一约束错误

今天在手工配置oracle11g的EM时总是报如下错误：

WARNING: ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated

ORA-06512: at "SYSMAN.EMD_LOADER", line 4986

ORA-06512: at line 1

以Metalink上也没有找到解决办法，以下是我的解决过程：

1、数据库版本

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

CORE11.2.0.3.0Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 – Production

2、配置EM时的步骤

[oracle@Core-Nbu-Rollback ~]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Nov 23, 2012 3:52:08 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.All rights reserved.

Enter the following information:

Database SID: test

Database Control is already configured for the database test

You have chosen to configure Database Control for managing the database test

This will remove the existing configuration and the default settings and perform. a fresh configuration

----------------------------------------------------------------------

WARNING : While repository is dropped the database will be put in quiesce mode.

----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Listener ORACLE_HOME [ /soft/ora11g/11202 ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /soft/ora11g/11202

Local hostname ................ Core-Nbu-Rollback

Listener ORACLE_HOME ................ /soft/ora11g/11202

Listener port number ................ 1521

Database SID ................ test

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

----------------------------------------------------------------------

WARNING : While repository is dropped the database will be put in quiesce mode.

----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Nov 23, 2012 3:52:33 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /soft/ora11g/cfgtoollogs/emca/test/emca_2012_11_23_15_52_07.log.

Nov 23, 2012 3:52:35 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

Nov 23, 2012 3:52:56 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Nov 23, 2012 3:54:56 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Nov 23, 2012 3:54:57 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Nov 23, 2012 3:57:30 PM oracle.sysman.emcp.EMReposConfig createRepository

WARNING: ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated

ORA-06512: at "SYSMAN.EMD_LOADER", line 4986

ORA-06512: at line 1

Nov 23, 2012 3:57:30 PM oracle.sysman.emcp.EMReposConfig invoke

SEVERE: Error creating the repository

Nov 23, 2012 3:57:30 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Refer to the log file at /soft/ora11g/cfgtoollogs/emca/test/emca_repos_create_.log for more details.

Nov 23, 2012 3:57:30 PM oracle.sysman.emcp.EMConfig perform

SEVERE: Error creating the repository

Refer to the log file at /soft/ora11g/cfgtoollogs/emca/test/emca_2012_11_23_15_52_07.logfor more details.

Could not complete the configuration. Refer to the log file at /soft/ora11g/cfgtoollogs/emca/test/emca_2012_11_23_15_52_07.log for more details.

3、解决过程

3.1、查看详细报错日志

在目录/soft/ora11g/cfgtoollogs/emca/test下有以emca_repos_create开头的日志文件，查看有如下报错：

BEGIN EMD_LOADER.set_purge_batchsize (p_purge_batchsize => EMD_LOADER.p_purge_batchsize); END;

*

ERROR at line 1:

ORA-00001: unique constraint (SYSMAN.PARAMETERS_PRIMARY_KEY) violated

ORA-06512: at "SYSMAN.EMD_LOADER", line 4986

ORA-06512: at line 1

3.2、以SYSMAN登陆查看EMD_LOADER的4986行，内容如下：

MERGE

INTOmgmt_parameters p

USING(SELECTEM_PURGE_BATCHSIZE parameter_name, p_purge_batchsize parameter_valueFROMDUAL) p1

ON(p.parameter_name = p1.parameter_name)

WHENMATCHEDTHEN

UPDATE

SETp.parameter_value = p1.parameter_value

WHENNOTMATCHEDTHEN

INSERT(parameter_name, parameter_value, parameter_comment, internal_flag)

VALUES(p1.parameter_name, p1.parameter_value,'Purge batchsize',1) ;

从以上报错看应该是脚本往表mgmt_parameters表里插入数据时违反唯一约束导致的

之所以出现这样的问题我推断是由于ORACLE bug导致的，但是在METALINK上又没查到有用信息，所以只能是我们在重建em时手工把这个主键先临时disable掉。由于sysman及其所有对象都是在重建EM时自动创建的，所以这就需要我们要瞅准时机、及时的把约束disable掉，这个时间大概在重建时出现以下红色提示时即可使用sysman用户登陆把主键约束disable

[oracle@Core-Nbu-Rollback test]$ emca -config dbcontrol db -repos recreate

STARTED EMCA at Nov 23, 2012 4:26:24 PM

EM Configuration Assistant, Version 11.2.0.3.0 Production

Copyright (c) 2003, 2011, Oracle.All rights reserved.

Enter the following information:

Database SID: test

Database Control is already configured for the database test

You have chosen to configure Database Control for managing the database test

This will remove the existing configuration and the default settings and perform. a fresh configuration

----------------------------------------------------------------------

WARNING : While repository is dropped the database will be put in quiesce mode.

----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Listener ORACLE_HOME [ /soft/ora11g/11202 ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /soft/ora11g/11202

Local hostname ................ Core-Nbu-Rollback

Listener ORACLE_HOME ................ /soft/ora11g/11202

Listener port number ................ 1521

Database SID ................ test

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

----------------------------------------------------------------------

WARNING : While repository is dropped the database will be put in quiesce mode.

----------------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: y

Nov 23, 2012 4:26:40 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /soft/ora11g/cfgtoollogs/emca/test/emca_2012_11_23_16_26_23.log.

Nov 23, 2012 4:26:41 PM oracle.sysman.emcp.util.DBControlUtil stopOMS

INFO: Stopping Database Control (this may take a while) ...

Nov 23, 2012 4:26:44 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Nov 23, 2012 4:29:45 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Nov 23, 2012 4:29:46 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

3.3、成功重建后最好把mgmt_parameters中的重复数据删除，然后把主键重新enable