Checking for Invalid Database Objects - Oracle Upgrade

[Former Member]

Hi All,

We upgrading our oracle database from

Hi All,

We are planning to upgrade oracle version 10.2.0.2.0 to Oracle patch set 10.2.0.5

As part of post upgrade steps I executed script utlu102i.sql to check for invalid database objects.

SQL> @utlu102i.sql

Oracle Database 10.2 Upgrade Information Utility 07-13-2011 11:20:26

.

**********************************************************************

Database:

**********************************************************************

--> name: D01

--> version: 10.2.0.2.0

--> compatible: 10.2.0

--> blocksize: 8192

.

**********************************************************************

Tablespaces: [make adjustments in the current environment]

**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade.

.... minimum required size: 1100 MB

--> PSAPTEMP tablespace is adequate for the upgrade.

.... minimum required size: 58 MB

--> SYSAUX tablespace is adequate for the upgrade.

.... minimum required size: 479 MB

--> PSAPUNDO tablespace is adequate for the upgrade.

.... minimum required size: 3798 MB

.

**********************************************************************

Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

-- No update parameter changes are required.

.

**********************************************************************

Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

-- No renamed parameters found. No changes are required.

.

**********************************************************************

Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]

**********************************************************************

-- No obsolete parameters found. No changes are required

.

**********************************************************************

Components: [The following database components will be upgraded or installed]

**********************************************************************

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

.

**********************************************************************

Miscellaneous Warnings

**********************************************************************

WARNING: --> Database contains INVALID objects prior to upgrade.

.... USER PUBLIC has 34 INVALID objects.

.... USER SYS has 4 INVALID objects.

.... USER SAPWEBUSR has 2 INVALID objects.

.... USER SAPR3 has 530 INVALID objects.

.

PL/SQL procedure successfully completed.

SQL> SPOOL OFF

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

Now I dont understand what we are supposed to do about these invalid objects.?

I checked SAP Note : 819830 , which then refers to sap note 1122956 to deal with Invalid Objects.

I checked SAP note 1122956 - ORA-24006: cannot create QUEUE

Which advises to execute script ORACLE_HOME/rdbms/admin/catproc

I ran this script and our put did not generated any errors.

So is that mean I dont need to do anything else with regards to these Invalid database objects?

[Former Member]

Hi Rizwan,

Please check SAP note : 980426 also ..

Thanks

Mithun Das.

[Former Member]

Hi Rizwan,

First of all, did you downloaded the script under "Oracle -> Oracle 10.2.0.4 -> Database RDBMS -> Generic" on SAP Service Marketplace? Check the note 1237838 - Current version of pre-upgrade information script

Identify the invalid objects;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

Then compile them manually;

alter <object_type> schemaname.<object_name> compile;

Best regards,

Orkun Gedik

Edited by: Orkun Gedik on Jul 13, 2011 6:04 PM

[Former Member]

Hi Orkun Gedik,

Thanks for replying,

I have already seen this note : Note 1237838 - Current version of pre-upgrade information script

I used the option 3 to get the utlu102i.sql.

I executed utlu102i.sql script which generated list of invalid objects.

WARNING: --> Database contains INVALID objects prior to upgrade.

.... USER PUBLIC has 34 INVALID objects.

.... USER SYS has 4 INVALID objects.

.... USER SAPWEBUSR has 2 INVALID objects.

.... USER SAPR3 has 530 INVALID objects.

I also executed following sql command on the system.

SQL> spool invalid_objects

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

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

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

SQL> spool off

I have reviewed the output file which shows number of objects for example.

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

OBJECT_NAME

-

---

OBJECT_TYPE OWNER

-

---

-

---

AQ$_AQ_SRVNTFN_TABLE_F

VIEW SYS

AQ$_SCHEDULER$_EVENT_QTAB_F

VIEW SYS

DBA_HIST_SGASTAT

SYNONYM PUBLIC

OBJECT_NAME

my question was :

what am i supposed to with these invalid objects.

you advised :

Then compile them manually;

alter <object_type> schemaname.<object_name> compile;

What will this do?

I am trying understand this process.

Thanks