Wednesday, June 15, 2016

Upgrade Oracle Database 11g to 12c

Upgrade Oracle Database 11g to 12c

Few things we should know before upgrading Oracle Database 11g to 12c.
This upgrade process will modify the data dictionary to compatible with newer version of Oracle Database software i.e. 12c.

This activity includes,
  • Adding, dropping, or modifying columns in system tables and views
  • Creating new system packages or procedures
  • Modifying existing system packages or procedures
  • Creating, modifying, or dropping database users, roles, and privileges
  • Modifying seed data that is used by Oracle database components
All above activity will affect the data dictionary of your database. This will not affect the data stored in your user or application tablespaces.

Selecting a Database Upgrade. Below is the Metric which describe database version you can upgrade to Oracle 12c.

Database Release
Source Patch Set
Direct Upgrade to 12c
Oracle Database 11g Release 2 and later
NO, Use another Method.
Oracle Database 11g Release 1
NO, Use another Method.
Oracle Database 10g
YES and earlier
NO, Use another Method.
Oracle Database 9i and earlier
NO, Use another Method.

Here we can see some of Oracle versions are directly upgradeable to 12c and some are not.
How other version can be migrated to 12c ?
Option 1) Use Import/Export or Impdb/expdp
Option 2) Transportable tablespace

Here, In my below listing, I am having Oracle Database 11g, version and also Oracle database 12c, version installed on my Linux box.
Oracle database 12c is my CDB database and having one PDB also.

Our Goal

1) Upgrade Database 11g to 12c
[By default upgraded database will be non-CDB]
2) Migrate a Non-Container Database to a Pluggable Database (PDB)
[In other words we can say that add non-CDB to PDB in existing CDB.]
Click Here to directly go to Step 2 Migrate a Non-Container Database to a Pluggable Database (PDB)

NOTE: We will not change our SID i.e. ORCL11G while upgrading database to 12c.

Let's start, to upgrade 11g database to 12c.

Set your environment to,

export ORACLE_HOME=/mnt/devops_0/Oracle/product/12.1.0/dbhome_1

bash-4.1$ export ORACLE_HOME=/mnt/devops_0/Oracle/product/12.1.0/dbhome_1
bash-4.1$ export PATH=$ORACLE_HOME/bin:$PATH
bash-4.1$ dbua


Choose option "Upgrade Oracle Database."

Now, select the instance from the list. Here in our case it is "orcl11g".

It is checking for per-requisite. In background it was running script to check all parameters and db version etc.

Pre-check has given below two errors. In our case, we are not having EM repository & APEX hence we have ignored it and continue with next screen.

EM Repository check description

APEX check description

Here, we have selected the option "Recompile invalid object during post upgrade." It would reduce our post upgrade task by running script manually i.e. utlrp.sql

We have disabled the EM upgrade option.

We wanted to move our data files to new Oracle 12c location hence we selected move data file option and given path of new location.

Benefit of moving file location and recovery area is, in future you can de-install Oracle 11g and remove the all directory structure of 11g versions.
Hence it is recommended to move all your data files and recovery path to new Oracle 12c database path.

We have selected existing Oracle Listner.

It would always better to take backup before upgrading your database. If you are having backup of your database then you can choose option i.e. "I have my own backup and restore strategy". This will save your time for upgrade process.

Before continue with upgrade, check the summary of your upgrade and check source db, target db, all the path of data file, initialization parameters,  listener information, backup location etc...

Yes, now we have started actually Upgrade of Oracle 11g Database to 12c.

Now, we have completed this process. Click on "Upgrade Results" button to see the final result.
Below are some screenshot for the same.

All completed...
if you are not satisfied with the upgrade here you can restore your database by clicking "Restore Database" button.

Click Close button to close this window.

Let's crosscheck our Database by connecting to it.
Set your environment variables as shown in screenshot below i.e. ORACLE_HOME, ORACLE_SID, PATH  

Yes, we have successfully upgraded our database from 11g ( to 12c (
Note: by default upgraded database is non-CDB database. It is not having your Multitenant feature.

Once you complete this upgradtion you can migrate your non-CDB 12c database to PDB in existing CDB.

Follow this link for our second step of Migrate a Non-Container Database to a Pluggable Database (PDB)

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
Post a Comment