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 …

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 …

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

Automatic SQL Plan Management in Oracle Database 19c

Posted on June 3, 2019 by Mike.Dietrich Oracle Database 19c SPM - SQL Plan Management 6

When you upgrade to a new database release, there will be changes. Some are obvious, others are not. This one is somewhere in between because it is documented quite well but not very well known yet as far as I see. I’m talking about the Automatic SQL Plan Management in Oracle Database 19c.

What is changing in Oracle 19c?

PLEASE READ THIS UPDATED BLOG POST:
https://mikedietrichde.com/2019/10/02/automatic-sql-plan-management-slight-change-with-19-4-0/
AS THERE IS A CHANGE WITH ORACLE 19.4.0.

First of all, I don’t want to rewrite everything Nigel Bayliss wrote in his detailed blog post about Automatic SQL Plan Management in Oracle 19c …

DBMS_JOB – Behavior Change in Oracle 19c during upgrade

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

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 …

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

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

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 …

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 …

New in Oracle 12c: _optimizer_gather_stats_on_load

Posted on October 6, 2015 by Mike.Dietrich Behavior Change Optimizer Oracle Database 12c Parameters 1

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 –

Oracle 12.1.0.2 – Security Behavior Change with non-SYSDBA Triggers

Posted on May 26, 2015 by Mike.Dietrich Behavior Change Flaws and Pitfalls Oracle Database 12c Security 6

Oracle Database Security Sometimes things get revealed at unexpected occasions. This one happened during a recent customer upgrade to Oracle Database 12c with a 3rd party geospatioanl application installed (ESRI).

At the very end of the upgrade the customer saw many ORA-1031 (insufficient privileges) errors and it seemed to be that nothing was working correctly anymore.

This happened during the run of catupend.sql. The following code path in catupend.sql causes the error.

cursor ddl_triggers
is
select o.object_id from dba_triggers t, dba_objects o
where t.owner = o.owner and t.trigger_name = o.object_name and o.object_type = 'TRIGGER'
and (t.triggering_event like '%ALTER%' or t.triggering_event like '%DDL%');

New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

Posted on March 2, 2015 by Mike.Dietrich Behavior Change Oracle Database 12c Security 5

You’ve just upgraded to Oracle Database 12c – but your favorite admin tool receives an ORA-1031: Insufficient Privileges after connection?

Then the reason may be the reduced set of privileges for the SELECT ANY DICTIONARY privilege. This privilege does not allow access to tables USER$, ENC$ and DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, and XS$VERIFIERS. Actually such changes are not new. For instance in Oracle 10.1 we removed the access to LINK$ in SELECT ANY DICTIONARY (well, this may have happened because the dblink’s password was stored in clear text in LINK$ – a misbehavior which is fixed since …

Behavior Change

Silent ORA-918 behavior change in RU 19.17.0 and newer

What is happening?

UNDO_RETENTION not inherited to PDBs anymore since 19.9.0

What is happening before 19.9.0?

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

What happened?

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

UTL_FILE_DIR Desupport

Automatic SQL Plan Management in Oracle Database 19c

What is changing in Oracle 19c?

DBMS_JOB – Behavior Change in Oracle 19c during upgrade

DBMS_JOB vs DBMS_SCHEDULER

Oracle Database 18c – Some important changes

Oracle Database 18c – Some important changes

Installation

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

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

Alert.log: New timestamp format in Oracle 12.2

Timestamp Format Change

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

Default

Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

Global Temporary Tables – Change in Oracle 12c

New in Oracle 12c: _optimizer_gather_stats_on_load

What caused this change?

Oracle 12.1.0.2 – Security Behavior Change with non-SYSDBA Triggers

New Behaviour in Oracle Database 12c and 11.2.0.4: SELECT ANY DICTIONARY with reduced privilege set

Archives

Categories

Upgrade @YouTube

RSS Feed for Upgrade your Database – NOW!

Subscribe via Email

Recent Posts

Follow me on Twitter