UNDO_RETENTION not inherited to PDBs anymore since 19.9.0

Behavior changes introduced via a bug fix may not be something you like a lot. And thanks to Sreedhar from one of our most important customers I learned on the weekend: UNDO_RETENTION not inherited to PDBs anymore since 19.9.0.

UNDO_RETENTION not inherited to PDBs anymore since 19.9.0

Photo by Ben Wicks on Unsplash

What is happening before 19.9.0?

Until Oracle 19.8.0 you could change the UNDO_RETENTION in the CDB$ROOT, and it applied to all PDBs automatically. You may or may not have cared. But there are cases when you’d like to change an undo related parameter in the CDB$ROOT without its propagation into all PDBs.

This behavior …

Continue reading...

Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE

Many of you recognized that we delivered a new feature with Oracle Database 19.10.0, the January 2021 Release Upgrade. So I’d like to put a few interesting things together from the upgrade and migration perspective regarding Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE.

What are Blockchain Tables?

At first, I don’t want to repeat what other people have blogged and written about already. You may just go through the blog posts of Connor McDonald and Tim Hall and others to learn more about this cool feature.

Continue reading...

When and how should you change COMPATIBLE?

When and how should you change COMPATIBLE?

COMPATIBLE is an almost mystic parameter. It has a default setting for each release. But if you try to find more information what it really does, you won’t be very happy. And in reply to my previous blog post about whether you need to change COMPATIBLE when you apply an RU, I received the following question: When and how should you change COMPATIBLE?

What does COMPATIBLE do?

To find an answer to this question, I consulted the documentation at first. And I found this:

    Setting COMPATIBLE ensures that new features do not write data formats or structures
Continue reading...



There usually a philosophic battle happening when it comes to underscore parameters. The official statement is more or less: It’s an underscore, hence it is undocumented and you shouldn’t touch it unless Oracle Support advises you to do so. I agree in many cases. But in this specific one, I don’t. And in this post I will explain why you should set _EXCLUDE_SEED_CDB_VIEW=FALSE in all your singe- and Multitenant environments. Always!

A bit of history

At first, a little bit of a history excursion. This parameter wasn’t an underscore in Oracle 12.1. You can find it as EXCLUDE_SEED_CDB_VIEW in …

Continue reading...

_optimizer_ignore_hints is now optimizer_ignore_hints

I’m refreshing our Hands-On Lab for Oracle Database 18c. And as part of a performance evaluation I wanted to set an underscore parameter to ignore all hints my load test tool sets. But then I learned: This does not work anymore as _optimizer_ignore_hints is now optimizer_ignore_hints. We made the underscore obsolete in Oracle Database 18c.

_optimizer_ignore_hints is now optimizer_ignore_hints

_optimizer_ignore_hints is now optimizer_ignore_hints

Actually in Oracle Database you can find a long list of obsolete parameters here:

To be precise: We obsoleted159 parameters. In addition, in Oracle Database 18c we added an additional …

Continue reading...

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Drop a tablespace in a PDB with a Guaranteed Restore Point being activeThis was an interesting question I received from Alain Fuhrer a couple of weeks ago: “Why can’t I drop a tablespace in a PDB whit a Guaranteed Restore Point being active?”. It’s important to note that the GRP is not active in the same but in another PDB.

An SR resolved the issue. But in case you face the same situation, here’s the solution.

Drop a tablespace in a PDB with a Guaranteed Restore Point being active

Simple test scenario. These are my PDBs:

show pdbs

---------- ------------------------------ ---------- ----------
Continue reading...

SPFILE Parameter: max_pdbs – a must for Single Tenant

Sometimes my job has a aspect making me smile at the end of the day šŸ˜‰

I sat together with Johannes Ahrends during a talk at the OUGN Conference on the boat from Oslo towards Kiel. And we were discussing afterwards why there’s no official way to limit the number of PDBs which will be essential for customers wanting to go the Single Tenant track. I had my Hands-On environment up and we played a bit in the break recognizing that a constraint on CONTAINER$ won’t be the correct solution as unplug/plug operations leave leftovers in it unless you …

