Invalid objs after upgrade from 9.2.0.7 to 10.2.0.4

[ganimede_dignan]

Hi,

after upgrade from 9.2.0.7 to 10.2.0.4 on a Solaris 10 box we have this list of inactive objs:

SELECT SUBSTR(OWNER, 1, 20) OWNER,

SUBSTR(OBJECT_NAME, 1, 35) OBJECT_NAME,

SUBSTR(OBJECT_TYPE, 1, 20) OBJECT_TYPE

FROM DBA_OBJECTS

WHERE STATUS = 'INVALID';

OWNER OBJECT_NAME OBJECT_TYPE

-

---

-

---

-

---

SYS DBMS_SQLPA PACKAGE BODY

SYS X_$KCBWDS VIEW

SYS X_$BH VIEW

PUBLIC X$KCBWDS SYNONYM

PUBLIC X$BH SYNONYM

SYSTEM UTL_RECOMP PACKAGE BODY

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

SYUMON2 QUEST_SPC_ANALYSIS_UTIL PACKAGE BODY

SYUMON2 QUEST_SPC_ANALYZE PACKAGE BODY

SYUMON2 QUEST_SPC_UTIL PACKAGE BODY

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

OWNER OBJECT_NAME OBJECT_TYPE

-

---

-

---

-

---

SYUMON2 QUEST_SOO_BUFPOOLSTATS VIEW

SYUMON2 QUEST_SOO_BPSET VIEW

SYUMON2 QUEST_SOO_BUFFERPOOL VIEW

SYUMON2 QUEST_IX_HOT_BLOCK_STATS VIEW

Have you got any idea ?

I've already try with 648203 note but without success.

SELECT SUBSTR(COMP_ID, 1, 20) COMP_ID,

SUBSTR(COMP_NAME, 1, 40) COMP_NAME,

STATUS

FROM DBA_REGISTRY;

COMP_ID COMP_NAME STATUS

-

---

-

---

-

---

CATALOG Oracle Database Catalog Views VALID

CATPROC Oracle Database Packages and Types VALID

dba_errors contains:

SQL> select * from dba_errors ;

SYS X_$KCBWDS VIEW

1 0 0

ORA-00904: "DBHDEPTH": invalid identifier

ERROR 0

SYS X_$BH VIEW

1 0 0

ORA-00904: "RRBA_BNO": invalid identifier

ERROR 0

SYS DBMS_SQLPA PACKAGE BODY

2 113 5

PL/SQL: SQL Statement ignored

ERROR 0

SYS DBMS_SQLPA PACKAGE BODY

1 118 44

PL/SQL: ORA-00904: "OTHER_XML": invalid identifier

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

20 318 7

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

19 318 40

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

18 267 10

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

17 268 42

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

16 202 13

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

15 203 35

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

14 196 13

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

13 197 35

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

12 188 13

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

11 189 35

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

10 183 13

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

9 184 35

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

8 104 7

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

7 104 14

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

6 77 7

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

5 77 44

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

4 64 10

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

3 64 17

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

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

2 60 10

PL/SQL: SQL Statement ignored

ERROR 0

SYSTEM UTL_RECOMP PACKAGE BODY

1 61 29

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

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

20 839 3

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

19 839 3

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

18 800 10

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

17 800 15

PLS-00201: identifier 'DBMS_PIPE' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

16 514 5

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

15 514 20

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

14 508 3

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

13 508 18

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

12 436 3

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

11 436 18

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

10 434 3

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

9 434 3

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

8 94 15

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

7 94 26

PLS-00201: identifier 'DBMS_PIPE' must be declared

ERROR 201

SYUMON2 QUEST_SPC_ANALYSIS_UTIL PACKAGE BODY

4 26 6

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_UTIL PACKAGE BODY

4 154 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_UTIL PACKAGE BODY

3 154 7

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_UTIL PACKAGE BODY

2 142 4

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_UTIL PACKAGE BODY

1 142 4

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

6 93 15

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

5 93 15

PLS-00201: identifier 'DBMS_PIPE' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

4 91 10

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

3 91 21

PLS-00201: identifier 'DBMS_PIPE' must be declared

ERROR 201

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

2 90 10

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

1 90 10

PLS-00201: identifier 'DBMS_PIPE' must be declared

ERROR 201

SYUMON2 QUEST_SPC_ANALYSIS_UTIL PACKAGE BODY

3 26 6

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_SPC_ANALYSIS_UTIL PACKAGE BODY

2 18 9

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_ANALYSIS_UTIL PACKAGE BODY

1 18 9

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_IX_HOT_BLOCK_STATS VIEW

1 0 0

ORA-04063: view "SYS.X_$BH" has errors

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

20 666 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

19 666 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

18 665 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_ANALYZE PACKAGE BODY

2 1198 20

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

17 665 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

16 664 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_SPC_ANALYZE PACKAGE BODY

1 1198 20

PLS-00201: identifier 'DBMS_LOCK' must be declared

ERROR 201

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

15 664 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

14 663 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

