dbi Blog

In today’s data-driven world, organizations often leverage a variety of databases to meet their diverse data storage and processing needs. MongoDB and Oracle Database are two prominent names in the database landscape, each excelling in different areas. MongoDB is renowned for its flexibility and scalability in handling unstructured data, while Oracle Database is a powerhouse for structured data with robust transactional capabilities.

Integrating MongoDB documents within an Oracle database allows organizations to harness the strengths of both systems. This hybrid approach enables seamless data management, providing the flexibility of NoSQL with the reliability and performance of a traditional RDBMS. In this blog, we will explore eth technical steps involved in integrating MongoDB documents into an Oracle database.

Why Integrate MongoDB Documents in Oracle Database ?

In short, is for getting the best of two worlds.

1. Unified Data View: Combining MongoDB’s unstructured data with Oracle’s structured data creates a unified view, making it easier to perform comprehensive data analysis and reporting.

2. Enhanced Flexibility: MongoDB’s schema-less architecture allows for rapid development and iteration, while Oracle’s structured schema ensures data integrity and consistency.

3. Scalability and Performance: MongoDB’s horizontal scalability complements Oracle’s vertical scaling capabilities, providing a balanced approach to handling large volumes of data.

4. Cost Efficiency: By leveraging both databases, organizations can optimize costs by storing data where it is most efficient based on its nature and usage patterns.

Technical steps to connect the two worlds.

The VM preparation

I made some changes to the configuration to make the database changes persistent.

For this example I use an Oracle 23 AI docker container. How to use it is explained here: https://www.oracle.com/my/database/free/get-started/

The VM used is an Oracle Linux Server VM deployed on OCI:

[opc@db23ai ~]$ cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="9.4"

The docker part installation is described here: https://docs.docker.com/engine/install/rhel/

[root@db23ai ~]# yum install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin

Next,I create the oracle user and groups with the same ID’s as used in the container:

[root@db23ai ~]# groupadd -g 54321 oinstall
[root@db23ai ~]# groupadd -g 54322 dba
[root@db23ai ~]# groupadd -g 54323 oper
[root@db23ai ~]# useradd -g oinstall -G dba,oper,docker --uid 54321 -m oracle

Finally I install the sqlplus client and mongo shell:

[root@db23ai u02]# dnf install oracle-instantclient-sqlplus.x86_64
[root@db23ai u02]# wget https://downloads.mongodb.com/compass/mongodb-mongosh-2.2.12.x86_64.rpm
[root@db23ai u02]# rpm -ivh mongodb-mongosh-2.2.12.x86_64.rpm
warning: mongodb-mongosh-2.2.12.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID d361cb16: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:mongodb-mongosh-2.2.12-1.el8     ################################# [100%]
...

The Oracle 23 AI container

The port 1700 is for database sqlplus connections and the port 27017 is for mongosh connections. I exported some volumes first to make the Oracle database persistent, and to have a place to install ORDS (https://www.oracle.com/ch-de/database/technologies/appdev/rest.html)

Get the container:

[root@db23ai ~]# sudo su - oracle
[oracle@db23ai ~]$ docker pull container-registry.oracle.com/database/free:latest

Run the container:

[oracle@db23ai ~]$ mkdir /u02/data/DB
[oracle@db23ai ~]$ mkdir /u02/reco/DB
[oracle@db23ai ~]$ mkdir /u02/ords

[oracle@db23ai ~]$ docker run  -it --name 23ai -p 1700:1521 -p 27017:27017 \
-v /u02/data/DB:/opt/oracle/oradata \
-v /u02/reco/DB:/opt/oracle/reco \
-v /u02/oracle/ords:/opt/oracle/ords \
-e ENABLE_ARCHIVELOG=true  \
-e ORACLE_PWD="Hello-World-123" \
container-registry.oracle.com/database/free
...

# Once the traces shows that database is running we can start it as daemon (-d option)

# Test the connection to the PDB 
[oracle@db23ai ~]$ sqlplus system/"Hello-World-123"@localhost:1700/freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Thu Jul 11 12:09:40 2024
Version 23.4.0.24.05

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 11 2024 11:59:21 +00:00

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL> show con_id

CON_ID
------------------------------
3

Create the scott schema:

SQL> drop user scott cascade;

SQL> create user SCOTT identified by tiger default tablespace users;

SQL> grant connect, create session, create table, create view, create sequence, create procedure, create job, to scott; 

SQL> alter user scott quota unlimited on users;

Install ORDS to give access to mongo shell to the database

Download ORDS from https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/

Download it to /u02/oracle/ords/ords_pkg wich is the /u02/oracle/ords directory in the docker container.

Connect as oracle user to the container and install ORDS:

# connect to the container
[oracle@db23ai ~]$ docker exec -u 54321 -it 23ai /bin/bash

bash-4.4$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)

