INVALID PACKAGE AND COMPONENT IN DATABASE WHILE UPGRADING TO 10204 FROM 10203

While upgrading the server we faced this issue that one component and a package got invalid..




Database : AREL203

Server : tonot

Activity : Upgrade database from 10203 to 10204

Error : invalid object / component

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle XML Database 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 INVALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID

8 rows selected.

OWNER OBJECT_NAME STATUS
------ ---- ---------------------------------------- -------
SYS KUPW$WORKER INVALID
P1INTF INTF_DCRR_WAAI_S INVALID
P1INTF INTF_DCRR_SOIT_S INVALID
P1INTF INTF_DCRR_RELI_S INVALID
P1INTF INTF_DCRR_PPLD_S INVALID
P1INTF INTF_DCRR_PUOE_S INVALID
P1INTF INTF_DCRR_PPOL_S INVALID
P1INTF INTF_DCRR_PURO_S INVALID
P1INTF INTF_DCRR_ALTY_S INVALID

9 rows selected.

Solution:



There are two ways of doing this

1) Rebuild the pkg body

2) Chk the error and rectify that



Solution 1)

a) Run the script to rebuild the pkg body

@$ORACLE_HOME/rdbms/admin/catdpb.sql

After this chk the status



SQL> column comp_name format a40

select COMP_NAME,VERSION,STATUS from dba_registry;



column object_name format a40

column owner format a10

SQL>select owner,object_name,status from dba_objects where status='INVALID';



Me got the same status as above



Solution 2)



SQL> ALTER SESSION SET EVENTS'942 trace name errorstack level 3';

Session altered.

SQL> alter package KUPW$WORKER compile BODY;

Warning: Package Body altered with compilation errors.

SQL> show error

Errors for PACKAGE BODY KUPW$WORKER:

LINE/COL ERROR

-------- -----------------------------------------------------------------

15304/5 PL/SQL: SQL Statement ignored

15304/34 PL/SQL: ORA-00942: table or view does not exist

SQL>

SQL> show parameter dump

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

background_core_dump string partial

background_dump_dest string /ora/admin/arel203/bdump

core_dump_dest string /ora/admin/arel203/cdump

max_dump_file_size string UNLIMITED

shadow_core_dump string partial

user_dump_dest string /ora/admin/arel203/udump

SQL> !

tonto(arepl203):/var/opt/oracle>cd /ora/admin/arel203/udump

-rw-r----- 1 oracle oinstall 1019931 Mar 23 12:19 arepl203_ora_13845.trc

-rw-r----- 1 oracle oinstall 576 Mar 23 12:20 arepl203_ora_17182.trc

-rw-r----- 1 oracle oinstall 14843871 Mar 23 12:35 arepl203_ora_17230.trc

tonto(arepl203):/ora/admin/arepl203/udump>vi arepl203_ora_17230.trc

ksedmp: internal or fatal error

ORA-00942: table or view does not exist

No current SQL statement being executed.



SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view;

CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view

*

ERROR at line 1:

ORA-25153: Temporary Tablespace is Empty

SQL> alter tablespace temp add tempfile '/m003b/oradata/arel203/temp201.dbf' SIZE 200M;

Tablespace altered.

SQL> CREATE GLOBAL TEMPORARY TABLE sys.ku$noexp_tab ON COMMIT PRESERVE ROWS AS SELECT * FROM sys.ku_noexp_view;

Table created.

SQL> GRANT SELECT ON sys.ku$noexp_tab TO PUBLIC;

Grant succeeded.

SQL> GRANT INSERT ON sys.ku$noexp_tab TO PUBLIC;

Grant succeeded.

SQL> alter package KUPW$WORKER compile BODY;

Package body altered.

Check the INVALID

SQL> column object_name format a40
column owner format a10
select owner,object_name,status from dba_objects where status='INVALID';SQL> SQL>

OWNER OBJECT_NAME STATUS
---------- ---------------------------------------- -------
P1INTF INTF_DCRR_WAAI_S INVALID
P1INTF INTF_DCRR_SOIT_S INVALID
P1INTF INTF_DCRR_RELI_S INVALID
P1INTF INTF_DCRR_PPLD_S INVALID
P1INTF INTF_DCRR_PUOE_S INVALID
P1INTF INTF_DCRR_PPOL_S INVALID
P1INTF INTF_DCRR_PURO_S INVALID
P1INTF INTF_DCRR_ALTY_S INVALID

8 rows selected.

Same as before the start of activity…inform client and take approval to recompile.

COMP_NAME VERSION STATUS
---------------------------------------- ------------------------------ -----------
Oracle XML Database 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Workspace Manager 10.2.0.4.3 VALID
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and Types 10.2.0.4.0 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID

8 rows selected.