Is your company’s IT infrastructure an out-of-date eyesore? The 11g support clock has been ticking since 2013, when Oracle released the major version upgrade for 12c. Like in any software cycle, Oracle has been phasing out support for version 11g.
If you are still using version 11g, chances are your database infrastructure is outdated and needs an update. However, you will want to do this carefully in case you compromise your stored data. The most recent upgrade offered by Oracle is their 19c version. If you’re on 11g, you are very behind. Fortunately, we’ve got you covered. Here’s our step-by-step guide on manually updating Oracle 11g to 12c. You can then use that foundation to upgrade oracle 12c to 19c if you want.
Step 1) Check Release and Version
Make sure you are using Linux.
The syntax to check your version is usually:
SELECT * FROM v$version;
You will want to make sure you are using
- Oracle Database 11gR2 (11.2.0.2.0) (64-bit)
- Running on Oracle Enterprise SUSE Linux
This tutorial will teach you how to upgrade Oracle 11g to 12c using dbua (a database upgrade assistant).
This is probably the simplest way to upgrade your database version.
Step 2) Check Prerequisites
There are a few prerequisites that you should check before upgrading, or else the process may fail.
Here are some things that you should check.
Gather Dictionary Statistics
You will be able to do this using an SQL prompt.
Input the following:
CONN / AS SYSDBA EXEC
DBMS_STATS.GATHER_DICTIONARY_STATS;
For Container Databases
If your Oracle database uses containers, you will want to use the following command to perform the actions in all containers.
This will gather your dictionary statistics across all your containers.
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gather_dict_stats — –x”EXEC DBMS_STATS.gather_dictionary_stats”
Purge Recycle Bin
You will want to purge your recycle bin as well. This can be done using an SQL prompt.
Input:
CONN / AS SYSDBA
PURGE DBA_RECYCLEBIN
Compile Invalid Objects
Again this can be done by inputting an SQL prompt.
CONN / AS SYSDBA
@?/rdbms/admin/utlrp.sql
Compile for Container Databases
This is the prompt you will want to use to compile invalid objects for container databases. This will execute the action across all your containers.
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b comp_invalid_objs — –x”@?/rdbms/admin/utlrp.sql”
Step 3) Stop the Database and Listener
Next, you want to stop the database from running.
Input the following into the command prompt.
oracle&hostname > sys /as sysdba
SQL> shutdown immediate;
oracle& hostanme> lsnrctl
LSNRCTL> stop
This will stop the database as well as the listener.
Step 4) Check Version and Components Again
Use the previous step to check the version of oracle and then refer to this link to check the components in $ORACLE_HOME.
You will also want to set the new ORACLE_HOME.
You can do this by typing in:
hostname> export ORACLE_HOME=oracle/SID/12.1.0
If you have completed Steps 1-4, you should now be ready to run the 12c installer.
Step 5) 12c Installer
Next, you’ll want to go to the 12c media directory and run the following command.
oracle&hostname> ./runInstaller
This should pull up the installer.
- Once you’ve navigated to the installer window, click Next
- In the next window, click Next again and select the “Skip software updates” option
- Click Next again and select “Upgrade an existing database.”
- What this should do is automatically launch the DBUA to help you upgrade your existing database
- You will want to select a language (e.g. English) and click Next
- You should select the “Enterprise Edition” and click Next
- Click Next again at “Specify Installation Location”
- Click Next again at “Privileged Operating System groups”
- The installer will then ask you to perform prerequisite checks again and click Next
- On this final page, all we have to do is hit Install
Once the progress bar is at 100%, Oracle 12c should be downloaded and installed. This is not all there is to it, though. You will still have to upgrade from 11g to 12c.
Step 6) Use DBUA to Upgrade 11g to 12c
The next few steps will guide you through the process of using the installer to upgrade your database.
The DBUA supports an 11g to 12c upgrade because the direct upgrade is supported according to Oracle’s upgrade matrix.
You can follow the instructions in the DBUA to interactively install the upgrade or simply follow this guide.
- On the first page, “Select Operation,” and click Next
- Click Next again at “Select Database”
- At “Prerequisite Checks,” ignore and hit Next again
- Hit Next again
- At “Upgrade Options,” click Next again
- At “Management Options,” you will want to select the “Configure Enterprise Management” button and click Next again
- At “Move Database Files,” hit Next
- At “Network Configuration,” hit Next
- At “Recovery Options,” select the option “I have my own backup” and click Next
Lastly, on the summary page, click Finish. This should again show a progress bar. Once this progress bar is at 100%, the database should be updated to version 12c successfully.
Check that the version has been upgraded by inputting:
SQL> select * from v$version;
This should show that you are on Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production.
Upgrade Oracle 12c to 19c
From here, you can also upgrade to oracle 19c. We will not cover the steps to do so in this guide, but the process should be similar.
Keep in mind that you want to have an upgrade timeline of 6 months to a year if you are still using Oracle 11g. The latest Oracle release is 21c; not upgrading your IT infrastructure could be costly for your company down the line as older versions of Oracle will become de-supported.
Have Any Questions?
While upgrading databases is complex, your company should aim to do so in a reasonable time frame.
Oracle supports direct upgrades from 11g to 12c, which can be done with their DBUA or database upgrade assistant. From there you can upgrade oracle 12c to 19c.
Hopefully, this guide was helpful in helping you get your IT up-to-date.
Please contact us at Buda Consulting if you have any questions about updating your Oracle product. We are Oracle experts and are here to help you at any time.