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…

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

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

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

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 Value Change in Oracle Database…

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 having SEC_CASE_SENSITIVE_LOGON=FALSE…

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 my colleague Nigel…

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 – Page 13 (PDF: 15) http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-12c-1967354.pdf…

Oracle 12.1.0.2 – Security Behavior Change with non-SYSDBA Triggers

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%’); ERROR at…

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 Oracle 10.2). Please be very careful with…