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
http://www.oracle.com/technetwork/database/features/oci/instant-client-wp-131479.pdf
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
Uma descrição passo-a-passo sensacional. Muito obrigado !!
Uma pequena correção: na seção “CONFIGURANDO o Ambiente Oracle Client” o ponto f) deve ser alterado por “Copie o texto abaixo para o aquivo oraclelib.conf”
Saudações!
Muito obrigado, alteração realizada.