When I see how often my blog entry about XE18c is read, the interest in this release seems to be very big. This is also understandable, as several years have passed since the last XE version was released.
Many thanks to Gerald Venzl from Oracle who gave me the opportunity to test the release in advance. Of course I was very excited to see if the new version really offers the full EE functionality and therefore leaves nothing to be desired. It is also important to know that the new version can also be used without restrictions on VMWare.
Step 1 Prepare OS and install binaries
Already during the installation it shows up that Oracle is anxious to make the installation available as simply and as fast as possible. The release consists of two RPMs
As the names show, the first package creates all the necessary packages and configurations to run the database. These are already known since Oracle 12c. The second package contains the Oracle software which can now be installed directly from RPM.
Let’s start with the installation of the Prinstall Package on a current Oracle Linux release:
yum -y localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
The parameter and kernel setttings can be checked on /etc/sysconfig/oracle-database-preinstall-18c/
After the system has been configured to use Oracle, the second RPM can be used to start the installation of Oracle Home.
The default settings for ORACLE_HOME and ORACLE_BASE are:
If this configuration is correct, the installation can be started with the following command
yum -y localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm Examining oracle-database-xe-18c-1.0-1.x86_64.rpm: oracle-database-xe-18c-1.0-1.x86_64 Marking oracle-database-xe-18c-1.0-1.x86_64.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package oracle-database-xe-18c.x86_64 0:1.0-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ============================================================================================================ Package Arch Version Repository Size ============================================================================================================= Installing: oracle-database-xe-18c x86_64 1.0-1 /oracle-database-xe-18c-1.0-1.x86_64 6.6 G Transaction Summary Inventory located at: /opt/oracle/oraInventory Oracle home name defined as: OraHome_122 Registering Oracle home to the Oracle inventory... Oracle home registered to the Oracle inventory. Executing post installation scripts... Configuring ADR directories... ADR directories configured. Post installation scripts executed successfully. Oracle home installed successfully and ready to be configured. To configure Oracle XE Database you can execute the following service configuration script as root: /etc/init.d/oracle-xe-18c configure, the configuration parameters can be modified by editing the file /etc/sysconfig/oracle-xe-18c.conf Verifying : oracle-database-xe-18c-1.0-1.x86_64 Installed: oracle-database-xe-18c.x86_64 0:1.0-1 Complete!
During the installation you can already see that it is a full release, because the size of 6GB is not that small. At the end of the installation you will be advised how to create the XE database and that you can specify some DB parameters in /etc/sysconfig/oracle-xe-18c.conf.
We take a look at the file and can see, that we can choose the listener and database express port, the DB File location, Char Set and some password settings. You can not change the DB Name, PDB Name or DB Architecture. It will always create a CDB with one PDB with Instance Name XE and PDB Name XEPDB1. That Instance name restriction exists allready in XE 11g
#This is a configuration file to setup the Oracle Database. #It is used when running '/etc/init.d/oracle-xe-19c configure'. # LISTENER PORT used Database listener, Leave empty for automatic port assignment LISTENER_PORT= # EM_EXPRESS_PORT Oracle EM Express URL port EM_EXPRESS_PORT=5500 # Charecter set of the database CHARSET=AL32UTF8 # Database file directory # If not specified, database files are stored under Oracle base/oradata DBFILE_DEST= # Autogenerate Database password # if true, the database password will be automatically generated # if false, the database password will be prompted by script AUTOGENERATE_PASSWORD=false # SKIP Validations, memory, space SKIP_VALIDATIONS=true
Step 2 Create XE18c CDB
If the data in the config file are correct, the setup of the DB can be started. Here, again, only one command is necessary. You need root permission to start the installation process. Oracle then automatically starts the DBCA and starts to create the DB. If the option AUTOGENERATE_PASSWORD=FALSE is set, the password for the highly privileged users must be set first.
etc/init.d/oracle-xe-18c configure Specify a password to be used for database accounts. Note that the same password will be used for SYS, SYSTEM and PDBADMIN accounts: Confirm the password: Configuring Oracle Listener. Parsing command line arguments: Parameter "orahome" = /opt/oracle/product/18c/dbhomeXE Parameter "instype" = typical Parameter "inscomp" = client,oraclenet,javavm,server,ano Parameter "insprtcl" = tcp Parameter "cfg" = local Parameter "authadp" = NO_VALUE Parameter "responsefile" = /opt/oracle/product/18c/dbhomeXE/network/install/netca_typ.rsp Parameter "silent" = true Parameter "orahnam" = OraHomeXE Parameter "listenerparameters" = DEFAULT_SERVICE=XE Done parsing command line arguments. Oracle Net Services Configuration: Profile configuration complete. Listener "LISTENER" already exists. Oracle Net Services configuration successful. The exit code is 0 Configuring Oracle Database XE. [WARNING] [DBT-09251] The listener configuration is not selected for the database. EM DB Express URL will not be accessible. CAUSE: The database should be registered with a listener in order to access the EM DB Express URL. ACTION: Select a listener to be registered or created with the database. Prepare for db operation 7% complete Copying database files 29% complete Creating and starting Oracle instance 30% complete 31% complete 34% complete 38% complete 41% complete 43% complete Completing Database Creation 47% complete 50% complete Creating Pluggable Databases 54% complete 71% complete Executing Post Configuration Actions 93% complete Running Custom Scripts 100% complete Database creation complete. For details check the logfiles at: /opt/oracle/cfgtoollogs/dbca/XE. Database Information: Global Database Name:XE System Identifier(SID):XE Look at the log file "/opt/oracle/cfgtoollogs/dbca/XE/XE.log" for further details. Database configuration completed successfully. Connect to database using one of the following connect strings Multitenant container database: localhost:1521 Pluggable database: localhost:1521/XEPDB1 [root@oracle-12201-vagrant xe_preinstall]#
If the installation was carried out without errors, easy connect can now be used to connect to the XEPDB1 and check the default settings.
oracle@localhost:/opt/oracle/ [XE] sqlplus sys/manager@localhost:1521/XEPDB1 as sysdba Connected to: Oracle Database 18c Express Edition Release 18.104.22.168.0 - Production Version 22.214.171.124.0 SQL>
Step 3 Test it
Check 1. installed options
Now we are ready to see what the XE can do and whether all EE options are actually included and enabled.
SQL> select comp_name,version,status from dba_registry; COMP_NAME VERSION STATUS ------------------------------------------------- ------------------------------ ------------ Oracle Database Catalog Views 126.96.36.199.0 VALID Oracle Database Packages and Types 188.8.131.52.0 VALID Oracle Real Application Clusters 184.108.40.206.0 OPTION OFF JServer JAVA Virtual Machine 220.127.116.11.0 VALID Oracle XDK 18.104.22.168.0 VALID Oracle Database Java Packages 22.214.171.124.0 VALID OLAP Analytic Workspace 126.96.36.199.0 VALID Oracle XML Database 188.8.131.52.0 VALID Oracle Workspace Manager 184.108.40.206.0 VALID Oracle Text 220.127.116.11.0 VALID Oracle Multimedia 18.104.22.168.0 VALID Spatial 22.214.171.124.0 VALID Oracle OLAP API 126.96.36.199.0 VALID Oracle Label Security 188.8.131.52.0 VALID Oracle Database Vault 184.108.40.206.0 VALID
We can see oracle didn’t promise too much. The only option that is turned off is the RAC option. However, this is also logical as the XE can only be used as a single instance.
Check 2. Storage Lmitations
Let’s see what limits apply to the new XE database. First let’s see how many PDB’s we can create.
SQL> CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY manager 2 FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed/','/opt/oracle/oradata/XE/pdb2/') 4 ; Pluggable database created. SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY manager 2 FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed/','/opt/oracle/oradata/XE/pdb3/') 4 ; Pluggable database created. SQL> CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY manager 2 FILE_NAME_CONVERT=('/opt/oracle/oradata/XE/pdbseed/','/opt/oracle/oradata/XE/pdb4/') 4 ; CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY manager * ERROR at line 1: ORA-65010: maximum number of pluggable databases created SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 XEPDB1 READ WRITE NO 4 PDB2 MOUNTED 5 PDB3 MOUNTED
As already expected from other blog posts, a maximum of 3 PDB’s can be created in addition to the SEED PDB
Now we check the maximum size that the new Instance can reach. We try to create a big Tablespace and check what oracle does.
SQL> create tablespace test datafile '/opt/oracle/oradata/XE/pdb2/test.dbf' size 14G; create tablespace test datafile '/opt/oracle/oradata/XE/pdb2/test.dbf' size 14G * ERROR at line 1: ORA-12954: The request exceeds the maximum allowed database size of 12 GB. OK the limit is 12GB. so we try again with this size create tablespace test datafile '/opt/oracle/oradata/XE/pdb2/test.dbf' size 11G; Tablespace created.
Have we already reached the limit for the whole CDB or can any PDB grow to 12G? Let’s see what happens when we want to create a tablespace in another DB
SQL> alter session set container=pdb3; Session altered. SQL> create tablespace test datafile '/opt/oracle/oradata/XE/pdb3/test.dbf' size 1G; * ERROR at line 1: ORA-12954: The request exceeds the maximum allowed database size of 12 GB.
As expected, the size refers to the CDB, so always make sure that the size of all PDB’s does not exceed 12G.
But since we now have all options available we should be able to create the TS with compression, so the effective capacity which can be stored increases of course.
SQL> alter session set container=xepdb1; Session altered. SQL> drop tablespace test including contents and datafiles; Tablespace dropped SQL> create tablespace test datafile '/opt/oracle/oradata/XE/XEPDB1/test.dbf' size 11g DEFAULT COMPRESS FOR OLTP; Tablespace created
And yes it works 🙂 So if you know that you will store a lot of data in you XE PDB, then use compression to increase capacity
Now SGA, PGA,CPU,PROCESSES. What is possible ?
My tests have shown that PGA and SGA together can be 2G. If this limit is exceeded, the CDB can no longer be started. This also applies to the SGA_MAX_SIZE parameter. That means if SGA_TARGET + PGA_TARGET are smaller than 2G everything is OK, but if SGA_MAX_SIZE + PGA_TARGET is larger than 2G, the CDB will not start any more regardless of whether SGA_TARGET is smaller.
SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ allow_group_access_to_sga boolean FALSE lock_sga boolean FALSE pre_page_sga boolean TRUE sga_max_size big integer 1808M sga_min_size big integer 0 sga_target big integer 1808M unified_audit_sga_queue_size integer 1048576 SQL> show parameter pga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_limit big integer 2G pga_aggregate_target big integer 172M
Know i ony change SGA_MAX_SIZE to a biger value and the CDB rises an error on startup regardless SGA_TARGET and PGA_TARGET are smaller then 2G
alter system set sga_max_size=1900M scope=spfile; startup force; ORA-56752: Oracle Database Express Edition (XE) memory parameter invalid or not specified ORA-01078: failure in processing system parameters
The max Processes that are possible depends from the SGA_SIZE, but in my test it was possible to set the Processes on 2700. If i specified more then the CDB don’t start
Is it possible to define partitioned tables ?
The last test I did was to see if it was possible to create a partitioned table. This feature was probably most missed in the old XE version. At the same time I want to create the table with “COMPRESS FOR ALL OPERATIONS” to see if advances compression works too.
SQL> CREATE TABLE invoices (invoice_no NUMBER NOT NULL, invoice_date DATE NOT NULL, comments VARCHAR2(500)) compress for all operations PARTITION BY RANGE (invoice_date) (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users, PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users, PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users, PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users); Table created.
As expected, this also works as expected
My tests show that Oracle has made a big success with the new XE version. For the first time you get a fully functional database that leaves nothing to be desired. Additionally you don’t have to worry about licensing and you can run the DB on any infrastructure. Oracle databases can now be run on all virtualization platforms (Docker, VMWare etc) without having to use OVM. Of course you don’t get patches and support is not included, but I am convinced that developers will enjoy this release very much.