11.2.36 OCI Search

The OCI Search Handler delivers transactional changes from Oracle GoldenGate trail files to an OpenSearch cluster.

The handler uses the OpenSearch Java client to write source operations as OpenSearch documents, enabling near real-time indexing and search capabilities. The OCI Search Handler supports connections to Oracle Cloud Infrastructure (OCI) Search as well as OpenSearch clusters, depending on the deployment model.

  • Overview
    OCI Search is a fully managed search and analytics service available on Oracle Cloud Infrastructure (OCI). Built on the OpenSearch project, OCI Search provides distributed indexing, full-text search, and analytics while maintaining compatibility with OpenSearch APIs and client libraries.
  • Detailing the Functionality
    This section describes how the OCI Search Handler maps Oracle GoldenGate trail records to OpenSearch indexes and documents.
  • Setting Up and Running the OCI Search Handler
    This section describes how to configure the OCI Search Handler to connect to an OpenSearch cluster.

Parent topic: Target

11.2.36.1 Overview

OCI Search is a fully managed search and analytics service available on Oracle Cloud Infrastructure (OCI). Built on the OpenSearch project, OCI Search provides distributed indexing, full-text search, and analytics while maintaining compatibility with OpenSearch APIs and client libraries.

The OCI Search Handler uses the OpenSearch Java client to connect to an OpenSearch cluster and apply transactional changes captured by Oracle GoldenGate. Each database operation in the source trail is translated into the corresponding OpenSearch document operation.

The OCI Search Handler supports the following deployment models:
  • On-premises Oracle GoldenGate for Distributed Applications and Analytics deployment: The OCI Search Handler can connect to any supported OpenSearch cluster regardless of where the cluster is hosted.
  • Oracle GoldenGate Service (GGS) deployment: The OCI Search Handler supports connections only to OpenSearch clusters hosted by the OCI Search service.

The OCI Search Handler uses the OpenSearch Java client to connect and receive data into an OpenSearch cluster, either set locally or in OCI Search. For more information about OpenSearch, see https://opensearch.org.

Parent topic: OCI Search

11.2.36.2 Detailing the Functionality

This section describes how the OCI Search Handler maps Oracle GoldenGate trail records to OpenSearch indexes and documents.

Parent topic: OCI Search

11.2.36.2.1 Indexes and Mapping

An index is a logical namespace that stores related documents within an OpenSearch cluster. Similar to a table in a relational database, an index groups documents that share a common structure. All documents within an index should contain the same fields and compatible data types.

The OCI Search Handler derives the target index name from the source trail object name.
  • For two-part object names, the index name is created by concatenating the schema and table names using an underscore ( _ ).
  • For three-part object names, the handler concatenates the catalog, schema, and table names.
Source Trail Object Open Search Index

schema.tablename

schema_tablename

catalog.schema.tablename

catalog_schema_tablename

If the target index does not exist when the OCI Search Handler processes the first operation for a source table, the handler automatically creates the index before applying the operation.

Parent topic: Detailing the Functionality

11.2.36.2.2 Documents

A document is the basic unit of information stored in an OpenSearch index. Each document is represented as a JSON object containing one or more fields.

Each document is uniquely identified by the _id field.

The OCI Search Handler uses the primary key value of the source table as the document identifier. Each source row is therefore mapped to a single OpenSearch document.

Column values from the Oracle GoldenGate trail are converted into JSON fields and written to the corresponding OpenSearch document.

Parent topic: Detailing the Functionality

11.2.36.2.3 Primary Key Updates

The OCI Search Handler creates the OpenSearch document identifier ( _id ) from the primary key value of the source table.

Because the document identifier cannot be modified after a document has been created, updates to a source table primary key are processed as two separate operations:

A DELETE and then an Insert.

Note:

It is expected to have full image updates in a trail file for primary key updates, without full image updates the data after the primary key update will be incomplete.

Parent topic: Detailing the Functionality

11.2.36.2.4 Supported Data Types

The OCI Search Handler supports the following data types.
  • 32-bit Integer
  • 64-bit Integer
  • Double
  • Timestamp
  • String
  • Boolean
  • Binary

