Use AI Models in OCI GoldenGate to Create Vector Embeddings

Learn how to generate vector embeddings using the AI Service in Data replication deployments using GoldenGate 26ai and higher.

Overview

OCI GoldenGate includes an AI Service that allows GoldenGate processes to interact with AI models from various providers (OCI Generative AI, OpenAI, Google Gemini, and Voyage AI) to generate vector embeddings directly within data replication pipelines.

The following steps guide you through how to set up connectivity to AI Models and use them to generate vector embeddings in a Replicat.

Before you begin

You must have the following in order to proceed:

Task 1: Set up the environment

Task 2: Configure the AI Model

Task 3: Create the Integrated Extract

An Integrated Extract captures ongoing changes to source database.

  1. On the deployment Details page, select Launch console.

  2. If required, enter oggadmin for User name and the password you used when creating the deployment, and then select Sign In.

  3. Add Transaction Data and a Checkpoint Table:

    1. Open the navigation menu and then select DB Connections.

    2. Select Connect to database SourceDB.

    3. In the navigation menu, select Trandata, and then select Add Trandata (plus icon).

    4. For Schema Name, enter SRC_OCIGGLL, and then select Submit.

    5. To verify, enter SRC_OCIGGLL into the Search field and select Search.

    6. Open the navigation menu and then select DB Connections.

    7. Select Connect to database TargetDB.

    8. In the navigation menu, select Checkpoint, and then select Add Checkpoint (plus icon).

    9. For Checkpoint Table, enter "SRCMIRROR_OCIGGLL"."CHECKTABLE", and then select Submit.

  4. Add an Extract.

    Note: See additional extract parameter options for more information about parameters that you can use to specify source tables.

    On the Extract Parameters page, append the following lines under EXTTRAIL <trail-name>:

    -- Capture DDL operations for listed schema tables
ddl include mapped

-- Add step-by-step history of to the report file. Useful when troubleshooting.
ddloptions report

-- Write capture stats per table to the report file daily.
report at 00:01

-- Rollover the report file weekly. Useful when IE runs
-- without being stopped/started for long periods of time to
-- keep the report files from becoming too large.
reportrollover at 00:01 on Sunday

-- Report total operations captured, and operations per second
-- every 10 minutes.
reportcount every 10 minutes, rate

-- Table list for capture
table SRC_OCIGGLL.*;

  5. Check for long running transactions. Run the following script on your source database:

    select start_scn, start_time from gv$transaction where start_scn < (select max(start_scn) from dba_capture);

    If the query returns any rows, then you must locate the transaction's SCN and then either commit or rollback the transaction.

Task 4: Add and run a Non-integrated Replicat

  1. Add and run a Replicat.

    On the Parameter File screen, replace MAP *.*, TARGET *.*; with the following script:

    	-- Capture DDL operations for listed schema tables
 ddl include mapped
 --
 -- Add step-by-step history of ddl operations captured
 -- to the report file. Very useful when troubleshooting.
 ddloptions report
 --
 -- Write capture stats per table to the report file daily.
 report at 00:01
 --
 -- Rollover the report file weekly. Useful when PR runs
 -- without being stopped/started for long periods of time to
 -- keep the report files from becoming too large.
 reportrollover at 00:01 on Sunday
 --
 -- Report total operations captured, and operations per second
 -- every 10 minutes.
 reportcount every 10 minutes, rate
 --
 -- Table map list for apply
 MAP SRC_OCIGGLL.SRC_CITY, TARGET SRCMIRROR_OCIGGLL.SRC_CITY_VECTOR,
 COLMAP (
 USEDEFAULTS,
 CITY_EMBED = @AISERVICE(embed,'OCI_GenAI_TextEmbeddingSmall',CITY)
 );

    Note

    The @AISERVICE function creates a vector embedding from the data stored in the CITY column using the AI Model connection defined earlier.

  2. Perform Inserts to the source database:

    1. Return to the Oracle Cloud console and use the navigation menu to navigate back to Oracle AI Database, Autonomous AI Transaction Processing, and then SourceDB.

    2. On the SourceDB Details page, select Database actions, and then select SQL.

    3. Enter the following inserts, and then select Run Script:

      		Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1000,'Houston',20,743113);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1001,'Dallas',20,822416);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1002,'San Francisco',21,157574);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1003,'Los Angeles',21,743878);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1004,'San Diego',21,840689);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1005,'Chicago',23,616472);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1006,'Memphis',23,580075);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1007,'New York City',22,124434);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1008,'Boston',22,275581);
 Insert into SRC_OCIGGLL.SRC_CITY (CITY_ID,CITY,REGION_ID,POPULATION) values (1009,'Washington D.C.',22,688002);

    4. In the OCI GoldenGate Deployment Console, select the Extract name (UAEXT), and then select Statistics. Verify that SRC_OCIGGLL.SRC_CITY is listed with 10 inserts.

    5. Go back to the Overview screen, select the Replicat name (REP), and then select Statistics. Verify that SRCMIRROR_OCIGGLL.SRC_CITY is listed with 10 inserts

    6. Go to your target Autonomous AI Lakehouse Details page, select Database Actions, then select SQL

    7. In SQL, run select * from SRCMIRROR_OCIGGLL.SRC_CITY_VECTOR; and review the results, including the vector embeddings created by OCI GoldenGate using the AI Service.

The SRCMIRROR_OCIGGLL.SRC_CITY_VECTOR table contains rows replicated from SRC_OCIGGLL.SRC_CITY, and the CITY_EMBED column contains a generated vector embedding for each city.

Troubleshooting