Flaws and Pitfalls

Datapatch may be slower when Traditional and Unified Auditing are on

Posted on April 11, 2023 by Mike.Dietrich Auditing Flaws and Pitfalls 7

I wrote several times especially about Unified Auditing in the past. It is a very efficient and useful way to audit operations in the database. But you need to be a bit careful about what’s on, and what isn’t. Now we recently had a customer case where it was clearly proven that Datapatch may be slower when Traditional and Unified Auditing are on. But how can this happen?

Datapatch may be slower when Traditional and Unified Auditing are on

Photo by marcos mayer on Unsplash

Recapping Traditional Auditing

At first, I am neither a security not an auditing expert. But I played quite a bit with Unified Auditing over the …

Oracle Optimizer Statistics Advisor in Oracle 19c

Christian Pfundtner pointed me to the fact that my old blog post about the Statistics Advisor from 2017 may not be up to date anymore. And he further pointed out that some of his clients still suffer from issues with the tool. Hence, let me refresh the topic with Oracle Optimizer Statistics Advisor in Oracle 19c. And thanks to Christian for the pointers.

Oracle Optimizer Statistics Advisor in Oracle 19c

Photo by Tobias Keller on Unsplash

Overview

At first, I won’t rewrite the entire blog post but instead focus on recent findings. Therefore, you may please want to read my previous blog post about the …

ORA-13516 “CATPROC not valid” during datapatch or autoupgrade to 19.18.0

Posted on February 24, 2023 by Mike.Dietrich Flaws and Pitfalls Patch Recommendation 18

I thought I could relax this week using my leftover vacation days from 2022. But this issue is triggering me since some of you mailed me already. Maybe this blog post can prevent others from being trapped by this issue: ORA-13516 “CATPROC not valid” during datapatch or autoupgrade to 19.18.0. And as a prolog, you will NOT see the below issue when you started your database in NORMAL mode for the execution of datapatch.

ORA-13516 "CATPROC not valid" during datapatch or autoupgrade to 19.18.0

Photo by Greg Rosenke on Unsplash

What is the symptom?

Regardless of whether you patch to Oracle Database RU 19.18.0 manually or with …

Hide and Seek with RU 19.18.0

Posted on February 2, 2023 by Mike.Dietrich Flaws and Pitfalls Patch Recommendation 46

One and a half weeks ago we released the Database and Grid Infrastructure Release Updates (RU) 19.18.0. You could read on the blog how to apply it, and you could read also about an alert. So far, all was more or less transparent but apparently sort of an Hide and Seek with RU 19.18.0 has happened. So let me shed some light.

Photo by Braydon Anderson on Unsplash

Heads Up!

I am just the messenger trying to bring some light into the topic. Neither is our team responsible nor do we have any sort of control on this process. I …

Suppress nasty error messages and traces during datapatch

Posted on January 31, 2023 by Mike.Dietrich Flaws and Pitfalls Patch Recommendation 5

A few days ago I published my quarterly blog post in the series of Patching all my environments … And I mentioned that datapatch took a bit longer for 19.18.0 than usual. In the alert I’ve had nasty error messages or warnings. And Walter (thanks!) commented immediately with some advice.

Suppress nasty error messages and traces during datapatch

Photo by Filippo Bortolotto on Unsplash

What are these messages?

Actually, I saw them before already. Since I always create CUSTOM databases with a template I spotted those warnings the first time when I was wondering why DB creation took so abnormally long with Multitenant. I discovered KGL Heap …

MRP3 for Oracle 19.17.0 adds an interesting surprise

Posted on January 30, 2023 by Mike.Dietrich Flaws and Pitfalls Patch Recommendation 9

You may have read my previous blog posts about MRPs (Monthly Recommended Patches). And today I did a quick check with Rodrigo. We both were a bit surprised to have the MRP3 for 19.17.0 add a lot of fixes to a standard RDBMS installation. So we were investigating a bit further. As a teaser, read on to see that MRP3 for Oracle 19.17.0 adds an interesting surprise.

MRP3 for Oracle 19.17.0 adds an interesting surprise

Photo by David Brooke Martin on Unsplash

How did we find out?

The MRP3 for Oracle Database 19.17.0 got released just a week ago. You can navigate to it via MOS …

Important alert for Oracle Database and GI RU 19.18.0 on Linux

Posted on January 25, 2023 by Mike.Dietrich Flaws and Pitfalls Patch Recommendation 59

Some of you may have seen the alert already released yesterday. And thanks to Peter Lehmann again for alerting me. When you logon to MyOracle Support, you will find it simply in the alert section to the left. On Jan 25, 2023, this Important alert for Oracle Database and GI RU 19.18.0 on Linux has been released.

Important alert for Oracle Database and GI RU 19.18.0 on Linux

Photo by Gary Bendig on Unsplash

