Upgrade / Migration

Upgrade fails with ORA-20001 during datapatch run

I encountered this issue now multiple times in my own environment. And I searched MOS and tried different things. But I couldn’t solve it yet. My Upgrade fails with ORA-20001 during datapatch run – and I’d like to show you how to bring the upgrade to an successful end.

Upgrade fails with ORA-20001 during datapatch run

Photo by chuttersnap on Unsplash

It all starts with an upgrade

In my lab environment I upgrade two databases often in parallel. An database and a The upgrades always flawless. But the occasionally fails. This is the screen AutoUpgrade is showing me – but this is not …

Continue reading...

Upgrade Testing – Online Clone a PDB to 19c

In the past I wrote some articles about cloning PDBs to new environments. And I added a post about RELOCATE as well. But I realized that I have no specific blog post about a very typical scenario: Upgrade Testing – Online Clone a PDB to 19c.

Upgrade Testing - Online Clone a PDB to Oracle 19c

Photo by Jørgen Håland on Unsplash

My Setup

I have an Oracle CDB with a PDB, and another Oracle 19.6.0 CDB. I will hot clone the PDB from to the higher version CDB, and then upgrade the PDB.

Upgrade Testing - Online Clone a PDB to Oracle 19c

The motivation to do it this way is to allow testing for the application …

Continue reading...

Upgrade PDBs with RELOCATE – How does it work?

Today Roy and I received a question from a colleague about the RELOCATE feature in Multitenant. And I’d like to shed some light and this topic a bit more. Upgrade PDBs with RELOCATE – How does it work?

Upgrade PDBs with RELOCATE - How does it work?

Photo by Sebastian Pena Lambarri on Unsplash


At first, it looks very promising to me. RELOCATE uses the HOT CLONE functionality of Multitenant but then relocates the PDB from one to another CDB. You need to fulfill a few important requirements, for instance:

  • Local Undo must be enabled in both CDBs
    • This limits the technique to at least Oracle
Continue reading...

Improved preupgrade.jar for Oracle 12.2 and 18c

My team mates are really quick. We received an information early this week about a missing or wrong DV check in the preupgrade – and they implemented it within a day or so. The September 2018 version of the improved preupgrade.jar for Oracle 12.2 and 18c is available for download.

Improved preupgrade.jar for Oracle 12.2 and 18c

What’s new in this version:

  • It adds a recycle bin check when the autofixup for dictionary statistics is run
  • And it updates DV_AUDIT_CLEANUP  to

Improved preupgrade.jar for Oracle 12.2 and 18c

Please find the new September versions of:

  • Oracle preupgrade.jar – build 17
    • There’s issue with “creating directories”
Continue reading...

Oracle Database 18.3.0 Upgrade on premises

Oracle Database 18.3.0 Upgrade on premisesAs Oracle Database 18c is now available on Linux on premises, I blogged about the installation already. And the next logical step is the Oracle Database 18.3.0 Upgrade on premises. In this case I will show an upgrade on the command line from Oracle to Oracle 18.3.0. A DBUA upgrade will follow in a separate blog post later.

Oracle Database 18.3.0 Upgrade on premises

Before you can start with the upgrade, you may download the software first, and then install it:

If …

Continue reading...

Upgrade Oracle to Oracle Database 18c on-premises

Upgrade Oracle to Oracle Database 18c on-premisesI’m currently refreshing our Hands-On Lab and will exchange Oracle with Oracle 18.2.0. This blog post series is about how to Upgrade to Oracle to Oracle Database 18c on-premises. There are other blog posts available you may use by yourself if you plan to refresh the lab by yourself until we uploaded a newer version. And it’s actually a good exercise.

For Multitenant upgrades from Oracle to Oracle you may read this:

Continue reading...

List of PREUPGRADE checks executed by preupgrade.jar

List of PREUPGRADE checks executed by preupgrade.jar

Most of you may not care about the checks our Pre Upgrade Check tool preupgrade.jar does. But some may. Especially in cases where you plan to run a command line upgrade unattended via a script. Therefore this List of PREUPGRADE checks executed by preupgrade.jar may help you.

List of PREUPGRADE checks executed by preupgrade.jar

As a result of a joint initiative between our Upgrade Development team and the Support group dealing with upgrade-related SRs we released this MOS Note:

Purpose of the new MOS note is to give you a complete list …

Continue reading...

Minimum Supported Version for Database Upgrades

This question comes up very often: What is the minimum supported version for database upgrades? Or a question I received today: “I have an Oracle database, can I upgrade directly to Oracle”

Usually I have to dig into my old slides from the past to give a definite answer. And of course, the Database Upgrade Guide of each version has the same information somewhere as well. Therefore I’d like to summarize the releases supported for direct database upgrades since Oracle below.


When I speak about database upgrades, I mean you’ll use either the command line

Continue reading...

