Integrando ORACLE com POSTGRESQL utilizando DataWrapper ORACLE FDW .

                      Mais uma do PreciseiPostei  para você!!

---

PROBLEMA:

PRECISEI integrar o banco de dados Postgresql com o SGBD Oracle SEM utilizar a extensão do Postgresql dblink…..

PARA SOLUCIONAR:

Utilizei o Data Wrapper Oracle FDW(ao invés do dblink do PostgreSQL).  Para a implementação do Data Wrapper, é necessário(a);

-PostgreSQL

-Oracle Client – Instant Client;

-Extensão Oracle-FDW.

Após estar em produção… POSTEI!!!

Então Vamos lá!

---

Instalação PostgreSQL

-PostgreSQL(server e Client); – (Não vou explicar a instalação do postgresql, para aqueles que desejarem instalar do source  recomendo o link https://www.postgresql.org/docs/9.5/static/installation.html)

---

Instalação do Oracle Client

Em nosso cenário temos a necessidade de instalarmos um cliente oracle para acessar as bases no SGBD Oracle. Para isso iremos baixar e instalar o ORACLE INSTANT CLIENT

     Oracle Instant Client habilita o desenvolvimento e implantação de Banco de Dados Oracle. É usado por diversas linguagens e ambientes incluindo Node.js,Python e PHP e também pode ser acessado por OCI, OCCI, JDBC, ODBC e Aplicações Pro C  ref:http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

De acordo com o White Paper da Oracle sobre o Instant Client Oracle, esse cliente pode ser utilizado com a mesma configuração para diversas fontes(JDBC, ODBC,…) sem alteração do ambiente (ORACLE_HOME por exemplo) ele tem alta performance e um tamanho reduzido. Para maiores detalhes acesse o material oficial em:   http://www.oracle.com/technetwork/database/features/oci/instant-client-wp-131479.pdf

Então…… Mão na massa

 

Teremos que baixar e instalar 2(dois) pacotes do Oracle Instant Client:

• oracle-instantclient-basic – (Pacote Básico)
• oracle-instantclient-devel –  (Pacote para Desenvolvimento)

 

Instalando oracle-instantclient-basic 

 

BAIXANDO e INSTALANDO o pacote oracle-instantclient-basic:

a) Acessar o site oficial Oracle para instalação do Oracle Instant Client:

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html 

(Versão 12.1 para Linux 64 bits compatível com gerenciador de pacotes RPM)

 

b) Clicar em Accept License Agreement para aceitar os termos:

 

 

c) Clicar no pacote para fazer download.:

oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

d) Utilizar SFTP ou SCP(recomendável programa Winscp) para copiar para o servidor o pacote baixado.

e) Após cópia para o servidor, Instalar o pacote copiado utilizando o usuário root

 

# rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

 

Instalando oracle-instantclient-devel

 

BAIXANDO e INSTALANDO o pacote oracle-instantclient-devel:

a) Acessar o site oficial Oracle para instalação do Oracle Instant Client:

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html

b) Clicar em Accept License Agreement para aceitar os termos:

 

 

c) Clicar no pacote para fazer download.:

oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

d) Utilizar SFTP ou SCP(recomendável programa Winscp) para copiar para o servidor o pacote baixado.

e) Após cópia para o servidor, Instalar o pacote copiado utilizando o usuário root

 

# rpm -ivh oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm

Fim da Instalação dos pacotes oracle instant client:

 

Agora vem uma situação que contradiz o white paper da Oracle que menciona o fato de não ser mais necessárias configurações como o ORACLE_HOME:

No ORACLE_HOME. The ORACLE_HOME requirement is gone. No more need for a full Oracle installation on every machine to access an Oracle database. (http://www.oracle.com/technetwork/database/features/oci/instant-client-wp-131479.pdf)

No nossa caso, para essa instalação funcionar,  foi necessária a instanciação da variável de ambiente ORACLE_HOME. Como (dessa vez) eu não quis discorrer sobre o assunto, segui com as configurações de ambiente.

CONFIGURANDO o Ambiente Oracle Client:

a) Crie o arquivo oracle_vars.sh utilizando o comando:

 

# vi /etc/profile.d/oraclevars.sh

b) Copie o texto abaixo para o aquivo oracle_vars.sh

ORACLE_HOME=/usr/lib/oracle/12.1/client64/

c)Feche o arquivo com o comando:

 

# :wq

d) Atualize as variáveis configuradas utilizando o comando:

 

# source /etc/profile.d/oraclevars.sh