What is the situation?

Due to an issue described in MOS Note: 2923428.1 – LMON trace file with repeated entries of “Submitting asynchronized dump request” the Release Updates (RU) 19.18.0 for the Oracle Database and …

Rolling back or removing all patch SQL changes

Posted on January 20, 2023 by Mike.Dietrich Flaws and Pitfalls Microsoft Windows Single-/Multitenant 2

This is quite an interesting case I’ve had to scratch my head at first as well. Magnus mailed me the other day asking for a client since the PDB compatibility check errored out with a patch conflict. We discussed the topic but the most easiest workaround to me was Rolling back or removing all patch SQL changes from the PDB before unplug. And since I had to look up the command I’d rather put it on the blog in case you or I need it again one day.

Rolling back or removing all patch SQL changes

Photo by Pramod Tiwari on Unsplash

What is the case?

I …

Upgrade from 11.2.0.4 to 19c with Label Security Policies

Posted on July 6, 2022 by Mike.Dietrich Flaws and Pitfalls 5

I remember, I have a blog. Forgive me that I don’t write a lot at the moment even though I still have a long queue of “future posts to write”. It is just too busy, and I was away for almost two full weeks in June as well. But let me briefly drop information on an issue I learned about recently. It just affects your when Upgrade from 11.2.0.4 to 19c with Label Security Policies. All other readers may simply ignore this blog post.

Upgrade from 11.2.0.4 to 19c with Label Security Policies

Photo by Erik Mclean on Unsplash

What happens?

You are using Oracle Label Security, and …

Upgrading a PDB with JVM may result in ORA-7445 joevm_invokevirtual

Posted on September 29, 2021 by Mike.Dietrich Flaws and Pitfalls JVM 3

As you know, this blog is also my brain dump about Oracle topics. Since this one happened yesterday, mostly at night with the help of my team mates and the Java team, I’d like to write it down into this blog post: Upgrading a PDB with Java may result in ORA-7445 joevm_invokevirtual() as it is still fresh and may affect you as well. If you don’t have PDBs, if you don’t have Java (or JVM) in it or if you upgraded already to Oracle Database 19c, you can stop reading now.

Upgrading a PDB with Java may result in ORA-7445 joevm_invokevirtual

Photo by Fahmi Fakhrudin on Unsplash

What

Pitfall: Upgrade to 21c fails when ORDIM is present but no SDO

Posted on August 25, 2021 by Mike.Dietrich Flaws and Pitfalls Oracle Database 21c Upgrade / Migration 2

You may have read my previous blog post about Upgrading a non-CDB to Oracle Database 21c. And I referred to a potential Pitfall: Upgrade to 21c fails when ORDIM is present but no SDO. In this blog post I would like to explain how to avoid this pitfall beforehand and explain what needs to be done.

Pitfall: Upgrade to 21c fails when ORDIM is present but no SDO

Photo by Sara Codair on Unsplash

What’s the problem?

In my previous blog post I showed you the ideal and normal way. But during my tests I came across an issue I was not aware of – and I simply had no …

DBMS_OPTIM_BUNDLE and Out-Of-Place Patching

Posted on August 12, 2021 by Mike.Dietrich Flaws and Pitfalls Optimizer Patch Recommendation 6

Well, you see, this is most likely my special DBMS_OPTIM_BUNDLE week. And since I receive quite a number of questions, it may be good to discuss here about DBMS_OPTIM_BUNDLE and Out-Of-Place Patching?

Photo by Didssph on Unsplash

Out-of-place Patching

When you patch out-of-place with a new home – which is clearly our recommendation – you may see another tiny pitfall with DBMS_OPTIM_BUNDLE.

When you check DBA_DIRECTORIES, you will find two directories being related to DBMS_OPTIM_BUNDLE.

SQL> select directory_name, directory_path from dba_directories where directory_name like '%OPTIM%'

DIRECTORY_NAME	     DIRECTORY_PATH
-------------------- --------------------------------------------------
DBMS_OPTIM_LOGDIR    /u01/app/oracle/product/19/cfgtoollogs
DBMS_OPTIM_ADMINDIR  /u01/app/oracle/product/19/rdbms/admin

You see the “19” in the …

After patching, Spatial Index creation fails with ORA-13249

Posted on August 4, 2021 by Mike.Dietrich Flaws and Pitfalls 11

Thanks again to Peter Lehmann from T-Systems for highlighting this issue to me. After patching, Spatial Index creation fails with ORA-13249. And Peter’s customer was quite worried. But see what may have caused this, and how we fixed it.

Photo by Bill Oxford on Unsplash

What happened?

Peter patched a system from 19.10.0 to 19.11.0. This database has undergone an upgrade from 12.2.0.1 to 19.7.0 before, then got patched to 19.9.0 before. The database has the Oracle Locator only but no Spatial installed. The patching including datapatch worked fine. All seemed to be good. Until the customer tried to …

