An initiative of Mobiliar IT

XE18c ist available now! First experience report about the new Version

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

oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
oracle-database-xe-18c-1.0-1.x86_64.rpm

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:

ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE/
ORACLE_BASE=/opt/oracle/

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.0.0.0.0 - Production
Version 18.3.0.0.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                      18.0.0.0.0                     VALID
Oracle Database Packages and Types                 18.0.0.0.0                     VALID
Oracle Real Application Clusters                   18.0.0.0.0                     OPTION OFF
JServer JAVA Virtual Machine                       18.0.0.0.0                     VALID
Oracle XDK                                         18.0.0.0.0                     VALID
Oracle Database Java Packages                      18.0.0.0.0                     VALID
OLAP Analytic Workspace                            18.0.0.0.0                     VALID
Oracle XML Database                                18.0.0.0.0                     VALID
Oracle Workspace Manager                           18.0.0.0.0                     VALID
Oracle Text                                        18.0.0.0.0                     VALID
Oracle Multimedia                                  18.0.0.0.0                     VALID
Spatial                                            18.0.0.0.0                     VALID
Oracle OLAP API                                    18.0.0.0.0                     VALID
Oracle Label Security                              18.0.0.0.0                     VALID
Oracle Database Vault                              18.0.0.0.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

Conclusion

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: