Upgrade the Oracle 11.2.0.4 database to Oracle Database 12.1.0.2

Database SID: UPGR is database version 11.2.0.4 is running and ready for an upgrade.The new pre-upgrade check script preupgrd.sql which will examine UPGR database. This script is shipped with the new Oracle 12c home in /u01/app/oracle/product/12.1.0.2/rdbms/admin

In this method, the database will stay in place and doesn’t get moved to another location.

***  Steps to Upgrade from Oracle 11.2.0.4 to Oracle 12.1.0.2  ***

1. Open Terminal and set the environment to 11g. I have. upgr script to set the necessary environment for the database

$ . upgr               
$ sqlplus / as sysdba

2. Run the new pre-upgrade check script preupgrd.sql, in  11.2.0.4 environment – it will generate 3 files:  

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

3.Verify the preupgrade.log and make necessary changes  

$ less /u01/app/oracle/cfgtoollogs/UPGR/preupgrade/preupgrade.log

The 11.2.0.4 database has the OLAP Catalog AMD component installed, and this component is no longer included in Oracle Database starting with Oracle Database 12c.

Remove the OLAP Catalog AMD component using the script from the 12.1.0.2 Oracle Home:

SQL>@/u01/app/oracle/product/12.1.0.2/olap/admin/catnoamd.sql
SQL> commit;

The preupgrade log includes a message about moving audit data from system.aud$ to sys.aud$ because Oracle Label Security is installed.Move the AUD$ table now using the olspreupgrade.sql script from the Oracle Database 12c home from SYSTEM to SYS:

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

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;
SQL> alter system set processes=300 scope=spfile;

 4. Gather dictionary stats prior to the upgrade:

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

5. Execute the preupgrade_fixups.sql – it was created by preupgrd.sql in directory /u01/app/oracle/cfgtoollogs/UPGR/preupgrade

SQL>@/u01/app/oracle/cfgtoollogs/UPGR/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.

6. Shutdown the UPGR database:

SQL >shutdown immediate
SQL >exit

7. Copy spfile and password file into the Oracle 12c home’s dbs directory:

$ 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/

Now  upgrade UPGR database to Oracle Database 12c using the new parallel upgrade scripts. Furthermore recompile and check for invalid objects before/after the upgrade.

8. Open terminal and set the environment point to 12. I have . upgr12 script to set necessary environment for database.

$ . upgr12
$ sqlplus / as sysdba

9. Bring the UPGR database into UPGRADE mode

SQL> startup upgrade
SQL> exit

 10. Upgrade the UPGR database with the parallel upgrade script.Start the new parallel upgrade – it will be driven by a PERL script catctl.pl outside of SQL*Plus and execute in 4 parallel threads – in maximum (You could run with 8 parallel threads by specifying the parameter option -n 8).

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

 I have seen that as many as 73 phases listed – some can act in parallel, other require serial execution. 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.

11. Finalize the upgrade with all required post upgrade steps. During this part finalize the upgrade with recompilation, postupgrade_fixups.sql and the time zone adjustment to TZ V18. Startup the database – post upgrade it is shutdown:

$ . upgr12
$ sqlplus / as sysdba

Startup the UPGR database and recompile everything:

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

Execute the postupgrade_fixups.sql:

SQL>@/u01/app/oracle/cfgtoollogs/UPGR/preupgrade/postupgrade_fixups.sql

Adjust Time Zone settings – you may look into the scripts taken from MOS Note: 1509653.1 before executing them:

SQL>@/home/oracle/DST/DST_prepare.sql
SQL>@/home/oracle/DST/DST_adjust.sql
SQL>exit;

And database Upgrade is completed successfully.