bash-4.4$ cd /opt/oracle/ords/ords_pkg
bash-4.4$ ls
ords-24.2.2.187.1943.zip

bash-4.4$ unzip ords-24.2.2.187.1943.zip
...

bash-4.4$ export JAVA_HOME=/opt/oracle/product/23ai/dbhomeFree/jdk
bash-4.4$ export PATH=$PATH:/opt/oracle/product/23ai/dbhomeFree/jdk/bin

bash-4.4$ /opt/oracle/ords/ords_pkg/bin/ords --config /opt/oracle/ords install

ORDS: Release 24.2 Production on Thu Jul 11 12:20:40 2024

Copyright (c) 2010, 2024, Oracle.

Configuration:
  /opt/oracle/ords

The configuration folder /opt/oracle/ords does not contain any configuration files.

Oracle REST Data Services - Interactive Install

  Enter a number to select the TNS net service name to use from /opt/oracle/product/23ai/dbhomeFree/network/admin/tnsnames.ora or specify the database connection
    [1] EXTPROC_CONNECTION_DATA SID=PLSExtProc
    [2] FREE         SERVICE_NAME=FREE
    [3] FREEPDB1     SERVICE_NAME=FREEPDB1
    [S] Specify the database connection
  Choose [1]: 3 <<<<<<<<<<<<< MY ENTRY <<<<<<<<<<<<<<<<
  Provide database user name with administrator privileges.
    Enter the administrator username: sys. <<<<<<<<<<<<< MY ENTRY <<<<<<<<<<<<<<<<
  Enter the database password for SYS AS SYSDBA:  Hello-World-123 <<<<<<<<<<<<< MY ENTRY <<<<<<<<<<<<<<<<

Retrieving information.
ORDS is not installed in the database. ORDS installation is required.

  Enter a number to update the value or select option A to Accept and Continue
    [1] Connection Type: TNS
    [2] TNS Connection: TNS_NAME=FREEPDB1 TNS_FOLDER=/opt/oracle/product/23ai/dbhomeFree/network/admin
           Administrator User: SYS AS SYSDBA
    [3] Database password for ORDS runtime user (ORDS_PUBLIC_USER): <generate>
    [4] ORDS runtime user and schema tablespaces:  Default: SYSAUX Temporary TEMP
    [5] Additional Feature: Database Actions
    [6] Configure and start ORDS in Standalone Mode: Yes
    [7]    Protocol: HTTP
    [8]       HTTP Port: 8080
    [A] Accept and Continue - Create configuration and Install ORDS in the database
    [Q] Quit - Do not proceed. No changes
  Choose [A]:   <<<<<<<<<<<<< MY ENTRY <<<<<<<<<<<<<<<<
  .....
2024-07-11T12:23:05.339Z INFO        Oracle REST Data Services initialized
Oracle REST Data Services version : 24.2.2.r1871943
Oracle REST Data Services server info: jetty/10.0.21
Oracle REST Data Services java info: Java HotSpot(TM) 64-Bit Server VM 11.0.23+7-LTS-222

<<<< ORDS is running in foreground. Press ^C to stop it once installed 
^C


bash-4.4$

Once installed stop the ords server to configure it for mongodb:

# in the container as oracl user
bash-4.4$ /opt/oracle/ords/ords_pkg/bin/ords --config /opt/oracle/ords config set mongo.enabled true

ORDS: Release 24.2 Production on Thu Jul 11 12:25:09 2024

Copyright (c) 2010, 2024, Oracle.

Configuration:
  /opt/oracle/ords

The global setting named: mongo.enabled was set to: true

# start the ords server 
bash-4.4$ /opt/oracle/ords/ords_pkg/bin/ords --config /opt/oracle/ords serve

ORDS: Release 24.2 Production on Thu Jul 11 12:25:51 2024

Copyright (c) 2010, 2024, Oracle.

Configuration:
  /opt/oracle/ords

2024-07-11T12:25:53.083Z INFO        HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
....
2024-07-11T12:25:53.170Z INFO        The Oracle API for MongoDB connection string is:
         mongodb://[{user}:{password}@]localhost:27017/{user}?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true
2024-07-11T12:25:58.362Z INFO        Configuration properties for: |default|lo|
awt.toolkit=sun.awt.X11.XToolkit
db.tnsAliasName=FREEPDB1
...

If the configuration is correct the mongo shell URI is printed: 2024-07-11T12:25:53.170Z INFO The Oracle API for MongoDB connection string is: mongodb://[{user}:{password}@]localhost:27017/{user}?

Connect as scott to the PDB and enable the schema for ORDS usage:

