We have standardized on DBUA for all DB upgrades of E-Business Suite instances. While DBUA was on the Upgrading Oracle Server stage, Anand asked me a question. How to know whether DBUA is proceeding well. The progress bar and the DBUA logs tell you only so much. So I checked the contents of $ORACLE_HOME/rdbms/admin/catupgrd.sql which is the upgrade script called by DBUA internally. Here's what it says:
catupgrd.sql - CATalog UPGraDe to the new release
This script is to be used for upgrading an 8.1.7, 9.0.1, 9.2
or 10.1 database to the new release. This script provides a direct
upgrade path from these releases to the new Oracle release.
The upgrade is partitioned into the following 5 stages:
STAGE 1: call the "i" script for the oldest supported release:
This loads all tables that are necessary
to perform basic DDL commands for the new release
STAGE 2: call utlip.sql to invalidate PL/SQL objects
STAGE 3: Determine the original release and call the
c0x0x0x0.sql for the release. This performs all
necessary dictionary upgrade actions to bring the
database from the original release to new release.
STAGE 4: call the a0x0x0x0.sql for the original release:
This performs all necessary upgrade using
STAGE 5: call cmpdbmig.sql
This calls the upgrade scripts for all of the
components that have been loaded into the database
* This script needs to be run in the new release's environment
(after installing the release to which you want to upgrade).
* You must be connected AS SYSDBA to run this script.
This gave me an idea that catupgrd.sql is first invalidating all objects through utlip.sql. Once the catalog is created some objects are recreated. So we can check the count of invalid objects to see if DBUA is proceeding well.
select count(*) from dba_objects
During the Upgrading Oracle Server stage, if you run the above query periodically, you'll notice that the number goes on increasing. This is a good indicator that DBUA is proceeding well. Later on the number will decrease when catalog is created. The number will again increase when the components like Intermedia and Spatial are getting upgraded. Finally during post upgrade step when utlrp.sql is called by DBUA, the number of invalids will start reducing.
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps