oracle数据库组件不可用,oracle 手动升级后组件状态为INVALID解决办法

After upgrade CATPROC and CATALOG comps are INVALID

报错信息：

oracle 10.2.0.1升级到10.2.0.5

运行catupgrd.sql后

racle Database 10.2 Upgrade Status Utility

racle Database 10.2 Upgrade Status Utility           03-16-2011 09:39:53

.

Component                                              Status         Version  HH:MM:SS

Oracle Database Server                          VALID      10.2.0.5.0  00:07:47

JServer JAVA Virtual Machine                 VALID      10.2.0.5.0  00:02:19

Oracle XDK                                                 VALID      10.2.0.5.0  00:00:40

Oracle Database Java Packages         INVALID      10.2.0.5.0  00:00:54

Oracle Text                                                  VALID      10.2.0.5.0  00:00:33

Oracle XML Database                               VALID      10.2.0.5.0  00:01:35

Oracle Real Application Clusters           VALID      10.2.0.5.0  00:00:02

Oracle Workspace Manager                    VALID      10.2.0.5.0  00:00:04

Oracle Data Mining                                    VALID      10.2.0.5.0  00:00:33

OLAP Analytic Workspace                        VALID      10.2.0.5.0  00:00:19

OLAP Catalog                                             VALID      10.2.0.5.0  00:00:56

Oracle OLAP API                                        VALID      10.2.0.5.0  00:01:45

Oracle interMedia                                      VALID      10.2.0.5.0  00:05:34

Oracle Expression Filter                           VALID      10.2.0.5.0  00:00:54

Spatial                                                          VALID      10.2.0.5.0  00:00:41

Oracle Rule Manager                                VALID      10.2.0.5.0  00:00:05

Oracle Enterprise Manager                      VALID      10.2.0.5.0  00:00:07

查了下metalink文档，有人碰到个这个，原来是FIXED_DATE这个参数设置不正确引起的，将FIXED_DATE设置为none,再次执行

升级脚本就可以了，oracle给出具体步骤为：

1.使用以下脚本验证那些无效对象对数据库组件状态有影响

spool /oracle/admin/obj_list.out

set serveroutput on

declare

start_time date;

end_time date;

object_name varchar(100);

object_id char(10);

c_time date;

s_time date;

m_time date ;

CURSOR C1 IS SELECT obj#,name

FROM obj$

WHERE status > 1 AND

( ctime BETWEEN start_time AND end_time OR

mtime BETWEEN start_time AND end_time OR

stime BETWEEN start_time AND end_time);

begin

SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';

open c1;

fetch c1 into object_id, object_name ;

while c1%FOUND loop

dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '|| object_id);

fetch c1 into object_id,object_name;

end loop;

close c1;

EXCEPTION

WHEN NO_DATA_FOUND THEN dbms_output.put_line('CATPROC can be validated now' );

end;

/

spool off

2.修改fixed_date参数值为none

SQL> ALTER SYSTEM SET FIXED_DATE=NONE SCOPE=BOTH;

3.关闭数据库，然后重新将数据库启动到升级模式

SQL>shutdown immediate

SQL>startup upgrade

4.重新运行升级脚本

SQL>@?/rdbms/admin/catupgrd.sql

运行完成后显示所有组件都升级到10.2.0.5,状态都为valid

5.重新启动数据，编译无效对象

SQL>shutdown immediate

SQL>@?/rdbms/admin/utlrp.sql

6.验证升级

SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry order by modified;

Component                                                Status      Version

Oracle Database Server                          VALID      10.2.0.5.0

JServer JAVA Virtual Machine                 VALID      10.2.0.5.0

Oracle XDK                                                 VALID      10.2.0.5.0

Oracle Database Java Packages         VALID      10.2.0.5.0

Oracle Text                                                  VALID      10.2.0.5.0

Oracle XML Database                               VALID      10.2.0.5.0

Oracle Real Application Clusters           VALID      10.2.0.5.0

