Prior Oracle 10g Upgrade: Invalid Database Objects

[Former Member]

Hi,

I am preparing the pre-requisite for Oracle 10g upgrade from Oracle 9.2.0.8.

I ran /oracle/stage/102_64/database/Disk1/SAP/utlu102i.sql to create /tmp/pre_upgrade.log

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

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

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

--> Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and Types [upgrade] VALID

.

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

Miscellaneous Warnings

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

WARNING: --> Deprecated CONNECT role granted to some user/roles.

.... CONNECT role after upgrade has only CREATE SESSION privilege.

WARNING: --> Database contains stale optimizer statistics.

.... Refer to the 10g Upgrade Guide for instructions to update

.... statistics prior to upgrading the database.

.... Component Schemas with stale statistics:

.... SYS

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

.... USER SAPAU1 has 84 INVALID objects.

.... USER SYS has 17 INVALID objects.

Note 819830 pt 8 leads me to OSS note 1122956 which advises me to run @$ORACLE_HOME/rdbms/admin/catproc.sql

I left catproc running for 15hrs+ and it is still not complete so i pressume its hang and terminate the process by shutting down the database and startup again.

I rerun catrpoc couple of times but it can never be completed, same as catolog.sql

I am currently facing the below.

COMP_ID COMP_NAME STATUS

-

---

-

---

-

---

CATALOG Oracle9i Catalog Views LOADING

CATPROC Oracle9i Packages and Types LOADING

Can anyone advise what I can do next to make CATALOG & CATPROC becomes valid again?

Regards,

Annie

[stefan_koehler · Accepted solution]

Hello Annie,

is your sap system running, while you are executing these 2 scripts?

• catalog.sql creates the data dictionary

• catproc.sql creates all structures required for PL/SQL

As these scripts are creating the database "internals", you can run on locks for database objects or library cache locks / pins for sql procedures.

If you just want to recompile / all invalid objects in your database, you can run the script "utlrp.sql".

For more information about invalid objects take a look at sapnote #648203

Maybe the 84 invalid objects of the user SAPAU1 are views, pl/sql procedures with invalid content.

You can run the following statement to identify which objects are invalid:

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';

Regards

Stefan

[Former Member · Accepted solution]

Hi Everyone,

Thanks for the useful answer. I managed to run catalog.sql and catproc.sql successfully after I turned off archivelog mode in the database. The hang scenior of these scripts can be found in Metalink 390998.1. Without able to complete running the two scripts, I am kind of stuck.

After I executed the scripts and performing manual validation of INVALID objects as according to note 648203, I am able to solve the INVALID objects now.

Many thanks to your input and useful OSS notes.

Cheers,

Annie

[Former Member]

This problem is resolved.

[Former Member]

Hi Annie

Stefan has given you the solution.

If here are invalid objects run:

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

[Former Member]

Dear,

Its not effect your database upgrade through below you can identify object which are invalid before database upgrade and re-run this script post upgrade so you can distinguish database objects that have become invalid due to the upgrade process

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

I suggest also refer below SAP Notes before upgrade.

Note 839182 - Oracle database 10g: OPatch

Note 871096 - Oracle Database 10g: Patch sets/patches for 10.2.0

Note 407314 - Released operating systems SAP R/3 kernel 6.x ORACLE

Note 720886 - Oracle Database 10g: Integration into the SAP environment

Note 948305 - Oracle Upgrade to 10g Release 2 (10.2) on Windows

Regards,

Nikunj Thaker.