Proper way to upgrade Oracle database

Question

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.!

Answer 1

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.

Answer 2

If you consider to go to the 12c ,you can do it either with expdp/impdp or Transport Database option (available only for 11.2.0.3 onwards). We actually do it manually on the same server (recommended). If you consider the 11.2.0.4 option you can again use expdp/impdp or clone the 11.2.0.3 on the new server and upgrade it either manually or with DBUA. Regarding the 10g "Direct upgrades to 12c are possible from existing databases with versions 10.2.0.5+, 11.1.0.7+, or release 11.2.0.2+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version."

Answer 3

I see in another one of your threads that you use Dataguard. In that case, may I suggest you look into rolling upgrades using a Transient Logical Standby Database (11gR1 onward, so this won't be an option for your 10g DB):

http://www.oracle.com/au/products/database/maa-wp-11g-transientlogicalrollingu-1-131927.pdf

This has 2 advantages:

1) you shoulder the responsibility of the upgrade itself while your primary DB is up and running... so if you run into any issues with the upgrade process, you can fix said issues without affecting your primary DB.

2) The actual downtime needed to perform the switch over is about 2 minutes. Compared to an exp/imp approach which could take hours to complete (depending on the size of the DB), and that is if you don't run into any issues.

There are a few gotcha's with a Logical Standby vs a Physical Standby. With a Logical Standby, the database is open in Read/Write mode, so you will want to turn off any triggers that could update data before you transition the DB to a Logical Standby, or they may interfere with the SQL Apply. Because of this, turning off the Job scheduler isn't a bad idea either (just make sure you enable anything you turned off when you switch-over to the primary role).