Consider integration of oracle XE

As a developer, I want as much automation for installing my required toolings as possible so that I can save time and use it for other things. Currently devonfw-ide focuses on local sandbox tools. Only docker is some kind of excuse that is a global tool. Why not also supporting other global tools and simplify the installation for the users in IDEasy?

Meanwhile Oracle XE can be downloaded without login, etc. - see e.g. https://download.oracle.com/otn-pub/otn_software/db-express/OracleXE213_Win64.zip Already being able to automatically download the latest or a specific version and running the installer could save some time.

However, it should be considered that a better alternative for projects is to use docker in combination of a project internal container image repository where Oracle could be a prepared image. This could even allow to customize the DB setup and auto-run some scripts to create users and tune some parameters as needed for the project. Still I can not believe that XE that is designed for development only has password expiry per default. Things like this are the first options to change and every project I have seen so far does this so IDEasy could help here with further automation. Also disabling the HTTP port 8080 in XE could be automated. There are many more such things that could be considered...

We had long discussions about this topic and came to the following conclusion:

instead of installing Oracle DB natively on the host, it is easier and more flexible to run it in a container (docker/podman)
when connecting to the DB via JDBC (from the app in development) or via tools like Oracle SQLDeveloper it does not make a difference for the user whether oracle is running on the host or in a container.
however, when the user wants to run tools like sqlplus or especially impdp or expdp this makes a big difference and this is where we should provide additional help, guidance and support in IDEasy.
Oracle provides an instance-client that can be downloaded and configured to a remote DB (e.g. the one in the container). This works fine with sqlplus but however for impdp there is a bug causing error ORA-39012: Client detached before the job started.
Finally, instead of implementing Java commandlets like here specific for Oracle that orchestrate container setup and invocation of commands inside the container (e.g. to circumvent the impdp bug) it may be smarter to provide this as simple bash scripts: In the end we need a script that is pulling a container image, launching the container - and we need another script for doing post-setup steps (e.g. for Oracle run some SQLs to set unlimited password lifetime, create user and potential DDL), and actually provide a script like impdp or sqlplus that can be invoked on the host but will delegate to the according tool inside the container. The benefit is that these bash scripts can still run on any OS but unlike our Java commandlets every project can customize them to their individual needs and there will always be requirements that we can never think of before. Ideally we can create a marketplace for such scripts (Oracle would be just one such use-case) and allow users to provide and consume such scripts/solutions. As all such scripts can rely on IDEasy they can utilize on whatever tools they need (e.g. terraform) without worrying where to get them from.

Deploy an Oracle database instance in a Docker container

General Info: Install the Oracle Client software

Download the Basic package, SQL*Plus Package and Tools Package from Oracle and decompressed them in one directory:

https://download.oracle.com/otn_software/nt/instantclient/2112000/instantclient-basic-windows.x64-21.12.0.0.0dbru.zip https://download.oracle.com/otn_software/nt/instantclient/2112000/instantclient-sqlplus-windows.x64-21.12.0.0.0dbru.zip https://download.oracle.com/otn_software/nt/instantclient/2112000/instantclient-tools-windows.x64-21.12.0.0.0dbru.zip

Add the directory to your PATH environment

General Info: how to connect to the database

cluster database: sqlplus "sys/let-me-in@0.0.0.0:1521 as sysdba" sqlplus "system/let-me-in@0.0.0.0:1521"

pluggable database sqlplus "sys/let-me-in@0.0.0.0:1521/XEPDB1 as sysdba" sqlplus "system/let-me-in@0.0.0.0:1521/XEPDB1"

Provide an image

The sources for the Oracle image can be fetched from Github and after that the image can be created from the sources (1), or you can download the finished image directly from container-registry.oracle.com (2). To avoid errors caused by creating the image, we prefere to use the finished image.

Load sources and create image:

git clone https://github.com/oracle/docker-images.git oracle-docker-images cd oracle-docker-images/OracleDatabase/SingleInstance/dockerfiles/ ./buildContainerImage.sh -v 21.3.0 -x

