9.2.28 Oracle NoSQL

The Oracle NoSQL Handler can replicate transactional data from Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) to a target Oracle NoSQL Database.

This chapter describes how to use the Oracle NoSQL Handler.

• Overview
  
• On-Premise Connectivity
  
• OCI Cloud Connectivity
  
• Oracle NoSQL Types
  
• Oracle NoSQL Handler Configuration
  
• Performance Considerations
  
• Operation Processing Support
  
• Column Processing
  
• Table Check and Reconciliation Process
  
• Oracle NoSQL SDK Dependencies
  

9.2.28.1 Overview

Oracle NoSQL Database is a NoSQL-type distributed key-value database. It provides a powerful and flexible transaction model that greatly simplifies the process of developing a NoSQL-based application. It scales horizontally with high availability and transparent load balancing even when dynamically adding new capacity.

Starting from the Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) 23ai release, the Oracle NoSQL Handler has been changed to use the Oracle NoSQL Java SDK to communicate with Oracle NoSQL. The Oracle NoSQL Java SDK supports both on-premise and OCI cloud instances of Oracle NoSQL. Make sure to read the documentation because connecting to on-premise verses OCI cloud instances of Oracle NoSQL both require specialized configuration parameters and possibly some setup.

For more information about Oracle NoSQL Java SDK, see Oracle NoSQL SDK for Java.

9.2.28.2 On-Premise Connectivity

The Oracle NoSQL Java SDK requires that connectivity route through the Oracle NoSQL Database Proxy. The Oracle NoSQL Database Proxy is a separate process which enables the http/https interface of Oracle NoSQL. The Oracle NoSQL Java SDK uses the http/https interface. Oracle GoldenGate effectively communicates with the on-premise Oracle NoSQL instance through the Oracle NoSQL Database Proxy process.

For more information on the Oracle NoSQL Database Proxy including setup instructions, see Connecting to the Oracle NoSQL Database On-premise.

Connectivity to the Oracle NoSQL Database Proxy requires mutual authentication whereby the client authenticates the server and the server authenticates the client.

• Server Authentication
  
• Client Authentication
  
• Sample On-Premise Oracle NoSQL Configuration
  

9.2.28.2.1 Server Authentication

Upon initial connection, the Oracle NoSQL Database Proxy process passes a certificate to the Oracle NoSQL Java SDK (Oracle NoSQL Handler). The Oracle NoSQL Java SDK then verifies the certificate against a certificate in a configured trust store. After the certificate received from the proxy has been verified against the trust store, the client has authenticated the server.

9.2.28.2.2 Client Authentication

Upon initial connection, the Oracle NoSQL Java SDK (Oracle NoSQL Handler) passes credentials (username and password) to the Oracle NoSQL Database Proxy. These credentials are used for the NoSQL On-Premise instance to client.

9.2.28.2.3 Sample On-Premise Oracle NoSQL Configuration

