MongoDB or AJD/ATP databases require every document (row) to have a column named _id whose value should be unique in a collection (table). This is similar to a primary key for RDBMS tables. If a document does not contain a top-level _id column during an insert, the MongoDB driver adds this column.

The MongoDB Handler builds custom _id field values for every document based on the primary key column values in the trail record. This custom _id is built using all the key column values concatenated by a : (colon) separator. For example:

KeyColValue1:KeyColValue2:KeyColValue3

The MongoDB Handler enforces uniqueness based on these custom _id values. This means that every record in the trail must be unique based on the primary key columns values. Existence of non-unique records for the same table results in a MongoDB Handler failure and in Replicat abending with a duplicate key error.

The behavior of the _id field is:

• By default, MongoDB creates a unique index on the column during the creation of a collection.

• It is always the first column in a document.

• It may contain values of any BSON data type except an array.

The MongoDB Handler supports delivery to the BSON data types as follows:

• 32-bit integer

• 64-bit integer

• Double

• Date

• String

• Binary data

The MongoDB Java Driver is required for Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) to connect and stream data to MongoDB. If the GG for DAA version is 21.7.0.0.0 and below, then you need to use 3.x (MongoDB Java Driver 3.12.8). If the GG for DAA version is 21.8.0.0.0 and above, then you need to use MongoDB Java Driver 4.6.0. The MongoDB Java Driver is not included in the GG for DAA product. You must download the driver from: mongo java driver.

Select mongo-java-driver and the version to download the recommended driver JAR file.

You must configure the gg.classpath variable to load the MongoDB Java Driver JAR at runtime. For example: gg.classpath=/home/mongodb/mongo-java-driver-3.12.8.jar.

GG for DAA supports the MongoDB Decimal 128 data type that was added in MongoDB 3.4. Use of a MongoDB Java Driver prior to 3.12.8 results in a ClassNotFound exception.

You configure the MongoDB Handler operation using the properties file. These properties are located in the Java Adapter properties file (not in the Replicat properties file).

To enable the selection of the MongoDB Handler, you must first configure the handler type by specifying gg.handler.name.type=mongodb and the other MongoDB properties as follows:

Bulk write is enabled by default. For better throughput, Oracle recommends that you use bulk write.

You can also enable bulk write by using the BulkWrite handler property. To enable or disable bulk write use the gg.handler.handler.BulkWrite=true | false. The MongoDB Handler does not use the gg.handler.handler.mode=op | tx property that is used by Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA).

With bulk write, the MongoDB Handler uses the GROUPTRANSOPS parameter to retrieve the batch size. The handler converts a batch of trail records to MongoDB documents, which are then written to the database in one request.

Write concern describes the level of acknowledgement that is requested from MongoDB for write operations to a standalone MongoDB, replica sets, and sharded-clusters. With sharded-clusters, Mongo instances pass the write concern on to the shards, see https://docs.mongodb.com/manual/reference/write-concern/.

Use the following configuration:

w: value
wtimeout: number

An Oracle GoldenGate trail may have data for sources that support three-part table names, such as Catalog.Schema.Table. MongoDB only supports two-part names, such as DBName.Collection. To support the mapping of source three-part names to MongoDB two-part names, the source Catalog and Schema is concatenated with an underscore delimiter to construct the Mongo DBName.

For example, Catalog.Schema.Table would become catalog1_schema1.table1.

The MongoDB Handler can recover from bulk write errors using a lightweight undo engine. This engine works differently from typical RDBMS undo engines, rather the best effort to assist you in error recovery. Error recovery works well when there are primary violations or any other bulk write error where the MongoDB database provides information about the point of failure through BulkWriteException.

Table 9-35Table 1 lists the requirements to make the best use of this functionality.

If there are errors during undo operations, it may be not possible to get the MongoDB collections to a consistent state. In this case, you must manually reconcile the data.

To configure SSL between the MongoDB instance and Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) MongoDB Handler, do the following:

openssl req -passout pass:password -new -x509 -days 3650 -extensions v3_ca -keyout 
ca_private.pem -out ca.pem -subj 
"/CN=CA/OU=GOLDENGATE/O=ORACLE/L=BANGALORE/ST=KA/C=IN"

Create key and certificate signing requests (CSR) for client and all server nodes

openssl req -newkey rsa:4096 -nodes -out client.csr -keyout client.key -subj
'/CN=certName/OU=OGGBDCLIENT/O=ORACLE/L=BANGALORE/ST=AP/C=IN'
openssl req -newkey rsa:4096 -nodes -out server.csr -keyout server.key -subj
'/CN=slc13auo.us.oracle.com/OU=GOLDENGATE/O=ORACLE/L=BANGALORE/ST=TN/C=IN'

