This blog could be consider the “on-premises” version of this previous blog “No Downtime Migration from MongoDB to Autonomous JSON Database” written by Deniz Sendil, a Senior Principle Product Manager in Oracle GoldenGate.
The use case is to migrate MongoDB documents to Oracle JSON Database on-premises.
Before we jump in, make sure you have the following:
This blog offers a detailed step by step to configure:
The main difficulty here is that to use the Oracle Database API for MongoDB.
To setup this feature, ORDS must be configured with SSL. We provide a step-by-step guide to configuring ORDS with SSL using a self-signed certificate.
The final Oracle Database API for MongoDB URI connection, allow the GoldenGate DAA configured as a MongoDB TARGET, deliver the documents in Oracle JSON Database.
You should follow this documentation
EXTRACT EMONGO
EXTTRAIL aa
SOURCEDB USERIDALIAS mongo DOMAIN OracleGoldenGate
JVMOPTIONS CLASSPATH /u01/app/product/goldengate/bigdata/ggjava/ggjava.jar:/u01/app/product/goldengate/bigdata/opt/DependencyDownloader/dependencies/mongodb_capture_4.11.0/*
NO_ABEND_ON_DDL
TRANLOGOPTIONS FETCHPARTIALJSON
TABLE "demodata"."testData";
mongodb://myUserAdmin:Welcome123@localhost:27017/admin?replicaSet=rs0
ORDS configure with SSL to use the Oracle Database API. To secure ORDS, we should configure ORDS with SSL a Self-Signed Certificate
Before you start, ensure administrative access to the server where ORDS is installed, and that the OpenSSL utility is installed to create the certificates.
Create and Navigate to a New Directory:
Make sure the directory does not already exist before creating it.
mkdir -p ~/selfsigned-certs-ok
cd ~/selfsigned-certs-ok
Generate the CA's Private Key:
Remember the password used here, as it will be needed to sign other certificates.
openssl genrsa -aes256 -out ca-key.pem 4096
Create the CA's Public Certificate:
This self-signed certificate acts as your Certificate Authority.
openssl req -new -x509 -sha256 -days 365 -key ca-key.pem -out ca.pem
View the CA Certificate’s Details:
openssl x509 -in ca.pem -text
Generate the Client's Private Key:
openssl genrsa -out client-key.pem 4096
Create a Certificate Signing Request (CSR):
Ensure the Common Name (CN) matches the hostname where the certificate will be used.
In my environment:
Hosts file:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
10.0.0.110 oraclelinux8.livelabs.oraclevcn.com oraclelinux8
openssl req -new -sha256 -subj "/CN= oraclelinux8" -key client-key.pem -out client.csr
Ensure IP addresses and DNS names in extfile.conf match your current needs. Check for consistency in IP addresses.
Hosts file:
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
10.0.0.110 oraclelinux8.livelabs.oraclevcn.com oraclelinux8
echo "subjectAltName=DNS:*.oraclevcn.com,DNS:oraclelinux8.livelabs.oraclevcn.com,IP:10.0.0.110" >> extfile.cnf
openssl x509 -req -sha256 -days 365 -in client.csr -CA ca.pem -CAkey ca-key.pem -out client.pem -extfile extfile.cnf -CAcreateserial
Verify the Client Certificate:
openssl verify -CAfile ca.pem -verbose client.pem
Convert the Certificate and Key to DER Format:
cat client.pem > fullchain.pem
cat ca.pem >> fullchain.pem
Convert the Certificate and Key to DER Format:
Some systems require certificates in DER format instead of PEM.
openssl pkcs8 -topk8 -inform PEM -outform DER -in client-key.pem -out client-key.der -nocrypt
openssl x509 -inform PEM -outform DER -in client.pem -out client.der
Check all *.der files generated
ls *.der
client.der client-key.der
There are a lot of steps by step blogs in Oracle.com:
https://blogs.oracle.com/database/post/installing-database-api-for-mongodb-for-any-oracle-database
Download ORDS software:
https://www.oracle.com/database/technologies/appdev/rest-data-services-downloads.html
Ensure the file paths are correct and match your ORDS configuration directory.
export JAVA_HOME=/usr/java/latest
export ORDS_HOME=/home/oracle/ords
export ORDS_CONFIG=/home/oracle/ordsconfigonprem
export PATH=${ORDS_HOME}/bin:${PATH}
export _JAVA_OPTIONS="-Xms1126M -Xmx1126M"
ords install
User: SYS AS SYSDBA
HTTPS
PORT 8443
Configure ORDS using the client Certificate and key in “der” format:
ords config set standalone.https.cert /home/oracle/selfsigned-certs-ok/client.der
ords config set standalone.https.cert.key /home/oracle/selfsigned-certs-ok/client-key.der
Enable Oracle Database Mongo API
ords config set mongo.enabled true
ords config set mongo.port 27040
Start ORDS
ords --config /home/oracle/ordsconfig serve
(From log in the ords serve start)
#Check URI
2024-05-15T23:50:20.786Z INFO The Oracle API for MongoDB connection string is:
mongodb://[{user}:{password}@]localhost:27040/{user}?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true
Test the SSL Connection:
openssl s_client -showcerts -connect 10.0.0.110:27040
sqlplus sys/xxxxxx@localhost:1521/orcl as sysdba
create user appuser identified by "Welcome#123";
grant soda_app, create session, create table, create view, create sequence, create procedure, create job, unlimited tablespace to appuser;
connect appuser/Welcome#123
exec ords.enable_schema;
mongosh --tlsAllowInvalidCertificates 'mongodb://appuser:Welcome#123@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=$external&tls=true&retryWrites=false&loadBalanced=true'
OUTPUT:
[orcl:oracle@oraclelinux8:~]$ mongosh --tlsAllowInvalidCertificates 'mongodb://appuser:Welcome#123@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=$external&tls=true&retryWrites=false&loadBalanced=true'
Current Mongosh Log ID: 66454cdb8593e216742202d7
Connecting to: mongodb://<credentials>@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=%24external&tls=true&retryWrites=false&loadBalanced=true&serverSelectionTimeoutMS=2000&tlsAllowInvalidCertificates=true&appName=mongosh+2.2.5
Using MongoDB: 4.2.14
Using Mongosh: 2.2.5
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
Warning: Found ~/.mongorc.js, but not ~/.mongoshrc.js. ~/.mongorc.js will not be loaded.
You may want to copy or rename ~/.mongorc.js to ~/.mongoshrc.js.
appuser>
mongosh --tls --tlsCAFile /home/oracle/selfsigned-certs-ok/ca.pem 'mongodb://appuser:Welcome#123@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
OUTPUT:
[orcl:oracle@oraclelinux8:~]$ mongosh --tls --tlsCAFile /home/oracle/selfsigned-certs-ok/ca.pem 'mongodb://appuser:Welcome#123@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true'
Current Mongosh Log ID: 66454faf68dedd56752202d7
Connecting to: mongodb://<credentials>@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=%24external&ssl=true&retryWrites=false&loadBalanced=true&tls=true&tlsCAFile=%2Fhome%2Foracle%2Fselfsigned-certs-ok%2Fca.pem&appName=mongosh+2.2.5
Using MongoDB: 4.2.14
Using Mongosh: 2.2.5
For mongosh info see: https://docs.mongodb.com/mongodb-shell/
Warning: Found ~/.mongorc.js, but not ~/.mongoshrc.js. ~/.mongorc.js will not be loaded.
You may want to copy or rename ~/.mongorc.js to ~/.mongoshrc.js.
appuser>
cd /u01/app/product/goldengate/bigdata
./keytool -import -trustcacerts -keystore /u01/app/product/goldengate/bigdata/jdk/jre/lib/security/cacerts -storepass changeit -alias MiCA -file /home/oracle/selfsigned-certs-ok/ca.pem -noprompt
[orcl:oracle@oraclelinux8:/u01/app/product/goldengate/bigdata/jdk/bin]$ ./keytool -import -trustcacerts -keystore /u01/app/product/goldengate/bigdata/jdk/jre/lib/security/cacerts -storepass changeit -alias MiCA -file /home/oracle/selfsigned-certs-ok/ca.pem -noprompt
Picked up _JAVA_OPTIONS: -Xms1126M -Xmx1126M
Certificate was added to keystore
Follow this documentation.
For this blog, we will use these parameter files in OGG:
REPLICAT MONGOAPI
MAP *.*, TARGET appuser.*;
# Properties file for Replicat MONGOAPI
# MongoDB Handler Template
gg.handlerlist=mongodb
gg.handler.mongodb.type=mongodb
gg.handler.mongodb.clientURI=mongodb://appuser:Welcome#123@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true&tlsCAFile=/home/oracle/selfsigned-certs-ok/ca.pem&tlsCertificateKeyFile=/home/oracle/selfsigned-certs-ok/fullchain.pem
gg.classpath=/u01/app/product/goldengate/bigdata/opt/DependencyDownloader/dependencies/mongodb_4.11.0/*
jvm.bootoptions=-Xmx512m -Xms32m
gg.log=log4j
gg.log.level=info
This is the clientURI you should use to connect to the Oracle JSON Database in OGG DAA 23ai configuration for MongoDB Replicat using Oracle Database MongoDB API:
gg.handler.mongodb.clientURI=mongodb://appuser:Welcome#123@10.0.0.110:27040/appuser?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true&tlsCAFile=/home/oracle/selfsigned-certs-ok/ca.pem&tlsCertificateKeyFile=/home/oracle/selfsigned-certs-ok/fullchain.pem
That’s it! You’ve now set up Oracle GoldenGate 23ai to replicate data from MongoDB to OracleDB, complete with SSL security. This process ensures that your data is transferred safely and efficiently. If you ran into any issues or have questions, feel free to reach out, and I’ll be happy to help.
I have more than 20 years of experience in Data Management, real-time data replication, bulk data movement and transformation, data quality, and metadata management in Cloud and On-prem environments.
Passionate GoldenGate evangelist.
Next Post