Continue reading...

Deprecated Parameters in Oracle Database

This is the 3rd posting in my series about init.ora/SPFILE parameters in Oracle Database


Finally, here’s the list of DEPRECATED parameters in Oracle Database


Only the ones in BOLD were newly marked as DEPRECATED in Oracle Database …

Continue reading...

Obsolete SPFILE Parameters in Oracle Database

This is the 2nd posting in my series about init.ora/SPFILE parameters in Oracle Database

Find the list of the 159 obsoleted parameters here (and of course in V$OBSOLETE_PARAMETERS):

Continue reading...

New SPFILE parameters in Oracle Database

This is the 1st posting in my series about init.ora/SPFILE parameters in Oracle Database

Oracle Database is available now in the Oracle Cloud.

And this is the list of 46 new init.ora/spfile parameters compared to Oracle Database – including the links (where possible) to the Oracle Database 12.2 Reference documentation.




allow_global_dblinks LDAP lookup for DBLINKS
allow_group_access_to_sga Allow read access for SGA
Continue reading...

GC Freelist Session Waits causing slowness and hangs

Best Practice Hint

One of the best things in my job:
I learn from you folks out there. Everyday.

Credits here go to Maciej Tokar who did explain the below topic to me via LinkedIn – thanks a lot, Maciej!

Locks are not being closed fast enough, resulting in gc freelist waits

You can find a reference for Global Cache Freelist in the Oracle Documentation. This issue here can or will lead to database being slow, up to complete hangs. Based on my research it looks as the issue is not related to RAC only but a general thing. In your session …

Continue reading...

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing.

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) – and the same thing happens with an IAS (Insert Append Select).

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter.

  • Oracle White Paper: Best Practices for Gathering Statistics –
Continue reading...


Just learned something new I couldn’t find actually in the doc at the first glance:

There’s a new init.ora parameter introduced in Oracle Database named:

By default it is set to FALSE and the parameter got introduced because not only the external use of Oracle GoldenGate requires a valid license but also the use of the internal APIs. For example, XStream provides high performance APIs that enable client
applications to receive and send real-time data changes from an Oracle
database. Other APIs were added for encryption support, trigger
suppression, etc. None of these APIs are licensed with

Continue reading...

Parameter _rollback_segment_count can cause trouble

Just some weeks ago we’ve learned that setting the hidden underscore parameter:


may cause trouble during upgrade. This parameter is used in very rare cases to have under all circumstances and situations this specified number of UNDO’s online. Now during upgrade this may result in massive latch contentionĀ  – and there’s a patch available as well. Recommendation is to unset it during upgrade.

I don’t think that many people will hit this as I personally haven’t seen databases with this underscore in their init.ora or spfiles. So take this post more or less as a reminder …

Continue reading...

DBMS_SCHEDULER jobs stuck after upgrade to 11.2? Check this parameter!

I think I learn something new practically every time that I talk to or work with customers. Here’s a nugget of information that may be worth its weight in gold if you are upgrading in a RAC environment. You might find after the upgrade that your DBMS_SCHEDULER jobs seem to be scheduled but unable to run, as if the scheduler is stuck for some reason.

The reason for this is an interesting parameter job_queue_processes whose behavior changed in 11.2. The parameter itself has been around for a long time, but here is the important note from the 11.2 Upgrade

Continue reading...

New hidden parameters in Oracle 11.2

We really welcome every external review of our slides. And also recommendations from customers visiting our workshops.

So it happened to me more than a week ago that Marco Patzwahl, the owner of MuniqSoft GmbH, had a very lengthy train ride in Germany (as the engine drivers go on strike this week it could have become even worse) and nothing better to do than reviewing our slide set. And he had plenty of recommendations.

Besides that he pointed us to something at least I was not aware of and added it to the slides:

In patch set …

Continue reading...