Observations when upgrading to Oracle Database 18c

I recently upgraded the repository database of my Enterprise Manager Cloud Control 13.3 playground from release 12.2 to release 18.3. When checking the alert.log of my repository database – which is a Non-CDB – I found some interesting entries in the alert.log file.

Continue reading

Posted in Multitenant, Oracle 18c | Comments Off on Observations when upgrading to Oracle Database 18c

Oracle Database 18c, Standard Edition 2 & ORA-38153

When creating a new Oracle 18c database (Standard Edition 2) with dbca (custom database), I noticed lots of messages in the alert.log file:

Errors in file /u00/app/oracle/diag/rdbms/sn18/SN18/trace/SN18_j002_20332.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_36"
ORA-38153: Software edition is incompatible with SQL plan management.
ORA-06512: at "SYS.DBMS_SPM_INTERNAL", line 4911
ORA-06512: at "SYS.DBMS_SPM", line 2696
ORA-06512: at line 34

The message appears every 10 minutes.

There is no information available on My Oracle Support on this issue, but the error message of the ORA-38153 is pretty clear: SQL Plan Management is not supported with the Standard Edition 2 of the Oracle database.

oracle@kereru:~/ [SN18] oerr ora 38153
38153, 00000, "Software edition is incompatible with SQL plan management."
// *Cause: SQL plan management could be used only with Oracle Database Enterprise Edition.
// *Action: Ensure that Oracle is linked with the Enterprise Edition options.

However, this general statement is not 100% true; some basic SQL Plan Management is licensed with SE2. The licensing information (https://docs.oracle.com/en/database/oracle/oracle-database/18/dblic/Licensing-Information.html#GUID-0F9EB85D-4610-4EDF-89C2-4916A0E7AC87) states concerning “SQL Plan Management” in Oracle Database 18c:

SE2 and DBCS SE Summary: Only one SQL plan baseline per SQL statement is allowed and SQL plan evolution is disabled.

SE2 and DBCS SE Details:
1. SQL plan baselines can be created or captured using the following methods:
– Auto capture (OPTIMIZER_CAPTURE_SQL_PLAN_BASELINE=TRUE)
– Manual loading from the cursor cache (DBMS_SPM.LOAD_PLANS_FROM CURSOR_CACHE)
– Migration from stored outlines (DBMS_SPM.MIGRATE_STORED_OUTLINE)
– Import using DBMS_SPM.UNPACK_STGTAB_BASELINE

2. All capture and creation methods store only one SQL plan baseline per SQL statement.
3. SQL plan baselines can be exported and imported using DBMS_SPM.CREATE_STGTAB_BASELINE, DBMS_SPM.PACK_STGTAB_BASELINE, and DBMS_SPM.UNPACK_STGTAB_BASELINE.
4. Unused SQL plan baselines are not auto-purged.
5. Alternative SQL execution plans for SQL statements are not added to the SQL plan history.
6. SQL plan baselines can be altered and dropped (DBMS_SPM.ALTER_SQL_PLAN_BASELINE and DBMS_SPM.DROP_SQL_PLAN_BASELINE).

7. The following DBMS_SPM functions and procedures are not allowed: CONFIGURE, LOAD_PLANS_FROM_AWR, LOAD_PLANS_FROM_SQLSET, and all functions and procedures associated with SQL plan evolution.

But the cause – in terms of licensing – “SQL Plan Management not supported” is correct in this specific case, because the database job is started as an “automatic SQL tuning advisor task” – which cannot be licensed in SE2

To my opinion, the message can be ignored. It is just another example, that Oracle uses
the same software for both EE and SE2 and does not have a clear separation between both
editions. Like the fact that by default dbca creates a SE2 database
with the parameter CONTROL_MANAGEMENT_PACK_ACCESS set to “DIAGNOSTIC+TUNING”, although Diagnostic Pack and Tuning Pack cannot be licensed against SE2. And although CONTROL_MANAGEMENT_PACK_ACCESS is set to NONE in my database, all the AWR and tuning stuff is running in the background. And a simple select on a “DBA_HIST%’-AWR-view could turn a SE2 database into a more expensive EE database.

 

Update 13-SEP-2018:

JacekGebal (@GebalJacek) on Twitter: “EXEC dbms_auto_task_admin.disable(‘sql tuning advisor’, NULL, NULL);” solved the issue for him.

 

Deutsche Übersetzung dieses Beitrages

Posted in Oracle 18c | Comments Off on Oracle Database 18c, Standard Edition 2 & ORA-38153

Oracle Critical Patch Updates – some statistics

In January I started some posts on the Oracle Critical Patch Updates (CPU) on my German blog (www.markusdba.de). Here’s the translated version of my July post:

In January 2018 I started publishing some statistics and graphics about Oracle’s Critical Patch Updates in my blog. Yesterday Oracle released the July patches and so it’s time for the July update.

Continue reading

Posted in Common | Tagged , , | Comments Off on Oracle Critical Patch Updates – some statistics

Oracle Multitenant – SYS*-privileges on PDB level

In an Oracle Container Database, SYSDBA, SYSDG, SYSBACKUP etc. privileges can
be granted on PDB level. This enables PDB administrators with their local
users e.g. to open and to close a PDB. How does this work and where is
this privilege information stored?

Continue reading

Posted in Multitenant, Oracle 12c | Comments Off on Oracle Multitenant – SYS*-privileges on PDB level

Back home from nlOUG Tech Experience 2018 at the Rijtuigenloods/Amersfoort

Almost one week ago, I attended the conference of the Dutch Oracle User Group (nlOUG), “nloug Tech Experience 2018”. It was another great experience, once again, thanks to nlOUG for the chance of being part of it.

I had two presentations at the conference:

Taming the PDB – resource management and lockdown profiles

Which was about some new features of Oracle Database 12c Release 2 in the area of Multitenant databases:

The three investigators: OraChk, TFA and DBSAT .. which is an introduction into these three tools

The latter presentation was the substitute for a presentation of my former colleague Stefan Panek, who was not able to attend the conference. I had hoped for some more attendees J

I like the venue where the conference took place. It was an old railway workshop with a special flair. Most of the conference rooms where old railcars.

“nlOUG Tech Experience” was held for the second time. It is still a small conference, but it deserves more attendees. Let’s see what the future brings …

Posted in Common | Tagged , | Comments Off on Back home from nlOUG Tech Experience 2018 at the Rijtuigenloods/Amersfoort