• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
We are running our second test upgrade and are having problems upgrading Oracle from 10.2.0.4 to 11.1.

The first problems occurs when we run the utlrp.sql script after running the stats.sql query to collect optimizer statistics. We end up with 2 INVALID objects:

* DBMS_SUMADVISOR INVALID
* DBMS_SQLPA INVALID

When we then move on to do the upgrade running the catupgrd.sql script we get an error:

ERROR:
ORA-04023: Object SYS.STANDARD could not be validated or authorized

We did successfully complete the upgrade once. In that upgrade we did not see any of these problems.

Resolution:
[From site:]

We were able to upgrade our database from Oracle 10 to Oracle 11.

What was causing the problem had to do with the section on Optimizer Statistics. The stats.sql script that was given to us does a brute force collection of all possible stale statistics. Most of the schemas did not exist in our database, so this brute force attack created many INVALID objects and caused problems with the upgrade.

There is a Metalink Note 560336.1 that includes an sql script to check schemas to find only those with stale statistics. This script then outputs the needed sql statements to clean up only the schemas that have stale statisics. In our case there was only 1 schema that had stale statistics. Once we used this sql instead of the stats.sql that tries to clean up any possible schema with stale statistics, everything went well.

• Article last edited: 10/8/2013