数据库巡检时发现，Oracle组件存在失效的状态。
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
-------------------- ---------------------------------------- ------------------------------ ----------------------
OWB OWB 11.2.0.4.0 VALID
APEX Oracle Application Express 3.2.1.00.12 INVALID
EM Oracle Enterprise Manager 11.2.0.4.0 VALID
AMD OLAP Catalog 11.2.0.4.0 VALID
SDO Spatial 11.2.0.4.0 INVALID
ORDIM Oracle Multimedia 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 INVALID
CONTEXT Oracle Text 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
RUL Oracle Rules Manager 11.2.0.4.0 VALID
OWM Oracle Workspace Manager 11.2.0.4.0 VALID
COMP_ID COMP_NAME VERSION STATUS
-------------------- ---------------------------------------- ------------------------------ ----------------------
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID
APS OLAP Analytic Workspace 11.2.0.4.0 VALID
XOQ Oracle OLAP API 11.2.0.4.0 INVALID
18 rows selected.
第一反应是使用utlrp.sql脚本编译一下，看看是否可以解决该问题。
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2024-01-26 23:31:23
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2024-01-26 23:31:29
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
Warning: XDB now invalid, invalid objects found:
object_name object_type
-------------------------------------------------------
DBMS_XDBT PACKAGE BODY
FAILED CHECK FOR PACKAGE BODY WWV_FLOW_GENERATE_DDL
PL/SQL procedure successfully completed.
执行以后，出现了报错且问题并没有得到解决。
为了查询组件失效的原因，首先排查了一下组件所属用户下是否存在失效对象。
select owner,object_name,object_type,status from dba_objects where owner in ('XDB','SYS','MDSYS','APEX_030200') and status='INVALID';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------------------ ------------------- -------
XDB DBMS_XDBT PACKAGE BODY INVALID
MDSYS SDO_OLS PACKAGE BODY INVALID
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY INVALID由于存在无效对象，首先把无效对象编译一下。
SQL> ALTER PACKAGE XDB.DBMS_XDBT COMPILE BODY;
ALTER PACKAGE mdsys.SDO_OLS COMPILE BODY;
ALTER PACKAGE APEX_030200.WWV_FLOW_GENERATE_DDL COMPILE BODY;
Warning: Package Body altered with compilation errors.
SQL>
Warning: Package Body altered with compilation errors.
SQL>
Warning: Package Body altered with compilation errors.
可以发现全都无法编译成功。此时来查询一下是什么原因导致编译失败的。
select owner,name,type,text from dba_errors;
OWNER NAME TYPE TEXT
------------------------------ ------------------------------ ------------ ----------------------------------------------------------------------------------------------------
XDB DBMS_XDBT PACKAGE BODY PLS-00201: identifier 'CTX_DOC' must be declared
XDB DBMS_XDBT PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00201: identifier 'UTL_HTTP' must be declared
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Item ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00201: identifier 'UTL_HTTP' must be declared
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Item ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00201: identifier 'UTL_HTTP' must be declared
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
MDSYS SDO_OLS PACKAGE BODY PLS-00320: the declaration of the type of this expression is incomplete or malformed
MDSYS SDO_OLS PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PLS-00201: identifier 'DBMS_METADATA' must be declared
APEX_030200 WWV_FLOW_GENERATE_DDL PACKAGE BODY PL/SQL: Statement ignored
可以发现报错的原因是由于 PLS-00201。这时我们来排查一下报错的其中一个对象是否正常。
SQL> select owner, object_name, object_type, status from dba_objects
2 where object_name = upper('&object_name');
Enter value for object_name: CTX_DOC
old 2: where object_name = upper('&object_name')
new 2: where object_name = upper('CTX_DOC')
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------- --
-----
PUBLIC CTX_DOC SYNONYM VALID
CTXSYS CTX_DOC PACKAGE BODY VALID
CTXSYS CTX_DOC PACKAGE VALID
SQL> select grantee, privilege from dba_tab_privs where table_name = upper('&object_name');
Enter value for object_name: CTX_DOC
old 1: select grantee, privilege from dba_tab_privs where table_name = upper('&object_name')
new 1: select grantee, privilege from dba_tab_privs where table_name = upper('CTX_DOC')
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
APEX_030200 EXECUTE
这时发现对象和同义词都是正常的，但是缺少执行的权限。此时只需要重新赋予权限并重新编译即可。
SQL> grant execute on CTXSYS.CTX_DOC to public;
Grant succeeded.
SQL> ALTER PACKAGE XDB.DBMS_XDBT COMPILE BODY;
Package body altered.
其他的失效对象由于是同样的原因，使用同样的修复办法即可。修复完成后重新执行utlrp.sql，Oracle组件失效的问题就顺利解决。
SQL> grant EXECUTE on sys.utl_http to public;
grant EXECUTE on CTXSYS.CTX_DOC to public;
grant EXECUTE on sys.DBMS_METADATA to public;
ALTER PACKAGE XDB.DBMS_XDBT COMPILE BODY;
ALTER PACKAGE mdsys.SDO_OLS COMPILE BODY;
ALTER PACKAGE APEX_030200.WWV_FLOW_GENERATE_DDL COMPILE BODY;
Grant succeeded.
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> SQL>
Package body altered.
SQL>
Package body altered.
SQL>
Package body altered.
SQL>
SQL>
SQL>
SQL>
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
-------------------- ---------------------------------------- ------------------------------ ----------------------
OWB OWB 11.2.0.4.0 VALID
APEX Oracle Application Express 3.2.1.00.12 INVALID
EM Oracle Enterprise Manager 11.2.0.4.0 VALID
AMD OLAP Catalog 11.2.0.4.0 VALID
SDO Spatial 11.2.0.4.0 INVALID
ORDIM Oracle Multimedia 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 INVALID
CONTEXT Oracle Text 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
RUL Oracle Rules Manager 11.2.0.4.0 VALID
OWM Oracle Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID
APS OLAP Analytic Workspace 11.2.0.4.0 VALID
XOQ Oracle OLAP API 11.2.0.4.0 INVALID
18 rows selected.
SQL>
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2024-01-27 00:02:55
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2024-01-27 00:02:56
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select comp_id,comp_name,version,status from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
-------------------- ---------------------------------------- ------------------------------ ----------------------
OWB OWB 11.2.0.4.0 VALID
APEX Oracle Application Express 3.2.1.00.12 VALID
EM Oracle Enterprise Manager 11.2.0.4.0 VALID
AMD OLAP Catalog 11.2.0.4.0 VALID
SDO Spatial 11.2.0.4.0 VALID
ORDIM Oracle Multimedia 11.2.0.4.0 VALID
XDB Oracle XML Database 11.2.0.4.0 VALID
CONTEXT Oracle Text 11.2.0.4.0 VALID
EXF Oracle Expression Filter 11.2.0.4.0 VALID
RUL Oracle Rules Manager 11.2.0.4.0 VALID
OWM Oracle Workspace Manager 11.2.0.4.0 VALID
CATALOG Oracle Database Catalog Views 11.2.0.4.0 VALID
CATPROC Oracle Database Packages and Types 11.2.0.4.0 VALID
JAVAVM JServer JAVA Virtual Machine 11.2.0.4.0 VALID
XML Oracle XDK 11.2.0.4.0 VALID
CATJAVA Oracle Database Java Packages 11.2.0.4.0 VALID
APS OLAP Analytic Workspace 11.2.0.4.0 VALID
XOQ Oracle OLAP API 11.2.0.4.0 VALID
18 rows selected.
SQL>
参考文档
Spatial Is Invalid And There Are Invalid MDSYS Package Bodies Where The Compilation Raises PLS-201 errors (文档 ID 1476866.1)
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
