Best Practice

How long will my database upgrade take? Revisited …

I received this question twice within a few days. And I remember that a tiny little while ago I wrote something on the blog about it. But as this “while” is 11 years ago, I came to conclusion to refresh this topic – and write something about How long will my database upgrade take? Revisited …

How long will my database upgrade take? Revisited ...

Photo by Aron Visuals on Unsplash

Is there a query?

At first, before I go into details, an important fact upfront: There is neither any query nor any tool giving you a rough estimate or precise figure of how long your upgrade will

Continue reading...

ORA-1403 during TEXT / CONTEXT post upgrade

When you see an ORA-1403 during TEXT / CONTEXT post upgrade phase, regardless of using AutoUpgrade, dbupgrade, catctl or DBUA, no worries. Two people reported this to me this week. And my dear support colleague Klaus Herndl told me about the existing MOS note. But there is a bit more to explain, especially as the bug is non-public and the MOS note doesn’t tell you when this can happen.

ORA-1403 during TEXT / CONTEXT post upgrade

Photo by Moritz Schmidt on Unsplash

What happens?

This is the error pattern you may see during the post upgrade phase when Oracle Text is installed in your database:

Oracle Database 
Continue reading...

Be aware when you use _ORACLE_SCRIPT in scripts

Recently I came across this when I gave a Multitenant workshop at a German customer. As part of the workshop I explained the _ORACLE_SCRIPT statements you will find in almost all scripts in ?/rdbms/admin. And one of the DBAs gave me a bright smile. He mentioned: “We use it already everywhere to avoid strange errors”. But be aware when you use _ORACLE_SCRIPT in scripts. I will explain, why this can be dangerous.

Be aware when you use _ORACLE_SCRIPT in scripts

Photo by Justin Chrn on Unsplash

What does _ORACLE_SCRIPT do?

First of all, this is an underscore parameter. Hence, you won’t find it in the Oracle …

Continue reading...

Save Patching Downtime with Oracle Multitenant?

At OOW 2019 we announced that you can have up to 3 user-created pluggable databases (PDBs) per container database (CDB) without having a Multitenant license. And in this blog post I’d like to demonstrate how you can save patching downtime with Oracle Multitenant.

The Concept

The idea this entire process settles on is very simple. When you create a fresh container database in the target (already patched!) environment, you don’t have to patch this one. You only have to relocate the PDB from the source into the target CDB. In addition, only datapatch needs to be executed. As the …

Continue reading...

When and how should you change COMPATIBLE?

When and how should you change COMPATIBLE?

COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE?

What does COMPATIBLE do?

To find an answer to this question, I consulted the documentation at first. And I found this:

    Setting COMPATIBLE ensures that new features do not write data formats or structures
Continue reading...

Should you change COMPATIBLE when you apply an RU?

I have a million ideas for blog posts. But I like it even more when people ask me to explain something on the blog which isn’t there yet but may be interesting for others as well. And Robert Ortel mailed me the other day and asked if he should change COMPATIBLE when he applies an RU for Oracle 18c. That’s a good question. And I doubt that the documentation has a good recommendation.

Should you change COMPATIBLE when you apply an RU?


Should you change COMPATIBLE when you apply an RU?

That’s a short blog post this time, isn’t it? πŸ™‚ But honestly, you shouldn’t touch COMPATIBLE for …

Continue reading...

Creating CDBs and non-CDBs with less options

Creating CDBs and non-CDBs with less optionsI wanted to write up this blog post a long time ago. When Helen Vanderheide did ask me a few weeks ago if she can omit catoctk.sql during the creation of the database, I realized that I should update my previous blog posts about Creating CDBs and non-CDBs with less options. And of course, the following is usable with non-CDBs as well.

You’ll find an example and especially the component dependency matrix below as well as many links to related blog posts at the end. If you don’t want to read all this, just scroll down to the Summary

Continue reading...

New preupgrade.jar and changes in MOS Note:884522.1

We read and listen to your comments. And that’s why my team mate Byron has added a new preupgrade.jar and changes to MOS Note:884522.1. One reader commented that MOS Note:884522.1Β (How to Download and Run Oracle’s Database Pre-Upgrade Utility) does not explain how to “run” the preupgrade tool even though it mentions this in the note’s title.

New preupgrade.jar and changes in MOS Note:884522.1

New preupgrade.jar and changes in MOS Note:884522.1

With MOS Note:884522.1 you can download always the most recent versions of the Oracle 18c and 12.2 preupgrade.jar tools, of the Oracle preupgrd.sql and utluppkg.sql. And you’ll even find the last utlu112i.sql for upgrades …

Continue reading...

Oracle Database 18c: A new preupgrade.jar is available

In case you plan to upgrade a database to Oracle Database 18c: A new preupgrade.jar is available for download from MyOracle Support (MOS).

Oracle Database 18c: A new preupgrade.jar is available

Oracle Database 18c: A new preupgrade.jar is available

