Best Practice

Almost every week Roy, Carol and I receive one or more emails in the following style:

“Hey, we (or my customer) plan(s) an upgrade to Oracle 11g. We (or the customer) wants zero downtime. Currently we (or they) are on AIX with Oracle 10g (and someold  9i) databases. Can we get an advice please?”

or another one here …

“Upgrade from 8i to 11g. The customer’s database is 28 TB (quite big!). Downtime is 5-6 hours. It’s on AIX. And it’s an it’s an Oracle EBS database”

Well, in both cases we lack a lot of useful information – or …

Having a corruption somewhere in the database is one of the worst case scenarios I could ever imagine – especially if it “sleeps” somewhere in the data dictionary. Recently I did talk to a customer who encountered a failing upgrade due to a data dictionary corruption gotten introduced in an earlier release.

What can you do to check your database(s) prior to an upgrade or generally from time to time? Actually I know now two powerful possibilities:

• hcheck.sql
  See MOS Note:136697.1
  This script will check for known problems in Oracle8i, Oracle9i, Oracle10g
  and Oracle 11g.
  You will need

Most people think of Data Guard as a disaster recovery solution, and it certainly excels in that role. However, did you know that you can also use Data Guard for platform migration under some conditions?

While you would normally have your primary and standby Data Guard systems running on the same OS and hardware platform, there are some heterogeneous combinations of primary and stanby system that are supported by Data Guard Physical Standby.

One example of heterogeneous Data Guard support is the ability to go between Linux and Windows on many processor architectures. Another is the support for environments that …

Do you understand this one here 😉 ? No??

Don’t worry 🙂 I’ve taken the picture last week when we’ve run a very special internal training called the “Upgrade Tech Challenge” here in Munich. This was actually the upgrade plan to 11.2 of one of the groups involving a Real Application Testing run with SQL Performance Analyzer (SPA) and taking care about the fallback as well. And as all groups completed their challenge by Friday I’d suppose at least the group itself understood what they wanted to do here 😉…

We are currently running a Tech Challange Workshop with 25 Oracle consultants and support folks from all over EMEA. We call it Tech Challange because we seperate these experts having between 5 and 20 years of Oracle experience into 5 groups – and each group has to complete their special challange such as moving a database from 10.2 to Exadata V2 or upgrading from single instance 10.2 to Real Application Clusters 11.2 with the new Grid Infrastructure.

Actually we start this training with a bit presentation pieces about upgrades, Real Application Testing and Golden Gate. And one topic I …

We refer in our upgrade talk to gather system (aka workload) statistics to give the optimizer some good knowledge about how powerful your IO-system might be. The optimizer since Oracle 9.2 takes CPU and IO costing into consideration. If you never have taken care on these stats you’ll find default values in AUX_STATS$. These values have been defined a few years back. So it might be a good idea right a few days after upgrading to the new release to create them while a real workload is running. Taking these stats does not generate any overhead or performance degredation …

It’s cleanup time!!

When upgrading to a new database release, especially Oracle Database 11g Release 1 or Release 2, you should always remove old init.ora parameters from the init.ora/spfile when you upgrade. For instance we’ve had a lot of useful (and necessary) tuning parameters in Oracle 9i environments such as _always_anti_join=off, star_transformation=false or several events.

Make sure you remove all of them when you upgrade as keeping them will definitely slow down the database performance in the new release. You don’t believe this? Then see this result of a customer case having a packaged OLTP workload for the …

You might ask yourself what an Oracle database upgrade and mountain hiking have in common?

On first sight: really nothing. But if you come a bit closer and look more into the details there are some similarities. Maybe more than you’d thought at the beginning.

Being well prepared is of vital importance for your upgrade success!

First of all, likewise a hike an upgrade has to be prepared very well. You probably won’t do an 5 hour hike without reading a tour description beforehand, grab a good map, check your equipment the day before. And with an Oracle upgrade it …

It seems to be a simple question but to predict the upgrade duration is really hard to do.

Due to the fact that an Oracle database upgrade doesn’t touch or change data the duration is totally independent of the size of the database. It won’t make any difference if the database is 2 GB or 28 TB of size.

The main factors determine the upgrade duration in descending order are:

• Number of installed database components and options – the more components/option gotten installed, the more upgrade scripts will have to be run, the longer it’ll take

• Valid and non-stale

Having a valid and complete backup before you start your upgrades is key.
But how do you plan your fallback strategy?

The first question you’ll have to ask is:
Are you allowed to loose any data updates in case of going back – Yes or No?

If you say “Yes we can!” because you would be able to redo any changes you’ve done to the upgraded system already once you’ll go back, then restoring your backup from before the upgrade could be your easy fallback strategy.

You don’t have the possibility to do a complete online …

Yes, I appologize for writing this sentence … “Backup your database!!!” … it sounds a bit frumpy, such as something from the past. But still nowadays with those high-class super storage solutions a valid backup can become of vital importance. Especially before you start the upgrade process.

RMAN (Oracle Recovery Manager) does a great job in backing up your database completely online. Have I said online? Yes!! Even though some Metalink notes still recommend to do an offline backup you shouldn’t stop your 58 TB data warehouse database for 2 days to do a complete offline backup. Use …

Customers and Oracle partners were asking for a long time about a comprehensive note about upgrades. Often questions were raised such as “Why has this default setting changed and where has it been documented?“, “How do I preserve performance information before the upgrade to ease diagnosis just in case something happens?” or “What’s new with the optimizer?“.

To answer these and many more questions in late 2007 the first Upgrade Companion for Oracle Database 10gR2 has been published on Metalink. Later the Companion for 11g got published as well – and as soon as …