DBLink Oracle para MYSQL – …::: César DBA :::…

Iremos utilizar uma tecnologia nativa do Oracle database o Heterogeneous Services (HS), construindo acesso via dblink para banco no MYSQL.

Databases:

10.16.0.11 - Banco de Origem Oracle database
10.16.2.84 - Banco de destino Mysql

ShellScript

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

Usuario: USR_CONSULTA
Snhea:   P!!2012300

ShellScript

Use um repositório da Oracle/EPEL para baixar os pacotes necessarios:

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

Download do driver odbc para MYSQL:

http://dev.mysql.com/downloads/connector/odbc

Instalação do driver ODBC para MYSQL:

[root@olaria ~]# rpm -ivh mysql-connector-odbc-8.0.29-1.el7.x86_64.rpm
warning: mysql-connector-odbc-8.0.29-1.el7.x86_64.rpm: Cabeçalho V4 RSA/SHA256 Signature, ID da chave 3a79bd29: NOKEY
Preparing...                         ################################# [100%]
Updating / installing...
   1:mysql-connector-odbc-8.0.29-1.el7################################# [100%]
Success: Usage count is 1
Success: Usage count is 1

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 banco no MYSQL que iremos acessar via dblink:

[oracle@olaria ~]$ cd $ORACLE_HOME/hs/admin/
[oracle@olaria admin]$ cat initMYSQL.ora
HS_FDS_CONNECT_INFO = MYSQL
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmyodbc8a.so
HS_FDS_FETCH_ROWS = 1
HS_FDS_SUPPORT_STATISTICS=FALSE
HS_LANGUAGE=american_america.we8iso8859P1
HS_NLS_NCHAR=UCS2

ShellScript

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

[root@olaria yum.repos.d]# cat /etc/odbc.ini
 
[MYSQL]
Description = MYSQL
Driver = /usr/lib64/libmyodbc8a.so
Server = 10.16.2.84
User = USR_CONSULTA
Password = 12398546
Port = 3306
Database = financas

ShellScript

Ajustar o tnsnames.ora com as entradas de acesso ao MYSQL 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.
 
 
MYSQL =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=10.16.0.11)(PORT=1521))
      (CONNECT_DATA=(SID=MYSQL))
      (HS=OK)
    )

ShellScript

Ajustar o listener.ora com as entradas de acesso ao banco do MYSQL 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=MYSQL)
          (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 FINANCAS -U USR_CONSULTA -P 12398546
 
Attempting connection as dblinkdev ...
+ isql FINANCAS USR_CONSULTA 12398546 -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

SQL

Criar os dblinks e realizar os testes de acesso:

create public database link financas connect to "USR_CONSULTA" identified by "12398546" using 'MYSQL';

SQL

Consultas de testes:

	
select * from das@FINANCAS;

SQL