Connecting a Dockerized Oracle Database with WSO2 API Manager
WSO2 API Manager is a state of the art, fully open source platform to build, integrate and manage all your APIs in one place. Since the WSO2 API manager can be set up easily on-premise, a user can chose a main database for the APIM, from a wide range of databases including MYSQL, MSSQL, Oracle and PostgresSQL etc. That is one of the best features in APIM, that you could set it up using the production database system that you already use, without using an entirely new database system just for the APIM to run.
In this article I thought about explaining how to set up an Oracle 18 XE database, through docker, with the WSO2 API Manager.
For this I will be using the docker images published by Gerald Venzl which are already used by many organizations including Apache and Eclipse. You could find the docker hub link below.
After you visit the docker hub, you could see that there are multiple versions of Oracle Database images available. Full versions and also slimmed down versions. Here I will be using a slimmed down version of Oracle 18 Express (XE) database. To pull the image to your local docker distribution you could use the below command.
docker pull gvenzl/oracle-xe:18-slimAfter the image has been pulled successfully, you need to install SQL*Plus which is a terminal based database management tool which is used to interact with Oracle databases. This ask-ubuntu thread explains how to install it in Ubuntu step by step.
Then we have to create a new container using the image we pulled. Here we have to provide a command line argument “ORACLE_PASSWORD” which will be the default SYSTEM password of the created oracle container.
We can create a new oracle container using the below command.
docker run -d -p 1521:1521 -e ORACLE_PASSWORD=<your-password> gvenzl/oracle-xe:18-slimIf you observe the logs of the created container using,
docker container logs -f <container-id>you could see that the container creation process has been started.
After a while you will get the below log which indicates that the oracle database is ready to be used.
After this log, you could use the installed SQL*Plus client to connect to the database using the SYSTEM user. Since the oracle databases follow a multi-tenancy architecture from Oracle 12g onwards, there are two separate types of databases in Oracle 18XE, CDB and PDB. CDBs can be explained as a root container database to which we can plug several PDBs in. Here we would be connecting to the default PDB which comes with the 18 XE database, “XEPDB1".
sqlplus SYSTEM/password@localhost/XEPDB1Now you will encounter a SQL command line which can be used to interact with your database.
Here we will create two database schema for the two sets of tables which are used in the APIM manager.
- Shared Database Tables
- APIM Related Tables.
Since in oracle, creating a user is equivalent to creating a schema, we will be essentially creating two new users with permissions. You could execute the following script to create the users.
ALTER SESSION SET CONTAINER=XEPDB1;CREATE USER AMDB IDENTIFIED BY password QUOTA UNLIMITED ON USERS;GRANT CONNECT, RESOURCE TO AMDB;GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE TO AMDB;GRANT DBA TO AMDB;CREATE USER SHAREDDB IDENTIFIED BY password QUOTA UNLIMITED ON USERS;GRANT CONNECT, RESOURCE TO SHAREDDB;GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE PROCEDURE TO SHAREDDB;GRANT DBA TO SHAREDDB;
We are creating two users named AMDB and SHAREDDB and giving them relevant permissions in the above code snippet.
After creation is successful, you can connect to the created users using the below commands.
sqlplus AMDB/password@127.0.0.1:1521/XEPDB1sqlplus SHAREDDB/password@127.0.0.1:1521/XEPDB1
Now that the database setup is complete, you need to download a fresh pack of the WSO2 API Manager which can be done using this link.
After creating it extract it to a directory (We will call this <APIM-HOME> from now on) and then we need to create database objects in those two databases we created earlier.
The database scripts needed for this are available in ,
<APIM-HOME>/dbscriptsdirectory. We should use <APIM-HOME>/dbscripts/oracle.sql for the shared database and <APIM-HOME>/dbscripts/apimgt/oracle.sql for the apimgt database.
You could execute these scripts in the databases using SQL*Plus using the below commands. After logging in to the shared database, execute,
@<APIM-HOME>/dbscripts/oracle.sql
And after that, log into the AMDB and execute the other script as well.
After the databases are created and scripts executed successfully, we need to configure the downloaded APIM pack to be used with Oracle.
This is done via two steps.
- Downloading the oracle JDBC driver from here are copying it to <APIM-HOME>/repository/components/lib/ directory.
- Changing the <APIM-HOME>/repository/conf/deployment.toml file to incorporate the databases.
You could paste the following configuration to your deployment.toml and replace the existing H2 database configurations.
[database.shared_db]
type = "oracle"
url = "jdbc:oracle:thin:@localhost:1521/XEPDB1"
username = "SHAREDDB"
password = "password"
driver = "oracle.jdbc.driver.OracleDriver"
validationQuery = "SELECT 1 FROM DUAL"[database.apim_db]
type = "oracle"
url = "jdbc:oracle:thin:@localhost:1521/XEPDB1"
username = "AMDB"
password = "password"
driver = "oracle.jdbc.driver.OracleDriver"
validationQuery = "SELECT 1 FROM DUAL"
After the above two steps you are all good to use the APIM distribution with the newly created Oracle databases.
You can go to <APIM-HOME>/bin and execute
sh api-manager.shto start-off the APIM instance.
So those are steps we need to follow when we configure a fresh Oracle 18 Express database to work with the WSO2 API Manager 4.0.0 distribution. In the upcoming articles I will explain how to setup some other databases with the WSO2 API Manager as well.
Do post any questions below if you have. Thanks for reading.
