In a previous post, we discussed the installation of GoldenGate 26ai on a Linux x86-64 platform.
This post outlines the replication of Vector Embeddings between two Oracle 26ai databases using GoldenGate 26ai (23.26.1).
Load the ONNX vector imbedding model into the database
[oracle@perth onnx_models]$ sqlplus system/Oracle_4U@perth:1521/pdb1
SQL*Plus: Release 23.26.1.0.0 - Production on Thu Jan 29 02:17:02 2026
Version 23.26.1.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Last Successful login time: Thu Jan 29 2026 02:13:22 +00:00
Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0
SQL> create directory vector_dump as '/u01/app/oracle/onnx_models';
Directory created.
SQL> grant all on directory vector_dump to public;
Grant succeeded.
SQL> !ls -l /u01/app/oracle/onnx_models
total 721808
-rwxrwx---. 1 oracle oracle 133322334 Nov 7 05:12 all_MiniLM_L12_v2.onnx
SQL> create user vector_demo identified by Oracle_4U;
User created.
SQL> grant db_developer_role to vector_demo;
Grant succeeded.
SQL> alter user vector_demo quota unlimited on users;
User altered.
SQL> conn vector_demo/Oracle_4U@perth:1521/pdb1
Connected.
SQL> EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL('VECTOR_DUMP', 'all_MiniLM_L12_v2.onnx', 'all_MiniLM_L12_v2');
PL/SQL procedure successfully completed.
SQL> SELECT MODEL_NAME, MINING_FUNCTION, ALGORITHM, ALGORITHM_TYPE,MODEL_SIZE FROM USER_MINING_MODELS ;
MODEL_NAME MINING_FUNCTION ALGORITHM ALGORITHM_ MODEL_SIZE
---------------------------------------- ------------------------------ ------------------------------ ---------- ----------
ALL_MINILM_L12_V2 EMBEDDING ONNX NATIVE 133322334
SQL> SELECT TO_VECTOR(VECTOR_EMBEDDING(all_MiniLM_L12_v2 USING 'Gavin' as data)) AS embedding;
EMBEDDING
----------------------------------------------------------------------------------------------------
[-2.32835822E-002,5.69170825E-002,-6.23411499E-003,-3.53773721E-002,
Create and load data into the PARKS table
Thanks to Gerald Venzl from Oracle for providing sample data and scripts!!
sample-data/national-parks at main · gvenzl/sample-data · GitHub
CREATE TABLE parks
(
park_id VARCHAR(100) NOT NULL,
park_code VARCHAR(4) NOT NULL,
name VARCHAR(51) NOT NULL,
full_name VARCHAR(74),
url VARCHAR(34),
description VARCHAR(408),
designation VARCHAR(48),
latitude NUMERIC(13,10),
longitude NUMERIC(13,10),
states VARCHAR(47),
directions_info VARCHAR(512),
directions_url VARCHAR(162),
weather_info VARCHAR(520),
country_id VARCHAR(3) NOT NULL,
CONSTRAINT park_pk
PRIMARY KEY (park_id)
);
Populate the PARKS table
Populate the PARKS table via SQL Developer using the downloaded parks.csv file.
SQL> select count(*) from parks;
COUNT(*)
___________
472
Perform Initial Load
Create the Initial Load Extract which writes to the Initial Load Trail File ‘il’.
Start the extract. Verify the number of rows processed.
Create the Initial Load Replicat process. This replicat processes the trail file ‘il’ and writes records to the target database.
Start the replicat and verify the number of rows inserted into the target database.
Add a VECTOR data type column to the PARKS table
Add the DESC_VECTOR column to the PARKS table using the new data type VECTOR.
SQL> ALTER TABLE parks ADD (desc_vector VECTOR);
Table PARKS altered.
SQL> desc PARKS
Name Null? Type
__________________ ___________ ____________________
PARK_ID NOT NULL VARCHAR2(36)
PARK_CODE NOT NULL VARCHAR2(4)
NAME NOT NULL VARCHAR2(51)
FULL_NAME VARCHAR2(74)
URL VARCHAR2(34)
DESCRIPTION VARCHAR2(408)
DESIGNATION VARCHAR2(48)
LATITUDE NUMBER(13,10)
LONGITUDE NUMBER(13,10)
STATES VARCHAR2(47)
DIRECTIONS_INFO VARCHAR2(512)
DIRECTIONS_URL VARCHAR2(162)
WEATHER_INFO VARCHAR2(520)
COUNTRY_ID NOT NULL VARCHAR2(3)
DESC_VECTOR VECTOR(*,*,DENSE)
Create and start the Integrated Extract
Generate Vector Embeddings
Create vector embeddings for the DESCRIPTION column in the PARKS table via the VECTOR_EMBEDDING API using the ONNX model imported earlier.
SQL> UPDATE parks
SET desc_vector = VECTOR_EMBEDDING(all_MiniLM_L12_v2 USING description AS data);
472 rows updated.
SQL> commit;
Commit complete.
Note the vector embeddings are mathematical vector representations of data points or an array of numbers.
This vector representation translates semantic similarity into proximity in a mathematical vector space – which is performed by the vector embedding model.
SQL> select desc_vector from parks where rownum < 6;
DESC_VECTOR
--------------------------------------------------------------------------------
[-1.14020826E-002,8.99399631E-004,-1.47378864E-003,1.17907979E-001,
[2.02028025E-002,-2.39682421E-002,3.47130634E-002,-1.92367018E-003,
[-8.24544802E-002,8.51269215E-002,3.51562575E-002,2.01934308E-004,
[-1.24872737E-001,1.75917223E-002,-3.74127999E-002,6.66005388E-002,
[-6.0074456E-002,2.22685058E-002,1.78528391E-002,-1.36752073E-002,
Verify the GoldenGate has captured the UPDATE statements on the DESC_VECTOR column in the PARKS table.
Create a Parallel Non-Integrated Replicat
Start the Replicat and note the UPDATE statements which have been processed for the target schema PARKS table which has the VECTOR data type.
We test the replication of vector embeddings on the target database by issuing a similarity search query – for the string ‘Canoe’. We use the VECTOR EMBEDDING function which calls the loaded ONNX model.
Note the results of the query which shows the Oracle AI Vector Search feature!!
SQL> SELECT name, description
FROM parks
ORDER BY VECTOR_DISTANCE(desc_vector,
VECTOR_EMBEDDING(all_MiniLM_L12_v2 USING 'Canoe' AS data), COSINE)
FETCH EXACT FIRST 5 ROWS ONLY;
NAME
---------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
Saint Croix
Grab your paddle and your longing for adventure and head to the St. Croix and Na
mekagon rivers! Together they form the St. Croix National Scenic Riverway, offer
ing over 200 miles of clean water that glides and rushes through a forested land
scape. Paddle, boat, fish, and camp among this wild and scenic beauty. Hiking an
d historic towns also beckon.
Niobrara
With a little something for everyone, the Niobrara National Scenic River is a de
stination for crossing adventures off your bucket list. Most popular for river r
ecreation, you can float the Niobrara River on a canoe, tube, or kayak as a begi
nner and have a blast. Hiking, biking, and wildlife watching keep our visitors b
usy. Oh! And we haven't even told you about the waterfalls yet.
Lake Meredith
Within the dry plains of the Texas Panhandle lies a hidden oasis, a haven where
wildlife and people can find relief from the dry grasslands above. Through this
plain, the Canadian River has cut dramatic 200-foot canyons, or breaks, where hu
mans lived many years ago. Lake Meredith occupies these hidden coves and provide
s 7 different habitats for migratory birds and other wildlife.
Yukon - Charley Rivers
Located in Interior Alaska, Yukon-Charley Rivers offers exploration in a largely
untouched landscape. Whether you float the mighty Yukon River or paddle the Cha
rley River's whitewater, your memories will last a lifetime. Geology, cultural h
istory, gold rush remnants, wildlife, and vast scenery will be a part of your ex
perience. But, the strongest element will be solitude. Your adventure awaits.
Ebey's Landing
This stunning landscape on the Salish Sea, with its rich farmland and promising
seaport, lured the earliest American pioneers north of the Columbia River to Ebe
y?s Landing. Today Ebey?s Landing National Historical Reserve preserves the hist
orical, agricultural and cultural traditions of both Native and Euro-American ?
while offering spectacular opportunities for recreation.
