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

 

#############  upgrade to 10.2.0.4

Before running the CATUPGRD.SQL , describe the PLAN_TABLE in the SYS schema level.
If you have OTHER_XML column in PLAN_TABLE means , this error wont come.
Otherwise, this error will come when u run the catupgrd.sql for 10.2.0.4.0 patch.

Customers upgrading to 10.2.0.4 or 11.1.0.7 will encounter the bug only and only if they are upgrading a pre-10g (e.g., 9i or 8) db where admin/utlxplan.sql was explicitly run by/as SYS to create a (user) plan table in the SYS schema. I believe this is very rare as DBA won't use the SYS schema to do explain plan of user/application queries.

Customers upgrading to 10.2.0.2, 10.2.0.3, or 11.1.0.6 won't encounter this bug during upgrade because for those releases SQL Performance Analyzer exists in the format of a one-off, which is not run as part of upgrade.
dbms_sqlpa is a package owned by SYS that has a static SQL statement that queries the plan table which is expected to contain the other_xml column. This is why we get an error if SYS.plan_table does not have this column. The other_xml column was introduced in 10.2.0.1 and added to the temporary global plan table plan_table$. plan_table$ was introduced in 10.1 as a replacement to table plan_table which is used to store the result of explain plan command. plan_table has become a public synonym to plan_table$.

Workaround is:
-------------------------

SQL>sqlplus / as sysdba
SQL>drop table plan_table;
SQL>@?/rdbms/admin/utlxplan
SQL>@?/rdbms/admin/prvtspao.plb
SQL>@?/rdbms/admin/utlrp.sql

This will resolve your issue.

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

转载于:http://blog.itpub.net/611609/viewspace-690086/