Skip to Content
avatar image
Former Member

Checking for Invalid Database Objects - Oracle Upgrade

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Jul 13, 2011 at 02:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jul 14, 2011 at 06:04 AM

    Hi Rizwan,

    Please check SAP note : 980426 also ..

    Thanks

    Mithun Das.

    Add comment
    10|10000 characters needed characters exceeded