Upgrading Oracle Databases from to

This discussion is about performing an upgrade from databases to
This is an out-of-place manual upgrade from to Patch set is a full release.
It cannot be installed on top of existing ORACLE_HOME.

Note the following steps
1. Install into a separate ORACLE_HOME.
2. Take a full backup of the database.
3. Always shutdown the database cleanly

Running the Pre-upgrade script utlu112i.sql. This upgrade is focusing on Unix environments.

After installing the software on new oracle /opt/app/oracle/product/, Run the following script from new oracle home on the database.

Connect as sys
SQL> spool pre_upgrade.log
SQL> @/opt/app/oracle/product/
SQL> spool off

Verify the pre_upgrade.log file for any issue.
If following warning is issued:
Oracle recommends gathering dictionary statistics prior to upgrading the database.
To gather dictionary statistics execute the following command while connected as SYSDBA:

Connect as sys
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Note: Ignore if dbms_stats.gather_dictionary_stats was run once.

If following warning is issued:

WARNING: –> Database contains INVALID objects prior to upgrade.
…. The list of invalid SYS/SYSTEM objects was written to
…. registry$sys_inv_objs.
…. The list of non-SYS/SYSTEM objects was written to
…. registry$nonsys_inv_objs.
…. Use utluiobj.sql after the upgrade to identify any new invalid
…. objects due to the upgrade.
…. USER PUBLIC has 1 INVALID objects.

connect as sys
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Note:Run the utlu112i.sql again after running above two statements.

Following are the known issue

  • uses Time Zone version 14. use Time Zone version 11

    WARNING: –> Database is using a timezone file older than version 14.
    …. After the release migration, it is recommended that DBMS_DST package
    …. be used to upgrade the database timezone version
    …. to the latest version which comes with the new release.

    Note: uses Time Zone version 14. use Time Zone version 11. DBUA would automatically upgrade Time Zone to version 14 if you checked “Upgrade Timezone Version and TIMESTAMP WITH TIME ZONE data” box

    You can either upgrade time zone version after upgrading to using DBMS_DST package
    or upgrade time zone to version 14

    Run pre-upgrade diagnostic utility dbupgradiag.sql

    Run dbupgdiag.sql script from below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects in dba_objects
    Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

    $ cd $ORACLE_HOME/rdbms/admin
    $ sqlplus “/ as sysdba”
    SQL> spool pre_dbupgdiag.log
    SQL>spool off

    If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

    $ cd $ORACLE_HOME/rdbms/admin
    $ sqlplus “/ as sysdba”

    After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

    Configure the database environment for Upgrade

    Copy pfile/spfile and orapw.ora file to the Home
    Connect as sys
    Go to /opt/app/oracle/product/11.2.0/dbs and copy the spfile.ora or init.ora and password file orapw.ora to /opt/app/oracle/product/

    Set the environment for Upgrade

    Following environment variables needs to be set before proceeding the upgrade.

    Export ORACL_HOME=/opt/app/oracle/product/
    Export ORACLE_SID=
    Echo $PATH

    Replace all occurrences of /opt/app/oracle/product/11.2.0 with /opt/app/oracle/product/ and set the PATH to new values.

    Export PATH=
    Export LD_LIBRARY_PATH=/opt/app/oracle/product/

    Copy network/admin files to new oracle Home
    Copy network configuration files (listener.ora, sqlnet.ora, tnsnames.ora ..etc) from $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location to $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location

    Copy OC4J directories from home to new oracle Home (This is required only if there is DB Console configured)


    Specify the actual name for

    Upgrade database to

    sqlplus ” / as sysdba ”
    spool upgrade.log
    startup upgrade
    set echo on
    spool off
    shutdown immediate

    Check the /tmp/upgrade.log for any errors.
    Restart the database in normal mode

    sqlplus ” / as sysdba ”
    select comp_name,version,status from dba_registry;
    select owner,count(*) from dba_objects where status != ‘VALID’ group by owner;

    Make sure all the components are valid.
    Change the compatibility parameter

    Change the compatible parameter and restart the database.

    SQL> alter system set compatible=’′ scope=spfile;
    SQL> shutdown immediate;
    SQL> startup;

    Check LISTENER started from Home

    Make sure Listener started from new Home and listening to Oracle database.