Oracle has launched a new database with AI incorporated into the database. Oracle Database 23c is rebranded as Oracle 23ai. It is currently available to download and also comes as a free container image for Autonomous Database.
In this blog, we will deploy the free 23ai ADB container on a Macbook Pro (Intel) in this post :
1. Install Podman on your MacOS
brew install podman
2. Startup Podman on your Mac with Intel chip
podman machine init
podman machine set --cpus 4 --memory 8192
podman machine start
If you have a Mac with M1/M2/M3 chips then install Colima + Docker. And then just replace every ‘podman’ command with ‘docker’ instead
## For M1/M2/M3 Macs
brew install docker
brew install docker-compose
brew install colima
brew reinstall qemu
colima start --cpu 4 --memory 8 --arch x86_64
## Make sure you don’t have a previous container called ‘adb-free’ running. In case it is; just remove it using command ‘docker container rm <container-id>’
If the container is not starting and you’re getting below error :
docker logs adb-free
TIME ELAPSED Unzipping /u01/POD1.zip: 1 minutes and 11 seconds elapsed
User input JSON not found
PermissionError: [Errno 13] Permission denied: '/u01/data/input.json'
# Then create the volume on your host machine and assign permissions as below :
mkdir -p /Users/shadab/Downloads/u01/data
chmod -R 775 /Users/shadab/Downloads/u01/data
docker container rm <container-id>
Re-run
docker run -d \
-p 1521:1522 \
-p 1522:1522 \
-p 8443:8443 \
-p 27017:27017 \
-e WORKLOAD_TYPE=ATP \
-e WALLET_PASSWORD=YourP@ssword321#_ \
-e ADMIN_PASSWORD=YourP@ssword321#_ \
--cap-add SYS_ADMIN \
--device /dev/fuse \
--volume adb_container_volume:/Users/shadab/Downloads/u01/data \
--name adb-free \
container-registry.oracle.com/database/adb-free:latest-23ai
docker container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
6a838dc47e3c container-registry.oracle.com/database/adb-free:latest-23ai "/bin/bash -c /u01/s…" 7 minutes ago Up 7 minutes (healthy) 0.0.0.0:1522->1522/tcp, :::1522->1522/tcp, 0.0.0.0:8443->8443/tcp, :::8443->8443/tcp, 0.0.0.0:27017->27017/tcp, :::27017->27017/tcp, 0.0.0.0:1521->1522/tcp, :::1521->1522/tcp adb-free
Then proceed with the same steps as below, skipping the first podman command.
3. Deploy the ADB 23ai container
# For INTEL Macs
podman run -d \
-p 1521:1522 \
-p 1522:1522 \
-p 8443:8443 \
-p 27017:27017 \
-e WORKLOAD_TYPE=ATP \
-e WALLET_PASSWORD=YourP@ssword321#_ \
-e ADMIN_PASSWORD=YourP@ssword321#_ \
--cap-add SYS_ADMIN \
--device /dev/fuse \
--volume adb_container_volume:/u01/data \
--name adb-free \
container-registry.oracle.com/database/adb-free:latest-23ai
# For M1/M2/M3 Macs
docker run -d \
-p 1521:1522 \
-p 1522:1522 \
-p 8443:8443 \
-p 27017:27017 \
-e WORKLOAD_TYPE=ATP \
-e WALLET_PASSWORD=YourP@ssword321#_ \
-e ADMIN_PASSWORD=YourP@ssword321#_ \
--cap-add SYS_ADMIN \
--device /dev/fuse \
--volume adb_container_volume:/Users/shadab/Downloads/u01/data \
--name adb-free \
container-registry.oracle.com/database/adb-free:latest-23ai
4. Check the container status, add database, change password and connect to it
$ podman container ls
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
ecfc957e782b container-registry.oracle.com/database/adb-free:latest-23ai 5 minutes ago Up 5 minutes (healthy) 0.0.0.0:1521->1522/tcp, 0.0.0.0:1522->1522/tcp, 0.0.0.0:8443->8443/tcp, 0.0.0.0:27017->27017/tcp adb-freeCreate alias for adb-cli
$ alias adb-cli="podman exec ecfc957e782b adb-cli" # ecfc957e782b is the container id for the ADB container we got from the command 'podman container ls'#For Apple Silicon (M1/M2/M3 Macs)
$ alias adb-cli="docker exec adb-free adb-cli"$ adb-cli --help Commands: add-database Plug a database in the running adb container... change-expired-password Changed expired password for a user change-password Changed password for a user cleanup-container-data-dir Used to clean-up old stale files before... set-http-proxy Set HTTP proxy for the Database # Add New Database adb-cli add-database --workload-type "ADW" --admin-password "YourP@ssword321#_" # Change the ADMIN and Wallet Password for the New Database adb-cli change-password --database-name "MYADW" --old-password "YourP@ssword321#_" --new-password "WelcomeP@ssword321#_"
Access APEX :
https://localhost:8443/ords/myadw/apex
Access SQL Developer Web :
https://localhost:8443/ords/myadw/sql-developer
Username : ADMIN
Password : WelcomeP@ssword321#_
Important Note : use the URL formats https://localhost:8443/ords/{database_name}/apex and https://localhost:8443/ords/{database_name}/sql-developer to access APEX and Database Actions respectively.
Download Wallet and Connect with sqlcl
1. Install sqlcl on your Mac
$ brew install sqlcl
2. Add below to your bash or zsh profile
$ export PATH=/usr/local/Caskroom/sqlcl/24.1.0.087.0929/sqlcl/bin:"$PATH"
$ sql -v
SQLcl: Release 24.1.0.0 Production Build: 24.1.0.087.0929
3. Copy the Wallet to your local machine
$ mkdir -p /Users/shadab/Downloads/wallet_adb_free_container
$ cd /Users/shadab/Downloads/wallet_adb_free_container
$ podman cp ecfc957e782b:/u01/app/oracle/wallets/tls_wallet ./
#You can see a folder called tls_wallet created which has the wallet files
4. Export TNS_ADMIN variable with the location of the wallet and add to your bash or zsh profile
$ export TNS_ADMIN='/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet'
5. Check tnsnames.ora file in the tls_wallet folder for the tns entry for your ATP
$ cd tls_wallet/
$ ls -ltr
$ cat tnsnames.ora
$ echo $TNS_ADMIN
6. Connect with sqlcl using the tns entry
$ sql ADMIN@myadw_high
SQLcl: Release 21.4 Production on Fri. May 03 14:32:25 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Password? (**********?) WelcomeP@ssword321#_
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - Production
Version 23.4.0.24.05
SQL> select instance_name from gv$instance;
INSTANCE_NAME
________________
POD1
Test with Python3.11
For this test, we will convert some strings and numbers to a vector and save it as a vector datatype in Oracle 23ai
Install Python oracledb driver to interact with our Oracle 23ai ADB container
python3.11 -m pip install oracledb
python3.11 -m pip install PyPDF2
python3.11 -m pip install openai==0.28
python3.11 -m pip install numpy
#### Simple Connect Script to 23ai using python-oracledb driver in thin mode ####
import oracledb
conn = oracledb.connect(user="ADMIN", password="WelcomeP@ssword321#_", dsn="myadw_high", config_dir="/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet", wallet_location="/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet", wallet_password="WelcomeP@ssword321#_")
cr = conn.cursor()
r = cr.execute("SELECT 1 FROM DUAL")
print(r.fetchall())
#### Create vector datatype for 23ai using python-oracledb driver in thin mode ####
import array
import oracledb
connection = oracledb.connect(user="ADMIN", password="WelcomeP@ssword321#_", dsn="myadw_high", config_dir="/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet", wallet_location="/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet", wallet_password="WelcomeP@ssword321#_")
print("Connected to Oracle Database")
cursor = connection.cursor()
cursor.execute("""
begin
execute immediate 'drop table t1';
exception when others then if sqlcode <> -942 then raise; end if;
end;""")
cursor.execute("""
create table t1 (
ID NUMBER,
VCOL VECTOR(3),
VCOL32 VECTOR(3, FLOAT32),
VCOL64 VECTOR(3, FLOAT64),
VCOL8 VECTOR(3, INT8),
PRIMARY KEY (id))""")
# Bind variable values
id_val = 1
vector_val = [5.3, 2.4, 3.1412]
vector_data_32 = array.array('f', [1.625, 2.5, 3.0]) # 32-bit float
vector_data_64 = array.array('d', [4.25, 5.75, 6.5]) # 64-bit float
vector_data_8 = array.array('b', [7, 8, 9]) # 8-bit signed integer
cursor.setinputsizes(None, oracledb.DB_TYPE_VECTOR)
cursor.execute("insert into t1 values (:1, :2, :3, :4, :5)",
[id_val,
vector_val,
vector_data_32,
vector_data_64,
vector_data_8])
connection.commit()
cursor.execute('select * from t1')
for row in cursor:
print(row)
connection.close()
print("Disconnected from Oracle Database")
#### Create embeddings using OpenAI ADA model and write to Vector Datatype Oracle 23ai using python-oracledb driver in thin mode ####
import oracledb
import os
import openai
import numpy
# Get your OpenAI API Key from the environment
openai.api_key = os.getenv("OPENAI_API_KEY")
# Create the vector embedding [a JSON object]
response = openai.Embedding.create(
model="text-embedding-ada-002",
input="The food was delicious and the waiter..."
)
# Extract the vector from the JSON object
vec = response['data'][0]['embedding']
# Verify the number of dimensions in the vector
print(len(vec))
# Connect to your Oracle 23.4 database
connection = oracledb.connect(user="ADMIN", password="WelcomeP@ssword321#_", dsn="myadw_high", config_dir="/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet", wallet_location="/Users/shadab/Downloads/wallet_adb_free_container/tls_wallet", wallet_password="WelcomeP@ssword321#_")
cursor = connection.cursor()
# Make sure to use a table with the correctly size vector
cursor.execute("""
begin
execute immediate 'drop table open_ai';
exception when others then if sqlcode <> -942 then raise; end if;
end;""")
cursor.execute("""
create table open_ai (
id number,
v vector(1536, float32),
primary key (id))""")
cursor.setinputsizes(None, oracledb.DB_TYPE_VECTOR)
# Insert the vector using the bind variable from the generated vector
id_val = 1
cursor.execute("insert into open_ai values (:1, :2)", [id_val, vec])
# Retrieve the vector
cursor.execute('select * from open_ai')
for row in cursor:
print(row)
connection.commit()
connection.close()
print("Bye bye ")