Thursday, February 27, 2014

Database UpGrade ("DBUA")

Upgrading a database is one of the reponsibilities of a database administrator (DBA). In most open source databases, it is much easier to upgrade the database whether you're a DBA, Developer or System Administrator. In Oracle, however, the process is somehow different. It is much more complex involving different steps.

In this post, we will look out the easy method of upgrading a database from one version to another higher version. I am using 11.2.0.3.0 to 11.2.0.4.0

In Oracle 11g, database upgrade can be invoked using the "DBUA" - an acronym for Oracle Database Upgrade Assistant.

Patch versus Upgrade
What is a Patch? How is this different from an upgrade?
Take note that an Oracle Patch is more of an update for newer security and performance fixes whereas upgrade is a total reconfiguring of the database or software version. You can login into Oracle Support with your userid and identifier to download an Oracle Patch.

Oracle version numbering
11.2.0.4.0 is the most current upgrade available for Oracle 11g to date. The "11" stands for the database version number, "2" is the release number, "0" is application release number, "4" is the component release number, which is the main upgrade we are interested in. The last "0" is the platform specific number. You can update this with Opatch. See Oracle documentation on this topic here: Identifying Your Oracle Database Software Release

Upgrading Database from 11.2.0.3.0 to 11.2.0.4.0

Please follow this easy steps to upgrade your database to 11.2.0.4:

1. Make sure you have installed the 11.2.0.4.0 full installation with a new ORACLE_HOME. This is a full installation
2. Take backup of database
3. Export new ORACLE_HOME and adjust /home/oracle/.bash_profile
6. Open a terminal and run "DBUA" to upgrade database

PLEASE NOTE

To upgrade a database, the database must be in "Open" mode before you start. If your database is in "mount" or "nomount" state, you will run into this error.


You have to start your database and ensure that it is open before you continue.


Launch the "DBUA" from the terminal


 
Choose the right database you want to upgrade. In this example, I have more than one databases with version 11.2.0.3.0. I chose the last one.


Confirm the operation. Click "Yes"


 
Here you can choose the "Paralellism" for the process. This implies the speed to accomplish this upgrade process.


Here is an opportunity to migrate the datafiles from their present location to a new one. DBUA can do this for you during the upgrade. Remember that almost all database settings can be adjusted during an upgrade. You can also let DBUA take a backup of the database for you before the upgrade.


Please "Browse" to select the right directory to move the datafiles to. If you do not want to move datafile to a new location, simply ignore this step.

 
 Change the size of the Fast Recovery Area (FRA). This size will control the backup repository. If you miss this setting, you will have to change the setting after the database has been upgraded by using the "Alter System Set" command.


 
This screen shows a summary of the Database Upgrade options selected before the process is started. You can still make adjustments by clicking the "Back" button. At this stage, you want to verify the Database name, version and ORACLE_HOME.

 
Click on "Next" and the process will start

 
Wait for the process to complete. Once the process completes 100%, you will click "Ok"

 
This is the feedback page. Here you need to verify that the upgrade has been done.
If for any reason you need to rollback the upgrade process, click on "Restore Setting Only".
If you need to change passwords, click on "Configure Database Password"


 
Here you log in to verify the version. In addition to the command shown in the image, you can also run this command:

SELECT  *  FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                                 VERSION     STATUS
-------------------------------------------------------- ----------- -----------
NLSRTL                                                       11.2.0.4.0  Production
Oracle Database 10g Enterprise Edition       11.2.0.4.0  Prod
PL/SQL                                                         11.2.0.4.0  Production

Thanks for reading!