If you recently created an Oracle Standard Edition 2 database, you might not be in compliance with Oracle after all. There are several obscure traps you could fall into during the SE2 database creation process. This post details each step of the installation and how to avoid paying millions in fines for features that were never used.
Before we begin creating the database, it is important to know there’s a specific SE2 installation. In recent versions, there’s only one binary for EE and SE2 (image below). If you are installing an older version, consult the documentation if it’s necessary to download a SE2 specific binary.
That said, let’s get back to the main topic: database creation.
Select Create a database:
Select Advanced Configuration:
Note: You must choose the Custom Database template– if you choose another template, such as Data Warehouse or General Purpose, the data files will be created with the wrong license.
SE2 can use only up to 3 pluggable databases per CDB, you cannot create more than that for the CDB. Multi-tenant is a paid, Enterprise Edition option.
No pitfalls in this window, choose the best option for your environment:
The same goes here–choose the best option for your environment. Important databases must have the FRA area specified and archivelog enabled:
Listener configuration, no surprises:
Some tricks in this step. Oracle Label Security and Oracle Database Vault are options for Enterprise Edition, therefore don’t select these flags.
As a best practice, I selected only the necessary components. Also, look for the type of application this database will support. When you install/create just the necessary components/features it avoids bugs, security issues, and upgrades and patching exercises run faster.
No traps here; choose the memory configuration and character set to suit your needs:
Uncheck the checkboxes and click Next:
For better security, you should choose a different password for each account. In this example, I preferred to choose just one password for all the accounts:
One of the most important steps. Don’t go forward before changing the parameters which can violate the SE2 license. Click on “All initialization Parameters”.
In the top-right corner click on Show advanced parameters flag and order the result by name:
Let’s change the “control_management_pack” to NONE and click on the Include in spfile flag. If you don’t mark this flag, after the database is created if you restart the database, the parameter will set up again with the default value:
Set “deferred_segment_creation” to FALSE. This is an Enterprise Edition feature, so don’t forget to include it in the spfile:
Set “Job_queue_processes” to zero. It’s required because RDBMS Oracle has some internal jobs that violate the SE2 license. After we disabled these jobs, we can set up the “job_queue_processes” to default value again:
Set “optimizer_adaptive_plans” and “optimizer_adaptive_statistics” to FALSE and include them in the spfile:
Take care of the “optimizer_use_sql_plan_baseline” and “optimizer_use_sql_plan”.
Baselines are usually for previous enterprise editions. According to the documentation for the 19c version, SE2 can use this feature, but with some limitations. Only one SQL plan baseline per SQL statement is allowed, and SQL plan evolution is disabled. Let’s avoid using it–better to be safe than sorry. If you need to use it, be cautious and double-check the documentation:
Set “parallel_max_servers” to zero. SE2 doesn’t allow parallelism. You can’t use parallelism in the backup, and you can’t increase the objects (tables and indexes) degree. One big pitfall is even if you set the “parallel_max_servers” to zero and your application is using SQL parallel hint, this SQL will violate the SE2 license. As a best practice, avoid using hints when possible:
Resource Manager is an Enterprise Edition feature, so change the “resource_limit” parameter to false.
So far so good. Now, we just need to click “create database”, right? Wrong. Create the response file for this setup and cancel the database creation:
Checking the response file saved:
If we had followed the database creation, the database would be installed with all components even with the flag unchecked. To avoid this problem we need to edit the file “$ORACLE_HOME/assistants/dbca/templates/New_Database.dbt”:
Change the value to false for the unnecessary components:
Now we’re ready to create the SE2 database. Use the response file created in the previous steps:
[[email protected] bin]$ ./dbca -silent -createDatabase -responseFile /home/oracle/dbca.rsp Enter SYS user password: Enter SYSTEM user password: Enter PDBADMIN User Password: Prepare for db operation 4% complete Creating and starting Oracle instance 5% complete 6% complete 8% complete Creating database files 9% complete 13% complete Creating data dictionary views 14% complete 16% complete 19% complete 20% complete 21% complete 23% complete 25% complete Oracle JVM 31% complete 38% complete 44% complete 46% complete Oracle Text 48% complete 50% complete Oracle Multimedia 63% complete Creating cluster database views 71% complete Completing Database Creation 73% complete 75% complete Creating Pluggable Databases 78% complete 88% complete Executing Post Configuration Actions 100% complete Database creation complete. For details check the logfiles at: /u01/app/oracle/cfgtoollogs/dbca/PYTHIAN. Database Information: Global Database Name:PYTHIAN System Identifier(SID):PYTHIAN Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/PYTHIAN/PYTHIAN0.log" for further details. [[email protected] bin]$ sqlplus / as sysdba SQL*Plus: Release 126.96.36.199.0 - Production on Mon May 23 07:38:37 2022 Version 188.8.131.52.0
Check if the components are correct:
Connected to: Oracle Database 19c Standard Edition 2 Release 184.108.40.206.0 - Production Version 220.127.116.11.0 SQL> set linesize 100 SQL> col comp_name for a40 SQL> select comp_name, status from dba_registry; COMP_NAME STATUS ---------------------------------------- ------------- Oracle Database Catalog Views VALID Oracle Database Packages and Types VALID Oracle Real Application Clusters OPTION OFF JServer JAVA Virtual Machine VALID Oracle XDK VALID Oracle Database Java Packages VALID Oracle XML Database VALID Oracle Workspace Manager VALID Oracle Text VALID Oracle Multimedia VALID
Now we need to disable the internal auto task job that, if executed, would violate the SE2 license:
SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT; CLIENT_NAME STATUS -------------------------------------------- -------- auto space advisor ENABLED auto optimizer stats collection ENABLED sql tuning advisor ENABLED BEGIN DBMS_AUTO_TASK_ADMIN.DISABLE ( CLIENT_NAME => 'SQL TUNING ADVISOR', OPERATION => NULL, WINDOW_NAME => NULL ); END; / PL/SQL procedure successfully completed. SQL> SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT; CLIENT_NAME STATUS --------------------------------------------- -------- auto space advisor ENABLED auto optimizer stats collection ENABLED sql tuning advisor DISABLED
After the auto task job is disabled we can set the “job_queue_processes” parameter again to the default value:
SQL> show parameter job_queue_processes NAME TYPE VALUE ---------------------- ----------- -------------- job_queue_processes integer 0 SQL> alter system set job_queue_processes=4000 scope=both sid='*'; System altered. SQL> show parameter job_queue_processes NAME TYPE VALUE --------------------- ----------- --------------------- job_queue_processes integer 4000
Check if any Enterprise Edition features were violated:
SQL> SELECT NAME, CURRENTLY_USED, FIRST_USAGE_DATE, LAST_USAGE_DATE, DETECTED_USAGES FROM DBA_FEATURE_USAGE_STATISTICS WHERE DETECTED_USAGES >0; no rows selected
I hope you found this post helpful. Feel free to share any questions or feedback in the comments, and make sure to sign up for updates so you don’t miss the next post.