Parent topic: Detailing the Functionality

11.2.36.2.5 Operation Processing Support

The OCI Search Handler operates in operation mode, processing each database operation independently for improved throughput.The following operations are supported.

The following operations are supported.

INSERT

For an INSERT operation, the OCI Search Handler performs the following actions:

  • Creates the target index if it does not already exist. Creates a new document using the values contained in the source row. Uses the source table primary key value as the document identifier.

UPDATE

For an UPDATE operation:

  • If the target index and document exist, the document is updated using the column values contained in the trail record.
  • If the target index or document does not exist, the handler creates the index (if necessary) and inserts a new document using the values contained in the UPDATE operation

Note:

Partial image updates are supported for updates that do not modify the primary key.

DELETE

For a DELETE operation:

  • If the target index and document exist, the document is deleted.
  • If the target index does not exist, the handler creates an empty index before processing the delete operation.

TRUNCATE

The OpenSearch data model does not support the Oracle GoldenGate TRUNCATE operation.

If a Replicat configured with the OCI Search Handler processes a TRUNCATE operation, the Replicat process abends.

Parent topic: Detailing the Functionality

11.2.36.3 Setting Up and Running the OCI Search Handler

This section describes how to configure the OCI Search Handler to connect to an OpenSearch cluster.

Parent topic: OCI Search

11.2.36.3.1 About the Connection

A cluster is a collection of one or more nodes (servers) that holds the entire data. It provides federated indexing and search capabilities across all nodes.

A node is a single server that is part of the cluster, stores the data, and participates in the cluster’s indexing and searching.

The OCI Search Handler property gg.handler.name.ServerAddressList can be set to point to the nodes available in the cluster.

Parent topic: Setting Up and Running the OCI Search Handler

11.2.36.3.2 Setting up the Classpath

The OCI Search Handler requires the OpenSearch Java client libraries. These libraries must be included in the Java Adapter classpath before starting the Replicat process.

The required dependencies can be downloaded using the ocisearch.sh Dependency Downloader script located in: 
OGG_INSTALL_HOME/opt/DependencyDownloader

The script can be run with up to two optional parameters:

  • OpenSearch Java client version
  • OpenSearch REST client version

If no parameters are specified, both dependency versions default to 3.1.0.

The following example downloads the default dependency versions:

./ocisearch.sh

To download specific dependency versions:

./ocisearch.sh <opensearch-java-version> <opensearch-rest-client-version>

After the script completes successfully, the downloaded dependencies are stored in:

OGG_INSTALL_HOME/opt/DependencyDownloader/dependencies

Configure the gg.classpath property to include the downloaded libraries.

The wildcard character ( * ) can be used to include all JAR files within the dependency directory.

Note:

Use the wildcard character ( * ) only. Do not specify *.jar

The following example shows a correctly configured classpath:

