Manual upgrading Oracle database 11g to 12c

If you want to know how we upgrade an 11g database to 12c using DBUA, click here. 

For upgrading 12.1.0.1 to 12.1.0.2 using DBUA, click here.

This article explains how to upgrade 11.2.0.2 and above, and 12.1.0.1 to 12.1.0.2 manually.

For details of each step, take a look at official 12c
upgrade guide which I also followed to write this article

https://docs.oracle.com/database/121/UPGRD/toc.htm

Manually upgrading to 12c

There are many DBAs who don’t want to follow the recommended way of upgrade using DBUA (Database upgrade Assistant). Following is the method of manually upgrading 11g database to 12c. I will be using here 11.2.0.3 to upgrade to 12.1.0.2, on Windows platform. Windows platform needs a couple of extra steps to be executed, like creation of OS level service using oradim.exe from 12c home before the upgrade, rest of the steps are almost same. Direct upgrade to 12c is possible only starting from 11.2.0.2 version. For other lower versions, you may wish to choose a different method of upgrade discussed in the following white paper.

http://www.oracle.com/technetwork/database/upgrade/upgrading-oracle-database-wp-12c-1896123.pdf

1)

Copy preupgrd.sql and utluppkg.sql scripts from ORACLE_HOME\rdbms\admin to any temporarily created directory. ORACLE_HOME mentioned here is 12c home.

C:\>copy C:\app\salmqure\product\12.1.0\dbhome_1\rdbms\admin\preupgrd.sql c:\temp         1 file(s) copied. C:\>copy C:\app\salmqure\product\12.1.0\dbhome_1\rdbms\admin\utluppkg.sql c:\temp         1 file(s) copied.

2)

Log in as SYSDBA and execute the preupgrd.sql script.

SQL> @c:\temp\preupgrd.sql The syntax of the command is incorrect. The syntax of the command is incorrect. Loading Pre-Upgrade Package... *************************************************************************** Executing Pre-Upgrade Checks in TESTDB... ***************************************************************************       ************************************************************                  ====>> ERRORS FOUND for TESTDB <<====  The following are *** ERROR LEVEL CONDITIONS *** that must be addressed                     prior to attempting your upgrade.             Failure to do so will result in a failed upgrade.            You MUST resolve the above errors prior to upgrade       ************************************************************       ************************************************************              ====>> PRE-UPGRADE RESULTS for TESTDB <<==== ACTIONS REQUIRED: 1. Review results of the pre-upgrade checks:  C:\app\salmqure\cfgtoollogs\testdb\preupgrade\preupgrade.log 2. Execute in the SOURCE environment BEFORE upgrade:  C:\app\salmqure\cfgtoollogs\testdb\preupgrade\preupgrade_fixups.sql 3. Execute in the NEW environment AFTER upgrade:  C:\app\salmqure\cfgtoollogs\testdb\preupgrade\postupgrade_fixups.sql       ************************************************************ *************************************************************************** Pre-Upgrade Checks in TESTDB Completed. *************************************************************************** *************************************************************************** *************************************************************************** SQL>

If there is any error displayed on the screen, you would need to correct it before proceeding. Check preupgrade.log file also.
Error "The syntax of the command is incorrect" as highlighted above can safely be ignored, as this is because of a bug in the preupgrd.sql script and does not harm anything.

3)
Above step also creates preupgrade_fixups.sql and postupgrade_fixups.sql script. Execute preupgrade_fixups.sql

SQL> @C:\app\salmqure\cfgtoollogs\testdb\preupgrade\preupgrade_fixups.sql Pre-Upgrade Fixup Script Generated on 2015-03-20 11:27:20  Version: 12.1.0.2 Build: 006 Beginning Pre-Upgrade Fixups... Executing in container TESTDB ********************************************************************** Check Tag:     EM_PRESENT Check Summary: Check if Enterprise Manager is present Fix Summary:   Execute emremove.sql prior to upgrade. ********************************************************************** Fixup Returned Information: 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. ********************************************************************** ********************************************************************** Check Tag:     AMD_EXISTS Check Summary: Check to see if AMD is present in the database Fix Summary:   Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP. ********************************************************************** Fixup Returned Information: INFORMATION: --> OLAP Catalog(AMD) exists in database      Starting with Oracle Database 12c, OLAP Catalog component is desupported.      If you are not using the OLAP Catalog component and want      to remove it, then execute the      ORACLE_HOME/olap/admin/catnoamd.sql script before or      after the upgrade. ********************************************************************** ********************************************************************** Check Tag:     APEX_UPGRADE_MSG Check Summary: Check that APEX will need to be upgraded. Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade. ********************************************************************** Fixup Returned Information: INFORMATION: --> Oracle Application Express (APEX) can be      manually upgraded prior to database upgrade      APEX is currently at version 3.2.1.00.12 and will need to be      upgraded to APEX version 4.2.5 in the new release.      Note 1: To reduce database upgrade time, APEX can be manually              upgraded outside of and prior to database upgrade.      Note 2: See MOS Note 1088970.1 for information on APEX              installation upgrades. ********************************************************************** **********************************************************************                       [Pre-Upgrade Recommendations] **********************************************************************                         *****************************************                         ********* Dictionary Statistics *********                         ***************************************** 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; ^^^ MANUAL ACTION SUGGESTED ^^^            **************************************************                 ************* Fixup Summary ************  3 fixup routines generated INFORMATIONAL messages that should be reviewed. **************** Pre-Upgrade Fixup Script Complete *********************

As we can see that it has given 4 recommendations (may be more or less for your case), OEM de-configuraiton, removal of OLAP and APEX upgrade prior to the database upgrade and gather dictionary stats. I can ignore first 1^st and 3^rd recommendation as these would be automatically done during upgrade of database (you may wish to execute them now to reduce the total upgrade process time).

4)
I would remove OLAP because I am not using OLAP and also gather dictionary statistics though, as recommended above.

SQL>@C:\app\salmqure\product\11.2.0\dbhome_1\olap\admin\catnoamd.sql SQL> EXECUTE dbms_stats.gather_dictionary_stats;

5)
Shutdown the database and take full database backup. Alternatively you can turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>

6)
Only for Windows platform, stop the OS level service, delete the service and then recreate the service by executing oradim.exe from Oracle 12c home.
Also copy SPFILE/ PFILE from 11G ORACLE_HOME\database directory to 12G ORACLE_HOME\database directory.

Stop Service C:\>net stop oracleserviceTESTDB The OracleServiceTESTDB service is stopping. The OracleServiceTESTDB service was stopped successfully. Delete Service using correct oradim C:\>where oradim C:\app\salmqure\product\11.2.0\dbhome_1\BIN\oradim.exe C:\app\salmqure\product\12.1.0\dbhome_1\BIN\oradim.exe C:\>C:\app\salmqure\product\11.2.0\dbhome_1\BIN\oradim.exe -delete -sid TESTDB Instance deleted. C:\>C:\app\salmqure\product\12.1.0\dbhome_1\BIN\oradim.exe -new -sid TESTDB -syspwd syspassword -startmode auto Instance created.

7)
Only for Unix based platforms, copy password file and SPFILE/PFILE from 11g $ORACLE_HOME/dbs directory to 12c $ORACLE_HOME/dbs directory.

8)
Log into the system (OS) using oracle 12c software owner and set environment variables. Modify the following paths according to your 12c oracle home

For Windows C:\>set ORACLE_HOME=C:\app\salmqure\product\12.1.0\dbhome_1 C:\>set PATH=%ORACLE_HOME%\bin;%PATH% C:\>set ORACLE_SID=TESTDB For Unix based $export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1 $export PATH=$ORACLE_HOME\bin:$PATH $export ORACLE_SID=TESTDB

9)
Log into the database using sqlplus, as SYSDBA, and the startup the database with upgrade option. Exit the SQLPLUS

C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>sqlplus SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 19 15:56:41 2015 Copyright (c) 1982, 2014, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size                  3047720 bytes Variable Size             838864600 bytes Database Buffers         1291845632 bytes Redo Buffers               13725696 bytes Database mounted. Database opened. If this is 12.1.0.1 CDB (container database) with one or more pluggable databases, open all pluggable database in upgrade mode SQL> alter pluggable database all open upgrade; SQL>EXIT C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>

Starting 12c, we have a new parallel upgrade utility called catctl.pl which is a perl script used to upgrade the database using parallel processes, thus reducing the upgrade time. Following link explains more about parallel upgrade utility for 12c.

https://docs.oracle.com/database/121/UPGRD/upgrade.htm#CHDDEIFJ

10)
Change the directory to %ORACLE_HOME%\rdbms\admin and start upgrade using catctl.pl utility. Use “-n” parameter for catctl.pl to specify the degree of parallelism. If you don’t specify, the default value for non-CDB is 4 and maximum value that could be used it 8. –l parameter is used for the location of output log files.

Before we start the upgrade, it is very much important to have sufficient memory allocated to the SGA, otherwise upgrade may fail with memory allocation errors. For this case, I used 2G value for SGA_TARGET parameter. C:\>cd %ORACLE_HOME%\rdbms\admin C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl -n 2  -l c:\temp catupgrd.sql Running catupgrd.sql in conventional way Alternatively, we can use traditional way of running catupgrd.sql script from SQLPLUS, but it should be run with PARALLEL=NO option as follows SQL> spool c:\temp\upgrade.log SQL> @catupgrd.sql PARALLEL=NO In case of a container 12.1.0.1 source database with one or more pluggable databases, it should be run for CDB$ROOT, PDB$SEED and also each of the pluggable database. SQL>alter session set container = "CDB$ROOT" SQL> spool c:\temp\upgrade.log SQL> @catupgrd.sql PARALLEL=NO SQL>alter session set container = "PDB$SEED" SQL> @catupgrd.sql PARALLEL=NO

11)
If used catctl.pl, the output of this upgrade process would be similar to the following. For this example, log files would be in c:\temp folder as specified in upgrade command. For conventional running of catupgrd.sql from SQLPLUS, please see the spool file for output or any errors

C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl -n 2  -l c:\temp catupgrd.sql Argument list for [catctl.pl] SQL Process Count     n = 2 SQL PDB Process Count N = 0 Input Directory       d = 0 Phase Logging Table   t = 0 Log Dir               l = c:\temp Script                s = 0 Serial Run            S = 0 Upgrade Mode active   M = 0 Start Phase           p = 0 End Phase             P = 0 Log Id                i = 0 Run in                c = 0 Do not run in         C = 0 Echo OFF              e = 1 No Post Upgrade       x = 0 Reverse Order         r = 0 Open Mode Normal      o = 0 Debug catcon.pm       z = 0 Debug catctl.pl       Z = 0 Display Phases        y = 0 Child Process         I = 0 catctl.pl version: 12.1.0.2.0 Oracle Home           = C:\app\salmqure\product\12.1.0\dbhome_1 Analyzing file catupgrd.sql Log files in c:\temp catcon: ALL catcon-related output will be written to c:\temp/catupgrd_catcon_9264.lst catcon: See c:\temp/catupgrd*.log files for output generated by scripts catcon: See c:\temp/catupgrd_*.lst files for spool files, if any Number of Cpus        = 4 SQL Process Count     = 2 ------------------------------------------------------ Phases [0-73] Serial   Phase #: 0 Files: 1     Time: 116s Serial   Phase #: 1 Files: 5     Time: 36s Restart  Phase #: 2 Files: 1     Time: 0s Parallel Phase #: 3 Files: 18    Time: 21s Restart  Phase #: 4 Files: 1     Time: 0s Serial   Phase #: 5 Files: 5     Time: 18s Serial   Phase #: 6 Files: 1     Time: 13s Serial   Phase #: 7 Files: 4     Time: 10s Restart  Phase #: 8 Files: 1     Time: 1s Parallel Phase #: 9 Files: 62    Time: 69s Restart  Phase #:10 Files: 1     Time: 0s Serial   Phase #:11 Files: 1     Time: 15s Restart  Phase #:12 Files: 1     Time: 0s Parallel Phase #:13 Files: 91    Time: 54s Restart  Phase #:14 Files: 1     Time: 0s Parallel Phase #:15 Files: 111   Time: 71s Restart  Phase #:16 Files: 1     Time: 0s Serial   Phase #:17 Files: 3     Time: 3s Restart  Phase #:18 Files: 1     Time: 0s Parallel Phase #:19 Files: 32    Time: 41s Restart  Phase #:20 Files: 1     Time: 0s Serial   Phase #:21 Files: 3     Time: 8s Restart  Phase #:22 Files: 1     Time: 0s Parallel Phase #:23 Files: 23    Time: 88s Restart  Phase #:24 Files: 1     Time: 0s Parallel Phase #:25 Files: 11    Time: 51s Restart  Phase #:26 Files: 1     Time: 1s Serial   Phase #:27 Files: 1     Time: 2s Restart  Phase #:28 Files: 1     Time: 0s Serial   Phase #:30 Files: 1     Time: 0s Serial   Phase #:31 Files: 257   Time: 25s Serial   Phase #:32 Files: 1     Time: 0s Restart  Phase #:33 Files: 1     Time: 0s Serial   Phase #:34 Files: 1     Time: 6s Restart  Phase #:35 Files: 1     Time: 0s Restart  Phase #:36 Files: 1     Time: 0s Serial   Phase #:37 Files: 4     Time: 50s Restart  Phase #:38 Files: 1     Time: 1s Parallel Phase #:39 Files: 13    Time: 55s Restart  Phase #:40 Files: 1     Time: 1s Parallel Phase #:41 Files: 10    Time: 11s Restart  Phase #:42 Files: 1     Time: 1s Serial   Phase #:43 Files: 1     Time: 6s Restart  Phase #:44 Files: 1     Time: 0s Serial   Phase #:45 Files: 1     Time: 7s Serial   Phase #:46 Files: 1     Time: 2s Restart  Phase #:47 Files: 1     Time: 0s Serial   Phase #:48 Files: 1     Time: 332s Restart  Phase #:49 Files: 1     Time: 0s Serial   Phase #:50 Files: 1     Time: 47s Restart  Phase #:51 Files: 1     Time: 1s Serial   Phase #:52 Files: 1     Time: 21s Restart  Phase #:53 Files: 1     Time: 1s Serial   Phase #:54 Files: 1     Time: 394s Restart  Phase #:55 Files: 1     Time: 1s Serial   Phase #:56 Files: 1     Time: 66s Restart  Phase #:57 Files: 1     Time: 0s Serial   Phase #:58 Files: 1     Time: 129s Restart  Phase #:59 Files: 1     Time: 0s Serial   Phase #:60 Files: 1     Time: 675s Restart  Phase #:61 Files: 1     Time: 0s Serial   Phase #:62 Files: 1     Time: 1595s Restart  Phase #:63 Files: 1     Time: 0s Serial   Phase #:64 Files: 1     Time: 3s Serial   Phase #:65 Files: 1 Calling sqlpatch with C:\app\salmqure\product\12.1.0\dbhome_1\perl\bin\perl.exe -I C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN -I C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch\sqlpatch.pl -verbose -upgrade_mode_only > c:\temp/catupgrd_datapatch_upgrade.log 2> c:\temp/catupgrd_datapatch_upgrade.err returned from sqlpatch A subdirectory or file C:\app\salmqure\product\12.1.0\dbhome_1\cfgtoollogs\TESTDB\upgrade already exists.     Time: 67s Serial   Phase #:66 Files: 1     Time: 50s Serial   Phase #:68 Files: 1     Time: 0s Serial   Phase #:69 Files: 1 Calling sqlpatch with C:\app\salmqure\product\12.1.0\dbhome_1\perl\bin\perl.exe -I C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN -I C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch C:\app\salmqure\product\12.1.0\dbhome_1\sqlpatch\sqlpatch.pl -verbose > c:\temp/catupgrd_datapatch_normal.log 2> c:\temp/catupgrd_datapatch_normal.err returned from sqlpatch     Time: 84s Serial   Phase #:70 Files: 1     Time: 241s Serial   Phase #:71 Files: 1     Time: 1s Serial   Phase #:72 Files: 1     Time: 0s Serial   Phase #:73 Files: 1     Time: 56s Grand Total Time: 4559s LOG FILES: (catupgrd*.log) Upgrade Summary Report Located in: C:\app\salmqure\product\12.1.0\dbhome_1\cfgtoollogs\TESTDB\upgrade\upg_summary.log Grand Total Upgrade Time:    [0d:1h:15m:59s]

