Best Practice

Gathering Workload Statistics

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 …

Continue reading...

Remove “old” parameters and events from your init.ora

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 …

Continue reading...

What does an upgrade has in common with mountain hiking?

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 …

Continue reading...

How long will your upgrade take?

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
Continue reading...

Is there a FALLBACK strategy?

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

Continue reading...

Backup your database!!!

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 …

Continue reading...

Upgrade Companion – Read it first!

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 …

Continue reading...