Minimize downtime during upgrade

How to minimize downtime for Oracle Apps Upgrade

Information in this applies to any platform.

 1.  Migration in NOARCHIVELOG mode, which will reduce the upgrade time and hence downtime.

2.  As the main function of the migration is to create a new data dictionary, the upgrade can be tested for most of the migration functionality by using a copy of the databases SYSTEM tablespace and ROL…LBACK SEGMENT tablespaces and marking all other tablespaces OFFLINE.

This allows realistic timings to be obtained without having to copy an entire database. 3. Make all tablespaces OFFLINE NORMAL or make READ ONLY  except for SYSTEM, SYSAUX (When migrating to 10gR2 or higher) and those containing rollback segments prior to migration. This way if migration fails only the SYSTEM and rollback datafiles need to be restored rather than the entire database.

Note: You must OFFLINE the TABLESPACE as migrate does not allow OFFLINE files in an ONLINE tablespace. 4. When upgrading to Oracle Database 10g, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade. To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. Should use the DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics for your 9.2.0.X database.

Purpose  :-

This script is an automated way to deal with stale statistics operations that are required to be done as part of manual upgrade OR when reported by DBUA

Operations of this script :- 

This script will be primarily used during database upgrade before starting the actual upgrade. Execution of this script will report stale statistics if any. It reports on schemas which are part of DBA_REGISTRY as these are the ones which are upgraded. If it finds any stale statistics it will give the recommended commands to remove those stale statistics.


The Script needs to be run in SQL*Plus as SYS user to find the schema with stale statistics.

 Configuring the Script

Download and save the script as check_schema_stale_stats.sql. The script needs no additional configuration.

Running the Script

Connect as sysdba and execute the script

$ sqlplus / as sysdbaSQL> @check_schema_stale_stats.sql

CautionThis script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it. Script

Download the script from here. Execute the script before upgrade, to make sure there are no stale statistics.

Script Output

SQL> @check_schema_stale_stats.sql

There are no stale statistics in CTXSYS schema.

There are no stale statistics in DMSYS schema.

There are no stale statistics in EXFSYS schema.

There are no stale statistics in MDSYS schema.

There are no stale statistics in OLAPSYS schema.-

There are no stale statistics in ORDSYS schema.

SYS schema contains stale statistics use the following to gather the statistics


There are no stale statistics in SYSMAN schema.

There are no stale statistics in WMSYS schema.

There are no stale statistics in XDB schema.

See More

Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Nagulu Polagani

"We are all apprentices in a craft where no one ever becomes a master."