升级后Oracle Database Server invalid

操作系统版本：
redhat 4update 7 64bit

9.2.0.4升级到10.2.0.4
升级过程中遇到的问题：
运行升级脚本：
SQL> spool /home/oracle/zhf/upgrade.log
SQL>@?/rdbms/admin/catupgrd.sql
Spool off;

升级脚本报错：
118/44   PL/SQL: ORA-00904: "OTHER_XML": invalid identifier

--查看组件状态
SQL> @?/rdbms/admin/utlu102s.sql TEXT
.
Oracle Database 10.2 Upgrade Status Utility           05-26-2012 10:47:36
.
Component                                Status         Version  HH:MM:SS
Oracle Database Server                  INVALID      10.2.0.4.0  00:06:46
Oracle Workspace Manager                  VALID      10.2.0.4.3  00:00:29
.
Total Upgrade Time: 00:07:40

PL/SQL procedure successfully completed.

SQL> select comp_name, status, version from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
STATUS                 VERSION
---------------------- ------------------------------
Oracle Workspace Manager
VALID                  10.2.0.4.3

Oracle Database Catalog Views
VALID                  10.2.0.4.0

Oracle Database Packages and Types
INVALID                10.2.0.4.0
查阅metalink文档
这个报错,是由于数据库在升级过程中编译DBMS_SQLPA包时遇错

问题原因：
由于以下对象的存在，导致这个报错：
- table SYS.PLAN_TABLE$
- public synonym PLAN_TABLE for the SYS.PLAN_TABLE$.
- and possibly (not always present) table SYS.PLAN_TABLE

为避免这个问题，需要在升级前报这些对象drop

解决方法：
sqlplus /"as sysdba"

column object_name format a25
select object_name, object_type, owner
 from dba_objects
where object_name like '%PLAN_TABLE%';

SQL> drop table plan_table$;

SQL> drop public synonym plan_table;

drop table plan_table;

--重建plan_table:
$ sqlplus '/ as sysdba'
SQL> @?/rdbms/admin/catplan.sql  -- creates a public plan table as a global temporary table accessible from any schema
SQL> @?/rdbms/admin/dbmsxpln.sql -- reload dbms_xplan spec
SQL> @?/rdbms/admin/prvtxpln.plb -- reload dbms_xplan implementation
SQL> @?/rdbms/admin/prvtspa.plb  -- reload dbms_sqlpa (Note use prvtspao.plb if prvtspa.plb is not present).

SQL> column object_name format a25
SQL> select object_name, object_type, owner
  2   from dba_objects
  3  where object_name like '%PLAN_TABLE%';

OBJECT_NAME               OBJECT_TYPE         OWNER
------------------------- ------------------- ------------------------------
SQL_PLAN_TABLE_TYPE       TYPE                SYS
SQL_PLAN_TABLE_TYPE       SYNONYM             PUBLIC
PLAN_TABLE                SYNONYM             PUBLIC
PLAN_TABLE$               TABLE               SYS

重编译无效对象：
@?/rdbms/admin/utlrp.sql
检查数据库组件有效性：
@?/rdbms/admin/utlu102s.sql TEXT
Oracle Database Server             仍显示     INVALID

DBA_registry已显示组件有效：

SQL> select comp_name, status, version from dba_registry;

COMP_NAME
------------------------------------------------------------------------------------------------------------------------------------
STATUS                 VERSION
---------------------- ------------------------------
Oracle Workspace Manager
VALID                  10.2.0.4.3

Oracle Database Catalog Views
VALID                  10.2.0.4.0

Oracle Database Packages and Types
VALID                  10.2.0.4.0

为保险起见，在重新跑一遍升级脚本：
shutdown immediate;
startup upgrade;
SQL> spool /home/oracle/zhf/upgrade.log
SQL>@?/rdbms/admin/catupgrd.sql
Spool off;

@?/rdbms/admin/utlrp.sql

问题解决。

 

col object format a30
col owner format a20
col type format a20
Select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status
from
dba_objects where status <>'VALID';

 

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

转载于:http://blog.itpub.net/10173379/viewspace-731027/