e) Crie o arquivo oraclelib.conf utilizando o comando:

 

#vi /etc/ld.so.conf.d/oraclelib.conf

f) Copie o texto abaixo para o aquivo oraclelib.conf

/usr/lib/oracle/12.1/client64/lib

g) Feche o arquivo com o comando:

# :wq

h) Atualize as bibliotecas dinâmicas.

 

# ldconfig

i) Verifique o hostname do servidor que contém o PostgreSQL utilizando o comando:

# hostname -s

j) Verificar se o nome de hostname do servidor está associado ao ip local do servidor no arquivo /etc/hosts.

# cat /etc/hosts

 

O Nome destacado foi adicionado no arquivo /etc/hosts locgo depois do localhost.

l) Caso o hostname não esteja incluído no arquivo /etc/hosts, utilizar o comando:

#vi /etc/hosts

e incluir o nome resultante do comando hostname -s

 Esses foram os passos para Baixar, instalar e configurar o ambiente do Oracle Client.

---

Criando e configurando Data Wrapper Oracle FDW

 

O  Oracle-FDW (assim como o TDS-FDW utilizado no post Integrando MSSQL2000 com Postgresql utilizando TDS FDW ) são componentes que irão nos auxiliar a manipular o acesso a objetos remotos em base de dados especificas. Existe uma padronização  para manipular esses acessos denominada  Foreign Data Wrapper(FDW);

O FDW utiliza componentes do SQL MED que é um gerenciador de dados externo e é a parte do SQL Padrão que lida com a integração de dados armazenados em base de dados remotas. Existem 2(dois) componentes no SQL/MED o Foreign Table e o Data Link.

Existem diversas implementações de FDW, como pode ser vistas em
ref: https://wiki.postgresql.org/wiki/Foreign_data_wrappers#Foreign_Data_Wrappers

Em nosso cenário será instalada e criada uma extensão FDW para o PostgreSQL se conectar com bases em SGDBD Oracle:

• Extensão oracle_fdw

As implementações de FDW estão sob a forma de extensões do SGBD PostgreSQL e serão responsáveis por trazer dados de uma fonte de dados remota e retorná-la para um executor do PostqreSQL.  https://www.postgresql.org/docs/current/static/fdwhandler.html

Instalando a extensão Oracle-FDW

 

O oracle_fdw é uma extensão do PostgreSQL que provê Foreign Data Wrapper com acesso fácil e eficiente a base de dados Oracle.

Instalação do Foreign Data Wrapper Oracle-FDW

a) Baixar o pacote mais atual do oracle_fdw do repositório.

 

#wget https://github.com/laurenz/oracle_fdw/archive/master.zip

b) Descompactar arquivo baixado.

#unzip master

c)Entrar no diretório extraído

# cd oracle_fdw-master

IV) Executar o comando make e make install com a instrução PGXS=1 para habilitar a extensão de módulos como o oracle_fdw.

 

# make USE_PGXS=1

 

 

 

Caso o resultado do comando “make” for semelhante a imagem acima, deve-se executar o comando make install também com a instrução “USE PGXS=1” para habilitar a extensão de módulos como o tds fdw.

 

#make USE_PGXS=1 install

Se a saída do ultimo comando se assemelhar com:

 

 

 

Caso o resultado do comando for semelhante a imagem acima, a instalação está concluída.

 

Criando a extensão Oracle-FDW

 

Para a criação da extensão, podemos usar o Administrador e Designer para PostgreSQL pgAdmin , ou o front-end baseado em terminal para PostgreSQL->  psql 

 

Usando o pgAdmin (https://www.pgadmin.org/download/)

 

No pgAdmin, antes de criarmos a extensão Oracle-FDW precisamos habilitar as opções de Foreign Data Wrappers 

 

a) Primeiro precisamos criar uma conexão com o banco de dados que está rodando o postgreSQL

(IP DO SERVIDOR QUE ESTÁ EXECUTANDO O POSTGRESQL). Após a criação conecte-se ao banco remoto.

 

 

 

 

 

 

 

 

 

 

b) Depois selecione o Item “Arquivo(ou File)” do menu principal e em seguida clique no submenu “Opções(ou Option)”.

c) Em seguida selecione a opção Display(a esquerda) e marque os seguintes objetos: Foreign Data Wrappers, Foreign Servers e Foreign Tables (a direita).

 

 

 

 

 

 

 

 

 

 

 

Após essa seleção, essas opções ficam visíveis em seu pgAdmin.

Como pode-se observar, as opções  Foreign Data Wrapper, Foreign Table e Foreign Server foram habilitadas. Essas opções provêm do conceito de SQL MED(vide conceito acima). 

