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

O Heterogeneous Services (HS) é um componente integrado no servidor de banco de dados Oracle que fornece a tecnologia genérica para acessar sistemas através do gateway transparente usando uma conectividade ODBC.

Iremos utilizar uma tecnologia nativa do Oracle database o Heterogeneous Services (HS) para criação de um dblink com PostgreSQL.

Databases PostgreSQL: bancos que iremos usar: ODBC e COPGEO.

192.168.0.1 - Servidor Origem Oracle database
192.168.0.2 - Servidor destino PostgreSQL

ShellScript

Criar o usuario de acesso no PostgreSQL:

CREATE USER dblink PASSWORD '123456pE';
ALTER USER dblink WITH SUPERUSER;
 
SHOW password_encryption;
SET password_encryption  = 'md5';
SHOW password_encryption;
ALTER USER "dblink" with password '123456pE';

SQL

Instalação do repositorio do PostgreSQL

https://www.postgresql.org/download/
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

ShellScript

Instalação dos pacotes necessários:

yum install unixODBC unixODBC-devel freetds -y
yum install -y postgresql-odbc

ShellScript

Configurar ODBC:

[root@hml ~]# cat /etc/odbc.ini
 
[OBSERVATORIO]
Description = OBSERVATORIO
Driver = /usr/lib64/psqlodbcw.so
ServerName = 192.168.0.2
Username = dblink
Password = 123456pE
Port = 5433
Database = observatorio
[Default]
Driver = /usr/lib64/libodbcpsqlS.so
 
 
[COPGEO]
Description = COPGEO
Driver = /usr/lib64/psqlodbcw.so
ServerName = 192.168.0.2
Username = dblink
Password = 123456pE
Port = 5433
Database = copgeo
[Default]
Driver = /usr/lib64/libodbcpsqlS.so

ShellScript

Testar acesso via ODBC:

isql -v OBSERVATORIO
isql -v COPGEO

ShellScript

Configurar Heterogeneous Services (HS) Oracle database.:

su - oracle
cd $ORACLE_HOME/hs/admin
 
[oracle@hml admin]$ cat initCOPGEO.ora
HS_FDS_CONNECT_INFO = COPGEO
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = en_US.UTF-8
 
[oracle@hml admin]$ cat initOBSERVATORIO.ora
HS_FDS_CONNECT_INFO = OBSERVATORIO
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbc.so
HS_LANGUAGE = en_US.UTF-8

ShellScript

Configurar listener e tnsnames, lembrando que o IP usado no TNSNAMES é do proprio Oracle database:

vi $ORACLE_HOME/network/admin/listener.ora
 
 
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hml)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
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=COPGEO)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
          (PROGRAM=dg4odbc)
        )
 
          (SID_DESC=
          (SID_NAME=OBSERVATORIO)
          (ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1)
          (PROGRAM=dg4odbc)
        )
 
  )

ShellScript

vi $ORACLE_HOME/network/admin/tnsnames.ora
 
OBSERVATORIO =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.0.1)(PORT=1521)) 
      (CONNECT_DATA=(SID=OBSERVATORIO))
      (HS=OK)
    )
 
COPGEO =
   (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.0.1)(PORT=1521))
      (CONNECT_DATA=(SID=COPGEO))
      (HS=OK)
    )
 
[oracle@hml ~]$ lsnrctl reload
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-AUG-2022 11:22:24
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-hml)(PORT=1521)))
The command completed successfully
 
 
[oracle@hml ~]$ lsnrctl status
 
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-AUG-2022 11:22:47
 
Copyright (c) 1991, 2013, Oracle.  All rights reserved.
 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl-hml)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                22-DEC-2021 09:03:54
Uptime                    240 days 2 hr. 18 min. 53 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/orcl-hml/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hml)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "COPGEO" has 1 instance(s).
  Instance "COPGEO", status UNKNOWN, has 1 handler(s) for this service...
Service "OBSERVATORIO" has 1 instance(s).
  Instance "OBSERVATORIO", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 2 instance(s).
  Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
  Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB" has 1 instance(s).
  Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

ShellScript

Testar tnsnames dos bancos OBSERVATORIO e COPGEO:

[oracle@orcl-hml ~]$ tnsping COPGEO
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.0.1)(PORT=1521)) (CONNECT_DATA=(SID=COPGEO)) (HS=OK))
OK (80 msec)
 
[oracle@hml ~]$ tnsping OBSERVATORIO
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.0.1)(PORT=1521)) (CONNECT_DATA=(SID=OBSERVATORIO)) (HS=OK))
OK (0 msec)
[oracle@hml ~]$

ShellScript

Criar dblinks para cada banco de dados:

create public database link OBSERVATORIO connect to "dblink" identified by "123456pE" using 'OBSERVATORIO';
create public database link COPGEO connect to "dblink" identified by "123456pE" using 'COPGEO';

SQL

Testar acesso dos dblinks:

select * from "linha_ibge"@COPGEO;
select * from "input"@OBSERVATORIO;
select * from "amostra"."terreno_isolado"@COPGEO;

SQL

Leave a Reply Cancel reply