Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Wednesday, July 2, 2008

How to track if your DBUA or catupgrd.sql is progressing

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
anonymous blocks.
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
where status='INVALID';

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.


Anonymous said...

thanks; found this very useful

Anonymous said...

Excellent knowledge sharing , thanks a lot.

Prashant Namdeo said...

Really vikram, this is best way so far to keep track on the progress of dbua.
but i noticed one thing in alertlog of source version db, it shows something like "shutting down instance, shutdown interrupted by a process."

my question is that, is it ok to query anything while dbua is running..!
please provide some light on it.

Prashant Namdeo