Behavior Change

Silent ORA-918 behavior change in RU 19.17.0 and newer

I think we have the best customers out there. It is incredible what some people find. We would be lost without you, and this blog would contain only a fraction of the information we can share with you. In this case let me explain the silent ORA-918 behavior change in RU 19.17.0 and newer. And all credits go to Peter Lehmann from Deutsche Telekom who found this issue only a few days after we released 19.17.0.

Silent ORA-918 behavior change in RU 19.17.0 and newer

Photo by Drew Farwell on Unsplash

What is happening?

Peter shared an awesome and simple test case with me. A query with ambiguously defined …

UNDO_RETENTION not inherited to PDBs anymore since 19.9.0

Posted on July 12, 2021 by Mike.Dietrich Behavior Change 15

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 …

New parameters and features in Oracle Database 19.11.0

Posted on April 26, 2021 by Mike.Dietrich Behavior Change Patch Recommendation 6

As usual, as soon as I apply the newest RU, I do also a quick check for New parameters and defaults in Oracle Database 19.11.0.

New parameters and features in Oracle Database 19.11.0

Photo by Roma Kaiuk on Unsplash

New Parameters in Oracle 19.11.0

Based on customer feedback, there is one really big improvement since this RU release. Not only new features but also parameter changes get listed. You will find them in the 19c New Features Guide.

In this RU you will find only 1 new parameter:

drcp_dedicated_opt
The description says “Turn on/off dedicated optimization for DRCP” – and the default is “YES

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 48

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 …

Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE

Posted on February 18, 2021 by Mike.Dietrich Behavior Change Oracle Database 19c Parameters Patch Recommendation 4

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.

Oracle Database 19.10.0 and Blockchain Tables and COMPATIBLE

Photo by Daniel von Appen on Unsplash

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.

Connor McDonald about “Blockchain

New Parameters in Oracle 19.10.0 – and a default change

Posted on February 8, 2021 by Mike.Dietrich Behavior Change Parameters 9

Oracle Database 19.10.0, the January 2021 Release Update (RU), does seem to change some well known rules. Especially when you have a closer look to a backported feature, Blockchain Tables. But I will write a bit more about this change next week. For now, let’s have a closer look at New Parameters in Oracle 19.10.0 – and a default change.

Photo by Stephen Leonardi on Unsplash

New Parameters in Oracle 19.10.0

This is the list of new parameters in the January 2021 Release Update 19.10.0:

Parameter	Description	Default
blockchain_table_max_no_drop	maximum idle retention minutes for blockchain table	NONE
container_data

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 …

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

Posted on May 28, 2020 by Mike.Dietrich Behavior Change Optimizer 42

Last week I recorded seminars – and I wanted to garnish the Performance part with demo recordings from our Hands-On Lab. I used the Hands-On Lab instructions we published. And while I edited a recording, I realized that there was something new I haven’t seen before. Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0? Of course you do …!

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

Photo by Pete Wright on Unsplash

What happened?

In my environment I run load against my database, I collect statements from AWR and from Cursor Cache into two separate SQL Tuning Sets (STS). And then I upgrade my …

DBMS_JOB one-off patch needed for Oracle 19.3.0 – 19.7.0

Posted on May 21, 2020 by Mike.Dietrich Behavior Change Patch Recommendation 16

A while ago I blogged already about this behavior change for DBMS_JOB in Oracle Database 19c. Many of you are aware of this. But today I learned something new about it. Kudos to Alexey Makhmutov for bringing this to my attention. There is a very important fix you will need on top in 19c to avoid performance issues. If you rely on jobs with the old job interface, take note: DBMS_JOB one-off patch needed for Oracle 19.3.0 – 19.7.0

DBMS_JOB one-off patch needed for Oracle 19.3.0 - 19.7.0

Photo by Christopher Burns on Unsplash

What is the problem?

As you can read from there is a internal …

Behavior Change in Oracle 18c/19c: No symbolic links for Data Pump directories

Posted on July 15, 2019 by Mike.Dietrich Behavior Change 8

Most of you may have recognized the desupport of UTL_FILE_DIR with Oracle Database 18c. Reason is mostly that UTL_FILE_DIR opens a lot of possibilities to do insecure things. But this has another effect which may not be obvious to you. There’s a behavior change in Oracle 18c/19c: No symbolic links for Data Pump directories. Read further to learn more about it.

UTL_FILE_DIR Desupport

In Oracle Database 18c we announced the desupport of UTL_FILE_DIR initialization parameter. But as you can read as well in the Database Upgrade Guide, there’s this remark:

UTL_FILE Package Symbolic Link in Directory Paths Not Supported

DBMS_JOB – Behavior Change in Oracle 19c during upgrade

Posted on May 24, 2019 by Mike.Dietrich Behavior Change 49

DBMS_JOB - Behavior Change in Oracle 19c during upgrade

Actually I missed to blog about this change but luckily a colleague did ask a question the other week about the migration of jobs when you upgrade to Oracle 19c. Let me shed some light on DBMS_JOB – Behavior Change in Oracle 19c.

