On the 20th october the new free Oracle database version 18XE (Express Edition) was anounced (https://blogs.oracle.com/oracle-database/oracle-database-18c-xe-now-available).
I had a first quick look at the database but mostly at the documentation. And here are my thoughts about it.
Restrictions
- Up to 12 GB of user data (previously 11 GB)
- Up to 2 GB of database RAM (previously 1 GB) – that is SGA + PGA combined.
- Up to 2 CPU threads (previously 1 CPU – but multiple threads?!)
- Up to 3 Pluggable Databases (this is new)
Not much of an improvement.
Previously there was a restriction of only 1 XE per computer (not per VM). The license guide does not mention this restriction anymore. I think it means that we can have multiple XEs on the same machine, but they need to be in different VMs. In the area of cloud based installations, we might not even know if another XE is on the same server. So this is very good news.
missing and included features
The following list does not claim to be complete. But those are features that I find interesting to consider.
✓ multi tenant (up to 3 PDBs)
− no plug / unplug into all other databases
✓ partitioning
− no sharding
✓ in-memory column store
✓ compression
− no active session history (ASH) and other performance related diagnostics
✓ spatial & graph support
✓ encryption and redaction
✓ Database Vault, FGA
− RAC
In general everything that works is allowed! This is different from 11gXE!
Thoughts
Server/VM limitations
11g XE was limited to one installation per machine. This was specified in the 11gXE license agreement.
Any use of the Oracle Database Express Edition is subject to the following limitations;
1. Express Edition is limited to a single instance on any server;
…
18cXE is limited to one installation per VM (my interpretation).
The restriction that was previously in the license guide is gone, however the install guide for linux, still has this passage
4.2 Oracle Database XE Installation and Execution Restrictions
Only one installation of Oracle Database XE can be performed on a single environment. …
To run more than one Oracle Database instance or install more than one copy of the database software, upgrade to Oracle Database 18c Personal Edition, Oracle Database 18c Standard Edition 2, or Oracle Database 18c Enterprise Edition.
The word server was replaced by environment. I interpret that so, that on a single server with multiple environments (e.g. VM images) we can have multiple XE installations.
The other restricting factor is that the oracle SID is fixed to XE: I first thought it is hardcoded, but I seen one trustworthy report, where the claim was made that this can be changed. But even if that is possible, it is not clear whether that is allowed.
This might be problematic for upgrade scenarios (from 18XE to 19XE). We can not have two active DBs one old 18XE and the new 19XE on the same environment. Maybe not even if one is shut down (because of the only one copy of the db software issue).
There is a section in the installation guide, that seems to support this impression: Exporting and Importing Data for non-APEX Users. It describes an upgrade scenario via datapump. It specifically mentions to deinstall 11cXE.
2. Deinstall Oracle Database XE 11.2 if installation of Oracle Database XE 18c is planned on the same system. See Deinstalling Oracle Database XE for more information
There certainly is some clarification with regards to possible upgrade scenarios needed in the future. For now, only one XE installation on a single virtual box/machine.
Ressource restrictions
The 2 CPU threads might be one of the more limiting factors. It feels like a downgrade from the previous version. With hyperthreading a CPU could support easily up to 4 or even 8 threads. This is now limited to two threads. Oracle tries to make sure that we can not trick the limitations by useing advanced hardware.
This reminds my of the restriction introduced when switching to SE2 (standard edition 2). SE2 is limited to 16 (user) threads. Which essentially means, we should disable hyperthreading and just use normal CPU power.
Especially for web based applications the thread limit seems severe. In APEX we typcially have connection pools that serve up to 10 concurrent connections. Sometimes multiple connections pools (apex, apex_al, apex_pu, apex_rt). We need to rethink the default apex and ords settings. A thread on a CPU usually does not resemble a user process 1:1 but it can serve as a crude first estimation.
The 12 GB user data means we can not store many document files in the database. For most well structured data this should be reasonably enough. As long as no video, audio or very large photo collections are used. This still leaves the option to store documents as BFILE directly in the file system. This would avoid the data being used for the limited user table space. I’m not sure if temp and undo space is included in that limit. Main question seems to be, what exactly is measured.
Also note that system tablespace is not included in that 12 GB limit. Unfortunatly I can’t find the source anymore where I read about this.
This query might help measureing how much space is used.
select nvl(tablespace_name,' - Total - ') tablespace_name,
round(sum(bytes)/1024/1024/1024,1) as size_in_GB
from dba_segments
where tablespace_name not in ('SYSAUX','SYSTEM')
group by rollup(tablespace_name)
order by size_in_GB desc;
I think that restriction is quite ok. Although the improvement from 11gXE is only marginal.
In-memory
Since we are only able to use 2 GB RAM in-memory column storage is not really useful. It can be nice to test out this feature or maybe use it on very tiny databases.
It might also be useful in combination with partitioning. I’m not sure if that is possible, but if we load only the relevant (e.g. current) partitions into memory we might be able to stay within the 2GB limit even when data sizes grow.
More of a marketing stunt, that in-memory option is included.
Compression
There is basic and advanced compression. Could be highly useful, especially to save up on disk space. However if you ever plan to upgrade to a Standard Edition, don’t depend on compression. This also is an EE feature.
On the other hand compression might be used to lower the pressure on the 12 GB user data limit. Using advanced compression it should be easily possible to double the size of the user data without reaching the limit.
APEX
APEX is not preinstalled. This is in line with the current 18c db situation. In 11g/12cR1 APEX was preinstalled in the CDB, which was generally considered bad practice. It was changed with 18c (i believe) and XE behaves the same way. Still it would be nice to have an option during install that gives us ORDS and APEX in a PDB.
In general APEX (and ORDS) update their versions more frequently than the database,
This might change with the new release cycles of the database however. 19XE is supposed to come out with the 19.3 db version.
upgrade thoughts
We can not directly upgrade from 11gXE to 18cXE. Nor will we be able to upgrade from 18cXE to 19XE. The way to upgrade is to export the data and import it into the new DB.
multitenant
We got 3 PDBs to use. This is very nice. Remember SE2 (standard edition 2) only allows one plugged PDB (+the seed PDB). I still would only put one PDB into major use. But it might become much easier to clone a PDB to try out a few things there, without the need to shut the original PDB down.
Unfortunatly it is not possible to plug the XE PDB into a SE2 database. It is possible to plug it into an EE database or even into a Oracle Cloud Db. I believe the SE restriction has to do with the problem that several EE features are available in the XE, which are not available in SE/SE2.
database links
So far I do not know if it is is possible to create a DB link on an XE. I totally expect that it will work. The whole CDB/PDB concept uses DB links.
backup
It is now possible to use RMAN to backup a XE. Recommended!
Tuning and diagnostic tools
At first I heared that ASH and AWR reports will not be possible in XE. But in this thread from July 2018 Gerald Venzl explained otherwise
Will it be legal to use all these options like DIAGNOSTIC+TUNING, ADDM, AWR, ASH in production systems?
–> Yes
Meanwhile Gerald clarified that the feature did not make it into 18XE. It is now planned for 19XE.
The license guide has a detailed chapter showing which options are available and which not.
It would be great to have it. Performance troubleshooting is a topic on any production system. I don’t expect XE to be an exception.
production ready and other usages
Can we use it for production environments?
From a pure license standpoint it is ok. See the quote from the faq
Can I use Oracle 18c XE in production?
Oracle Database Express Edition does not restrict in which environment it can be deployed. However, Oracle Database Express Edition is not supported and does not receive any patches, including security patches. Oracle recommends to run production deployments on fully supported Oracle Database editions or Cloud Services.
So – yes we can! But should we use it in production? Yes and no. For non-critical applications (like if you want to manage your local club data via a XE + apex) that is certainly fine. Although the cloud based alternatives are interesting. However we will not receive any security fixes and don’t have oracle support (we are not paying for it, so that is fair). This might be a risk. And that’s why I wouldn’t recommend XE for mission critical stuff.
The other thing people already speculated, is to use an XE for testing purposes. If it is feature testing, then this we can already do with an EE (enterprise edition) using the OTN Developer license. Just recently the oracle prebuild developer-vm was upgraded to 18c. If you just want the database then you can use a vagrant-box to to provision the installation: Oracle DB 18.3 . Since XE will not receive any patches, we will not be able to test the newest version of a feature on it. So for pure testing purposes it would be a bad choice.
It might be an exellent tool to demonstrate the capability of an oracle database to critical customers however. If you are an ISV you could even use it to give your potential customer a trial version of your software running on an XE.
Conclusion
I’m very excited that 18cXE finally made it. I’m also happy that a lot of EE features are available. For demos, talks and presentations I will probably stay with the preconfigured Developer-VM and an EE version of the database. For long-running small side projects, I consider to setup one or two XEs. Additionally it is a great counter argument for all those “Oracle DB is (too) expensive” opinions.
useful links
XE Quick Start: https://www.oracle.com/database/technologies/appdev/xe/quickstart.html
XE licensing information: https://docs.oracle.com/en/database/oracle/oracle-database/18/xelic/licensing-information.html#GUID-0F2574A6-360F-4237-8098-17B02FFC3BB3
Oracle Vagrant-Boxes: https://blogs.oracle.com/developers/announcing-the-oracle-vagrant-boxes-github-repository
Jonathan Lewis about compression: https://www.red-gate.com/simple-talk/sql/oracle/compression-oracle-basic-table-compression/
svenweller 