DBlink Oracle to SQL Server

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

  • TNSNAMES

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

  • 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

  • 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

  • Criando o init no Oracle

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

  • 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

  • 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

  • Outro teste de acesso ao SQL Server

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

 

  • Crie o usuário com acesso no SQL Server e suas permissões, o meu foi o teste.
  • Criando o dblink no Oracle

create public database link MSSQL connect to “teste” identified by “teste” using ‘MSSQL’;

  • 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>

 

Anúncios