(takes about an hour)
Download image directly:

docker pull container-registry.oracle.com/database/express:21.3.0-xe (takes about an hour)

The right mirror can speed up the download significantly:

docker pull container-registry-frankfurt.oracle.com/database/express:21.3.0-xe

(takes about half an hour)

The result is:

docker image ls

REPOSITORY	TAG	IMAGE ID	CREATED	SIZE
oracle/database	21.3.0-xe	6b0fc451335f	3 minutes ago	6.54GB	(Build)
container-registry-frankfurt.oracle.com/database/express	21.3.0-xe	8da8cedb7fbf	3 months ago	11.4GB	(Download)

create a docker volume and a dump directory

docker volume create oradatavol mkdir %DEVON_IDE_HOME%\oracle_dump (mkdir ${DEVON_IDE_HOME}/oracle_dump)

Start the container with a volume for the database and dump path for exports

CMD:

docker run --name OracleDatabaseXE -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=let-me-in -e ORACLE_CHARACTERSET=AL32UTF8 -d --mount type=volume,source=oradatavol,target=/opt/oracle/oradata/XE --mount type=bind,source=%DEVON_IDE_HOME%\oracle_dump,target=/opt/oracle/admin/XE/oracle_dump container-registry-frankfurt.oracle.com/database/express:21.3.0-xe

--> commandline: docker run --name OracleDatabaseXE -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=let-me-in -e ORACLE_CHARACTERSET=AL32UTF8 -d --mount type=volume,source=oradatavol,target=/opt/oracle/oradata/XE --mount type=bind,source=%DEVON_IDE_HOME%\oracle_dump,target=/opt/oracle/admin/XE/oracle_dump container-registry-frankfurt.oracle.com/database/express:21.3.0-xe

GitBash

MSYS_NO_PATHCONV=1 cmd /c

docker run --name OracleDatabaseXE -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=let-me-in -e ORACLE_CHARACTERSET=AL32UTF8 -d --mount type=volume,source=oradatavol,target=/opt/oracle/oradata/XE --mount type=bind,source=$(cygpath -w ${DEVON_IDE_HOME}/oracle_dump),target=/opt/oracle/admin/XE/oracle_dump container-registry-frankfurt.oracle.com/database/express:21.3.0-xe

--> commandline: MSYS_NO_PATHCONV=1 cmd /c docker run --name OracleDatabaseXE -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=let-me-in -e ORACLE_CHARACTERSET=AL32UTF8 -d --mount type=volume,source=oradatavol,target=/opt/oracle/oradata/XE --mount type=bind,source=$(cygpath -w ${DEVON_IDE_HOME}/oracle_dump),target=/opt/oracle/admin/XE/oracle_dump container-registry-frankfurt.oracle.com/database/express:21.3.0-xe

Attention: If several containers with Oracle databases are to be created in parallel, the ports must be set accordingly. docker run ... -p 1522:1521 -p 5501:5500 docker run ... -p 1523:1521 -p 5502:5500 ...

Starting the container

docker start OracleDatabaseXE

database configuration and create data_pump_dir

sqlplus "sys/let-me-in@0.0.0.0:1521 as sysdba" create pfile='/opt/oracle/dbs/initXE_backup.ora' from spfile; alter system set sga_target=1g scope=spfile; alter system set sga_max_size=1g scope=spfile; alter session set container=XEPDB1; create or replace directory data_pump_dir_pdb as '/opt/oracle/admin/XE/oracle_dump'; exit

docker stop OracleDatabaseXE docker start OracleDatabaseXE

remind to wait long enough, because the database starting after the container is started

create a user/schema

sqlplus "system/let-me-in@0.0.0.0:1521/XEPDB1" create user mytestuser identified by mytestuser default tablespace USERS container=CURRENT; grant dba to mytestuser; grant read on directory data_pump_dir_pdb to mytestuser; grant write on directory data_pump_dir_pdb to mytestuser; exit

testing export of schema