12)
Database would be shutdown after the upgrade, startup the database and execute postupgrade_fixups.sql script.

C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>sqlplus SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 25 11:33:34 2015 Copyright (c) 1982, 2014, Oracle.  All rights reserved. Enter user-name: sys as sysdba Enter password: Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size                  3047720 bytes Variable Size            1023413976 bytes Database Buffers         1107296256 bytes Redo Buffers               13725696 bytes Database mounted. Database opened. SQL> @C:\app\salmqure\cfgtoollogs\TESTDB\preupgrade\postupgrade_fixups.sql Post Upgrade Fixup Script Generated on 2015-03-25 12:01:40  Version: 12.1.0.2 Build: 006 Beginning Post-Upgrade Fixups... ********************************************************************** Check Tag:     OLD_TIME_ZONES_EXIST Check Summary: Check for use of older timezone data file Fix Summary:   Update the timezone using the DBMS_DST package after upgrade is complete. ********************************************************************** Fixup Returned Information: INFORMATION: --> Older Timezone in use      Database is using a time zone file older than version 18.      After the upgrade, it is recommended that DBMS_DST package      be used to upgrade the 12.1.0.2.0 database time zone version      to the latest version which comes with the new release.      Please refer to My Oracle Support note number 977512.1 for details. ********************************************************************** ********************************************************************** Check Tag:     NOT_UPG_BY_STD_UPGRD Check Summary: Identify existing components that will NOT be upgraded Fix Summary:   This fixup does not perform any action. ********************************************************************** Fixup Returned Information: This fixup does not perform any action. If you want to upgrade those other components, you must do so manually. ********************************************************************** **********************************************************************                      [Post-Upgrade Recommendations] **********************************************************************                         *****************************************                         ******** Fixed Object Statistics ********                         ***************************************** Please create stats on fixed objects two weeks after the upgrade using the command:    EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; ^^^ MANUAL ACTION SUGGESTED ^^^            **************************************************                 ************* Fixup Summary ************  2 fixup routines generated INFORMATIONAL messages that should be reviewed. *************** Post Upgrade Fixup Script Complete ******************** PL/SQL procedure successfully completed.