Sign the certificate signing requests with CA

openssl x509 -passin pass:password -sha256 -req -days 365 -in client.csr -CA ca.pem -CAkey
ca_private.pem -CAcreateserial -out client-signed.crtopenssl x509 -passin pass:password -sha256 -req -days 365 -in server.csr -CA ca.pem -CAkey
ca_private.pem -CAcreateserial -out server-signed.crt -extensions v3_req -extfile
 <(cat << EOF[ v3_req ]subjectAltName = @alt_names 
[ alt_names ]
DNS.1 = 127.0.0.1
DNS.2 = localhost
DNS.3 = hostname 
EOF)

cat client-signed.crt client.key > client.pem
cat server-signed.crt server.key > server.pem

Create trust store and keystore

openssl pkcs12 -export -out server.pkcs12 -in server.pem
openssl pkcs12 -export -out client.pkcs12 -in client.pem

bash-4.2$ ls
ca.pem  ca_private.pem     client.csr  client.pem     server-signed.crt  server.key  server.pkcs12
ca.srl  client-signed.crt  client.key  client.pkcs12  server.csr         server.pem

Start instances of mongod with the following options:

--tlsMode requireTLS --tlsCertificateKeyFile ../opensslKeys/server.pem --tlsCAFile
        ../opensslKeys/ca.pem 

credentialstore connectionString

alter credentialstore add user  
        mongodb://myUserAdmin@localhost:27017/admin?ssl=true&tlsCertificateKeyFile=../mcopensslkeys/client.pem&tlsCertificateKeyFilePassword=password&tlsCAFile=../mcopensslkeys/ca.pem
        password root alias mongo

For CDC Extract, add the key store and trust store as part of the JVM options.

JVM options

-Xms512m -Xmx4024m -Xss32m -Djavax.net.ssl.trustStore=../mcopensslkeys /server.pkcs12
          -Djavax.net.ssl.trustStorePassword=password  
        -Djavax.net.ssl.keyStore =../mcopensslkeys/client.pkcs12
        -Djavax.net.ssl.keyStorePassword=password

Oracle Autonomous JSON Database (AJD) and Autonomous Database for transaction processing also uses wire protocol to connect. Wire protocol has the same MongoDB CRUD APIs.

Basic configuration remains the same including optional properties mentioned in this chapter.

The handler uses same protocol (mongodb wire protocol) and same driver jar for Autonomous databases as that of mongodb for performing all operation in target agnostic manner for performing the replication. The properties can also be used for any of the supported targets.

gg.handlerlist=mongodb
gg.handler.mongodb.type=mongodb
#URL mentioned below should be an AJD instance URL
gg.handler.mongodb.clientURI=mongodb://[username]:[password]@[url]?authSource=$external&authMechanism=PLAIN&ssl=true
#Path to MongoDB Java driver. Maven co-ordinates
# <dependency>
# <groupId>org.mongodb</groupId>
# <artifactId>mongo-java-driver</artifactId>
# <version>3.10.1</version>
# </dependency>
gg.classpath=/path/to/mongodb/java/driver/mongo-java-driver-3.10.1.jar
javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm

To migrate from MongoDB to AJD, first it is required to run initial load. Initial load comprises inserts operations only. After running initial load, start CDC which keeps the source and target database synchronized.

1. Start CDC extract and generate trails. Do not start replicat to consume these trail files.
2. Start Initial load extract and wait for initial load to complete.
3. Create a new replicat to consume the initial load trails generated in Step 2. Wait for completion and then stop replicat.
4. Create a new replicate to consume the CDC trails. Configure this replicat to use HANDLECOLLISIONS and then start replicat.
5. Wait for the CDC replicat (Step 4) to consume all the trails, check replicat lag, and replicat RBA to ensure that the CDC replicat has caught up. At this point, the source and target databases should be in sync.
6. Stop the CDC replicat, remove HANDLECOLLISIONS parameter, and then restart the CDC replicat.

1. Before running CDC, ensure to run initial load, which loads the initial data using insert operations.
2. Use bulk mode for running mongoDB handler in order to achieve better throughput.
3. Enable handle-collision while migration to allow replicat to handle any collision error automatically.
4. In order to insert missing update, ensure to add the INSERTMISSINGUPDATES property in the.prm file.