sqlplus "mytestuser/mytestuser@0.0.0.0:1521/XEPDB1" create table test1 (a number primary key, b char(100)); create table test2 (a number primary key, b char(100)); begin for i in 1 .. 100 loop insert into test1 values (i, 'test' || i); insert into test2 values (i, 'test' || i); end loop; end; / commit; exit

expdp mytestuser/mytestuser@0.0.0.0:1521/XEPDB1 directory=DATA_PUMP_DIR_PDB dumpfile=mytestuser.dmp logfile=exp_mytestuser.log schemas=mytestuser job_name=myexpjob

Attention! at the moment there are problems with creating the export on GitBash. The mountpoint is not available, but the command docker inspect says all is alright

sqlplus "mytestuser/mytestuser@0.0.0.0:1521/XEPDB1" drop table test1; drop table test2; exit

impdp mytestuser/mytestuser@0.0.0.0:1521/XEPDB1 directory=DATA_PUMP_DIR_PDB dumpfile=mytestuser.dmp logfile=imp_mytestuser.log schemas=mytestuser job_name=myexpjob

sqlplus "mytestuser/mytestuser@0.0.0.0:1521/XEPDB1" select from test1; select from test2; exit

Conclusion

A Container with an Oracle database instance is now created from the image with the following configuration:

the database files are in a local volume so that the data is retained when the container is shut down.
a local directory is mounted in the container for exports with Data Pump.
Requirements for the database (parameters and others configurations) can be passed with a configuration file.
The parts "create data pump dir", "create a user/schema" and "database configuration" has to be part of a customer script.

@CREITZ25 thanks for this detailed and comprehensive instructions. 👍

--mount type=volume,source=oradatavol,target=/opt/oracle/oradata/XE --mount type=bind,source=%DEVON_IDE_HOME%\oracle_dump,target=/opt/oracle/admin/XE/oracle_dump

This is really a smart solution. Thanks for this trick. I was not aware of this feature in docker with the different mount types and using volume for the data that the end-user should not normally interfere with seems to fully make sense (and may also be faster/more efficient than a bind mount).

I am thinking in the following direction:

we could create a simple way to extend IDEasy with plugins (or maybe even using the existing custom tool feature)
if somebody could simply write some custom logic via bash scripts and bundle this somehow together (e.g. as a zip/tgz package) it could extend IDEasy with such features like OracleXE support via docker but with ability to export backup dumps, import existing dumps (with autocompletion? :) ), etc.
assuming we have an open "marketplace" for such plugins but still some concept of trust (e.g. by signing and users/projects trusting public keys), we could allow to extend IDEasy more dynamic for the individual needs of the projects
if we do not just use the existing custom tool feature but more some kind of plugin concept on top we could integrate such plugins as commandlets so e.g. with ide custom «keyword» «arg» users could simply get and use the plugin for «keyword» and invoke with with an according argument «arg». So e.g. ide custom oraclexe install he could setup an Oracle XE in a single call and with ide custom oraclexe export you could export a dump by default with the USER configured by the project via some variables and with the filename «USER»-YYYY-MM-DD_HH_mm_ss.dump etc. or via ide custom oraclexe export mydumpfile.dump with an explicit dumpfilename.
However, oraclexe would not be a commandlet implemented in Java and shipped via IDEasy but some plugin implemented by some vendor (could also be us) and available in some repo or marketplace.
Someone else could fork oraclexe and create oraclexe-fast-slim-advanced with his personal improvements, etc.

I still have to rethink this concept. On the one hand I am happy that we just left this bash hacking hell and came to a solid Java based development for our core features based on advanced QA processes that we failed to established with bash in devonfw-ide. On the other hand for dynamic needs of the projects it may still be beneficial to have the ability to extend our product with some scripting approach. If some need or demand evolves as custom plugin outside of the IDEeasy product it can then be integrated and rewritten in Java. But not every project need has to be build into the product IDEasy. So this could be some kind of nice balance.

NOTE: If we decide to go for such custom plugin mechanism, we will create a new story for it and do some PoC first. This story could then later be build on top of that feature...

devonfw / IDEasy