Oracle 18c Express Edition: How to create a Non-Container-Database

Oracle 18c Express Edition (XE) comes with a container database as default database. Up to 3 pluggable databases are free so XE is a good start for getting familiar with the Multitenant architecture. However, you can still run a Non-Container-Database with Oracle 18c XE. You can simply use the Database Configuration Assistant (dbca) to create such a database.

Continue reading

Posted in Common, Multitenant, Oracle 18c | Leave a comment

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