Apply Patch 31088341 before Upgrade to prevent ORA-1403

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

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. You may see this error pattern in catupgrd0.log:

==Error from catupgrd0.log===

=================================================================
20:34:11 SQL> Rem BEGIN bug 28757685: repair tabpart$
20:34:11 SQL> Rem
=========================================================================
20:34:11 SQL> /* bug 28757685: tabpart$.hiboundval and tabpart$.hiboundlen
are incorrect
20:34:11 SQL> * when tables are

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

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

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 0

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

Upgrade Essentials – Videos for the Virtual Classroom Series 2020

Posted on November 10, 2020 by Mike.Dietrich Video / Viewlet Workshop 0

Roy, Daniel and I ran 6 workshops in our Virtual Classroom Series 2000 format. Each of them is between 90 and 120 minutes. This sounds quite long but of course you can scroll forward if you are interested in only a part of it. We consider those the Upgrade Essentials – Videos for the Virtual Classroom Series 2020. As as we now completed the 6th web seminar, it’s time for a short recap.

Upgrade Essentials - Videos for the Virtual Classroom Series 2020

Photo by Niamat Ullah on Unsplash

Is Upgrade that complicated?

Good question. If upgrade is so easy as we say, why do you need 6 virtual sessions …

Why is the 19.9.0 Release Update not available yet for MY platform?

Posted on November 9, 2020 by Mike.Dietrich Patch Recommendation 0

I’ve had a week off and tried to relax and stay away from work topics (which worked quite well this time). The downside is 300+ emails in the inbox. And I spotted several ones already asking: Why is the 19.9.0 Release Update not available yet for MY platform?

Why is the 19.9.0 Release Update not available yet for MY platform?

Photo by AZGAN MjESHTRI on Unsplash

Details for Patch 123456789 not found.

At first, this is not a uncommon question. And the root cause is two-fold. As you know from my quarterly blog posts such as Patching all my environments with the October 2020 Patch Bundles, these notes are important:

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 …

October 2020 Version of AutoUpgrade is available

Posted on October 27, 2020 by Mike.Dietrich AutoUpgrade 2

The October 2020 Version of AutoUpgrade is available now for download since Friday last week. As usual, you can access it easily via MOS Note: 2485457.1 – AutoUpgrade Tool.

Photo by RoseBox رز باکس on Unsplash

What’s new in the October 2020 version?

As usual you find the change.log at the end of MOS Note: 2485457.1 – AutoUpgrade Tool for the most recent Oracle Database upgrade tool. You will find the following fixes and enhancements:

Enahancements
- BUG_31709141 Allow the job state to be reset when database is restored manually
Fixes
- AUPG-1862 ORA-65173 occurs when running the fix for

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

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

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 …

Patching all my environments with the October 2020 Patch Bundles

Posted on October 22, 2020 by Mike.Dietrich Patch Recommendation 19

I have to confess: Time didn’t allow to rearrange my lab yet to have GI and ASM. Hence, this will be another blog post about Patching all my environments with the October 2020 Patch Bundles with only non-RAC patching. But it is in the works …

Patching all my environments with the October 2020 Patch Bundles

Photo by Olga Guryanova on Unsplash

As usual, an important annotation upfront: I patch in-place due to space issues. But in reality, you please patch always out-of-place with a separate home. Please see this blog post about how to apply the RU directly when you provision a new home with OUI.

Security Alert

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 3

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 4

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

Does Data Pump import only serially into PDBs?

Posted on October 15, 2020 by Mike.Dietrich Data Pump 0

Daniel and I did Web Seminars this week. One of them was about the different migration strategies. We did talk a lot about Data Pump. One attendee mentioned that Data Pump Import does not work parallel into PDBs in Oracle 12.2.0.1 and Oracle 18c. We were skeptical, and today I tried out to check: Does Data Pump import only serially into PDBs?

Does Data Pump import only serially into PDBs?

Photo by Nikola Johnny Mirkovic on Unsplash

Very simple test setup

For this test, I take a schema export from an 11.2.0.4 database in our Hands-On Lab, the TPCC user HammerORA uses in the UPGR database.…

Oracle Database 12.1.0.2 certified on Oracle Linux 8 and RHEL8

Posted on October 13, 2020 by Mike.Dietrich Operating System 7

The other day I received a question on Twitter regarding my experience with Oracle 12.1.0.2 on Linux 8. I first thought this may be an error. Why should Oracle 12.1.0.2 been certified on Linux 8. And I didn’t see anything internally about Oracle Database 12.1.0.2 certified on Oracle Linux 8 and RHEL8.

Oracle Database 12.1.0.2 certified on Oracle Linux 8 and RHEL8

Photo by NOAA on Unsplash

But it is true …

Thanks to Marian Bednar (Thanks a lot!!) who couldn’t find any information on my blog, now I know. Marian pointed me to the Release Notes Oracle Database 12.1.0.2. They’ve got updated in July 2020 already …

Which database version can be used with Grid Infrastructure 19c?

Posted on October 6, 2020 by Mike.Dietrich ASM / RAC / GI 7

This will be a very short blog post today. But a colleague asked me this question just a few minutes ago: Which database version can be used with Grid Infrastructure 19c? And I realized that I neither put it on the blog yet nor did I bookmark it. But I answered this question several times already.

Photo by Sincerely Media on Unsplash

Which database version can be used with Grid Infrastructure 19c?

There are 4 sources I did check:

MOS Notes on MyOracle Support
Oracle Documentation
Certification Information on MyOracle Support
Database Upgrade Blog

At first, I answered with MOS …

Transportable Tablespaces: Keep tablespace files read-only

Posted on October 5, 2020 by Mike.Dietrich Transportable 2

When you Transport Tablespaces, you may be aware of a change in behavior a long while ago. When your tablespace import has been finished, a tablespace will be taken read-write automatically. But there may be situations when you’d like the previous behavior. Now you can have this with Transportable Tablespaces: Keep tablespace files read-only.

Transportable Tablespaces: Keep tablespace files read-only

Photo by freestocks on Unsplash

Default Behavior

Since Oracle Database 12c a tablespace will be taken read-write once the meta data import has been completed automatically. This was different in previous database releases. We used the technique a long time ago for a telco provider …

Test Transportable Tablespaces without Read-Only Mode

Posted on October 2, 2020 by Mike.Dietrich Transportable 0

From time to time I deal with Transportable Tablespaces as an upgrade and migration approach. But TTS has one major flaw, especially when databases get really really big in the 3-digit-terabyte sizes or more: You will need to switch your tablespaces into read-only mode. But since Oracle Database 19c, you can Test Transportable Tablespaces without Read-Only Mode.

Test Transportable Tablespaces without Read-Only Mode

Photo by freestocks on Unsplash

Transport Constraints

Transportable Tablespaces (TTS) is a very common technique, especially when you need to migrate databases across Endianness. Since Oracle 10g, TTS works even from Big Endianness to Little Endianness platforms, and vice versa. Only if …

Upgrade to Oracle 19c – Virtual Classroom Series – Parts 5 and 6

Posted on October 1, 2020 by Mike.Dietrich Workshop 2

October will be a very busy month. Daniel and I are putting the slides together at the moment for the upcoming Upgrade to Oracle 19c – Virtual Classroom Series – Parts 5 and 6. We will speak about “Database Migration Techniques” and about “Move to the Cloud – For Techies Only“. No worries if you missed Parts 1-4 as you can watch all of them online on-demand. Find all links below.

Upgrade to Oracle 19c – Virtual Classroom Series - Parts 5 and 6

Photo by Austin Distel on Unsplash

Register for the Virtual Classroom Part 5 and Part 6 events

Find the overview for both events here …

User Group Seminars in October about Upgrade, Migration and Patching

Posted on September 30, 2020 by Mike.Dietrich Workshop 0

I didn’t blog a lot in the past weeks. At the moment, most of my time is consumed either by customer web meetings and workshops, or the preparation for the User Group Seminars in October about Upgrade, Migration and Patching. Below I will list these – please feel free to sign up. #SupportUserGroups

User Group Seminars in October about Upgrade, Migration and Patching

Photo by Kyle Glenn on Unsplash

October 1 – Swiss Oracle User Group

October will start for me with a keynote for the Swiss Oracle User Group on Thursday, October 1 at 13:00h, about the Migration to Oracle Multitenant.

Registration: https://soug.ch/soug-day-september-21st-2020/
Date / Time:

KSAOUG – EMEA Groundbreakers – Database Patching – Welcome to the Jungle

Posted on September 29, 2020 by Mike.Dietrich Workshop 0

Sorry … posted by accident in the wrong category – this is the correct blog post:

User Group Seminars in October about Upgrade, Migration and Patching

–Mike…

Another surprise: DBMS_PDB in Oracle 11.2.0.4

Posted on September 17, 2020 by Mike.Dietrich Single-/Multitenant 2

I think I should start a blog post series of surprises. At least, this time it’s not harmful at all. But it still a surprise. And ACE Director Rodrigo Jorge reported about it and did ask me for more details. So yes, here we have Another surprise: DBMS_PDB in Oracle 11.2.0.4.

Photo by Kira auf der Heide on Unsplash

DBMS_PDB

You use the package DBMS_PDB normally to create the so called XML manifest file which describes the physical layout of a PDB or future PDB. And it adds some markers. You can find the package since Oracle Database 12.1.0.1 …

ORA-1555 won’t get reported into alert.log anymore since 19.4.0

Posted on September 10, 2020 by Mike.Dietrich Behavior Change 8

Many thanks to a German customer for showing me this tiny behavior change with Oracle Database RU 19.4.0. From this RU on the well known ORA-1555 won’t get reported into alert.log anymore since 19.4.0. But if you still like to see the “snapshot too old” error, then you can use a workaround.

ORA-1555 won't get reported into alert.log anymore since 19.4.0

Photo by Dariusz Sankowski on Unsplash

Why has this been changed?

Actually this is something I don’t understand completely. It may be just a side effect. Unpublished fix bug 29424999 – DUMP MINIMAL DIAGNOSTICS BY DEFAULT IN CASE OF ORA-01555 IN ADW/ATP ENV has been …

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

Posted on August 25, 2020 by Mike.Dietrich Flaws and Pitfalls 15

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.

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

Photo by Daniel J. Schwarz on Unsplash

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;

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

Posted on August 20, 2020 by Mike.Dietrich ASM / RAC / GI Flaws and Pitfalls 4

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 …

AutoUpgrade and the Partial Offline Backup Strategy

Posted on August 18, 2020 by Mike.Dietrich AutoUpgrade Flaws and Pitfalls 1

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:

Fallback Strategy: Partial Offline Backup

with graphics included. In brief, you take …

LAOUC Web Seminar: Oracle Multitenant – 3 PDBs and many secrets

Posted on August 17, 2020 by Mike.Dietrich Workshop 2

Just a few hours left until I’ll start my LAOUC Web Seminar: Oracle Multitenant – 3 PDBs and many secrets today, Aug 17, 2020. I’m really looking forward to it even though it is a bit sad this time. Usually in the past years I was traveling to Latin America. And I enjoyed the intense discussions in Uruguay, Argentina and Brazil a lot. The local folks invest so much time in these yearly user group conferences. Hence, as for most other events as well, this year you’ll be able to participate virtually over multiple days.

LAOUC Web Seminar: Oracle Multitenant - 3 PDBs and many secrets

Oracle Multitenant – 3 PDBs

What is the issue?

What happened?

Is Upgrade that complicated?

Details for Patch 123456789 not found.

What happens?

Why does it happen?

What’s new in the October 2020 version?

What a strange error

Security Alert

Unified Auditing

Very simple test setup

But it is true …

Which database version can be used with Grid Infrastructure 19c?

Default Behavior

Transport Constraints

Register for the Virtual Classroom Part 5 and Part 6 events

October 1 – Swiss Oracle User Group

DBMS_PDB

Why has this been changed?

The Testcase

What is the MGA?

Partial Offline Backup Strategy?

Oracle Multitenant – 3 PDBs

Archives

Categories

Upcoming Events

Upgrade @YouTube

Blog Stats

Subscribe via Email

Follow me on Twitter

Recent Posts