Messaging Gateway – Upgrade can’t find mgwu122.sql

Posted on May 21, 2021 by Mike.Dietrich Flaws and Pitfalls Oracle Database 18c Oracle Database 19c 6

Thanks to Christian Ballweg from Optiz Consulting who brought this issue to my attention. I haven’t seen it before since I haven’t installed it but you may encounter this if you have the Messaging Gateway – Upgrade can’t find mgwu122.sql.

Messaging Gateway - Upgrade can't find mgwu122.sql

Photo by Olav Ahrens Røtne on Unsplash

What is the Messaging Gateway?

Actually I steal this from the documentation:

Messaging Gateway enables communication between applications based on non-Oracle messaging systems and Oracle Database Advanced Queuing.

Oracle Database Advanced Queuing provides propagation between two Oracle Database Advanced Queuing queues to enable e-business (HTTP through IDAP). Messaging Gateway extends

Oracle 19c on Windows may flood your trace file directory

Posted on March 11, 2021 by Mike.Dietrich Flaws and Pitfalls Oracle Database 19c 21

Oh … it’s Windows week here. And all this even though since I didn’t install Oracle on Windows for quite a while. But of course I’m fully aware that many of you out there operate Oracle on Windows. In this particular case thanks to Joël for the pointer to this issue. Oracle 19c on Windows may flood your trace file directory.

Oracle 19c on Windows may flood your trace file directory

Photo by Daniel von Appen on Unsplash

What happens?

In every release of Oracle 19c, at least until 19.10.0 BP, you may find out that every few minutes a trace file gets written into the %ORACLE_BASE%\diag\..\..\trace directory. And all …

ORA-12638 on Windows only from Oracle 19.10.0 onwards

Posted on March 10, 2021 by Mike.Dietrich Behavior Change Flaws and Pitfalls Oracle Database 19c 56

You like unexpected changes and surprises, don’t you? And especially those which aren’t in the patch notes or the docs. I blogged about such changes a few weeks ago. And thanks to the people reading this blog, I learned now about another change with Oracle 19.10.0 on the Windows platform. You may receive now an ORA-12638 on Windows only from Oracle 19.10.0 onwards.

ORA-12638 on Windows only from Oracle 19.10.0 onward

Photo by Fanny Rascle on Unsplash

What has been changed?

So at first, thanks to Ernst and Marcus for bringing this to my attention. This is an issue which happens on MS Windows only.

When you …

Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0

Posted on March 2, 2021 by Mike.Dietrich Flaws and Pitfalls 6

The other week a customer from my hometown alerted me about an issue they saw when applying the 19.9.0 RU. A long sequence of errors in the datapatch run, all of them signaled from sdoloadj.sql. They opened an SR. And Support had an – understandable – recommendation the customer didn’t want to implement: Install JAVAVM. But thanks to my PM mate, Hans Viehmann (Mr Spatial), here is a simple Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0. And it may even apply to later releases, too.

Workaround for sdoloadj.sql errors with Datapatch in 19.9.0 and 19.10.0

Photo by Mael BALLAND on Unsplash

What happens?

Take …

Why is there a lib32 directory in Oracle 19.8.0 and 19.9.0?

Posted on December 21, 2020 by Mike.Dietrich Flaws and Pitfalls Installation Patch Recommendation 0

I received two emails from different customers within a week – and both asked the same thing: Why is there a lib32 directory in Oracle 19.8.0 and 19.9.0? In one case, it was more like “We are wondering” but in the other case it caused issues during patching.

Photo by Erik Mclean on Unsplash

What happens?

Due to a packaging issue most likely, there is a lib32 directory appearing after you patch your homes to either 19.8.0 or 19.9.0. With the next RU, 19.10.0, this should not be the case anymore.

Why does this sound strage?

The first customer who …

Apply Patch 31088341 before Upgrade to prevent ORA-1403

Posted on November 23, 2020 by Mike.Dietrich Flaws and Pitfalls 6

As I learned from a customer this week, this patch is a must have when you use Partitioning and you attempt to upgrade to Oracle 19.9.0 or earlier. So please apply patch 31088341 before Upgrade to prevent ORA-1403 happening.

Apply Patch 31088341 before Upgrade to prevent ORA-1403

Photo by Grant Durr on Unsplash

What is the issue?

This applies to all 19c upgrades at least until 19.9.0.

The fix is included from 19.10.0 Release Update onward. So if you are upgrading to 19.10.0 or higher, you can stop reading here (thanks Pablo for the hint!).

You may see this error pattern in catupgrd0.log:

==Error from catupgrd0.log===

=================================================================

Fixed Objects Stats Gathering Fails – what do you do now?

Posted on November 16, 2020 by Mike.Dietrich Flaws and Pitfalls 3

