Upgrade Oracle 11g R2 (11.2.0.4) Database to Oracle 12c (12.2.0.1)

Step 1:

Upgrade Path for Oracle Database to Oracle 12c Release 1 (12.1)

For example:

If you are upgrading from 11.2.0.1 or 11.1.0.6, then you must first upgrade to Oracle Database 11g Release 2 (11.2.0.2).
If you are upgrading from 10.2.0.2, 10.2.0.3, 10.2.0.4, or 10.1.0.5, then you must first upgrade to 10.2.0.5 or later.
For release 9.2.0.8, you must first upgrade to an intermediate Oracle Database release, as follows: 9.2.0.8 -> 11.2.0.2 or 11.2.0.3 -> 12.1

Upgrading 11.2.0.4 to 12.2.0.1, on Linux (x64_86) platform

Step 2 :

Requirements and recommendations for Source  database 

Either take a cold or hot backup of the source database (advisable to have cold backup).

a) Take a backup using RMAN
(or)
b) Perform Cold Backup  ( if  your database is in NOARCHIVELOG mode)


Step.3:

check source database before upgrade

-Ensure That No Files Need Media Recovery Before Upgrading
-Ensure That No Files Are in Backup Mode Before Upgrading
-Purge the Database Recycle Bin Before Upgrading
-Disable all batch and cron jobs
-location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME

Step.4:

Run the new preupgrade check script preupgrd.sql, in  11.2.0.4 environment – it will generate 3 files in $ORACLE_HOME/cfgtoollogs/$SID/preupgrade:

SQL> @/u01/app/oracle/product/12.1.0.2/rdbms/admin/preupgrd.sql

preupgrade.log
preupgrade_fixups.sql
postupgrade_fixups.sql

Step.5:

Verify the preupgrade.log and make necessary changes

Example of preupgrade.log:

-WARNING: --> Process Count may be too low
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE (bydefault 150)
-WARNING: --> Inactive DBIDs found in AWR
AWR contains inactive DBIDs which may need additional updating after upgrading.
-WARNING: --> Enterprise Manager Database Control repository found in the database In Oracle Database 12c, Database Control is removed during the upgrade. To save time during the Upgrade, this action can be done prior to upgrading using the following steps after copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql Without the set echo and serveroutput commands you will not be able to follow the progress of the script.

-WARNING: --> Existing DBMS_LDAP dependent objects Database contains schemas with objects dependent on DBMS_LDAP package. Refer to the Upgrade Guide for instructions to configure Network ACLs. USER APEX_030200 has dependent objects.

-INFORMATION: -->
OLAP Catalog(AMD) exists in database Starting with Oracle Database 12c, OLAP is desupported. If you are not using the OLAP Catalog component and want to remove it, then execute the ORACLE_HOME/oraolap/admin/catnoamd.sql script before or after the upgrade.

-INFORMATION: --> Older Timezone in use

Please gather dictionary statistics 24 hours prior to upgrading the database.
To gather dictionary statistics execute the following command while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;

Step.6:

Prepare spfile for the 12c upgrade according to the output from preupgrade.log:
(Please note: Best Practice would be to edit the init.ora for the upgrade manually. You could do so – the way i propose here is just a shortcut avoiding manual edit steps)
SQL> create pfile from spfile;


Step.7:

Execute the preupgrade_fixups.sql – it was created by preupgrd.sql in directory ORACLE_HOME/cfgtoollogs/$SID/preupgrade

SQL>@ORACLE_HOME/cfgtoollogs/$SID/preupgrade/preupgrade_fixups.sql

Please note that the preupgrade_fixups.sql script will still complain about PROCESSES being set too low. This is because I have used the ALTER SYSTEM command to adjust the parameter, but that adjustment will not take effect until the database is shutdown and restarted. Because I specified SCOPE=SPFILE, this parameter will be set correctly for the upgrade.

Step.8:

Shutdown the UPGRADE database:
SQL >shutdown immediate
SQL >exit

Step.9:

Copy the passwordfile and pfile/spfile from the 11g home to the 12c home.

$ cp /u01/app/oracle/product/11.2.0/dbs/spfileUPGR.ora /u01/app/oracle/product/12.1.0.2/dbs/
$ cp /u01/app/oracle/product/11.2.0/dbs/orapwUPGR /u01/app/oracle/product/12.1.0.2/dbs/

Step.10:

Set new ORACLE HOME of 12c location
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1/

Step.11:

SQL> startup nomount;
SQL> alter database mount;
SQL> alter database open upgrade;
SQL> exit

Step.12:

Run catctl.pl

Go to $ cd $ORACLE_HOME/rdbms/admin
$ $ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql

This will take some time depending on your system. If you wonder about the RESTART phases: those happen if timing dependencies make it necessary to rerun a certain action. The logfiles will be written by default into the directory from which you started catctl.pl, $ORACLE_HOME/rdbms/admin Once the upgrade is finished it will shutdown the database and in the next phase restart it in normal mode.

logfile and upgrade summary reports are created in @ORACLE_HOME/cfgtoollogs/$SID

Note: It will create logfiles based on process in $ORACLE_HOME/rdbms/admin

example:

rw-r--r-- 1 oracle oinstall 5.4M Dec 18 10:41 catupgrd3.log
-rw-r--r-- 1 oracle oinstall 4.9M Dec 18 10:41 catupgrd2.log
-rw-r--r-- 1 oracle oinstall 4.2M Dec 18 10:41 catupgrd1.log
-rw-r--r-- 1 oracle oinstall 228M Dec 18 11:01 catupgrd0.log

Step.13:

Run postupgrade_fixups.sql which was created earlier.
@ORACLE_HOME/cfgtoollogs/$SID/preupgrade/postupgrade_fixups.sql

The database is using timezone datafile version 14 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

Step.14

Run below scripts:

SQL> @?/rdbms/admin/utlu122s.sql
SQL> @?/rdbms/admin/catuppst.sql
Important: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.

SQL> @catuppst.sql
This script can be run concurrently with utlrp.sql.
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

SQL>@?/rdbms/admin/utlrp.sql

Step.15:

Set COMPATIBALE parameter value to 12.2.0, start the listener with new home and count the invalid objects

Step.16:

Restart the database and check the version of a database