gg.handlerlist=nosql
gg.handler.nosql.type=nosql
gg.handler.nosql.nosqlURL=https://localhost:5555
gg.handler.nosql.ddlHandling=CREATE,ADD,DROP
gg.handler.nosql.interactiveMode=false
#Client Credentials
gg.handler.nosql.username={your username}
gg.handler.nosql.password={your password}
gg.handler.nosql.mode=op
# Set the gg.classpath to pick up the Oracle NoSQL Java SDK
gg.classpath=/path/to/the/SDK/*
# Set the -D options in the bootoptions to resolve the trust store location and password
jvm.bootoptions=-Xmx512m -Xms32m -Djavax.net.ssl.trustStore=/usr/nosql/kv-20.3.17/USER/security/driver.trust -Djavax.net.ssl.trustStorePassword={your trust store password}

9.2.28.3 OCI Cloud Connectivity

Connectivity to an OCI Cloud instance of Oracle NoSQL is easier as it does not require the Oracle NoSQL Database Proxy required by the on-premise instance. Again, there is mutual authentication whereby the client authenticates the server and the server authenticates the client.

• Server Authentication
  
• Client Authentication
  
• Sample Cloud Oracle NoSQL Configuration
  
• Sample OCI Configuration file
  

9.2.28.3.1 Server Authentication

Upon initial connection, the Oracle NoSQL cloud instance passes a CA signed certificate to the client. The client then authenticates this CA signed certificate with the Certificate Authority. Once complete, the client has authenticated the server.

9.2.28.3.2 Client Authentication

Upon initial connection, the fingerprint, keyfile, and pass_phrase properties are used for the server to authenticate the client.

9.2.28.3.3 Sample Cloud Oracle NoSQL Configuration

gg.handlerlist=nosql
gg.handler.nosql.type=nosqlNoSQLSdkHandler
#gg.handler.nosql.type=nosql
gg.handler.nosql.ddlHandling=CREATE,ADD,DROP
gg.handler.nosql.interactiveMode=false
gg.handler.nosql.region=us-sanjose-1
gg.handler.nosql.configFilePath=/path/to/the/OCI/conf/file/nosql.conf
gg.handler.nosql.compartmentId=ocid1.compartment.oc1..aaaaaaaae2aedhka4jlb3h6zhpaonaoktmg53adwkhwjflvv6hihz5cvwfeq
gg.handler.nosql.storageGb=10
gg.handler.nosql.readUnits=50
gg.handler.nosql.writeUnits=50
gg.handler.nosql.mode=op
# Set the gg.classpath to pick up the Oracle NoSQL Java SDK
gg.classpath=/path/to/the/SDK/*

9.2.28.3.4 Sample OCI Configuration file

[DEFAULT]
user=ocid1.user.oc1..aaaaaaaaammf6u5h4wsmiuk52us5vnqhnnyzexkn56cqijlyo4vaao2jzi3a
fingerprint=77:53:2c:e5:31:81:48:c3:3d:af:60:cf:e0:42:5c:7f
tenancy=ocid1.tenancy.oc1..aaaaaaaattuxbj75pnn3nksvzyidshdbrfmmeflv4kkemajroz2thvca4kba
region=us-sanjose-1
key_file=/home/username/OracleNoSQL/lastname.firstname-04-13-18-51.pem
openssl rsa -aes256 -in in.pem -out out.pem

tenancy

The Tenancy ID is displayed at the bottom of the Console page.

region

The region is displayed with the header session drop-down menu in the Console.

fingerprint

To generate the fingerprint, use the How to Get the Key's Fingerprint instructions at:

https://docs.cloud.oracle.com/iaas/Content/API/Concepts/apisigningkey.htm

key_file

You need to share the public and private key to establish a connection with Oracle Cloud Infrastructure. To generate the keys, use the How to Generate an API Signing Keyat:

https://docs.cloud.oracle.com/iaas/Content/API/Concepts/apisigningkey.htm

pass_phrase

This is an optional property. It is used to configure the passphrase if the private key in the pem file is protected with a passphase. The following openssl command can be used to take an unprotected private key pem file and add a passphrase.

The following command prompts the user for the passphrase:

openssl rsa -aes256 -in in.pem -out out.pem

For more information, see Configuring Credentials for Oracle Cloud Infrastructure.

9.2.28.4 Oracle NoSQL Types

Oracle NoSQL provides a number of column data types and most of these data types are supported by the Oracle NoSQL Handler. A data type conversion from the column value in the trail file to the corresponding Java type representing the Oracle NoSQL column type in the Oracle NoSQL Handler is required.

The Oracle NoSQL Handler does not support Array, Map and Record data types by default. To support them, you can implement a custom data converter and override the default data type conversion logic to override it with your own custom logic to support your use case. Contact Oracle Support for guidance.

The following Oracle NoSQL data types are supported:

• Binary
• Boolen
• Double
• Integer
• Number
• String
• Timestamp

The following Oracle NoSQL data types are not supported:

• Array
• Map

9.2.28.5 Oracle NoSQL Handler Configuration

Properties	Required/Optional	Legal Values	Default	Explanation
gg.handler.name.type	Required	nosql	None	Selects the Oracle NoSQL Handler.
gg.handler.name.interactiveMode	Optional	true|false	true	When set to true, the NoSQL handler will process one operation at a time. When set to false, the NoSQL Handler will process the batch perations at transaction commit. Batching has limitations. Batched operations must be separated by table and all batch operations for a table must have a common shared key(s).
gg.handler.name.ddlHandling	Optional	CREATE, ADD, DROP in any combination separated by a comma delimiter	None	Configure the Oracle NoSQL Handler for the DDL functionality to provide. Options include CREATE, ADD, and DROP. When CREATE is enabled, the handler creates tables in Oracle NoSQL if a corresponding table does not exist. When ADD is enabled, the handler adds columns that exist in the source table definition, but do not exist in the corresponding target Oracle NoSQL table definition. When DROP is enabled, the handler drops columns that exist in the Oracle NoSQL table definition, but do not exist in the corresponding source table definition.
gg.handler.name.retries	Optional	Positive Integer	3	The number of retries on any read or write exception that the Oracle NoSQL Handler encounters.
gg.handler.name.requestTimeout	Optional	Positive Integer	30000	The maximum time in milliseconds for a NoSQL request to wait for a response. If the timeout is exceeded, the call is assumed to have failed.
gg.handler.name.noSQLURL	Optional	A valid URL including protocol.	None	On-premise only. Used to set the connectivity URL for the NoSQL proxy instance.
gg.handler.name.username	Optional	String	None	On-premise only. Used to set the username for connectivity to an on-premise NoSQL instance through the NoSQL proxy process.
gg.handler.name.password	Optional	String	None	On-premise only. Used to set the password for connectivity to an on-premise NoSQL instance through the NoSQL proxy process.
gg.handler.name.compartmentId	Optional	The OCID of an Oracle NoSQL compartment on OCI.	None	Cloud only. The OCID of an Oracle NoSQL cloud instance compartment on OCI.
gg.handler.name.region	Optional	Legal Oracle OCI region name.	None	Cloud only. The OCI region name of an Oracle NoSQL cloud instance.
gg.handler.name.configFilePath	Optional	A legal path and file name.	None	Cloud only. Set the path and file name of the config file containing the Oracle OCI information on the user, fingerprint, tenancy, region, and key-file.
gg.handler.name.profile	Optional	None	"DEFAULT"	Cloud only. Sets the named sub-section in the gg.handler.name.configFilePath. OCI config files can contain multiple entries and the naming specifies which entry to use.
gg.handler.name.storageGb	Optional	Positive Integer	10	Cloud only. Oracle NoSQL tables created in a cloud instance must be configured with a maximum storage size. This sets that configuration for tables created by the Oracle NoSQL Handler.
gg.handler.name.readUnits	Optional	Positive Integer	50	Cloud only. Oracle NoSQL tables created in an OCI cloud instance must be configured with read units which is the maximum read throughput. Each unit is 1KB per second.
gg.handler.name.writeUnits	Optional	Positive Integer	50	Cloud only. Oracle NoSQL tables created in an OCI cloud instance must be configured with write units which is the maximum write throughput. Each unit is 1KB per second.
gg.handler.name.abendOnUnmappedColumns	Optional	true|false	true	Set to true if the desired behavior of the handler is to abend when a column is found in the source table but the column does not exist in the target NoSQL table. Set to false if the desired behavior is for the handler to ignore columns found in the source table for which no corresponding column exists in the target NoSQL table.
gg.handler.name.dataConverterClass	Optional	The fully qualified data converter class name.	The default data converter.	The custom data converter can be implemented to override the default data conversion logic to support your specific use case. Must be included in the gg.classpath to be used.
gg.handler.name.timestampPattern	Optional	A legal pattern for parsing timestamps as they exist in the source trail file.	yyyy-MM-dd HH:mm:ss	This feature can be used to parse source field data into timestamps for timestamp target fields. The pattern needs to follow the Java convention for timestamp patterns and source data needs to conform to the pattern.
gg.handler.name.proxyServer	Optional	None	The proxy server host name.	Used to configure the forwarding proxy server host name for connectivity of on-premise Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) to Oracle Cloud Infrastructure (OCI) cloud instances of Oracle NoSQL. You must use at least version 5.2.27 of the Oracle NoSQL Java SDK.
gg.handler.name.proxyPort	Optional	80	Positive Integer	Used to configure the forwarding proxy server port number for connectivity of on-premise GG for DAA to OCI cloud instances of Oracle NoSQL. You must use at least version 5.2.27 of the Oracle NoSQL Java SDK.
gg.handler.name.proxyUsername	Optional	None	String	Used to configure the username of the forwarding proxy for connectivity of on-premise GG for DAA to OCI cloud instances of Oracle NoSQL if applicable. Most proxy servers do not require credentials. You must use at least version 5.2.27 of the Oracle NoSQL Java SDK.
gg.handler.name.proxyPassword	Optional	None	String	Used to configure the password of the forwarding proxy for connectivity of on-premise GG for DAA to OCI cloud instances of Oracle NoSQL if applicable. Most proxy servers do not require credentials. Must use at least version 5.2.27 of the Oracle NoSQL Java SDK.

9.2.28.6 Performance Considerations

When then NoSQL Handler is processing in interactive mode, operations are processing one at a time as they are received by the NoSQL Handler.

The NoSQL Handler will process in bulk mode if the following parameter is set.

gg.handler.name.interactiveMode=false

The NoSQL SDK allows bulk processing of operations for operations which meet the following criteria:

1. Operations must be for the same NoSQL table.
2. Operations mush be in the same NoSQL shard (have the same shard key or shard key values).
3. Only one operation per row exists in the batch.

When interactive mode is set to false, the NoSQL handler group operations by table and shard key, and deduplicates operations for the same row.

An example of Deduplication: If there is an insert and an update for a row, then only the update operation is processed if the operations fall within the same transaction or replicat grouped transaction.

The NoSQL handler may provide better performance when interactive mode is set to false. However, for the interactive mode to provide better performance, operations need to be groupable by the above criteria. If operations are not groupable by the above criteria or if operations or bulk mode only provide grouping into very small batches, then bulk mode may not provide much or any improvement in performance.

9.2.28.7 Operation Processing Support

The Oracle NoSQL Handler moves operations to Oracle NoSQL using synchronous API. The insert, update, and delete operations are processed differently in Oracle NoSQL databases rather than in a traditional RDBMS:

The following explains how insert, update, and delete operations are interpreted by the handler depending on the mode of operation:

• insert: If the row does not exist in your database, then an insert operation is processed as an insert. If the row exists, then an insert operation is processed as an update.
• update: If a row does not exist in your database, then an update operation is processed as an insert. If the row exists, then an update operation is processed as update.
• delete: If the row does not exist in your database, then a delete operation has no effect. If the row exists, then a delete operation is processed as a delete.

The state of the data in Oracle NoSQL databases is idempotent. You can replay the source trail files or replay sections of the trail files. Ultimately, the state of an Oracle NoSQL database is the same regardless of the number of times the trail data was written into Oracle NoSQL.

Primary key values for a row in Oracle NoSQL databases are immutable. An update operation that changes any primary key value for a Oracle NoSQL row must be treated as a delete and insert. The Oracle NoSQL Handler can process update operations that result in the change of a primary key in an Oracle NoSQL database only as a delete and insert. To successfully process this operation, the source trail file must contain the complete before and after change data images for all columns.

9.2.28.8 Column Processing

You can configure the Oracle NoSQL Handler to add columns that exist in the source trail file table definition though are missing in the Oracle NoSQL table definition. The Oracle NoSQL Handler can accommodate metadata change events of adding a column. A reconciliation process occurs that reconciles the source table definition to the Oracle NoSQL table definition. When configured to add columns, any columns found in the source table definition that do not exist in the Oracle NoSQL table definition are added. The reconciliation process for a table occurs after application start up the first time an operation for the table is encountered. The reconciliation process reoccurs after a metadata change event on a source table, when the first operation for the source table is encountered after the change event.

Drop Column Functionality

Similar to adding, you can configure the Oracle NoSQL Handler to drop columns. The Oracle NoSQL Handler can accommodate metadata change events of dropping a column. A reconciliation process occurs that reconciles the source table definition to the Oracle NoSQL table definition. When configured to drop columns, any columns found in the Oracle NoSQL table definition that are not in the source table definition are dropped.

Caution:

Dropping a column is potentially dangerous because it is permanently removing data from an Oracle NoSQL Database. Carefully consider your use case before configuring dropping.

Primary key columns cannot be dropped.

Column name changes are not handled well because there is no DDL-processing. The Oracle NoSQL Handler can handle any case change for the column name. A column name change event on the source database appears to the handler like dropping an existing column and adding a new column.

9.2.28.9 Table Check and Reconciliation Process

1. The Oracle NoSQL Handler interrogates the target Oracle NoSQL database for the table definition. If the table does not exist, the Oracle NoSQL Handler does one of two things. If gg.handler.name.ddlHandling includes CREATE, then a table is created in the database. Otherwise, the process abends and a message is logged that tells you the table that does not exist.
2. If the table exists in the Oracle NoSQL database, then the Oracle NoSQL Handler performs a reconciliation between the table definition from the source trail file and the table definition in the database. This reconciliation process searches for columns that exist in the source table definition and not in the corresponding database table definition. If it locates columns fitting this criteria and the gg.handler.name.ddlHandling property includes ADD, then the Oracle NoSQL Handler alters the target table in the database to add the new columns. Otherwise the columns missing in the target will not be added. If the property gg.handler.name.abendOnUnmappedColumns is set to true, then the NoSQL Handler will abend. Else, if the configuration propery gg.handler.name.abendOnUnmappedColumns is set to false, then the NoSQL Handler will continue the process and will not replicat data for the columns which exist in the source table and do not exist in the target NoSQL table.
3. The reconciliation process searches for columns that exist in the target Oracle NoSQL and do not exist in the source table definition. If it locates columns fitting this criteria and the gg.handler.name.ddlHandling property includes DROP, then the Oracle NoSQL Handler alters the target table in Oracle NoSQL to drop these columns. Otherwise, those columns are ignored.

• Full Image Data Requirements
  

9.2.28.9.1 Full Image Data Requirements

In Oracle NoSQL, update operations perform a complete reinsertion of the data for the entire row. This Oracle NoSQL feature improves ingest performance, but in turn levies a critical requirement. Updates must include data for all columns, also known as full image updates. Partial image updates are not supported (updates with just the primary key information and data for the columns that changed). Using the Oracle NoSQL Handler with partial image update information results in incomplete data in the target NoSQL table.

9.2.28.10 Oracle NoSQL SDK Dependencies

The maven coordinates are as follows:

Maven groupId: com.oracle.nosql.sdk

Maven artifactId: nosqldriver

Version: 5.2.27

• Oracle NoSQL SDK Dependencies 5.2.27
  

9.2.28.10.1 Oracle NoSQL SDK Dependencies 5.2.27

bcpkix-jdk15on-1.68.jar
bcprov-jdk15on-1.68.jar
jackson-core-2.12.1.jar
netty-buffer-4.1.63.Final.jar
netty-codec-4.1.63.Final.jar
netty-codec-http-4.1.63.Final.jar
netty-codec-socks-4.1.63.Final.jar
netty-common-4.1.63.Final.jar
netty-handler-4.1.63.Final.jar
netty-handler-proxy-4.1.63.Final.jar
netty-resolver-4.1.63.Final.jar
netty-transport-4.1.63.Final.jar
nosqldriver-5.2.27.jar