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"
![](https://static.wixstatic.com/media/ee2524_f0fb6eaa595d488cac3c3b7bc2d27fdb~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_f0fb6eaa595d488cac3c3b7bc2d27fdb~mv2.jpg)
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
![](https://static.wixstatic.com/media/ee2524_a7883022db5b44c1a2174b036f89eac0~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_a7883022db5b44c1a2174b036f89eac0~mv2.jpg)
We will have to change the source home to that of 11g and dbua will automatically pick up any existing 11g database
![](https://static.wixstatic.com/media/ee2524_789048dae32247c1a07d559c96be3e9c~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_789048dae32247c1a07d559c96be3e9c~mv2.jpg)
It might take some time for the wizard to gather the information
![](https://static.wixstatic.com/media/ee2524_f958823f7dd84976bd7f2582083904e6~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_f958823f7dd84976bd7f2582083904e6~mv2.jpg)
Once done it will perform the pre-requisite check
![](https://static.wixstatic.com/media/ee2524_633be09826f4483287d53ee0b2541cde~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_633be09826f4483287d53ee0b2541cde~mv2.jpg)
If it finds any issue it will inform you in the next screen
![](https://static.wixstatic.com/media/ee2524_f56d1c300bee42f8a88ae7d7c6080411~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_f56d1c300bee42f8a88ae7d7c6080411~mv2.jpg)
In order to fix it all you have to do is click on the "Apply Action" at the top of the screen
![](https://static.wixstatic.com/media/ee2524_acdb81a67d51467b9071740942ed0683~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_acdb81a67d51467b9071740942ed0683~mv2.jpg)
Then it will give the option to specify the degree of parallelism to perform the upgrade operation and for recompiling the invalid objects
![](https://static.wixstatic.com/media/ee2524_f0fb6eaa595d488cac3c3b7bc2d27fdb~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_f0fb6eaa595d488cac3c3b7bc2d27fdb~mv2.jpg)
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
![](https://static.wixstatic.com/media/ee2524_d096dcf1d84f403d9c12bc6b21d3f76d~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_d096dcf1d84f403d9c12bc6b21d3f76d~mv2.jpg)
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
![](https://static.wixstatic.com/media/ee2524_d40ecddf6154481f87cc85ef6e182b11~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_d40ecddf6154481f87cc85ef6e182b11~mv2.jpg)
Then it will provide you with a list of existing listener and you have the option to select which listeners will mapped against the upgraded database
![](https://static.wixstatic.com/media/ee2524_85202af65bea40d3a8ee9e140ca96aee~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_85202af65bea40d3a8ee9e140ca96aee~mv2.jpg)
It will then ask what strategy you want to adopt in order to perform recovery in case the upgrade operation fails
![](https://static.wixstatic.com/media/ee2524_8f68796f41824feda0dfcd5292ed20d6~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_8f68796f41824feda0dfcd5292ed20d6~mv2.jpg)
It will now give you a summary screen where you can review your database definition
![](https://static.wixstatic.com/media/ee2524_0959ebc0b84b4fc7a9b5f4f8c31dc2e8~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_0959ebc0b84b4fc7a9b5f4f8c31dc2e8~mv2.jpg)
Next it will start the upgrade process
![](https://static.wixstatic.com/media/ee2524_44dcd78341754f9285f5d76f6154c255~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_44dcd78341754f9285f5d76f6154c255~mv2.jpg)
![](https://static.wixstatic.com/media/ee2524_a7f3ff3656a94aeb9b18e59757e47430~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_a7f3ff3656a94aeb9b18e59757e47430~mv2.jpg)
![](https://static.wixstatic.com/media/ee2524_c29aabef1edb4fa898dd95ecd796e126~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_c29aabef1edb4fa898dd95ecd796e126~mv2.jpg)
Once the upgrade is complete it will give you a Upgrade Result screen confirming that the upgrade was successful
![](https://static.wixstatic.com/media/ee2524_a8e2a5b8f1644223847c4c37d8e3177e~mv2.jpg/v1/fill/w_980,h_551,al_c,q_85,usm_0.66_1.00_0.01,enc_auto/ee2524_a8e2a5b8f1644223847c4c37d8e3177e~mv2.jpg)
Now let's connect to sqlplus and confirm that the upgrade was successful
$ . oraenv
[orcl11g]
$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1
$ sqlplus / as sysdba
SQL> SELECT version FROM v$instance;
VERSION
---------------
12.1.0.1.0
Oracle recommends to gather the dictionary statistics after the upgrade has completed.
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> exit
A video to the above steps can also be found in my youtube channel:
Thanks for viewing...hope it helped !!!