DBMS_JOB vs DBMS_SCHEDULER

Back in the 10g days, we introduced a new interface to run and monitor jobs in the database: The scheduler. The scheduler with DBMS_SCHEDULER is way more powerful than the old job interface. And we use it internally a lot as well. But as things appear in the real world. the introduction …

Oracle Database 18c – Some important changes

Posted on March 7, 2018 by Mike.Dietrich Behavior Change Oracle Database 18c 7

I know that Oracle Database 18c is available in the Oracle Cloud and on Exadata engineered systems only right now. But actually I’ve had conversations with some customers who downloaded Oracle 18c on-prem software for Exadata and installed it on their systems. Therefore it may be useful to talk about Oracle Database 18c – Some important changes.

Oracle Database 18c – Some important changes

I will highlight some important changes but of course won’t cover all of them here.

Installation

You may recognize the first change after downloading the image: The installation and configuration of Oracle Database software is simplified …

Automatic Maintenance Jobs are enabled after upgrade

Posted on February 27, 2018 by Mike.Dietrich Behavior Change 7

This is a strange behavior – but it seems as automatic maintenance jobs are enabled after upgrade. A customer (thanks Naveen!!) sent me an email the other week asking if there’s a flag in DBUA to prevent this enabling as on some of their databases the automatic maintenance jobs are disabled on purpose.

Automatic Maintenance Jobs are enabled after upgrade

It sounded kind of strange to me – and my first test was to use the catctl.pl instead of the DBUA.

First of all I did check the status of the Automatic Maintenance Jobs in my 11.2.0.4 database:

SELECT client_name,

DBCA 12.2 does not update /etc/oratab in GI / RAC

Posted on December 20, 2017 by Mike.Dietrich ASM / RAC / GI Behavior Change 12

Interesting things happen. And I learned (credits to Arun Gupta and others) that there is a change in Oracle Database 12.2 environments I wasn’t aware: The DBCA 12.2 does not update /etc/oratab in GI / RAC environments. Let me give you some extra information and hints on this topic as it may cause some strange situations.

DBCA 12.2 does not update /etc/oratab in GI / RAC

Arun Gupta commented on the blog:

Another case in point. DBCA fails to update the /etc/oratab file in 12.2 when a database is created. There is no documentation of this behavior. So, I opened

Behavior Change: READ privilege for user SYSTEM in Oracle 12.2

Posted on December 1, 2017 by Mike.Dietrich Behavior Change 2

All credits here go to Marcel Pils from Logicalis, a German Oracle partner. Thanks Marcel! In Oracle 12.2 there’s an interesting behavior change: READ privilege for user SYSTEM in Oracle 12.2.

Some Background Information

In Oracle 12.1 the READ privilege has been introduced. Please find more information in the Oracle 12.1 Security Guide: New READ Object Privilege and READ ANY TABLE System Privilege for SELECT Operations. The idea behind the READ object and the READ ANY TABLE system privileges is that you can enable users query database tables, views, materialized views, and synonyms. But they can’t lock rows …

ORA-44787 – Don’t mess with the Default Oracle Service

Posted on August 16, 2017 by Mike.Dietrich Behavior Change Single-/Multitenant 11

ORA-44787 - Don't mess with the Default Oracle Service At the moment I work with one of our reference customers, Swiss Mobiliar Insurance, on their Multitenant upgrade to Oracle Database 12.2.0.1. And we encountered an “interesting” issue. After upgrade we received an ORA-44787 making it impossible to switch between containers with “alter session set container=pdb1;” commands. Lesson learned now: If you don’t want ORA-44787 – Don’t mess with the Default Oracle Service.

What has happened?

Very simple case. We approach a database upgrade with a Multitenant deployment of over 90 PDBs. Source database version is Oracle Database 12.1.0.2.BP170117, destination version is Oracle Database 12.2.0.1.RU170718. …

Alert.log: New timestamp format in Oracle 12.2

Posted on May 30, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 3

Timestamp Format Change

There’s an interesting change in the alert.log since Oracle Database 12.2.0.1: The format of the timestamps has changed.

If you compare the timestamps in Oracle 12.1 vs Oracle 12.2 you’ll recognize the change immediately:

Oracle 12.1

Oracle 12.2

Wed Feb 08 15:39:29 2017
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Wed Feb 08 15:39:29 2017
ALTER DATABASE OPEN
Wed Feb 08 15:39:29 2017
Ping without log force is disabled.
Starting background process TMON

2017-05-29T14:09:17.064493+02:00
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
2017-05-29T14:09:17.252853+02:00
ALTER DATABASE OPEN

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

Posted on May 4, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 Parameters 11

The init.ora/spfile parameter PGA_AGGREGATE_LIMIT got introduced in Oracle Database 12.1.0.1.

As per documentation in Oracle Database 12.1 it got defined as:

“PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.“.

Furthermore the algorithm for its setting got described as:

“By default, PGA_AGGREGATE_LIMIT is set to the greater of 2 GB, 200% of PGA_AGGREGATE_TARGET, and 3 MB times the PROCESSES parameter. It will be set below 200% of PGA_AGGREGATE_TARGET if it is larger than 90% of the physical memory size minus the total SGA size, but not below 100% of PGA_AGGREGATE_TARGET.”

Default

SQL*Plus Banner looks different since Oracle Database 12.2

Posted on May 3, 2017 by Mike.Dietrich Behavior Change 0

Did you recognize that the login messages displayed when you start SQL*Plus in Oracle Database 12.2.0.1 looks different than in releases before?

SQL*Plus banner in Oracle Database 12.2.0.1:

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 2 11:23:10 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

SQL*Plus banner in Oracle Database 12.1.0.2 and before:

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 2 11:24:15 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c

Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

Posted on April 24, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 Security 21

The init.ora/spfile parameter SEC_CASE_SENSITIVE_LOGON got deprecated since Oracle Database 12.1.0.1. This means, we don’t do any further developments to it, you shouldn’t change it from its default TRUE – and if you still do you’ll receive a nice warning during STARTUP of your database:

SQL> alter system set sec_case_sensitive_logon=false scope=spfile;

System altered.

SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Recently a customer asked me if we’d changed the behavior of this parameter in Oracle Database 12c Release 2 as he receives now an ORA-1017: Invalid username or password error when …

Security change in Oracle Database 12.2 with login.sql

Posted on April 17, 2017 by Mike.Dietrich Behavior Change Oracle Database 12.2.0.1 Security 12

Sometimes there are tiny little changes in a new release – and sometimes they are not documented very well yet. One of these changes is the new behavior of login.sql for SQL*Plus in Oracle Database 12.2.0.1.

Behavior before Oracle Database 12.2

In all releases until Oracle Database 12.1.0.2 by default SQL*Plus searches for the user profile (login.sql) in your current directory first, and then in the directories you specify with the ORACLE_PATH environment variable.

Please be aware that this change can be introduced with a PSU or a BP from 2017 as well.

Behavior since Oracle Database 12.2

Since Oracle …

New SPFILE parameters in Oracle Database 12.1.0.2 with July 2016 (and newer) PSU/BP

Posted on February 1, 2017 by Mike.Dietrich Behavior Change Oracle Database 12c Parameters 7

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

By following an internal discussion and checking parameter changes between Patch Set Updates (PSU) and Proactive Bundle Patches (BP) I learned that we introduced two new SPFILE parameters in Oracle Database 12.1.0.2 with the July PSU and BP. One is documented in the patch readme, the other one can be found right now only in the Oracle Database 12.2.0.1 manual:

ALLOW_GROUP_ACCESS_TO_SGA

ENCRYPT_NEW_TABLESPACES

The Oracle 12.2 documentation about ALLOW_GROUP_ACCESS_TO_SGA, the parameter which appears not in the Oracle 12.1 documentation right now, says:

ALLOW_GROUP_ACCESS_TO_SGA controls group access to shared memory

Global Temporary Tables – Change in Oracle 12c

Posted on July 7, 2016 by Mike.Dietrich Behavior Change Optimizer Oracle Database 12c 0

A few weeks back I was copied on an email conversation about a important change with Global Temporary Tables (GTT) in Oracle Database 12c.

Something you need to be aware of when using GTTs in Oracle Database 12.1.0.2:
Prior to this release GTTs shared statistics between sessions. Statistics were SHARED between different sessions. Now since Oracle Database 12c this is different by default – statistics on GTTs are visible to the SESSION only.

This can be changed and altered of course. And there are pros and cons. But as I’m not an optimizer expert I’m glad that …

GC Freelist Session Waits causing slowness and hangs

Posted on March 22, 2016 by Mike.Dietrich Behavior Change Flaws and Pitfalls Oracle Database 12c Parameters Patch Recommendation 0

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 …

Parameter Recommendations for Oracle Database 12c – Part II

Posted on March 8, 2016 by Mike.Dietrich Behavior Change Oracle Database 12c Parameters Patch Recommendation 6

Preface

Again, please be advised – the following parameter list is mostly based on personal experience only. Some of them are officially recommended by Oracle

Behavior Change

What is happening?

What is happening before 19.9.0?

New Parameters in Oracle 19.11.0

What has been changed?

What are Blockchain Tables?

New Parameters in Oracle 19.10.0

Why has this been changed?

What happened?

What is the problem?

UTL_FILE_DIR Desupport

DBMS_JOB vs DBMS_SCHEDULER

Oracle Database 18c – Some important changes

Installation

Automatic Maintenance Jobs are enabled after upgrade

DBCA 12.2 does not update /etc/oratab in GI / RAC

Some Background Information

What has happened?

Timestamp Format Change

Default

SQL*Plus banner in Oracle Database 12.2.0.1:

SQL*Plus banner in Oracle Database 12.1.0.2 and before:

Behavior before Oracle Database 12.2

Behavior since Oracle Database 12.2

New Parameters in Oracle Database 12.1.0.2 with July 2016 PSU/BP

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

Preface

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Follow me on Twitter

Recent Posts