Recommendations displayed by the postupgrade_fixups.sql should be executed here. For this example, upgrade the time zone file and gather fixed objects stats.

13)
Execute utlu121s.sql

SQL>  @utlu121s.sql PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. CATCTL REPORT = C:\app\salmqure\product\12.1.0\dbhome_1\cfgtoollogs\testdb\upgrade\upg_summary.log PL/SQL procedure successfully completed. Oracle Database 12.1 Post-Upgrade Status Tool           03-25-2015 11:36:42 Component                               Current         Version  Elapsed Time Name                                    Status          Number   HH:MM:SS Oracle Server                          UPGRADED      12.1.0.2.0  00:13:01 JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:05:29 Oracle Workspace Manager                  VALID      12.1.0.2.0  00:00:50 OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:19 Oracle OLAP API                           VALID      12.1.0.2.0  00:00:16 Oracle XDK                                VALID      12.1.0.2.0  00:00:44 Oracle Text                               VALID      12.1.0.2.0  00:00:50 Oracle XML Database                       VALID      12.1.0.2.0  00:05:42 Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:12 Oracle Multimedia                         VALID      12.1.0.2.0  00:02:07 Spatial                                UPGRADED      12.1.0.2.0  00:11:12 Oracle Application Express                VALID     4.2.5.00.08  00:25:48 Final Actions                                                    00:01:41 Post Upgrade                                                     00:03:44 Total Upgrade Time: 01:12:52 PL/SQL procedure successfully completed. SQL> SQL> -- SQL> -- Update Summary Table with con_name and endtime. SQL> -- SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,   2                                  con_name = SYS_CONTEXT('USERENV','CON_NAME'),   3                                  endtime  = SYSDATE   4         WHERE con_id = -1; 1 row updated. SQL> commit; Commit complete.

Everything is fine and all components have been upgraded. If there is any error message faced during upgrade or message displayed in the output of utlu121s.sql, we would need to run catuppst.sql script manually. If there is no error during upgrade, this catuppst.sql is executed as part of the upgrade process. C:\temp\catupgrd0.log file should show any error message generated during the upgrade and check whether catuppst.sql was run or not. As mentioned in Oracle upgrade guide, if we find text “Rem BEGIN catuppst.sql” in the log file, it means this script was executed as part of upgrade.

If we have not deleted OLAP before the upgrade, we will see following information for OLAP in the output of postupgrade_fixups.sql

OLAP Catalog                         OPTION OFF      11.2.0.3.0  00:00:00

If you are upgrading 11g to 12.1.0.1, there could be error messages during upgrade if OLAP was not deleted before the upgrade, and because of these errors, catuppst.sql would not be run and would require to be run manually. There could be other errors as well which would require catuppst.sql to be executed manually. If catupgrd.sql was executed from SQLPLUS, then catuppst.sql was also not run - and in this case, you would also required to run catuppst.sql, manually as follows.

C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%ORACLE_HOME%\ perl/bin/perl catcon.pl -n 1 -b catuppst  catuppst.sql

14)
Lastly, execute utlrp.sql script to compile if there are any invalid objects. Again we can do this in parallel (which also compiles packages in all containers for a CDB) or using a conventional way

Parallel, using catcoon.pl C:\app\salmqure\product\12.1.0\dbhome_1\RDBMS\ADMIN>%oracle_home%\perl/bin/perl catcon.pl -n 2 -b utlrp  utlrp.sql Conventional way SQL> @utlrp.sql

Set COMPATIBLE parameter value to 12.