Proper way to upgrade Oracle database

oracleupgrade

Two questions:

  1. I have 11.2.0.3 database. I need to upgrade to a higher version either 11.2.0.4 or 12.1.0.2. Which one I should upgrade it to? Im not going to upgrade on the same server. I will have to do it in a new server. So how can I proceed with the upgrade? Export/import? DBCA? Manual upgrade? What is the procedure to do this?

  2. Same as above question except that this is another database but 10g. Which version I should upgrade it to and how to?

Added info::

I will be moving from OEL 5 to 6 or above.

Downtime I will get over an weekend.

No change in character set or licensing features.

Database size is approx 50 GB.

This is only development instance and not prod.

Should I install 12c database and then import everything from 11g to 12c database or run DBUA?

Best.!

Best Answer

Version 10 is out of support and 11.2.0.4 is out as well except for those who paid for extended. Upgrading to 12 is the best choice if you want a database that is supported with security patch sets and bug fixes.

Many other factors need to be considered as well and some are outlined in this answer here:

  • downtime allowed: an hour, a day, a weekend?
  • are you changing character sets (if you are will need to use the oracle tool to check for possible issues)
  • are you adding or removing licensed features? Example: Enterprise to Standard means partitioning is not allowed
  • databases with a large code base can have migration issues where things don't work the same way. QA and testing need to be done.
  • changing operating systems? Directory paths need adjusting
  • what other systems interact with the database? They all need to have configuration changes too
  • what resources do you have to test your migration? Believe me you will want to do it more than once. VMWare snapshots allow a go-back facility that is handy.

Migration paths

  • Easiest way is to create an installation script that can be used on the new production and development instances and install the latest version of Oracle.
  • Create table spaces to match your existing ones or take this opportunity to improve the organization of them.
  • use the data pump to export the contents from existing and import into the new
  • check that grants, directories, jobs and database links are copied over
  • test and test again

Given the specifics you have added to your answer the choice of the upgrade assistant or a clean install is still an "It depends" issue. The upgrade assistant makes things easier but does things like locking the user accounts that can catch one by surprise. If you want total control over the upgrade do a clean install and import. If you are not changing table spaces then try the assistant.

The important thing is to test and test again.

Related Question