Oracle Workspace Manager                    VALID      10.2.0.5.0

Oracle Data Mining                                    VALID      10.2.0.5.0

OLAP Analytic Workspace                        VALID      10.2.0.5.0

OLAP Catalog                                             VALID      10.2.0.5.0

Oracle OLAP API                                        VALID      10.2.0.5.0

Oracle interMedia                                      VALID      10.2.0.5.0

Oracle Expression Filter                           VALID      10.2.0.5.0

Spatial                                                          VALID      10.2.0.5.0

Oracle Rule Manager                                VALID      10.2.0.5.0

Oracle Enterprise Manager                      VALID      10.2.0.5.0

OK。

metalink 原文：

After upgrade CATPROC and CATALOG comps are INVALID even if only user invalid objects are found [ID 745183.1]

In this Document

Symptoms

Cause

Solution

References

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1

Information in this document applies to any platform.

***Checked for relevance on 25-Jul-2010***

Symptoms

After upgrade from 9.2.0.8 to 10.2.0.4 there are invalid components under dba_registry:

Oracle Database 10.2 Upgrade Status Utility 09-17-2008 18:01:00

Component Status Version

Oracle Database Server INVALID 10.2.0.4.0

JServer JAVA Virtual Machine VALID 10.2.0.4.0

Oracle XDK VALID 10.2.0.4.0

Oracle Database Java Packages INVALID 10.2.0.4.0

Oracle XML Database VALID 10.2.0.4.0

Oracle Workspace Manager VALID 10.2.0.4.3

No critical errors have been found in the spool file for the run of the upgrade script.

No SYS/SYSTEM invalid objects have been found.

The select which indicates the invalid objects that caused the invalidation of the CATALOG or CATPROC return

only user invalid objects.

Cause

The parameter FIXED_DATE was set before the upgrade and the upgrade was done with this parameter set.

Because the FIXED_DATE parameter is used the SYSDATE will return only the date fixed by the parameter. During

the upgrade the registry$ table columns 'date_loading' and 'date_loaded' are updated using SYSDATE.

Also after the upgrade, the run of utlrp.sql modifies the 'mtime' column of obj$ and updates it using

SYSDATE.

This issue causes the invalidation of the CATPROC and CATALOG database components, since the user objects

will match the select statement criteria, used to collect the invalid objects, that influence the status of

the CATALOG and CATPROC components.

Solution

To implement the solution, please execute the following steps:

1. use the below script. to verify which invalid objects affect the status of the CATALOG and CATPROC database

components

spool /oracle/admin/obj_list.out

set serveroutput on

declare

start_time date;

end_time date;

object_name varchar(100);

object_id char(10);

c_time date;

s_time date;

m_time date ;

CURSOR C1 IS SELECT obj#,name

FROM obj$

WHERE status > 1 AND

( ctime BETWEEN start_time AND end_time OR

mtime BETWEEN start_time AND end_time OR

stime BETWEEN start_time AND end_time);

begin

SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';

open c1;

fetch c1 into object_id, object_name ;

while c1%FOUND loop

dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '|| object_id);

fetch c1 into object_id,object_name;

end loop;

close c1;

EXCEPTION

WHEN NO_DATA_FOUND THEN dbms_output.put_line('CATPROC can be validated now' );

end;

/

spool off

2. verify and remove the parameter fixed_date from your init.ora file or database:

SQL> ALTER SYSTEM SET FIXED_DATE=NONE SCOPE=BOTH;

3. bounce the database

4. rerun the upgrade script. (catupgrd.sql)

5. validate all the invalid objects using utlrp.sql script

6. verify the status of the dba_registry:

SQL> set pagesize 999

SQL> column status format a15

SQL> column version format a15

SQL> column comp_name format a35

SQL> SELECT comp_name, status, substr(version,1,10) as version from dba_registry order by modified

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24668589/viewspace-689950/，如需转载，请注明出处，否则将追究法律责任。