a) Crie a extensão do oracle_fw utilizando o editor de query do pgAdmin utilizando uma instrução SQL create extension:

create extension oracle_fdw;

 

 

 

 

 

 

 

Usando o front-end para PostgreSQL psql (MODO ALTERNATIVO AO PgAdmin)

 

a) Conectar ao banco utilizando psql

 

#psql -U postgres

II) Crie a extensão do tds_fdw criando uma instrução SQL;

CREATE EXTENSION oracle_fdw

---

Hora do TESTE!!!! Agora é a hora da Verdade!!! !

 

Para isso precisamos executar alguns passos:

• Criar um Servidor Estrangeiro (FOREIGN SERVER)
• Criar Mapeamento de Usuários
• Criar Tabela Estrangeira (FOREIGN TABLE)
• Verificação Visual

 

Todos os passos podemos executar no pgAdmin ou no psql

 

Usando o pgAdmin (https://www.pgadmin.org/download/)

 

a) Crie um Servidor Estrangeiro utilizando o SQL Editor do pgAdmin com as seguintes instruções:

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver ‘//192.168.2.2/XE’);

 

 

 

 

b) Criar uma instrução sql para mapeamos o usuário postgres com o usuario do banco ORACLE

CREATE USER MAPPING FOR postgres SERVER oradb
OPTIONS (user ‘ususario_oracle’, password ‘senha_usuario_oracle’);

 

 

 

 

c)Fazer uma instrução SQL para criar a tabela foreign contendo os dados da tabela do banco do ORACLE.

CREATE FOREIGN TABLE oratab (
NOME text NULL,
CPF text NULL,
SEXO char NULL
) SERVER oradb OPTIONS (table ‘(SELECT * FROM TABELA_NO_ORACLE)’);

 

 

 

 

 

 

d) Terminada a criação das instruções sql vamos verificar no pgAdmin se os objetos foram criados.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

e) Para verificar se funcionou, clique com o botão direto em cima da Foreign Tables “tabela_fdw” e em seguida selecionar: “View Data ”em seguida clicar em “View all Rows”

 

 

 

 

 

Se o resultado listou o conteúdo da tabela que se encontra no SGBD ORACLE, então oracle_fdw funcionou e trouxe os dados para o PostgreSQL.

---

 

Usando o front-end para PostgreSQL psql (MODO ALTERNATIVO AO PgAdmin)

 

a) Crie um Servidor Estrangeiro utilizando o pgsql

CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver ‘//IP_SERVIDOR_ORACLE/INSTANCIA’);

 

b) Para confirmar a criação do Servidor Foreign basta digitar:

\des

 

 

 

 

c) Criar uma instrução sql para mapeamos o usuário postgres com o usuário do banco ORACLE:

CREATE USER MAPPING FOR postgres SERVER oradb

OPTIONS (user ‘ususario_oracle’, password ‘senha_usuario_oracle’);

 

 

d) Verificar a criação do usermap:

\deu

 

 

 

 

e) Fazer uma instrução sql para criar a tabela foreign chamada “tabela_psql_fdw” através de uma consulta aos dados da tabela “dbo.tabela_teste” que encontra-se na base de dados “teste” do MSSQL2000:

CREATE FOREIGN TABLE oratab (

NOME text NULL,

CPF text NULL,

SEXO char NULL

) SERVER oradb OPTIONS (table ‘(SELECT * FROM TABELA_NO_ORACLE)’);

 

 

 

f) Verificar a criação da Foreign Table:

\det

 

 

 

a)Para saber se funcionou execute uma query na tabela foreign criada e verificar o resultado.

select * from public.oratb

b) Se o resultado listou o conteúdo da tabela que se encontra no SGBD ORACLE, então oracle_fdw funcionou e trouxe os dados para o PostgreSQL.

 

 

 

 

 

---

 

É isso ai , mais uma do PreciseiPostei  . Espero ter ajudado!!

 

---

Configuração do sistema utilizado:

Sistema: Linux

Distribuição: Oracle Linux 6.4

Banco de Dados: Postgresql 9.5

Banco de Dados: Oracle XE

REFERÊNCIAS:

http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

https://github.com/tds-fdw/tds_fdw/blob/master/ForeignTableCreation.md

https://www.postgresql.org/docs/9.3/static/sql-createserver.html

https://www.postgresql.org/docs/current/static/fdwhandler.html

https://wiki.postgresql.org/wiki/Foreign_data_wrappers