Upgrade database 11g to 12c

Here we will upgrade a database from Oracle 11g to Oracle 12c.

The name of the 11g database is orcl11g and the 11g database home is located in /u01/app/oracle/product/11.2.0/db_1 and the 12c database home is located in /u01/app/oracle/product/12.1.0/db_1 .

First check the version of the orcl11g database:

$ . oraenv

[orcl11g]

$sqlplus / as sysdba

SQL> SELECT version FROM v$instance;

VERSION

----------------

11.2.0.3

SQL> exit

Now check if all the pre-requisite to perform the upgrade operation are done by executing the script preupgrd.sql script present in the /u01/app/oracle/product/12.1.0/rdbms/admin directory.

$ cd /u01/app/oracle/product/12.1.0/rdbms/admin

$ sqlplus / as sysdba

SQL> @preupgrd.sql

SQL> exit

Note that the preupgrd script is being executed from the 12c but sqlplus is being executed from 11g home sqlplus NOT from 12c home.

The above script will generate a log name preupgrade.log in the /u01/app/oracle/cfgtoollogs/orcl11g/preupgrade directory.

To determine the steps to perform before the upgrade and after the upgrade you can go through the content of the log using any editor. In my case I used gedit.

$ gedit /u01/app/oracle/cfgtoollogs/orcl11g/preupgrade /preupgrade.log

There will a couple of steps that the log file will point out to perform before starting the upgrade operation. Mainly:

1. Set the number of PROCESSES to 100 or more.

2. Deconfigure the Enterprise Manager database console in case it is configured for the 11g database.

3. Gather the dictionary statistics for the 11g database.

4. Remove the exisiting OLAP objects from the database.

The solution for them will also be present in the same log file.

To increase the number of processes execute:

$ sqlplus / as sysdba

SQL> ALTER SYSTEM SET PROCESSES=100 SCOPE=SPFILE;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

SQL> exit

To deconfigure the enterprise manager database console first stop the database console and then execute the emremove.sql file created by preupgrd.sql script

$ emctl stop dbconsole

$ sqlplus / as sysdba

SQL> @emremove.sql

To gather the dictionary statistics, still connected to sql as the sys user, execute the command

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

SQL> exit

Finally to delete the OLAP objects execute the catnoamd.sql script present in the /u01/app/oracle/product/11.2.0/db_1/oraolap/admin

$ cd $ORACLE_HOME/oraolap/admin

$ sqlplus / as sysdba

SQL> @catnoamd.sql

SQL> exit

Now that all the pre-requisite has been met open a new terminal navigate to the 12c home and execute the dbua [Database upgrade assistant]. Note do not set up the oraenv.

$ cd /u01/app/oracle/product/12.1.0/db_1/bin

$ ./dbua

In the first screen it will prompt us what we want to perform. We will select the option to "Upgrade Oracle Database"

In the next screen it will prompt for the source and target directory. By default it will show the 12c home as both source and target directory. It will also show any existing 12c database here

We will have to change the source home to that of 11g and dbua will automatically pick up any existing 11g database

It might take some time for the wizard to gather the information

Once done it will perform the pre-requisite check

If it finds any issue it will inform you in the next screen

In order to fix it all you have to do is click on the "Apply Action" at the top of the screen

Then it will give the option to specify the degree of parallelism to perform the upgrade operation and for recompiling the invalid objects

Next it will prompt you to specify if you want to configure the Enterprise Manager Database Express or register it to a Cloud Control. Since we do not have a database cloud control here we will go for the database express

In the next screen you get the option to move the datafiles and the backup files to another location as a part of the upgrade operation. Here we are not opting for this option