Oracle DBA Tip: Single Instance Upgrade to the Latest Oracle Database 12c
If you're using outdated Oracle software, you might consider upgrading. Fortunately, with a bit of legwork in the beginning, upgrading to 12c isn't too much of a challenge.
Join the DZone community and get the full member experience.Join For Free
There were quite a few releases by Oracle RDBMS starting from Oracle v2 (there was no Oracle v1, as they assumed no one may want to buy a version 1), to the latest Oracle database 11g and then Oracle Database 12c. With each release of the Oracle database, there had always been an upgrade path that needed to be followed by the users.
There are many who are overconfident and blindly rush in as soon as a new release comes out. They try to download or upgrade even before knowing what exactly they need to do. However, this may, most of the time, lead to disaster, which will be much severe in terms of a business or enterprise environment. Throughout 2016, we saw the two latest Oracle Database versions:
Oracle Database 12c - Enterprise Edition (EE)
Oracle Database 12c - Standard Edition 2 (SE2)
Oracle Database 12c - Enterprise Edition: As Oracle’s flagship database suite, EE is a full-featured edition of Oracle RDBMS with the option to get add-on features like database options and management packs with complete exploration of the server resources.
Oracle Database 12c - Standard Edition 2: This is a lighter version meant for small and medium size implementations. SE2 comes with a limited set of DB features but with access to Real Application Clusters. Oracle introduces SE2 as a starter version, with easy upward compatibility to the higher Edition.
Upgrading to Oracle Database 12c
For those who are using older versions of Oracle databases, the upgrade path to Oracle Database 12c is straightforward. However, if you are using a version that does not support a direct upgrade, then you have to first upgrade it to a supported version and further to Oracle Database 12c. As of late, the direct upgrade is supported from:
Oracle Database 10g
Oracle Database 11g
There are three options for upgrading:
DBUA: Database Upgrade Assistant.
Script based manual upgrade.
In this article, we will focus more on how to use the Database Upgrade Assistant, which is a widely used method for upgrading.
Database Upgrade Assistant
Oracle has lately enhanced the DBUA by ensuring options for a seamless upgrade. The users now have the option to fix any errors directly from the DBUA, and additionally, it makes monitoring the upgrade much easier and in real-time. In order to use DBUA, users need to access the home page of Oracle Database 12c and start by running the DBUA. The best practice is to run the preupgrd.sql to see what may have to fix before running the assistant.
For running ‘preupgrd.sql’, one first needs to install new binaries into Oracle DBA home. Once that's done, set up the environment to connect it to the database needed to be upgraded.
As a sample, we will take the immediately previous Oracle Database 11g settings:
ORACLE_SID=ora 11g ORACLE_BASE=/opt/oracle ORACLE_HOME=/opt/oracle/product/220.127.116.11.0/db_1
Then access the directory in which the preupgrd.sql file is available.
After this, we have to connect the 11g database with SQL*Plus to run preupgrd.sql
#>sqlplus / as sysdba SQL>@preup grd.sql
After running the script, we can get the file locations, which are needed for reference to verify and correct any issues with the environment later.
The results of this check may be located at:
/opt/oracle/cfgtoollogs/ora11/preup grade/preup grade.log
The pre-upgrade fix-up script run at the source database environment is:
/opt/oracle/cfgtoollogs/ora11/preup grade/preup grade_fixups.sql
The post-upgrade fix-up script run after the upgrade is:
/opt/oracle/cfgtoollogs/ora11/preup grade/postup grade_fixups.sql
Once reached up to this level, you can review these scripts to correct if anything requires additional fixes. Once these fixes are done properly, running DBUA will become much simpler. The errors listed in preupgrade.log also needed to be corrected before running DBUA.
Once preupgrade.log is reviewed and corrected, next one can start with Database Upgrade Assistant.
Log on to Oracle Database 12c home and then run the DBUA:
#> cd/opt/oracle/product/18.104.22.168/dbhome_1/bin #> ./dbua&
Running this will initiate the GUI to start automatically and get upgraded. There is no need to change anything in the environment, which can still be the 11g set up.
ORACLE_SID=ora11g ORACLE_BASE=/opt/oracle ORACLE_HOME=/opt/oracle/product/22.214.171.124.0/db_1
Once the DBUA starts, just follow the steps below.
Upgrade Oracle database > Move the existing 12c database to home of 12c Oracle > Choose upgrade, click next.
We need to identify which Oracle home needs to be upgraded. As we have it set to 11g home, in this case, the DBUA will show all databases associated with the 11g Oracle home. Select which database needs to be upgraded and click next.
The results may be similar to what we saw at preupgrade.log. The DBUA also is running the same preupgrd.sql script and will return with the results. However, the assistant is made to be more intelligent with the capability to allow users to select whether the issues to be fixed, ignored, or revalidated.
Oracle has made an interesting change here as the upgrade can be done in parallel. This is made possible with the help of intelligent Perl script called catctl.pl. We can also recompile objects in parallel and let the DBUA gather statistics, perform upgrades, and make the tablespaces read-only as the upgrade runs.
This step will allow users to manage the Oracle Database 12c environment. You can use EM Express or can register the DB with OEM 12c (Oracle Enterprise Manager). The DBUA will automatically pick the needed information if OEM12c agents are already installed on the server.
Users can specify where the data files need to be moved and set up the Fast Recovery Area (FRA). You can also configure the Oracle Managed Files (OMF).
This step is designed to help users to migrate the 11g listener to 12c. The ‘migrate’ column on the screen at this step is very important. This is what tells you whether the listener is going to be migrated.
Now, users have the option to create a backup of the database before the upgrade runs. You can also instruct the DBUA by choosing a radio button not to do a backup if you are confident about your existing backup practices.
We've reached the summary screen, which will show what the DBUA plans to do. Review this screen carefully to make sure that everything is in order before clicking the ‘Finish’ button to start the upgrading process. Once clicked, the upgrade will start and we can see that status on the progress screen.
Once the upgrade is completed, the stop button on the window will change to ‘Upgrade Results.’ Click on this and you will see the interface changing to provide the results of the latest upgrade. The easiest way to verify the upgrade is to check ‘etc/oratab’ file. If the upgrade is properly done, then this oratab file may have changed the Oracle home in sync with the 12c binary location.
I hope this article provided you with the easiest and most user-friendly way of upgrading Oracle from the previous 11g version to the latest Oracle Database 12c version.
Opinions expressed by DZone contributors are their own.