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 11.2.0.4 Please find the new September versions of: Oracle 12.2.0.1 preupgrade.jar – build 17 There’s issue with “creating directories”…

Oracle Database 18.3.0 Upgrade on premises

As 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 11.2.0.4 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: Oracle Database 18.3.0 on premises is available on Linux Oracle Database 18.3.0 Installation on premises If you read…

Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises

I’m currently refreshing our Hands-On Lab and will exchange Oracle 12.2.0.1 with Oracle 18.2.0. This blog post series is about how to Upgrade to Oracle 12.2.0.1 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. Deinstalling Oracle Database 12.2.0.1 Installing Oracle Database 18.1.0 Patching Oracle 18.1.0 to 18.2.0 Upgrade Oracle 12.2.0.1 to Oracle Database 18c on-premises For Multitenant upgrades from Oracle 12.1.0.2 to Oracle 12.2.0.1 you may read this: Upgrade Everything at…

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: MOS Note: 2380601.1 Database Preupgrade tool check list Purpose of the new MOS note is to give you a complete list…

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 10.2.0.4 database, can I upgrade directly to Oracle 11.2.0.4?” 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 11.2.0.4 below. Upgrade? When I speak about database upgrades, I mean you’ll use either the command line 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. COMP_ID COMP_NAME SCHEMA STATUS VERSION ——– ———————————- ———— ———- ———— CATALOG Oracle Database Catalog Views SYS VALID 12.1.0.2.0 CATJAVA Oracle Database Java Packages SYS VALID 12.1.0.2.0 CATPROC Oracle Database Packages and Types SYS VALID 12.1.0.2.0 JAVAVM JServer…

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 12.1.0.2 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 find the…

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? I have written several blog posts in the past about the different techniques to upgrade an Oracle Multitenant environment: Upgrade Everything At Once 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 PDB…

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: MOS Note:884522.1: How to Download and Run Oracle’s Database Pre-Upgrade Utility 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 11.2.0.4. 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 with the…

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

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 12.2.0.1 if – and only if – your database got created with 2k or 4k db block size. Problem 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 12.2.0.1 the upgrade – regardless of DBUA or catctl.pl – will fail with an ORA-1450: maximum key length (1478) exceeded. Analysis In the catupgrd0.log – regardless of using the DBUA or the command…

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. Restarting a failed Database Upgrade with catctl.pl 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 C =…

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

I did blog about this topic a while back. Upgrade PDBs – Everything At Once (Full CDB Upgrade) 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 CON_ID CON_NAME OPEN MODE RESTRICTED ———-…

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? 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 10.2.0.3 database – and your targeted release is Oracle 12.2.0.1 . A direct upgrade is not possible. I would opt now for Data Pump or TTS as you could jump directly. But…

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 12.1.0.2 database to Oracle 12.2.0.1 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 newest…

Upgrade to Oracle Database 12.2 – New MOS Notes

Some very helpful MOS Notes got published regarding the upgrade to Oracle Database 12.2.0.1: MOS Note: 2173141.1 Complete Checklist for Manual Upgrades to non-CDB Oracle Database 12.2 MOS Note: 2173144.1 Complete Checklist for Manual Upgrade for Multitenant Architecture Oracle Databases from 12.1. to 12.2. MOS Note: 2189854.1 Complete Checklist for Upgrading to Oracle Database 12.2 using DBUA Furthermore check regularly for Known Issues and Alerts – currently the note has no content but exists already: MOS Note: 2239820.1 12.2.0.1 Base Release – Availability and Known Issues Cheers –Mike

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. If you are going to upgrade to Oracle Database 12.1.0.2 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. –Mike

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. Problem 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 11.2.0.4 off IBM AIX and migrate it into Oracle 12.1.0.2 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 12.1.0.2 database you’d create would get TZ V18 by default. Solution 1…

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: MOS Note:884522.1 – Download and Run Oracle’s Database Pre-Upgrade Utility And of course the previous preupgrd.sql with the package utlupkkg.sql and the old utlu112i.sql for upgrade to Oracle 11.2.0.4 (which I wouldn’t do anymore) are available as well. 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…

Upgrades to Oracle Database 12.2.0.1 (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 11.2.0.3 Oracle Database 11.2.0.4 Oracle Database 12.1.0.1 Oracle Database 12.1.0.2 No direct upgrades are supported from versions below Oracle Database 11.2.0.3. For versions older than Oracle Database 11.2.0.3 other tools such as Data Pump or…

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 12.1.0.2 instead of the deployed (or any other earlier version). Changes since initial release of 12.1.0.2 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 is…

Speed up Upgrade Phase 65 with a new catuposb.sql

Credits to Chris Smids from Proximus in Belgium 🙂 Thanks, Chris!!! Upgrade to Oracle 12.1.0.2 is slow in phase: #65 ? You are wondering why phase: #65 of the database upgrade to Oracle Database 12.1.0.2 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 as…

Minor Upgrade? Going from 11.2.0.1 to 11.2.0.4?

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 11.2.0.1 to 11.2.0.3 as a minor upgrade whereas going…

Upgrade to Oracle Database 12c: We don’t insist :-)

It’s so funny – for years I discuss with customers minimal downtime upgrade strategies back and forth, up and down. I saw DBAs really hunting to save a few seconds potential downtime – and I always take this serious as there is usually a real reason behind that. Just to learn a few days later by repeating experience that my work Windows7 laptop goes down to apply upgrades … and this takes looooooooong …. sometimes it takes a lengthy +30 minutes of quiet time. Whereas my OL6 VBox image applies upgrades in the background and needs a simple restart to…

New PREUPGRD.SQL is available – Upgrade 12c – Apr16

It’s time for a new and improved version of our team’s preupgrd.sql (comes with the preupgrade package utluppkg.sql). The April 2016 preupgrd.sql  Please always download and use the most recent version from: MOS Note:884522.1 How to Download and Run Oracle’s Database Pre-Upgrade Utility as this version is 3 years newer than the one you’ll get with a fresh install of Oracle Database 12.1.0.2 . Included in the April 2016 PSU and BP as well Great news – and very important. Once you apply the most recent April 2016 PSU or BP (recommended – please see here: April 2016 PSU/BP are…

RMAN NOOPEN Clause DUPLICATE FOR UPGRADE

In Oracle Database 12c, the RMAN “DUPLICATE” command has new “NOOPEN” option which clones the database but does not open the auxiliary instance. This feature can be used to duplicate a database to higher version. MOS Note: 2022820.1 Upgrade to 12c database using RMAN Duplicate with NOOPEN clause The note contains also a full example on how to duplicate the production database as a standby, and upgrade it. –Mike