In modern data warehousing, the “pure” Oracle environment is a myth. We often manage dozens of Oracle databases but still encounter that one necessary SQL Server instance holding critical data.
When you need to pull data from SQL Server into Oracle, you have two primary options: the paid “Oracle Database Gateway for SQL Server” (DG4MSQL) or the free “Oracle Database Gateway for ODBC” (DG4ODBC).
This guide covers the technical implementation of the free DG4ODBC solution on Oracle Linux 9, documenting the exact configuration steps, common pitfalls, and performance optimizations required for a production-grade ETL pipeline.
Part 1: The Decision Matrix (Free vs. Paid)
Before implementation, it is vital to understand what you are choosing. I frequently get asked, “Why shouldn’t we just buy the dedicated gateway?” Here is the technical breakdown:
| Feature | Oracle Gateway for ODBC (DG4ODBC) | Oracle Gateway for SQL Server (DG4MSQL) |
| Cost | Free. Included in your Oracle DB License. | Paid. Licensed per CPU core of the target machine. |
| Architecture | Generic Wrapper. Oracle ==> ODBC ==> SQL Server. | Native Translation. Oracle ==> TDS Protocol ==> SQL Server. |
| Performance | Good for Batch/ETL. Dependent on driver quality. | Superior for high-frequency queries. |
| Transaction Support | Basic Read/Write. 2PC is difficult to configure. | Robust Distributed Transactions (2PC). |
| Data Type Mapping | Standard ODBC mapping. | Native SQL Server type awareness (e.g., MONEY). |
The Verdict: For ETL pipelines where you are extracting data to a warehouse (Bulk Read), DG4ODBC is the pragmatic, cost-effective choice.
Part 2: The Architecture
We are building a chain of communication that looks like this:
Part 3: Step-by-Step Implementation Guide
Environment:
- Source: SQL Server 2019/2022
- Target: Oracle Database 19c on Oracle Linux 9.6 (RHEL 9 Compatible)
Step 1: Install the ODBC Driver (OS Level)
Oracle Linux 9 does not include the Microsoft drivers by default. We must add the official Microsoft repository.
- Register the Microsoft RHEL 9 Repository:
sudo curl -o /etc/yum.repos.d/mssql-release.repo https://packages.microsoft.com/config/rhel/9/prod.repo
- Remove Conflicting Packages:
Critical: Older unixODBC packages often conflict with the Microsoft driver.
sudo dnf remove unixODBC-utf16 unixODBC-utf16-devel
- Install the Driver and Tools:
This installs msodbcsql18 (the driver) and mssql-tools18 (for testing).
sudo ACCEPT_EULA=Y dnf install -y msodbcsql18 mssql-tools18
- Verify Installation:
odbcinst -q -d
# Output should include: [ODBC Driver 18 for SQL Server]
Step 2: Configure the OS Data Source (ODBC.INI)
We must define a DSN (Data Source Name) so the Linux OS knows how to find the SQL Server.
File: /etc/odbc.ini
Action: Create or append the following block.
[SQLSERVER_DSN]
Driver = ODBC Driver 18 for SQL Server
Server = 192.168.1.50,1433 <– Replace with your SQL IP and Port
Database = PPS_DB <– Replace with your ACTUAL Database Name
TrustServerCertificate = yes <– Required for Driver 18+ on internal networks
Encrypt = yes
Testing the OS Connection:
Before touching Oracle, verify the OS can connect.
isql -v SQLSERVER_DSN <SQL_User> <SQL_Password>
- Success: You see a prompt +—————-+ | Connected! |.
- Failure: Do not proceed until isql works.
Step 3: Configure the Oracle Gateway (INIT.ORA)
Now we configure the Oracle Gateway binary.
Location: $ORACLE_HOME/hs/admin
File Name: initdg4msql.ora (The suffix dg4msql determines the SID).
# This points to the DSN name defined in Step 2
HS_FDS_CONNECT_INFO = SQLSERVER_DSN
HS_FDS_TRACE_LEVEL = OFF
# Point to the Driver Manager library (NOT the driver itself)
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
# PERFORMANCE TUNING
# Critical for ETL: Increases batch size to reduce network round trips
HS_FDS_FETCH_ROWS = 1000
Step 4: Configure the Oracle Listener
The Listener needs to spawn the dg4odbc program when requested.
Location: $ORACLE_HOME/network/admin/listener.ora
Add this entry to your SID_LIST:
(SID_DESC =
(SID_NAME = dg4msql) <– Must match the init file suffix
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(PROGRAM = dg4odbc)
(ENVS=”LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql18/lib64:$ORACLE_HOME/lib”)
)
Crucial Detail: The ENVS parameter is mandatory. The Oracle Listener runs in a restricted environment and will not see the ODBC libraries in /usr/lib64 or /opt/microsoft unless explicitly told to look there.
Restart Listener:
lsnrctl stop
lsnrctl start
Step 5: Configure TNS Names
Tell the Oracle Database how to reach the Listener service we just created.
Location: $ORACLE_HOME/network/admin/tnsnames.ora
PPS_DB_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = dg4msql)
)
(HS = OK) <– Tells Oracle this is a Heterogeneous Service
)
Step 6: Create the Database Link
Log in to your Oracle Database as a user with CREATE DATABASE LINK privilege.
CREATE DATABASE LINK dblink_to_sqlserver
CONNECT TO “sql_user” IDENTIFIED BY “sql_password”
USING ‘PPS_DB_LINK’;
Part 4: Troubleshooting Common “Gotchas”
Even with perfect instructions, environments differ. Here are the three specific errors we encountered during deployment and how we fixed them.
1. The “Cannot open database ‘dbo'” Error
Symptom: isql fails with [SQL Server]Cannot open database “dbo” requested by the login.
Cause: In odbc.ini, the parameter Database = dbo was used. dbo is a schema, not a database.
Fix: Change the parameter to the actual catalog name, e.g., Database = PPS_DB.
2. The ORA-28500 “Silent Failure”
Symptom: Running SELECT * FROM table@link fails with ORA-28500 and no detailed error message, even though isql works perfectly.
Cause: The Oracle Listener cannot find the ODBC driver libraries.
Fix: Ensure the ENVS line in listener.ora includes both /usr/lib64 (for the manager) and /opt/microsoft/msodbcsql18/lib64 (for the driver).
3. The File Location Trap
Symptom: Connection fails, acting as if the configuration file is blank.
Cause: We initially created initdg4msql.ora in $ORACLE_HOME/hs.
Fix: The gateway only looks in $ORACLE_HOME/hs/admin. Moving the file there resolved the issue immediately.
Conclusion
With this architecture in place, you now have a zero-cost, high-performance pipeline to SQL Server. You can query tables directly:
SQL
SELECT count(*) FROM “sales_data”@dblink_to_sqlserver;
This setup forms the foundation for our PL/SQL ETL pipelines, allowing us to orchestrate complex data warehouse loads without leaving the comfort of the Oracle environment.
Ahmed AbdelFattah Blog ! 