CentOS Question posted in
The official documentation can be found here.

Oracle XE dg4odbc: connecting to SQL Server doesn't work – CentOS

I can’t create a DBLink from Oracle XE 18c to SQL Server 2017 Express!

I have an instance of Oracle XE 18c on my server centos 7.6 and an instance of SQL Server 2017 Express, both working properly.

I’d like to create an Oracle dblink to the SQL Server.

I can connect to SQL Server remotely thanks to this firewall rule

firewall-cmd --zone=public --add-port=1433/tcp --permanent
firewall-cmd --reload

I’ve installed Microsoft odbc tools

curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
yum remove unixODBC-utf16 unixODBC-utf16-devel
yum install -y mssql-tools unixODBC-devel
ACCEPT_EULA=Y yum install msodbcsql

cd /usr/lib64
sudo ln -s libodbccr.so.2.0.0   libodbccr.so.1
sudo ln -s libodbcinst.so.2.0.0 libodbcinst.so.1
sudo ln -s libodbc.so.2.0.0     libodbc.so.1

created a DB, a ODBC DSN and verified that all works well

sqlcmd -D -S roberto_dsn -U sa -P password -d robertodb -Q "select *  FROM dbo.Person"

I’ve configured Oracle Instance with the following code:

nano /opt/oracle/product/18c/dbhomeXE/hs/admin/initroberto.ora

HS_FDS_CONNECT_INFO = roberto_dsn
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI=/etc/odbc.ini

nano /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
aggiungere
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=roberto)
     (ORACLE_HOME=/opt/oracle/product/18c/dbhomeXE)
     (PROGRAM=dg4odbc)
         (envs="LD_LIBRARY_PATH=/usr/lib64:/opt/oracle/product/18c/dbhomeXE/lib")
   )
 )

nano /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
#added
ROBERTO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))
    (CONNECT_DATA =
      (SID_NAME = roberto)
    )
        (HS=OK)
  )

I restarted the listener correctly

lsnrctl stop
lsnrctl start

tnsping roberto
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521)) (CONNECT_DATA = (SID_NAME = roberto)) (HS=OK))
OK (0 msec)

Finally I created the DBLink

sqlplus / as sysdba
 alter session set container = xepdb1;

 CREATE PUBLIC DATABASE LINK ROBERTO CONNECT TO
"sa" IDENTIFIED BY "password" using 'roberto';

First Error I receive is the following:

sqlplus sa/password@roberto
ERROR:
ORA-01017: Invalid Username/Password; Logon Denied

But also with the DBLink:

select * from dual@roberto;
ORA-28546: Connection initialization failed, probable Net8 admin error
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.164)(PORT=1521)))(CONNECT_DATA=(SID_NAME=roberto)))
ORA-02063: preceding 2 lines from roberto
28546. 0000 -  "connection initialization failed, probable Net8 admin error"
*Cause:    A failure occurred during initialization of a network connection
           from the Oracle server to a second process:  The connection
           was completed but a disconnect occurred while trying to
           perform protocol-specific initialization, usually due to
           use of different network protocols by opposite sides
           of the connection.  This usually is caused by incorrect
           Net8 administrative setup for database links or external
           procedure calls.   The most frequent specific causes are:
           --  Database link setup for an Oracle-to-Oracle connection
           instead connects to a Heterogeneous Services agent
           or an external procedure agent.
           --  Database link setup for a Heterogeneous Services connection
           instead connects directly to an Oracle server.
           --  The extproc_connection_data definition in tnsnames.ora
           connects to an Oracle instance instead of an external
           procedure agent.
           --  Connect data for a Heterogeneous Services database link,
           usually defined in tnsnames.ora, does not specify (HS=).
           --  Connect data for an Oracle-to-Oracle database link,
           usually defined in tnsnames.ora, specifies (HS=).
*Action:   Check Net8 administration in the following ways
           --  When using TNSNAMES.ORA or an Oracle Names server, make sure
           that the connection from the ORACLE server uses the correct
           service name or SID.
           --  Check LISTENER.ORA on the connection end point's host machine
           to assure that this service name or SID connects to the
           correct program.
           --  Confirm in TNSNAMES.ORA or the equivalent service definition
           that service 'extproc_connection_data' does NOT contain
           (HS=), or that the service definition used by a
           Heterogeneous Services database link DOES contain (HS=).

Any suggestions?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Finally I've solved! I linked microsoft libraries into /usr/lib64

    cd /usr/lib64
sudo ln -s /opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2 .
sudo ln -s /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1 .

    Also I changed my added script in tnsnames.ora replacing SID_NAME with SID

    nano /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
#added
ROBERTO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.164)(PORT = 1521))
    (CONNECT_DATA =
      (SID = roberto)
    )
        (HS=OK)
  )

    I restarted the db and now:

    select*from dual@roberto;

    works!


  2. 0

    Let’s try:

    firewall-cmd --zone=internal --add-port=1433/tcp --permanent
firewall-cmd --reload

    I’m not a windows guru at all (I’m a Linux user), but I suppose you may need to enable also the internal zone for local use, in your case.. (you state that all the services/stuff are running on the same server)
    Hope useful, but It’s an attempt.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search