Sunday, June 30, 2013

Upgrade Oracle Database to Using DBUA

Upgrading Oracle Database to Using DBUA

New Features

I will list out some of the new feature for upgrading your databases to 12c. You can review the complete details of the new feature by clicking the link in the references section.

  • New Pre-Upgrade Information Tool- A new Pre-Upgrade Information Tool has been created for this release. The preupgrd.sql script replaces the utlu121s.sql script. 
  1. Parallel Processing for Database Upgrade- In Oracle Database 12c, the catupgrd.sql Upgrade Utility has been replaced with the new Parallel Upgrade Utility.
  2. DBUA Can Be Restarted During the Upgrade Process- In this release, there are more options for recovering from failures during upgrade and for restarting the upgrade from the point where the failure occurred.
  3. Enhanced Pre-Upgrade and Post-Upgrade Summary Report- DBUA generates HTML reports before and after the upgrade.

Upgrade Database

Make sure you have a full valid database backup.

  • Make sure your database is in archivelog mode
  • Take a full backup using RMAN.

Pre-Upgrade Information Tool

Copy SQL scripts preupgrd.sql and utluppkg.sq

  1. $cd <12c_home>/rdbms/admin
  2. $cp preupgrd.sql /home/oracle/scripts/
  3. $cp utluppkg.sql /home/oracle/scripts/
  4. Under your ORACLE_HOME/cfgtoollogs or ORACLE_BASE/cfgtoollogs create directory <db_unique_name>/preupgrade 

Execute Pre-Upgrade Scripts

  1. Login to the database you want to upgrade with SYS account
  2. Execute the preupgrd.sql script from the location where you copied the file.
  • SQL>@preupgrd
Example Output

Review Fixup and Log Files

Example Output from Preupgrade.log

After you have reviewed the scripts, Oracle recommends that you execute preupgrade_fixups.sql on the source database. The preupgrade_fixups.sql script will attempt to resolve issues reported by the preupgrade process.

Execute the preupgrade_fixups.sql before upgrading your database.

Make any manual changes to your database as needed based on the output from the preupgrade.log

1. ./dbua

2. Select upgrade Oracle database click next

3. Select the source Oracle home and the database to be upgraded click next.

4. Prerequisite checks will begin

5.  Prerequisite check dialog will show any issue encounter during the checks and will have the option to fix it the validation is fixable. Click next

6. There is an option now to have the upgrade run in parallel so select the upgrade parallelism. Upgrade options can be set to recompile invalid objects during post upgrade. There are other options for Timezone, gather stats and set user tablespace to read-only during upgrade. There is options to change file location if needed. In the custom SQL scripts tab you can set a before and after custom SQL script to be executed.

Click next

7. Managment options can be set to configure EM Express of Cloud Control click next

In my case I left both unchecked.
8. In the move database files there are options to move the datafile and flash recovery area as part of the upgrade. Click next

In my case I left blank as I did not need to move my datafiles.
9. Network configuration give the options of registering to any listener running. Click next

In my case the database dbtest2 that I am upgrading is already registered to my default listener.
10. 12c DBUA now has the options of recovering from an upgrade failure. In the recovery options dialog you select different options for recovery. The select are RMAN backup creating a new backup or using existing backup. There is an option for flashback and guaranteed restore point (GPR). There is also the options of having your own backup and recovery strategy. Click next.

In my case I choose RMAN backup with existing RMAN backup.
11. Review summary and click finish
12. Upgrade will being and progress can be monitored.
12.1 Click the activity log button to review the detail progress 

12.2 Click the alert log button to review alert log entries during the upgrade process.

13. When upgrade is complete click upgrade results

14. Review results if there were any issues here you would have the option of restoring the database. Click close

15. Database has been upgraded

Once you feel that your upgraded database is good change the compatible to
SQL>alter system set compatible='' scope=spfile;
SQL>shutdown immediate

If you want to see this installation in a video demo see below from my YouTube channel.

Require Post Upgrade Task

Recommend and Best Practices to Complete After Upgrade

Follow me on Twitter



  1. HI sir,
    When I try to upgrade my db to, then the Source Oracle Home
    it doesn't show on step 2.
    Please help me to solve it.

    Best Regards,
    Kosal Kong

    1. For others.. since your question was 2 years ago..
      You need to have the source DB listed in the oratab, and the SID must be in CAPS

  2. Did you successfully install 12c database software?

  3. Just so we are clear which step 2 are you talking about?

  4. Below one is using manual method.

  5. Hi

    Thanks for your clear document !
    I done DB upgrade from 11204 to 12102 on OL 6.7 by by DBUA.

    >> But getting error .. please clarify it !

    I created a service by SRVCTL for 11204 2 node RAC Database.

    I just started by upgrade by DBUA
    Once 67% completed ... suddenly DBUA was throwing error ..

    Error was stated ...
    Some processes are active in Database HOME
    It was pointing exactly that service only ...

    Before upgrade process, service was stopped Explicitly.
    During upgrade (on which NODE i called DBUA, on that node service has started automatically ..

    >> Is this true ?

    Someone told me that before upgrade we need to remove services explicitly otherwise DBUA will throw error.

    Pls update

    Current DB: 11204, planned to upgrade to 12102 on OL 6.7

  6. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training

  7. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Financials . Actually I was looking for the same information on internet for Oracle Fusion Financials and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle Fusion Financials . By attending Oracle Fusion FinancialsTraining .