cesardbaLeave a Comment on Criando DBlink Oracle para três bancos em SQL Server

Criando DBlink Oracle para três bancos em SQL Server

Construindo acesso via dblink para três bancos SQL Server.

Iremos utilizar uma tecnologia nativa do Oracle database o Heterogeneous Services (HS)

Databases.

10.16.0.11 - Banco de Origem Oracle database
10.16.0.15 - Banco de destino SQL Server
ShellScript

Criar um usuário com as permissões necessárias de leitura no ambiente SQL Server:

Usuário: USR_CONSULTA
Senha:   P!!2012300
ShellScript

Use um repositório da Oracle/EPEL para baixar os pacotes necessários:

### ORACLE LINUX ###
[root@instance-20220613-1754 yum.repos.d]# cat oracle-linux-ol7.repo
[ol7_latest]
name=Oracle Linux $releasever Latest ($basearch)
baseurl=https://yum.oracle.com/repo/OracleLinux/OL7/latest/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
#### EPEL ####
# cat /etc/yum.repos.d/epel-yum-ol7.repo
[ol7_epel]
name=Oracle Linux $releasever EPEL ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

Instalando os pacotes necessários:

yum clean all
yum repolist
yum install unixODBC unixODBC-devel freetds -y
ShellScript

Validação da instalação do ODBC:

[root@olaria ~]# odbcinst -j
unixODBC 2.3.7
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
[root@olaria ~]#
ShellScript

Criando o init de configuração do Heterogeneous Services no Oracle referente aos três databases do SQL Server que iremos acessar via dblink:

Nome dos databases que iremos acessar no SQL Server: AEQUUS,FINANCIAL,CONTABILIS

[oracle@olaria ~]$ cd $ORACLE_HOME/hs/admin/
 
[oracle@olaria admin]$ cat initAEQUUS.ora
HS_FDS_CONNECT_INFO = AEQUUS
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini
 
[oracle@olaria admin]$ cat initFINANCIAL.ora
HS_FDS_CONNECT_INFO = FINANCIAL
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini
 
[oracle@olaria admin]$ cat initCONTABILIS.ora
HS_FDS_CONNECT_INFO = CONTABILIS
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so # LIB
set ODBCINI=/etc/odbc.ini
ShellScript

Criar caso não exista ou editar o arquivo de configuração do ODBC que instalamos anteriormente, cada database dever ter uma entrada para conexão:

[root@olaria yum.repos.d]# cat /etc/odbc.ini
 
[DBC Data Sources]
AEQUUS = MSSQL Server
 
[AEQUUS]
# 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          = 10.16.0.15
Database        = AEQUUS
Port            = 1433
TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
DisguiseGuid = Yes
text size = 64512
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
 
 
[ODBC Data Sources]
CONTABILIS = MSSQL Server
 
[CONTABILIS]
# 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          = 10.16.0.15
Database        = CONTABILIS
Port            = 1433
TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
text size = 64512
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
 
 
[ODBC Data Sources]
FINANCIAL = MSSQL Server
 
[FINANCIAL]
# 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          = 10.16.0.15
Database        = FINANCIAL
Port            = 1433
TDS_Version     = 7.4 # >= MSSQL2012 = 7.4, MSSQL2008= 7.3, MSSQL2005=7.2, 8.0
VarMaxAsLong = Yes
text size = 64512
#QuotedId=YES
#AnsiNPW=YES
#VarMaxAsLong=YES
#client charset = UTF-8
ShellScript

Ajustar o tnsnames.ora com as entradas de acesso aos três databases do SQL Server que iremos usar no dblink, lembrando que o IP usado no TNSNAMES é do proprio Oracle database:

[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
AEQUUS =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
      (CONNECT_DATA=(SID=AEQUUS))
      (HS=OK)
    )
 
CONTABILIS =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
      (CONNECT_DATA=(SID=CONTABILIS))
      (HS=OK)
    )
 
FINANCIAL =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
      (CONNECT_DATA=(SID=FINANCIAL))
      (HS=OK)
    )
ShellScript

Ajustar o listener.ora com as entradas de acesso aos três databases do SQL Server que iremos usar no dblink:

[oracle@olaria db_1]$ cat $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = olaria)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
DEFAULT_SERVICE_LISTENER=orcl
ADR_BASE_LISTENER = /u01/app/oracle
 
 
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (SID_NAME = ORCL)
    )
        (SID_DESC=
          (SID_NAME=AEQUUS)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
          (PROGRAM=dg4odbc)
        )
 
        (SID_DESC=
          (SID_NAME=CONTABILIS)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
          (PROGRAM=dg4odbc)
        )
 
        (SID_DESC=
          (SID_NAME=FINANCIAL)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
          (PROGRAM=dg4odbc)
        )
 
  )
ShellScript

Recarregar as configurações do listener.ora:

[oracle@olaria db_1]$ lsnrctl reload
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JUN-2022 10:16:12
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=olaria)(PORT=1521)))
The command completed successfully
ShellScript

Testar o acesso via odba pelo utilitário osql:

osql -S AEQUUS -U USR_CONSULTA -P 12398546
 
Attempting connection as dblinkdev ...
+ isql AEQUUS USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
ShellScript
osql -S CONTABILIS -U USR_CONSULTA -P 12398546
 
Attempting connection as dblinkdev ...
+ isql CONTABILIS USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
ShellScript
osql -S FINANCIAL -U USR_CONSULTA -P 12398546
 
Attempting connection as dblinkdev ...
+ isql FINANCIAL USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>
ShellScript

Criar os dblinks e realizar os testes de acesso:

create public database link AEQUUS connect to "USR_CONSULTA" identified by "12398546" using 'AEQUUS';
create public database link CONTABILIS connect to "USR_CONSULTA" identified by "12398546" using 'CONTABILIS';
create public database link FINANCIALARACAJU connect to "USR_CONSULTA" identified by "12398546" using 'FINANCIALARACAJU';
SQL

Consultas de testes:

select "codigo","nome" from IMPACOES@AEQUUS;
select * from ALVARA@CONTABILIS;
select "nu_cnpj","sq_produto","nm_produto" from PRODUTO@FINANCIAL;
SQL

Obs: Se algum database no SQL Server possua tabelas com o tipo de dados varchar(max), devemos criar views para consumir esses dados, pois o tipo de dados varchar(max) é problematico com o dblink, eu não consegui contornar este problema, desta forma criei uma view, veja o exemplo abaixo:

CREATE VIEW VEW_IMPACOES
AS 
SELECT codigo,CONVERT(VARCHAR(2000),nome) nome FROM IMPACOES;
SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

search previous next tag category expand menu location phone mail time cart zoom edit close