Oracle provides several options for customers to create Oracle Databases in Oracle Cloud as either Fully managed Autonomous Database Service or automated and self-managed Base Database Service in OCI. For choosing which database service best suits your purpose , please follow this blog Which Oracle database service to choose. For creating database link between Oracle Autonomous Database and Snowflake, please follow Step-by-Step instructions for creating database link between Autonomous Database and Snowflake. Following blog will walk you through the steps to create database link between Base Database Service in OCI and Snowflake Data Warehouse database. The blog assumes that you have already created a Snowflake database using ‘docs.snowflake.com’ and Base Db System in OCI. To create the Base DB System in OCI use these instructions Create a Oracle Base Database System. To get access to SSH follow these Steps.
Step 1: Once you have the shell access to the VM where you have installed Base Database System, execute the below commands as a root user, to install Linux ODBC packages
# yum install unixODBC -y /* installs ODBC package */
# which odbcinst
/bin/odbcinst
# which isql
/bin/isql
# odbcinst -j /* displays the configuration details */
Step 2 : Download and Install the latest ODBC driver for Snowflake using this Install ODBC driver for Snowflake
Create the below file with the URL’s specified in the link above
# cat /etc/yum.repos.d/snowflake-odbc.repo
[snowflake-odbc]
name=snowflake-odbc
baseurl=https://sfc-repo.snowflakecomputing.com/odbc/linux/3.0.2/
gpgkey=https://sfc-repo.snowflakecomputing.com/odbc/Snowkey-630D9F3CAB551AF3-gpg
# yum install snowflake-odbc -y /* this will install ODBC driver for Snowflake */
Step 3 : Configure ODBC driver for Snowflake following this Snowflake document Configuring ODBC driver for Snowflake
After adding entries to simba.snowflake.init file, the file might look similar to the below.
# cat /usr/lib64/snowflake/odbc/lib/simba.snowflake.ini
[Driver]
DriverManagerEncoding=UTF-16
DriverLocale=en-US
ErrorMessagesPath=/usr/lib64/snowflake/odbc/ErrorMessages – path to Snowflake ODBC directory
LogNamespace=
LogPath=/tmp
ODBCInstLib=libodbcinst.so
CURLVerboseMode=false
#LogLevel=6
CABundleFile=/usr/lib64/snowflake/odbc/lib/cacert.pem
Step 4 : Register the driver (odbcinst.ini) using the link Registering the driver using odbcinst.ini
odbcinst.ini file should look similar to this.
# cat /etc/odbcinst.ini
[SnowflakeDSIIDriver]
APILevel=1
ConnectFunctions=YYY
Description=Snowflake DSII
Driver=/usr/lib64/snowflake/odbc/lib/libSnowflake.so /* points to the path of the snowflake_odbc directory */
DriverODBCVer=03.52
SQLLevel=1
UsageCount=1
Step 5 : Configure connection parameters in odbc.ini file to connect to Snowflake using this link odbc.ini file ; section 4.3
odbc.ini file would look similar to the below
# cat /etc/odbc.ini
[SNOWDB]
Description=SnowflakeDB
Driver=SnowflakeDSIIDriver
Locale=en-US
SERVER=snowdb.eu-central-1.snowflakecomputing.com
PORT=443
SSL=on
DATABASE=SNOWDB
WAREHOUSE=WH_SNOW_DEV
SCHEMA=ODS
ROLE=ODSADMIN
UID=JOHN_SNOW
PWD=Str0ngP@ssw0rd!2023
Test the ODBC driver using isql
# isql -v SNOWDB /* the below SQL command should return a row */
SQL> SELECT CURRENT_DATABASE();
CURRENT_DATABASE()
SNOWDB
Step 6 : Add connection entry to Snowflake DB in to ORACLE_HOME/network/admin/listener.ora file as oracle user. listener.ora should have similar entries for Snowflake as below.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = SNOWDB)
(ENVS=”LD_LIBRARY_PATH=/usr/lib64:/usr/lib64/snowflake/odbc/lib:/u01/app/oracle/product/19.0.0.0/dbhome_1/hs/lib:/u01/app/oracle/product/19.0.0.0/dbhome_1/lib“)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
(PROGRAM = dg4odbc)
)
)
Stop and start the listener.
$lsnrctl stop
$lsnrctl start
Step 7 : Add SNOWDB in to tnsnames.ora file
SNOWDB =
(DESCRIPTION =
(ADDRESS= (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID=SNOWDB)
)
(HS=OK)
)
Step 8 :
Create initSNOWDB.ora file with the following entries
$ cat $ORACLE_HOME/hs/admin/initSNOWDB.ora
HS_FDS_CONNECT_INFO = “SNOWDB”
HS_FDS_TRACE_LEVEL = DEBUG
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
set ODBCINI=/etc/odbc.ini
set LD_LIBRARY_PATH=/usr/lib64
$ tnsping SNOWDB /* test the connection before creating database link */
TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 31-AUG-2023 20:13:20
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS= (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SID=SNOWDB)) (HS=OK))
OK (10 msec)
Step 9 : Create the DB Link to Snowflake
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 – Production on Thu Aug 31 20:14:14 2023
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c EE High Perf Release 19.0.0.0.0 – Production
Version 19.20.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
2 PDB$SEED READ ONLY NO
3 PDB19C READ WRITE NO
SQL> show parameter global_names;
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean TRUE
SQL> ALTER SYSTEM SET global_names=FALSE SCOPE=BOTH SID=’*’;
System altered.
SQL> show parameter global_names;
NAME TYPE VALUE
———————————— ———– ——————————
global_names boolean FALSE
SQL> alter session set container=PDB19C;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
———- —————————— ———- ———-
3 PDB19C READ WRITE NO
SQL> CREATE DATABASE LINK ORACLE_TO_SNOWFLAKE CONNECT TO “JOHN_SNOW” IDENTIFIED BY “&1” USING ‘SNOWDB’;
Enter value for 1: Str0ngP@ssw0rd!2023
old 1: CREATE DATABASE LINK ORACLE_TO_SNOWFLAKE CONNECT TO “JOHN_SNOW” IDENTIFIED BY “&1” USING ‘SNOWDB’
new 1: CREATE DATABASE LINK ORACLE_TO_SNOWFLAKE CONNECT TO “JOHN_SNOW” IDENTIFIED BY “Str0ngP@ssw0rd!2023” USING ‘SNOWDB’
Database link created.
SQL> SELECT COUNT(*) FROM JOHN_SNOW.SNOWFLAKE_TABLE@ORACLE_TO_SNOWFLAKE;
COUNT(*)
———-
9903
SQL> exit
Disconnected from Oracle Database 19c EE High Perf Release 19.0.0.0.0 – Production
Version 19.20.0.0.0
WARNING: In the event that you encounter the ORA-02085 error, update the database domain and bounce database (optional step)