Reading MSSQL DB data from Oracle 19c database using DB link & ODBC Drivers :
****************************************************************************
List of steps:
**************
1. Root - Install Microsoft SQL server ODBC drivers.
sudo ACCEPT_EULA=Y yum install -y msodbcsql17
2. Root - Create ODBC entry in /etc/odbc.ini file pointing to remote.
3. Oracle - create init parameter file initora_19c_DB_MSSQL. Ora
4. Oracle - register SID in listener.ora.
5. Oracle- Create entry in tnsnames.ora pointing to SID.
6. Oracle - create DB link in oracle database.
7. Oracle- Optionally - Create synonym.
8. Install Microsoft SQL server ODBC drivers
How to install the Microsoft ODBC Driver for SQL Server on Linux. It also includes instructions for the optional command-line tools for SQL Server and the unixODBC development headers.
Here we are taking up the ODBC 17 drivers. Please go to the below link and ask to unix team to install the driver on the server.
https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver17&tabs=redhat18-install%2Credhat17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline
Once installed the package they need to run the ODBC driver using below command.
sudo ACCEPT_EULA=Y yum install -y msodbcsql17
9. Create ODBC entry in /etc/odbc.ini file pointing to remote
To create an ODBC entry in /etc/odbc.ini that points to a remote database, you need to define a Data Source Name (DSN) within the file. This DSN will contain the necessary parameters to connect to your remote database.
Here is an example of an odbc.ini entry for a remote database.
[DS_MSSQL]
Description = MSSQL
Driver = /usr/lib64/libmsodbcsql-17.so
Server = 20.20.30.30.\MSSQL_instance1
Port = 57500
Database = MSSQL_DB
UID = user_ora2mssql
Explanation of the parameters:
• [DS_MSSQL]: This is the DSN name. You can choose any descriptive name for your connection.
• Description: A brief description of the data source.
• Driver: The name of the ODBC driver you have installed for your specific database SQL Server. Ensure the corresponding driver is correctly installed and configured in /etc/odbcinst.ini.
• Server : The hostname or IP address of your remote database server.
• Port: The port number on which the database server is listening for SQL Server.
• Database: The name of the specific database you want to connect to on the remote server.
• UID: The username for authenticating with the database.
10. Create init parameter file initora_19c_DB_MSSQL. Ora
To create a Heterogeneous Services (HS) initialization parameter file for connecting to a SQL Server database named ora_19c_DB, you would create a text file named initora_19c_DB_MSSQL.ora and place it in the $ORACLE_HOME/hs/admin directory. This file contains parameters specific to the gateway, not the main Oracle database.
The file should be located in the $ORACLE_HOME/hs/admin directory.
Below is a sample configuration. The specific parameters required will depend on your environment and the specific Oracle Database Gateway version you are using.
vi $ORACLE_HOME/hs/admin/initora_19c_DB_MSSQL.ora
# needed for the database gateway for odbc
# HS init parameters
HS_FDS_CONNECT_INFO = DS_MSSQL
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so
HS_LANGUAGE= AL16UTF16
HS_FDS_REMOTE_DB_CHARSET=AMERICAN_AMERICA.AL16UTF16
HS_NLS_NCHAR=UCS2
#
# ODBS SPECIFIC ENVIRONMEANT VARIABLES
#
set ODBCINI=/etc/odbc.ini
11. register SID in listener.ora.
Add the HS SID listener in listener file To register an Oracle System Identifier (SID) for a database instance or a Heterogeneous Services (HS) agent in the listener.ora file, you need to perform static registration. This involves manually adding a SID_DESC entry to the SID_LIST section of the file.
The listener. Ora file is typically located in the $ORACLE_HOME/network/admin directory
Add a SID_DESC entry:-
Within the SID_LIST_<listener_name> section (by default SID_LIST_LISTENER), add a new SID_DESC block. This block specifies the details for the service you want to register.For an Oracle Database Instance
==================================
LISTENER_ora_19c_DB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 30.30.30.50)(PORT = 1587))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1587))
)
)
SID_LIST_LISTENER_ora_19c_DB =
(SID_LIST =
(SID_DESC =
(SID_NAME = ora_19c_DB)
(ORACLE_HOME = /oracle/ora_19c_DB/12201)
)
(SID_DESC =
(SID_NAME = ora_19c_DB_MSSQL)
(ORACLE_HOME = /oracle/ora_19c_DB/12201)
(PROGRAM = /oracle/ora_19c_DB/12201/bin/dg4odbc)
)
)
12. Create entry in tnsnames.ora pointing to SID.
To create an entry in the tnsnames.ora file pointing to an Oracle SID, follow these steps:
• Locate the tnsnames.ora file: This file is typically found in the $ORACLE_HOME/network/admin directory. If the TNS_ADMIN environment variable is set, it will point to the directory containing the tnsnames.ora file.
• Open the tnsnames.ora file: Use a text editor to open the tnsnames.ora file.
• To create an entry in the tnsnames.ora file for an Oracle Heterogeneous Services (HS) SID, you need to manually edit the file to include a specific connect descriptor. The HS=OK parameter is crucial in the CONNECT_DATA section to direct Oracle Net to use Heterogeneous Services.
TNS_MSSQL.WORLD =
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = 30.30.30.50)
(PORT = 1587)
)
)
(CONNECT_DATA =
(SID =ora_19c_DB_MSSQL)
)
(HS=OK)
)
• (HS = OK): This mandatory parameter tells Oracle Net to connect to a non-Oracle system using Heterogeneous Services.
13. Oracle - create DB link in oracle database.
• Permissions:
Ensure the user creating the database link has the necessary CREATE DATABASE LINK privilege. The remote_user in the CONNECT TO clause must exist in the remote database and have privileges to access the objects you intend to use via the link.
• Public vs. Private:
Database links can be PUBLIC (accessible by all users) or private (owned by the user who created it). To create a public link, add the PUBLIC keyword after CREATE.
• GLOBAL_NAMES parameter:
The GLOBAL_NAMES initialization parameter can impact database link behavior. If set to TRUE, the database link name must match the global name of the remote database. Consider setting it to FALSE if strict name matching is not desired.
Note :- Before you creating the DB link they need to be create the SQL server authentication user in SQL server then only you make connection between from Oracle to MSSQL.
Create SQL Server Authentication User:-
• In SQL Server Management Studio (SSMS):
o Go to Security → Logins → New Login.
o Choose SQL Server Authentication (not Windows Authentication).
o Set a strong password and ensure Enforce password policy is configured as needed.
o Map the login to the required database and assign appropriate roles (e.g., db_datareader.
Syntax :- CREATE DATABASE LINK my_remote_link CONNECT TO remote_user IDENTIFIED BY remote_password USING 'REMOTE_DB_ALIAS';
Example :- create public database link mssql_dblink6 connect to "user_ora2mssql" identified by "" using 'TNS_MSSQL';
Select count(*) from "view_test"@mssql_dblink6;
14. Optionally - Create synonym.
To create a synonym for a remote object accessed through a database link in Oracle.
Once the database link is established, you can create a synonym for a specific object (table, view, sequence, etc.) in the remote database.
CREATE SYNONYM synonym_name FOR remote_table_name@db_link_n
Example :- Create public synonym mssqlview For view_test@mssql_dblink6;
After creating the synonym, you can query the remote table using the synonym name as if it were a local object:
Select count(*) from mssqlview.
This will read the Data from MSSQL from Oracle DB
No comments:
Post a Comment