What happens to components with OPTION OFF status during upgrade?

What happens to components with OPTION OFF status during upgrade?In the newer releases of Oracle database you may see components with status OPTION OFF in the database’s registry. But what happens to components with OPTION OFF status during upgrade? Will those stay on the old release?

What happens to components with OPTION OFF status during upgrade?

You may have a quick look into your DBA_REGISTRY (or CDB_REGISTRY). I used the check_components.sql script.

-------- ---------------------------------- ------------ ---------- ------------
CATALOG	Oracle Database Catalog Views	   SYS		VALID
CATJAVA	Oracle Database Java Packages	   SYS		VALID
CATPROC	Oracle Database Packages and Types SYS		VALID
Continue reading...

September 2017 releases of preupgrade.jar and preupgrd.sql are available

On a regular basis the Database Upgrade Team releases new and improved versions of the preupgrade tools via a MyOracle Support note. And it’s time again: the September 2017 releases of preupgrade.jar and preupgrd.sql are available for download.

September 2017 releases of preupgrade.jar and preupgrd.sql are available

Please ALWAYS download the most recent versions of preupgrade.jar for upgrades to Oracle Database 12.2 and preupgrd.sql (including utluppkg.sql) for Oracle Database upgrade from MOS Note: 884522.1. The version you’ll download is way newer than the version you’ll get with the base installation.

preupgrade.jar – Rel.7 – September 2017

Please …

Continue reading...

Why does the upgrade of PDB$SEED always take longer?

Upgrading the PDB$SEED takes always longer than any other container.
What is the reason for an upgrade of the PDB$SEED taking so much longer in a real world environment?

How does an upgrade with Oracle Multitenant work?

Upgrading the PDB$SEED takes always longerI have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment:

  1. Upgrade Everything At Once
  2. Upgrade via Unplug/Plug/Upgrade

The interesting case happens in the “Everything At Once“. The PDB$SEED takes significantly longer to upgrade than any other container.

Upgrading the PDB$SEED takes always longer?

Quick recap: The PDB$SEED is the (I call it) blueprint

Continue reading...

New version of preupgrade.jar (build 5) is available

There’s a new and improved version of the preupgrade.jar (build 5 – May 2017) available for download from MyOracle Support:

The same note will get you access also to the most recent builds of preupgrd.sql for upgrades to Oracle Database 12.1 and utlu112i.sql for the unlikely event you’ll have to upgrade a database to Oracle

Please always exchange the version getting installed by default with the version we offer via MOS Note:884522.1 as the downloadable version is usually much newer and improved over the one getting deployed …

Continue reading...

Issue with 2k and 4k db_block_size – ORA-1450 when upgrading to Oracle

Recently we got alerted by a customer and a colleague from Italy about an issue with the upgrade from Oracle 12.1.0.x to Oracle if – and only if – your database got created with 2k or 4k db block size.


If your database got created with 2k or 4k db block size, and you attempt an upgrade from any upgrade-supported Oracle version to Oracle Database the upgrade – regardless of DBUA or catctl.pl – will fail with an ORA-1450: maximum key length (1478) exceeded.


In the catupgrd0.log – regardless of using the DBUA or …

Continue reading...

Can I restart a failed Multitenant Upgrade as well?

A while back I did blog about the new -R option of the parallel upgrade tool catctl.pl in Oracle Database 12.2.

And in case you will do a real Multitenant upgrade and fail – as it happened to me today due to “no space left on device” (no audit files could be written anymore) I tried the -R option as well based on Joe’s (our lead catctl.pl developer) recommendation:

$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -R -l /home/oracle/mike2 catupgrd.sql

Argument list for [catctl.pl]
Run in                c = 0
Do not run in         
Continue reading...

Upgrade Everything at Once – Multitenant Upgrade from Oracle 12.1 to 12.2

I did blog about this topic a while back.

Theory is nice but it’s a big difference when you do it the first time with a non-lab deployment at a customer with a real Multitenant database with almost 100 PDBs.Then you learn that neither the documentation nor my previous blog post contain all the necessary steps.

My test setup

I have a simple deployment of just 5 PDBs. But it will showcase with my limited compute capabilities how the process should work.

SQL> show pdbs

Continue reading...

Multiple hop upgrades? Execute the matching preupgrade scripts for each hop

We discussed an interesting upgrade case last week together with our upgrade colleagues in Support – and learned the double-hop (or triple-hop) upgrade case is not described in the documentation somewhere.

What is a multiple-hop upgrade?

Triple Jump

Triple Jump – Willie Banks – Olympics 1988 Seoul

Actually this describes the case where somebody can’t upgrade directly and has to do several upgrades in a sequence to reach the targeted release. For example, you start of with an Oracle database – and your targeted release is Oracle . A direct upgrade is not possible. I would opt now for Data …

Continue reading...

Upgrade to Oracle Database 12.2 and Converting it into an 12.2 PDB (plugin)

