EM Recreate and PLS-00302: component 'VALID' must be declared
公司进行domain的migration,给我的生活工作带来诸多不便，相当杯具:( 先不说email用不了，收不到任何邮件，搞得心慌慌，而且今天尝试访问下EM，居然发现搞不定了！
因为用的是windows操作系统，首先想到去启动“OracleDBConsoleorcl"服务，但是发现根本启动不起来。后来发现因为进行域迁移，电脑的名字也给改了，因此首先就导致listener不work了，这个好办，直接打开listener.ora文件将Host名字给改过来，这样listner就可以正常work了。因为em dbconsole依赖一个oc4j的repository,一般名字也是跟host名字相关的，所以很显然原来的repository也用不了了，因此需要重新创建以下。
通过命令 emca -config dbcontrol db -repos recreate来创建新的repository, 如下
但是杯具的是，在创建repository的时候出现问题了！找到对应的log文件查看下，发现如下一些错误信息...
INFO: Creating the EM repository (this may take a while) ...Dec 15, 2010 4:12:57 PM oracle.sysman.emcp.EMReposConfig createRepositoryCONFIG: ORA-06550: line 1, column 21:PLS-00302: component 'VALID' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignored
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-06550: line 1, column 21:PLS-00302: component 'VALID' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignored
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1474)at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:848)at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:267)at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:308)at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:422)at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:192)at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:134)at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:171)at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:419)
oracle.sysman.assistants.util.sqlEngine.SQLFatalErrorException: ORA-06550: line 1, column 21:PLS-00302: component 'VALID' must be declaredORA-06550: line 1, column 7:PL/SQL: Statement ignored
at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeImpl(SQLEngine.java:1474)at oracle.sysman.assistants.util.sqlEngine.SQLEngine.executeScript(SQLEngine.java:848)at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:267)at oracle.sysman.assistants.util.sqlEngine.SQLPlusEngine.executeScript(SQLPlusEngine.java:308)at oracle.sysman.emcp.EMReposConfig.createRepository(EMReposConfig.java:422)at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:192)at oracle.sysman.emcp.EMReposConfig.invoke(EMReposConfig.java:134)at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:171)at oracle.sysman.emcp.EMConfigAssistant.invokeEMCA(EMConfigAssistant.java:486)at oracle.sysman.emcp.EMConfigAssistant.performConfiguration(EMConfigAssistant.java:1142)at oracle.sysman.emcp.EMConfigAssistant.statusMain(EMConfigAssistant.java:470)at oracle.sysman.emcp.EMConfigAssistant.main(EMConfigAssistant.java:419)
注意错误信息中给出VALID这个东西找不到，可以猜测一下应该是调用某个procedure （VALID），发现不存在造成的。那么究竟是什么东东呢？ google了一下，发现有人提到dbms_registry.valid('EM')。 难倒是这个VALID??
首先想到是不是dbms_registry不是默认安装的，需要手动安装一下呢？
先describe了一下dbms_registry看看，
SQL> desc dbms_registry
PROCEDURE CHECK_SERVER_INSTANCE
PROCEDURE LOADED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_VERSION VARCHAR2 IN DEFAULT
COMP_BANNER VARCHAR2 IN DEFAULT
PROCEDURE LOADING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_NAME VARCHAR2 IN
COMP_PROC VARCHAR2 IN DEFAULT
COMP_SCHEMA VARCHAR2 IN DEFAULT
COMP_PARENT VARCHAR2 IN DEFAULT
FUNCTION TIME_STAMP RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
SQL>
奇怪，dbms_registry是存在的，不过可以看到是不存在VALID这个procedure的！难倒猜错了？不甘心又google了一下，发现网上有介绍package dbms_registry里面确实是存在VALID这个procedure的！这真是太诡异了！
查看了下Oracle安装目录下的脚本（?\RDBMS\ADMIN), 发现有两个文件中涉及到这个package的创建，
1） catclust.sql
2) prvtcr.plb
这两者的区别是第一个可以看到源代码，第二个因为package是wrapped的，看不到具体信息。
catclust.sql中dbms_registry部分代码如下，
CREATE OR REPLACE PACKAGE dbms_registry AS
PROCEDURE loading (comp_id IN VARCHAR2,
comp_name IN VARCHAR2,
comp_proc IN VARCHAR2 DEFAULT NULL,
comp_schema IN VARCHAR2 DEFAULT NULL,
comp_parent IN VARCHAR2 DEFAULT NULL);
PROCEDURE loaded (comp_id IN VARCHAR2,
comp_version IN VARCHAR2 DEFAULT NULL,
comp_banner IN VARCHAR2 DEFAULT NULL);
FUNCTION time_stamp (comp_id IN VARCHAR2) RETURN VARCHAR2;
PROCEDURE check_server_instance;
END dbms_registry;
/
这个跟我上面的describe得到的内容很吻合，那么 prvtcr.plb这里面又是什么东东呢？ 索性一不做二不休，直接运行下这个脚本看看，
SQL> @?\rdbms\admin\prvtcr.plb
完了之后再describe一下看看，
SQL> desc dbms_registry;
PROCEDURE CHECK_SERVER_INSTANCE
FUNCTION COMP_NAME RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
PROCEDURE DOWNGRADED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
OLD_VERSION VARCHAR2 IN
PROCEDURE DOWNGRADING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
OLD_NAME VARCHAR2 IN DEFAULT
OLD_PROC VARCHAR2 IN DEFAULT
OLD_SCHEMA VARCHAR2 IN DEFAULT
OLD_PARENT VARCHAR2 IN DEFAULT
PROCEDURE INVALID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION IS_COMPONENT RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION IS_IN_REGISTRY RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION IS_LOADED RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
VERSION VARCHAR2 IN DEFAULT
FUNCTION IS_STARTUP_REQUIRED RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION IS_VALID RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
VERSION VARCHAR2 IN DEFAULT
PROCEDURE LOADED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_VERSION VARCHAR2 IN DEFAULT
COMP_BANNER VARCHAR2 IN DEFAULT
PROCEDURE LOADING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_NAME VARCHAR2 IN
COMP_PROC VARCHAR2 IN DEFAULT
COMP_SCHEMA VARCHAR2 IN DEFAULT
COMP_PARENT VARCHAR2 IN DEFAULT
PROCEDURE LOADING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_NAME VARCHAR2 IN
COMP_PROC VARCHAR2 IN
COMP_SCHEMA VARCHAR2 IN
COMP_SCHEMAS DBMS_REGISTRY IN
COMP_PARENT VARCHAR2 IN DEFAULT
FUNCTION NOTHING_SCRIPT RETURNS VARCHAR2
FUNCTION PREV_VERSION RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
PROCEDURE REMOVED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
PROCEDURE REMOVING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
PROCEDURE RESET_VERSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION SCHEMA RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION SCHEMA_LIST RETURNS DBMS_REGISTRY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION SCHEMA_LIST_STRING RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION SCRIPT RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
SCRIPT_NAME VARCHAR2 IN
FUNCTION SCRIPT_PATH RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION SCRIPT_PREFIX RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION SESSION_NAMESPACE RETURNS VARCHAR2
PROCEDURE SET_COMP_NAMESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
NAMESPACE VARCHAR2 IN
PROCEDURE SET_SESSION_NAMESPACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMESPACE VARCHAR2 IN
PROCEDURE STARTUP_COMPLETE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
PROCEDURE STARTUP_REQUIRED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION STATUS RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION STATUS_NAME RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
STATUS NUMBER IN
FUNCTION SUBCOMPONENTS RETURNS TABLE OF VARCHAR2(30)
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_OPTION NUMBER IN DEFAULT
PROCEDURE UPDATE_SCHEMA_LIST
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
COMP_SCHEMAS DBMS_REGISTRY IN
PROCEDURE UPGRADED
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
NEW_VERSION VARCHAR2 IN DEFAULT
NEW_BANNER VARCHAR2 IN DEFAULT
PROCEDURE UPGRADING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
NEW_NAME VARCHAR2 IN DEFAULT
NEW_PROC VARCHAR2 IN DEFAULT
NEW_SCHEMA VARCHAR2 IN DEFAULT
NEW_PARENT VARCHAR2 IN DEFAULT
PROCEDURE UPGRADING
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
NEW_NAME VARCHAR2 IN
NEW_PROC VARCHAR2 IN
NEW_SCHEMA VARCHAR2 IN
NEW_SCHEMAS DBMS_REGISTRY IN
NEW_PARENT VARCHAR2 IN DEFAULT
PROCEDURE VALID
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
FUNCTION VERSION RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMP_ID VARCHAR2 IN
可以看到这次dbms_registy里面包含了VALID这个procedure。跟上面的区别除了多了些procedure, 也少了一个TIME_STAMP function.
注意到catclust.sql文件中的相关注释，
Rem -------------------------------------------------------------------------
Rem DBMS REGISTRY PACKAGE - minimal version for loading CATALOG
Rem -------------------------------------------------------------------------
可以猜测默认安装的dbms_registry是个精简版本，复杂版本的就没有暴漏源代码。
这个问题解决之后，重新创建下dbconsole repository, 发现成功了！
可以发现现在的em URL的地址也改成了新的host名字！
-- 总结--
这件事给我的最大启示就是遇到不知道原因的时候，要大胆进行猜测尝试，这样往往会有意想不到的结果！
--------------------------------------
Regards,
FangwenYu