oracle@db23ai ~]$ sqlplus scott/tiger@localhost:1700/freepdb1

-- Enable SODA_APP role to work with JSON collections.
SQL> grant soda_app to scott;

SQL> exec ORDS.ENABLE_SCHEMA;

PL/SQL procedure successfully completed.

Now connect to Oracle database using mongosh:

[oracle@db23ai ~]$ export URI='mongodb://scott:tiger@localhost:27017/freepdb1?authMechanism=PLAIN&authSource=$external&tls=true&retryWrites=false&loadBalanced=true'

[oracle@db23ai ~]$ mongosh  --tlsAllowInvalidCertificates $URI
Current Mongosh Log ID:	668fd0b81156f2c04b482f8a
Connecting to:		mongodb://<credentials>@localhost:27017/freepdb1?authMechanism=PLAIN&authSource=%24external&tls=true&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&tlsAllowInvalidCertificates=true&appName=mongosh+2.2.12
Using MongoDB:		4.2.14
Using Mongosh:		2.2.12

For mongosh info see: https://docs.mongodb.com/mongodb-shell/


To help improve our products, anonymous usage data is collected and sent to MongoDB periodically (https://www.mongodb.com/legal/privacy-policy).
You can opt-out by running the disableTelemetry() command.

freepdb1>

Let’s create a collection:

freepdb1> use scott

scott> db.createCollection('emp_mdb');

scott> db.emp_mdb.insertOne({"name":"Miller","job": "Programmer","salary": 70000});
{
  acknowledged: true,
  insertedId: ObjectId('668fd349ec28de3a61482f8d')
}

scott> db.emp_mdb.find({"name":"Miller"});
[
  {
    _id: ObjectId('668fd349ec28de3a61482f8d'),
    name: 'Miller',
    job: 'Programmer',
    salary: 70000
  }
]

# remark the table name in lowercase 
scott> show collections
emp_mdb

Let’s check the collection table from Oracle sqlplus point of view:

# connect to the PDB 
[oracle@db23ai ~]$ sqlplus scott/tiger@localhost:1700/freepdb1

SQL> 
-- describe the MongoDB collection stored as a table 
SQL> desc emp_mdb;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 DATA						    JSON

-- select the collection 
SQL> select * from emp_mdb;

DATA
--------------------------------------------------------------------------------
{"_id":"668fd349ec28de3a61482f8d","name":"Miller","job":"Programmer","salary":70


-- check the tables from scott schema (the collection is in lowercase ;) ) 
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
DEPT
EMP
BONUS
SALGRADE
emp_mdb


-- print JSON fields a human readable 
SQL> select json_serialize(dbms_json_schema.describe(object_name=>'emp_mdb', owner_name  => 'SCOTT') pretty) as json_schema;

JSON_SCHEMA
--------------------------------------------------------------------------------
{
  "title" : "emp_mdb",
  "dbObject" : "SCOTT.emp_mdb",
  "type" : "object",
  "dbObjectType" : "table",
  "properties" :
  {
    "DATA" :
    {
    }
  },
  "dbPrimaryKey" :
  [
    "RESID"
  ]
}

--  print them more pretty 
SQL> select json_serialize((e.data) pretty) from emp_mdb e;

JSON_SERIALIZE((E.DATA)PRETTY)
--------------------------------------------------------------------------------
{
  "_id" : "668fd349ec28de3a61482f8d",
  "name" : "Miller",
  "job" : "Programmer",
  "salary" : 70000
}

Conclusion

Integrating MongoDB documents into an Oracle database enables organizations to take advantage of the unique strengths of both databases. This hybrid approach supports flexible, scalable, and robust data management, catering to a wide range of business needs. By following the steps outlined in this blog, you can create a seamless integration that leverages the best of both worlds.

Embrace the hybrid model and unlock new potentials in your data architecture.

Post Views: 1,250

Integrating MongoDB Documents in Oracle Database: A Modern Approach to Hybrid Data Management

Why Integrate MongoDB Documents in Oracle Database ?

Technical steps to connect the two worlds.

The VM preparation

The Oracle 23 AI container

Install ORDS to give access to mongo shell to the database

Conclusion

Alexandre Nestor

Leave a Reply:

Related blog articles

Why Integrate MongoDB Documents in Oracle Database ?

Technical steps to connect the two worlds.

The VM preparation

The Oracle 23 AI container

Install ORDS to give access to mongo shell to the database

Conclusion

Alexandre Nestor

Leave a Reply:

Related blog articles

New Oracle Database Appliance X11 series for 2025

Oracle Database on Apple Silicon with Virtualbox 7.1

Convert Single Instance database to RAC on an ODA

DPR and Dbvisit Standby on ODA