DBlink Oracle to SQL Server – …::: César DBA :::…

Pessoal estava com a necessidade de consumir algumas informações do SQL Server em um banco Oracle, desta forma fiz um procedimento simples para quem precisar.

Instalando os pacotes necessários.

yum install unixODBC unixODBC-devel freetds -y

ShellScript

Configurando entrada do TNSNAMES, lembrando que o IP usado no TNSNAMES é do proprio Oracle database.

TNSNAMES
MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=lab11g.localdomain)(PORT=1524))
(CONNECT_DATA=(SID=MSSQL))
(HS=OK)
)

ShellScript

Configurando listener.

LISTENER
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = lab11g.localdomain)(PORT = 1521))
)
)
 
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora11g)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
(SID_NAME = ora11g)
)
(SID_DESC=
(SID_NAME=MSSQL) # ALIAS USADO EM TODA A CONFIGURACAO
(ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
(PROGRAM=dg4odbc)
)
 
)
 
ADR_BASE_LISTENER = /u01/app/oracle
 
lsnrctl stop
lsnrctl start

ShellScript

Testando a instalação do ODBC.

[root@lab11g ~]# odbcinst -j
unixODBC 2.3.1
DRIVERS…………: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size…….: 8
SQLLEN Size……..: 8
SQLSETPOSIROW Size.: 8

ShellScript

Criando o init no Oracle Heterogeneous Services (HS).

vi $ORACLE_HOME/hs/admin/initMSSQL.ora
 
HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini #LOCAL DO ARQUIVO ODBC DE CONFIGURACAO

ShellScript

Criando o arquivo do ODBC com as configurações.

vi /etc/odbc.ini
 
[ODBC Data Sources]
MSSQL = MSSQL Server
 
[MSSQL]
# Reference driver from the “/etc/odbcinst.ini” file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the “/etc/odbcinst.ini” file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 192.168.0.31
Database = LAB
Port = 1433
TDS_Version = 7.2
 
[Default]
Driver = /usr/lib64/libtdsodbc.so.0

ShellScript

Crie o usuário com acesso no SQL Server com permissão de leitura, o meu foi o teste.

USE [master]
GO
CREATE LOGIN [teste] WITH PASSWORD=N'teste', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [rh]
GO
CREATE USER [teste] FOR LOGIN [teste] WITH DEFAULT_SCHEMA=[dbo]
go
EXEC sp_addrolemember 'db_datareader', 'teste'  
go

SQL

Testando o acesso ao banco no SQL Server.

[root@lab11g ~]# osql -S MSSQL -U teste -P teste
 
checking shared odbc libraries linked to isql for default directories…
strings: ”: No such file
trying /tmp/sql … no
trying /tmp/sql … no
trying /etc … OK
checking odbc.ini files
reading /root/.odbc.ini
[MSSQL] found in /root/.odbc.ini
found this section:
[MSSQL]
# Reference driver from the “/etc/odbcinst.ini” file.
# Driver = FreeTDS
# Or use a direct driver reference, rather than reference
# one in the “/etc/odbcinst.ini” file.
Driver=/usr/lib64/libtdsodbc.so.0
Description = MSSQL Server
Trace = No
Server = 192.168.44.33
Database = CENTER_MIDIA
Port = 1433
TDS_Version = 7.2
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
 
looking for driver for DSN [MSSQL] in /root/.odbc.ini
found driver line: ” Driver=/usr/lib64/libtdsodbc.so.0″
driver “/usr/lib64/libtdsodbc.so.0” found for [MSSQL] in .odbc.ini
found driver named “/usr/lib64/libtdsodbc.so.0”
/usr/lib64/libtdsodbc.so.0 is an executable file
“Server” found, not using freetds.conf
Server is “192.168.44.33”
looking up hostname for ip address 192.168.44.33
osql: warning: no DNS hostname found for “192.168.44.33”
 
Configuration looks OK. Connection details:
 
DSN: MSSQL
odbc.ini: /root/.odbc.ini
Driver: /usr/lib64/libtdsodbc.so.0
Server hostname: 192.168.44.33
Address: 192.168.44.33

Attempting connection as teste …
+ isql MSSQL teste teste -v
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>quit

ShellScript

Outro teste de acesso ao SQL Server.

[root@lab11g ~]# isql MSSQL teste 'teste' -v
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL>quit

ShellScript

Criando o dblink no Oracle.

create public database link MSSQL connect to "teste" identified by "teste" using 'MSSQL';

SQL

Acessando a tabela do SQL Server pelo Oracle.

SQL> DESC TB_BAIRRO@MSSQL;
Name Null? Type
—————————————– ——– —————————-
bairro_codigo NUMBER(10)
cidade_codigo NVARCHAR2(50)
bairro_descricao NVARCHAR2(50)
 
SQL>

SQL