1. Go to the official Oracle download page: Oracle Database Free 23c (https://www.oracle.com/in/database/free/get-started/).
2. Download the RPM package oracle-database-free-23c-1.0-1.el8.x86_64.rpm for Oracle Linux 8 (OL8) machines.
3. Install the RPM Package:
   1. Run the following command to install Oracle Database Free sudo dnf install -y oracle-database-free*
      Expected Output

      Package: oracle-database-free-23ai
      Version: 1.0-1
      Architecture: x86_64
      Repository: @commandline
      Size: 1.3 GB

4. Configure Oracle Database: After installation, configure the database with: sudo /etc/init.d/oracle-free-23ai configure. You will be prompted to enter passwords for SYS, SYSTEM, and PDBADMIN users. Follow the on-screen instructions to complete configuration.

1. Set Environment Variable: export ORACLE_HOME=/opt/oracle/product/23ai/dbhomeFree/.
2. Verify Installation: Connect to the Oracle Database to ensure installation is successful: /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus sys/Admin01@localhost:1521/FREE as sysdba.

1. Log in to SQL*Plus as SYSDBA: /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus sys/Admin01@localhost:1521/FREE as sysdba.
2. Check Current Container and PDBs:

   show con_name;
   show pdbs;
   
   Expected Output:
   
   CON_NAME
   ------------------------------
   CDB$ROOT
   
   CON_ID   CON_NAME       OPEN MODE  RESTRICTED
   ------   ------------   ---------- ----------
   2        PDB$SEED       READ ONLY  NO
   3        FREEPDB1       READ WRITE NO

3. Create a New PDB for ORDS:
   CREATE PLUGGABLE DATABASE pdbords ADMIN USER pdbordsadmin IDENTIFIED BY Admin01 FILE_NAME_CONVERT = ('/opt/oracle/oradata/FREE/pdbseed/', '/scratch/oracle/pdbseed_ords/');

   Note:

   Precreate directory /scratch/oracle/pdbseed_ords/
4. Open the New PDB and verify: ALTER PLUGGABLE DATABASE pdbords OPEN;SHOW pdbs;
   Expected Output:

   CON_ID   CON_NAME       OPEN MODE  RESTRICTED
   ------   ------------   ---------- ----------
   2        PDB$SEED       READ ONLY  NO
   3        FREEPDB1       READ WRITE NO
   5        PDBORDS        READ WRITE NO

5. Set Container to PDBORDS:

   ALTER SESSION SET CONTAINER = pdbords;
   SHOW con_name;

1. Create ORDS User:

   CREATE USER ordsuser IDENTIFIED BY Admin01;
   GRANT CONNECT, RESOURCE, DBA TO ordsuser;

   This user will be used for MongoDB integration through ORDS.

2. Connect as the New User:

   /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus ordsuser/Admin01@localhost:1521/PDBORDS

1. Create a directory for certificates:

   mkdir certs
   cd certs/

2. Generate the CA's Private Key. The following command will generate a private key for your Certificate Authority (CA). Remember the password used here, as it will be needed to sign other certificates.

   openssl genrsa -aes256 -out ca-key.pem 4096

3. Create the CA's Public Certificate

   Generate a self-signed public certificate for your CA, which acts as the Certificate Authority.

   openssl req -new -x509 -sha256 -days 365 -key ca-key.pem -out ca.pem

4. View the CA Certificate’s Details. Verify the details of the CA certificate with the following command:

   openssl x509 -in ca.pem -text

5. Generate the Client's Private KeyCreate a private key for the client:

   openssl genrsa -out client-key.pem 4096

6. Create a Certificate Signing Request (CSR)Generate a CSR for the client certificate. Make sure the Common Name (CN) matches the hostname where the certificate will be used.

   openssl req -new -sha256 -subj "/CN=oraclelinux8" -key client-key.pem -out client.csr

7. Generate the Client Certificate Using the CA. Add the IP addresses and DNS names that match your current environment to extfile.cnf. Ensure they are consistent with the hostname details in your hosts file.
   Hosts File Example:

   127.0.0.1   localhost
   100.01.01.01 phoenix.dev3sub2phx.oraclevcn.com

   Create extfile.cnf:

   echo "subjectAltName=DNS:*.oraclevcn.com,DNS:phoenix.dev3sub2phx.oraclevcn.com,IP:100.01.01.01" >> extfile.cnf

8. Sign the Certificate:

   openssl x509 -req -sha256 -days 365 -in client.csr -CA ca.pem -CAkey ca-key.pem -out client.pem -extfile extfile.cnf -CAcreateserial

9. Verify the Client Certificate.

   Use the following command to verify the client certificate:

   openssl verify -CAfile ca.pem -verbose client.pem

10. Combine Certificates for Full Chain.

    Create a full chain certificate by combining the client certificate and the CA certificate:

    cat client.pem > fullchain.pem
    cat ca.pem >> fullchain.pem

11. Configure ORDS to Use Your SSL Certificate.

    Some systems require certificates in DER format instead of PEM.

    Convert the Client Key and Certificate to DER Format:

    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

1. Download ORDS.
   1. Download the file ords-24.3.0.262.0924.zip from Oracle's website: ORDS Download
   2. Unzip the downloaded file.

   mkdir certs
   cd certs/

2. Setup ORDS:
   1. Set Environment Variables.

      export ORDS_HOME=/scratch/ords/
      export ORDS_CONFIG=/scratch/ords/ordsconfig
      export PATH=${ORDS_HOME}/bin:${PATH}
      export _JAVA_OPTIONS="-Xms1126M -Xmx1126M"

      Note:

      Requires JAVA11 or above. Unset ORACLE_HOME if set any.
   2. Install ORDS. Run the following command:

      ords install

      • Choose the following options during installation:
        • Connection Type: Basic
        • Database: localhost:1521/PDBORDS
        • Protocol: HTTPS
        • Port: 8443
        • Certificate Type: Use Self-Signed Certificate with fully qualified hostname

      Expected Output:

      -bash-4.4$ ords install
      
      Enter a number to update the value or select option A to Accept and Continue
      
          [1] Connection Type: Basic
      
          [2] Basic Connection: HOST=localhost PORT=1521 SERVICE_NAME=PDBORDS
      
                 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: HTTPS
      
          [8]       HTTPS Port: 8443
      
          [9]    Certificate Type: Use Self-Signed Certificate
      
          [10]      SSL Hostname: phoenix.dev3sub2phx.oraclevcn.com
      
          [A] Accept and Continue - Create configuration and Install ORDS in the database
      
          [Q] Quit - Do not proceed. No changes
      
        Choose [A]: A

   3. Enable ORDS Schema. Connect to the database and grant necessary permissions:

      /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus ordsuser/Admin01@localhost:1521/PDBORDS
      SQL> grant soda_app, create session, create table, create view, create sequence, create procedure, create job, unlimited tablespace to ordsuser;
      SQL> exec ords.enable_schema;

   4. Configure Certificates and MongoDB.

      ords config set standalone.https.cert /scratch/ords/certs/client.der
      ords config set standalone.https.cert.key /scratch/ords/certs/client-key.der
      ords config set mongo.enabled true
      ords config set mongo.port 27040

3. Start ORDS
   1. Run the following command to start ORDS: ords serve

   Expected Output:

   -bash-4.4$ ords serve
   
   Picked up _JAVA_OPTIONS: -Xms1126M -Xmx1126M
   
   2024-11-04T10:53:10.119Z WARNING     Your configuration folder /scratch/ords/ordsconfig is located in ORDS product folder.  Oracle recommends to use a different configuration folder.
   
   Refer to Oracle REST Data Services Documentation on how to setup your configuration folder.
   
   ORDS: Release 24.3 Production on Mon Nov 04 10:53:10 2024
   
   Copyright (c) 2010, 2024, Oracle.
   
   Configuration:
   
     /scratch/ords/ordsconfig
   
   
   2024-11-04T10:53:10.642Z INFO        HTTP and HTTP/2 cleartext listening on host: 0.0.0.0 port: 8080
   
   2024-11-04T10:53:10.643Z INFO        HTTPS and HTTPS/2 listening on host: 0.0.0.0 port: 8443
   
   2024-11-04T10:53:10.679Z INFO        Disabling document root because the specified folder does not exist: /scratch/ords/ordsconfig/global/doc_root
   
   2024-11-04T10:53:10.680Z INFO        Default forwarding from / to contextRoot configured.
   
   2024-11-04T10:53:10.764Z SEVERE      ORAMLVERSION null
   
   2024-11-04T10:53:10.775Z INFO        Oracle API for MongoDB listening on port: 27040
   
   2024-11-04T10:53:10.775Z 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

1. Add generated CA Certificate to Java Keystore.
   1. Add generated CA Certificate to Java KeystoreTo enable secure communication with MongoDB, the CA certificate (ca.pem) must be added to the Java keystore. Run the following command:

   keytool -import -trustcacerts -keystore /scratch/oui_21.15/ogg_install_home/jdk/jre/lib/security/cacerts -storepass changeit -alias MiCA -file /scratch/ca.pem -noprompt

   The certificate should now be added to the keystore successfully.

   Note:

   Use cacerts from OGGDAA installation directory.
2. MongoDB Connection URI: To connect to MongoDB securely, use the following connection URI. Make sure that paths for tlsCAFile and tlsCertificateKeyFile are correct and match your environment.Set Environment Variables.

   mongodb://ordsuser:Admin01@phoenix.dev3sub2phx.oraclevcn.com:27040/ordsuser?
   authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true&tlsCAFile=/scratch/ca.pem

   Syntax:

   mongodb://[{user}:{password}@]localhost:27040/{user}?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true

3. Create Table Space ORDS
   1. Connect to SQL Plus:

      /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus ordsuser/Admin01@localhost:1521/PDBORDS

   2. Create Tablespace

      CREATE TABLESPACE jcttest
      DATAFILE '/scratch/ords/tablespace/jct_datafile.dbf'
      SIZE 100M
      AUTOEXTEND ON
      NEXT 10M
      MAXSIZE UNLIMITED
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO;

      Note:

      Precreate /scratch/ords/tablespace directory.
   3. Set Default Tablespace:

      ALTER USER ordsuser DEFAULT TABLESPACE JCTTEST;

4. Configure Replication
   1. To configure the MongoDB handler, create a prm file for the replicat as:

      REPLICAT JCT
      MAP *.*, TARGET ordsuser.*; // here target schema is the ords user that we created.

   2. Create a properties file for the Replicat JCT.

      gg.handlerlist=mongodb
      gg.handler.mongodb.type=mongodb
      #TODO: MongoDB Client URI (replace with the actual MongoDB URI)
      gg.handler.mongodb.clientURI=mongodb://ordsuser:Admin01@phoenix.dev3sub2phx.oraclevcn.com:27040/ordsuser?authMechanism=PLAIN&authSource=$external&ssl=true&retryWrites=false&loadBalanced=true&tlsCAFile=/scratch/ca.pem
      #TODO: Path to MongoDB Java driver
      gg.classpath=/scratch/oui_21.15/ogg_install_home/opt/DependencyDownloader/dependencies/mongodb_5.0.0/*

5. Verify Data in ORDS SQL Developer

   After setting up replication, you can verify the data in SQL Developer:

   Login URL: SQL Developer URL (https://phoenix.dev3sub2phx.oraclevcn.com:8443/ords/sql-developer)

   Credentials: ordsuser/Admin01

   SELECT DATA,  RESID, ETAG FROM ORDSUSER.JSONCT;

   The DATA column should hold the JCT records from MongoDB.

   Note:

   • If the JCT table is not present, the MongoDB handler will create it by default.
   • You may also create the target JCT table manually and map it in the handler configuration.

   Syntax to Create JSON Collection Table:

   CREATE JSON COLLECTION TABLE JSONCT;

1. If you come across the error: JSON type cannot be used in non-automatic segment space management tablespace 'SYSTEM', run the following commands to assign a default tablespace to ordsuser.

   Connect to SQL Plus:

   /opt/oracle/product/23ai/dbhomeFree/bin/sqlplus ordsuser/Admin01@localhost:1521/PDBORDS

   Set Default Tablespace:

   ALTER USER ordsuser DEFAULT TABLESPACE JCTTEST;

   This sets JCTTEST as the default tablespace for the ordsuser schema.

The required dependent client libraries are:

• bson-4.6.0.jar
• bson-record-codec-4.6.0.jar
• mongodb-driver-core-4.6.0.jar
• mongodb-driver-legacy-4.6.0.jar
• mongodb-driver-legacy-4.6.0.jar
• mongodb-driver-sync-4.6.0.jar

The Maven coordinates of these third-party libraries that are needed to run MongoDB replicat are:

<dependency>

    <groupId>org.mongodb</groupId>
    <artifactId>mongodb-driver-legacy</artifactId>
    <version>4.6.0</version>
    </dependency>

 <dependency>
    <groupId>org.mongodb</groupId>
    <artifactId>mongodb-driver-sync</artifactId>
    <version>4.6.0</version>
</dependency>

Example

Download the latest version from Maven central at: https://central.sonatype.com/artifact/org.mongodb/mongodb-driver-reactivestreams/4.6.0.

<!-- https://mvnrepository.com/artifact/org.mongodb/mongo-java-driver -->
<dependency>
    <groupId>org.mongodb</groupId>
    <artifactId>mongo-java-driver</artifactId>
    <version>3.12.8</version>
</dependency>