gg.classpath=OGG_INSTALL_HOME/opt/DependencyDownloader/dependencies/ocisearch_3.1.0/*

Parent topic: Setting Up and Running the OCI Search Handler

11.2.36.3.3 Configuring the OCI Search Handler

The OCI Search Handler is configured using the Java Adapter properties file.

The following table describes the configurable properties supported by the OCI Search Handler.
Property Required/Optional Legal Values Default Explanations
gg.handlerlist Required

Name (Any name of your choice for handler)

Default Specifies the list of handlers used by the Replicat process.
gg.handler.name.type Required

opensearch

None Type of handler to use. For example, Elasticsearch, Kafka, or Flume.
gg.handler.name.ServerAddressList Required

Server:Port[, Server:Port …]

None Comma separated list of contact points of the nodes.
gg.handler.name.bulkWrite Optional

True | false

localhost:9200 When this property is true, the OCI Search Handler uses the bulk write API to ingest data into OpenSearchcluster. The batch size of bulk write can be controlled using the MAXTRANSOPS Replicat parameter.
gg.handler.name.authType Required

basic | ssl

false Controls the authentication type for the OpenSearchclient. basic - Client authentication using username and password without message encrytption. ssl - Mutual authentication. Client authenticates the server via certificate. If the certificate is a CA signed certificate, trustore isn’t required, if it isn’t a CA signed certificate, trustore is required. Server authenticates client either by using username and password or via certificate. If the server is configured for SSL mutual auth, then keystore configuration is required on the client side. Messages are encrypted.
gg.handler.name.username Required (for auth-type basic.) A valid username none The username for the server to authenticate the OpenSearchclient. Must be provided for auth types basic.
gg.handler.name.password Required (for auth-type basic.) A valid password None The password for the server to authenticate the OpenSearchclient. Must be provided for auth types basic.
gg.handler.name.trustStore Optional A valid password The fully qualified name (path + name) of trust-store file None The truststore for the OpenSearchclient to validate the certificate received from the OpenSearchserver. Will only be read if the auth type is set to ssl.
gg.handler.name.trustStorePassword Optional A valid trust-store Password None The truststore’s password for the OpenSearchclient to validate the certificate received from the OpenSearchserver. Will only be read if the auth type is set to ssl.
gg.handler.name.trustStoreType Optional pkcs12, jks jks The truststore type of the OpenSearchclient. Will only be read if the auth type is set to ssl.
gg.handler.name.keyStore Optional The fully qualified name (path + name) of trust-store file None The keystore for the OpenSearch client to present its certificate to the OpenSearch server. Will only be read if the auth type is set to ssl.
gg.handler.name.keyStorePassword Optional The fully qualified name (path + name) of trust-store file A valid key-store Password None The password for the keystore for the OpenSearch client to present its certificate to the OpenSearch server. Will only be read if the auth type is set to ssl. is set to ssl.
gg.handler.name.keyStoreType Optional pkcs12, jks jks The keystore type of the OpenSearchclient. Will only be read if the auth type
gg.handler.name.keyPassword Optional A valid key-store key password None The password for the private key in the keystore for the OpenSearch client to present its certificate to the OpenSearch server. Will only be read if the auth type is set to ssl.
gg.handler.name.maxConnectTimeout Optional Positive integer Default value of Apache HTTP Components framework. Must be provided only if the private key in the keystore is protected by a password. Set the maximum wait period for a connection to be established from the OpenSearchclient to the OpenSearchserver.
gg.handler.name.maxSocketTimeout Optional Positive Integer Default value of Apache HTTP Components framework. Sets the maximum wait period in milliseconds to wait for a response from the service after issuing a request. May need to be increased when pushing large data volumes.
gg.handler.name.proxyUsername Optional The proxy server username None If the connection to the OpenSearch client is routed through a proxy server, this property sets the proxy server’s username. Most proxy servers do not require credentials.
gg.handler.name.proxyPassword Optional The proxy server password None If the connection to the OpenSearch client is routed through a proxy server, this property sets the proxy server’s password. Most proxy servers do not require credentials.
gg.handler.name.proxyProtocol Optional http | https None If the connection to the OpenSearch client is routed through a proxy server, this property sets the proxy server’s protocol.
gg.handler.name.proxyPort Optional The port number of your proxy server. None If the connection to the OpenSearch client is routed through a proxy server, this property sets the proxy server’s port.
gg.handler.name.proxyServer Optional The host name of your proxy server. None If the connection to the OpenSearch client is routed through a proxy server, this property sets the proxy server’s host name.

Sample properties of a replicat using the OCI Search handler

gg.handlerlist=opensearch
gg.handler.opensearch.type=opensearch 
gg.handler.opensearch.serverAddressList=100.70.111.130:9200  
gg.handler.opensearch.authType=basic 
gg.handler.opensearch.basicAuthUsername=admin  
gg.handler.opensearch.basicAuthPassword=xxxxx 
gg.classpath=OGG_INSTALL_HOME/opt/DependencyDownloader/dependencies/ocisearch_3.1.0/*

Parent topic: Setting Up and Running the OCI Search Handler

11.2.36.3.3.1 Authentication for the OpenSearch Client

Authentication for the OpenSearch Client

OpenSearch supports multiple authentication mechanisms for client connections, including:

  • Basic
  • Kerberos
  • SSL
  • JWT
  • SAML
  • OpenID
  • Proxy

The OCI Search Handler currently supports the following authentication methods:

  • Basic
  • SSL
For more information about configuring the OpenSearch security backend, see https://docs.opensearch.org/latest/security/configuration/configuration/

Note:

Basic authentication uses the HTTP protocol, whereas SSL authentication uses HTTPS. When the OpenSearch Security plugin is enabled (the default configuration), OpenSearch accepts only HTTPS connections. To use HTTP, the security plugin must be disabled.

Using Basic Authentication

Set the gg.handler.<handlerName>.authType property to basic to configure Basic authentication.

When Basic authentication is enabled, you must also configure the following properties:

  • gg.handler.<handlerName>.basicAuthUsername
  • gg.handler.<handlerName>.basicAuthPassword

    Note:

    When authType is set to basic, SSL-related properties, such as trustStore, trustStorePassword, keyStore, and keyStorePassword are ignored.

The following example shows a Java Adapter properties file configured for Basic authentication.

gg.handlerlist=opensearch
gg.handler.opensearch.type=opensearch

gg.handler.opensearch.ServerAddressList=100.70.111.130:9200

gg.handler.opensearch.authType=basic
gg.handler.opensearch.basicAuthUsername=admin
gg.handler.opensearch.basicAuthPassword=xxxxx

Using SSL Authentication

Set the gg.handler.<handlerName>.authType property to ssl to configure SSL authentication.

When SSL authentication is enabled, the following properties are available.

  • gg.handler.name.trustStore: The truststore for the OpenSearch client to validate the certificate received from the OpenSearch server.
  • gg.handler.name.trustStorePassword: The truststore’s password for the OpenSearch client to validate the certificate received from the OpenSearch server.

  • gg.handler.name.trustStoreType: The truststore type of the OpenSearch client. (PKCS12 / JKS). JKS set by default.

  • gg.handler.name.keyStore: The keystore for the OpenSearch client to present its certificate to the OpenSearch server.
  • gg.handler.name.keyStorePassword:The password for the keystore for the OpenSearch client to present its certificate to the OpenSearch server
  • gg.handler.name.keyStoreType: The keystore type of the OpenSearch client. (PKCS12 / JKS). JKS set by default.
  • gg.handler.name.keyPassword: The password for the private key in the keystore for the OpenSearch client to present its certificate to the OpenSearch server. Must be provided only if the private key in the keystore is protected by a password.
None of the above is mandatory, since an OpenSearchcluster security layer is really customizable.

Note:

The basicAuthUsername and basicAuthPassword properties can also be configured when authType is set to ssl. These properties are used when the OpenSearch server requires user authentication in addition to SSL/TLS.
The following example shows a Java Adapter properties file configured for SSL authentication.
gg.handlerlist=opensearch
gg.handler.opensearch.type=opensearch

gg.handler.opensearch.ServerAddressList=100.70.111.130:9200

gg.handler.opensearch.authType=ssl

gg.handler.opensearch.basicAuthUsername=admin
gg.handler.opensearch.basicAuthPassword=xxxxx

gg.handler.opensearch.trustStore=/someDir/caKS.jks
gg.handler.opensearch.trustStoreType=jks
gg.handler.opensearch.trustStorePassword=xxxxxx

Using Bulk Write

The OCI Search Handler supports Bulk operation mode, which groups multiple operations into a batch and submits the batch to the target OpenSearch cluster in a single request. Using the Bulk API can improve data ingestion performance.

Enable Bulk operation mode by setting the following property:

gg.handler.<handlerName>.bulkWrite=true

By default, Bulk operation mode is disabled.

Bulk operation mode has the following limitations:

  • If an operation within a bulk request fails, the remaining operations in the batch are not applied.
  • A failed bulk request can result in inconsistent data between the source and target systems.

For example, a DELETE operation against a missing index generates an exception. If the DELETE operation is part of a bulk request, subsequent operations in the batch are not processed.

To minimize Bulk API errors, Oracle recommends configuring the Replicat MAXTRANSOPS parameter to match, at a minimum, the Bulk thread pool queue size configured for the target OpenSearch cluster.