A few days ago I blogged about what you could do when Fixed Objects Stats Gathering is slow in an upgrade process. You can skip the preupgrade gathering, and the postupgrade gathering does not happen anymore since the October 2020 version of AutoUpgrade. Still, I came across another issue the other day: Fixed Objects Stats Gathering Fails – what do you do now?

Fixed Objects Stats Gathering Fails - what do you do now?

Photo by Spencer Imbrock on Unsplash

What happened?

Actually this is connected to the Fixed Objects Stats Gathering is slow in an upgrade case. The customer encountered an incredible long runtime. And the support engineer …

ORA-29702 – and your instance does not startup in the cluster anymore

Posted on November 13, 2020 by Mike.Dietrich ASM / RAC / GI Flaws and Pitfalls Patch Recommendation 11

This is the right blog post for a Friday 13th. And please forgive me – I wanted to put this on the blog earlier as two of my customers hit this weeks ago already. But it must have fallen through the cracks. Still, now it is hopefully not too late to tell you what you should do if you hit ORA-29702 – and your instance does not startup in the cluster anymore. Especially when you tested a database upgrade – and after a restore, the database doesn’t want to start, no matter what you try.

ORA-29702 - and your instance does not startup in the cluster anymore

Photo by Nadine Shaabana

Solution for ORA-02303 from JSON_OBJECT_INVALID in upgrade to 19c

Posted on October 30, 2020 by Mike.Dietrich Flaws and Pitfalls 0

I just learned yesterday about this error – and as it seems to happen occasionally, you may want to read more about the Solution for ORA-02303 from JSON_OBJECT_INVALID in upgrade to 19c.

Solution for ORA-02303 from JSON_OBJECT_INVALID in upgrade to 19c

Photo by Pankaj Patel on Unsplash

What happens?

You are upgrading a database to Oracle 19c, and you see this error pattern:

CREATE OR REPLACE TYPE JSON_Object_T AUTHID CURRENT_USER UNDER JSON_Element_T(
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

This is coming when catjson.sql gets executed.

Why does it happen?

Unfortunately the FORCE option is missing when we create …

Receiving ORA-55940: An error occurred during execution of ktliIngestExternData

Posted on October 23, 2020 by Mike.Dietrich Auditing Flaws and Pitfalls 10

During my previous test with Unified Auditing Policies anomalies after upgrade to Oracle 19c, I also hit and error when I queried the unified audit trail. Hence, I make a reminder to myself and would like explain what to do in case you are Receiving ORA-55940: An error occurred during execution of ktliIngestExternData as well.

Receiving ORA-55940: An error occurred during execution of ktliIngestExternData

Photo by Moritz Mentges on Unsplash

What a strange error

I created a few policies by myself and did a database upgrade from Oracle 12.2.0.1 to Oracle 19.8.0. And after upgrade, I wanted to check the records in my audit trail. But instead of …

Check your Unified Auditing Policies after upgrading to Oracle 19c

Posted on October 21, 2020 by Mike.Dietrich Auditing Flaws and Pitfalls Oracle Database 19c 11

This may become a longer blog post. One of the customers I work with for many years contacted me a few weeks ago. After upgrade to 19c, it looked like as if some Unified Auditing Policies weren’t correct anymore. It started as a tiny issue, but thanks to the customer who analyzed down to the bones, it seems to be bigger than we thought at the beginning. So you may Check your Unified Auditing Policies after upgrading to Oracle 19c as well.

Photo by Felix Luo on Unsplash

Unified Auditing

Actually a lot of my customers use Unified Auditing these …

When Fixed Object Stats Gathering takes very long during upgrade …

Posted on October 20, 2020 by Mike.Dietrich Auditing AutoUpgrade Flaws and Pitfalls 6

Over the past days I exchanged several emails with a very important customer regarding Fixed Objects Stats gathering in relation to database upgrade. The customer found out that it takes very long in their environment – over an hour to be precise. Relaxing on the weekend helped me a bit. And this morning, it dawned me that I’ve had two similar cases before already. Plus I didn’t blog about it yet. Now it’s time to tell you a little but about Unified Auditing and the situation when Fixed Object Stats Gathering takes very long during upgrade …

When Fixed Object Stats Gathering takes very long during upgrade ...

Photo by Ayse

Flaws and Pitfalls

Recapping Traditional Auditing

Overview

What is the symptom?

Heads Up!

What are these messages?

How did we find out?

What is the situation?

What is the case?

What happens?

What

What’s the problem?

Out-of-place Patching

What happened?

What is the Messaging Gateway?

What happens?

What has been changed?

What happens?

What happens?

Why does this sound strage?

What is the issue?

What happened?

What happens?

Why does it happen?

What a strange error

Unified Auditing

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Recent Posts

Follow me on Twitter