Я не могу создать DBLink из Oracle XE 18c в SQL Server 2017 Express!
У меня есть экземпляр Oracle XE 18c на моем сервере centos 7.6 и экземпляр SQL Server 2017 Express, оба работают правильно.
Я хотел бы создать dblink Oracle для SQL Server.
Я могу удаленно подключаться к SQL Server благодаря этому правилу брандмауэра
firewall-cmd --zone=public --add-port=1433/tcp --permanent
firewall-cmd --reload
Я установил инструменты Microsoft odbc
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
создал БД, ODBC DSN и проверил, что все работает хорошо
sqlcmd -D -S roberto_dsn -U sa -P password -d robertodb -Q "select * FROM dbo.Person"
Я настроил экземпляр Oracle со следующим кодом:
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)
)
Я перезапустил прослушиватель правильно
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)
Наконец я создал DBLink
sqlplus / as sysdba
alter session set container = xepdb1;
CREATE PUBLIC DATABASE LINK ROBERTO CONNECT TO
"sa" IDENTIFIED BY "password" using 'roberto';
Первая ошибка, которую я получаю, следующая:
sqlplus sa/password@roberto
ERROR:
ORA-01017: Invalid Username/Password; Logon Denied
Но также и с 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=).
Какие-либо предложения?
