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.

What is happening?

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

Continue reading...

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

Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

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 …

Continue reading...

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

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

Continue reading...

Automatic SQL Plan Management in Oracle Database 19c

Automatic SQL Plan Management in Oracle Database 19cWhen 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

Continue reading...

DBMS_JOB – Behavior Change in Oracle 19c during upgrade

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 …

Continue reading...

Oracle Database 18c – Some important changes

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 …

Continue reading...

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

DBCA 12.2 does not update /etc/oratab in GI / RACInteresting 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

Continue reading...

Alert.log: New timestamp format in Oracle 12.2

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
Continue reading...

PGA_AGGREGATE_LIMIT enforces default since Oracle Database 12.2.0.1

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

Continue reading...

Having some fun with SEC_CASE_SENSITIVE_LOGON and ORA-1017

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 …

Continue reading...

Global Temporary Tables – Change in Oracle 12c

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 …

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

Oracle 12.1.0.2 – Security Behavior Change with non-SYSDBA Triggers

Oracle Database SecuritySometimes 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%');
Continue reading...

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

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

Continue reading...