Please download it – as always – from MOS Note:884522.1.

The same MOS note will get you also the most recent versions of the preupgrade.jar for Oracle Database upgrades. Of course you can download also all previous incarnations such as preupgrd.sql for upgrades and even utlu112i.sql for 11g upgrades.

What’s new in preupgrade.jar for Oracle 18c upgrades?

There are a few important and good Improvements and …

Continue reading...

Multiple Hops – Which should be the intermediate release?

Multiple Hop Oracle Database UpgradesThis is a topic which doesn’t come up very often. But if it does come up from time to time, and the documentation may give you advice – but as I realized – it does not always give the best advice. When you have to do multiple hops – which should be your intermediate release?

I posted something about this topic a while back:

What are Multiple Hops?

We speak of multiple hops in relation to database upgrades when your source release does not allow you to upgrade directly …

Continue reading...

preupgrade.jar January 2018 (Build 11) is available now

A new version of the preupgrade.jar January 2018 (Build 11) is available now.

Please download it from MOS Note:884522.1.:

preupgrade.jar January 2018 (Build 11) is available now

MOS Note: 884522.1 – How to Download and Run Oracle’s Database Pre-Upgrade Utility

Please always download the most recent version as this one is much newer with a lot of improvements compared to the version you get with the default installation. Major changes since the first drop are:

  • Added load only capabilities.
  • Improved the interaction with OS
  • Added more obsolete/deprecated parameters
  • Improved IPC calls on Windows
  • Added functionality for aud$unified table partitions.
  • Added preupgrade and postupgrade automatic fixups
  • Check for
Continue reading...

Book Recommendation: Oracle Database Upgrade and Migration Methods

Thanks guys, I received my copy πŸ™‚

Book Recommendation: Oracle Database Upgrade and Migration Methods

APRESS: Oracle Database Upgrade and Migration Methods

And today I’d like to write a book recommendation: Oracle Database Upgrade and Migration Methods.

The book got authored by these 3 true database upgrade and migration experts:

And it’s cool book mostly for techies. It has plenty of examples in all details. A lot of good tips and tricks (for instance: Disable the Broker when you upgrade your database with standby in place). All of it from …

Continue reading...

utlrp.sql – How to decrease resource consumption with utlprp.sql

 utlrp.sql - How to decrease resource consumption with utlprp.sqlJust being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before.

One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql.

Where it started

Actually it’s all Uwe’s fault (Uwe Kirchhoff is one of my best and oldest friends at Oracle and one of the best ACS guys I can think of). Uwe told me this story about a customer case he …

Continue reading...

Other people’s thoughts: “Should you upgrade to 12c?”

You still don’t believe me yet when I say: You can’t seriously wait for the so called 2nd release of Oracle Database 12c as it will be an entirely new release again?Β And you haven’t upgraded yet to Oracle Database for various reasons?

Then sometimes it’s good to listen to other people’s opinions – and I’m happy to share this 6 minute video by Tim Hall (very well known for his great page oracle-base). Listen to Tim and his Thoughts about Upgrading to Oracle Database 12c. And don’t get disturbed that he’s driving on the wrong …

Continue reading...

The MAGIC Questions

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 …

Continue reading...

Potential check for corruptions

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

Will gathering fixed object stats reduce recompilation time post upgrade?

Interesting question, isn’t it?
Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered?

First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job].
X$ structures are undocumented. V$ views are build on top of them and
should only be used even though it might be sometimes useful to access
X$ tables such as X$BH (buffer headers – contains information describing the current contents of a piece of the buffer cache

Continue reading...

Upgrade Fallback: Offline Backup??

When me an Roy talk about Fallback Scenarios in our Upgrade Workshop we cover first issues happening during the upgrade and second issues happening a few days (hours, weeks) after the upgrade.

One of the fallback options for issues encountered during the upgrade is to take an offline backup. Ok, not a complete offline backup of your 24TB database. Take all data tablespaces into read-only mode, then shutdown the database. Take a backup of all datafiles for SYSTEM, SYSAUX and UNDO plus the controlfiles plus the redologs. TEMP files are not a requirement as you could …

Continue reading...

Move to Locally Managed Tablespaces

As I’ve got asked during the workshop in Warsaw how to migrate Dictionary Managed to Locally Managed tablespaces here’s some additional information and an example.

To find out if a tablespace is dictionary or locally managed you’d use this query:

SQL> select

The procedure to migrate to locally managed tablespace is:


And the whole migration procedure would look like this:

— do this for all tablespaces except SYSTEM, TEMP and SYSAUX
— necessary if there’s no “real” temp tablespace definded yet – see Note:160426.1

Continue reading...

Cross-Platform Migration using Heterogeneous Data Guard

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 …

Continue reading...

What an upgrade scenario …

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 πŸ˜‰…

Continue reading...

Fix invalid objects and components – BEFORE you upgrade!

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 …

Continue reading...

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