Prévia do material em texto
Sumário
Conteúdo sobre Novidades
25 – Oracle 12c: Conheça a cláusula pattern matching
[ Tércio Costa ]
14 – Trabalhando com o PostgreSQL: implantação do banco de dados
[ Bruno de Oliveira Machado ]
Conteúdo sobre Novidades
06 – SQL Server 2016: Conheça as novidades
[ Felipe de Assis ]
Artigo no estilo Mentoring
32 – Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
[ Marcelo Thomaz Cicala ]
145ª Edição - 2017 - ISSN 1677918-5 - Impresso no Brasil
Editor
Rodrigo Oliveira Spínola (rodrigo.devmedia@gmail.com)
Subeditor
Eduardo Oliveira Spínola
Consultora Técnica
Anny Caroline (annycarolinegnr@gmail.com)
Jornalista Responsável
Kaline Dolabella - JP24185
Capa e Diagramação
Romulo Araujo
Fale com o Editor!
É muito importante para a equipe saber o que você está achando da
revista: que tipo de artigo você gostaria de ler, que artigo você mais
gostou e qual artigo você menos gostou. Fique a vontade para entrar
em contato com os editores e dar a sua sugestão!
Se você estiver interessado em publicar um artigo na revista ou no site
SQL Magazine, entre em contato com os editores, informando o título e
mini-resumo do tema que você gostaria de publicar:
Rodrigo Oliveira Spínola - Editor da Revista
rodrigo.devmedia@gmail.com
RODRIGO OLIVEIRA SPÍNOLA
Editor Chefe da SQL Magazine, Mobile
e Engenharia de Software Magazine.
Professor da Faculdade Ruy Barbosa,
uma instituição parte do Grupo DeVry.
Doutor e Mestre em Engenharia de
Software pela COPPE/UFRJ.
Assine agora e tenha acesso a
todo o conteúdo da DevMedia:
www.devmedia.com.br/mvp
Atendimento ao leitor
A DevMedia possui uma Central de Atendimento on-line, onde você
pode tirar suas dúvidas sobre serviços, enviar críticas e sugestões e
falar com um de nossos atendentes. Através da nossa central também
é possível alterar dados cadastrais, consultar o status de assinaturas
e conferir a data de envio de suas revistas. Acesse www.devmedia
.com.br/central, ou se preferir entre em contato conosco através do
telefone 21 3382-5038.
Publicidade
publicidade@devmedia.com.br – 21 3382-5038
Anúncios – Anunciando nas publicações e nos sites do Grupo DevMedia,
você divulga sua marca ou produto para mais de 100 mil desenvolvedores
de todo o Brasil, em mais de 200 cidades. Solicite nossos Media Kits, com
detalhes sobre preços e formatos de anúncios.
EXPEDIENTE
Guia HTML 5Guia HTML 5
DEVMEDIA
http://www.devmedia.com.br/guias/guia-html/3
Um verdadeiro manual de referência
com tudo que você precisa sobre HTML!
6 SQL Magazine • Edição 145
Este artigo é destinado a profissionais que lidam com bancos de dados
SQL Server diretamente e/ou tomam decisões técnicas relacionadas
à implementação de soluções usando esta plataforma de gerencia-
mento de dados. O conteúdo abordará as novidades disponibilizadas
na versão 2016 do produto com o intuito de expor suas melhorias e
seus novos recursos.
Fique por dentro
SQL Server 2016:
Conheça as novidades
Veja neste artigo uma análise sobre as
funcionalidades apresentadas para a nova versão
As duas últimas versões do SQL Server (2012 e 2014) trouxeram algumas funcionalidades que mudaram bastante o cenário de implantação
do produto, principalmente quando são consideradas
características como performance e alta disponibi-
lidade. Nesse contexto, recursos como Always On
Availability Groups, In-Memory Optimized Tables/
Procedures e Columnstore Indexes se tornaram, desde
seus respectivos lançamentos, essenciais para garantir
a ambientes críticos de bancos de dados os mais altos
níveis de performance, disponibilidade e capacidade
de gerenciamento.
Dando sequência à evolução do produto, o time res-
ponsável pelo desenvolvimento da plataforma trabalhou
para melhorar ainda mais os mecanismos já implemen-
tados e, claro, adicionar novas funcionalidades para que
o SQL Server possa atender eficientemente diversos ce-
nários e casos de uso onde performance, confiabilidade
e segurança são necessários junto ao gerenciamento de
dados, não importando sua volumetria.
Temporal Tables, Query Store e Stretch Databases são
apenas três das novas funcionalidades presentes no
SQL Server 2016. Além dessas, pode-se mencionar as
melhorias feitas junto ao recurso In-Memory Optimized
Tables/Procedures, cuja lista de pré-requisitos para
implantação foi drasticamente reduzida, e também os
ajustes nos Columnstore Indexes, que serão compatíveis
com índices tradicionais do tipo Row Store a partir da
versão 2016, limitação que bloqueava a adoção desse
recurso em alguns cenários.
Outra novidade que se pode notar na versão 2016 do
SQL Server é a abordagem de integração entre On-Premi-
ses e Cloud Computing. Essa ideologia, que já vem sendo
aplicada há alguns anos pela Microsoft, tornou-se ainda
mais forte, trazendo ao principal software de gerencia-
mento de dados novas funcionalidades que permitirão
melhor aproveitamento dos recursos disponibilizados
na plataforma de nuvem Windows Azure.
Devido à amplitude do assunto em relação a cada uma das fun-
cionalidades aqui apresentadas, o objetivo desse artigo não será
adentrar nos detalhes técnicos acerca de como as novas funciona-
lidades irão se comportar quando utilizadas, mas sim apresentar
uma visão geral do que cada uma possibilitará, trazendo ao leitor
um esboço expressivo da nova versão do SQL Server.
Stretch Databases
O primeiro recurso a ser apresentado neste artigo é denominado
Stretch Databases. Seu objetivo é permitir o armazenamento de
tabelas específicas no Azure SQL Database, fazendo com que
objetos do mesmo banco de dados fiquem distribuídos entre o
ambiente tradicional e a plataforma de nuvem.
Basicamente, com a funcionalidade Stretch Databases será
possível selecionar uma ou mais tabelas de um banco de dados
armazenado localmente e indicar ao SGBD que esses objetos
devem ser mantidos agora no Windows Azure, removendo-os do
ambiente On-Premises e mantendo apenas uma referência lógica
entre os dois locais.
Para as aplicações, essa divisão lógica do banco de dados é
completamente transparente, visto que o próprio SQL Server é
responsável por enviar e recuperar os registros do Azure SQL
Database quando houver manipulações e/ou consultas sobre as
tabelas armazenadas em nuvem.
Na camada de banco de dados, o funcionamento será relativamente
simples: as tabelas selecionadas para usarem esse recurso ainda
existirão no servidor local e ainda serão capazes de receber
comandos DML normalmente, mas todos os registros serão
Edição 145 • SQL Magazine 7
assincronamente transferidos para o Azure SQL Database quando
inseridos no objeto, diferente do que acontecerá quando comandos
UPDATE, DELETE e SELECT forem executados, pois nesses casos
os dados serão movimentados “em tempo real” para atender as
requisições de forma transparente às aplicações, agregando à
solicitação apenas o tempo necessário para a transferência na rede.
A transferência dos dados entre os ambientes On-Premises e
Cloud Computing acontece totalmente em background, através de
tarefas internas do SQL Server que são as principais responsáveis
por direcionar os registros para o Azure SQL Database e/ou solicitá-
los novamente quando uma query faz requisições de leitura.
A Figura 1 ilustra a arquitetura básica do recurso Stretch
Databases.
Figura 1. Arquitetura básica da funcionalidade Stretch Databases
O principal benefício dessa funcionalidade é a possibilidade
de usar os recursos locais com maior eficiência, pois será
significativamente mais fácil remover dados históricos e/ou
raramente acessados do mesmo servidor onde a porção de dados
mais ativa e importante ao negócio está localizada, permitindo
assim melhor utilização dos discos, por exemplo.
Dados históricos costumamfazer com que os backups dos bancos
de dados sejam maiores e, consequentemente, a janela de execução
aumente drasticamente ao longo da vida útil da solução, além de
prejudicarem a performance da seleção e manipulação de dados
por parte das aplicações, pois na maioria das vezes, apenas uma
pequena porcentagem dos registros armazenados em uma tabela
é constantemente utilizada.
Outro exemplo de dados acessados ocasionalmente são
tabelas de auditoria, cuja funcionalidade geralmente é rastrear
e armazenar ações de usuários ou aplicações para uma possível
consulta depois. Normalmente, os dados de histórico mantidos
por questões de auditoria são destinados a ajudar caso haja algum
tipo de investigação a respeito de uma falha ou erro, ou mesmo
para serem entregues a órgãos do governo conforme possíveis
regulamentações existentes para aquele segmento de negócio.
Os dois cenários mencionados anteriormente são apenas
alguns dos exemplos que poderão ser beneficiados com a Stretch
Databases, pois ambos lidam com uma porção dos dados que
é acessada apenas ocasionalmente. Desse modo, armazená-los
em um servidor de alta performance, com discos extremamente
rápidos e que trazem consigo um custo elevado talvez não seja o
melhor dos cenários quando o assunto é rentabilidade.
Outra vantagem ganha destaque quando consideramos questões
financeiras, visto que o custo de armazenamento em nuvem é
significativamente inferior ao valor exigido para a aquisição e
manutenção de equipamentos de alta performance. É importante
destacar que o custo, em ambientes tradicionais, não se refere
apenas à aquisição de equipamentos, mas também à manutenção,
contratação ou treinamento de pessoal qualificado para
administrar, infraestrutura para instalação, entre outras coisas.
Além disso, a implantação desse recurso é bastante simples.
Basta possuir em mãos uma conta do Windows Azure, habilitar
o banco de dados para Stretch e selecionar as tabelas desejadas,
que precisam atender uma pequena lista de critérios, como não
possuir constraints unique ou default. Depois disso, basta deixar
que o próprio SQL Server provisione o ambiente em nuvem, caso
ele não exista, e faça os demais ajustes.
A principal desvantagem é a latência de rede existente entre
os ambientes On-Premises e Cloud Computing, fator que deve
ser considerado quando performance ainda é importante para
consultas e manipulações dos dados armazenados no Azure SQL
Database. Devido ao armazenamento das tabelas selecionadas
ser realizado remotamente, em um Datacenter da Microsoft,
consultas sobre os dados desses objetos tendem a necessitar um
tempo superior ao que seria necessário caso estivessem localmente
no servidor.
Devido a essa desvantagem, é importante avaliar cuidadosamente
quais tabelas indicar para o armazenamento remoto. Não é
recomendado, por exemplo, aplicar esse recurso sobre uma tabela
que seja crítica a uma aplicação LOB (Linha de Negócio, em inglês),
pois cada transação efetuada precisará do tempo normalmente
exigido e também do tempo agregado pela latência de rede, e esse
aumento no tempo de execução pode não ser tolerável.
Query Store
O objetivo desse novo recurso é prover ao administrador de
banco de dados a capacidade de analisar planos de execução
retroativamente, assim possibilitando, por exemplo, a comparação
de como uma determinada rotina tem sido executada pelo SQL
Server ao longo de um determinado período, o que facilita a
identificação de alguns tipos de problemas de performance.
Basicamente, o Query Store armazena planos de execução
gerados pelo Query Optimizer e os disponibiliza para consulta
através de uma nova interface gráfica adicionada ao Management
Studio. Para isso, ele captura os planos de execução depois que o
processo é executado e armazena no mesmo banco de dados onde
a query foi disparada.
Assim como o recurso Stretch Databases, o Query Store é
invisível às aplicações e pode ser considerado uma ferramenta de
apoio a profissionais responsáveis pela identificação e resolução
de problemas de performance mais voltados a rotinas e comandos
T-SQL.
SQL Server 2016: Conheça as novidades
8 SQL Magazine • Edição 145
A configuração do Query Store é muito
simples. É necessário apenas habilitar
a funcionalidade no banco de dados
desejado e ajustar algumas configurações
como, por exemplo, a quantidade de
espaço em disco que será destinado ao
armazenamento de planos de execução e
o período de retenção dos planos dentro
do banco de dados.
Uma vez habilitado, o SQL Server
passa a inserir uma cópia dos planos
de execução na base de dados do Query
Store e disponibilizá-los para consulta
conforme a periodicidade configurada
inicialmente. Esse ajuste e os mencionados
anteriormente podem ser visualizados
na janela de configuração do recurso,
apresentada na Figura 2.
Depois de coletados e disponibilizados
para consulta, os planos de execução
podem ser acessados através de alguns
relatórios presentes no SQL Server
Management Studio, sob a aba Query
Store, dentro do diretório do próprio banco
de dados em questão. Por padrão, existem
quatro relatórios, cada um permitindo
que os dados sejam visualizados sob uma
perspectiva diferente.
A Figura 3 apresenta um dos relatórios
disponíveis, onde é possível conferir,
por exemplo, as 25 queries que mais
consumiram recursos na última hora (canto
superior esquerdo), além de permitir a
visualização do plano de execução (canto
inferior) e também de como diferentes
planos de execução (caso exista mais de um)
da mesma procedure têm se comportado no
ambiente no mesmo período mencionado
anteriormente (canto superior direito).
Os relatór ios apresentados pelo
Query Store ainda possibilitam que
o administrador de banco de dados
selecione um plano de execução que
possui melhor desempenho dentre aquelas
diferentes execuções de uma determinada
procedure e solicitar ao SQL Server que
fixe essa estratégia como caminho padrão
que o produto deve utilizar para todas as
chamadas da rotina.
Essa ferramenta facilitará, ainda, a
aplicação de outro recurso já existente
nas versões anteriores da plataforma e
conhecido como Plan Guides, que pode ser
definido justamente como a fixação de um
único plano de execução para um código
T-SQL em específico, técnica que remove
a autonomia do produto em definir a
melhor estratégia durante a execução
propriamente dita do processo e transfere
ao administrador de banco de dados essa
responsabilidade.
Temporal Tables
As tabelas temporais são um novo tipo de
objeto no SQL Server 2016 cujo objetivo é
rastrear e armazenar as modificações feitas
sobre os registros de uma determinada
Figura 2. Configuração do recurso Query Store
Figura 3. Relatório de visualização do Query Store
Edição 145 • SQL Magazine 9
tabela, possibilitando que os dados sejam consultados como se a
query tivesse sido disparada em outro ponto específico no tempo,
como uma semana atrás.
É válido destacar que esse recurso, diferentemente do SQL Server
Audit, por exemplo, não tem como foco a identificação de quem
fez uma ou outra modificação nos dados, mas sim o que mudou
propriamente dito nos registros. O foco é totalmente direcionado
ao histórico dos dados, não considerando questões de auditoria
de segurança e/ou assuntos correlacionados.
Seu funcionamento é bem simples: a tabela temporal é criada e
referenciada junto à tabela que deverá ser rastreada. Depois disso,
qualquer operação que altere ou exclua dados do objeto principal
provocará uma inserção na tabela temporal, onde serão mantidas
as versões anteriores dos registros que foram modificados,
mantendo assim um histórico de todas as versões geradas para
cada registro depois de sua implementação.
Além disso, toda a implementação,assim como o funcionamento,
é invisível às aplicações e usuários, não exigindo nenhuma
modificação em códigos-fonte ou rotinas presentes em qualquer
uma das camadas, mesmo nos bancos de dados. Isso acontece
porque a aplicação desse recurso no banco de dados não exige
modificações significativas nos objetos tradicionais, onde os dados
são normalmente manipulados.
Basicamente, ao criar uma tabela temporal, comandos de
manipulação de dados como UPDATEs e DELETEs (deve-se
também considerar o MERGE, pois, na prática, ele atualiza ou
exclui registros) efetuados sobre a tabela principal provocam um
comando adicional, responsável por registrar a versão anterior
do registro na tabela temporal, que acumulará todas as versões já
existentes e não mais válidas dos dados que sofreram alterações
ou exclusões.
Comandos INSERT não geram operação alguma na tabela
temporal, visto que não criam uma versão anterior para ser
considerada como histórico, mas sim incluem um novo registro no
banco de dados. Já em relação aos comandos SELECT, a Microsoft
adicionou quatro novos comandos para permitir a seleção dos
dados históricos como se a query tivesse sido executada em
outro horário/dia. Caso nenhuma dessas novas cláusulas seja
usada, uma consulta convencional é realizada pelo SQL Server,
retornando apenas os dados da tabela tradicional. A Figura 4
ilustra o conceito aplicado nas tabelas temporais.
Armazenar dados históricos da forma que as Temporal
Tables provisionarão era, até então, uma tarefa árdua para
o administrador, pois exigia a aplicação de outros recursos,
complexos de implementar e, principalmente, de administrar, ou
até mesmo dependiam de aquisição de ferramentas de terceiros,
adaptações no banco de dados ou nas aplicações, o que tornava a
necessidade de se manter esses dados um enorme desafio técnico.
O principal ponto de atenção em relação a esse recurso é o volume
de dados que pode ser gerado na tabela temporal depois de um
período com essa funcionalidade habilitada. Como cada comando
UPDATE e DELETE gera um novo registro no histórico, deve-se
atentar ao tamanho do objeto por questões de administração e
performance, pois uma tabela temporal associada a um objeto
que sofra uma considerável quantidade de alterações pode gerar
um volume de versões extremamente grande.
Deve-se ressaltar que o volume de dados armazenados em
uma tabela temporal não trará impactos na performance e no
funcionamento convencional do ambiente em relação a operações
de leitura na tabela principal, visto que nada muda para as
tabelas onde os dados de produção estão armazenados. Contudo,
operações que geram alteração de dados, como UPDATE e
DELETE, causarão um comando adicional de INSERT no servidor,
justamente para manter a versão histórica do registro que foi
modificado.
Figura 4. Arquitetura básica das Temporal Tables
Adicionalmente, tarefas administrativas como backup,
manutenção de índices, transferências de dados feitas por recursos
como Always On Availability Groups tendem a considerar esses
registros em suas operações, aumentando assim a quantidade de
dados a serem processados por cada uma delas.
Para mitigar as dificuldades mencionadas, estratégias como
particionamento de dados, combinadas com operações de
Switch Partition, por exemplo, podem ser aplicadas e usadas
constantemente para movimentar os dados históricos para tabelas
separadas e/ou outros filegroups do banco de dados.
FOR JSON
O SQL Server 2016 traz agora suporte à cláusula FOR JSON, o que
faz a nova versão do produto ser capaz de retornar dados nesse
formato, facilitando a integração de dados entre a plataforma de
banco de dados e as aplicações que consomem e transferem dados
usando o padrão JSON (BOX 1).
É uma linguagem de comunicação similar ao XML que tem sido largamente adotada por web
services para responder a requisições através da internet. É baseada em JavaScript, porém possui
suas particularidades adaptadas justamente para possibilitar transferências de dados através da
internet com menor tráfego de dados possível.
BOX 1. JSON
Basicamente, será possível consultar os dados usando a
linguagem T-SQL e formatar os resultados em JSON, tornando
viável o consumo e transmissão direto da informação sem
necessidade de uma nova conversão por parte da aplicação, através
de web services.
SQL Server 2016: Conheça as novidades
10 SQL Magazine • Edição 145
No enta nto, a i nda não haverá a
possibilidade de armazenar os dados no
formato JSON, nem haverá uma grande
variedade de comandos específicos para
manipular dados nesse modelo, como é
possível fazer quando se trabalha com
dados armazenados dentro do SQL Server
em XML.
Os comandos já divulgados pela
Microsoft incluem conversões automáticas
de dados tabulares para o formato JSON,
como pode ser visto na Figura 5, além da
possibilidade de se realizar o inverso, ou
seja, ler dados provindos desse modelo e
manipulá-los como registros tabulares,
como é comumente feito no SQL Server.
Live Query Statistics
Mais uma novidade presente no SQL
Server 2016 será a possibilidade de
visualizar um plano de execução em
tempo real. Na prática, esse recurso
permitirá que um administrador de
banco de dados acompanhe como o SQL
Server está conduzindo uma determinada
query durante seu processamento em
tempo real.
Nas versões anteriores, haviam duas
possibilidades quando o assunto era
visualizar um plano de execução de
uma query: estimado e real. O primeiro
deles é baseado em uma estimativa
do SQL Server de como seria a melhor
forma de se atender à requisição feita
pelo usuário sem mesmo processá-la,
enquanto o segundo corresponde ao plano
de execução efetivamente utilizado para
concluir aquela solicitação.
Naturalmente, o plano de execução
estimado é mais rápido para ser obtido,
visto que não há necessidade de o
SQL Server processar a consulta. Em
contrapartida, o plano estimado pode
não ser fidedigno à estratégia que seria
escolhida pelo produto em uma situação
onde a query fosse realmente processada.
O plano de execução real, por sua vez,
é totalmente confiável em relação ao
que foi escolhido pelo SQL Server no
processamento da query. Adicionalmente,
retorna informações complementares
que podem ser obtidas apenas quando
a consulta é processada de fato. Sua
desvantagem é a necessidade de aguardar
a conclusão da consulta, o que muitas
vezes demanda muito tempo ou em alguns
ambientes não é possível devido a i números
fatores ou situações adversas.
É na questão tempo exigido para se
obter o plano de execução real que esse
novo recurso se encaixa perfeitamente,
pois será possível analisar as etapas
que o SQL Server está conduzindo para
entregar os dados solicitados antes mesmo
do processo ser concluído, inclusive
acompanhando como o volume de dados
está sendo processado por cada um dos
operadores.
A Figura 6 ilustra o resultado obtido
através do uso desta nova funcionalidade:
um plano de execução “vivo”, ainda em
processamento, onde o responsável pela
análise pode visualizar não apenas a
estratégia de execução do SQL Server,
mas também o percentual de conclusão de
cada operador, entre outras informações
adicionais.
Backup to Azure
Não é segredo que o assunto backup seja
uma das principais responsabilidades de
qualquer profissional cuja posição inclua
cuidar de um ambiente de banco de
dados. Essa questão é tão importante que
são inúmeros os cuidados que devem ser
tomados com os arquivos resultantes desse
processo. Uma vez que serão requisitados,
na maioria das vezes, em momentos
críticos, de grande tensão e preocupação
de todos, principalmente das pessoas
responsáveis pelo negócio da empresa,
como coordenadores, diretores, presidente,
etc., é imensurável a importânciade tais
arquivos.
Backups são normalmente gerados em
disco e depois copiados para um dispositivo
de armazenamento a longo prazo, como
uma fita. Em alguns ambientes, são feitos
diretamente para a fita ou mesmo para
um servidor munido de um software
gerenciador de backups, que automatiza
boa parte do trabalho, como o módulo
Data Protection Manager do System Center,
conjunto de soluções de administração de
ambientes corporativos da Microsoft.
Armazenar arquivos de backup fora
de um ambiente On-Premises, onde Figura 6. Visualização do plano de execução em tempo real
Figura 5. Suporte nativo a JSON
Edição 145 • SQL Magazine 11
são necessários equipamentos adequados, sala cofre, proteção,
segurança de acesso, entre outros requisitos, não é um conceito
totalmente novo, visto que alguns fornecedores de soluções
voltadas para Cloud Computing disponibilizam serviços
dedicados ao armazenamento de dados críticos. Para tal,
oferecem todos os requisitos mencionados anteriormente com
um custo inferior ao normalmente investido pelas empresas no
provisionamento dessa infraestrutura.
Uma das novas funcionalidades do SQL Server 2016 endereça
justamente esse novo cenário: a realização de backups e restores
de forma integrada ao armazenamento de dados em nuvem. Essa
nova capacidade introduzirá maior facilidade em questões como
segurança dos backups, garantia de disponibilidade e até mesmo
maior flexibilidade em projetos de migração.
A partir da versão 2016 é possível realizar backups e armazenar
os arquivos diretamente em um espaço de armazenamento do
Windows Azure, assim como restaurar bancos de dados usando
a mesma premissa. Isso facilita consideravelmente atividades de
recuperação de desastres, pois os arquivos de backup estarão
salvos e acessíveis independentemente do tipo de problema que
venha a ocorrer, mesmo que um dos datacenters da Microsoft
fique indisponível, pois cada arquivo é replicado para mais duas
localizações visando garantir total proteção dos dados.
Outra atividade que se beneficiará desse novo recurso é a
migração de bancos de dados entre dois ambientes On-Premises
que estejam conectados por uma rede de baixa performance, mas
possuam uma conexão mais rápida com a internet, por exemplo.
Nesse cenário, será possível realizar o backup diretamente para o
ambiente Cloud Computing e depois restaurar o banco de dados
para o segundo servidor usando o mesmo arquivo, provindo do
Windows Azure.
Outro cenário onde esse recurso trará benefícios será a migração
entre um ambiente On-Premises e outro hospedado na plataforma
Windows Azure, visto que o mesmo processo apresentado no
cenário anterior poderá ser aplicado.
Para efeitos de comparação, essa estratégia de cópia e migração
de bancos de dados através de backups possui uma pequena
diferença em relação às versões anteriores, onde é necessário
realizar os backups localmente ou em um diretório de rede, e
depois fazer o upgrade para o Windows Azure. Com o SQL Server
2016, o tempo poderá ser poupado, visto que apenas uma tarefa,
a execução do backup propriamente dita, será necessária para
atingir o mesmo objetivo.
Always Encrypted
Pensando na segurança dos dados, a Microsoft introduziu
no SQL Server 2016 uma funcionalidade que permitirá que
aplicações se comuniquem com a plataforma de banco de dados
e estabeleçam uma transmissão de dados entre eles onde apenas
informações criptografadas poderão ser enviadas e recebidas.
Essa funcionalidade permitirá que a aplicação manipule os
dados em formato legível e no momento do envio deles para o
banco de dados, providencie sua criptografia, transferindo-os
na rede já protegidos por algoritmos avançados de segurança.
O banco de dados, por sua vez, receberá os dados criptografados
e os armazenará da mesma forma, ilegíveis. Por fim, quando a
aplicação solicitar ao SQL Server que responda a uma requisição
de dados, a plataforma de banco de dados enviará os dados
ainda criptografados para a camada de apresentação, que será
responsável por exibi-los em seu formato tradicional. Essa
arquitetura pode ser conferida na Figura 7.
Figura 7. Arquitetura básica de funcionamento do Always Encrypted
O objetivo principal desse recurso é evitar que dados
sejam acessados, roubados e interpretados por pessoas mal-
intencionadas que consigam acesso à rede de dados da empresa.
Caso haja uma violação de segurança e o atacante consiga capturar
qualquer informação que esteja sendo transferida entre a aplicação
e o banco de dados, ele não conseguirá compreendê-los, visto
que somente a aplicação, através de sua camada de acesso ADO,
pode remover a criptografia e disponibilizar os dados no formato
“clear text”.
Dynamic Data Masking
Essa nova funcionalidade do SQL Server 2016 tem a capacidade de
proteger dados armazenados em colunas específicas mascarando
os valores para que usuários sem o privilégio necessário não
tenham acesso a determinadas informações mesmo que ainda
possam executar comandos sobre aquele objeto, tudo isso de
forma totalmente transparente para as aplicações.
O Dynamic Data Masking pode ser aplicado tanto em uma nova
tabela quanto sobre uma já existente, através de novas cláusulas
T-SQL adicionadas ao produto para configurar como os dados
serão mascarados. Cada coluna recebe sua própria configuração,
ou seja, uma tabela com 10 colunas pode ter algumas com essa
funcionalidade e outras sem.
Embora a conf iguração seja feita sobre cada coluna
individualmente, para visualizar os dados legivelmente é
necessário possuir a permissão UNMASK sobre aquela tabela.
SQL Server 2016: Conheça as novidades
12 SQL Magazine • Edição 145
Todos os usuários que não possuírem esse direito, ao selecionar
os dados do objeto, receberão os valores das colunas onde há o
Dynamic Data Masking configurado como valores mascarados,
conforme a especificação feita na implantação da funcionalidade.
É importante destacar que os dados permanecem armazenados
sem máscara na tabela e nos índices existentes, sendo mascarados
apenas no momento da entrega do result set ao usuário solicitante
que não possuir a permissão necessária.
A Figura 8 ilustra a arquitetura geral dessa funcionalidade,
onde é possível visualizar a diferença entre os resultados obtidos
para dois diferentes grupos de usuários. O primeiro deles com
permissão de SELECT sobre o objeto, mas sem direito a visualizar
o valor contido naqueles campos, e o segundo, com direito total
a consultar e ver os dados de toda a tabela.
Figura 8. Arquitetura da funcionalidade Dynamic Data Masking
Row-Level Security
Pode-se definir como objetivo desse recurso a capacidade de
conceder permissões de segurança aos usuários conforme o
resultado de uma função, que atuará como critério de validação
para liberar ou não consultas e operações de manipulação de
dados a quem estiver tentando alguma dessas operações. Tudo
isso dinamicamente, sem que aplicações ou os próprios usuários
notem alguma diferença nas queries executadas por eles.
Na prática, essa funcionalidade foi projetada para permitir
aos administradores de banco de dados a implementação de
segurança baseada em predicados personalizados, que podem
inclusive ser dinâmicos conforme a lógica inserida dentro da
função responsável por essa classificação, em uma arquitetura
bem similar a outro recurso: o Resource Governor.
Sua arquitetura é constituída por dois objetos: uma função de
classificação e uma política de segurança (denominada security
policy na arquitetura oficial da funcionalidade), objeto novo
disponibilizado no SQL Server 2016 para associar uma função a
uma ou mais tabelas, podendo inclusive ser considerada como
um container de associações.
A função de qualificação é responsável por receber um ou
mais parâmetros, provindos da tabelaassociada na política de
segurança, e avaliar, a cada comando SELECT efetuado, quais
serão os dados que aquele usuário poderá visualizar. Com base
nisso, o próprio SQL Server adicionará o filtro equivalente nos
operadores do plano de execução e assim retornar apenas os
registros classificados como acessíveis para o devido usuário.
A Figura 9 apresenta a arquitetura básica da funcionalidade
Row-Level Security simulando um cenário de vendas onde
dois grupos de colaboradores (gerentes e diretores) possuem
diferentes permissões em relação a quais dados podem ou não
serem visualizados.
Figura 9. Arquitetura básica da funcionalidade Row-Level Security
No exemplo em questão, os diretores possuem permissão total
de visualização. Assim, quando disparam uma consulta à tabela
de vendas, receberão como resultado todos os dados existentes
naquela tabela. Por outro lado, os gerentes, ao executarem a mesma
consulta, poderão ver apenas as vendas relativas ao território
correspondente à sua unidade.
A lógica por trás da classificação de quais dados um usuário
específico pode visualizar fica dentro da função, que é
desenvolvida pelo profissional responsável pelo ambiente e
associada a uma tabela através do container Security Policy. Dentro
desse objeto as possibilidades são praticamente incontáveis. Um
exemplo é a possibilidade de determinar a permissão baseada
em diversos critérios, como o ID do departamento onde aquele
colaborador está alocado, restringindo-o, nesse caso, a visualizar
apenas os dados do setor em questão.
Cada uma das novidades do SQL Server 2016, tanto as novas
funcionalidades quanto as melhorias feitas pela Microsoft junto
a alguns dos recursos já existentes, reforçam o comprometimento
da empresa em tornar o produto melhor, atendendo a cenários
com a maior robustez e eficiência possíveis, independente da
criticidade e volumetria de dados a ser processada.
Além de todas as funcionalidades apresentadas neste artigo,
vale destacar que diversos recursos já existentes no produto
desde versões anteriores receberam melhorias, algumas delas
significativas, como no caso do In-Memory Optimized Tables
Edição 145 • SQL Magazine 13
and Procedures, mencionado no começo deste artigo, além
de outras alterações que foram aplicadas na versão 2016 que
superficialmente passam aos olhos, mas em cenários específicos
podem fazer total diferença.
Os investimentos feitos pela Microsoft na plataforma de
dados baseada em SQL Server (tanto On-Premises quanto
Cloud Computing, através do Azure SQL Database) colocaram
recentemente o produto como líder no quesito segurança, por
exemplo, entre todos os fornecedores de sistemas gerenciadores
de bancos de dados segundo o Gartner, instituto responsável por
diversos testes e benchmarks, entre outras coisas relacionadas à
tecnologia da informação.
Essa marca apenas comprova a evolução que o SQL Server vem
tendo em suas versões mais recentes. Novas funcionalidades,
novos conceitos, inúmeras melhorias e novas possibilidades
Felipe de Assis
felipe.assis@outlook.com / http://felipedba.wordpress.com
É especialista SQL Server na DataEX, empresa Gold Partner
Microsoft especializada na prestação de serviços para a plata-
forma de dados Microsoft. É certificado desde a versão 2005 do produto
e atualmente é MCSE Data Platform nas versões 2012/2014. Colabora
eventualmente com artigos para a revista SQL Magazine e é um dos palestrantes do SQL
Server PASS no Brasil, tendo participado do último SQL Saturday, realizado no mês de
novembro de 2015 em Brasília, onde falou sobre SQL Server 2016: as Temporal Tables.
Autor
destacaram o produto como uma solução corporativa de dados
completa e muito abrangente, capaz de ser implementada nos
mais diferentes cenários onde a gestão de dados é primordial.
14 SQL Magazine • Edição 145
São inúmeras as situações em que o administrador de dados ou sys-
admin não dispõe de métricas eficazes para obter a melhor implemen-
tação e performance do banco de dados. Algumas vezes isso decorre
até da falta de informação sobre as potencialidades oferecidas pelo
sistema. Para os que optaram pelo PostgreSQL em sua organização,
apresentamos nesse artigo diversos aspectos técnicos, do hardware ao
software, da instalação ao tuning, do sistema gerenciador de banco de
dados ao sistema operacional, das formas de instalação à configuração
do cluster, visando auxiliar na tarefa de implantação para que o leitor
obtenha o melhor resultado em sua instalação.
Fique por dentro
Trabalhando com o
PostgreSQL:
implementação do
banco de dados
Domine os aspectos de uma implementação em
ambiente de produção
A produção e interpretação de dados relacionados às atividades humanas sempre fomentou a evolução da tecnologia que atualmente cons-
titui como um dos pilares da civilização moderna. Na
década de 70, no séc. XX, não era diferente. Os setores
comercial e industrial da época já manifestavam o in-
teresse em obter acesso mais rápido aos seus registros
em decorrência do armazenamento convencional de
arquivos, bem como reduzir a redundância e inconsis-
tência dos dados até que fosse possível compartilhá-los,
garantindo certo nível de segurança. Apostando nesse
mercado, a IBM (International Business Machines) iniciou
suas pesquisas determinada a oferecer uma ferramenta
que atendesse à nova demanda. Durante as pesquisas,
alguns modelos de bancos de dados foram explorados,
dentre eles, o hierárquico, de rede e relacional. Nesse
contexto, o modelo de dados deve ser entendido como
o conjunto de ferramentas conceituais para a descrição
de dados, seus relacionamentos, semântica e restrições
que mantenham a consistência. Esses modelos se sub-
dividem em três grupos: lógicos baseados em objetos,
lógicos baseados em registros e modelo físicos de dados.
Retornando à pesquisa da IBM, ela batizou o resultado
do projeto inicial com o nome System R, visionário na
implementação de sistemas de bancos de dados rela-
cionais e na implementação da SQL (Structured Query
Language), embora não fosse integralmente fiel ao pro-
posto por Codd em suas regras para o modelo relacional.
Na época seus autores divulgaram generosamente os detalhes de
seu objeto de estudos à comunidade por meio de artigos, o que
encorajou dois pesquisadores de Berkley, os cientistas Michael
Stonebraker e Eugene Wong, a desenvolverem o próprio projeto
de banco de dados relacional. Ambos já haviam arrecadado fun-
dos com o projeto INGRES (Interective Graphics Retrieval System)
- um banco de dados geográfico que fora desenvolvido para um
grupo econômico, implementado por um grupo de estudantes e
financiado por órgãos de pesquisa do governo norte-americano - o
que garantiria subsídio ao início do projeto. Do projeto "INGRES"
derivaram outros sistemas de bancos de dados, tais como SQL
Server, Sybase e o próprio PostgreSQL.
O projeto Postgres foi iniciado em 1986 na Universidade de
Berkley, e sua proposta era suprir as dificuldades impostas pelo
Edição 145 • SQL Magazine 15
INGRES, como a manipulação de tipos definidos pelo usuário
(hoje conhecidos como objetos) pelo sistema relacional e a de gran-
de volume de armazenamento. Através dessa lógica, o nome Post-
greSQL veio da aglutinação de "post-INGRES" (após o INGRES).
Em 1994 o PostQUEL, que era a linguagem nativa para interpre-
tação de consultas implementada pelo Postgres, foi substituída
pelo interpretador SQL. Esse foi um marco importante na história
do projeto, que após isso passou a ser chamado PostgreSQL.
PostgreSQL
O PostgreSQL utiliza o modelo lógico baseado em registros atra-
vés do modelo relacional. Adicionalmente, o projeto atual permite
manipulações diretas análogas ao conceito da orientação a objetos,
como herança, polimorfismo e o uso de objetos.É distribuído
sob a licença BSD (Berkley Software Distribution), que caracteriza
o software como pertencendo ao domínio público e, portanto,
livremente personalizável, exigindo somente o reconhecimento
autoral para a distribuição do binário, versão original ou alterada.
Essa licença é muito permissível e viabiliza o funcionamento de
softwares em diferentes formatos de licença trabalhando juntos.
Não se pode dizer que a licença BSD é necessariamente melhor
que a licença "concorrente", GPL (Gnu General Public License).
A questão é que a primeira é menos restritiva. Note que tanto a
licença BSD quanto o Postgres tiveram origem no mesmo local, a
Universidade de Berkley.
A norma ACID, acrônimo para os conceitos de Atomicidade,
Consistência, Isolamento e Durabilidade, que representam os
critérios básicos para transações e recuperações a falhas, é inte-
gralmente implementada pelo PostgreSQL. Essas propriedades
asseguram aspectos essenciais para a qualidade das operações,
tais como controle de integridade referencial e de concorrência de
multi-versão, ou MVCC (Multiversion Concurrency Control), além
de ser premissa do modelo relacional.
Existem muitas implementações maduras no PostgreSQL, tais
como replicação síncrona e assíncrona, segurança SSL(Secure So-
cket Layer) e criptografia nativos, utilização de clusters de dados,
operação com multithreads, multiplataforma, suporte a 24 idiomas,
backups PITR (Point-In-Time Recovery), áreas de arma-
zenamento (tablespaces), pontos de salvamento (save-
poins), subconsultas e visões atualizáveis, controle
de locking, suporte à definição de funções em PL/
PgSQL, Perl, Python, Ruby, dentre outras lingua-
gens de programação, foreign data-wrappers, alerta
e failover automatizado para ambientes de missão
crítica, além de inúmeras características vantajosas.
Existe também o interesse crescente da comunidade
em apoiar o desenvolvimento do software através
de contribuições e patrocínios, como é o caso da
Hewlett-Packard, VMware, EnterpriseDB, Fujitsu,
Google, Skype e RedHat, para citar algumas. Por
isso, o PostgreSQL é frequentemente apontado como
ótima opção em projetos que demandem estabi-
lidade e facilidade de uso conjugada a boa curva
de aprendizado, visto que ostenta a reputação de ser o sistema
gerenciador de banco de dados de código livre mais sofisticado
disponível. Como dito anteriormente, o PostgreSQL é um projeto
comunitário, open source, coordenado pelo PostgreSQL Global De-
velopment Group, e que não limita o escopo de suas capacidades,
recursos e funcionalidades aos usuários, ou seja, está totalmente
à disposição para qualquer um, a qualquer momento.
Um aspecto que corrobora o alinhamento às demandas mais
atuais, como a disponibilidade de serviços em nuvem e a escala-
bilidade vertical, é o fato de que o PostgreSQL não é somente re-
lacional: é multi-paradigma, agregando também funcionalidades
No-SQL (Not Only SQL). Aqui temos um bom exemplo de como
o uso de extensões pode ampliar a gama de possibilidades do
sistema. O PGXN (PostgreSQL Extension Network) é um sistema de
distribuição central para bibliotecas open source de extensão para
o PostgreSQL. De forma similar, também existe o pgFoundry, que
reúne diversos projetos relativos ao PostgreSQL.
A maioria das linguagens de programação modernas proveem
interface nativa para conexões com o PostgreSQL. Como alterna-
tiva em caso de ausência, há disponibilidade de acesso através
da interface ODBC (Open Database Connectivity), que permitiriam
acesso por produtos da Microsoft como Access e Excel. Aplicações
desenvolvidas em Java contam com o suporte oferecido pelo driver
JDBC (Java Database Connectivity). Já para o PHP, teríamos o PDO
(PHP Data Objects) como opção. O acesso aos dados ocorre de vá-
rias formas, podendo ser através de linha de comando (psql), pelo
código SQL embutido na aplicação/framework, por cliente gráfico
(pgAdmin III) ou via navegador web (phpPgAdmin), por exemplo.
Arquitetura e versionamento
Outro ponto forte do projeto é a sua arquitetura cliente-servidor
(ver Figura 1), fazendo com que o acesso aos dados seja distribuído
a clientes que não os acessam diretamente mas via processo no
servidor (postmaster), mesmo que o cliente esteja acessando na mes-
ma máquina onde o serviço está instalado. A seguir, uma forma
simplificada de como esse acesso se dá, através de TCP/IP (Transfer
Control Protocol/Internet Protocol) ou Internet, via tunelamento.
Figura 1. Arquitetura cliente-servidor implementada no PostgreSQL
Trabalhando com o PostgreSQL: implementação do banco de dados
16 SQL Magazine • Edição 145
A Tabela 1 indica algumas das limitações para as capacidades
de armazenamento.
Antes de iniciarmos nossas considerações sobre a instalação
do PostgreSQL, explicaremos o controle de versão praticado em
suas distribuições. Suas atualizações encontram-se disponíveis
no site oficial do PostgreSQL, e o número da versão já nos indica,
de imediato, o que podemos encontrar. Esse número deve ser
decomposto em duas partes: a primeira delas nos dará a versão
majoritária e a segunda, a versão minoritária. A versão majoritária
representa um marco evolutivo do sistema, composto por novas
funcionalidades e revisões e é identificada pelos dois primeiros
números da versão. Já a versão minoritária se refere a atualizações
de segurança e correções de bugs, sinalizada pelo último número
da versão. O clico de vida da versão majoritária é de cerca de cinco
anos, com versões lançadas a cada ano, enquanto isso, disponibili-
zações de atualizações de versões minoritárias são lançadas quase
que mensalmente. A inclusão de novas funcionalidades costuma
ser amplamente discutida em listas internacionais, como a wiki
do Roadmap, debatidas e aprovadas durante a PgCon, evento que
ocorre no Canadá anualmente.
Por exemplo, à época da publicação desse artigo, a versão estável
mais atual disponível era a 9.5.3. Isso significa que tínhamos à dis-
posição todas as funcionalidades da versão 9.5 e também estávamos
à terceira revisão de segurança e correções de erros para a série.
Atualizações minoritárias do cluster nunca mudam o formato
de armazenamento interno e são sempre compatíveis. É possível
apenas substituir os executáveis enquanto o sistema estiver pa-
rado e reiniciar o cluster após isso. Por exemplo, a versão 8.42 é
compatível com a 8.4, 8.4.1 e 8.4.6. As atualizações minoritárias
não alteram o diretório de dados e são simples assim. Contu-
do, atualizações majoritárias sujeitam o PGDATA, diretório de
dados anteriormente citado, a alterações. Isso torna o processo
mais sensível. O método tradicional para esse procedimento é a
realização do dump, utilizando o pg_dump ou pg_dumpall a partir
da versão mais recente, a fim de compatibilizar os recursos, na
versão anterior, com consequente restore na versão mais atual. No
entanto, essas não são as únicas opções seguras de atualização
A partir da série 8.4.x foi possível migrar um cluster da série
8.3.x para a mais recente, de forma direta, sem a necessidade de
dump-restore, através do aplicativo pg_migrator, que mais tarde
teve seu nome alterado para pg_upgrade e atualmente encontra-
-se estável. Sua performance é notadamente mais rápida que a
forma tradicional, além de evitar que espaço adicional em disco
seja necessário para a migração.
Também é possível usar certos métodos de replicação, como
Slony, para criar um servidor de espera (stand by) com a versão
atualizada do PostgreSQL. Isso é possível porque Slony suporta
replicação entre diferentes versões majoritárias do PostgreSQL. A
espera pode estar no mesmo computador ou em um computador
diferente. Uma vez que tenha sincronizado com o servidor master
(executando a versão mais antiga do PostgreSQL), você pode alter-
nar mestres e encerrar a instância de banco de dadosmais antigo.
De forma geral, os itens atualizados são os relativos à adminis-
tração, SQL, API da biblioteca, catálogos do sistema e o servidor
API (Application Programming Interface) da linguagem C.
É altamente aconselhável que sejam consultadas as notas de
lançamento da versão antes de proceder as atualizações. Mesmo
que o processo seja crítico, é melhor realizá-lo do que expor seus
dados a vulnerabilidades já conhecidas.
Caso o fornecedor do seu sistema operacional não forneça
atualizações de segurança da série utilizada por você, considere
utilizar os pacotes de atualização disponíveis no site oficial do
PostgreSQL. No entanto, se não existir pacote disponível de qual-
quer forma, considere compilar o PostgreSQL diretamente no seu
ambiente com as suas preferências e otimizações.
Recursos de hardware
Até esse ponto já abordamos vários aspectos que justificariam
a escolha do PostgreSQL como sistema gerenciador de bancos de
dados como opção. Agora iremos abordar questões relacionadas
ao hardware. Esse é um aspecto central que pode conduzir ao su-
cesso ou fracasso da implementação. Prioritariamente, devemos
conhecer os discos e controladoras, memória RAM (Random Access
Memory), CPU (Central Processing Unit) e rede. Nos adiantando um
pouco mais, inclusive a opção pelo sistema de arquivos é deter-
minante. Torna-se obrigatório avaliar informações técnicas tais
como estatísticas, relatórios de benchmarks e consultar a opinião
de pessoas cuja experiência poderia contribuir. Alta performance
em bancos de dados só é obtida com domínio sobre o hardware.
Hard Disk
Por ser o recurso mais consumido pelo SGBD, iniciaremos nossa
discussão pelos discos e controladoras. Ao escolher um HD (Hard
Disk), você certamente se atenta à sua capacidade de armazena-
mento, à sua interface e, provavelmente, ao tamanho do cache,
afinal, essas são as informações que acompanham a descrição do
produto. Mas há outros parâmetros ligados ao desempenho do
dispositivo que também devem ser observados. Os mais conhe-
cidos são: seek time, latency time e access time.
O seek time, em tradução livre, tempo de busca, normalmente
indica o tempo que a cabeça de leitura e gravação leva para se
deslocar até uma trilha do disco ou mesmo de uma trilha a ou-
tra. Quanto menor esse tempo, melhor o desempenho, é claro.
Esse parâmetro pode ter algumas diferenciações, sendo que sua
divulgação varia de fabricante para fabricante. Latency time é a
Tamanho máximo de um banco Ilimitado
Tamanho máximo de uma tabela 32 TB
Tamanho máximo de uma linha 1,6 TB
Tamanho máximo de um campo 1 GB
Tamanho máximo de linhas por tabela Ilimitado
Tamanho máximo de colunas por tabela
De 250 a 1.600, dependendo dos
tipos de dados utilizados
Tamanho máximo de índices por tabela Ilimitado
Tabela 1. Capacidade de armazenamento
Edição 145 • SQL Magazine 17
medida que indica o tempo necessário para que a cabeça de lei-
tura e gravação se posicione no setor do disco que deve ser lido
ou mesmo gravado. Esse parâmetro sofre influência do tempo
de rotação dos discos (atualmente de 5.400, 7.200 e 10.000 RPM /
Rotações Por Minuto) e também é informado em milissegundos.
O access time, corresponde a um cálculo que combina os parâme-
tros de latency time e seek time. Em termos práticos, o access time
indica o tempo necessário para se obter uma informação do HD.
Novamente, quanto menor esse tempo, melhor.
RAID
Além de meramente armazenar dados, soluções de armazena-
mento têm que prover acesso à informação de maneira eficiente,
em tempo hábil e, dependendo do caso, oferecendo algum tipo
de proteção contra falhas. É nesse ponto que os sistemas RAID
(Redundant Array of Independent Disks) apresentam sua utilidade:
segurança, capacidade e desempenho. Dessa forma, um arranjo
ou sistema RAID é a aglomeração física de HDs representados
por uma unidade lógica de armazenamento. Podemos entender
a unidade lógica como o dispositivo que o sistema operacional
identifica como apto ao armazenamento secundário, independen-
temente da quantidade de dispositivos em uso e da tecnologia do
disco. Os benefícios são muitos. Para mencionar alguns, temos:
• Em caso de falha de um dos discos, os dados existentes nele
não serão perdidos, pois podem ser replicados em outra unidade
(redundância);
• É possível elevar o volume de armazenamento a qualquer mo-
mento com a adição de mais HD;
• O acesso à informação pode se tornar mais rápido, pois os dados
são distribuídos em todos os discos;
• Dependendo do caso, há maior tolerância a falhas, pois o sistema
não é paralisado se uma unidade parar de funcionar;
• Um sistema RAID pode ser mais barato que um dispositivo de
armazenamento mais sofisticado e, ao mesmo tempo, oferecer
praticamente os mesmos resultados.
Múltiplos discos RAID separados por tabelas e índices muito
acessados são importantes. Outro ponto importante é optar por
controladoras RAID que disponham de baterias. Isso viabiliza o
uso de seu cache de forma mais eficiente e segura. Há disponi-
bilidade de diferentes tipos de arranjos que visam atender com
flexibilidade as necessidades dos administradores de dados e
sysadmins em RAID. Não serão citadas aqui as opções porque
fogem ao escopo do artigo.
Memória RAM
No caso da análise dos requisitos para a aquisição de memória
temos um cenário mais enxuto: quanto mais, melhor. Porém, como
sempre há uma reserva, tem-se que devemos dar preferência às
que possuem correção de erro com registradores, ou ECC (Error
Correction Check). A principal característica desse tipo de memória
em relação às comuns é a presença de bits adicionais. Contudo,
vale ressaltar que a correção de erro não ocorre semente na me-
mória RAM: em discos rígidos, mídias digitais, CD, DVD, etc. A
estratégia é simples: “o que foi escrito foi exatamente o que foi
gravado?”. Caso sim, ok; caso contrário, uma nova tentativa de
resposta positiva é feita. Em caso de sucessivos erros de integri-
dade da informação, o setor defeituoso é marcado como badblock
e isolado no sistema de arquivos para que outras tentativas de IO
(input-output) não ocorram ali. Em tempo, caso esteja trabalhando
em um sistema OLTP (On-Line Transaction Processing), saiba que a
demanda por memória principal não será tão grande quanto em
um sistema OLAP (On-Line Analytical Processing), onde a produção
de relatórios, estatísticas e transformações podem exigir mais que
em um ambiente de transações rápidas, que é o caso do primeiro.
CPU
A CPU é o componente do hardware que, em última instância,
realiza as tarefas solicitadas pelos programas. Existe um proces-
so denominado escalonamento, ou scheduling, responsável por
verificar qual processo é mais prioritário e possui menor tempo
de realização, possibilitando a execução de processos concor-
rentes e viáveis. O escalonamento com múltiplos processadores
não é linear. Nesse caso, poucos processadores rápidos são mais
eficientes que muitos processadores lentos. Contudo, o banco de
dados tira proveito de ambientes multiprocessados através da
implementação de processadores de 64 bits e clock elevado. Para o
PostgreSQL, a visibilidade entre processos é importante, portanto,
quanto mais núcleos no mesmo local, melhor será a comunicação
entre eles. Por isso, processadores com múltiplos núcleos são
muito bem recebidos.
Memória compartilhada e semáforos
Apesar de admitir que o PostgreSQL busca atender a uma vasta
gama de necessidades, partiremos do princípio de que o leitor
visa usá-lo em ambientes de grande porte. Nesse caso, é razoável
imaginar que, a princípio, o software esbarre em alguns limites
do sistema operacional. Semáforos - utilizados como método de
sincronização entre processos - e memória compartilhada - área de
memória em que um contexto processual pode trabalhar - frequen-
temente necessitamde ajustes. Alguns outros pontos como limite
de processos por usuário, quantidade de arquivos abertos simul-
taneamente por processo e a quantidade de memória disponível
para esse podem afetar o funcionamento do PostgreSQL, já que
ele utiliza a memória compartilhada e semáforos para realizar a
comunicação inter processos baseando-se no modelo System V IPC
(lê-se "System Five") porque a execução de um processo pressupõe
por parte do sistema operacional, entre outras coisas, a criação de
um contexto de execução próprio que, de certa forma, abstrai o
processo dos componentes reais do sistema. Devido a essa virtu-
alização dos recursos, o processo não tem conhecimento acerca
dos outros processos e, como tal, não consegue trocar informação.
O cache do PostgreSQL é conhecido como shared buffer e fica
armazenado em segmentos de memória compartilhada pelos
processos do servidor, ou back-ends. Uma forma de visualizá-los
é utilizando a ferramenta ipcs.
Trabalhando com o PostgreSQL: implementação do banco de dados
18 SQL Magazine • Edição 145
Os limites de memória compartilhada ajustáveis ocorrem através
dos parâmetros de kernel, com o Linux:
• SHMMNI: representando o número máximo de segmentos de
memória compartilhada no sistema operacional;
• SHMMAX: tamanho máximo de um segmento de memória
compartilhada. Dependendo da instalação do sistema operacional,
esse valor pode vir baixo. A configuração para esse valor deve
estar alinhada ao que for definido em alguns outros parâmetros
do PostgreSQL, como shared_buffers, max_connections, autova-
cuum_max_workers, max_prepared_transactions, max_locks_per_tran-
sactions e wal_buffers;
• SHMALL: significa o tamanho total de memória compartilhada
pelo sistema operacional. É importante disponibilizar espaço o
suficiente para que toda a memória compartilhada e mais os back-
-ends caibam nesse parâmetro.
Como mencionado anteriormente, os semáforos são responsáveis
pela sincronização inter processos e mostram-se particularmente
úteis quando o número de conexões se eleva demasiadamente.
A seguir, temos alguns parâmetros relacionados a eles:
• SEMMNI: número máximo de conjuntos de semáforos. A re-
gra para o PostgreSQL é que seja, no mínimo, (max_connections +
autovacuum_max_workers) / 16;
• SEMMSL: número máximo de semáforos por conjunto.
É considerada uma boa prática reservar pelo menos 17 semáforos
por conjunto;
• SEMMNS: número máximo de semáforos no sistema
operacional, que deve ser SEMMNI * SEMMSL;
• SEMOPM: número máximo de operações por semáforo quando
da chamada do sistema semop().
Journaling
Existe também outro aspecto que foge ao escopo das confi-
gurações nativas do PostgreSQL e diz respeito aos sistemas de
arquivos do sistema operacional, que é a estrutura lógica uti-
lizada pelos sistemas operacionais para organização do arma-
zenamento. A relevância desse tema nesse contexto se mostra
ao considerarmos que estamos lidando com volumes cada vez
maiores de dados cuja complexidade de manutenção aumenta
proporcionalmente, sendo que a escolha de um file system em
detrimento de outro, mais adequado, poderia comprometer o
desempenho e a confiabilidade do cluster. Há consenso entre
especialistas em bancos de dados que o suporte a journaling é
uma vantagem, o que justifica a sua popularidade e que, de
forma geral, lida melhor com prevenção a perdas de dados e a
recuperação em caso de falha. Journal, também conhecida como
"registros de log", de onde deriva o termo journaling, é uma
pequena área reservada no sistema de arquivos que armazena
metadados sobre arquivos a fim de oferecer uma recuperação
rápida e eficiente. No entanto, há um ponto a considerar: a ge-
ração de overhead, já que, nesse cenário, o processo de gravação
do arquivo ocorre da seguinte forma:
• Registro de blocos de metadados;
• Efetuar a escrita no bloco;
• Registrar metadados do bloco.
Contudo, em caso de falha entre as etapas, podem ocorrer incon-
sistência das informações onde existiriam blocos marcados como
utilizados, porém, vazios. Da mesma forma, blocos de metadados
preenchidos, sem referência a qualquer dado. Isso não inviabiliza a
recuperação, todavia, a torna mais lenta. Quanto ao overhead, pode
ser considerável, visto que pode gravar o mesmo dado duas vezes.
Por esse motivo, existem modos de operação de journaling. São eles:
• Journal: persiste todas as alterações. É o mais lento dos três
modos, mas é o que possui maior capacidade de evitar perda de
dados;
• Ordered: grava somente mudanças em arquivos de metadados,
bem como as atualizações no arquivo de dados antes de fazer as
mudanças associadas ao sistema de arquivos. É o modo padrão nos
sistemas de arquivos ext3 (abordado posteriormente). Os modos
journal e ordered são os mais indicados para o armazenamento de
dados brutos;
• Write-back: semelhante ao ordered, mas utiliza o processo de
escrita do sistema de arquivos em uso para gravação. É o mais
rápido journaling ext3 com o menor overhead, o que o torna forte
candidato a ser utilizado de forma segura com pg_xlog porque
resíduos de armazenamento podem ser facilmente removidas pelo
mecanismo WAL (Write-Ahead Logging). A seguir listamos alguns
dos sistemas de arquivos que possuem o journaling:
- EXT3: desenvolvido pela Red Hat, é basicamente a extensão
do EXT2 com a adição do journaling. E isso é uma vantagem
porque o torna compatível com seu antecessor. Utiliza endereço
de 32 bits e blocos de até 8 kB. O tamanho máximo dos arqui-
vos e da partição são determinados pelo tamanho dos blocos
durante a formatação. Na Tabela 2, vemos a representação
dessa distribuição.
Por padrão, o tamanho do bloco é definido automaticamente
mas também pode ter seu valor configurado manualmente
usando o parâmetro “-b” do comando mkfs.ext3 (usado para for-
matar as partições EXT3 no Linux), como em “mkfs.ext3 -b 2048
/dev/hda1” (cria blocos de 2 KB) ou “mkfs.ext3 -b 4096 /dev/hda1”
(para blocos de 4 KB). A implementação do recurso no EXT3
é feita na camada “Journaling Block DeviceI” com o propósito
de alcançar qualquer tipo de dispositivo com sua tecnologia.
O EXT3 dá suporte a “write barries” com algumas limitações,
por exemplo, não funcionar em RAID de software e despejar o
cache inteiro da controladora e não somente o arquivo.
Tamanho do bloco Tamanho máximo da
partição
Tamanho máximo dos arquivos
1 kB 2 TB 16 GB
2 KB 8 TB 256 GB
4 KB 16 TB 2 TB
8 KB 32 TB 2 TB
Tabela 2. Capacidade de armazenamento no EXT3
Edição 145 • SQL Magazine 19
A utilização do EXT3 é ampla em distribuições menos re-
centes e o sistema de arquivos é reconhecido como estável,
contudo, a tendência é que o EXT3 seja lentamente substi-
tuído pelo EXT4.
- EXT4: tornou-se o padrão atual por ser aceito como a evo-
lução natural do EXT3. Implementa corretamente a questão
dos “write barriers”, reserva de blocos contíguos para alocação
e oferece outras melhorias para reduzir fragmentação de ar-
quivos. Possui desfragmentação on-line – que é o equivalente
a desfragmentar os arquivos enquanto estão sendo alocados,
verificação de integridade do journal (journal checksums) e
compatibilidade com as versões da família EXTFS (ext2, ext3
e ext4). Para usar o ext4 em uma nova partição, basta que
você formate ela com o comando mkfs.ext4. Por exemplo,
vamos considerar que a partição /dev/hda6 vai ser formatada
para usar o novo file-system através do comando mkfs.ext4 /
dev/hda6. Para usar o ext4 em uma partição que já existe e
que foi formatada previamente com o ext3, basta que ela seja
montada usando o ext4dev no tipo do file-system conforme o
comando mount -t ext4dev /dev/hda1 /mnt/hda1. Na Tabela 3
verificamos algumas limitações de armazenamento do EXT4.
Tamanho do bloco
Tamanho máximo da
partição
Tamanho máximo dos
arquivos
4 kB 1 EB 16 TB
Tabela 3.Capacidade de armazenamento no EXT4
- XFS: sistema de arquivos 64 bits compatível com os de
32 bits, de alto desempenho e geralmente escalável. Desenvol-
vido inicialmente para sistema operacional IRIX mas como é
open source, logo foi migrado para o mundo GNU/Linux. Tam-
bém possui desfragmentação on-line. Um recurso interessante
para esse sistema de arquivos é o redimensionamento on-line,
realizado pelo utilitário “xfs_growfs”. Contudo, o redimen-
sionamento é no único sentido de aumentar o volume, nunca
diminuí-lo, sendo apropriado para lidar com grandes volumes
de dados. É pouco popular no Linux, porém é admitido como
o mais adequado para bancos de dados, pois apresenta uma
melhor implementação do journaling e de write barriers, envian-
do somente o necessário para o cache. Na Tabela 4 verificamos
algumas limitações de armazenamento do XFS. Especialistas
o apontam como o mais recomendado para PostgreSQL. Esse
sistema é divido em três partes:
a) Seção de dados: contém informações sobre ponteiros para
extensões ou ponteiros para listas de extensões;
b) Seção de log;
c) Seção de tempo real.
Tamanho do bloco Tamanho máximo da partição
Tamanho máximo dos
arquivos
512 b a 64 kB 8 EB, em 32 b
8 EB, em 64b
16 TB, em 32 b
Tabela 4. Capacidade de armazenamento no XFS
Tuning do pg_xlog
O pg_xlog é o local onde se armazenam os logs de transação do
PostgreSQL. Em um banco com caráter fortemente transacional,
é uma otimização obrigatória separá-lo desse contexto, uma vez
que nesse ponto a carga de entrada e saída é intensa e provocada
pelas seguidas sincronizações do fsync. Para isso, basta separar
o pg_xlog em outro dispositivo e referenciá-lo através de um link
simbólico para o ponto de montagem. Podemos obter um resul-
tado equivalente se utilizarmos a opção –X no initd no momento
da criação do cluster.
Dê preferência por alocar o diretório do pg_xlog em armazena-
mento não-volátil, porque a perda de dados nesse arquivo pode
impedir a inicialização do cluster. Sendo otimista, no mínimo as
informações das últimas transações. Portanto, não é exagero su-
gerir que se utilize de armazenamento confiável em um esquema
de redundância para esse diretório.
Pool de conexões: o pgBouncer
Ainda falando sobre performance, é oportuno discutir sobre o
pooler de conexões. Esse conceito está relacionado à capacidade
de disponibilizar um cache de conexões de bancos de dados que
possam ser reutilizadas. Esse recurso tem o objetivo de garantir o
desempenho das aplicações uma vez que abrir e fechar conexões,
notadamente em sistemas distribuídos, é um processo custoso.
Mas se todas as conexões do depósito de conexões estiverem em
uso, o sistema gerenciador de banco de dados deve ser capaz
de fornecer uma nova. Como o PostgreSQL é multiprocesso e
cada processo representa um back-end, se executarmos múltiplos
processos no SGBD teremos a mesma quantidade no sistema
operacional. O problema disso é que, via de regra, processos,
além de exigirem mais trabalho do escalonador, costumam des-
perdiçar recursos.
Por isso, um grande número - superior a 200 - de conexões di-
retas deve ser evitado. O número máximo de conexões simultâ-
neas que o PostgreSQL pode suportar é definido pelo parâmetro
max_connections. A memória compartilhada para cada conexão
é de aproximadamente 400 bytes. Ainda há um parâmetro que
permite acesso reservado a administradores para o caso do pool
estar em sua capacidade máxima e o mesmo precisar acessar, é
o superuser_reserved_connections. Como alternativa às aplicações
que não gerenciam de forma independente o pool de conexões,
existem dois gerenciadores externos: pgPool2 e pgBouncer, que
atendem muito bem a ambientes em que existem muitas sessões
simultâneas que executam tarefas breves. O acesso via gerenciador
externo deve ser transparente e diminuir o impacto do custo de
abertura e fechamento de sessões. Esse gerenciador de conexões
não requer muitos recursos de hardware. Um servidor virtual
com 4 cores, 8 GB de memória e 30 GB de disco, é suficiente para
a implementação do pgBouncer. Contudo, o throughput de rede
deve ser monitorado pois esse é o gargalo. OpgBouncer funciona
em três modos de operação:
• Session: considerado o mais sutil entre as opções. Sua caracte-
rística é fornecer uma conexão do banco sob demanda durante
Trabalhando com o PostgreSQL: implementação do banco de dados
20 SQL Magazine • Edição 145
toda a permanência do usuário. Após isso, a conexão é devolvida
ao pooler. Cabe mencionar que ele não traz ganhos significativos;
• Transaction: nesse modo, a solicitação só é atendida durante
uma transação. Quando o gerenciador percebe o fim da transação,
a sessão é encerrada e a conexão é devolvida. Esse é sugerido como
modo de operação para o PostgreSQL;
• Statement: oposto ao modo session. Aqui, a conexão é atribuída
a cada comando, isoladamente, o que inviabiliza uma transação
com múltiplas declarações, o que o torna pouco prático. Como os
outros, a conexão retorna ao pooler no final.
É possível configurar o pgBouncer através do arquivo “pg-
bouncer.ini”, geralmente alocado em /etc/pgbouncer/pgbouncer.ini.
O leitor perceberá que ele é divido nas seções database e pgbouncer.
O elemento básico é um parâmetro configurado no formato chave-
-valor. Podemos observar na Tabela 5 as opções de configuração
para a seção databases.
dbname Nome do banco de dados do servidor
dbhost Endereço IP do servidor
port Porta TCP onde a requisição será atendida
user Usuário do servidor
password Senha de autenticação para o usuário
pool_size
Tamanho máximo do pool para esse banco de dados.
O padrão é o default_pool_size
connect_query
Consulta executada entre o estabelecimento da conexão e
o início da sessão
client_encoding Codificação de caracteres utilizada
datestyle Informa o estilo da data a ser utilizada
timezone Informa a zona horária a ser utilizada
Tabela 5. Parâmetros de configuração de conexão do pgBouncer
Já na seção pg_bouncer, temos à disposição os parâmetros gerais
para ajustes apresentados na Tabela 6.
Temos igualmente à disposição opções administrativas e de visu-
alização de logs que auxiliam na implementação do gerenciador de
pooler e no monitoramento de eventos. Na Tabela 7 identificamos
os mais relevantes.
Enquanto gerenciador do pool de conexões, um sistema deve
prover um bom monitoramento das filas de acesso entre outras
verificações. O pgBouncer dispõe de alguns parâmetros que aten-
dem a essa demanda que são explicados brevemente na Tabela 8.
Monitoramento com pgBouncer
Acessos pelo console do pgBouncer se relacionam ao controle e
gerenciamento de operações. O monitoramento é feito através
do comando show, que exibe estatísticas sobre o uso do pool.
Vemos as opções disponíveis para argumentos no comando
show na Listagem 1. Os colchetes representam o conjunto de
opções, enquanto o caractere pipe indica a exclusividade entre
elas. Ou seja, dentre as possibilidades, apenas uma pode ocorrer
em certo momento.
listen_addr Endereço TCP/IP onde o servidor atenderá as solicitações.
listen_port Porta TCP/IP onde o servidor está atendendo.
unix_socket_dir Diretório do soquete de domínio.
log_file Caminho do arquivo de log.
pidfile Caminho do arquivo que informa o PID, ou id do processo.
auth_type
Tipo de autenticação suportada. Pode ser: trust, any, plain,
crypt ou md5.
pool_mode
Define quando uma conexão pode ficar disponível a ou-
tros acessos. São eles: session, transaction e statement.
max_client_conn
Número máximo de conexões cliente aceitas. Tecnica-
mente, esse valor deve corresponder a max_client_conn +
(max_pool_size * num_databases * num_clientes).
default_pool_size
Define quantas conexões são permitidas por usuário ou
banco.
reserve_pool_size
Se refere à quantidade de conexõesadicionais são permiti-
das a usuário ou banco.
reserve_pool_ti-
meout
Limite de tempo para o atendimento a uma solicitação.
Caso o cliente não seja atendido, o pgBouncer reserva
uma conexão para ele.
server_round_ro-
bin
Visto que as conexões são reutilizadas e solicitadas a partir
de uma pilha, significa que poucas conexões reais são utili-
zadas e que serão distribuídas de forma mais uniforme.
Tabela 6. Parâmetros de configurações gerais do pgBouncer.
syslog
Habilita o recurso na plataforma Windows, onde é
utilizado.
syslog_facility
Para esse parâmetro, “facilidade” se refere ao tipo de
programa que está enviando as informações para o
syslog.
log_connections Registra as conexões realizadas com sucesso.
log_disconnections Registra eventos de conexões desfeitas e suas causas.
log_pooler_errors
Registra as saídas de erro que o pgBouncer envia para
os clientes.
admin_users
Parâmetro administrativo que identifica os usuários
que têm permissão para se autenticar no console do
gerenciador de pooler e efetuar comandos.
stats_users
Outra opção administrativa que permite a identificação
dos usuários que podem obter acesso ao pgBouncer
através do console mas que só podem realizar coman-
dos read-only.
Tabela 7. Parâmetros de monitoramento do pgBouncer
server_login_retry
Tempo de espera necessário para que uma nova
tentativa de login seja realizada em caso de falha
anterior.
query_timeut
Consultas que atingem esse limite sem obter o resul-
tado de seu processamento são canceladas.
client_idle_timeout
Se a conexão do cliente estiver ociosa mais do que o
tempo informado por esse parâmetro, ela será fecha-
da. O valor mínimo para esse parâmetro deve ser o
tempo total de conexão.
client_login_timetout
Se o cliente obter a conexão mas não estabelecer uma
sessão dentro desse limite, terá seu acesso cancelado.
autodb_idle_timeout
Tempo de espera antes de solicitar o fechamento de
certa conexão utilizada por um pool genérico.
Tabela 8. Parâmetros de gerenciamento de pool do pgBouncer
Edição 145 • SQL Magazine 21
Já o controle pode ser realizado através das opções:
• Pause: o pgBouncer utiliza a estratégia de tentar desconectar
todos os servidores após a finalização normal de as consultas;
• Suspend: todos os buffers são escritos e o pgBouncer para de
atender a solicitações. Assim como o pause, aguarda que todas as
consultas tenham terminado normalmente;
• Resume: retorno às rotinas após a execução de um comando
pause ou suspend;
• Shutdown: termina o processo do pgBouncer;
• Reload: faz a recarga do arquivo de configuração do pgBouncer.
Instalação do PostgreSQL
Existem diferentes formas de instalar o PostgreSQL. Agora,
passaremos da teoria à prática. O primeiro ponto de interrogação
que nos vem à mente é: “qual pacote usar? ”. Para tornar mais
explícita nossa abordagem e dar espaço à análise, os elencamos
conforme segue:
• Fornecido pelo fabricante do sistema operacional;
• Disponível em postgresql.org;
• Compilado a partir do código-fonte.
Dito isso, vamos começar pelo primeiro. Geralmente essa é a
forma mais rápida e fácil de instalação pois o PostgreSQL já vem
com todas as flags desejadas pelo fabricante e integrando-se ao seu
sistema de diretórios. Não é necessariamente vantajoso porque
dificulta a personalização das flags e por ter caráter de versio-
namento com foco nas versões minoritárias – o que geralmente
é definido pelo fabricante –, deixando de fora as atualizações
majoritárias. Mas a possibilidade de um suporte personalizado
que suplante essas dificuldades para o usuário passa a ser uma
vantagem.
Mais uma possibilidade é visitar o site oficial do PostgreSQL
e escolher e realizar o download do pacote mais apropriado para
seu sistema operacional. Os pacotes disponíveis são mantidos
pela comunidade de desenvolvimento do projeto e não ne-
cessariamente abrangerão todos os sistemas operacionais ou
todos os forks e versões dos mesmos. Para usuários do Windows
e Mac OS X, essa é a única via. Os pacotes são compilados com
as configurações de flags acordadas pela comunidade. Para as
distribuições GNU/Linux mais recentes baseadas em Debian e
Red Hat, respectivamente, estão disponíveis os repositórios para
apt e yum. Os repositórios também contam com todas as versões
disponibilizadas do PostgreSQL, além dos módulos contrib e
ferramentas que já citamos aqui, como pgAdminIII e Slony. Por-
tanto, utilize as instruções do seu gerenciador de pacotes em
função da sua escolha:
• No Debian: como superusuário, apt-get install postgresql;
• No CentOS: como superusuário, yum install postgresql-server;
• No Windows e Mac: utilize o instalador próprio.
Por fim, resta a compilação do código-fonte que é uma técnica
que apresenta maior grau de dificuldade e requer certa familiari-
dade com desenvolvimento. É necessário ter instalado o compila-
dor da linguagem C e o código-fonte das bibliotecas necessárias:
zlib e libreadline. Certamente essa é a opção que permite maior
liberdade de configuração e, por isso, possibilita a instalação de
qualquer versão disponível do PostgreSQL.
Para encerrar a discussão, você é que precisa entender a neces-
sidade do seu negócio e optar. Contudo, é importante considerar
alguns pontos que podem guiar o leitor nessa decisão. Dê pre-
ferência ao pacote disponibilidade pelo fabricante, caso esteja
disponível e atenda às suas necessidades. Caso seu fornecedor não
ofereça o pacote, busque-o na comunidade. Contudo, se ambos os
cenários não lhe agradam, siga pelo caminho da compilação. Mas
para isso, esteja consciente de que os itens a seguir se esgotaram,
na seguinte ordem:
1) O sistema operacional não possui os pacotes;
2) O pacote oferecido pelo sistema não está na versão que você
precisa;
3) Não há oferta pelo pacote que você precisa no site oficial;
4) Você precisa alterar flags de compilação;
5) Você precisa alterar o código-fonte.
Instalação a partir de repositórios no Debian
A partir de agora iniciaremos os processos de instalação do
PostgreSQL através de repositórios, que são locais, remotos ou
não, de onde as dependências das instalações e atualizações
podem buscar automaticamente arquivos de forma a facilitar a
instalação e tornar o processo mais confiável. Essa é uma conve-
niência pois, dessa forma, podemos incrementar nosso sistema
operacional de acordo com nossa vontade. As informações sobre
os pacotes ficam armazenadas em listas que devem ser sincroni-
zadas periodicamente.
Partimos do princípio de que temos em mãos uma instalação
GNU/Linux cuja lista de repositórios está desatualizada. Em um
terminal, como root, editamos o arquivo sources.list. Considerando
a Listagem 2 como referência, verificamos a instalação utilizando
o apt-get na primeira linha. Na linha 02 temos a adição do reposi-
tório para a instalação do PostgreSQL. Ele contém as referências
de onde serão baixados os pacotes. Adicionamos as linhas 03 e 04
e salvamos a alteração. Essas inclusões dizem respeito à importa-
ção da chave de autenticação do repositório e permitem instalar
os pacotes. Após isso, no terminal, executamos as linhas de 05 a
07. Com isso, temos as dependências mapeadas para iniciarmos
a instalação do PostgreSQL.
Listagem 1. Parâmetros para a cláusula show.
SHOW { STATS|
SERVERS|
CLIENTS|
POOLS|
LISTS|
USERS|
DATABASES |
FDS|
CONFIG
}
Trabalhando com o PostgreSQL: implementação do banco de dados
22 SQL Magazine • Edição 145
A sequência de comandos representada na Listagem 3 inicia o
processo de instalação do PostgreSQL em sua linha 01 e, após isso,
verificamos a localização dos arquivos da instalação no diretório
referidopela linha 02.
Instalação a partir de repositórios no CentOS
De forma análoga ao que foi realizado para instalação no Debian,
teremos que configurar o repositório para proceder a instalação
do PostgreSQL. A interpretação que fazemos da Listagem 4 é que,
a princípio, é muito semelhando ao que foi exibido na listagem
anterior. Mas há diferenças: o cluster não é inicializado automa-
ticamente no CentOS como é no Ubuntu. Essa característica é
mostrada na linha 04 da Listagem 4. Além disso, os arquivos de
dados e de configuração são separados em diretórios distintos no
Ubuntu. Já no CentOS essa separação não existe.
Acompanhando os comandos, vemos que na linha 01 a lista
de pacotes do repositório informado é atualizada. Após isso, o
comando da linha 02 sugere que listemos todas as ocorrências
de pacotes relacionados ao postgres no repositório que acabamos
de atualizar. Ao leitor que está executando esses comandos no
terminal, será exibida uma lista com o resultado solicitado pela
linha anterior. A instalação é iniciada através do comando da
linha 03. A penúltima linha habilita o serviço do PostgreSQL no
CentOS e a última o inicializa. Essas duas últimas configurações
são particulares desse sistema operacional.
Instalação a partir da compilação de código-fonte
Iniciaremos agora a demonstração de como instalar o Postgre-
SQL a partir de binários. Essa situação não é rara e muitas vezes
temos que lançar mão desse recurso para dispor de um ambiente
que atenda às nossas expectativas. O código apresentado na
Listagem 5 mostra a sequência dos comandos para a instalação
no Debian para a versão 9.4.2 do PostgreSQL. Com a finalidade
de abstrair o processo e torná-lo mais convidativo, não vamos
alterar flags durante o processo. Contudo, o momento seria esse,
se fosse o caso.
Da interpretação e execução do código da Listagem 5, depreen-
demos que se trata de um processo relativamente simples e sem
muitos percalços, mas que deve ser feito com atenção. A linha
01 mostra a necessidade de, previamente, fazer o download de
pacotes de desenvolvimento que dão início ao processo. A linha
seguinte realiza o download do código-fonte diretamente do repo-
sitório para que seja utilizado. Em seguida, na linha 03, o pacote
é descompactado para o diretório informado nela. A utilidade
desse diretório será informada posteriormente. O comando da
linha 04 se refere à navegação, não necessariamente obrigatória,
mas que facilita o entendimento, até a pasta onde está o arquivo
recém descompactado. Lá podemos visualizar alguns arquivos,
o que é visto pela linha 07.
A presença do arquivo “configure” é o que nos interessa nesse
momento já que ele é o responsável por proceder a verificação de
todos os requisitos de instalação do PostgreSQL. Essa verificação
é iniciada pelo comando da linha 06. O makefile contém todos os
blocos de configuração para que o PostgreSQL seja instalado. Ve-
remos isso acontecer ao executar o comando indicado na linha 07
da listagem. Nesse momento já temos o software instalado, agora
daremos início à configuração do ambiente. Começaremos pelos
binários de instalação, que por padrão após a compilação são
alocados em /usr/local/pgsql/bin. O arquivo profile deve ser alterado
para que seja possível a todos os usuários a invocação dos biná-
rios pelo terminal. Nesse caso, basta alterarmos a configuração
do path – dentro desse arquivo – e adicionarmos o endereço dos
binários da nova instalação, que é usr/localpgsql/bin. Atualizamos
a leitura do arquivo conforme visto na linha 09 para que as alte-
rações sejam efetivadas.
Nessa forma de instalação o usuário postgres não é automatica-
mente criado. Por isso temos que criar a infraestrutura para ele.
Como é sabido, ele deve existir no contexto do sistema operacional.
Listagem 4. Instalação do PostgreSQL no Red Hat.
01 # yum localinstall
http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1
.noarch.rpm
02 # yum list postgres*
03 # yum install postgresql94-server
04 # /usr/pgsql-9.4/bin/postgresql94-setup initdb
05 # systemctl enable postgresql-9.4
06 # systemctl start postgresql-9.4
Listagem 2. Atualização da lista de repositório local no Debian.
01 # vim /etc/apt/sources.list
02 ....
03 deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdgmain
04 # wget https://www.postgresql.org/media/keys/ACCC4CF8.asc
05 # apt-key add ACCC4CF8.asc
06 # apt-get update
07 # apt-get upgrade
Listagem 3. Instalação do PostgreSQL no Debian.
01 # apt-get install postgresql-9.4
02 # ls -l /usr/lib/postgresql/9.4/bin
03 # whereis pg_dump
04 # whereis pg_dumpall
05 # whereis psql
Listagem 5. Instalação do PostgreSQL a partir dos pacotes binários.
01 # apt-get install build-essential libreadline-dev libghc-zlib-dev
02 # wget ftp://ftp.postgresql.org/pub/source/v9.4.2/postgresql/9.4.2.tar.gz
03 # tar zxvf postgresql-9.4.2.tar.gz –C /usr/src
04 # cd /usr/src/postgresql-9.4.2/
05 # ls
06 # ./configure
07 # make world && make install –world
08 # vim /etc/profile
09 # source /etc/profile
10 # mkdir –p /var/lib/postgresql –s /bin/bash postgres
11 # chown postgres. /var/lib/postgresql/ -R
12 # chmod 700 /var/lib/postgresql
13 # initdb –D /var/lib/postgresql/9.4/main
Edição 145 • SQL Magazine 23
Para isso, criamos uma pasta e sua estrutura de subdiretórios,
indicando seu local e o bash com o nome postgres, conforme exibido
na linha 10. A linha 12 lhe dá permissões pertinentes sobre ela.
Por fim, a última linha da listagem inicia o cluster do banco. Após
isso, o sistema gerenciador de banco de dados open source está
devidamente configurado. O leitor já pode realizar uma conexão
básica com o servidor, conforme mostrado na Listagem 6.
O código mostrado realiza uma conexão bem simples com o ban-
co de dados. A linha 01 faz o login com o usuário postgres. A linha
02 invoca o aplicativo, enquanto a linha 03 faz o detalhamento
de todas as tabelas do banco. A linha 04 encerra o aplicativo e a
última faz o logoff. Uma sintaxe bem prática para o acesso via psql
é "psql – U usuario –h host banco". Algumas considerações sobre
essa sintaxe são:
• Omitir o usuário faz o programa assumir o nome do usuário
do sistema operacional;
• Não informar o host sugere ao programa que o endereço é
localhost;
• Por último, omitir a informação do banco leva o psql admitir
que a conexão deve ser feita com um banco de mesmo nome que
o usuário.
Inicialização do cluster do PostgreSQL
Chegamos a um ponto fundamental no nosso processo de
implementação do sistema gerenciador de banco de dados.
Conforme explicado anteriormente, o hardware é um dos recur-
sos que podem potencializar o serviço ou mesmo se tornar um
gargalo. Mas esse é um problema que a computação contornou
através do recurso de cluster, que possibilita ao PostgreSQL a
expansão de sua capacidade de processamento para múltiplos
servidores físicos, interligados e sincronizados, transferindo o
gerenciamento de funções de alta disponibilidade, tolerância a
falhas e balanceamento de carga do hardware para o software, uti-
lizando recursos abertos e interoperáveis e soluções baseadas
em software livre que permitem a implementação de sistemas
de cluster e grid sem ter que arcar com o ônus de licenciamento
de software.
O PostgreSQL permite clusters na mesma máquina física,
desde que tenham configurações e bancos de dados distintos.
Inclusive, mais de um cluster pode estar em execução simul-
taneamente desde que em portas TCP diferentes. Fisicamente,
pode ser definido na variável de ambiente $PGDATA. Caso a
variável exista e esteja configurada corretamente, não é neces-
sário utilizar o parâmetro –D no utilitário initdb, tampouco nos
outros utilitários que utilizam a pasta do cluster.
Listagem 6. Conexão de teste com o servidor.
01 # su - postgres
02 $ psql
03 =#\dt
04 =# \q
05 $ exit
É necessário que o diretório de armazenamento seja de
propriedade de quem irá gerenciá-lo, normalmente o postgres.
A Tabela 9 apresenta os principais arquivos que ficarão dis-
postos no cluster.
PG_VERSION Arquivo Registra a versão majoritária do PostgreSQL.
base Subdiretório
Dispõe de um subdiretório para cada banco
de dados em uso.
global Subdiretório Armazena o catálogo do PostgreSQL.
pg_clog Subdiretório
Contém dados sobre a finalização das
transações.
pg_multixact Subdiretório
Registra dados sobre o estado multitran-
sacional. Usado para prover níveis de
isolamento.
pg_stat_tmp Subdiretório
Armazena arquivos temporários do subsis-
tema de estatísticas.
pg_subtrans Subdiretório Dados do estado de subtransações.
pg_tblspc Subdiretório
Contém links simbólicos para as tablespa-
ces.
pg_twophase Subdiretório
Contém arquivos de estados para prepared
statements.
pg_xlog Subdiretório
Armazena os logs de transação, conhecidos
como WAL, já citados.
postmaster.opts Arquivo
Disponibiliza as opções de prompt da últi-
ma inicialização do cluster.
postmaster.pid Arquivo
Contém o PID e o ID do segmento de shared
buffer.
postgresql.conf Arquivo
Contém as configurações gerais do Post-
greSQL.
pg_hba.conf Arquivo
Responsável por definir as permissões por
usuário, banco de dados, máscara de rede,
tipo de autenticação ou a combinação entre
esses fatores.
pg_ident.conf Arquivo
Modelo “chave-valor” que define a associa-
ção entre usuários de sistemas e bancos de
dados.
Tabela 9. Estrutura do cluster PostgreSQL
O pg_ctl é um utilitário para iniciar, parar ou reiniciar o servidor
PostgreSQL ou mostrar o status de um servidor ativo. Embora
o servidor possa ser iniciado manualmente, o pg_ctl encapsula
tarefas como redirecionar a saída do log, desacoplar do terminal
e do grupo de processos de forma adequada, além de fornecer
opções convenientes para uma parada controlada. O comando
demonstrado na primeira linha da Listagem 7 é a sintaxe para
a inicialização do cluster. Onde há "pasta", entenda-se o caminho
de diretórios - que pode ser variável - onde se localiza o cluster.
As demais linhas seguem a mesma lógica e são autoexplicativas.
Listagem 7. Utilização do aplicativo pg_ctl para tarefas no cluster.
# pg_ctl –D /pasta/cluster start
# pg_ctl –D /pasta/cluster stop
# pg_ctl –D /pasta/cluster restart
# pg_ctl –D /pasta/cluster reload
# pg_ctl –D /pasta/cluster status
Trabalhando com o PostgreSQL: implementação do banco de dados
24 SQL Magazine • Edição 145
Ainda sobre cluster, temos mais uma explicação sobre pg_stat_
tmp. Como a escrita é intensa nesse diretório e seu conteúdo não
necessariamente precisa ser persistente, aconselha-se colocá-lo
em RAM disk. Mas fique atento por que ao fazer isso, em caso de
reinício do cluster, as estatísticas de uso do banco serão perdidas.
O banco de dados e o usuário postgres
Podemos dizer nessa etapa que esse é o primeiro contato que
o usuário terá com o PostgreSQL após concluir todas as etapas
de instalação. O banco criado automaticamente é o que contém
as informações do catálogo do sistema. Esse banco, chamado
postgres, cria um usuário homônimo e não pode ser removido se
não exist irem outras opções para conexão no cluster. Esse é o pri-
meiro banco a ser criado no cluster já apto a estabelecer conexões.
Além desse, são criados outros dois: o template0 e o template1.
A estrutura utilizada como “molde” para o banco postgres é de-
finida pelo template1. Esse template, ou modelo, é o padrão para
a criação dos bancos de dados posteriores. Ele pode ser ajustado
e essas alterações se refletirão nos objetos futuros. Já o template0
é imutável, sequer a ponto de removê-lo. Ele é conhecido como
"modelo mínimo". Por "mínimo" entenda-se "mínimo funcional",
visto que pode ser utilizado como modelo em caso de falha de
todos os objetos e do template1.
Sobre o usuário postgres, ele deve existir no sistema operacional.
Geralmente o próprio processo de instalação se encarrega dessa
instanciação. Esse será inicialmente o superuser do banco, sendo
que esse objeto não pode ser removido pois é através dele que o
cluster pode ser recuperado em caso de crash.
Já dispomos de todo o ambiente devidamente instalado, configu-
rado e ajustado. Nos resta fazer um teste simples de conexão para
verificar o mínimo sobre o que acabamos de montar. A ferramenta
mais comum para essa tarefa é a psql. A Listagem 6 demonstrou
de forma bem simples como uma conexão pode ser realizada.
Bruno de Oliveira Machado
bruno.execute@hotmail.com
Tecnólogo em Análise e Desenvolvimento de Sistemas, espe-
cialista em Tecnologias para Desenvolvimento Web, trabalha
no Núcleo de Tecnologia da Informação da Universidade Estadual Vale
do Acaraú como Administrador de Dados utilizando PostgreSQL. Tem se
dedicado a conciliar suas atividades entre a administração e otimização dos servidores de
banco de dados, suporte à equipe de desenvolvimento de software e à implementação
de soluções em Business Intelligence com Pentaho, bem como à atividade de tutor do
curso de Análise e Desenvolvimento de Sistemas da Unopar.
Autor
Links:
Site oficial do PostgreSQL
http://www.postgresql.org
Repositório de extensões para o PostgreSQL
http://pgxn.org
Sistemas de arquivos NTFS, FAT16, FAT32, EXT2 e EXT3
http://www.diegomacedo.com.br/sistemas-de-arquivos-ntfs-fat16-fat32-ext2-e-ext3/
Referência oficial sobre o utilitário pg_ctl na versão 9.4 do PostgreSQL
https://www.postgresql.org/docs/current/static/app-pg-ctl.html
Projetos relacionados ao PostgreSQL.
http://pgfoundry.org
Referência oficial sobre atualização do cluster
https://www.postgresql.org/docs/9.2/static/upgrading.html
Sistemas de arquivos com Journaling
http://www.ppgia.pucpr.br/~laplima/ensino/so_common/materia/05_arquivos.html
Edição 145 • SQL Magazine 25
Este artigo é útil quando queremos pesquisar e encontrar padrões
nas linhas que estão armazenadas no nosso banco de dados. Iremos
aprender a utilizar a nova cláusula introduzida na versão 12c do Oracle,
a MATCH_RECOGNIZE. Com ela poderemos criar e identificar esses
padrões. Com essa nova sintaxe, podemos fazer o mesmo trabalho de
antes, mas de uma maneira muito mais simples, ganhando em perfor-
mance, escalabilidade e manutenção. Vamos aprender, com a ajuda de
exemplos, o que são esses padrões, como defini-los e encontrá-los nas
linhas correspondentes, e o que podemos ganhar com isso.
Fique por dentro
Oracle 12c: Conheça a
cláusula pattern matching
Aprenda a utilizar a cláusula MATCH_RECOGNIZE
para identificar padrões através das linhas
armazenadas no banco
Estamos vivendo em uma época em que a in-formação está sendo cada vez mais valorizada e necessária. Podemos constatar isso com o
aumento de ofertas de emprego na área de BI, onde
são analisados os dados da empresa para apoiar as
atividades de tomada de decisão. Encontrar padrões no
consumo dos clientes, nas ofertas de certo produto etc.,
está cada vez mais comum e quase que mandatório em
uma empresa que deseja sobreviver nesse mundo cada
vez mais competitivo.
Encontrar esses padrões já era possível em versões
anteriores a 12c do Oracle, mas essas soluções eram
difíceis de se escrever, de entender e muitas vezes inefi-
cientes ao executar, consumindo recursos consideráveis
no processo. Com a ajuda das novas cláusulas e sintaxe
do 12c, o mesmo processo ficou agora nativo ao SQL e
podemos, assim, alcançar resultados melhores de forma
bem mais eficiente.
Podem existir diferentes tipos de padrões, tudo depen-
de daquilo que estamos procurando. Alguns exemplos
seriam: padrão de preços, em que diferentes épocas do
ano o preço sobe e desce de acordo com o mercado, ou
apenas o número bruto de vendas, quantidade de visitasem um site, comportamento em aplicações financeiras
para detecção de fraude, etc. Neste artigo iremos abordar
como definir esses padrões, agora com a sintaxe nativa
do SQL, e como pesquisar por isso nas linhas que estão
armazenadas no banco de dados. Iremos aprender a
utilizar a nova cláusula introduzida na versão 12c do
Oracle, MATCH_RECOGNIZE, que irá nos ajudar a
identificar padrões através de uma sequência de linhas
armazenadas no banco. Com essa nova sintaxe, podemos
fazer o mesmo trabalho de antes, mas de uma maneira
muito mais simples, ganhando em performance, esca-
labilidade e manutenção. Vamos aprender o que são
esses padrões, como defini-los e encontrá-los nas linhas
correspondentes, e o que podemos ganhar com isso.
DEFINE
Entre todas as novas cláusulas da sintaxe do Oracle relacionadas
a Pattern Matching, a primeira que vamos falar é a DEFINE. Com
essa cláusula, que é obrigatória, podemos definir variáveis de pa-
drão primárias que serão então utilizadas para criar o padrão final
que será utilizado para realizar a busca nas linhas. A pergunta
que devemos fazer é: que características definem o meu padrão?
Vamos utilizar um exemplo clássico, que é o histórico de preços
de um produto, algo bem comum em sites que buscam o melhor
preço para um determinado produto, guardando assim o seu
histórico para saber se o preço realmente está bom ou não. Vamos
analisar a Figura 1.
Olhando rapidamente para esse gráfico, podemos encontrar
o preço do produto em um determinado dia. Mas podemos ver
mais nesse gráfico do que apenas isso. Podemos encontrar alguns
padrões, como as variações de preços em forma de "V", onde o
preço começa a baixar em um determinado momento e depois
começa a subir até certo ponto. Ou até mesmo um "W" em que esse
mesmo processo ocorre duas vezes. Antes de definir esse padrão,
precisamos das variáveis que serão utilizadas nesse padrão, que
nada mais é que uma condição que deverá ser cumprida para que
uma linha seja mapeada para essa variável.
É isso o que a cláusula DEFINE faz. Vamos ver agora como definir
essas variáveis utilizando o exemplo do gráfico.
Oracle 12c: Conheça a cláusula pattern matching
26 SQL Magazine • Edição 145
Quais são as características do padrão em "V" que está compre-
endido entre os dias 5 e 10? O dia 5 é o ponto de partida, onde o
preço estava alto e a próxima mudança de preço é quando possi-
velmente existiu uma promoção, no dia 6. Essa foi caracterizada
por uma decida no preço, a primeira perna do nosso "v". A parti
do dia 7 até o dia 10, o preço começa a subir até atingir o preço
mais alto, antes de começar a cair novamente. Então devemos ter
três variáveis, o início, a descida e a subida. A cláusula ficaria
como nos mostra o código da Listagem 1.
Definimos então duas variáveis de padrão. A primeira identificada
com SUB, faz a comparação com a linha anterior, PREV, para saber
se tem um preço maior ou não. Se tiver, essa linha será mapeada
como SUB. A variável DES é exatamente o contrário, comparando
se tem um valor menor que a linha anterior, assim essa linha será
mapeada como DES. Agora faltou uma variável para o início do
padrão. Acontece que nem toda variável precisa de uma definição,
significando que qualquer linha pode então ser mapeada para esse
padrão, que é o caso do que veremos mais adiante.
PATTERN
Aqui definimos qual é o padrão que será pesquisado em todas
as linhas. Nessa cláusula, utilizamos as variáveis definidas em
DEFINE para criar esse padrão. Descrevemos de forma simples,
Figura 1. Gráfico de preços de um determinado produto em um período de tempo
Listagem 1. Criando um padrão com duas variáveis.
DEFINE
SUB AS SUB.preco > PREV(SUB.preco),
DES AS DES.PRECO < PREV(DES.preco)
a sequência em que as linhas serão mapeadas entre as variáveis
e a quantidade de linhas. Podemos utilizar expressões regulares
para tornar a nossa busca ainda mais poderosa. Veja um exemplo
na linha a seguir:
PATTERN (INI, DES+, SUB+)
Nesse exemplo utilizamos uma variável INI, que não foi definida
na Listagem 1, ou seja, qualquer linha pode ser mapeada como
INI, o nosso ponto de partida. Logo após temos uma a variável
DESC, que significa que a próxima linha deverá ter um preço
menor que a linha mapeada como INI. O símbolo de "+" significa
que pelo menos uma linha deverá ser mapeada ou mais como essa
variável. E logo após temos a última variável, SUB, que também
tem que ter pelo menos uma ou mais linhas mapeadas, indicando
um aumento no preço. Formando assim um padrão de "V".
Para que esse padrão seja encontrado, um conjunto de linhas
contínuas deverão ser mapeadas conforme a sequência defini-
da em PATTERN e todas as condições em DEFINE deverão ser
verdadeiras. A definição desse padrão poderá ser algo bem mais
avançada do que esse exemplo. Para isso, é necessário ter um bom
conhecimento sobre expressões regulares.
MEASURES
Aqui definimos as medidas, que serão apresentadas em forma
de uma lista de colunas como resultado final para cada combi-
nação de padrão encontrada. Aqui podemos usar funções junto
com as variáveis de padrão, além de operadores de navegação,
como o PREV que já vimos anteriormente. Vejamos um exemplo
na Listagem 2.
Edição 145 • SQL Magazine 27
Nessa listagem definimos qual será o retorno da nossa query em
formato de colunas ao encontrar uma combinação de padrão. As
duas primeiras colunas, definidas nas linhas 01 e 02, estão referen-
ciando a variável INI, que não tem definição, ou seja, poderá ser
qualquer linha (como o início do nosso padrão). Irá então exibir
tanto o preço inicial como a data inicial do nosso ponto de partida
do padrão. Logo após isso, nas linhas 03 a 06 temos a referência
às variáveis DES e SUB, que foram definidas na Listagem 1, que
é uma linha que tem um preço menor que a sua anterior (DES) e
uma linha com um preço maior que a anterior (SUB). Também irá
exibir a data e o preço da linha. Note o uso da função de navegação
LAST. Com o uso dessa função, garantimos que o valor retornado
será o último para cada padrão, tanto o de descida quanto o de
subida, sendo então o menor e o maior preço, respectivamente.
AFTER MATCH SKIP
Essa cláusula se refere a determinar o ponto para retomar a
busca por um padrão após ter encontrado uma correspondência.
Ou seja, a partir de qual linha poderá ser contado como início
para uma próxima combinação de padrão. Temos algumas opções
disponíveis, que são:
• AFTER MATCH SKIP TO NEXT ROW: será determinado como
ponto de partida a linha após a primeira linha do padrão atual
encontrado;
• AFTER MATCH SKIP PAST LAST ROW: será determinado
como ponto de partida a linha após a última linha do padrão
atual encontrado. Esse é o valor default;
• AFTER MATCH SKIP TO FIRST variavel_padrao: será deter-
minado como ponto de partida a primeira linha mapeada para
uma determinada variável de padrão;
• AFTER MATCH SKIP TO [LAST] variavel_padrao: será deter-
minado como ponto de partida a última linha mapeada para uma
determinada variável de padrão.
Deve-se ter alguns cuidados ao escolher o ponto de retorno para
a busca de uma nova combinação de padrão, pois poderá não ser
encontrado nada ou até mesmo ficar em um estado de loop infini-
to, gerando assim exceções. Um exemplo seria retomar para uma
variável padrão, mas se no DEFINE essa variável for opcional e
não tiver nenhuma linha mapeada para ela (não existindo assim
um ponto de retomada), será gerada uma exceção em tempo de
execução. Outro exemplo seria utilizar o ponto de retorno para
uma determinada variável sendo que ela também foi o início da
combinação de padrão atual, formando assim um loop infinito,
gerando também uma exceção em tempo de execução.
Algumas outras cláusulas importantes
Temos algumas outras cláusulas importantes a serem mencio-
nadas antesde termos um exemplo prático. Veja alguma delas:
• (ONE ROW | ALL ROWS) PER MATCH: aqui escolhemos se
para cada variável de padrão encontrada, serão exibidas todas as
linhas mapeadas ou apenas uma como um resumo;
• PARTITION BY coluna: dividimos as linhas em grupos de
acordo com os valores em comum na coluna especificada. Algo
similar ao GROUP BY;
• ORDER BY: ordena as linhas, com os seus grupos, para serem
localizados os padrões.
Essas últimas duas cláusulas são já bem conhecidas por quem
usa funções analíticas. Temos também algumas funções impor-
tantes para mencionar, que são:
• CLASSIFIER(): retorna qual foi a variável de padrão na qual
a linha foi mapeada;
• MATCH_NUMBER(): atribui um número em sequência para
cada padrão encontrado, retornando assim em qual padrão, da
sequência, aquela linha pertence.
Como os dados são processados
Após ter conhecido as cláusulas e funções mais importantes,
vamos ver como é o processamento de uma query com a cláusula
MATCH_RECOGNIZE em alguns passos simples:
1. A tabela será particionada em grupos de acordo com a cláusula
PARTITION BY, onde cada grupo tem o mesmo valor em uma
determinada coluna;
2. Cada partição será ordenada pelo ORDER BY;
3. Se inicia então em cada partição a busca pelo padrão definido
em PATTERN;
4. A busca se inicia na primeira linha e as seguintes para encon-
trar uma combinação como definida em PATTERN. Se não for
encontrado nada, a busca irá se iniciar na linha seguinte e assim
por diante. Caso seja encontrada uma combinação positiva, são
calculadas as expressões presentes na cláusula MEASURES;
5. São retornadas as quantidades de linhas de acordo com a
cláusula ONE ROW PER MATCH ou ALL ROWS PER MATCH;
6. E para finalizar, após uma combinação de padrão, a cláusula
AFTER MATCH SKIP irá informar aonde irá se retomar o processo
de pesquisa por mais uma combinação de padrão.
Criando o ambiente de testes
Para iniciar a demonstração do uso do MATCH_RECOGNIZE,
devemos criar uma tabela onde irão ficar os registros de testes,
que nada mais são que um histórico de preços de determinados
produtos. Após isso, serão inseridas algumas linhas para popular
a tabela e realizar uma query. É nessa tabela que iremos fazer a
busca pelos padrões. Na estrutura dessa tabela temos o ID, pro-
duto, preço e data da venda. Confira a Listagem 3.
Vamos analisar um pouco o conteúdo da tabela teste_pattern, que
possui apenas quatro colunas. Além do ID da venda, temos o pro-
duto, que são apenas dois, JAVA e ORACLEDB. Nas próximas duas
Listagem 2. Criando measures.
01 MEASURES INI.data AS data_inicio,
02 INI.preco AS preco_inical,
03 LAST(DES.data) AS data_menor_preco,
04 LAST(DES.preco) AS menor_preco,
05 LAST(SUB.data) AS data_menor_preco,
06 LAST(SUB.preco) AS menor_preco
Oracle 12c: Conheça a cláusula pattern matching
28 SQL Magazine • Edição 145
colunas temos a data da venda e o preço que o produto foi vendido.
Nas instruções em sequência, temos uma série de INSERTs, que
cobre um período de 18 dias e tem uma variação de preço dos dois
produtos já mencionados. Apenas com essas informações, pode-
mos criar e pesquisar por padrões. Iremos utilizar tudo o que foi
visto anteriormente na explicação de cada uma das cláusulas mais
comuns. Veja um primeiro exemplo na Listagem 4.
Listagem 3. Criando o ambiente de testes.
CREATE TABLE teste_pattern (
pattern_id NUMBER,
produto VARCHAR2(20),
data_venda DATE,
preco NUMBER
);
/
INSERT INTO teste_pattern VALUES(1, ‘JAVA’, sysdate, 20);
INSERT INTO teste_pattern VALUES(2, ‘ORACLEDB’, sysdate, 200);
INSERT INTO teste_pattern VALUES(3, ‘ORACLEDB’, sysdate+1, 190);
INSERT INTO teste_pattern VALUES(4, ‘ORACLEDB’, sysdate+2, 185);
INSERT INTO teste_pattern VALUES(5, ‘ORACLEDB’, sysdate+3, 190);
INSERT INTO teste_pattern VALUES(6, ‘ORACLEDB’, sysdate+4, 210);
INSERT INTO teste_pattern VALUES(7, ‘JAVA’, sysdate+5, 25);
INSERT INTO teste_pattern VALUES(8, ‘JAVA’, sysdate+6, 15);
INSERT INTO teste_pattern VALUES(9, ‘JAVA’, sysdate+7, 10);
INSERT INTO teste_pattern VALUES(10, ‘JAVA’, sysdate+8, 25);
INSERT INTO teste_pattern VALUES(11, ‘ORACLEDB’, sysdate+9, 210);
INSERT INTO teste_pattern VALUES(12, ‘ORACLEDB’, sysdate+10, 150);
INSERT INTO teste_pattern VALUES(13, ‘JAVA’, sysdate+11, 30);
INSERT INTO teste_pattern VALUES(14, ‘ORACLEDB’, sysdate+12, 180);
INSERT INTO teste_pattern VALUES(15, ‘ORACLEDB’, sysdate+13, 300);
INSERT INTO teste_pattern VALUES(16, ‘JAVA’, sysdate+14, 35);
INSERT INTO teste_pattern VALUES(17, ‘JAVA’, sysdate+15, 25);
INSERT INTO teste_pattern VALUES(18, ‘JAVA’, sysdate+16, 30);
INSERT INTO teste_pattern VALUES(19, ‘ORACLEDB’, sysdate+17, 250);
INSERT INTO teste_pattern VALUES(20, ‘ORACLEDB’, sysdate+18, 350);
Listagem 4. MATCH_RECOGNIZE.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 PARTITION BY produto
04 ORDER BY data_venda
05 MEASURES INI.data_venda AS data_inicio,
06 LAST(DES.data_venda) AS data_menor_preco,
07 LAST(DES.preco) AS menor_preco,
08 LAST(SUB.data_venda) AS data_maior_preco,
09 LAST(SUB.preco) AS maior_preco
10 ONE ROW PER MATCH
11 AFTER MATCH SKIP TO LAST SUB
12 PATTERN (INI DES+ SUB+)
13 DEFINE
14 DES AS DES.preco < PREV(DES.preco),
15 SUB AS SUB.preco > PREV(SUB.preco)
16 ) MR
17 ORDER BY MR.produto, MR.data_inicio;
18 /
Na linha 2 foi utilizada a cláusula MATCH_RECOGNIZE,
que possibilita criar e pesquisar por padrões. Logo nas linhas
3 e 4, foi definido que a partição será feita com base nos valo-
res da coluna produto, que no caso será duas partições, e que
cada partição será ordenada com base na coluna data_venda. As
variáveis de padrão foram definidas nas linhas 14 e 15 e utili-
zadas para criar o padrão na linha 12. Na linha 10 foi definido
que para cada padrão encontrado só será exibida uma linha e
não todas as linhas que foram mapeadas para as variáveis de
padrão. Já na linha 11 indicamos onde recomeçar pela busca
de um novo padrão após já ter encontrado um, no caso foi
escolhida a última linha mapeada como SUB, ou seja, a última
linha de um padrão poderá ser o início de outro padrão. E, para
finalizar, nas linhas 5 até 9 foram definidas as medidas que
serão apresentadas como resultado final em forma de colunas.
Veja esse resultado na Listagem 5.
Aqui podemos verificar o resultado da nossa primeira query.
Além das colunas que definimos na cláusula MEASURES, foi
retornada a coluna produto, que foi a condição da nossa cláu-
sula PARTITION BY. Os resultados estão ordenados de acordo
com a cláusula ORDER BY da linha 17 da Listagem 4. Foram
encontrados dois padrões para o produto JAVA, conforme as
linhas 2 e 3, e três padrões para o produto ORACLEDB, que
estão representados nas linhas 4 a 6. Para cada um dos padrões
encontrados é mostrada a data de início, do menor preço e do
maior preço, juntamente com os respectivos preços nessas
datas. Para cada padrão é exibida apenas uma linha, como
uma espécie de resumo. Podemos constatar esse resultado
verificando a Figura 2.
Compare os resultados da Listagem 5 com a Figura 2. Vamos
pegar um exemplo de padrão e comparar com o nosso gráfico.
Veja a linha 5 da Listagem 5. Esse padrão indica que o ponto
de partida foi no dia 03/06/2016, se olhar no gráfico, verá que
o valor do produto ORACLEDB, linha em laranja, era de 210,
após isso o valor do produto sofreu uma queda chegando a
ser cotado com o menor valor em 150, no dia 04/06/2016. Após
isso, o preço começou a subir tendo o valor de 180 e depois 300
como o valor mais alto no dia 07/06/2016. Isso foi um exemplo
de padrão em forma de "V", que foi definido como sendo o
PATTERN INI, DES+, SUB+.
Se for necessária mais informação no retorno do padrão, como
cada preço e cadadata que foi mapeado, deve ser utilizada a
cláusula ALL ROWS PER MATCH. Mas talvez fique difícil de
identificar cada linha com os seus respectivos padrões e o que
significa cada uma dessas linhas. Para auxiliar nisto, existem
duas funções que podem nos ajudar
a identificar cada linha.
Classifier e Match_number
Existem duas funções que podem
nos ajudar a identificar cada linha
retornada quando é utilizado ALL
ROWS PER MATCH, visto que o
Listagem 5. Resultado gerado.
01 PRODUTO DATA_INICIO DATA_MENOR_PRECO MENOR_PRECO DATA_MAIOR_PRECO MAIOR_PRECO
02 JAVA 30/05/16 01/06/16 10 08/06/16 35
03 JAVA 08/06/16 09/06/16 25 10/06/16 30
04 ORACLEDB 25/05/16 27/05/16 185 29/05/16 210
05 ORACLEDB 03/06/16 04/06/16 150 07/06/16 300
06 ORACLEDB 07/06/16 11/06/16 250 12/06/16 350
Edição 145 • SQL Magazine 29
Figura 2. Dados em Gráfico
número de linhas retornado pode ser muito grande para cada pa-
drão. A primeira função, a CLASSIFIER, retorna a variável padrão
na qual aquela linha foi mapeada. Já a função MATCH_NUMBER
retorna um número inteiro positivo em sequência, para cada
padrão encontrado, representando a qual padrão aquela linha
pertence. Para visualizar melhor como essas funções funcionam,
iremos analisar mais um exemplo.
Contudo, antes disso, devemos entender que ao mudar de
ONE ROW para ALL ROWS, o comportamento das medidas em
MEASURES pode ser alterado. Isso ocorre por que, por default, é
utilizado o modificador ou função de navegação RUNNING, que
significa que o valor calculado naquela linha não será considerado
o padrão inteiro, mas será considerada a linha corrente e as ante-
riores da mesma, mesmo que existam muitas linhas depois dela
que serão incluídas no mesmo padrão. Um exemplo disso seria
a medida do menor preço, que talvez não exiba o menor preço
que foi encontrado no padrão inteiro, mas sim o menor preço até
aquela linha. Para corrigir isso, caso não seja o desejado, deve-se
utilizar o modificador FINAL, que irá então considerar todas as
linhas do mesmo padrão. Observe agora a Listagem 6.
Vamos entender as alterações feitas nessa listagem. A primeira
modificação foi feita na cláusula MEASURES. Nela alteramos
algumas medidas, removemos algumas listagens e adicionamos
outras. Na linha 7 adicionamos o modificador FINAL, conforme
já foi discutido, e assim podemos comparar com o retorno da
medida na linha 6, que é a mesma medida, mas sem modificador
nenhum explícito, ou seja, utilizou o modificador default, que
Listagem 6. Utilização do CLASSIFIER, MATCH_NUMBER e FINAL.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 PARTITION BY produto
04 ORDER BY data_venda
05 MEASURES INI.data_venda AS inicio,
06 LAST(SUB.preco) AS maior_preco,
07 FINAL LAST(SUB.preco) AS maior_final,
08 MATCH_NUMBER() AS padrao,
19 CLASSIFIER() AS var_padrao
10 ALL ROWS PER MATCH
11 AFTER MATCH SKIP TO LAST SUB
12 PATTERN (INI DES+ SUB+)
13 DEFINE
14 DES AS DES.preco < PREV(DES.preco),
15 SUB AS SUB.preco > PREV(SUB.preco)
16 ) MR
17 ORDER BY MR.produto, MR.data_veda;
é o RUNNING no caso. Nas linhas 8 e 9 adicionamos o uso das
funções CLASSIFIER e MATCH_NUMBER para identificar me-
lhor o que cada linha representa, já que na linha 11 foi informado
para retornar todas as linhas por padrão, e não apenas uma como
no exemplo anterior. Observe o resultado agora na Listagem 7.
Agora podemos analisar todas as linhas em cada padrão.
A coluna PADRAO poderá nos ajudar nessa tarefa, já que com a
ajuda da função MATCH_NUMBER, podemos identificar a qual
padrão aquela linha pertence. Já para a coluna VAR_PADRAO foi
utilizada a função CLASSIFIER, que nos retorna para qual variável
de padrão aquela linha foi mapeada. Agora podemos saber quem
é o início, a descida de preço e depois a subida de preço.
Oracle 12c: Conheça a cláusula pattern matching
30 SQL Magazine • Edição 145
Listagem 7. Uso das funções CLASSIFIER e MATCH_NUMBER.
PRODUTO DATA_VENDA INICIO MAIOR_PRECO MAIOR_FINAL PADRAO VAR_PADRAO PATTERN_ID PRECO
JAVA 30/05/16 30/05/16 35 1 INI 7 25
JAVA 31/05/16 30/05/16 35 1 DES 8 15
JAVA 01/06/16 30/05/16 35 1 DES 9 10
JAVA 02/06/16 30/05/16 25 35 1 SUB 10 25
JAVA 05/06/16 30/05/16 30 35 1 SUB 13 30
JAVA 08/06/16 30/05/16 35 35 1 SUB 16 35
JAVA 08/06/16 08/06/16 30 2 INI 16 35
JAVA 09/06/16 08/06/16 30 2 DES 17 25
JAVA 10/06/16 08/06/16 30 30 2 SUB 18 30
ORACLEDB 25/05/16 25/05/16 210 1 INI 2 200
ORACLEDB 26/05/16 25/05/16 210 1 DES 3 190
ORACLEDB 27/05/16 25/05/16 210 1 DES 4 185
ORACLEDB 28/05/16 25/05/16 190 210 1 SUB 5 190
ORACLEDB 29/05/16 25/05/16 210 210 1 SUB 6 210
ORACLEDB 03/06/16 03/06/16 300 2 INI 11 210
ORACLEDB 04/06/16 03/06/16 300 2 DES 12 150
ORACLEDB 06/06/16 03/06/16 180 300 2 SUB 14 180
ORACLEDB 07/06/16 03/06/16 300 300 2 SUB 15 300
ORACLEDB 07/06/16 07/06/16 350 3 INI 15 300
ORACLEDB 11/06/16 07/06/16 350 3 DES 19 250
ORACLEDB 12/06/16 07/06/16 350 350 3 SUB 20 350
Agora vamos analisar em conjunto duas colunas, a MAIOR_
PRECO e MAIOR_FINAL. As medidas que essas colunas
fazem são as mesmas, a única diferença é que uma utiliza o
modificador FINAL e a outra, sem o modificador, utiliza o
RUNNING. Vamos pegar o primeiro padrão do produto JAVA
como exemplo. Repare que desde a primeira linha a coluna
MAIOR_FINAL já mostra o maior preço encontrado em todo
o padrão. Já a linha MAIOR_PRECO retorna NULL no início e
na descida. Já na subida, conforme ele encontra um preço maior
que o anterior ele vai retornando esse valor, até encontrar o
maior preço em todo o padrão, apenas na última linha do pa-
drão. Isso ocorre por que essa coluna usa o modificar default
RUNNING e ele verifica apenas a linha atual e as anteriores
que já foram analisadas.
Note também que algumas linhas foram mapeadas duas ve-
zes, uma em cada padrãodiferente. Um exemplo foi a linha com
o PATTERN_ID 15. Perceba que ela foi mapeada como SUB, úl-
tima linha, no segundo padrão do produto ORACLEDB e como
INI no terceiro padrão do mesmo produto. Esse comportamento
ocorrer a depender de como foi utilizada a cláusula AFTER
MATCH SKIP. No nosso exemplo foi para SKIP TO LAST SUB,
indicando que o ponto de partida para a procura de um novo
padrão será a última linha mapeada como SUB. Ou seja, essa
linha poderá ser contada como início de outro padrão.
Diferentes tipos de padrão
Até aqui, em todos os nossos testes e exemplos, foi conside-
rado um único tipo de padrão em forma de "V". Vejamos agora
como definir outros tipos de padrão, como um em forma de
"W", onde o padrão anterior que já utilizamos, em forma de "V",
ocorre duas vezes seguidas. Confira como ficaria essa query
na Listagem 8.
Listagem 8. Padrão em Forma de “W”.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 PARTITION BY produto
04 ORDER BY data_venda
05 MEASURES MATCH_NUMBER() AS padrao,
06 CLASSIFIER() AS variavel_padrao
07 ALL ROWS PER MATCH
08 AFTER MATCH SKIP TO LAST SUB
09 PATTERN (INI DES+ SUB+ DES+ SUB+)
10 DEFINE
11 DES AS DES.preco < PREV(DES.preco),
12 SUB AS SUB.preco > PREV(SUB.preco)
13 ) MR
14 ORDER BY MR.produto, MR.data_venda;
Para melhorar a compressão do retorno da query, a cláusula
MEASURES, nas linhas 5 e 6, foi modificada para retornar pouca
informação. Na linha 9 foi criado o nosso padrão, utilizando as
variáveis de padrão nas linhas 11 e 12. Lá foi informado que após
o início do padrão, o preço iria descer, depois subir e refazer o
processo de descer e subir, formando assim uma forma de "W"
no gráfico da Figura 2. Vamos analisar o retorno dessa query na
Listagem 9.
Essa listagem retorna dois padrões, um para cada produto,
onde foi encontrada uma combinação positiva do padrão em
forma de "W". Pode-se constatar isso observando o gráfico na
Figura 2. Podemos verificar isso também pela ordem em que
as variáveis de padrão retornadas pela função CLASSIFIER na
coluna VARIAVEL_PADRAO é retornada, sendo exatamente a
mesma ordem que aparece na cláusula PATTERN. Isso evidência
que as possibilidades de criar padrões diferentes são muitas.
Até agora, todos os nossos exemplos foram baseados no valor
de cada produto em uma determinada data.
Edição 145 • SQL Magazine 31
Listagem 9. Padrão em Forma de “W”.
PRODUTO DATA_VENDA PADRAO VARIAVEL_PADRAO PATTERN_ID PRECO
JAVA 30/05/16 1 INI 7 25
JAVA 31/05/16 1 DES 8 15
JAVA 01/06/16 1 DES 9 10
JAVA 02/06/16 1 SUB 10 25
JAVA 05/06/16 1 SUB 13 30
JAVA 08/06/16 1 SUB 16 35
JAVA 09/06/16 1 DES 17 25
JAVA 10/06/16 1 SUB 18 30
ORACLEDB 03/06/16 1 INI 11 210
ORACLEDB 04/06/16 1 DES 12 150
ORACLEDB 06/06/16 1 SUB 14 180
ORACLEDB 07/06/16 1 SUB 15 300
ORACLEDB 11/06/16 1 DES 19 250
ORACLEDB 12/06/16 1 SUB 20 350
Listagem 10. Padrão de quantidade de determinados produtos vendidos.
01 SELECT *
02 FROM teste_pattern MATCH_RECOGNIZE (
03 ORDER BY data_venda
04 MEASURES MATCH_NUMBER() AS padrao,
05 CLASSIFIER() AS variavel_padrao
06 ALL ROWS PER MATCH
07 AFTER MATCH SKIP PAST LAST ROW
08 PATTERN (JAV{2,4} ORA{1,2})
09 DEFINE
11 JAV AS JAV.produto = ‘JAVA’,
12 ORA AS ORA.produto = ‘ORACLEDB’
13 ) MR
14 ORDER BY MR.data_venda, MR.padrao;.
15
16 DATA_VENDA PADRAO VARIAVEL_PADRAO PATTERN_ID PRODUTO PRECO
17 30/05/16 1 JAV 7 JAVA 25
18 31/05/16 1 JAV 8 JAVA 15
19 01/06/16 1 JAV 9 JAVA 10
20 02/06/16 1 JAV 10 JAVA 25
21 03/06/16 1 ORA 11 ORACLEDB 210
22 04/06/16 1 ORA 12 ORACLEDB 150
23 08/06/16 2 JAV 16 JAVA 35
24 09/06/16 2 JAV 17 JAVA 25
25 10/06/16 2 JAV 18 JAVA 30
26 11/06/16 2 ORA 19 ORACLEDB 250
27 12/06/16 2 ORA 20 ORACLEDB 350
Vejamos agora, na Listagem 10, um exemplo de padrão um pou-
co diferente, referente à quantidade de determinados produtos
vendidos em uma certa sequência.
O primeiro item a se notar é que não criamos partição nenhuma.
Nas linhas 11 e 12 foram definidas duas variáveis de padrão onde
verificamos qual foi o produto vendido. Após isso, o padrão foi
criado na linha 8 utilizando essas mesmas variáveis já citadas.
Aqui utilizamos o poder das expressões regulares, onde defini-
mos que primeiro teria que ter sido vendido entre dois e quatro
produtos JAVA e, após isso, entre um e dois produtos ORACLEDB.
Toda essa verificação é feita respeitando a ordem das linhas que
foi imposta na linha 3, no ORDER BY. A última diferença desse
exemplo para os anteriores se encontra na linha 7, onde foi defi-
nido que a busca de uma nova combinação de padrão será feita
na linha seguinte à última linha do padrão atual encontrado.
Sendo assim, uma mesma linha não poderá ser mapeada em dois
padrões diferentes.
Todos os exemplos utilizados aqui foram apenas para ajudar no
entendimento dessa nova cláusula, que em um primeiro momento
pode parecer um pouco complicada, mas que na verdade não é.
A versão 12c do Oracle trouxe grandes avanços na sintaxe do SQL,
um bom exemplo disso é a nova cláusula MATCH_RECOGNIZE.
Com ela podemos fazer tudo nativamente, a criação e a pesquisa
de padrões, ganhando assim bastante em performance, escalabi-
lidade e manutenção. Caso seja necessário, sempre utilize essas
novas cláusulas e, para formas mais avançadas do que as apre-
sentadas aqui, consulte a documentação do Oracle.
Tércio Costa
terciosilvacosta@gmail.com - https://oraclepress.wordpress.com/
Formado em Ciências da Computação pela UFPB com experi-
ência em Servidores Windows Server e Linux e banco de dados
Oracle desde 2008 juntamente com os seus serviços. Desenvolvimento
de Sistemas em Java SE com banco de dados Oracle e MySQL. Certificado
Oracle Database SQL Certified Expert, mantendo o blog http://oraclepress.wordpress.
com/ reconh ecido pela OTN onde também sou articulista e no portal http://www.
profissionaloracle.com.br/gpo
Autor
32 SQL Magazine • Edição 145
Existem diversas razões pelas quais será necessária a extração de
grande volume de dados, a principal e muito utilizada é para backup. No
entanto, existem diversas outras, como salvar arquivos históricos antes
de serem removidos, atualização de ambientes DW (Data Warehouse),
atualizar ambientes de desenvolvimento de homologação e afins.
O artigo expõe conceitos e práticas de uso do utilitário Oracle Data
Pump Export (EXPDP) e Data Pump Import (IMPDP) na atualização de
schemas, geração de backup e outros em servidores demonstrativos de
produção, homologação e desenvolvimento. O artigo é muito útil para
entender melhor sobre os conceitos de exportação e importação de
dados no Oracle e conhecer, na prática, alguma das possibilidades de
utilização dessa ferramenta, que é bastante útil no dia a dia de um DBA.
Cenário
Atualização de schemas
no Oracle 11gR2 com o
Oracle Data Pump
No curso normal dos eventos em um banco de da-dos, os comandos DMLs (INSERT, UPDATE e DELETE) e SELECT são utilizados para extrair
e inserir dados no banco de dados, porém há situaçõesem que será necessário um método muito mais rápido
e eficiente para realizar operações de grande volume.
Antes da versão Oracle 10g, era utilizado o utilitário
export e import, que realizava a extração/importação
dos dados como um processo de usuário no sistema
operacional, usando uma sessão para recuperar os dados
e transformá-los em arquivos, para depois salvá-los no
sistema operacional.
Após a chegada da versão Oracle 10g, o utilitário
padrão passou a ser o Oracle Data Pump usando os
clientes expdp e impdp, que rodam como um processo de
servidor. Isso melhora consideravelmente o desempenho
da extração e importação de dados, pois, por ser um
processo de servidor, o acesso aos arquivos de dados e a
SGA serão diretos, não sendo mais necessário recuperar
os mesmos através de uma sessão como era realizado
nos utilitários anteriores.
O Oracle Data Pump é também base para muitos outros
recursos-chave no banco de dados Oracle, como AWR,
replicação Streams-Based, Logical Standby, Grid e em
tablespaces transportáveis (Transportable tablespace).
No entanto, não é o objetivo deste artigo detalhar cada
um desses recursos.
Além do ganho em performance, os utilitários expdp
e impdp trouxeram ganho na administração. É possível
gerenciar o andamento de vários jobs se conectando a
eles através de um usuário e senha que foram utilizados
na execução da rotina ou com algum usuário que tenha
os privilégios necessários para administrar o job.
EstE artigo é do tipo mEntoring
saiba mais: www.dEvmEdia.com.br/mEntoring-saibamais
Após conectado a um determinado job, o mesmo poderá ser
consultado, pausado, reiniciado, interrompido, ou até mesmo ter
alguns parâmetros alterados. Iremos realizar a administração de
um job mais adiante.
Quando um job do Data Pump é iniciado, ao menos dois pro-
cessos se iniciam: um processo Data Pump Master (DMnn) e
um ou mais processos Worker (DWnn). Como o nome sugere, o
processo mestre irá controlar os outros processos workers. Se for
utilizado paralelismo, cada DWnn poderá fazer uso de dois ou
mais servidores de execução paralela (Pnnn). O processo interno
do Oracle Data Pump não é o escopo desse artigo, sendo assim,
não entraremos em mais detalhes referentes ao funcionamento
interno do utilitário.
Modos de exportação usando Oracle Data Pump
O Oracle Data Pump possui alguns modos específicos de
exportação de dados que podem ser especificados na linha de
comando ou usando o arquivo de parâmetros. Os modos que
estão disponíveis para o Data Pump Export são:
• “FULL EXPORT MODE”;
• “SCHEMA MODE”;
• “TABLE MODE”;
Edição 145 • SQL Magazine 33
exportação deverá deter os privilégios necessários para que seja
possível exportar todas as tabelas contidas nas tablespaces que
forem especificadas, caso contrário, o usuário não conseguirá
exportar tabelas que não são de sua propriedade.
O modo “TRANSPORTABLE TABLESPACES” é especificado
utilizando o parâmetro TRANSPORT_TABLESPACES. Nesse
modo, a estrutura dos objetos será exportada assim como a
estrutura dos objetos que são dependentes daqueles que estão
armazenados no conjunto de tablespaces especificado. Nesse
modo, existem algumas restrições como colunas que estiverem
encriptadas não serão exportadas.
Não se pode exportar uma tablespace e importá-la em uma ver-
são inferior à que foi exportada. O banco de dados destino deve
ter a mesma ou uma versão superior ao banco de dados para onde
que estão sendo exportada as tablespaces.
Considerações de rede
Podemos especificar uma string de conexão quando formos
executar o utilitário Data Pump Export. Este identificador pode
especificar uma instância de banco de dados diferente do banco
em que a variável SID está definida no momento. O identificador
pode ser um alias contido no arquivo tnsnames.ora que aponta
para um banco específico.
A exportação irá utilizar o TNS para se conectar ao banco de
dados e, através da autenticação de usuário e senha, será realizada
a conexão no banco e então iniciado o processo de exportação dos
dados. Para utilizar o TNS na execução da exportação de dados,
é necessário que o listener esteja ativo e registrado.
Um exemplo simples de como utilizar o TNS seria: “User/
password@TNS_NAME”. Assim, o cliente local se conectará ao
banco de dados destino e realizará o procedimento. A especifica-
ção de uma string de conexão é diferente da utilização do parâme-
tro NETWORK_LINK, o mesmo será utilizado posteriormente.
O Oracle Data Pump permite filtrar dados de tabelas e os me-
tadados de objetos para ajudar a limitar os tipos de informação
que estão sendo exportados. Os filtros são divididos em filtros
de dados e metadados.
O filtro de dados é realizado através dos parâmetros “QUERY”
e “SAMPLE”, que especificam as restrições daqueles objetos que
serão exportados.
O parâmetro SAMPLE é usado para exportar ou importar
uma determinada porcentagem de dados, seja de uma tabela
específica ou do banco de dados inteiro (não iremos usá-lo
nos exemplos). Uma breve descrição de como usar ele seria:
“SAMPLE=”HR”.”EMPLOYEES”:70”. Esse parâmetro iria forçar
o Data Pump a exportar somente 70% dos dados da tabela EXM-
PLOYEES no schema HR.
Os filtros de dados podem ocorrer indiretamente por causa de
um filtro de metadados. Ele poderá incluir ou excluir determina-
das tabelas com qualquer linha de dados associado. Os filtros de
metadados são implementados através dos parâmetros INCLUDE
e EXCLUDE. Os parâmetros INCLUDE e EXCLUDE são mutu-
almente exclusivos, independendo um do outro.
• “TABLESPACE MODE”;
• “TRANSPORTABLE TABLESPACE MODE”.
Neste artigo iremos utilizar alguns desses modos, os demais
poderão ser consultados na documentação de referência da
Oracle 11gR2.
O modo “FULL EXPORT MODE” equivale à exportação
completa do banco de dados, todos os dados e meta dados que
estiverem no banco de dados, sendo uma exportação de todos os
schemas e objetos presentes no banco. Para a execução do Data
Pump Export nesse nível, é necessário que o usuário que estiver
executando a rotina de exportação tenha o privilégio DATA
PUMP_EXP_FULL_DATABASE.
O modo de exportação por schema, “SCHEMA MODE”, ocorre
quando é especificado o parâmetro schemas e equivale à expor-
tação específica de alguns determinados que forem especificados
na linha de comando ou através dos arquivos de parâmetro.
Esse modo é o padrão, sendo necessário ter o privilégio DATA
PUMP_EXP_FULL_DATABASE para então poder especificar uma
lista de schemas a serem exportados.
Se o usuário utilizado na execução da rotina não tiver os
privilégios necessários para exportação completa (FULL) do
banco de dados, o mesmo poderá somente exportar seu pró-
prio schema.
Objetos que estejam cruzados entre schemas, como uma trigger
que está habilitada para uma tabela residente em um determina-
do schema, mas que pertence a outro que não for especificado
no momento da exportação, não serão exportados. A menos que
o schema em que o objeto pertence esteja ou seja adicionado na
lista dos exportados. É possível especificar todos eles da lista um
a um ou através do parâmetro “FULL”, o qual irá exportá-los no
banco, exceto se houver alguma restrição utilizada com o parâ-
metro EXCLUDE.
O modo de exportação conhecido como "TABLE MODE" é es-
pecificado utilizando o parâmetro TABLES. Nesse modo, somente
aquelas tabelas que forem especificadas serão exportadas além de
outros objetos pertencentes ao schema em questão. Para utilização
desse modo, também é necessário que o usuário tenha o privilégio
DATA PUMP_EXP_FULL_DATABASE para exportar tabelas que
não estejam no próprio schema, caso contrário, o usuário poderá
exportar somente suas próprias tabelas.
As definições dos tipos de colunas presentes na tabela não serão
exportadas, uma vez que deverão existirno banco de dados de
destino. Assim como o modo de exportação de schemas, o modo
de exportação de tabelas também não exporta referências de
schemas cruzados.
O modo de exportação de "TABLESPACES", é especificado com
a utilização do parâmetro TABLESPACES. Nesse modo, somente
as tabelas contidas no conjunto de tablespaces especificado se-
rão exportadas, se uma determinada tabela é exportada, então
os objetos que dependem da mesma também serão exportados.
É exportado tanto a estrutura física (meta dados) quanto os dados
propriamente ditos. O usuário que executar o procedimento de
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
34 SQL Magazine • Edição 145
Os filtros de metadados incluem ou excluem objetos de uma
operação de exportação.
Por exemplo: podemos especificar o modo “FULL”, mas espe-
cificar alguns filtros de metadados para, por exemplo, excluir
determinados packages. Diversas adaptações poderão ser feitas
com a base de alguns dos principais parâmetros utilizados e
descritos neste artigo.
O Oracle Data Pump gera três tipos de arquivo: arquivos de log,
arquivos binários do Oracle (dumps), e os arquivos SQL (Structured
Query Language). O arquivo SQL gerado são instruções DDL's que
descrevem os objetos no banco que foram ou serão exportados e/
ou importados. É possível exportar os objetos sem nenhum dado
(somente a estrutura física) para documentação do banco ou para
criação dos mesmos em outros ambientes, entre outros fins.
Os arquivos dumps unem todos os dados exportados em tags
XML formatadas, o que causa um overhead nesses arquivos
quando for descrever os dados. Os arquivos de logs contêm
as informações referentes ao processo de exportação ou de
importação de dados e ficam armazenados no diretório que for
especificado no comando.
O Oracle Data Pump requer que um diretório seja especificado
e que o usuário usado na inicialização do job tenha os privilégios
corretos. A versão do Oracle 11g que será abordada neste artigo já
vem com alguns diretórios configurados. No entanto, não basta
que os diretórios estejam criados no banco, os usuários que forem
executar operações utilizando o mesmo deverão receber os privi-
légios de leitura e escrita nesses diretórios para que os arquivos
possam ser criados no sistema operacional.
Os diretórios são mapeados internamente no banco Oracle no
momento da criação do mesmo com o comando CREATE OR RE-
PLACE DIRECTORY. Para este artigo, iremos criar um diretório
específico mapeando um diretório já criado no sistema e nome-
ando o mesmo para facilitar na visualização e identificação do
arquivo no sistema operacional.
Estudo de caso
Para demonstrar como iremos atualizar os schemas no Oracle
usando o Data Pump, serão utilizados uma VM Oracle Linux
Server Release 6.3 e o Oracle Enterprise Edition 11.2.0. Vale lembrar
que a utilização de alguns recursos requer a licença de uso e só
estão disponíveis na versão Enterprise Edition, como paralelismo,
compressão e encriptação.
Vamos iniciar criando um diretório no banco de dados Oracle,
como mostra a Figura 1, executando o seguinte comando:
CREATE OR REPLACE DIRECTORY DUMP_DIR AS ‘/u02/app/backup/’;
O comando OR REPLACE é utilizado para que não seja emitido
um erro, reportando a existência do diretório. Caso o mesmo já
exista, ele será recriado com o novo DIRECTORY_PATH que estiver
no comando. É muito importante que seja analisada a localização
ideal para a criação do diretório tendo em vista que serão arma-
zenados arquivos de grande volume de dados.
Figura 1. Sintaxe do comando de criação de diretórios
Vale lembrar também que o Oracle não verifica se o diretório
realmente existe no momento da criação. Para ter certeza de que o
diretório será utilizado, crie-o antes de apontar com um diretório
no banco de dados. A criação do diretório pode ser consultada
através da view DBA_DIRECTORIES, conforme a Figura 2.
Figura 2. Verificando a criação do diretório
Com o diretório criado, podemos iniciar a explicação dos pa-
râmetros que serão utilizados na exemplificação de uma atuali-
zação de schemas e também outros parâmetros que poderão ser
utilizados em outros cenários.
Vamos iniciar usando o schema SH para explicar uma maneira
de exportar o próprio schema usando o utilitário Oracle Data
Pump Export (expdp). A criação do usuário não é necessária caso
os schemas de exemplos tenham sido criados junto com o banco
de dados.
O usuário que for realizar a exportação deverá ter privilégios
de leitura e escrita sobre o diretório em que foi criado no Oracle.
Vamos tentar realizar a exportação sem os devidos privilégios.
Inicialmente, vamos realizar a primeira exportação com o usuário
SH com todos os privilégios revogados (somente foi atribuído o
privilégio CREATE SESSION).
A Figura 3 representa o privilégio do usuário SH que será usado
para exportar seu próprio schema.
Figura 3. Privilégios do usuário SH
Como esperado, o comando falhou por conta de privilégios
(Figura 4), mas os privilégios de leitura e escrita já foram conce-
didos ao usuário SH. Por que ainda assim falhou por esse motivo?
O usuário em questão, que será exportado, precisará de alguns
privilégios além desse, como cota de gravação em uma determi-
nada tablespace.
Além disso, será necessário que o schema tenha privilégios
de criação de objetos que podemos conceder através da role
RESOURCE, que possui alguns privilégios específicos inclusive,
Edição 145 • SQL Magazine 35
dentre eles, o UNLIMITED TABLESPACE que garante ao schema
a gravação em qualquer tablespace no banco de dados (o que é
um erro de administração e deverá ser revogado).
Vamos alterar o schema SH, oferecer cota ilimitada na tablespace
USERS a ele e também conceder o privilégio de RESOURCE e re-
vogar o UNLIMITED TABLESPACE. Após conceder esses passos,
o usuário RH possuirá os privilégios apresentados na Figura 5.
Figura 5. Após revogação do privilégio UNLIMITED TABLESPACE
Agora será necessário conceder privilégio de cota em uma de-
terminada tablespace. Nesse caso, vamos definir na tablespace
USERS que é default na instalação dos schemas de exemplo, como
mostra a Figura 6.
Vamos novamente testar a exportação do schema usando o SH.
Para isso, execute o seguinte comando:
expdp sh/oracle@NEW directory=DUMP_DIR dumpfile=schema_sh.dmp
logfile=exoprt_sh.log schemas=SH
Dessa vez o comando é executado corretamente e gera o arquivo
no diretório DUMP_DIR, como mostra a Figura 7.
Bom, inicialmente a primeira exportação ocorreu com sucesso.
Os parâmetros utilizados que não foram descritos ainda são
DUMPFILE e LOGFILE.
Figura 4. Erro ao realizar a exportação
Figura 6. Adicionando cota ilimitada na tablespace users ao schema SH
Figura 7. Sucesso na exportação do schema SH
O parâmetro DUMPFILE é utilizado para especificar o nome do
arquivo que será gerado. Dessa forma, não foram especificados
múltiplos arquivos para serem criados, mas poderíamos utilizar
o parâmetro “%U” junto com o nome do arquivo de dump (dump-
file) e especificar outro parâmetro FILESIZE com o tamanho dos
arquivos para que fosse distribuído em arquivos com tamanhos
definidos.
O parâmetro LOGFILE especifica o nome do arquivo de log da
rotina. O mesmo será criado no diretório que estiver mapeado pelo
DUMP_DIR no banco de dados e registrará cada uma das etapas
em que o job Data Pump estiver. Já no parâmetro SCHEMAS,
especificamos somente os schemas que serão exportados. Nesse
exemplo somente SH foi especificado.
Usamos também uma configuração de rede para se conectar à
instância NEW usando o TNS @NEW. É interessante usar o TNS
para ter certeza de que a instância correta está definida como
alvo da operação. Após a criação do arquivo, o mesmo poderá ser
reutilizado em qualquer outro servidor que tenha um utilitáriocom versão compatível para realizar o import.
Nesse primeiro exemplo foi usado o SH para exportar seu pró-
prio schema. O mesmo não possui privilégios especiais, como
o de DBA que é o mais indicado para realizar determinados
procedimentos de administração no banco de dados. Portanto,
vamos usar o usuário TESTE para os próximos exemplos com
privilégio de DBA atribuído a ele.
Uma dica é que seja muito bem analisado o comando, a sintaxe
da instância alvo da importação, os schemas alvos, as tabelas
alvo e os dados para que não tenhamos maiores problemas (por
exemplo, alguma atualização a mais ou no lugar errado).
Um parâmetro que ajuda bastante a compreender o que será feito
na importação é SQLFILE. Ele é utilizado para criar um arquivo
SQL com todas as linhas de comandos que serão executadas du-
rante a importação do arquivo de dump.
Assim, quando quisermos saber o que será importado e quais
objetos serão importados, ou até mesmo para compreender a
estrutura do banco de dados, usamos esse parâmetro. A seguir,
realizaremos a criação de um arquivo SQL com o parâmetro
SQLFILE.
O uso do SQLFILE só pode ser gerado através de um DUMP,
dessa forma, somente antes de uma importação. É importante
lembrar que, em relação ao uso do SQLFILE, quando o mesmo
for especificado, somente o arquivo é criado, não há nenhuma
importação. A Figura 8 apresenta um exemplo de uso do SQLFILE.
O arquivo SQL gerado é demonstrado na Figura 9. Esse arquivo
é criado na mesma localização dos arquivos de log e dos arquivos
de dumps, aquele que estiver especificado na criação do diretório.
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
36 SQL Magazine • Edição 145
Figura 9. Arquivo SQL gerado com o uso do parâmetro SQLFILE
Figura 8. Uso do parâmetro SQLFILE
Os comandos SQL que serão executados no momento da im-
portação estarão presentes nesse arquivo. Em alguns cenários, é
importante levar os privilégios do usuário ao novo ambiente, para
isso, precisamos verificar quais privilégios o schema em questão
tem no ambiente de origem e criar, através dessas informações,
concessão de privilégios no novo ambiente.
O SELECT apresentado na Listagem 1 recupera informações
referentes à concessão de privilégios provenientes de outros
schemas ao usuário SH que estamos testando. É um SQL dinâ-
mico e retornará comandos que deverão ser executados no novo
ambiente, caso seja solicitado pelo cliente. Lembrando que o uso
de privilégios não é o foco do artigo. Para entender mais sobre
privilégios consulte a docu mentação oficial da Oracle.
Listagem 1. Recuperação de informações referentes a concessão de privilégios
SELECT ‘GRANT ‘||privilege||’ ON ‘||owner||’.’||table_name||’ TO ‘||grantee||’;’
FROM DBA_TAB_PRIVS
WHERE grantee = ‘&NOME_SCHEMA’;
Serão apresentados a partir de agora alguns parâmetros que po-
demos usar na exportação e importação de dados com o utilitário
Oracle Data Pump para atender uma solicitação de replicação de
um determinado schema no banco de dados na qual será neces-
sário manter o de origem, além de criar um novo com um nome
diferente, porém com os mesmos objetos. Para isso, podemos usar
o parâmetro REMAP_SCHEMA.
Edição 145 • SQL Magazine 37
O parâmetro REMAP_SCHEMA serve
para tornar aquele schema especificado em
um novo com um novo nome. Caso seja
necessário, é possível redirecionarmos a
tablespace em que o novo schema irá gra-
var seus dados também, para isso, teremos
que usar o parâmetro REMAP_TABLE-
SPACE que gravará nessa nova tablespace
os dados do schema de origem usado na
importação.
Um exemplo de utilização do parâmetro
REMAP_SCHEMA e REMAP_TABLESPA-
CE será demonstrado a seguir. Imagine-
mos que o cliente pediu para clonarmos
um determinado schema. O schema HR
de um servidor origem (NEW), simbo-
lizando um servidor de produção, para
outra instância, simbolizando o servidor
de homologação (ORCL).
Esse, por sua vez, em produção, está
usando a tablespace USERS e o cliente
quer que o clone desse schema se cha-
me HR_HML e que grave na tablespace
EXAMPLE. Para atendermos a essa soli-
citação, o procedimento deverá:
• Exportar o schema HR de produção (ver
código a seguir);
• Importar o schema HR remapeando
tanto ele, quanto a tablespace;
• Compilar os objetos inválidos.
Podemos ver um exemplo no código a
seguir:
expdp teste/teste directory=DUMP_DIR dumpfile=HR_
prod.dmp logfile=HR_prod.log schemas=HR cluster=no
reuse_dumpfiles=yes
Veja que estamos usando o parâmetro
CLUSTER=NO que força o utilitário a
usar somente um node em um servidor
RAC. Esse parâmetro é usado para não
onerar o banco de dados no momento da
rotina. A Figura 10 mostra a finalização
da exportação.
Com o arquivo DUMP disponível, po-
demos realizar a importação utilizando
os parâmetros que serão necessários para
atender à solicitação do cliente.
Temos assim configurado duas instân-
cias de testes nesse servidor: uma com o
SID NEW e outra com o SID orcl. O DUMP
de origem assume que a instância NEW
se trata de uma instância de produção e
que a instância ORCL é a de homologação,
nesse caso, a instância alvo da atualização
(Figura 11).
A exportação foi realizada usando o
TNS @NEW como pode ser verificado
no comando de exportação realizado.
Nosso exemplo de importação, a pedido
do cliente, será na instância ORCL, então
devemos trocar o TNS e usar @ORCL
para que a importação ocorra na instância
correta sem ter que se preocupar com o
ORACLE_SID que estiver definido na
variável de sistema.
Os comandos de importação seguem a
mesma ideia daqueles de exportação que
usamos. Portanto, a sintaxe é semelhante e
fácil de entender. Antes de falar da impor-
tação, é interessante falar dos privilégios e
da segurança de conceder os mesmos em
ambientes reais.
Para realizar os procedimentos de expor-
tação e importação de diversos schemas, o
usuário que estiver realizando a chamada
ao utilitário deverá ter os privilégios IMP_
FULL_DATABASE e EXP_FULL_DATA-
BASE. Nesse caso, é fortemente recomen-
dado que o procedimento seja realizado por
usuários DBAs, para que não comprometa
a segurança no banco de dados, pois caso
sejam concedidos os privilégios citados
a um usuário schema qualquer de uma
aplicação e o mesmo realizar diariamente
importações no banco de dados, ele poderá
afetar outros schemas com um simples
erro de chamada e, com isso, comprometer
ou perder diversos dados ou até schemas
inteiros em ambientes reais.
O usuário usado para a importação
apresentada na Figura 12 é um usuário
com privilégios de DBA e será usado para
os próximos testes.
No procedimento apresentado, foi uti-
lizado paralelismo com o parâmetro PA-
RALLEL=2, que é uma feature de versões
Enterprise Edition e que pode ser usada
com o coringa “%U” para criar vários
arquivos de dumps paralelos, conforme
o número de paralelismo ou de filesize.
O schema HR_HML foi criado na instância
ORCL com sua tablespace remapeada para a
tablespace solicitada pelo cliente. É interes-
sante lembrar que, como o cliente pediu um
clone idêntico ao schema HR, isso significa
que as permissões que ele recebeu no am-
biente de produção deverão ser replicadas no
ambiente de homologação. Na Figura 13 ele é
gerado com o mesmo comando que executa-
mos anteriormente, porém com o parâmetro
SQLFILE para identificar os GRANTS que
o usuário HR trouxe no arquivo de DUMP
de produção. Observe que os privilégios que
vieram são somente aqueles concedidos pelo
HR, não recebido por ele.
A Figura 14 contém uma adaptação
do SQL dinâmico que foi demonstrado
anteriormente. Ele recupera os grants
que o schema HR recebeu no ambiente
de produção e altera o nome do schema
HR para HR_HML para que sejam repli-
cados no ambiente alvo conforme mostra
aListagem 2.
Figura 10. Finalização da exportação
Figura 11. Instâncias em execução no servidor de testes
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
38 SQL Magazine • Edição 145
Figura 12. Clonagem do schema HR de produção para uma instância de homologação
Figura 13. Grants do usuário HR que serão remapeados ao usuário HR_HML
Figura 14. Privilégios do schema HR no ambiente de produção
Listagem 2. Recuperação dos grants e alteração do nome do schema
select grantee schema,
‘GRANT ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘||grantee||’_HML’||’;’ grants,
(select name from v$database) instancia
from DBA_TAB_PRIVS where grantee = ‘HR’;
Figura 15. Falha na importação do schema HR remapeando para HR_HML
No caso, o HR recebeu somente esse
privilégio no nosso exemplo com o sche-
ma SH, no entanto, em ambientes reais,
a quantidade de permissões é grande e
deverá ser gerada com a ajuda de um SQL
dinâmico.
Como a exportação do schema foi realiza-
da pelo usuário HR, o mesmo não pode ser
importado no novo servidor, remapeando
para um novo schema, porque o usuário
HR não tem privilégios para a criação do
usuário caso haja a necessidade. Portanto,
a tentativa de importar, remapeando o
schema HR para HR_HML, falha como
pode ser visto na Figura 15.
Para que o procedimento seja concluí-
do, devemos exportar o schema HR do
ambiente de origem com um usuário que
tenha privilégios de DBA para que o sche-
ma HR_HML seja criado no momento da
importação na nova instância.
Assim, é necessário realizar o procedi-
mento com usuário que tenha os privilé-
gios necessários, no caso, foi utilizado o
nosso usuário criado para testes o schema
TESTE.
Após exportar novamente o schema
HR, agora com o schema TESTE em pro-
dução, o DUMP deverá ser copiado para
a localização do diretório DUMP_DIR na
nova instância e, então, reexecutado o
procedimento de importação. Feito isso,
o schema HR_HML será criado na nova
instância e terá os mesmos objetos que o
HR na instância de produção. Se o proce-
dimento de exportação for realizado com
usuário DBA, os privilégios de sistema
já estão no arquivo de DUMP e aqueles
privilégios de objetos de outros schemas
foram gerados através do SQL dinâmico
que criamos. A Figura 16 compara as
duas instâncias, onde todos os objetos
foram recriados. Nesse exemplo o schema
HR possui uma variedade bem limitada
de objetos, se comparado a schemas de
produção reais, que em alguns casos
deverão ter um tratamento diferenciado
para os dados.
Com o NETWORK_LINK, podemos
importar um schema sem ter a necessi-
dade de gerar o arquivo de dump (.dmp).
Fazemos essa importação especificando o
parâmetro NETWORK_LINK, disponí-
vel desde a versão 10g do Oracle. Vamos
agora mostrar um exemplo de uso do
NETWORK_LINK.
Suponha que é necessário atualizar o
schema SH no ambiente de homologa-
ção (ORCL) com os dados da instância
do banco de produção (NEW). O cliente
deseja atualizar as tabelas do schema SH
de homologação com os dados atuais de
produção. O cliente quer que na tabela
promotions, coluna promo_name, os da-
Edição 145 • SQL Magazine 39
dos possuam em alguma parte a palavra
INTERNET e/ou MAGAZINE. Também
quer que o total vendido da tabela sales
AMOUNT_SOLD seja maior ou igual a
500 e que os produtos vendidos estejam no
intervalo de 25 a 110. Além disso, também
quer que sejam somente atualizados os
dados, sem estruturas, sem privilégios,
sem estatísticas e que o job seja nomeado
por IMPDPDATAv1.
Para atender à solicitação, deve-se usar
o modo de importação em rede com o
parâmetro network_link. Para usar o
parâmetro, devemos ter configurado os
links entre os bancos de dados. Nesse caso,
somente um link é necessário, pois a atua-
lização é somente de uma via, de produção
para homologação, mas nos servidores de
teste foram configurados dois links para
explicar melhor como a ligação deve ser
realizada (Figura 17).
É importante ter configurado o tnsnames
apontando para os servidores alvos para
que a conexão seja realizada com sucesso.
Um teste simples de conexão via TNS pode
ser realizada usando o comando TNSPING
(nome do alias), que corresponde à ligação
entre o servidor e outro servidor remoto ou
o mesmo, como mostra a Figura 18.
Para o exemplo vamos usar os seguintes
parâmetros:
• USER;
• DIRECTORY;
• CONTENT;
• QUERY;
• EXCLUDE;
• NETWORK_LINK;
• LOGFILE;
• PARFILE.
Inicialmente vamos tratar sobre o PAR-
FILE que é um arquivo que contém os
comandos que serão executados pelo utili-
tário. Ele é muito usado para organizar me-
lhor os parâmetros e tornar compreensível
um comando de exportação ou importação
que seja complexo demais.
Figura 16. Imagem comparativa entre os schemas nas instâncias de produção e homologação
Figura 17. Teste de ligação usando os database links
Os comandos apresentados na Listagem 3
estão presentes no arquivo de testes
chamado imdpdatav1.par que será usa-
do depois na chamada do comando de
importação.
Embora a solicitação seja de produção
para homologação, o processo de im-
portação deve ser executado no servidor
de homologação, que irá se conectar ao
servidor de produção e importará os
dados do schema SH conforme solicitado
(Figura 19).
Vamos agora explicar alguns dos parâ-
metros que foram usados.
• PARFILE: serve para armazenar os
comandos que serão usados na chamada
dos utilitários expdp ou impdp;
• NETWORK_LINK: usado para es-
tabelecer uma conexão remota com um
determinado banco de dados de origem
para realizar as operações;
• QUERY: serve para filtrar os dados que
serão exportados ou importados;
• EXCLUDE: serve para excluir aqueles
objetos que não devem ser considerados
no arquivo de DUMP ou na importação;
• JOB_NAME: usado para nomear a roti-
na de importação ou de exportação;
Listagem 3. Comandos chamados durante a importação
NETWORK_LINK=dblink_new
DIRECTORY=DUMP_DIR
SCHEMAS=SH
QUERY=SALES:’”WHERE AMOUNT_SOLD >= 500 AND PROD_ID BETWEEN 25 AND 110”’
QUERY=PROMOTIONS:’”WHERE PROMO_NAME LIKE UPPER(‘%INTERNET%’) OR PROMO_NAME LIKE
UPPER(‘%MAGAZINE%’)”’
LOGFILE=IMPDPDATAv1.LOG
EXCLUDE=STATISTICS,CONSTRAINT,VIEWS,DIMENSIONS,TABLE:”=’COSTS’”
JOB_NAME=IMPDPDATAv1
Figura 18. Teste de conexão via TNSPINGFigura 18. Teste de conexão via TNSPING
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
40 SQL Magazine • Edição 145
• ATTACH: usado para se conectar ao painel de controle do
IMPDP ou EXPDP durante a execução de um determinado job.
Ele pode ter parâmetros alterados durante sua execução, pode ser
pausado ou terminado através do painel de controle.
Como ainda não usamos o parâmetro ATTACH, que é bastante
útil para gerenciar as rotinas quando as mesmas estiverem em
execução, vamos iniciar um exemplo exportando o banco de dados
inteiro. Para isso, precisamos especificar o parâmetro FULL=Y. En-
tão, inicie um job com os seguintes parâmetros somente para teste:
EXPDP teste DIRECTORY=dump_dir DUMPFILE=dump_full.dmp FULL=y
A Figura 20 mostra a conexão ao painel do job de exportação.
Observe que o número máximo de paralelismo utilizado neste
caso foi 1, o que pode prejudicar o desempenho das operações
realizadas no contexto dessa exportação.
Podemos alterar o paralelismo da rotina. Para isso, basta utilizar
o comando “Export> parallel=4” que o job irá passar a usar parale-
lismo igual a 4. O uso de paralelismo é mais recomendado quando
se divide os arquivos usando o atributo “%U” após o nome do
arquivo de dump, por exemplo, DUMPFILE=dump_full_part_%
U.dmp. Podemos observar esse ajuste na Figura 21.
Conforme apresentado na imagem, podemos identificar que o
paralelismo passou a ser 4 ao invés de 1. Nesse caso, é instruído
ao job que ele utilizeos recursos do servidor e execute em para-
lelismo igual a 4. A utilização de paralelismo em ambientes reais
requer licença de uso, portanto, antes de utilizar alguns recursos
disponíveis no enterprise edition, tenha conhecimento das options
que fazem parte do suporte do cliente.
Para interrompermos um job, basta utilizarmos o comando
stop_job. Após interrompida a execução do job, ao se conectar ao
mesmo, veremos o status dele como indefinido.
Figura 19. Importação em andamento
Figura 20. Conexão ao painel do JOB de exportação EXPDP
Figura 21. Verificando alteração no paralelismo
Edição 145 • SQL Magazine 41
O mesmo poderá ser restabelecido
ou ter parâmetros alterados antes de
reiniciar conforme pode ser observado
na Figura 22.
Após conectado no job, podemos conti-
nuar a execução do mesmo com o comando
CONTINUE. Se usarmos o comando con-
tinue client, o job irá imprimir novamente
as mensagens no prompt. Para retornar ao
menu do EXPDP, pode-se usar o comando
Ctrl+C para interromper a impressão dos
logs da rotina e retornar ao menu. Após
a finalização da rotina, não podemos nos
conectar ao job novamente (Figura 23). Os
dados das execuções do Data Pump po-
dem ser consultados na view DBA_DATA
PUMP_JOBS.
Muitos outros comandos e possibilidades
de uso do Data Pump podem ser consul-
tados através da documentação oficial da
Oracle 11gR2 (ver seção Links).
O Oracle Data Pump é uma excelente
ferramenta que incorpora recursos dos
antigos EXPORT (exp) e IMPORT (imp),
além de muitos outros recursos. O novo
Data Pump da Oracle trouxe muito mais
velocidade na movimentação de dados
e um grande avanço na administração
das rotinas de exportação e importação
com o painel de controle oferecido pela
ferramenta.
Os comandos do utilitário são bastante
amigáveis e de fácil entendimento. A fer-
ramenta é muito poderosa com a utilização
de alguns parâmetros. Pode ser usada, por
exemplo, na movimentação de tablespaces
em uma eventual migração de versão ou
de plataforma com o uso do remapea-
mento de datafiles. Além disso, existem
inúmeros cenários no qual o Oracle Data
Pump poderá ser utilizado para realizar
as operações de um grande de volume
de dados.
Figura 22. Reconectando no JOB export_full e verificando status
Figura 23. Tentando reconectar no JOB
Links:
Oracle Database 11gR2
https://docs.oracle.com/cd/E11882_01/server.112/
e22490/toc.htm
Oracle Data Pump 11gR2
http://download.oracle.com/otndocs/products/database/
enterprise_edition/utilities/pdf/Data Pump11gr2_techo-
ver_1009.pdf
Oracle Data Pump Import 11gR2
https://docs.oracle.com/cd/E11882_01/server.112/
e22490/dp_import.htm#SUTIL300
Oracle Data Pump Export 11gR2
https://docs.oracle.com/cd/E11882_01/server.112/
e22490/dp_export.htm#SUTIL825
Marcelo Thomaz Cicala
marcelocicala@gmail.com
Brasileiro, solteiro, residente
em São Paulo capital, Tecnó-
logo em banco de dados pela FATEC,
DBA Oracle na empresa DBACorp.
Autor
Atualização de schemas no Oracle 11gR2 com o Oracle Data Pump
C
M
Y
CM
MY
CY
CMY
K
Porta80_AnINst.pdf 1 18/09/2011 14:58:37