Flaws and Pitfalls

Suppress nasty error messages and traces during datapatch

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.

 

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 …

Continue reading...

MRP3 for Oracle 19.17.0 adds an interesting surprise

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.

 

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

Continue reading...

Important alert for Oracle Database and GI RU 19.18.0 on Linux

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 …

Continue reading...

Rolling back or removing all patch SQL changes

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.

 

What is the case?

I …

Continue reading...

Upgrade from 11.2.0.4 to 19c with Label Security Policies

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 …

Continue reading...

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

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.

What

Continue reading...

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

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 …

Continue reading...

DBMS_OPTIM_BUNDLE and Out-Of-Place Patching

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?

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 …

Continue reading...

After patching, Spatial Index creation fails with ORA-13249

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.

After patching, Spatial Index Creation Fails with ORA-13249

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 …

Continue reading...

Messaging Gateway – Upgrade can’t find mgwu122.sql

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.

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

Continue reading...

Oracle 19c on Windows may flood your trace file directory

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.

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 …

Continue reading...

ORA-12638 on Windows only from Oracle 19.10.0 onwards

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.

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 …

Continue reading...

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

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.

What happens?

Take …

Continue reading...

Why is there a lib32 directory in Oracle 19.8.0 and 19.9.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.

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

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 …

Continue reading...

Apply Patch 31088341 before Upgrade to prevent ORA-1403

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

=================================================================
Continue reading...

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

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?

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 …

Continue reading...

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

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.

Continue reading...

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

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.

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 …

Continue reading...

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

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.

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 …

Continue reading...

Check your Unified Auditing Policies after upgrading to Oracle 19c

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.

Check your Unified Auditing Policies after upgrading to Oracle 19c

Photo by Felix Luo on Unsplash

Unified Auditing

Actually a lot of my customers use Unified Auditing these …

Continue reading...

When Fixed Object Stats Gathering takes very long during upgrade …

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

Continue reading...

Pitfall: ORA-1843 – NOT A VALID MONTH in Oracle 19.4 – 19.8

Today, I’d like to write about a tiny little issue – which can cause some real trouble in Oracle 19c. This blog post is about the Pitfall: ORA-1843 – NOT A VALID MONTH in Oracle 19.4 – 19.8.

The Testcase

My testcase is super-simple – and you can reproduce it even without any object in 2 seconds (one for “copy“, the other for “paste“). Simply execute this query:

select to_date('20191120','RRMMDD') from dual;

Or this one where I replace “RR” with the more common “YY”:

select to_date('20191120','YYMMDD') from dual;
Continue reading...

MGA Issue – and it is fixed with Oracle 19.8.0 and newer

Where do I start? One of the customer accounts I worked the longest time with recently upgraded to Oracle 19c on Exadata. They are an Exadata customer since 2009. After going live on 19c, a few days later they hit an MGA Issue – and it is fixed with Oracle 19.8.0 and newer. But question no.1 was: Why hasn’t Oracle warned us – and how could we have learned about it?

MGA Issue - and it is fixed with Oracle 19.8.0 and newer

Photo by Scott Webb on Unsplash

What is the MGA?

OK, I have heard of SGA and PGA. But MGA? The first two hits when I search with …

Continue reading...

AutoUpgrade and the Partial Offline Backup Strategy

In our Fallback talk we promote a backup strategy for large DWHs and database which are on purpose in NOARCHIVELOG mode. But last week I learned that there is something important to know about AutoUpgrade and the Partial Offline Backup Strategy. Otherwise you may hit a

AutoUpgrade and the Partial Offline Backup Strategy

Photo by Esri Esri on Unsplash

pitfall.

 

Partial Offline Backup Strategy?

I won’t explain in all details what this is. If you’d like to read more, please find all the necessary information on my blog post from July 17, 2018:

with graphics included. In brief, you take …

Continue reading...

Windows Only: Upgrade to Oracle 18.10.0 fails with ORA-01722

Liron Amitzi reported this on his blog a few days ago already. And it is a very nasty tiny little issue happening with RUs where the second number of the release enters the two-digit-space, such as 18.10.0. At this point, on Windows Only: Upgrade to Oracle 18.10.0 fails with ORA-01722. This happens late in the upgrade phase.

What is the issue?

The problem here is a tiny little piece in %ORACLE_HOME\admin\catupstr.sql – a SUBSTR function is used in a way that it does not take into account a potential two-digit …

Continue reading...