preupgrade.jar

This script is a pre-upgrade diagnostic tool giving specific pre-and post-upgrade tasks. It can be run at any time but is generally run as a pre-requisite task during an Oracle Upgrade. It is possible to download a later version of the preupgrade script from 884522.1, but I have not done this in testing.

Run

Run the script below as 'oracle' with the old ORACLE_HOME enviroment set...

$ORACLE_HOME/jdk/bin/java -jar /u01/app/oracle/product/${NEWVER}/rdbms/admin/preupgrade.jar FILE TEXT

NOTE: The environment variable ${NEWVER} should be set to reflect the target ORACLE_HOME

You should get some output files:

/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/preupgrade.log

/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/preupgrade_fixups.sql

/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/postupgrade_fixups.sql

Common Actions

There follow some examples of common output tasks from the tool with some guidance for how to address the issues.

Before Upgrade

+ Set the value of JOB_QUEUE_PROCESSES to a minimum of 0 or remove the

setting entirely and accept the Oracle default.


The database has JOB_QUEUE_PROCESSES=0.


Starting with Oracle Database 11.2, setting JOB_QUEUE_PROCESSES=0 will

disable job execution via DBMS_JOBS and DBMS_SCHEDULER.

Work out why JOB_QUEUE_PROCESSES has been set to 0 before correcting this issue. For example, it may have been set to prevent jobs from running which could cause incorrect data to be processed (maybe ingestion of a file via AQ that should be ingested elsewhere etc.). If it is safe to do so:

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=100 SCOPE=BOTH;

See also: Scheduler

+ Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid

objects. You can view the individual invalid objects with


SET SERVEROUTPUT ON;

EXECUTE DBMS_PREUP.INVALID_OBJECTS;


355 objects are INVALID.


There should be no INVALID objects in SYS/SYSTEM or user schemas before

database upgrade.

+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database

upgrade in off-peak time using:


EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;


Dictionary statistics do not exist or are stale (not up-to-date).


Dictionary statistics help the Oracle optimizer find efficient SQL

execution plans and are essential for proper upgrade timing. Oracle

recommends gathering dictionary statistics in the last 24 hours before

database upgrade.


For information on managing optimizer statistics, refer to the 12.1.0.2

Oracle Database SQL Tuning Guide.

NOTE: If you have followed the upgrade instructions correctly then you should not see this action.

You can run...

sqlplus / as sysdba

@/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/preupgrade_fixups.sql

exit

or you can just execute the following...

sqlplus / as sysdba

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

exit

+ Switch off Exclusive Mode prior to the upgrade.


Your database system has at least one account with only the 10G password

version (see the PASSWORD_VERSIONS column of DBA_USERS).


Starting with Oracle Database release 12.2, Exclusive Mode is the

default password-based authentication mode. In Exclusive Mode, accounts

which only have the 10G password version (see

DBA_USERS.PASSWORD_VERSIONS) are no longer accessible. If your system

has any account with only the 10G password version, Exclusive Mode must

be switched off prior to the upgrade. See the Network Reference Manual

chapter about the SQLNET.ORA parameter

SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode.

Refer to the Oracle Database Readme 12c Release 2 (12.2) for further

information regarding Bug 22031049.


+ Review and remove any unnecessary EVENTS.


The database contains events.


There are events set that should be removed before upgrade, unless your

application vendors and/or Oracle Support state differently. Changes

will need to be made in the spfile.


+ Complete any pending DST update operation before starting the database

upgrade.


There is an unfinished DST update operation in the database. It's

current state is: DATAPUMP(5)


There must not be any Daylight Savings Time (DST) update operations

pending in the database before starting the upgrade process.

Refer to My Oracle Support Note 1509653.1 for more information.


+ (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade.


The database contains 1149 objects in the recycle bin.


The recycle bin must be completely empty before database upgrade.

PURGE DBA_RECYCLEBIN;

Check the recyclebin is empty...

SELECT COUNT(*) FROM sys.RECYCLEBIN$;

For more information on any remaining objects use...

SELECT OBJ#,OWNER#,SPACE,ORIGINAL_NAME,PURGEOBJ FROM sys.RECYCLEBIN$;

See: 1910945.1 Unable To Empty or delete rows from Sys.recyclebin$ which is causing dbua (upgrade) stopped and purge dba_recyclebin not helping

If objects remain in the recyclebin even after purging then you can truncate (Oracle recommend you take a backup before doing this)..

PURGE DBA_RECYCLEBIN;

TRUNCATE TABLE sys.RECYCLEBIN$;

SELECT COUNT(*) FROM sys.RECYCLEBIN$;

After Upgrade

+ (AUTOFIXUP) If you use the -T option for the database upgrade, then run

$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,

to VALIDATE and UPGRADE any user tables affected by changes to

Oracle-Maintained types.


There are user tables dependent on Oracle-Maintained object types.


If the -T option is used to set user tablespaces to READ ONLY during the

upgrade, user tables in those tablespaces, that are dependent on

Oracle-Maintained types, will not be automatically upgraded. If a type

is evolved during the upgrade, any dependent tables need to be

re-validated and upgraded to the latest type version AFTER the database

upgrade completes.


+ Upgrade the database time zone version using the DBMS_DST package.


The database is using timezone datafile version 18 and the target

12.2.0.1.0 database ships with timezone datafile version 26.


Oracle recommends using the most recent timezone data. For further

information, refer to My Oracle Support Note 1585343.1.


+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the

command:


EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;


Oracle recommends gathering dictionary statistics after upgrade.


Dictionary statistics provide essential information to the Oracle

optimizer to help it find efficient SQL execution plans. After a

database upgrade, statistics need to be re-gathered as there can now be

tables that have significantly changed during the upgrade or new tables

that do not have statistics gathered yet.

If you follow the steps under Oracle Upgrade, you will run the postupgrade_fixups that will include this task:

@/u01/app/oracle/cfgtoollogs/$ORACLE_SID/preupgrade/postupgrade_fixups.sql

Alternatively, run the command manually...

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;