13 663 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

12 662 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

11 662 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

10 661 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

9 661 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

8 660 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

7 660 57

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

6 659 7

PL/SQL: Statement ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

5 659 53

PLS-00320: the declaration of the type of this expression is incomplete or malfo

rmed

ERROR 320

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

4 626 35

PL/SQL: Item ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

3 626 35

PLS-00905: object SYUMON2.QUEST_IX_HOT_BLOCK_STATS is invalid

ERROR 905

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

2 625 35

PL/SQL: Item ignored

ERROR 0

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

1 625 35

PLS-00905: object SYUMON2.QUEST_IX_HOT_BLOCK_STATS is invalid

ERROR 905

SYUMON2 QUEST_SOO_BUFPOOLSTATS VIEW

1 0 0

ORA-04063: view "SYS.X_$KCBWDS" has errors

ERROR 0

SYUMON2 QUEST_SOO_BPSET VIEW

1 0 0

ORA-04063: view "SYS.X_$KCBWDS" has errors

ERROR 0

SYUMON2 QUEST_SOO_BUFFERPOOL VIEW

1 0 0

ORA-04063: view "SYS.X_$BH" has errors

ERROR 0

SQL> spool off

Ganimede Dignan

Regards.

[Former Member]

Hello,

Try to recompile the schema and check if that resolves this issue.

You will find the script supplied by oracle in the following directory:

$ORACLE_HOME/rdbms/admin -> utlrp.sql

There is also a database procedure available to recompile the schema compile_schema as part of dbms_utility, but running the script is easier method. As part of database upgrade activity, this script is executed by default.

Cheers,

Nisch

[ganimede_dignan]

Hi,

we have already used utlrp.sql, without success.

Thank you for your post.

Regards.

Ganimede Dignan.

[fidel_vales]

Hi,

several of the objects you mentioned belong to a non SAP user (SYUMON2) and the name begins with "QUEST"

May be that the product is not compatible with the new version. Have you check with the tool provider?

[ganimede_dignan]

Hi,

>May be that the product is not compatible with the new version. Have you check with the tool provider?

excusme, what is it ?

I use only installation kit from SAP DVD, download patcheset from service market place and other ora patches from service market place.

Regards.

Ganimede Dignan.

[fidel_vales]

OK,

I'll try to rephrase.

You are using QUEST.

This tool is NOT from SAP, it is from another provider.

May be you should also ask them why their tool is not upgrading properly to oracle 10.2.0.4.

As you can see (from your first output) most of the invalid objects belong to QUEST:

SELECT SUBSTR(OWNER, 1, 20) OWNER,

SUBSTR(OBJECT_NAME, 1, 35) OBJECT_NAME,

SUBSTR(OBJECT_TYPE, 1, 20) OBJECT_TYPE

FROM DBA_OBJECTS

WHERE STATUS = 'INVALID';

OWNER OBJECT_NAME OBJECT_TYPE

-

---

SYS DBMS_SQLPA PACKAGE BODY

SYS X_$KCBWDS VIEW

SYS X_$BH VIEW

PUBLIC X$KCBWDS SYNONYM

PUBLIC X$BH SYNONYM

SYSTEM UTL_RECOMP PACKAGE BODY

SYUMON2 QUEST_SPC_REPAIR_CHAINED_ROW PACKAGE BODY

SYUMON2 QUEST_SPC_ANALYSIS_UTIL PACKAGE BODY

SYUMON2 QUEST_SPC_ANALYZE PACKAGE BODY

SYUMON2 QUEST_SPC_UTIL PACKAGE BODY

SYUMON2 QUEST_IX_LATCH_RULE_PAK PACKAGE BODY

SYUMON2 QUEST_SOO_BUFPOOLSTATS VIEW

SYUMON2 QUEST_SOO_BPSET VIEW

SYUMON2 QUEST_SOO_BUFFERPOOL VIEW

SYUMON2 QUEST_IX_HOT_BLOCK_STATS VIEW

Regarding the others:

SYS DBMS_SQLPA PACKAGE BODY

SYS X_$KCBWDS VIEW

SYS X_$BH VIEW

PUBLIC X$KCBWDS SYNONYM

PUBLIC X$BH SYNONYM

SYSTEM UTL_RECOMP PACKAGE BODY

Currently I do not have 10.2.0.4 installed (i hope this week) but in my 10.2.0.2 the following does not exist:

SYS DBMS_SQLPA PACKAGE BODY

SYS X_$KCBWDS VIEW

SYS X_$BH VIEW

PUBLIC X$KCBWDS SYNONYM

PUBLIC X$BH SYNONYM

[ganimede_dignan]

Hi,

>I'll try to rephrase.

>You are using QUEST.

>This tool is NOT from SAP, it is from another provider.

>May be you should also ask them why their tool is not upgrading properly to oracle 10.2.0.4.

ehmmm.... in the past this system was managed from an outsourcer that should be used Toad or other similar tool.

Thank you for information.

Regards.

[ganimede_dignan]

.