2 customers requested a bit more explanation on how to:
Convert and Plugin an Oracle 12.1 non-CDB database into an Oracle 12.2 container database

Cool, I like that 🙂 I will do this right away and use the approach to Upgrade first, then Plugin afterwards.

Upgrading an Oracle database to Oracle

First the preupgrade.jar must be executed – please download always the most recent version from MOS Note:884522.1 as the version we publish is usually newer including important improvements (and fixes) than the version you’ll get with the release drop. We are working on including always the …

Continue reading...

Upgrade to Oracle Database 12.2 – New MOS Notes

Some very helpful MOS Notes got published regarding the upgrade to Oracle Database

Furthermore check regularly for Known Issues and Alerts – currently the note has no content but exists already:


Continue reading...

February 2017 version of PREUPGRD.SQL is available

The new version of the preupgrd.sql (including the utluppkg.sql package) in its February 2017 version is available for download from MOS Note:884522.1.

New Preupgrd.sql - MOS Note 884522.1

If you are going to upgrade to Oracle Database please exchange your current preupgrd.sql/utluppkg.sql either in ?/rdbms/admin or in the location of the source system you’ve copied it to with the new version.


Continue reading...

Create a database with NON-DEFAULT Time Zone

One requirement of Transportable Tablespaces (and of course Full Transportable Export/Import as well) is to have identical database character sets AND identical time zone settings.


Source database has a lower time zone setting than the default target database in the destination home.

Lets assume you’d like to migrate an Oracle off IBM AIX and migrate it into Oracle on an Exadata meaning Oracle Linux 6. The source time zone version if it has never been upgraded would be TZ V14 – whereas the new Oracle database you’d create would get TZ V18 by default.

Time Zone Setting Oracle Database

Solution 1

Continue reading...

New PREUPGRADE.JAR is available – MOS 884522.1

As promised – and thanks to everybody from the Database Upgrade Development Team – the new preupgrade.jar for Oracle Database 12.2 is available:

And of course the previous preupgrd.sql with the package utlupkkg.sql and the old utlu112i.sql for upgrade to Oracle (which I wouldn’t do anymore) are available as well.

New Preupgrd.sql - MOS Note 884522.1

Please ALWAYS use the version downloaded from this MOS note as this version is much newer and better than the version getting shipped due to code freeze dates. And you will recognize that the new preupgrade.jar is now identical …

Continue reading...

Upgrades to Oracle Database (and Downgrades)

Oracle Database 12c Release 2 (12.2) is available now in the Oracle Cloud
in DBCS and ECS in both NAS and EMEA zones, as well as being available on Exadata Express Cloud Service. The Oracle Database 12.2 documentation should be published later today.

Just to note down, the versions supporting direct upgrade either with catctl.pl on the command line or with DBUA are:

  • Oracle Database
  • Oracle Database
  • Oracle Database
  • Oracle Database

Direct Upgrade to Oracle Database 12.2

No direct upgrades are supported from versions below Oracle Database

For versions older than Oracle Database other tools such as Data Pump or …

Continue reading...

New preupgrd.sql is available – MOS 884522.1

A new preupgrd.sql got released this week via MOS Note:884522.1.

Plus use this one for upgrades to Oracle Database instead of the deployed (or any other earlier version).

New Preupgrd.sql - MOS Note 884522.1

Changes since initial release of

  • Do not recommend pga_aggregate_limit for 12.1 databases
  • Fix miscalculation of upgrade parallel pdb count
  • Updates to the memory sizing uploaded in build #12
  • Removed the 32-bit and 64-bit values in the manual display.
  • Added a pga_aggregate_limit.
  • Shared_pool_size has gone from 472M to 660M for non-CDB.
  • Memory_target algorithm is more robust now that we’ve added a pga_aggregate_target to its sizing.
  • CDB sizing also in  (And
Continue reading...

Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!!

Upgrade to Oracle is slow in phase: #65 ?

You are wondering why phase: #65 of the database upgrade to Oracle Database takes quite a while. You dig down into the catupgrd0.log and recognized this statement taking a while:

dbms_output.put_line('catuposb, update 4 - rows updated ' || rows_updated);
END; -- end of update for system internally generated objs

The cause for this issue is buried in the script catuposb.sql hitting stale histograms which did not get refreshed even if you gathered dictionary stats before the upgrade …

Continue reading...

Minor Upgrade? Going from to

My Belgium friend Phillipe Fierens raised a great question on Twitter last week and dropped me an email again after having a discussion with his client:

For Phillipe and myself the answer is pretty clear and straight forward:

There is no minor upgrade anymore since every (patch set) release is a full release and since new parameters, parameter values, features and whatever appears even in patch sets.

But the following discussion on Twitter with comments from all sides made me think about why people would honestly declare going for instance from to as a minor upgrade whereas going …

Continue reading...