Índice
Utilizando UML: Diagramas de Implantação, Comunicação e Tempo ............................................................ 3 Desafio Desafio SQL .................................................................... ....................................................................................................... ....................................................................... .........................................16 .....16 Alta Disponibilid Disponibilidade ade no SQL Server 2005/2008.................................. 2005/2008..................................................................... ..........................................................22 .......................22 Compactação Compactação de Dados Dados com o SQL Server 2008 ........................................... .............................................................................. ...............................................41 ............41 Gerenciando Gerenciando Usuários Usuários e Permissões Permissões no PostgreSQL PostgreSQL ............................................................................... ....................................................................................55 .....55 Desvendan Desvendando do o Oracle Data Integrator Integrator ...................................................................................... .......................................................................................................71 .................71 Oracle RAC Instalação Instalação - Parte 2 .................................................................................................. .................................................................................................................101 ...............101
Utilizando UML: Diagramas de Implantação, Comunicação e Tempo Paulo César Barreto da Silva Graduado em Análise de Sistemas pelo Centro Universitário Salesiano de São Paulo e Pós graduado pela Universidade Estadual de Campinas na área de Orientação Ori entação a Objetos. De que trata o artigo: Este artigo apresenta três dos 13 diagramas propostos pela UML na versão 2.0, os Diagramas de Implantação, Comunicação e Tempo. Para que serve: Os diagramas apresentados neste artigo permitem a ilustração das atividades relacionadas ao produto de software em suas etapas de desenvolvimento e validação de lógi ca. Em que situação o tema é útil: A utilização destes diagramas está amplamente associada à etapa de análise e projeto, principalmente na modelagem dos comportamentos esperados pela implementação do sistema. No oitavo artigo da série Utilizando a UML, apresentaremos mais três dos 13 diagramas descritos na especificação 2.0 da UML, completando assim a série de artigos que descreveu todos os diagramas da UML 2.0. Em nosso último artigo, tratamos dos Diagramas de Interação Geral, Componentes e Pacotes indicados por muitos autores como método de especificação e documentação das etapas de modelagem de solução e implementação. No presente artigo, vamos tratar de três Diagramas bastante conhecidos na versão 2.0 da UML: os Diagramas Diag ramas de Implantação, Comunicação e Tempo. Entre as versões 1.5 e 2.0 da UML, diversas alterações/evoluções foram realizadas. Os três diagramas que iremos abordar ao longo deste artigo são resultados nítidos de tal evolução da UML, como veremos a seguir. O Diagrama de Implantação determina as necessidades de hardware do sistema, as características físicas como servidores, estações, topologias e protocolos de comunicação, ou seja, todo o aparato físico sobre o qual o sistema deverá ser executado. Os Diagramas de Componentes e de Implantação são bastante associados, podendo ser representados em separado ou em conjunto. O Diagrama de Comunicação era conhecido como Diagrama de Colaboração até a versão 1.5 da UML, tendo seu nome modificado para Diagrama de Comunicação a partir da versão 2.0. Este Diagrama está amplamente associado ao Diagrama de Seqüência. Na verdade, um complementa o outro. O Diagrama de Tempo é a fusão do Diagrama de Seqüência e Estado apresentando o comportamento dos objetos e sua interação em uma escala de tempo, ou seja, o estado dos objetos em relação ao tempo e às mensagens que modificam esse estado. Estes três diagramas permitem na etapa análise e projeto modelar com bastante clareza os comportamentos e a implementação do modelo a ser desenvolvido. Neste artigo, vamos falar um pouco da definição, da sua utilização e principalmente dos aspectos de produtividade que fazem desses diagramas, importantes ferramentas na etapa de projeto e desenvolvimento. O Diagrama de Implantanção O Diagrama de Implantação é o diagrama com a visão mais física da UML (GUEDES, 2007). Este diagrama foca a questão da organização da arquitetura física sobe a qual o software irá ser implantado e executado em termos de hardware, ou seja, as máquinas (computadores pessoais, servidores etc.) que suportam o sistema, além de definir como estas máquinas serão conectadas e por meio de quais protocolos se comunicarão e transmitirão as informações. Os elementos básicos deste diagrama são os Nós, que representam os componentes, Associações entre Nós, que são as ligações entre os Nós do diagrama, e os Artefatos, representações de entidades físicas do mundo real. Veremos cada um dos componentes que compõem o Diagrama de Implantação a seguir.
Nós Nós são componentes fundamentais do Diagrama de Implantação. Um nó pode ilustrar um item de hardware, como um servidor em que um ou mais módulos do software são executados ou que armazene arquivos consultados pelos módulos do sistema, ou po de representar um ambiente de execução, ou seja, um ambiente que suporta o sistema de alguma al guma forma. Nós podem conter outros nós, sendo comum encontrar um nó que representa um item de hardware contendo outro nó que representa um ambiente de execução, embora nó que represente um item de hardware possa conter outros nós representando itens de hardware, e um nó que represente um ambiente de execução possa conter outros ambientes de execução. Quando um nó representa um hardware, deve possuir o estereótipo <>; quando, porém, um nó representa um ambiente de execução, pode utilizar o estereótipo <>. A Figura 1 apresenta exemplo de utilização de nó para representar um item de hardware. Outros exemplos de ambientes de execução são os sistemas operacionais ou sistemas si stemas e banco de dados. Os estereótipos são um dos três mecanismos de extensão da UML. Eles dão mais poder à UML, permitindo classificar elementos "com algo em comum" (Wiki pédia). Associação entre Nós Os Nós possuem ligações físicas entre si de forma que possam se comunicar e trocar informações. Essas ligações são chamadas associações e são representadas por retas ligando um Nó a outro. Uma associação pode conter estereótipos utilizados para determinar, por exemplo, o tipo de protocolo e comunicação utilizado entre os nós (ver Figura 2). A Figura 2 demonstra um exemplo de associação entre o Nó que representa o Servidor de Comunicação e o Nó que representa o Servidor de Firewall. O protocolo de comunicação é descrito na Associação como um estereótipo <>. Figura 1. Exemplo de Nó (GUEDES, pg. 162, 2007)
Figura 2. Exemplo de associação entre Nós (GUEDES, pg. 162, 2007)
Exemplo de Diagrama de Implantação Os Diagramas de Implantação são conhecidos, principalmente, pela sua simplicidade e facilidade de compreensão. Como facilitador, apresentaremos um exemplo de Diagrama de Implantação referente à arquitetura física necessária para suportar um Sistema de Controle de Submissões (ver Figura 3). 3) . O exemplo apresentado na Figura 3 é o mesmo modelado na edição 67 da SQL Magazine. O sistema que estamos modelando representa um processo de submissão de artigos à edição de um periódico. A Figura 3 demonstra as associações existentes entre os vários Nós, que representam cada um dos hardwares existentes na arquitetura de implantação do sistema. Através deste diagrama, notamos que
a comunicação entre o Nó Hardware do Autor, equipamento utilizado pelo autor para desenvolver o artigo, e o Nó Servidor de Aplicação I, equipamento instalado do lado do servidor onde a aplicação Sistema de Controle de Submissões está instalada, passa pelos Nós Servidor de Comunicação, equipamento que garantirá a boa performance e zelará pela transmissão e recepção dos dados, e Servidor de Firewall, responsável pela proteção da arquitetura do sistema. Podemos notar que após a comunicação com o Nó Servidor de Aplicação I, há a comunicação com os Nós Servidor de Banco de Dados, onde ocorre a persistência e gestão dos dados do sistema, e o Nó Servidor de Aplicação II, que neste contexto representa um modelo de balanceamento ou de administração de sistemas de apoio, como por exemplo, ferramentas de controle administrativo. Podemos obter também através da leitura deste diagrama (ver Figura 3) o Protocolo de comunicação adotado entre os vários Nós, representado pelo estereótipo <>. A Figura 4 apresenta o Diagrama de Componentes (ler Nota DevMan 1) equivalente aos módulos executáveis do Sistema de Controle de Submissões que estamos modelando. Alguns módulos não são exatamente executáveis, como é o caso do componente que representa a página de submissão de artigos, ou pertencem exclusivamente ao sistema, como o componente que representa o Sistema Gerenciador de Banco de Dados, mas são indispensáveis para o funcionamento do mesmo. Nota do Devman Na edição 67 da SQL Magazine, apresentamos o Diagrama de Componentes. O Diagrama de Componentes, como o próprio nome sugere, apresenta a identificação dos componentes que compõem um sistema, subsistema ou mesmo componentes ou classes internas de um componente individual. Para maiores detalhes, leia os artigos anteriores da série Utilizando UML.
Figura 3.
Exemplo de Diagrama de Implantação (adaptado Guedes, 2007)
Figura 4.
Diagrama de Componentes do Sistema de Controle d e Submissões (adaptado de GUEDES, 2007)
Podemos observar a utilização dos relacionamentos entre componentes por meio de Interfaces Fornecidas e Requeridas, onde podemos notar, por exemplo, que o componente Sistema de Gerenciamento de Banco de Dados é Interface Fornecida por outros oito componentes: Gerenciador de Login, Gerenciador de Submissões do Autor, Cadastro de Avaliadores, Cadastro de Temas, Gerenciador de Avaliações, Relatório de Avaliações, Notificação de Autor e Gerenciador de Submissões do Coordenador. O componente Página Eletrônica de Submissão de Artigos é o componente inicial deste diagrama. Percebemos isso porque é através dele que o Submissor tem o acesso a executar o componente Controlador de Submissões. O componente componente Controlador de Submissões é Interface Provida pelo componente Página Eletrônica de Submissão de Artigos, e Interface Requerida para os componentes Gerenciador de Login e Gerenciador de Submissões do Autor. A utor. Artefatos Um artefato é uma entidade física, um elemento concreto que existe realmente no mundo real, assim como os nós que o suportam. Um artefato pode ser um arquivo fonte, um arquivo executável, um arquivo de ajuda, um documento de texto etc. Um artefato deve estar implementado em um Nó. Na Figura 5 é apresentado um exemplo de Artefato implementado em um Nó. Na Figura 5 podemos notar que o artefato denominado “Módulo Gerenciador de Login” possui a mesma denominação que um dos componentes apresentados na Figura 4. Na verdade, um artefato é muitas vezes uma “manifestação” no mundo real de um componente. No enta nto, não necessariamente existirá um artefato de cada componente, sendo possível existirem diversos artefatos manifestados a partir de um único componente. A Figura 6 demonstra um exemplo de artefato instanciado a partir de um componente. Observe que existe um relacionamento de dependência entre o componente e o artefato, contendo o estereótipo <>, significando que o artefato é uma representação do componente do mundo real.
Figura 5. Exemplo de Artefato implementado em um Nó
Figura 6. Exemplo de Artefato manifestado a partir de um Componente C omponente Outra forma de manifestar um artefato contido em um Nó, segundo Guedes em seu livro UML Guia Prático, é utilizar um relacionamento de dependência, contendo o estereótipo <> entre o nó e os artefatos (ver Figura 7). Um Nó pode conter componentes da mesma forma que artefatos, como uma maneira de demonstrar em que lugar os componentes poderão ser localizados locali zados no hardware que suportará o sistema. Especificação de Implantação A Especificação de Implantação especifica um conjunto de propriedades que determinam parâmetros de execução de um artefato implementado em um Nó N ó (ver Figura 8). A Figura 8 demonstra a Especificação de Implantação do artefato Modulo.jar. O arquivo Módulo.xml é o conjunto de propriedade que descreve o parâmetros que o artefato Modulo.jar implementado na aplicação Sistema de Controle de Submissões. Su bmissões. Diagrama de Comunicação O Diagrama de Comunicação era conhecido como Diagrama de Colaboração até a versão 1.5 da UML, tendo o seu nome modificado para Diagrama de Comunicação a partir da versão 2.0 da UML. Esse diagrama está amplamente associado ao diagrama de seqüência - na verdade, um complementa o outro. As informações mostradas no Diagrama de Comunicação são, com freqüência, praticamente as mesmas apresentadas no Diagrama de Seqüência (ler Nota DevMan 2), porém com um enfoque diferente, visto que este diagrama não se preocupa com a ordem temporal dos processos, concentrando-se em como os objetos estão vinculados e quais mensagens trocam entre si durante o processo.
Figura 7. Artefato A rtefato implementado em um Nó (adaptado de GUEDES, 2007)
Figura 8. Especificação de Implantação Nota do Devman No artigo publicado na edição 64 da SQL Magazine, abordamos a definição e a estrutura do Diagrama de Seqüência. O Diagrama de Seqüência serve para representar a ordem temporal em que as mensagens são trocadas entre os objetos envolvidos em determinado processo. Um diagrama de seqüência mostra a colaboração dinâmica entre os vários v ários objetos de um sistema Por ser muito semelhante ao Diagrama de Seqüência, o Diagrama de Comunicação utiliza muitos de seus elementos, como atores e objetos, incluindo seus estereótipos de fronteira e controle. No entanto, os objetos no Diagrama de Comunicação não possuem linhas de vida. Além disso, esse diagrama não suporta ocorrências de interação ou fragmentos combinados como o Diagrama de Seqüência, por isso é utilizado para a modelag modelagem em de processos mais simples. Da mesma forma que o Diagrama de Seqüência, um Diagrama de Comunicação enfoca um processo, normalmente baseado em um Caso de Uso. As semelhanças entre ambos são tão grandes que existem até mesmo ferramentas CASE capazes de gerar um dos diagramas a partir do outro. Atores Os atores são os mesmos descritos no Diagrama de Casos de Uso (ler Nota DevMan 3) e Diagrama de seqüência, ou seja, descreve entidades externas que interagem com o sistema, solicita serviços e gera, dessa forma, eventos que iniciam processos. Normalmente representa usuários que interagem com o sistema e outros softwares, como um sistema integrado ou um hardware específico. Atores são representados por bonecos magros idênticos aos usados no Diagrama de Casos de Uso. Nota do Devman No artigo publicado na edição 62 da SQL Magazine, apresentamos a definição e a forma de utilização do diagrama de casos uso.
Objetos Os objetos representam as instâncias das classes que estão envolvidas no processo descrito pelo diagrama de seqüência. Os objetos são representados com um retângulo contendo um texto que identifica primeiramente o nome do objeto, em minúsculo, e depois o nome da classe, com letras iniciais maiúsculas, a qual o objeto pertence. As duas informações são separadas por dois pontos (:). Mensagens Como comentamos, o Diagrama de Comunicação se preocupa com o relacionamento entre os objetos envolvidos em um processo, e isto é feito principalmente por meio de mensagens. Uma mensagem é causada por um evento e pode conter uma descrição, uma chamada de um método ou ambos. Mensagens podem ainda conter condições de guarda, bastante úteis neste diagrama. Para que possa ser enviada uma mensagem de um componente é necessário haver uma associação entre os componentes. Após existir a associação, pode-se então acrescentar mensagens a ela. Uma mensagem se caracteriza por conter uma seta apontando ao objeto para o qual está sendo enviada (ver Figura 9). O Controlador_Congresso, representado por um símbolo em forma de circulo com uma seta incluída, é uma Control Class (Classes de Controle geralmente são as classes que conectam as classes de interface às classes do domínio).
Figura 9. Exemplo de Mensagem entre componentes Autochamada Um objeto pode disparar uma mensagem em si próprio, o que é conhecido como auto-chamada, onde a mensagem parte do objeto e retorna ao próprio objeto. A Figura 10 apresenta um exemplo de autochamada em um objeto. A Figura 10 demonstra o envio de uma mensagem do objeto autor1 para si próprio, solicitando o disparo do método ValCpf() responsável pela validação do CPF. Esta instância da classe Autor está contida no processo de submissão de artigos como um método de validação da informação de CPF do autor do artigo. Condições de Guarda e Iterações Condições de Guarda são textos entre colchetes que estabelecem condições ou validações para que uma mensagem possa ser enviada. Já Iterações representam uma situação em que uma mensagem pode ser enviada várias vezes, correspondendo muitas vezes a um laço de repetição. As iterações são representadas por um asterisco (*) na frente da mensagem e em geral vêm apoiadas por Condições de Guarda. Uma vez que o Diagrama de Comunicação não suporta fragmentos combinados, muitas vezes é necessário lançar mão desse artifício para representar situações opcionais ou laços. Um exemplo é apresentado na Figura 11. Na Figura 11 observamos a utilização da Condição de Guarda e Iteração no processo de ordenação das submissões em relação à instância da classe Edicao. O processo se inicia com a validação das informações de acesso do Ator Editor_Chefe e em seguida pela execução do processo de ordenação. Esta Condição de Guarda e Iteração representa que para cada submissão uma ordem será definida e isso ocorre enquanto houver submissões a serem ordenadas.
Figura 10. Exemplo de Autochamada (adaptado de GUEDES, pg. 242, 2009)
Figura 11. Exemplo de Condição de Guarda e Iteração (adaptado de GUEDES, 2009) O responsável por esta atividade é o método DefOrd(ord), recebe como parâmetro à ordem desta submissão dentro da edição, da classe Edicao estimulado/executado estimul ado/executado pela instancia da classe Editor. Modelando Diagrama de Comunicação para o Sistema de Controle de Submissões A partir de agora, iremos demonstrar a continuação da modelagem do Sistema de Controle de Submissões, citado anteriormente e descrito no artigo anterior desta série. Os diagramas seguintes correspondem aos mesmos processos apresentados na Figura 4, que demonstra o Diagrama de Componentes. A Figura 12 demonstra o processo de Login do Submissor através do Diagrama de Comunicação. O processo de Login do Submissor é executado com o objetivo de validar suas informações de acesso, e em seguida executar a atividade de ordenação das submissões realizadas pelos autores dentro de uma edição. Este processo inicia-se com a mensagem Informar login e senha na interface Pagina_Congresso que é validada pelo componente Controlador_Congresso que executa o método Login() da instância da classe Autor (objeto autor1). A seguir, a Figura 13 demonstra o processo de Submissão de artigos, através do Diagrama de Comunicação. O processo apresentado no exemplo demonstra a validação das informações de acesso do responsável pela submissão no sistema.
Figura 12. Realizar Login (adaptado de GUEDES, pg. 113, 2007)
Figura 13. Realizar Submissão (adaptado de GUEDES, pg. 113, 2007) O processo é iniciado pelo Ator Submissor que começa selecionando a opção de submissão na interface Pagina_Congresso. Em seguida, ele seleciona o tema e informa os dados de submissão. Através das informações transmitidas pelo Ator à Interface, o componente Controlador_Congresso recebe de Pagina_Congresso a solicitação de submissão (mensagem Submissão solicitada), a informação de tema (mensagem Tema) e a confirmação de submissão (mensagem Submissão confirmada). Após receber as mensagens enviadas ao componente Controlador_Congresso, este realiza um processo executado sobre a Condição de Guarda Para cada tema, que para cada tema executará o método SelTema() do objeto da classe Tema. Em seguida o componente Controlador_Congresso executa o método SelTema() do objeto da classe Tema e o método RegSub() do objeto sub1 da classe Submissao, responsável pelo registro da submissão. Continuando, a Figura 14 demonstra o processo de verificação de submissões de artigos também. Para cada submissão é realizada uma verificação através do componente Controlador_Congresso. A Figura 15 demonstra o processo de verificação de comentários de um artigo. Para cada submissão é realizada uma avaliação através do componente Controlador_Congresso que executa o método SelAval()do objeto da classe Avaliacao. Neste exemplo há duas Condições de Guarda. A primeira Condição de Guarda restringe que para cada avaliação será executado uma vez o método SelAval() da
classe Avaliacao. A segunda Condição de Guarda possui comportamento semelhante, porém a restrição se refere a um comentário sobre uma avaliação realizada.
Figura 14. Verificar Submissões (adaptado de GUEDES, pg. 114, 2007)
Figura 15. Verificar Comentários (adaptado de GUEDES, pg. 114, 2007) A Figura 16 apresenta o processo que permite a manutenção, modificação, das informações relacionadas às avaliações e comentários em relação a uma submissão. Este processo complementa aspectos apresentados no processo descrito na Figura 15. A Figura 17 demonstra o processo de manutenção de comentários que podem ser feitos durante o processo de avaliação de um artigo. O processo é iniciado pelo Avaliador que poderá ao longo do processo criar um novo comentário, alterar, selecionar e excluir a qualquer momento um comentário. As Condições de Guarda do inicio do Diagrama de Comunicação irão determinar o fluxo de mensagens. O exemplo apresentado na Figura 18 demonstra o processo de emissão de relatórios no Sistema de Controle de Submissões. Neste diagrama, o estímulo inicial que parte do Ator Coordenador que seleciona a opção Relatório de Avaliações e o Tema e Tipo de Submissão desejada. Para cada Submissão, seleciona-se o Tema, o conteúdo submetido e a avaliação aval iação desta submissão.
Diagrama de Tempo ou de Temporização Esse diagrama apresenta algumas semelhanças com o Diagrama de Máquinas de Estados. No entanto, ele enfoca as mudanças de estado de um objeto ao longo do tempo. Esse diagrama terá pouca utilidade, segundo Guedes, em seu livro UML - Uma Abordagem Prática, para modelar aplicações comerciais, contudo, poderá ser utilizado na modelagem de sistemas de tempo real ou sistemas que utilizem recursos de multimídia/hipermídia, onde o tempo em que o objeto executada algo é muitas vezes importante.
Figura 16. Manter Avaliações (adaptado de GUEDES, pg. 114, 2007)
Figura 17. Manter Comentários (adaptado de GUEDES, pg. 114, 2007)
Figura 18. Relatório de Avaliações (adaptado de GUEDES, pg. 114, 2007)
Em um sistema, por exemplo, de concurso público, há uma seqüência lógica de etapas que necessita ser executada. Não se pode “Aplicar prova de seleção” sem antes “Elaborar Edital de Concurso”. O exemplo do processo de concurso (ver Figura 19) descreve a mudança no estado ou condição da instância de “Concurso” durante o tempo de existência da instância. Tipicamente os Diagramas de Tempo demonstram mudanças no estado de um objeto no tempo em repostas a eventos externos. Cada etapa ou estado do objeto da classe “Concurso” é apresentada por meio de um hexágono, sendo que o primeiro e o último estado se encontram abertos. Abaixo de cada etapa, entre barras verticais, se encontram as restrições de duração que determinam o tempo em que transcorrem as etapas. No caso do estado “Abrindo Inscrições”, o período vai de 05 de janeiro a 31 de janeiro. É muito importante destacar que o Diagrama de Tempo tem duas notações ou formas de representação: uma notação conhecida como concisa, mais simples (conforme foi usado na Figura 19), chamada de linha de vida de valor, e uma notação considerada mais robusta, onde as etapas são apresentadas em uma forma semelhante a um gráfico (ver Figura 20), chamada linha de vida de estado. No Diagrama de Tempo, o termo linha de vida (lifeline) refere-se ao caminho percorrido por um objeto durante um determinado tempo. A Figura 20 demonstra o mesmo diagrama da Figura 19, dessa vez utilizando a forma robusta e linha de vida de estado, onde as transições de estado são determinadas por mudanças em um gráfico, podendo estas conter descrições que determinam o evento que causou a mudança, se isso for considerado necessário. Um Diagrama de Tempo pode ter linhas de vida de múltiplos objetos, utilizando a mesma notação ou notações diferentes.
Figura 19. Diagrama de Tempo - Forma concisa
Figura 20. Diagrama de Tempo - Forma considerada mais robusta
Conclusão Este foi o último artigo da série Utilizando UML. No decorrer destes 8 artigos pudemos com bastante detalhe conhecer cada um dos 13 diagramas da UML 2.0. A Modelagem através da UML adotada em processos de desenvolvimento representa uma das boas práticas da programação e manutenção de softwares. Até a próxima, sucesso e bons estudos!
Desafio SQL Wagner Crivelini Engenheiro formado pela UNICAMP, consultor em TI com 15 anos de experiência, particularmente em projetos de Business Intelligence. Atualmente trabalha na IBM, onde atua como DBA em projeto internacional. De que trata o artigo? Desenvolvimento de soluções para problemas cotidianos enfrentados por DBAs e desenvolvedores de aplicações para banco dados. Para que serve? Fornecer conceitos de utilização de funcionalidades do padrão SQL ANSI na resolução de problemas enfrentados no dia-a-dia na recuperação de informações do banco de dados. Em que situação o tema é útil? Integridade referencial.
Estamos de volta com a coluna Desafio SQL. Para quem nunca a leu, tratamos aqui de problemas enfrentados no dia-a-dia pelos profissionais que trabalham com bancos de dados. E para situarmos estes desafios, a cada artigo contamos um novo capítulo da história da empresa fictícia chamada ItsMyBusiness. Por curiosidade, lembro aos interessados que esta história começou faz um bom tempo, na Revista #50. Este é o 14o capítulo desta "novela" (no bom sentido, claro). A ItsMyBusiness é uma empresa de varejo que fez recentemente o seu site de e-commerce. E este site está "bombando"! Vender mais significa mais dinheiro. Mas do ponto de vista de um banco de dados, representa também um volume maior de transações, maiores cuidados com performance, com armazenamento de dados e disponibilidade do sistema. Estes são quesitos que devemos ter em mente desde o início i nício da modelagem de qualquer banco. Mas o fato é que a ItsMyBusiness tratou seu e-commerce como se fosse uma experiência e não tomou cuidados básicos com a criação cri ação deste sistema. Se você achou que este cenário se parece com o de algum sistema real com o qual você trabalhou, isso não é mera coincidência. É triste dizer, mas isso é terrivelmente comum. As empresas economizariam muito dinheiro se seguissem noções básicas de projeto. Bom ou mal, certo ou errado, o fato é que agora a ItsMyBusiness tem que consertar o "motor do seu carro" quando a corrida já está em andamento. Uma série de melhorias e correções de bugs no modelo do banco de dados da empresa tem sido feitas nos últimos meses. No nosso último desafio, apresentamos uma solução de modelagem para melhorar o controle sobre os pedidos que a ItsMyBusiness recebe. A solução previa o detalhamento dos possíveis status que um pedido poderia ter ao longo da sua história, ou seja, desde o momento em que ele é submetido pelo cliente até o momento em que ele é encerrado pela empresa (seja por qual razão for). Esta mesma solução incluía a integridade referencial dos dados, ou seja, nosso modelo deveria garantir que os dados registrados no banco fossem 100% consistentes. O modelo final da base, já incluídas as alterações citadas acima, é apresentado a seguir (Figura 1).
Figura 1. Modelo de dados simplificado si mplificado da empresa ItsMyBusiness. O script de criação deste banco de dados está disponível para download no portal da SQL Magazine. O script apresenta versões para rodar em S QL SERVER, DB2, ORACLE e FIREBIRD. FIR EBIRD. Voltando ao nosso assunto, para sorte da empresa ItsMyBusiness, o DBA que ela contratou, que no caso é você, é um cara muito cuidadoso. Antes de implementar esta solução, o DBA DBA abriu seu caderno de anotações e viu a seguinte frase escrita 100 vezes em letras garrafais: “NUNCA FAREI ALTERAÇÕES NO MEU AMBIENTE DE PRODUÇÃO ANTES DE VALIDAR MINHAS SOLUÇÕES EM UM AMBIENTE DE TESTES QUE SIMULE A OPERAÇÃO REAL”. Então ele passou o script de alteração da base para a equipe de testes, que depois de avaliar dezenas de casos de teste, apresentou o seguinte veredito: “Por razões desconhecidas, o modelo em análise permite a inserção manual de informações inconsistentes na tabela tblPedidoStatus. O problema foi observado quando fizemos inserção de dados usando uma declaração SQL do tipo INSERT”. Xiiii... a casa caiu! Na verdade, ainda não caiu, porque a alteração não foi para produção e é para isso mesmo que fazemos testes meticulosos antes de qualquer implementação. i mplementação. Já sabemos qual é o problema, pois os testadores não só disseram que o modelo “deu pau”. Eles disseram detalhadamente o que eles estavam fazendo quando o erro foi observado. O que houve foi o seguinte: foram executadas várias declarações de inserção de dados na tabela dbo.tblPedidoHistorico. Algumas delas deveriam ser aceitas e outras deveriam ser rejeitadas. Chamamos isso de casos de testes. Na Listagem 1 vemos quatro casos de teste que deveriam ser rejeitados.
Listagem 1. Os testes de rejeição . 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
-- Inserção de código de pedido inexistente inexi stente -- =====> Insert REJEITADO (CORRETO) INSERT INTO dbo.tblPedidoHistorico (codPedido, codProduto, codPedidoStatus, codPedidoStatus, Observacao) VALUES (1000, 1,1, nao existe pedido # 1000) -- Inserção de código de produto inexistente -- =====> Insert REJEITADO (CORRETO) INSERT INTO dbo.tblPedidoHistorico (codPedido, codProduto, codPedidoStatus,Observacao) VALUES (1, 2000 ,1, ,1 , nao existe produto # 2000) -- Insercao de código de status inexistente inexi stente -- =====> Insert REJEITADO (CORRETO) INSERT INTO dbo.tblPedidoHistorico (codPedido, codProduto, codPedidoStatus, Observacao) VALUES (1, 1, 3000, nao existe status # 3000) -- Insercao com produto que não pertence ao pedido -- =====> Insert ACEITO (ERRADO!!!!!!!!!!!!!) INSERT INTO dbo.tblPedidoHistorico (codPedido, codProduto, codPedidoStatus, Observacao) VALUES (1, 8, 1, o produto # 8 nao faz parte do pedido # 1)
Nos três primeiros, tentamos inserir códigos que não existem (linhas 1 a 22 da Listagem 1) e todos eles foram corretamente rejeitados. Mas no quarto teste houve um erro. Neste teste, tínhamos códigos válidos para os campos codPedido, codProduto e codPedidoStatus. Mas o produto descrito não faz parte daquele pedido. O banco deveria rejeitar esta inserção, mas ele erradamente a aceitou (linhas (l inhas 19 a 23). Agora volta tudo para as suas mãos, já que você é o DBA/arquiteto/desenvolvedor responsável por este projeto. Sua missão é: 1. identificar onde está o problema 2. propor uma nova solução Divirta-se! Resposta do desafio Muita gente simplesmente despreza o uso de chaves estrangeiras dentro dos seus bancos de dados. A maioria dos sistemas de gestão empresarial com os quais eu trabalhei as tratam como se fossem um pecado que deve ser evitado a qualquer custo. A alegação é que as chaves estrangeiras tem impacto na performance do banco, porque o banco de dados sempre fará a validação dos dados contra cada uma das chaves estrangeiras existentes numa tabela toda vez que for executar qualquer declaração INSERT, DELETE ou UPDATE. Isso é verdade. Existe mesmo um pequeno custo. E vai acontecer a cada transação que ocorrer no seu banco de dados, exigindo um pouco mais de tempo para execução de qualquer inserção, exclusão ou alteração nos seus dados. Mas este pensamento estreito esquece um pequeno detalhe: a qualidade dos dados armazenado s no seu banco. A integridade referencial (e todos os recursos que ela nos oferece, como é o caso das chaves estrangeiras) existe para garantir a consistência das informações. i nformações. Para uma empresa que vive na era da informação, é muito mais caro dispor de informações erradas e/ou inconsistentes do que levar um pouco mais mai s de tempo para realizar cada transação. Pessoalmente, eu uso chaves estrangeiras em todos os modelos de dados que eu crio e não vejo motivo que justifique a sua ausência.
Mas vamos ao que interessa. Em primeiro lugar, temos que traduzir as palavras dos testadores em termos do modelo do banco de dados. Quando dissemos "o produto descrito não faz parte daquele pedido", precisamos entender como o modelo lida com esta informação. Por isso vamos ver esta parte do modelo com maior detalhe (Figura 2).
Figura 2. Tratamento do ciclo de vendas Veja que o modelo usa a tabela dbo.tblPedidoDetalhe exatamente para armazenar as informações dos produtos que fazem parte de cada pedido. Tanto é assim que a chave primária desta tabela é composta pelos campos código de Pedido e código de Produto. Entendendo isso, podemos reformular a frase que apresentamos acima. Em termos do modelo de dados, estamos falando que não existe na tabela dbo.tblPedidoDetalhe nenhuma chave primária composta pelos código de Pedido e código de Produto que estamos inserindo na tabela de histórico do status do pedido. Para todos os efeitos práticos, nós acabamos de responder a primeira pergunta deste desafio! Olhe novamente o modelo na Figura 2. Veja que a integridade referencial que criamos no último desafio não garante que a tabela dbo.tblPedidoHistorico receba combinações de códigos de pedido e de produto que já estejam cadastrados na tabela dbo.tblPedidoDetalhe. dbo.tblP edidoDetalhe. Ao invés disso, a definição existente garante apenas que não poderemos cadastrar códigos de pedido e de produto que não existam nas tabelas dbo.tblPedido e dbo.tblProduto, respectivamente. Mas isso não faz tudo o que precisamos. Escrevendo explicitamente a resposta à primeira pergunta: o modelo em teste não usa a integridade referencial adequada para a tabela dbo.tblPedidoHistorico, a qual precisa ser alterada. Então tá, sabemos o que está errado. Mas o que vamos fazer para corrigir? Bom, nós precisamos criar chaves estrangeiras na tabela dbo.tblPedidoHistorico que façam referência à chave primária da tabela dbo.tblPedidoDetalhe. E a chave primária é formada pelo par de campos codPedido + codProduto. Maravilha. A solução parece simples. E aí vem outra pergunta: o que fazer com as chaves estrangeiras existentes?
Essa é uma boa pergunta. Muita gente acaba deixando “lixo” para trás dentro do banco de dados simplesmente porque ele parece “inofensivo”. Mas se as chaves existentes não resolvem o problema que deveriam cuidar, é muito importante avaliar se elas podem simplesmente ser eliminadas. Lembre-se que seria uma perda de tempo deixar para trás chaves estrangeiras inúteis, porque isso tem sim um pequeno impacto na performance do sistema, como eu já comentei anteriormente. No caso em questão, basta olharmos para Figura 2 para termos uma resposta. A tabela dbo.tblPedidoHistorico possui três chaves estrangeiras: uma referenciando dbo.tblPedidoStatus, outra referenciando dbo.tblPedido e a terceira referenciando dbo.tblProduto. A primeira delas, criada sobre o campo codPedidoStatus, não é afetada pela solução proposta. Portanto ela fica. Já sobre as duas outras, veja que elas são idênticas às chaves estrangeiras que existem na tabela dbo.tblPedidoDetalhe: uma referenciando a tabela dbo.tblPedido e outra referenciando dbo.tblProduto Como nós vamos criar uma nova chave estrangeira em dbo.tblPedidoHistorico referenciando exatamente a tabela dbo.tblPedidoDetalhe, seria redundante manter as referências antigas. Então devemos excluir ambas. Para isso, vamos precisar saber os nomes das chaves que serão excluídas. E esta parte nem sempre é tão fácil... E cada SGBD tem um meio de lhe mostrar esta informação. i nformação. No SQL SERVER, por exemplo, existem visões de sistema (as Dynamic Management Views ou DMVs) que nos dão estas e outras informações. Aos interessados, recomendo dar uma olhada na solução apresentada por Pinal Dave (http://blog.sqlauthority.com/2007/09/04/sql-server-2005-find-tables-withforeign-key-constraint-in-database/). Respondemos metade da segunda pergunta. Dissemos o que fazer, mas não como fazer a alteração.Faltou criarmos uma nova chave estrangeira referenciando dois campos ao mesmo tempo. O padrão ANSI SQL prevê esta situação de forma muito simples e intuitiva: basta referenciar os dois campos desejados, separando-os por uma vírgula. A Listagem 2 mostra o script final incluindo a exclusão das chaves antigas e a criação da nova chave. Este script é válido para SQL SERVER, DB2 e ORACLE. Listagem 2. Solução do desafio (SQL SERVER, DB2 e ORACLE). 1 2 3 4 5 6 7 8 9 10 11 12 13 14
-- exclui FKs existentes ALTER TABLE dbo.tblPedidoHistorico DROP CONSTRAINT FK_tblPedidoH_tblPedido ; ALTER TABLE dbo.tblPedidoHistorico DROP CONSTRAINT FK_tblPedidoH_tblProduto ;
-- cria a FK correta!!! ALTER TABLE dbo.tblPedidoHistorico ADD CONSTRAINT fkPedidoH_DUPLO FOREIGN KEY (codPedido, codProduto) REFERENCES dbo.tblPedidoDetalhe (codPedido, codProduto) 15 ;
Para o FIREBIRD, a única alteração necessária é excluir a referência ao esquema “dbo”, já que este SGBD não usa nome de esquema e/ou login à frente do nome dos objetos. O restante da sintaxe é idêntico, conforme Listagem 3. Com isso terminamos o desafio SQL deste mês. Agora podemos passar a correção do código para nova série de testes e, se tudo der certo, em breve teremos as novas implementações rodando no ambiente de produção da ItsMyBusiness! I tsMyBusiness! Espero que você tenha gostado. Listagem 3. Solução do desafio (FIREBIRD) 1 2 3 4 5 6 7 8 9 10 11 12 13 14
-- exclui FKs existentes ALTER TABLE tblPedidoHistorico DROP CONSTRAINT FK_tblPedidoH_tblPedido ; ALTER TABLE tblPedidoHistorico DROP CONSTRAINT FK_tblPedidoH_tblProduto ;
-- cria a FK correta!!! ALTER TABLE tblPedidoHistorico ADD CONSTRAINT fkPedidoH_DUPLO FOREIGN KEY (codPedido, codProduto) REFERENCES dbo.tblPedidoDetalhe (codPedido, codProduto) 15 ;
Alta Disponibilidade no SQL Server 2005/2008 Priscila Azarias Formada pela Universidade Tecnológica Federal do Paraná (UTFPR) em Sistemas de Informações. Atualmente especializando em Engenharia de Produção pela UTFPR. Atualmente trabalha na empresa W.Security, como DBA utilizando SQL Server 2005. De que se trata o artigo: O presente artigo apresenta os principais conceitos sobre alta disponibilidade e as soluções que podem ser implementadas utilizando o SQL Server. . Para que serve: Este artigo serve de base introdutória para a construção de uma solução que mantém a disponibilidade de um sistema após uma falha de hardware ou software. Em que situação o tema é útil: Minimizar o tempo de inatividade de um sistema em caso de alguma falha de software ou hardware, disponibilizando um segundo servidor responsável em assumir os serviços do servidor principal. Alta disponibilidade pode ser definida como uma solução que mascara os efeitos de uma falha de hardware ou software e mantém a disponibilidade dos aplicativos, de modo a minimizar o tempo de inatividade de um sistema. Para algumas empresas, esta definição significa que deverá existir um hardware redundante igual ao de produção, o que requer que os dados e o hardware tenham duração e disponibilidade de 99,995 % ou mais. Outras empresas necessitam apenas que os dados propriamente ditos tenham alta disponibilidade, sem tanta preocupação com o desempenho do nível de produção caso um failover (processo no qual uma máquina assume os serviços de outra, quando esta última apresenta alguma falha) seja necessário. Para determinar a melhor solução de alta disponibilidade, é necessário avaliar questões referentes aos tipos de interrupções que poderão ocorrer e indicar como isso afeta seus Contratos de Nível de Serviço (SLAs). As interrupções que podem afetar a disponibilidade são: - Desempenho Planejado: normalmente é uma manutenção programada sobre a qual os usuários dos sistemas são informados com antecedência; - Não Planejado: geralmente resulta de uma falha de hardware ou software que torna os dados inacessíveis; e - Degradação do Desempenho: a degradação do desempenho também pode provocar interrupções, e normalmente é medida no tempo de resposta re sposta do usuário final. E por fim, identificar o nível de atividade dos dados e se estes devem estar sempre on-line ou offline ocasionalmente. A seguir será descrito previamente cada opção de disponibilidade disponível para o Microsoft SQL Server 2005, que seriam: Cluster de Failover, Espelhamento de banco de dados, Log Shipping e Replicação. Cluster de Failover O Cluster de failover é basicamente uma solução de hardware que consiste em um grupo de computadores independentes que trabalham juntos para aumentar a disponibilidade de aplicativos e serviços. Os servidores em cluster (chamados de nós) são conectados através de cabos físicos e de software. Se um dos nós do cluster falhar, outro começará a fornecer os serviços, sendo que os usuários do sistema teriam o mínimo de interrupções i nterrupções nos serviços. Um requisito inicial que deve ser verificado antes da instalação do cluster é identificar se o hardware é certificado pela Microsoft. Este deve constar na lista de soluções de hardware certificada,
chamada de Hardware Compatibility List (HCL). Por ser uma solução de alta disponibilidade, é preciso assegurar que componentes lógicos e físicos funcionam da maneir a adequada. Para uma solução em cluster, são necessários os seguintes componentes físicos (ver Figura 1): - Nós de cluster (Cluster Nodes): é um servidor que faz parte do cluster e compartilha os recursos do cluster. Todos os nós do cluster devem possuir o mesmo sistema operacional e plataforma (32 bits ou 64 bits). - Rede Privada (Private Network): a função da rede privada é verificar se os nós que compõem o cluster estão funcionando e disponíveis. A rede privada é implementada através de uma placa de rede dedicada e exclusiva no nó do cluster. - Rede Pública (Public Network): a função da rede pública é permitir que as aplicações conectemse no cluster e que o cluster possa conectar-se na rede. A rede pública é implementada através de uma placa de rede dedicada e exclusiva no nó do cluster. - Conjunto de discos compartilhados (Shared Disk Array): conjunto de discos físicos (SCSI ou Fiber Channel) que são acessados pelos nós do cluster. O conjunto de discos compartilhados também é conhecido como “storage do cluster”. A “storage” apresenta para os nós do cluster um conjunto lógico de discos que são acessados pelo sistema operacional como se fossem discos internos do servidor. O serviço de cluster da Microsoft implementa o conceito de shared nothing disk, pois desta forma somente um nó do cluster tem acesso exclusivo a uma ou mais mai s unidades lógicas da “storage” de d e cada vez. - Disco de Quorum (Quorum Disk): é uma unidade lógica na “storage” que contém o arquivo de log e informações de estado do cluster. O nó que for o dono do disco de quorum é o nó responsável pelo cluster. Na Figura 1 é possível visualizar como ficaria um cluster completo com todos os seus componentes mais um disco onde possui uma instalação de uma instância (serviço) do SQL Server. No caso de uma falha no nó principal, o segundo nó assumirá os serviços que estavam sendo disponibilizados, sendo transparente para o usuário final. A mudança entre os nós pode ser feita de forma manual ou automática.
Figura 1. Cluster Completo
Espelhamento de banco de dados O espelhamento de banco de dados é basicamente uma solução de software para aumentar a disponibilidade dos dados, dando suporte a failover quase instantâneo. O espelhamento de banco de dados mantém duas cópias de um único banco de dados em servidores diferentes. Uma instância do servidor atua como banco de dados para os clientes (servidor principal) enquanto a outra instância funciona como servidor em espera ativa ou passiva (servidor de espelho), dependendo da configuração. A configuração mais simples do espelhamento do banco de dados envolve apenas os servidores: principal e espelho. Nessa configuração, se o servidor principal for perdido, o servidor espelho poderá ser usado como um servidor de espera passiva (a mudança deve ocorrer de forma manual), onde poderá ocorrer possível perda de dados (Ver Figura 2). Outra configuração é dita como modo de alta segurança com failover. Neste caso envolverá mais uma instância de servidor de banco de dados, conhecido como testemunha, que possibilita que o servidor espelho atue como um servidor em espera ativa (a mudança ocorre de forma automática) (ver Figura 3). O failover do banco principal para o banco de espelho normalmente demora vários segundos.
Figura 2. Espelhamento de Banco de Dados
Figura 3. Espelhamento com Servidor Servi dor de Testemunha
As Figuras 2 e 3 demonstram como resultaria a configuração do espelhamento de banco de dados com e sem o servidor de testemunha. Caso ocorra uma falha no banco de dados principal o servidor espelho deverá assumir o seu lugar, fazendo com que os usuários possam continuar acessando o apl icativo, mesmo após a ocorrência de alguma falha. O espelhamento de banco de dados oferece os seguintes segui ntes benefícios: - Detecção e failover automático; - Failover manual; - Redirecionamento transparente para os clientes; - Opera em nível de banco de dados; - Usa uma única cópia duplicada do banco de dados; - Usa servidores padrão; - Fornece relatórios no servidor de espelho, usando cópias có pias do banco de dados (instantâneos); - Quando opera sincronicamente, proporciona zero perda de trabalho por meio de confirmação atrasada no banco de dados principal. Log Shipping (Envio de Logs) Assim como o espelhamento de banco de dados, o Log Shipping também é uma solução de software. Este recurso pode ser utilizado para manter um ou mais banco de dados de espera passiva (banco de dados secundário) para um banco de dados de produção (banco de dados primário). O Log Shipping permite o envio automático de backups do log de transações (ver Nota DevMan ) de um banco de dados primário para um banco de dados secundário. Os backups de logs de transação são aplicados individualmente aos bancos de dados secundários, dessa forma existindo cópias do banco de dados primário. Uma terceira instância de servidor opcional, conhecido como servidor monitor, registra o histórico e o status das operações de backup e restauração e podendo emitir alertas se essas operações não forem executadas corretamente. Nota Devman - Controle de Log de Transações Controle de Log e Transações do SQL Server: Uma transação garante que qualquer operação seja ou totalmente completada ou desfeita caso ocorra uma falha, mas nunca permite que o banco de dados fique em um estado intermediário. O SQL Server implementa as transações usando um arquivo de Log. Quaisquer mudanças realizadas em qualquer dado irão atualizar a memória cachê, simultaneamente todas as operações realizadas serão escritas no Log. A Figura 4 mostra a configuração do envio de logs com uma instância do servidor primário, uma instância secundária e uma instância de servidor monitor. Esta figura ilustra as etapas executadas pelos backups, cópia e restauração: 1. A instância do servidor primário executa o trabalho de backup do log de transações do banco de dados primário. Essa instância do servidor coloca o backup do log em um arquivo de backup de log primário, enviado para a pasta de backup. 2. A instância de servidor secundário executa seu próprio trabalho de cópia do arquivo de backup de log primário para a sua própria pasta de destino local. 3. O servidor secundário executa seu próprio trabalho de restauração do arquivo de backup de log a partir da pasta de destino local no banco de dados secundário local. O Log Shipping envolve um atraso modificável pelo usuário entre o momento em que o servidor primário cria um backup de log do banco de dados e quando o servidor secundário restaura um banco do backup. Antes que um failover possa ocorrer, um banco de dados deve ser atualizado completamente pela aplicação manual de quaisquer backups de log não restaurados. Esta solução fornece a flexibilidade de suportar vários bancos de dados de espera, oferecendo as seguintes funcionalidades: - Suporte a vários bancos de dados secundários em várias instâncias de servidor para um único banco de dados primário;
- Permite um atraso especificado pelo usuário entre o momento em que o servidor primário faz backup do log do banco de dados primário e quando os servidores secundários devem restaurar o backup de log. Um atraso mais longo pode ser útil, por exemplo, se dados forem alterados acidentalmente no banco de dados primário. Se a alteração acidental for notada rapidamente, um atraso pode permitir que você recupere dados ainda inalterados de banco de dados da dos secundário, antes que alteração seja refletida.
Replicação A replicação é utilizada para copiar dados para um servidor e distribuí-los para outros servidores. Também pode ser utilizada para copiar, transformar e distribuir os dados personalizados entre os múltiplos servidores. Usando a replicação, é possível distribuir dados para diferentes locais e para usuários remotos e móveis através de redes locais e de longa distância, conexões dial-up, conexões sem fio e a Internet. Algumas razões para usar a replicação incluem: - Sincronizar alterações para bancos de dados remotos com um banco de dados central. Por exemplo, se a equipe de vendas utiliza laptops remotos, você pode precisar criar uma cópia de dados para a região de vendas da equipe no laptop. Mais tarde, um vendedor no campo poderá desconectado da rede, acrescentar informações ou fazer alterações. Com a replicação, essas modificações seriam sincronizadas com o banco de dados central. - Criar múltiplas instâncias de um banco de dados para que você possa distribuir a carga de trabalho. Por exemplo, se tiver um banco de dados central que é atualizado regularmente, talvez seja recomendável obter alterações para os bancos de dados departamentais à medida que elas ocorram. Os empregados podem então acessar os dados departamentais em vez de tentar se conectar ao banco de dados central. - Mover conjuntos de dados específicos de um servidor central e distribuí-los para vários outros servidores. Por exemplo, usar a replicação para um banco de dados central que precisasse distribuir os dados de vendas para todos os bancos de dados de lojas de departamento da empresa. A replicação foi projetada para atender às necessidades de uma ampla variedade de ambientes. A arquitetura de replicação é dividida em vários processos, procedimentos e componentes diferentes, cada um dos quais é utilizado para personalizar a replicação para uma situação particular. A arquitetura de replicação inclui: - Componentes da replicação: são os componentes servidores e dados na replicação. Sendo eles: - Publicador: são servidores que disponibilizam os dados para a replicação em outros servidores. Também monitoram alterações nos dados e mantêm outras informações sobre o banco de dados de origem. Todo agrupamento de dados tem apenas um publicador. - Distribuidor: são servidores que distribuem os dados replicados. Os distribuidores armazenam o banco de dados de distribuição, os metadados, os dados históricos e (para replicação transacional) as transações.
- Assinante: são servidores de destino para replicações. Esses servidores armazenam os dados replicados e recebem atualizações. Os assinantes também podem fazer alterações em dados. Os dados podem ser publicados em múltiplos assinantes. - Agentes e trabalhos de replicação: Aplicativos que auxiliam no processo de replicação. - Variantes da replicação: São os tipos de replicação, sendo elas: * Replicação Transacional: normalmente é usada em cenários de servidor para servidor que requerem alta taxa de transferência, incluindo: melhora da escalabilidade e disponibilidade; armazenamento de dados data warehouse e relatórios; integração de dados de vários sites; integração de dados heterogêneos e descarregamento de processamento em lote. * Replicação de Mesclagem: é projetada principalmente para aplicativos móveis ou de servidor distribuído que possuem possíveis conflitos de dados. Os cenários comuns incluem: troca de dados com usuários móveis; aplicativos de POS (ponto de vendas) para o consumidor e integração de dados de vários sites. * Replicação de Instantâneo (Snapshot): é usada para fornecer o conjunto inicial de dados para replicação transacional e de mesclagem. Ela também pode ser usada quando as atualizações completas co mpletas de dados estiverem apropriadas. A Figura 5 demonstra como ficaria a arquitetura da replicação. A replicação possibilita disponibilidade em tempo real e escalabilidade entre servidores. Suporta filtragem para fornecer um subconjunto de dados nos Assinantes e também permite atualizações particionadas. Os Assinantes ficam online e disponíveis para relatórios e outras funções, sem recuperação de consultas. Configurando Espelhamento de Banco de Dados Agora que conhecemos as soluções disponíveis para disponibilidade de um banco de dados, vamos agora simular uma das soluções de disponibilidade que o SQL Server 2005/2008 fornece levando em consideração o seguinte estudo de caso: você é administrador de um banco de dados de uma empresa que vende seus produtos através da web. É preciso garantir a disponibilidade dos dados, sem qualquer tipo de interrupção. Analisando o ambiente do cliente, você decide implementar o espelhamento do banco com espera ativa.
Figura 5. Replicação
Antes de aprendermos como criar um espelhamento no banco, vamos criar o banco de dados SQLMagazine e as tabelas que o compõem: PRODUTOS, CLIENTES e VENDAS (Ver Listagem 1). Para executar a Listagem 1, abra o SQL Server Management Studio, conecte-se na instância que será o serviço principal do espelhamento. Em seguida, na barra de ferramentas solicite uma nova query (Ver Figura 6).
Figura 6. Solicitando uma nova query Listagem 1. Criando banco de dados e tabelas USE [MASTER] GO -- CRIA O BANCO DE DADOS CREATE DATABASE SQLMagazine GO USE [SQLMAGAZINE] GO -- TABELA CLIENTE CREATE TABLE [dbo].[CLIENTE]( [PKID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [RAZAO_SOCIAL] [varchar](50) NULL, [NOME_FANTASIA] [varchar](50) NULL, [CPF_CNPJ] [varchar](18) NOT NULL, [TIPO] [int] NULL, [DATA_CADASTRO] [datetime] NOT NULL CONSTRAINT [DF_ DATA_CADASTRO] DEFAULT (getdate()), [MUNICIPIO] [varchar](50) NULL, [ENDERECO] [varchar](60) NULL, [NUMERO] [varchar](7) NULL, [BAIRRO] [varchar](30) NULL, [COMPLEMENTO] [varchar](40) NULL, [CEP] [varchar](10) NULL )GO
-- TABELA PRODUTO CREATE TABLE [dbo].PRODUTOS( [PKCODIGO] [varchar](20) PRIMARY KEY CLUSTERED NOT NULL, [VALOR_UNITARIO] [decimal](18, 2) NULL, [STATUS] [bit] NOT NULL, [PRECO_VENDA] [decimal](18, 2) NOT NULL, [QTDE_ESTOQUE] [decimal](18, 4) NULL, [DATA_VALIDADE] [datetime] NULL ) GO -- TABELA VENDA CREATE TABLE [dbo].[VENDA]( [PKID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT N OT NULL, [CLIENTE_PKID] [int] NULL, [PRODUTO_PKCODIGO] [varchar](20) NULL, [DATA_VENDA] [datetime] NULL, [QUANTIDADE] [decimal](18, 2) NULL, [VALOR_TOTAL] [decimal](18, 2) NULL ) GO -- CRIANDO O RELACIONAMENTO DAS TABELAS -- ENTRE VENDA/CLIENTE ALTER TABLE [dbo].[VENDA] WITH CHECK ADD CONSTRAINT [FK_VENDA_CLIENTE] FOREIGN KEY([CLIENTE_PKID]) REFERENCES [dbo].[CLIENTE] ([PKID]) GO -- CRIANDO O RELACIONAMENTO DAS TABELAS -- ENTRE VENDA/PRODUTO ALTER TABLE [dbo].[VENDA] WITH CHECK ADD CONSTRAINT [FK_VENDA_PRODUTO_SERVICO] FOREIGN KEY([PRODUTO_PKCODIGO]) REFERENCES [dbo].[PRODUTOS] ([PKCODIGO]) GO Agora que possuímos nosso banco de dados, vamos preparar o nosso ambiente. É necessário ter uma atenção especial na preparação inicial do espelhamento de banco de dados, cuidando para atender todos os pré-requisitos. Sendo eles: - Os servidores que você escolher para o espelhamento devem possuir a mesma edição do SQL Server 2005/2008. Sendo que as versões que permitem o espelhamento são SQL Server Enterprise e SQL Server Standard, para os papéis do banco principal e espelho. A terceira instância, que é responsável pelo failover, poderá utilizar as seguintes versões: SQL Server Express, SQL Server Workgroup. Para verificar as versões, você deve executar uma consulta em todas as instâncias que serão utilizadas. Para tal, abra uma nova query (Figura 6), digite e execute a consulta mostrada na Figura 7. Verifique se todos os servidores estão se comunicam. Está verificação pode ser feita dando um ping nos servidores através dos seguintes passos: - Menu Iniciar -> Executar; - Digite CMD; - Na janela que aparece, digite ping [Nome Servidor], conforme pode ser visualizado na Figura 8.
Figura 7. Verificando a versão do SQL Server
Figura 8. Verificando a comunicação Repita o processo nos outros servidores, disparando o comando de um para outro, por exemplo, ping SRV01 - no servidor SRV02; ping SRV02 - no servidor SRV01. - O banco de dados principal deve estar configurado com o modo de recuperação FULL. Execute a Listagem 2 em uma nova query para configurar está opção. Após concluir os pré-requisitos, poderemos iniciar a configuração do espelho do banco de dados. Em uma ambiente de produção, o ideal é que cada instância esteja em máquinas diferentes, mas a título de teste você pode instalar três instâncias na mesma máquina. m áquina. Para iniciar o processo, conecte-se na instância que será o principal. Deve-se realizar um backup completo e um backup de log. Este backup será restaurado na instância que será o espelho, isto é necessário para sincronizar as informações. Após o backup, o ideal é que nenhum aplicativo adicione novos dados no banco principal. Para realizar os backups, execute a Listagem 3 em uma nova query. Com os backups realizados, o próximo passo é restaurá-los na instância que será o espelho. Copie os arquivos para o servidor espelho, conecte-se na instância que possuíra o espelho do banco. Abra uma nova query e execute o código da Listagem 4. Listagem 2. Alterando o modo de recuperação USE [master] GO ALTER DATABASE [SQLMagazine] SET RECOVERY FULL GO Listagem 3. Realizando o backup do banco de dados SQLMagazine USE [master] GO -- BACKUP COMPLETO BACKUP DATABASE SQLMagazine TO DISK=C:\Backup\BKPSQLMagazine.bak WITH INIT GO
-- BACKUP DO LOG BACKUP LOG SQLMagazine TO DISK=C:\Backup\BKPLOG_SQLMagazine.trn WITH INIT GO Listagem 4. Restaurando o banco de dados SQLMagazine no servidor espelho USE [master] GO -- RESTAURANDO OS ARQUIVOS RESTORE DATABASE SQLMagazine FROM DISK=C:\Backup\BKPSQLMagazine.bak DISK=C:\ Backup\BKPSQLMagazine.bak WITH NORECOVERY GO -- RESTAURANDO O LOG BACKUP LOG SQLMagazine TO DISK=C:\Backup\BKPLOG_SQLMagazine.trn WITH R EPLACE, NORECOVERY GO Ao restaurar o banco de dados espelho, verifique se o banco de dados possui o mesmo nome do banco principal, e a restauração deve ser no modo WHIT NORECOVERY, N ORECOVERY, conforme Listagem 4. Se possível, o caminho do banco de dados espelho deve ser idêntico ao caminho do banco de dados principal. Se os caminhos não forem idênticos, será necessário adicionar a opção MOVE na instrução de restauração (ver Listagem 5). Listagem 5. estaurando o banco de dados (MOVE) USE [master] GO RESTORE DATABASE SQLMagazine FROM DISK=C:\Backup\BKPSQLMagazine.bak REPLACE,NORECOVERY, MOVE SQLMagazine_Data TO F:/Dados/SQLMagazine_Data.mdf, F:/ Dados/SQLMagazine_Data.mdf, MOVE SQLMagazine_Log TO F:/Dados/SQLMagazine_Log.ldf GO
WITH
Concluída esta etapa, você deverá possuir uma imagem semelhante à Figura 9 na instância do banco de dados espelho.
Figura 9. Banco de Dados Espelho
Com o servidor espelho preparado, retornaremos para o servidor principal para configurar o espelhamento. Para isto, conecte-se na instância que possui o banco principal. No Object Explore, clique com o botão direito no banco, nas opções que aparecem selecione Task à Mirror (Figura 10). Em seguida, aparecerá uma nova janela (Figura 11) onde você configurará a conexão entre os servidores e modo de operação, que poderá ser escolhido uma das três opções disponíveis. Após a configuração do espelhamento elas serão habilitadas. As opções disponíveis são: - High Availability: Para maximizar o desempenho, o banco de dados espelho fica sempre um pouco atrás do banco de dados principal, isto é, há uma demora para sincronizar todos os dados do banco. Porém, a lacuna entre os bancos de dados é geralmente pequena. A perda de um parceiro tem o seguinte efeito: o Se a instância do servidor espelho ficar indisponível, o principal continuará. o Se a instância do servidor principal ficar indisponível, o espelho irá parar; mas se a sessão não tiver um servidor testemunha (como recomendado) ou se o servidor testemunha estiver conectado ao servidor espelho, o servidor espelho ficará acessível como espera passiva e o proprietário do banco de dados poderá forçar o serviço para a instância do servidor espelho (com possível perda de dados).
Figura 10. Acessando a opção de criação de Espelho (Mirror)
Figura 11. Configurando o espelho - High Protection: Todas as transações confirmadas têm a garantia de serem gravadas no disco do servidor espelho. O failover manual é possível quando os parceiros estão conectados um ao outro e o banco de dados está sincronizado. A perda de um parceiro tem o seguinte efeito: * Se a instância do servidor espelho ficar indisponível, o principal continuará. * Se a instância do servidor principal ficar indisponível o espelho irá parar, mas ficará acessível como espera passiva e o DBA poderá forçar a inicialização do serviço do servidor espelho (com possível perda de dados). - High Performance: Todas as transações confirmadas têm a garantia de serem gravadas no disco no servidor espelho. A disponibilidade é maximizada incluindo uma instância do servidor testemunha para dar suporte ao failover automático. Observe que você só pode selecionar a opção Alta segurança com failover automático (síncrono) se tiver antes especificado o endereço de um servidor testemunha. Na presença de um servidor testemunha, a perda de um parceiro tem o seguinte efeito: ef eito: * Se a instância do servidor principal ficar indisponível, ocorrerá failover failover automático. A instância do servidor espelho é alternada para a função principal e oferece seu banco de dados como banco de dados principal. * Se a instância do servidor espelho espelho ficar indisponível, o principal principal continuará. Feito isso, clique no botão Configure Security... (Figura 11). Aparecerá um Wizard que ajudará a configurar o espelhamento. Clique next nesta primeira janela.
A primeira etapa a ser configurada é se a sessão de espelhamento possuirá um servidor de testemunha. No nosso exemplo, precisamos do failover automático, então selecionaremos a opção Yes. Clique em next. Aparecerá uma lista com os servidores que deverão fazer parte do espelhamento (Figura 12). Deixe as opções padrão e clique em next. A próxima etapa consiste em criar as conexões entre os servidores. Para tal, o SQL Server criará um endpoint, que é um objeto que permite a comunicação entre a rede. Quando o espelhamento é configurado, a instância requer seu próprio e dedicado endpoint mirroring, que é usado exclusivamente para receber a comunicação entre os bancos de dados (principal, espelho e testemunha).
Figura 12. Servidores que serão configurados As Figuras 13, 14 e 15 mostram essa configuração. Você deverá identificar cada instância SQL Server que irá participar e informar uma porta (se as instâncias estivem em máquinas diferentes pode deixar a porta default; caso contrário deverá informar portas diferentes). Para a conexão nos servidores, utilize Windows Authentication se estiverem no mesmo domínio, senão utilize a SQL Authentication, informando um usuário e senha.
Figura 13. Principal Server
Figura 14. Server Mirror
Figura 15. Server Witness Após terminar de configurar o Witness e clicar em next, aparecerá uma janela pedindo para informar a conta que deve iniciar o serviço (Figura 16). Se a mesma conta iniciar todos os serviços ,você poderá deixar as caixas em brancos, caso contrário deverá informar uma conta que possua permissões de acesso em todos os servidores.
Figura 16. Contas de Serviço
Clique em next, aparecerá uma listagem com todos as configurações que foram efetuadas. Se estiver tudo de acordo clique em Finish, e o SQL Server irá criar todas as configurações. Se tudo estiver correto você verá a Figura 17.
Figura 17. Finalizando a criação dos endpoints
Ao clicar em close, aparecerá uma mensagem se você deseja iniciar o espelhamento (Ver Figura 18).
Figura 18. Iniciando o espelhamento Clique no botão iniciar, para que o espelhamento comece. Como configuramos um servidor testemunha, ele iniciará com o modo High Performance. Se tudo estiver ocorrido bem você verá a seguinte imagem no Object Explorer (Figura 19).
Figura 19. Verificando o espelhamento
Pronto! O espelhamento está configurado e iniciado. Agora você pode inserir, alterar ou excluir os dados ou criar novas tabelas, que as mudanças serão refletidas para o banco espelho. Para testar isto, vamos criar uma nova tabela, bem simples, apenas para teste. Execute a Listagem 6 para criar uma nova tabela no banco de dados principal. Listagem 6. Criando uma nova tabela USE [SQLMagazine] GO CREATE TABLE TB01( COD INT NOT NULL) GO Vamos verificar agora se a tabela foi replicada para o banco espelho. Iremos parar o serviço do banco de dados principal. Com a interrupção do serviço, deverá ocorrer um failover automático para o banco espelho. Para isto, selecione com o botão direito do mouse na instância principal. Nas opções que aparecem cliquem em Stop (Figura 20).
Figura 20. Parando serviço do banco principal
Atualize as instâncias e poderá ser verificado que agora quem está como principal é a segunda instância (Figura 21).
Figura 21. Verificando o failover Conforme podemos visualizar na Figura 21, a segunda instância assumiu os serviços do banco principal. Verificamos também que a tabela que foi criada no banco principal foi replicada para o espelho, possuindo a mesma estrutura, dados e informações. É possível visualizar que ao lado do banco vemos o status do banco, que aparece SQLMagazine (Principal, Disconnected). A mensagem Desconectado aparece por que o outro parceiro ainda não está no ar. Quando iniciarmos o serviço novamente, os bancos ficarão como a tela mostrada na Figura 19, apenas com os papéis trocados. O espelhamento é útil quando os dados devem estar sempre disponíveis. Assim poderemos ter uma alternativa rápida de troca de serviços quando um problema acontece ou quando o servidor principal precisa passar uma manutenção, que exigem deixá-lo deixá -lo indisponível. Conclusão A alta disponibilidade tem como objetivo eliminar as paradas não planejadas. Paradas não planejadas ocorrem por defeitos, já as paradas planejadas são normalmente por causa de atualizações, manutenção preventiva e atividades correlatas. Desta forma é preciso identificar primeiramente todas as necessidades de negócios da empresa para que se possa definir a correta opção de alta disponibilidade. No artigo foram apresentadas, de forma resumida, as diversas opções que o SQL Server disponibiliza para a alta disponibilidade dos dados, assim como foi demonstrado como é configurado um espelhamento do banco de dados, que é uma das opções de alta disponibilidade. Possuindo assim as informações em outro servidor que poderá assumir o papel de principal sem que os usuários percebam e sem grandes transtornos.
Compactação de Dados com o SQL Server 2008 Pedro Antonio Galvão Junior Experiência de mais de 14 anos na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação na Faculdade FAC São Roque (Filiada a Faculdades Uninove de São Paulo). Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Certificado Microsoft MVP - Most Valuable Profissional na competência Windows Server System - SQL Server. Formação MCDBA Microsoft SQL Server 2000. Especialista na Administração de Servidores de Banco de Dados, Coordenador de Projetos e Processos relacionados a área de TI. Atuou em diversas empresas e instituições acadêmicas na região do São Roque e Sorocaba. De que trata o artigo: Neste artigo veremos as formas de compactação de dados existente no Microsoft SQL Server 2008. Em seguida, demonstraremos como utilizar cada uma destas formas, com base em duas tabelas contendo dados fictícios. Para que serve: A compactação de dados tem como objetivo proporcionar um melhor dimensionamento de espaço em disco necessário para alocar dados existentes em tabelas do Microsoft SQL Server 2008. Procurando evitar qualquer tipo de aumento no tempo de processamento necessário para armazenar ou consultar estes dados compactados. Em que situação o tema útil: ú til: A compactação de dados é uma técnica muito útil para ambientes com falta de espaço em disco, mas que possuem uma grande necessidade de armazenamento de dados. Sua utilização reflete diretamente na perda de tempo e esforço necessário para alocar os dados armazenados nas tabelas ou índices que utilizam compactação em linha de linha ou páginas pág inas de dados. Além disso, a compactação de dados pode trazer alguns benefícios em relação à diminuição da fragmentação de dados armazenados em uma tabela que esteja utilizando o nível de compactação em linha.
Quando falamos em armazenamento de dados, sempre pensamos na necessidade que temos em guardar uma informação em local seguro, confiável e íntegro. A evolução da capacidade de armazenamento de dados ocorrido nos últimos anos ofereceu às empresas recursos que permitem armazenar e gerenciar grandes volumes de informação, independente da sua origem. Acompanhando este crescimento e evolução, as empresas desenvolvedoras de Sistemas Gerenciadores de Bancos de Dados identificaram como pré-requisito para seus produtos a capacidade de armazenar qualquer tipo de informação, sendo elas arquivos de áudio, vídeo, apresentações, ou si mplesmente um dado. Mas o aumento da capacidade de armazenamento também obrigou estas empresas a se preocuparem com o gerenciamento deste deste volume de informações, informações, e, ainda mais, a buscarem uma melhor forma para alocar informações evitando desperdícios da capacidade de armazenamento, sem ocasionar aumento no tempo de processamento. Com base no atual momento tecnológico e procurando manter seus produtos atualizados, a Microsoft decidiu fazer algumas mudanças no formato de compactação de dados realizada pelo SQL Server 2008, oferecendo suporte nativo a esta funcionalidade. Utilizando as funcionalidades de compactação de dados existentes no SQL Server 2008, torna-se possível realizar esta tarefa economizando espaço de armazenamento, mas, em algumas situações, ocasionando um pequeno aumento de processamento e tempo de execução. Neste artigo, iremos apresentar esta nova funcionalidade, provida a partir das versões Standard e Enterprise do SQL Server 2008
Conhecendo a compactação de dados A possibilidade de compactação de dados no SQL Server surgiu no lançamento do Service Pack 2 para o SQL Server 2005, com base no formato de armazenamento vardecimal (sendo um formato de armazenamento, não um tipo de dados).Anteriormente o Microsoft SQL Server não apresentava recursos relacionados a compactação de dados. Analisar a melhor forma para se alocar um dado em uma tabela sem gerar fragmentação ou desperdício de espaço em disco era de total responsabilidade e dever do administrador de banco de dados (DBA) ou administrador de dados (DA).O SQL Server 2008 oferece suporte a compactação de linha e de página para tabelas e índices. A compactação de dados pode ser configurada para os seguintes objetos do banco de dados: - Uma tabela inteira que é armazenada como um heap; - Uma tabela inteira que é armazenada como um índice clusterizado; - Um índice não clusterizado inteiro; - Uma view indexada inteira. A partir SQL Server 2005 Service Pack 2 e versões posteriores, tipos de dados como decimal e numeric tornaram-se mais versáteis e compatíveis com o formato de armazenamento vardecimal. Este formato de dados possibilita a redução do tamanho ocupado pelos dados, podendo ocasionar um pequeno aumento no tempo de processamento.Quando utilizamos vardecimal o SQL Server deverá verificar inicialmente o tamanho da informação que será armazenada e, logo após, estabelecer o quanto de espaço será necessário para sua alocação. Caso o dado que será armazenado esteja compactado em nível de página, o SQL Server terá a missão de identificar a melhor posição de armazenamento dentro da página de dados, evitando a alocação desnecessária em outra página, sem gerar disperdício de espaço ou aumentando o tempo de processamento. Entendendo a compactação de dados Compactar um dado parece ser uma tarefa fácil, tendo em vista as diversas ferramentas ou aplicações compactadoras de arquivos existentes no mercado. Além disso, atualmente a grande maioria dos sistemas operacionais apresenta este tipo de recurso. Em um Sistema Gerenciador de Banco de Dados o recurso de compactação é um pouco diferente em relação a estas ferramentas. O Microsoft SQL Server 2008 apresenta este recurso de forma nativa, sem necessitar de ferramentas externas ou de terceiros para trabalhar sobre as informações armazenadas em tabelas ou índices. Realizando uma análise de acordo acordo com os dados que se encontram encontram armazenados nestes objetos e possibilitando aplicar a melhor forma de compactação. O processo de compactação necessita de uma identificação prévia da forma que o dado se encontra ou será armazenado. Na versão atual, o SQL Server 2008 estabelece duas formas básicas de compactação, chamadas: Compactação por linha de dados d ados (registros) e Compactação por página de dados. Não podemos dizer que existe a melhor forma de compactação ou a forma mais correta para realizar este processo. O que existe é a necessidade de compactar um dado mediante o seu estado es tado atual. Na compactação em nível de linha de dados, o SQL Server deverá procurar dimensionar cada linha de registros armazenadas em uma tabela ou índice da forma a evitar fragmentação de dados, seja em uma nova linha ou a necessidade de criar mais uma página de dados. Na compactação em nível de página de dados, a tarefa do SQL Server é um pouco mais complicada. O processo de dimensionamento da informação não consiste simplesmente em identificar o tamanho do dado ou da linha, mas sim em estabelecer em qual página de dados aquele conjunto de informações poderá ser alocada, respeitando inicialmente os dados já armazenados na página como também a informação que poderá ser repassada para outra página ou a criação de uma nova página. Durante a leitura deste artigo você poderá identificar as diversas características e peculiaridades existentes nos dois tipos de compactação. Estabelecer qual será a mais indicada para sua necessidade não é
tarefa deste artigo, nosso objetivo é apresentar e demonstrar como utilizar este recurso muito útil e de extrema importante. Conhecendo a compactação em nível de linha de dados d ados Como destacado anteriormente, a compactação em nível de linha de dados representa um recurso para dimensionamento e alocação de informações para cada linha de informações (registros), armazenadas em uma tabela ou índice. Sua utilização está diretamente relacionada com cada informação manipulada sobre a tabela configurada para trabalhar com este tipo de compactação. Antes de utilizar a compactação de linhas de dados, torna-se necessário conhecer algumas características e considerações importantes desta forma de compactação, entre elas: - A compactação pode permitir que mais linhas sejam armazenadas em uma página devido à diminuição do tamanho do dado que será alocado em cada linha. Isso é alcançado sem ultrapassar o tamanho por linha e evitando gerar qualquer tipo de fragmentação fragm entação dos dados; - Somente as edições Enterprise e Developer do SQL Server 2008 possuem a capacidade de trabalhar com compactação de linhas e páginas; - Uma tabela não pode ser habilitada para compactação quando o tamanho máximo da linha mais a sobrecarga de compactação exceder o tamanho máximo de linha de 8060 bytes. Por exemplo, uma tabela que tem as colunas col1 char (8000) e col2 char (53) não pode ser compactada por causa da sobrecarga de compactação adicional; - Para a compactação de linha e de página, a verificação do tamanho da linha é executada quando o objeto é inicialmente compactado e, depois, verificado à medida que cada linha é inserida ou modificada. A compactação impõe as seguintes regras: - Quando a estrutura de uma tabela é modificada, a compactação existente é preservada, a menos que especificada de outra maneira, através do número da partição ou da lista de partições. Esta lista de partições corresponde à quantidade de partições existentes em uma Tabela. Caso seja especificado um valor ou uma faixa de valores fora do número de partições existentes o SQL Server será forçado a emitir uma mensagem de erro; - Índices não clusterizados não herdam a propriedade de compactação da tabela. Para compactar índices é preciso definir explicitamente a sua propriedade de compactação. Por padrão, a configuração de compactação de índices será definida como NONE quando o índice for criado; - Quando um índice clusterizado é criado em um heap, ele herda o estado de compactação do heap, a menos que um estado de compactação alternativo seja especificado. - Uma atualização para um tipo de comprimento fixo sempre deve ter êxito, por exemplo, se utilizamos uma coluna do tipo varchar (10) e alterarmos para um campo char (10); - A desabilitação da compactação de dados sempre deve ter êxito. Mesmo que a linha compactada caiba em uma página (o que significa que ela é menor do que 8060 bytes). Em alguns casos, a linha descompactada poderá sofrer atualizações que possam gerar a necessidade de armazenar estas alterações em outra página de dados, mesmo que a atual página pági na possua um pequeno espaço livre. - Quando uma lista de partições é especificada, o tipo de compactação deve ser definido como ROW, PAGE ou NONE em partições individuais, possibilitando uma melhor alocação de espaço; A Tabela 1 apresenta um exemplo de como a compactação de dados em nível de linha possibilita a diminuição do consumo do armazenamento de dados. Como a compactação de linha afeta o armazenamento A Tabela 2 descreve como a compactação de linha afeta os tipos existentes no SQL Server. Ela não destaca o possível aumento do tamanho físico de uma tabela caso a compactação utilizada esteja definida no nível de página de dados. Em algumas situações, o nível de compactação de página de dados poderá ocasionar o armazenamento de dados em novas páginas. Desta forma, o SQL Server será obrigado a utilizar mais espaço físico do disco rígido ríg ido para armazenamento destas informações. A compactação em nível de linha reduz a quantidade de metadados usado para armazenar a linha, ou seja, de acordo com tamanho informado para este tipo de dado, o SQL Server deverá reservar e dimensionar o espaço de alocação para o dado independente do tamanho real que o dado for ocupar.
A partir do momento em que utilizamos a compactação de dados sobre tipos de dados de tamanho fixo, Char, Nchar, entre outros. O SQL Server irá realizar o mesmo procedimento para dados de formato variável, ou seja, se o dado CHAR (100) utilizar apenas 10 caracteres, os espaços em branco não utilizados serão descartados, podendo assim reduzir o espaço necessário para seu armazenamento.
Tabela 1. Compactação de dados aplicada em nível de linha.
Tabela 2. Como a compactação em nível de linha afeta cada tipo de dados.
Por outro lado, não serão compactados valores em campos de tamanho fixo ou variável, caso a infomação passada apresentar valores nulos (NULL) ou for simplesmente um número 0 (zero), para a compactação em nível de linha. Neste caso, não ocorrerá nenhum ganho de armazenamento a rmazenamento se comparado com o tamanho a original ocupado sem a compactação. A seguir destacaremos a forma de compactação em nível de página de dados, suas características e considerações. Conhecendo a compactação em nivel de páginas de d e dados Como destacado anteriomente, a compactação em nível de página de dados está relacionada diretamente com as informações armazenadas em cada página de dados que compõem uma tabela. Esse recurso é uma tarefa um pouco mais complicada em relação à compactação em nível de linha de dados. O processo de dimensionamento da informação não consiste simplesmente em identificar o tamanho do dado ou da linha, mas sim em estabelecer em qual página de dados aquele conjunto de informações poderá ser alocada, respeitando inicialmente os dados já armazenados na página como também a informação que poderá ser repassada para outra página ou a criação de uma nova página. Quando uma tabela é criada e seu nível de compactação foi definido como página, o SQL Server não realizará qualquer tipo de compactação. A partir do momento em que os dados começarem a ser adicionados, os mesmos serão alocados na primeira página de dados, mas utilizando a compactação por linha. Este procedimento é necessário para que o SQL Server consiga identificar a página que o dado será alocado posteriormente. A compactação por página será realizada conforme a inserção de novos dados. Durante o processo de inserção de dados, o SQL Server deverá dimensionar o tamanho de alocação destes dados para cada linha, não permitindo que o conjunto de dados ultrapasse o tamanho de 8060 bytes. Quando este valor é ultrapassado, o SQL Server identificará esta linha de registro como uma linha cheia e inicia o processo de alocação do dado para uma próxima linha. Esta alocação será realizada utilizando a compactação em nível de página. Por outro lado, se o espaço obtido pela compactação de página for menor que o espaço exigido para o armazenamento dos dados, a compactação de página não será utilizada para página. Caso a compactação de página tenha criado espaço suficiente na página para uma linha adicional, esta linha será adicionada e os dados serão compactados por linha e página. O armazenamento da informação nesta página será realizada após uma revisão em cada coluna que compõem a tabela avaliada. Para realizar esta avaliação e validação o SQL Server utiliza por padrão a chamada compactação de prefixo. Em seguida o SQL Server definirá se utiliza a compactação de prefixo ou compactação por dicionário. Tanto a compactação por prefixo e dicionário serão destacadas posteriormente, A s linhas futuras serão ajustadas à nova página se não couberem na página atual. O SQL Server deverá adicionar à tabela uma nova página de dados semelhante à primeira página. Esta nova página não será compactada imediatamente, ou seja, esta página deverá ser dimensionada a partir do momento em que uma das linhas de dados ultrapassar o seu tamanho máximo. Assim, devemos destacar que a compactação de páginas de dados também necessita de uma análise sobre algumas caractéristas e considerações importantes antes da sua apli cação, entre elas: - Quando um heap é configurado para compactação em nível de página, as páginas só recebem compactação em nível de página nos seguintes modos: * Os dados são inseridos usando a sintaxe BULK INSERT; * Os dados são inseridos usando INSERT INTO ... Sintaxe WITH (TABLOCK); * Uma tabela é recriada executando ALTER TABLE ... Instrução REBUILD com a opção de compactação PAGE. - As novas páginas alocadas em um heap como parte de operações DML não usarão a compactação PAGE até o heap ser recompilado; recompil ado; - A alteração da configuração de compactação de um heap exige que todos os índices não clusterizados na tabela sejam recriados, para que tenham ponteiros para os novos locais de linha no heap;
- Os requisitos de espaço em disco para habilitar ou desabilitar a compactação de página ou de linha são os mesmos que para criar ou recriar um índice. Para dados particionados você pode reduzir o espaço exigido para habilitar ou desabilitar a compactação para uma partição de cada vez; - Para determinar o estado de compactação das partições em uma tabela particionada, consulte a coluna data_compression existente no catálogo de visões (view catalog), chamada sys.partitions; - Quando você estiver compactando índices, as páginas de nível folha poderão ser compactadas com a compactação de linha e de página. As páginas que não são de nível folha não recebem a compactação de página; - A compactação de dados não está disponível para os dados armazenados ar mazenados separadamente. A compactação de página é semelhante para tabelas, partições de tabela, índices e partições de índice. A compactação do nível folha de tabelas e índices usando a compactação de página consiste em três operações, nesta ordem: 1. Compactação de linha; 2. Compactação de prefixo; 3. Compactação de dicionário. Este tipo compactação é mais eficiente pois oferece um ganho a mais na compressão, entretanto, proporciona um aumento na utilização da CPU. Quando você usa a compactação de página, as páginas do nível não-folha dos índices são compactadas usando apenas a compactação de linha. l inha. Compactação em nível de página utilizando a compactação por prefixo p refixo Nesta forma de compactação o SQL Server utiliza um caractere identificador chamado prefixo para procurar dados que possam apresentar características compatíveis para esta técnica de compactação. Este caractere deverá identificar em cada informação armazenada sobre as colunas analisadas, os dados que podem ser compactados. Para cada página que está sendo compactada, a compactação de prefixo usa três etapas para estabelecer a melhor forma de compactação: c ompactação: 1. Para cada coluna avaliada é identificada qual informação poderá ser compactada. Isto é feito com o objetivo de reduzir o espaço de armazenamento para os valores de cada coluna; 2. Uma linha que representa os valores de prefixo de cada coluna é criada e armazenada em uma estrutura CI (informações de compactação) que segue imediatamente o cabeçalho da página; 3. Os valores de prefixo repetidos da coluna são substituídos por uma referência ao prefixo correspondente. Se o valor de uma linha não corresponder exatamente ao valor do prefixo selecionado, deverá ser indicada uma correspondência parcial. A Figura 1 a mostra um exemplo de página de uma tabela antes da compactação de prefixo.
Figura 1. Exemplo da página de dados antes da compactação do prefixo.
A Figura 2 mostra a mesma página após a compactação de prefixo. O prefixo é movido para o cabeçalho e os valores da coluna são alterados para referências ao prefixo. Na primeira linha da primeira coluna o valor 4b indica que os primeiros quatro caracteres do prefixo (aaab) estão presentes para essa linha e, também, o caractere b na área de cabeçalho da página. Isso gera o valor resultante aaabb, que é o valor original.
Figura 2. Exemplo da página de dados após a compactação do prefixo. Compactação em nível de página utilizando a compactação por dicionário d icionário Após entendermos como é realizada a compactação de prefixo, podemos agora conhecer a compactação de dicionário. A compactação de dicionário procura valores repetidos em qualquer lugar da página e os armazena na área á rea de informações de compactação. Diferentemente da compactação de prefixo, a compactação de dicionário não é restrita a uma coluna. A compactação de dicionário pode substituir valores repetidos que ocorrem em qualquer lugar de uma página. A Figura 3 mostra o mesmo exemplo da Figura 1 após a compactação de dicionário.
Figura 3. Exemplo página de dados após a compactação do dicionário.
O SQL Server realizou uma busca para identificar todos os dados repetidos, deslocando os mesmos para a área de compactação no cabeçalho da página de dados. Observe que os valores [0bbbb] que se encontravam repetidos em duas colunas distintas agora o está armazenado no cabeçalho e possui um valor de identificação. Neste caso, o número 1 é o número identificador dos dados que estavam armazenados nestas colunas. Agora que já conhecemos um pouco mais sobre as duas formas de compactação, suas principais características e particularidades, o que nos resta é por a mão na massa e utilizar estes recursos. Para isso criaremos um ambiente de demonstração trabalhando com um conjunto de informações fictícias para auxiliar e melhorar nosso entendimento sobre o assunto. A seguir veremos como aplicar a compactação de dados utilizando o nível de compactação por linha de dados e posteriormente a compactação de página de dados será abordada. Aplicando a compactação de dados A forma de aplicação da compactação de dados consiste na utilização das funcionalidades disponíveis no Microsoft SQL Server 2008 sobre as tabelas e índices disponíveis. Iniciaremos o processo de demonstração do uso destes recursos em nível de linhas, através da criação do banco de dados SQLMagazine, conforme a Listagem 1. Posteriormente criaremos duas tabelas chamadas Revistas e RevistasCompactadas, onde a tabela Revistas não sofrerá nenhum tipo de compactação de dados. O código para criação das tabelas pode ser visto na Listagem 2. O processo de compactação de dados pode ser definido no momento da criação de uma nova tabela ou índice, fazendo uso das instruções CREATE TABLE, de acordo com o Bloco 2 da Listagem 2. Listagem 1. Criação do Banco de dados -- Bloco 1 -Create Database SQLMagazine Go Use SQLMagazine Go Listagem 2. Criação das tabelas Revistas Revi stas e RevistasCompactadas -- Bloco 1 -Create Table Revistas (Codigo SmallInt Identity(1,1) Primary Key, Descricao Varchar(50), Edicao Int Default(1), AnoPublicacao Int Default(2009)) On [Primary] Go -- Bloco 2 -Create Table RevistasCompactadas (Codigo SmallInt Identity(1,1) Primary Key, Descricao Varchar(50), Edicao Int Default(1), AnoPublicacao Int Default(2009)) On [Primary]
Agora que já temos o Banco e as tabelas criadas, vamos povoar estas tabelas com informações fictícias para ilustrar nosso exemplo. Acompanhando a Listagem 3, encontramos as instruções para colocar informações nas tabelas Revistas e RevistasCompactadas. Listagem 3. Inserindo dados nas tabelas Revistas e RevistasCompactadas -- Bloco 1 -Declare @Cont Int Set @Cont=1 While (@Cont <= 10000) Begin Insert Into Revistas Values (SQL Magazine,@Cont,2009) Set @Cont +=1; End Go -- Bloco 2 -Declare @Cont Int Set @Cont=1 While (@Cont <= 10000) Begin Insert Into RevistasCompactadas Values (SQL Magazine,@Cont,2009) Set @Cont +=1; End Go Agora, ambas as tabelas possuem informações simulando tabelas verdadeiras. Se consultarmos os dados armazenados em cada tabela, poderemos observar que a inserção de dados ocorreu normalmente. A seguir, a Figura 4 apresenta uma pequena relação de registros armazenados nas tabelas Revistas e RevistasCompactadas.
Figura 4. Dados armazenados nas tabelas Revistas Revi stas e RevistasCompactadas.
Na Figura 4 podemos observar visualmente que a estrutura das tabelas e os dados existentes em cada uma não apresentam diferenças, sendo que, a tabela RevistasCompactadas está neste momento configurada para trabalhar com compactação de dados em nível de linhas. Agora vamos comparar o espaço físico ocupado por cada tabela fazendo uso da system stored procedure sp_spaceused definida na Listagem 4. O resultado da execução desta stored procedure é exibido na Figura 5. Listagem 4. Consultando o espaço físico ocupado por cada tabela -- Bloco 1 -sp_spaceused Revistas Go -- Bloco 2 -sp_spaceused RevistasCompactadas Go
Figura 5. Comparativo entre a tabela Revistas e RevistasCompactadas. Analisando os resultados gerados através da system stored procedure sp_spaceused, podemos observar a diferença de tamanho no espaço ocupado ploes dados na tabela RevistasCompactadas em relação a tabela Revistas. O próximo passo é realizar algumas alterações na forma de compactação dos dados, iniciando pela mudança do nível de compactação de linha para página, de acordo com a Listagem 5. Listagem 5. Alterando o nível de compactação da tabela RevistasCompactadas -- Bloco 1 -Alter Table RevistasCompactadas Rebuild With (DATA_COMPRESSION=PAGE) Go Após a alteração na forma de compactação realizada na tabela RevistasCompactas, devemos verificar se o espaço ocupado fisicamente por esta tabela sofreu alguma mudança. Para isso, executaremos o código apresentado na Listagem 6. Você poderá observar alguma semelhança entre os resultados apresentados na Figura 6. Listagem 6. Consultando o espaço físico ocupado por cada tabela em nível de pagina -- Bloco 1 -sp_spaceused Revistas Go -- Bloco 2 -sp_spaceused RevistasCompactadas Go
Figura 6. Comparativo entre a tabela Revistas e RevistasCompactadas com compactação em nível de página. Mais uma vez a compactação de dados nos apresenta algumas mudanças em relação aos dados armazenados em uma tabela. Neste caso, observamos de forma clara que a compactação em nível de página de dados dimensionou consideravelmente a alocação de dados, como também diminuiu o espaço não alocado para o armazenamento dos dados compactados. Agora devemos verificar se esta alteração ocasionou alguma mudança nos dados armazenados na tabela RevistasCompactadas. Podemos consultar alguns registros, conforme demonstrado na Figura 7.
Figura 7. Dados armazenados nas tabela RevistasCompactadas. R evistasCompactadas.
Estimando o tamanho da tabela de acordo com sua compactação Depois de vários testes realizados, temos a certeza de que a compactação de dados em nível de linhas ou páginas de dados pode apresentar diferenças no armazenamento físico dos dados. Agora vamos conhecer como podemos realizar uma estimativa do tamanho de uma tabela de acordo com sua compactação. A compactação pode ser avaliada para tabelas inteiras ou partes de tabelas. Isso inclui heaps, índices clusterizados, índices não clusterizados, exibições indexadas e partições de tabelas e índices. Estruturas de tabela podem ser compactadas usando compactação de linha ou de página. Se a tabela, índice ou partição já estiverem compactadas, é possível usar esse procedimento para estimar o tamanho da tabela, do índice ou da partição se eles forem descompactados. Para realizar esta estimativa do tamanho de uma tabela devemos utilizar a system stored procedure sp_estimate_data_compression_savings, conforme a sintaxe apresentada na Listagem 7 e descrita na Tabela 3.
Listagem 7. Sintaxe S intaxe da sp_estimate_data_compression_savings -- Bloco 1 -sp_estimate_data_compression_savings [ @schema_name = ] schema_name , [ @object_name = ] object_name , [@index_id = ] index_id , [@partition_number = ] partition_number , [@data_compression = ] data_compression [;]
Tabela 3. Conjunto de resultados retornados para fornecer o tamanho atual e estimado da tabela, índice ou partição. No código apresentado na Listagem 7: - [ @schema_name = ] schema_name: É o nome do esquema de banco banco de dados que contém a tabela ou visão indexada. Se schema_name não for informado, ou seja, considerado NULL, o esquema padrão do usuário atual será usado, pois o SQL Server não considera um schema_name definido como NULL; - [ @object_name = ] object_name: É o nome da tabela ou ou visão indexada onde índice está; - [ @index_id = ] index_id: index_id: É o ID do índice. O index_id é int e pode ter ter um dos seguintes valores: o número do ID de um índice, NULL ou 0 se object_id for um heap. Para retornar informações de todos os índices de uma tabela base ou visão, especifique NULL. Se você especificar NULL, também deverá especificar NULL para partition_number, com isso, o SQL Server tentará estimar o espaço de compactação de dados para tabelas desconsiderando a existência ou não de particionamento; - [ @partition_number = ] partition_number: É o número da partição no objeto. partition_number é int e pode ter um dos seguintes valores: o número da partição de um índice ou heap, NULL ou 1 para um heap ou índice não particionado. Para especificar a partição, também é possível especificar a função $partition. Para retornar informações de todas as partições do objeto proprietário, especifique NULL; - [ @data_compression @data_compression = ] data_compression: data_compression: É o tipo de compactação a ser avaliada. data_compression pode ser um dos seguintes valores: NONE, ROW ou PAGE. Nota Devman - Row Altera somente o formato de armazenamento físico dos dados associados a um tipo de dados, mas não sua sintaxe ou semântica. Não são exigidas alterações de aplicativo quando uma ou mais tabelas são habilitadas para compactação. Nota Devman - Page A compactação de página é semelhante para tabelas, partições de tabela, índices e partições de índice.
Como já conhecemos a finalidade da sp_estimate_data_compression_savings, agora temos a possibilidade de realizar o cálculo da estimativa do tamanho da tabela, como pode ser visto nas Listagens 8 e 9. O resultado é apresentado nas Figuras 8 e 9. Listagem 8. Obtendo os resultados da estimativa de compactação em nível de linha -- Bloco 1 EXEC sp_estimate_data_compression_savings dbo, RevistasCompactadas, NULL, NULL, ROW
Figura 8. Estimativa do tamanho da compactação em nível de linha. A Figura 8 apresenta os resultados de estimativa do tamanho da tabela com base na compactação em nível de linha para a tabela RevistasCompactadas. Com base neste resultado, podemos observar uma possível mudança no tamanho físico da tabela RevistasCompactadas, representando um ganho na alocação do espaço em disco. Listagem 9. Obtendo os resultados da estimativa de compactação em nível de página -- Bloco 1 EXEC sp_estimate_data_compression_savings dbo, RevistasCompactadas, NULL, NULL, PAGE
Figura 9. Estimativa do tamanho da compactação em nível de página de dados. Nota Devman - Partições individuais O particionamento pode ser atingido sem dividir tabelas, colocando-se as tabelas fisicamente em unidades individuais de disco. Colocar uma tabela em uma unidade física e as tabelas relacionadas em uma unidade separada pode vir a melhorar o desempenho das consultas, pois, quando as consultas que envolvem junções entre as tabelas forem executadas, diversos cabeçotes de discos lerão os dados ao mesmo tempo. Grupos de arquivos do SQL Server podem ser usados para especificar em quais discos colocar as tabelas Nota Devman - none Representa que a tabela selecionada não utilizará compactação de dados. Conclusão Através da compactação de dados presente no SQL Server 2008, é possível melhorar a alocação de dados armazenados fisicamente, como também evitar possíveis desperdícios de espaço em disco sem gerar perda de performance. O artigo demonstrou o conceito e a prática deste recurso presente no SQL Server 2005 SP 2 e melhorado no SQL Server 2008. Aprendemos com os exemplos a utilizar a compactação de dados em nível de linha e página de dados, suas principais considerações e impactos em relação aos dados armazenados em uma tabela tanto no momento da sua criação, como também após os dados já estarem armazenados.
Gerenciando Usuários e Permissões no PostgreSQL Willamys Rangel Nunes de Sousa Atua no ramo de tecnologia da informação e banco de dados há mais de 5 anos. Atualmente é professor do Instituto Federal de Educação, Ciência e Tecnologia do Piauí (IFPI). Possui especialização em Banco de Dados e é graduado em tecnologia te cnologia da Informação. De que se trata o artigo: O artigo aborda os conceitos relacionais com gerenciamento de usuários e permissões de acesso em SGBD´s, focando o PostgreSQL. Neste artigo, foi implementado um estudo de caso para demonstrar na prática as funções do PostgreSQL no que se refere ao gerenciamento de usuários. Para que serve: O conteúdo deste artigo visa solucionar problemas inerentes do acesso a dados em um SGBD, buscando formas eficientes de controlar as permissões dos usuários. Em que situação o tema é útil: Em toda aplicação com conexão a banco de dados é importante o controle de acesso diferenciado para cada papel de usuário no sistema. Tendo em vista isto, este artigo torna-se uma boa opção de fonte de pesquisa.
A segurança dos sistemas si stemas de informação engloba um número elevado de áreas que poderão estar sob a responsabilidade de uma ou várias pessoas. Entre estas áreas encontram-se a segurança de redes, a segurança física, a segurança de computadores, a segurança segu rança das aplicações, a segurança da informação i nformação etc. O responsável pela implementação da segurança dos sistemas de informação em uma organização possui como primeira missão, e mais importante, a garantia da segurança da informação que protege. Esta garantia é conseguida mediante a utilização de vários instrumentos. Uma política de backup e recuperação de dados adequadamente elaborada e executada protegerá a organização contra a perda de informação devido a falhas de hardware, defeitos de software, erros humanos, intrusos, sabotagem e desastres naturais. Entretanto, esta não é a única maneira existente de proteção das informações. Afinal, precisamos mais do que uma solução relativa ao acontecimento de falhas. Quando se trata de proteger informações guardadas em banco de dados, nos preocupamos em que dados proteger e como protegê-los. Os SGBD´s atuais nos fornecem poderosas ferramentas que auxiliam em tais tarefas. Sendo os bancos de dados sistemas de armazenamento de informação, e sendo esta um elemento de elevado valor, quer seja financeiro, fi nanceiro, quer seja estratégico, é necessário que haja algum al gum tipo de controle de acesso a essa informação. Para mantermos a integridade das informações e realizarmos auditoria das mesmas, podemos utilizar os recursos dos gatilhos. Além disso, podemos fazer uso de uma linguagem especial para controle de acesso e permissão aos dados. Esta linguagem é a DCL (Data Control Language - Linguagem de Controle de Dados). Ela é um subconjunto da SQL para o controle de permissões dos usuários aos objetos do banco de dados. Neste artigo, será demonstrado o funcionamento do controle de permissões de acesso aos objetos do banco de dados PostgreSQL.
Administrando Usuários e Privilégios no PostgreSQL Todo agrupamento de bancos de dados possui um conjunto de usuários de banco de dados. Estes usuários são distintos dos usuários gerenciados pelo sistema operacional onde o servidor executa. Eles possuem objetos de banco de dados (por exemplo, tabelas, visões etc.), e podem conceder privilégios nestes objetos para outros usuários controlando, assim, quem pode acessar qual objet o. A DCL controla os aspectos de autorização de dados e licenças de usuários para controlar quem tem acesso para ver ou manipular dados dentro do banco de dados. Os nomes dos usuários de banco de dados são globais para todo o agrupamento de bancos de dados (e não apenas próprio de cada banco de dados). No decorrer deste artigo, veremos conceitos relacionados a usuários, grupos e “roles” (papéis), ao passo que aprenderemos como criá-los e quando utilizá-los utili zá-los em nossas aplicações. Criação de Usuário de Banco de Dados - CREATE USER Como foi mencionado, existem usuários que são independentes do sistema operacional, que servem para manipular objetos do SGBD. No PostgreSQL existe o comando CREATE USER que adiciona um novo usuário no bancos de dados. Este comando já foi muito utilizado, mas mesmo ainda funcionando, hoje é apenas um alias para o comando CREATE ROLE que veremos mais adiante. O comando genérico de criação de um usuário está exibido na Listagem 1. Listagem 1. Comando de Criação de um gatilho no PostgreSQL CREATE USER nome [ [ WITH ] opção [ ... ] ] As opções que acompanham este comando estão listadas li stadas na Tabela 1.
Tabela 1. Opções do comando CREATE CR EATE USER. Removendo um Usuário no Banco de Dados - DROP USER Uma vez criado no banco de dados, o comando DROP USER remove o usuário especificado. Porém, esse comando não remove as tabelas, visões ou outros objetos pertencentes ao usuário. Se o usuário possuir algum banco de dados, uma mensagem de erro será gerada. O comando que remove um usuário do PostgreSQL está mostrado na Listagem 2 . Listagem 2. Comando que remove um usuário no PostgreSQL DROP USER nome_usuário
Neste caso, nome_usuário refere-se ao usuário que está sendo removido. Para remover um usuário que possui um banco de dados, primeiro remove-se o banco de dados ou muda-se o dono do mesmo. Alterando um Usuário - ALTER USER Em alguns casos, é conveniente modificar um usuário, seja para alterar sua senha ou validade da mesma ou para incluir a permissão de criação de usuários ou retirá-la, caso exista etc. O comando para modificar um usuário é o ALTER USER e está demonstrado na Listagem 3. Listagem 3. Comando que altera um usuário no PostgreSQL ALTER USER nome [ [ WITH ] opção [ ... ] ] As opções desse comando são exibidas na Tabela 2.
Tabela 2. Opções do comando ALTER A LTER USER Uma variação do comando ALTER USER exibido na Listagem 4, muda o nome de um usuário. É importante comentar que apenas um super-usuário pode mudar o nome de ou tro usuário. Listagem 4. Comando para alterar o nome de um usuário ALTER USER nome RENAME TO novo_nome Conceitos de Grupos de Usuários no PostgreSQL Assim como nos sistemas operacionais baseados em Unix, os grupos são uma forma lógica de juntar usuários para facilitar o gerenciamento de privilégios. Tais privilégios podem ser concedidos, ou revogados, para o grupo como um todo. Para criar um grupo, deve ser utilizado o comando CREATE GROUP, como mostrado na Listagem 5. Listagem 5. Comando para criar um grupo no PostgreSQL CREATE GROUP nome_do_grupo;
Podemos adicionar ou remover usuários em um grupo existente utilizando o comando ALTER GROUP da Listagem 6, respectivamente. Uma vez criado um grupo, ainda podemos removê-lo. Tal operação pode ser feita usando o comando DROP GROUP, conforme Listagem 7. O comando DROP GROUP remove os grupos, mas não remove os usuários membros do grupo. Uma coisa importante que deve ser comentada é que não existe o comando CREATE GROUP no padrão SQL. O conceito de "papéis" (roles) que veremos adiante é semelhante ao de grupos e será o foco principal deste artigo.
Alterando Grupos no PostgreSQL - ALTER GROUP Assim como podemos modificar um usuário criado no banco de dados, podemos, da mesma forma, modificar um grupo. Para fazermos isso, utilizamos o comando ALTER GROUP. A Listagem 8 adiciona, remove e altera usuários em um grupo, respectivamente. Listagem 6. Comandos para adicionar e remover usuários em um grupo ALTER GROUP nome_do_grupo ADD USER nome_do_usuário; ALTER GROUP nome_do_grupo DROP USER nome_do_usuário; Listagem 7. Comando para remover um grupo no PostgreSQL DROP GROUP nome_do_grupo; Listagem 8. Comandos para alterar um grupo no PostgreSQL ALTER GROUP nome_do_grupo ADD USER nome_do_usuário; ALTER GROUP nome_do_grupo DROP USER nome_do_usuário; ALTER GROUP nome_do_grupo RENAME TO novo_nome; Criando Papéis (roles) no PostgreSQL - CREATE ROLE O comando CREATE ROLE adiciona um novo papel (role) ao agrupamento de bancos de dados do PostgreSQL. O papel é uma entidade que pode possuir objetos do banco de dados e possuir privilégios do banco de dados. Ele pode ser considerado como sendo um "usuário", um "grupo", ou ambos, dependendo de como é utilizado. O comando CREATE ROLE é o substituto dos comandos CREATE USER e CREATE GROUP por possuir mais recursos que os mesmos. A Listagem 9 demonstra o comando de criação de um ROLE, enquanto a Tabela 3 explica as opções que podem ser utilizadas utili zadas neste comando. Listagem 9. Comando para criar um role no PostgreSQL CREATE ROLE nome [ [WITH] opção [...]];
Tabela 3. Opções do comando CREATE CR EATE ROLE.
Removendo um Role - DROP ROLE O comando DROP ROLE remove os papéis especificados. A Listagem 10 demonstra como o comando DROP ROLE funciona. Listagem 10. Comando para remover um role no PostgreSQL DROP ROLE [ IF EXISTS ] nome [, ...] O papel não poderá ser removido se ainda estiver sendo referenciado em qualquer banco de dados do agrupamento. agrupamento. Assim será lançado lançado um erro caso tente-se tente-se removê-lo. Antes de de remover o papel, é necessário remover todos os objetos pertencentes ao mesmo (ou mudar o dono), e revogar todos os privilégios concedidos pelo papel. Alterando um Role - ALTER ROLE O comando ALTER ROLE altera os os atributos atributos de um papel do PostgreSQL. demonstra o comando genérico de alteração de um role. r ole.
A Listagem Listagem 11
Listagem 11. Comando para alterar um role no PostgreSQL ALTER ROLE nome [ [ WITH ] opção [ ... ] ] O comando ALTER ROLE pode modificar um role, utilizando as mesmas opções mostradas na Tabela 3. Concedendo e Revogando Privilégios no PostgreSQL Quando um objeto do banco de dados é criado, é atribuído um dono ao mesmo. O dono é o usuário que executou o comando de criação do objeto. Para mudar o dono de uma tabela, índice, seqüência ou visão deve ser utilizado o comando comando ALTER TABLE. A Listagem 12 mostra a modificação de um dono de uma tabela de um banco de dados. Por padrão, somente o dono (ou um super-usuário) pode fazer qualquer coisa com o objeto. Para permitir o uso por outros usuários, devem ser concedidos privilégios aos mesmos. Em SQL, existe o comando GRANT. Ele possui duas funcionalidades básicas: conceder privilégios para um objeto do banco de dados (tabela, visão, seqüência, banco de dados, função, linguagem procedural, esquema e espaço de tabelas) e conceder o privilégio de ser membro de um papel. Existem vários privilégios distintos: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE e ALL PRIVILEGES. A Tabela 4 exibe os privilégios possíveis e os seus significados. Listagem 12. Comando para alterar um dono de uma tabela no PostgreSQL ALTER TABLE nome_da_tabela OWNER TO novo_dono
Tabela 4. Todos os possíveis privilégios pri vilégios de acesso Em alguns casos também se torna necessário revogar alguns privilégios de acesso a usuários ou grupos de usuários. Para que isso i sso seja feito, utiliza-se o comando REVOKE. Deve ser observado que os super-usuários do banco de dados podem acessar todos os objetos, independentemente dos privilégios definidos para o objeto. Assim, não é aconselhável operar como um super-usuário a não ser quando for absolutamente necessário. Se um super-usuário decidir submeter o comando GRANT ou REVOKE, o comando será executado como se tivesse sido submetido pelo dono do objeto afetado. Em particular, os privilégios concedidos através destes comandos aparecerão como se tivessem sido concedidos pelo dono do o bjeto. Atualmente, o PostgreSQL não suporta conceder ou revogar privilégios para as colunas da tabela individualmente. Uma forma simples de contornar isso seria criar uma visão contendo apenas as colunas desejadas e, então, conceder os privilégios para a visão. Os privilégios especiais do dono da tabela (ou seja, o direito de DROP (remover), GRANT (conceder), REVOKE (revogar), etc.) são sempre implícitos ao fato de ser o dono, não podendo ser concedidos ou revogados. Estudo de Caso – Caso – Hotel Para demonstrar o controle de permissões de acesso aos objetos de um banco de dados no PostgreSQL, foi criado um banco de dados de um hotel fictício. Este banco de dados possui as tabelas cliente, reserva, hospedagem, quarto, tipo_quarto, atendimento e serviço. No exemplo, foi considerado que cada cliente pode realizar várias reservas e várias hospedagens. Além de, a cada hospedagem, solicitar várias serviços. Ainda é interessante notar que os quartos podem ser de tipos diferentes (apartamento simples, suíte casal, suíte luxo etc.). A Figura 1 apresenta o diagrama Entidade-Relacionamento do banco de dados em estudo.
Figura 1. Diagrama Diagr ama Entidade-Relacionamento de um hotel. Neste banco de dados, além das das tabelas foram criadas três funções e uma visão. A primeira função é chamada de adicionaReserva e serve para realizar a reserva de um determinado quarto para um cliente. Uma outra função, chamada adicionaHospedagem, cria uma hospedagem para o cliente em um quarto. A última função, denominada realizaPedido, será responsável por registrar os pedidos feitos pelos clientes, enquanto estiverem hospedados. Além das funções mencionadas, existe a visão listaClientes, l istaClientes, que exibe apenas os nomes e os sexos dos clientes, descartando o RG e o telefone dos mesmos. A visão listaClientes foi criada para demonstrar a permissão de acessar apenas algumas colunas de uma determinada tabela. Também foram criados alguns roles e foram dadas permissões diferentes de acesso aos dados para os mesmos. A Tabela 5 mostra os três roles (neste caso, servem para definir o perfil de alguns grupos de usuários) que têm acesso ao banco e que tipo de permissão cada um deles possui. Implementação do Banco de Dados Hotel no PostgreSQL O PostgreSQL será utilizado para criar o banco de dados, as funções, a visão e os roles citados na seção anterior. A ferramenta gráfica PgAdmin III foi utilizada para a criação do banco de dados do estudo em caso. A Listagem 13 exibe o comando SQL para criação do banco de dados chamado hotel.
Tabela 5. Roles do banco de dados hotel Listagem 13. Comando SQL para criação do banco hotel CREATE DATABASE hotel;
As tabelas do banco de dados hotel são criadas pelos comandos da Listagem 14. Listagem 14. Comandos SQL para criação cri ação das tabelas do banco hotel // Tabela de CLIENTES CREATE TABLE cliente ( rg NUMERIC NOT NULL, nome VARCHAR(40) NOT NULL, sexo CHAR(1) NOT NULL, telefone NUMERIC(10,0), PRIMARY KEY (rg) ) WITHOUT OIDS; // Tabela TIPO_QUARTO CREATE TABLE tipo_quarto ( id_tipo SERIAL NOT NULL, descricao VARCHAR(40) NOT NULL, valor NUMERIC(9,2) NOT NULL, PRIMARY KEY (id_tipo) ) WITHOUT OIDS; // Tabela QUARTO CREATE TABLE quarto ( num_quarto INTEGER NOT NULL, andar CHAR(10), id_tipo INTEGER NOT NULL, status CHAR(01) NOT NULL DEFAULT D, PRIMARY KEY (num_quarto), FOREIGN KEY (id_tipo) REFERENCES tipo_quarto (id_tipo) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; // Tabela SERVIÇO CREATE TABLE servico ( id_servico SERIAL NOT NULL, descricao VARCHAR(60) NOT NULL, valor NUMERIC(9,2) NOT NULL, PRIMARY KEY (id_servico) ) WITHOUT OIDS;
// Tabela RESERVA CREATE TABLE reserva ( id_reserva SERIAL NOT NULL, rg NUMERIC NOT NULL, num_quarto INTEGER NOT NULL, dt_reserva DATE NOT NULL, qtd_dias INTEGER NOT NULL, data_entrada DATE NOT NULL, status CHAR(1) NOT NULL DEFAULT A, PRIMARY KEY (id_reserva), FOREIGN KEY (rg) REFERENCES cliente (rg) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (num_quarto) REFERENCES quarto (num_quarto) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; // Tabela HOSPEDAGEM CREATE TABLE hospedagem ( id_hospedagem SERIAL NOT NULL, rg NUMERIC NOT NULL, num_quarto INTEGER NOT NULL, data_entrada DATE NOT NULL, data_saida DATE, status CHAR(1) NOT NULL, PRIMARY KEY (id_hospedagem), FOREIGN KEY (rg) REFERENCES cliente (rg) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (num_quarto) REFERENCES quarto (num_quarto) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS; // Tabela ATENDIMENTO A TENDIMENTO CREATE TABLE atendimento ( id_atendimento SERIAL NOT NULL, id_servico INTEGER NOT NULL, id_hospedagem INTEGER NOT NULL, PRIMARY KEY (id_atendimento), FOREIGN KEY (id_servico) REFERENCES servico (id_servico) ON UPDATE RESTRICT ON DELETE RESTRICT, FOREIGN KEY (id_hospedagem) REFERENCES hospedagem (id_hospedagem) ON UPDATE RESTRICT ON DELETE RESTRICT ) WITHOUT OIDS;
As funções adicionaHospedagem, adicionaReserva e realizaPedido são criadas com os comandos das Listagens 15, 16 e 17, respectivamente. Listagem 15. Comando de criação da função adicionaHospedagem CREATE OR REPLACE FUNCTION adicionaHospedagem(rg_cliente numeric, numero_quarto int) RETURNS void AS $$ begin perform * from cliente where rg = rg_cliente; if found then perform * from quarto where upper(status) = D and num_quarto = numero_quarto; if found then insert into hospedagem values (default, rg_cliente, numero_quarto, current_date, null, A); update quarto set status = O where num_quarto = numero_quarto; RAISE NOTICE Hospedagem realizada com sucesso!; else RAISE EXCEPTION Quarto indisponivel para hospedagem!; end if; else RAISE EXCEPTION Cliente nao consta no cadastro!; end if; end; $$ LANGUAGE plpgsql SECURITY DEFINER;
Listagem 16. Comando de criação da função adicionaReserva CREATE OR REPLACE FUNCTION adicionaReserva (rg_cliente numeric, numero_quarto int, dias int, data_entrada date) RETURNS void AS $$ begin perform * from cliente where rg = rg_cliente; if found then perform * from quarto where upper(status) = D and num_quarto = numero_quarto; if found then insert into reserva values (default, rg_cliente, numero_quarto, current_date, dias, data_entrada, A); update quarto set status = R where num_quarto = numero_quarto; RAISE NOTICE Reserva realizada com sucesso!; else RAISE EXCEPTION Quarto indisponivel para reserva!; end if; else RAISE EXCEPTION Cliente nao consta no cadastro!; end if; end; $$ LANGUAGE plpgsql SECURITY DEFINER; Listagem 17. Comando de criação da função realizaPedidos CREATE OR REPLACE FUNCTION realizaPedido(hosp int, serv int) RETURNS void AS $$ begin perform * from hospedagem where upper(status) = A and id_hospedagem = hosp; if found then perform * from servico where id_servico = serv; if found then insert into atendimento values (default, serv, hosp); RAISE NOTICE Pedido realizado com sucesso!; else RAISE EXCEPTION Servico indisponivel!; end if; else RAISE EXCEPTION Hospedagem nao consta no cadastro ou ja foi desativada!; end if; end; $$
LANGUAGE plpgsql SECURITY DEFINER; Não é interessante que alguns usuários acessem todas as informções sobre os clientes, como, por exemplo, o RG e o tefefone. Por esse motivo, foi criada uma visão que permite apenas visualizar apenas o nome e o sexo dos clientes (veja Listagem 18). Tendo definido os três tipos de usuários que terão acesso aos objetos do banco de dados (veja Tabela 5), agora criaremos cada um (veja Listagem 19) e, mais adiante, daremos as permissões que ambos têm direito. Depois de criarmos todos os papéis que irão agrupar todos os usuários com perfis semelhantes, temos que dar as permissões para cada tipo, de acordo com o que foi definido anteriormente. Antes de dar as permissões sobre as funções é importante explicar a opção SECURITY DEFINER usada na criação das três funções. Esta opção serve para permitir que todos os usuários acessem essas funções com a mesma permissão de quem as criou. Isto é muito importante, pois os usuários dos grupos atendente e estagiário não têm acesso às tabelas do banco de dados hotel, então não poderiam executá-las. Por exemplo, os grupos atendente e estagiário não executariam a função adicionaHospedagem, porque na sua definição existem consultas que buscam valores que estão em algumas tabelas como clientes e quartos e esses grupos de usuários não têm permissão para acessa-las. Para resolver este problema, foram criadas todas as funções com a opção SECURITY DEFINER, e antes de dar a permissão de execução para os grupos gerente, atendente e estagiário, deve-se revogar o direito de qualquer usuário do banco executar ex ecutar tais funções. Os comandos que revogam o execução das funções adicionaHospedagem, adicionaReserva e realizaPedido pelos usuários, estão exibidos na Listagem 20. Listagem 18. Criação da visão para consultar o nome e o sexo dos clientes CREATE VIEW listaClientes (nome_cliente,sexo) AS SELECT nome, sexo FROM cliente Listagem 19. Criação dos papéis (roles) gerente, atendente e estagiário CREATE ROLE gerente; CREATE ROLE atendente; CREATE ROLE estagiario; Listagem 20. Revogando a execução ex ecução das três funções para todos os usuários REVOKE ALL ON FUNCTION FUNC TION adicionaReserva(numeric,int,int,date) adicionaReserva(numeric,int,int,date) FROM PUBLIC; REVOKE ALL ON FUNCTION adicionaHospedagem(numeric,int) FROM PUBLIC; REVOKE ALL ON FUNCTION realizaPedido(int,int) FROM PUBLIC; Feito isso, podemos agora dar as permissões de acesso para cada um dos grupos de usuários. O papel gerente poderá modificar todos os registros de todas as tabelas (veja Listagem 21), além de acessar as funções adicionaReserva (veja Listagem 22), adicionaHospedagem (veja Listagem 23) e listaClientes (veja Listagem 24) e a visão listaClientes (veja Listagem 25). Listagem 21. Concedendo permissão para o role gerente acessar todas as tabelas e conceder permissões para outros usuários GRANT SELECT, INSERT ON cliente, reserva, hospedagem, quarto, tipo_quarto, atendimento, servico, listaClientes TO gerente WITH GRANT OPTION; Listagem 22. Concedendo permissão para o role gerente para acessar a função adicionaHospedagem GRANT EXECUTE ON FUNCTION FUN CTION adicionaHospedagem(numeric,int) TO gerente;
Listagem 23. Concedendo permissão para o role gerente para acessar a função adicionaReserva GRANT EXECUTE ON FUNCTION FUN CTION adicionaReserva(numeric,int,int,date) adicionaReserva(numeric,int,int,date) TO gerente; Listagem 24. Concedendo permissão para o role gerente para acessar a função realizarPedido GRANT EXECUTE ON FUNCTION realizaPedido(int,int) TO gerente; Listagem 25. Concedendo permissão para o role gerente para acessar a view listaClientes listaClie ntes GRANT SELECT ON listaClientes TO gerente O grupo de usuários atendente não pode ter acesso a nenhuma tabela. Ele pode apenas acessar as funções adicionaHospedagem, adicionaReserva e realizaPedidos. As permissões para acessar tais funções estão disponíveis nas Listagens 26, 27 e 2 8, respectivamente. Listagem 26. Concedendo permissão para o role atendente para acessar a função adicionaHospedagem GRANT EXECUTE ON FUNCTION FUN CTION adicionaHospedagem(numeric,int) TO atendente; Listagem 27. Concedendo permissão para o role atendente para acessar a função adicionaReserva GRANT EXECUTE ON FUNCTION FUN CTION adicionaReserva(numeric,int,int,date) adicionaReserva(numeric,int,int,date) TO atendente; Listagem 28. Concedendo permissão para o role atendente para acessar a função realizaPedidos GRANT EXECUTE ON FUNCTION realizaPedido(int,int) TO atendente; A Listagem 29 dá a permissão para o grupo de usuários do tipo estagiário acessar a visão listaClientes. Com o banco de dados, as funções, a visão e os usuários criados, já podemos realizar testes para verificar se o que foi permitido para cada tipo de usuário está de acordo com cada um dos seus perfis. Simulando Testes para Validar as Permissões Com todos os papéis criados, iremos realizar alguns testes para validar as permissões que foram concedidas para cada um dos papéis. Em um primeiro momento, serão criados três usuários pertencentes a cada um dos papéis criados (gerente, atendente e estagiário). A Listagem 30 exibe a criação do um usuário chamado tony e que pertencerá ao grupo de usuários gerente. O parâmetro “LOGIN” permite que o usuário possa logar no sistema e o parâmetro “PASSWORD” atribui a senha ‘111´ para o mesmo. Por sua vez, a Listagem 31 cria o usuário maria com direito de logar no sistema, mas agora pertencendo ao grupo de atendentes. O último usuário criado é a vitoria. Ela pertencerá ao grupo de estagiários e sua criação está mostrada na Listagem 32. Listagem 29. Concedendo permissão para o role estagiário acessar a visão listaCliente li staCliente GRANT SELECT ON listaClientes TO estagiario; Listagem 30. Criação de usuário com papel de gerente CREATE ROLE tony LOGIN PASSWORD 111 IN ROLE gerente; Listagem 31. Criação de usuário com papel de atendente CREATE ROLE maria LOGIN PASSWORD 222 IN ROLE atendente;
Listagem 32. Criação de usuário com papel de atendente CREATE ROLE vitoria LOGIN PASSWORD 333 IN ROLE estagiario; Com o usuário tony logado, pode-se realizar todas as operações com as tabelas, exceto operações próprias dos donos dos objetos, como por exemplo, exempl o, remover uma tabela (DROP TABLE). Quando este usuário tenta executar o comando de inclusão de um novo cliente, conforme Figura 2, o sistema executa o comando normalmente. normalm ente.
Figura 2. Usuário tony incluindo um novo cliente. Se usuário também tentar realizar qualquer operação (SELECT, DELETE, UPDATE, INSERT) com qualquer uma das tabelas, o sistema executará o comando normalmente. Porém, o mesmo não ocorrerá, caso ele tente remover uma tabela do banco de dados (veja Figura 3).
Figura 3. Usuário tony tentando apagar do banco de dados a tabela tipo_quarto Agora com o usuário maria logado, tenta-se utilizar a função adicionaReserva, conforme Figura 4.
Figura 4. Usuário maria acessando a função adicionaReserva
O mesmo não aconteceria se esse usuário tentasse acessar diretamente a tabela reserva, porque o seu perfil não tem permissão para isto (veja Figura 5).
Figura 5. Usuário maria tentando selecionar os registros da tabela reserva
Por último, com o usuário vitoria, tenta-se consultar a visão listaClientes (veja Figura 6). Verifique que vitoria não teve problema porque o seu perfil tem permissão para consultar somente a visão listaClientes. O mesmo não ocorreria caso ele tentasse consultar diretamente a tabela cliente (veja Figura 7).
Figura 6. Usuário vitória acessando a visão listaClientes
Figura 7. Vitória tentando consultar diretamente a tabela cliente
Conclusão Segurança é importante em todos os níveis, principalmente a nível de informação. Hoje o maior patrimônio de uma empresa são seus dados e por isso i sso é tão importante preserva-los íntegros. í ntegros. Com o que foi visto neste artigo, percebe que podemos controlar de forma bastante eficaz os nossos dados, dando permissão de acesso àqueles que possuem determinadas características e dar acesso diferenciado para outros. O PostgreSQL é um SGBD bastante utilizado e como foi visto, é uma excelente opção por vários motivos, além de permitir meios eficazes de controle de acesso a cesso aos dados.
Desvendando o Oracle Data Integrator Rodrigo Atkinson Graduado em Informática - Sistemas de Informações e Mestre em Gestão de Organizações com ênfase em Sistemas de Informações. Já atuou como DBA, Analista de BI\DW. Atualmente é professor do curso de Graduação da FTEC-POA. Rodrigo Radtke de Souza Graduado em Engenharia de Computação pela FURG e certificado Java SCJP e SCWCD. Atualmente trabalha como analista de sistemas em Porto Alegre. De que trata o artigo: Uso da ferramenta Oracle Data Integrator (ODI) para a construção de processos ETL (Extract, Transform, Load). Neste artigo, utilizaremos o ODI para integrar dados de diferentes origens (SGBD Oracle, Firebird e arquivo texto) para uma base de destino Oracle. Para que serve: O ODI nos permite transformar o trabalho, muitas vezes maçante, da construção de processos ETLs, em interfaces e fluxos de fácil desenvolvimento, manutenção e visualização. Em que situação o tema é útil: Além de padronizar e otimizar otimi zar processos de ETL, o ODI é capaz de fazer a integração de diferentes tecnologias e bancos de dados em um único lugar, facilitando o trabalho de qualquer projeto que necessite fazer integração de dados. Para retomarmos a estrutura apresentada no artigo publicado na SQL Magazine 65, vamos relembrar de que maneira está estruturada e armazenada as tabelas envolvidas no processo de ETL. Como explicado, embora nosso modelo esteja em um DER único, nossas origens estão armazenadas em estruturas diferentes: as tabelas Cliente, TipoCliente, Venda e Vendedor estão alocadas no banco de dados ORACLE; as tabelas Grupo, Item e ItVenda estão no FIREBIRD; e ainda vamos utilizar uma fonte de dados oriunda de arquivo texto. Para facilitar o entendimento e a leitura dos tópicos apresentados a seguir, vamos disponibilizar no contexto da estrutura relacional apresentada no primeiro artigo, todas as DDL´s e DML´s envolvidas nos processos descritos. Estes scripts podem ser obtidos no site da revista SQL Magazine. Iniciando o desenvolvimento Depois de configurada todas as Topologias (passos apresentados na primeira parte do artigo), vamos iniciar o desenvolvimento no módulo Designer. A primeira tarefa que temos é criar um novo projeto. Na aba Projetos do Módulo Designer devemos clicar com o botão bo tão direito e escolher a opção “Inserir Projeto”. Vamos nomear nosso projeto como “PROJETO_ETL” conforme Figura 1.
Figura 1. Inserindo Inseri ndo Projeto de ETL. Ainda na Figura 1 vamos explorar alguns alguns conceitos importantes. Na “Primeira Pasta” localizam-se localizam -se os nossos objetos criados no ODI que são disponibilizados em estruturas de pastas para uma melhor organização. Porém, uma pasta sempre contém um conjunto de três tipos de objetos: Pacotes, Interfaces e Procedimentos. - Pacotes: são os objetos que servirão servirão para modelar o nosso fluxo no processo de ETL. ETL. No pacote são armazenados os objetos utilizados e a ligação entre eles. Depois que finalizamos a construção de um pacote, geramos a partir dele, um Cenário, que é a versão “compilada” do nosso pacote. Façamos uma analogia a um programa “comum”. Os pacotes contêm os arquivos fonte do programa e os cenários são os executáveis gerados a partir dos arquivos fonte; - Interfaces: são os objetos que realmente fazem o trabalho de ETL. ETL. Nas interfaces são definidas as tabelas de origem, de destino e quais as regras serão aplicadas no processo de ETL; - Procedimentos: como o nome indica, são objetos em que são escritos escritos qualquer tipo tipo de procedimento “extra” que se faça necessário no processo de ETL. Podemos criar procedimentos que contenham vários tipos de códigos, de diferentes tecnologias suportadas pelo ODI, como por exemplo, escrever um procedimento em PL/SQL, em Java, em Jython, etc. Dentro da hierarquia hierarquia do “PROJETO_ETL” ainda temos: - Variáveis: são utilizadas no ODI como qualquer variável é utilizada em um programa. Elas armazenam um valor que é utilizado util izado e modificado durante o processo de ETL; - Seqüências: o ODI nos dá a possibilidade de criação criação de Sequences, iguais a uma Sequence Sequence de Banco de Dados. Criamos seqüências no ODI quando a Tecnologia que estamos utilizando não nos permite ter uma Sequence própria no banco; Dentro da hierarquia do “PROJETO_ETL” ainda temos: - Variáveis: são utilizadas no ODI como qualquer variável é utilizada em em um programa. Elas Elas armazenam um valor que é utilizado util izado e modificado durante o processo de ETL; - Seqüências: o ODI nos dá a possibilidade de criação criação de Sequences, iguais a uma Sequence de Banco de Dados. Criamos seqüências no ODI quando a Tecnologia que estamos utilizando não nos permite ter uma Sequence própria no banco; - Funções do Usuário: estas funções nos nos dão a possibilidade de criação de de funções funções que irão ser utilizadas várias vezes no processo de ETL. Por exemplo, se temos que fazer um determinado tratamento em uma string ou uma data, podemos criar uma função para não ter que escrever a mesma função várias vezes nas nossas Interfaces;zes nas nossas Interfaces; - Módulos de Conhecimento: Conhecimento: são conhecidos também também como KMs (Knowledge (Knowledge Modules). Os KMs são considerados os “corações” do processo de ETL no ODI. Eles são os responsáveis por todas as tarefas executadas nos processos de ETL.
Para melhorar o entendimento vamos detalhar cada tipo de Módulo de Conhecimento (KM): - RKM - Reverse Knowledge Module (Engenharia (Engenharia Reversa): é o responsável por fazer uma reversa “customizada” dos armazenamentos de dados no ODI. Por exemplo: se existir uma situação em que se necessite fazer algum tipo de procedimento extra ao reverter um modelo de dados, podemos utilizar RKMs específicos e não o padrão para esta tarefa. O ODI faz reversas de tabelas automaticamente, mas podemos customizar estas reversas com um RKM; - LKM - Load Knowledge Module (Carga): é o responsável por carregar carregar os dados das tabelas de origens no nosso processo de ETL quando estas tabelas se encontram em servidores de dados (Data Servers) diferentes; - CKM - Check Knowledge Knowledge Module (Verificação): é o responsável por realizar as validações dos dados no processo de ETL. No ODI podemos criar check constraints próprias contendo alguma regra de negócio (por exemplo, valor não pode ser negativo) ou podemos validar FKs de banco antes de inserir os dados na tabela de destino, ou ainda, durante o próprio processo de ETL, podemos verificar dados not null, etc. O CKM é o responsável por executar todas estas verificações; - IKM - Integration Knowledge Module Module (Integração): é o responsável pela pela integração dos dados efetivamente no banco de destino. Ele resolve as regras do ETL descritas nas interfaces e insere os dados finais na tabela de destino; - JKM - Journalizing Knowledge Module (Documentação): (Documentação): é o responsável por fazer a jornalização de dados quando se trabalha com este tipo de conceito. Pode ser usado, por exemplo, para se fazer replicação de bancos de dados; - SKM - Service Knowledge Modules (Serviço): (Serviço): é utilizado para publicar dados utilizando Web Services. Pode ser utilizado para gerar e manipular dados via Web Services para arquiteturas SOA (Service Oriented Architecture - Arquitetura Orientada a Serviços); Servi ços); - Marcadores: são utilizados para colocar marcadores nos objetos objetos criados no ODI. Servem para a organização do projeto. Nesta fase de nosso projeto ainda não temos nenhum KM. A cada novo projeto é fundamental a escolha de quais KMs iremos utilizar. Para o nosso projeto vamos importar os KMs necessários, que são dois: - LKM: para carregar os dados de origens diferentes diferentes do nosso destino; destino; - IKM: para fazer a integração efetiva dos dos nossos dados para o destino; destino; No Módulo Designer, acessamos a aba “Projetos” e clicamos com o botão direito sobre a opção “Importar” e escolhemos a opção “Importar Knowledge Modules...”. Devemos então informar o diretório onde se encontram os KMs a serem importados. Originalmente os KMs que fazem parte da instalação do ODI estão na pasta “oracledi\oracledi\ “oracledi \oracledi\impexp”. impexp”. Várias opções serão apresentadas e devemos escolher as que se encaixam ao Projeto. Os KMs que vamos utilizar no nosso projeto são: - LKM File to SQL: Carrega dados de arquivos texto texto e traz para uma área de armazenamento armazenamento temporário (ou área de estagiamento, ou stagging, onde ficam as tabelas temporárias que o ODI cria automaticamente no processo de ETL); - LKM SQL to ORACLE: Carrega Carrega dados de um banco banco de dados genérico genérico para um banco de dados ORACLE; - IKM ORACLE Incremental Update: Update: Integra os dados de forma forma incremental em um banco banco de dados ORACLE, ou seja, linhas que ainda não existem na tabela são inseridas, linhas que existem sofrem atualização. Quando os KMs já estiverem importados podemos ter uma definição do que cada um faz, bastando clicar duas vezes sobre o mesmo, surgindo assim uma tela com a descrição e a funcionalidade do mesmo. Para este processo de ETL não importamos todos os KMs, pois isso dificultaria a seleção dos mesmos no momento do desenvolvimento desenvolvimento devido à grande g rande quantidade de KMs existentes. Portanto, é uma boa prática importar para o seu projeto apenas os KMs que serão realmente utilizados, a fim de trabalhar com um ambiente mais “limpo” e com menos men os chances de selecionar um KM errado. Em relação aos KMs
importados para o nosso projeto, suas funcionalidades ficarão mais claras no decorrer do Projeto, mais precisamente no momento do desenvolvimento das Interfaces. Construindo a Estrutura do Projeto - Modelos Mod elos de Dados Partimos para a definição de nosso Modelo de Dados, e neste ponto o entendimento de dois conceitos são importantes: Modelo de Dados (Data Models) e o Armazenamento de Dados (Data Stores). Um Modelo de Dados pode conter N armazenamentos de dados (tabelas efetivas do banco de dados). É utilizado para agrupar tabelas de uma determinada tecnologia de um determinado Esquema Lógico. Em nosso Projeto teremos quatro Modelos de Dados, um para cada finalidade: fi nalidade: Origem Oracle, Origem Firebird, Origem File e Destino Oracle. Dentro de cada modelo estarão os nossos armazenamentos de dados, ou seja, nossas tabelas do banco de dados. Portanto, dentro do Módulo Designer, mais precisamente na aba Modelos, vamos criar pastas para melhor organização. Vamos Vamos inserir duas pastas de modelos: uma chamada “Destinos” e outra “Origens”. Agora vamos inserir as pastas de modelos para ambas. Para isso, basta clicar com o botão direito sobre a pasta Destinos e selecionar a opção opç ão “Inserir Pasta de Modelos”. Vamos inserir a pasta “ORACLE”, onde ficarão as tabelas de destino da tecnologia ORACLE, e repetimos a tarefa para as Origens, criando três pastas: “FILE”, “FIREBIRD” e “ORACLE”, onde ficarão as tabelas de origem das suas respectivas res pectivas tecnologias. Inserindo o Modelo de Dados Oracle - Origem Vamos criar nosso Modelo da Origem ORACLE. Para esta tarefa devemos clicar com o botão direito sobre a Pasta de Modelo ORACLE que acabamos de criar e escolher a opção “Inserir Modelo”. Na janela que se abre devemos inserir o nome para o nosso modelo, selecionar a tecnologia (ORACLE) e a qual Esquema Lógico (ORACLE_ORIGEM) o modelo irá se referenciar. O nome de nosso Modelo é auto-explicativo (MODELO_ORACLE_ORIGEM). Ainda nas configurações configurações do Modelo vamos acessar a aba “Reverter”, pois devemos setar o Contexto que iremos utilizar para “importar” as nossas tabelas. Em nosso Projeto o Contexto selecionado é o “Desenvolvimento”. Nesta aba também devemos selecionar quais tipos de objetos queremos que a reversa importe para o ODI. Para o nosso caso selecionamos apenas Tabelas, pois queremos reverter apenas as tabelas criadas nos scripts (que se encontram no site da SQL Magazine). Nesta aba de configuração poderíamos também aplicar alguma máscara de filtro para que no momento da reversa o ODI selecionasse apenas os objetos que se adequassem a esta determinada máscara. A próxima aba de configuração é a “Reversão Seletiva” (Figura 2). Nesta aba devemos escolher, das tabelas que passaram no filtro anterior, quais tabelas importar para o ODI. Para o nosso projeto iremos importar as quatro tabelas que estão alocadas no banco de dados. Após selecionar as tabelas podemos clicar na opção “Aplicar”, e após em “Reverter”. Uma mensagem de confirmação será exibida: “Deseja fazer engenharia reversa neste modelo antes de fechar esta janela?” Se anteriormente já clicamos na opção “Reverter” podemos clicar em “Não” nesta confirmação. Depois de “revertido”, teremos as tabelas da nossa origem ORACLE no ODI. Inserindo o Modelo de Dados Firebird – Firebird – Origem Devemos agora inserir o Modelo de Dados também para o Firebird. Faremos o mesmo processo detalhado anteriormente apenas alterando a Tecnologia escolhida. Selecionamos a Tecnologia Interbase que foi a selecionada para utilização com o Firebird no momento da criação da Topologia. Conforme a Figura 3, selecionamos a tecnologia Interbase e o Esquema Lógico FIREBIRD_ORIGEM.
Figura 2. Executando a Reversa do Modelo de Origem.
Figura 3. Criando modelo de Origem do Firebird. Após selecionar o contexto e quais objetos queremos importar na aba Reverter (novamente selecionamos Tabelas), e quais as tabelas que importaremos na aba Reversão Seletiva (tabelas criadas no script que se encontra no site da SQL Magazine), podemos clicar na opção “Aplicar” e após em “Reverter”. Se o procedimento for correto, as tabelas da Origem Firebird serão importadas. Inserindo o Modelo de Dados File – File – Origem Terminada a inclusão dos Modelos de Dados ORACLE e Firebird vamos partir para a inclusão do Modelo de Dados do tipo FILE. Para esta tecnologia existem algumas particularidades que devem ser observadas. Vamos proceder com a criação do modelo de forma normal seguindo os padrões da inclusão da Tecnologia ORACLE. Nomeamos o modelo para MODELO_FILE_ORIGEM e selecionamos a Tecnologia FILE. Também associamos neste ponto o Esquema Lógico FILE_ORIGEM. Vamos à aba Reverter, selecionando o contexto “Desenvolvimento”. A única particularidade está no momento de salvar o modelo: devemos salvá -lo sem revertê-lo. Podemos notar que o ODI não apresentou nenhuma mensagem de aviso ou confirmação em relação à reversa no momento que nós criamos o modelo. Isso acontece porque a Tecnologia FILE não segue necessariamente um padrão. Podemos ter arquivos com delimitações por caracteres, como “;” (ponto e vírgula) ou então “|” (pipe) como podemos ter arquivos que não são delim itados mas sim fixos por um determinado valor em cada coluna. Todos estes padrões se encaixam na Tecnologia FILE. Devido a particularidades de cada arquivo devemos fazer a reversa de cada ar quivo de forma individual.
Para isso devemos estar no Repositório de Trabalho do ODI, e clicar com o botão direito no “MODELO_FILE_ORIGEM” que se encontra dentro da pasta FILE. Devemos escolher a opção “Inserir Armazenamento de Dados”. Nota Devman - Tabela Dual Oracle Tabela “dual” Oracle: A tabela DUAL é uma pequena tabela no dicionário de dados que o Oracle ou qualquer usuário pode referenciar para garantir um resultado conhecido. Esta tabela possui apenas uma coluna, chamada DUMMY com apenas uma linha, contendo o valor X. A DUAL é criada automaticamente pelo Oracle, sob o esquema SYS, mas pode ser acessada por outros usuários. Sempre que precisamos verificar um resultado conhecido, como a data e hora do servidor ou o valor atual de uma sequence, simplesmente fazemos a consulta referenciando a tabela DUAL. Isto por que toda consulta SQL deve envolver uma tabela, porém, se utilizarmos qualquer tabela “povoada” nesta consulta, teremos uma série de inconvenientes, como estratégia de acesso ou eventual utilização utili zação de índices, etc. Na janela que será exibida, na aba “Definição”, devemos colocar um nome para o modelo de dados e devemos escolher o arquivo correspondente que queremos reverter. Neste caso o arquivo é do tipo TXT (dtempo.txt) e armazena as informações referentes à dimensão tempo de nosso Data Warehouse. Depois de feita a seleção do arquivo, vamos para a aba “Arquivos” (Figura 4), onde devemos informar se o arquivo possui ou não delimitação. No nosso caso, escolhemos que ele é “Delimitado”. Neste ponto informamos que o caractere caractere separador de campos do arquivo dtempo.txt é o “;” (ponto e vírgula). Também nesta estrutura de configuração podemos informar se o arquivo possui cabeçalho e de quantas linhas o mesmo é formado. Para este caso informamos o valor 0 (zero). Se algum valor fosse informado, a quantidade de linhas informada seria retirada do início do arquivo e seria desprezada. Outra opção que precisamos definir diz respeito ao “Separador de Registros”. Podemos selecionar se o arquivo tem separador do tipo: - MS-DOS (CR+LF (Carriage Return / Line Feed) = \r\n - hexa 0D0A); - UNIX (LF (Line Feed) = \n - hexa 0A). Estes padrões de separadores de registros se referem às possíveis quebras de linhas do arquivo. Também devemos configurar o delimitador de texto que neste cas o é ‘ (aspas simples), ou seja, as strings do arquivo texto são envoltos por aspas simples. Com esta configuração o ODI irá considerar apenas o conteúdo “interno” da string ignorando as aspas. Neste ponto também podemos indicar qual separador decimal os n ossos valores estão utilizando, o que não se aplica neste caso.
Figura 4. Criando o armazenamento de dados da origem TXT.
Nota Devman - DDL (Linguagem de Definição de Dados): A DDL permite ao usuário definir tabelas novas e elementos associados. A maioria dos bancos de dados de SQL comerciais têm extensões ex tensões proprietárias no DDL. Os comandos básicos da DDL são poucos: - CREATE: cria um objeto (uma Tabela, por exemplo) dentro da base base de dados; - DROP: apaga um objeto objeto do banco de dados. Alguns sistemas de banco de dados (Oracle, por exemplo) usam o comando ALTER, que permite ao usuário alterar um objeto, por exemplo, adicionando uma coluna a uma tabela existente. Outros comandos DDL: ALTER TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX, I NDEX, CREATE VIEW, DROP VIEW. V IEW. Finalizando o processo de configuração devemos clicar na aba “Colunas” e selecionar a opção reverter. Neste momento o ODI busca as informações da aba “arquivos” e separa em colunas automaticamente (Figura 5). Por padrão as colunas ficam com nomes C1, C2, C..., mas podem ser renomeadas conforme necessidade e\ou organização. Nota Devman - DML (Linguagem de Manipulação de Dados): A DML é um subconjunto da linguagem li nguagem usada para selecionar, inserir, atualizar e apagar dados. - SELECT: é o mais usado do do DML, comanda e permite ao usuário especificar uma query como uma descrição do resultado desejado. - INSERT: é usada para inserir um registro (formalmente (formalmente uma tupla) a uma tabela tabela existente; - UPDATE: para mudar os valores de dados em uma ou mais linhas da tabela tabela existente; - DELETE: permite remover linhas existentes existentes de uma tabela. Inserindo o Modelo de Dados Oracle - Destino Vamos agora proceder com a criação do modelo de destino seguindo os padrões da inclusão da tecnologia Oracle para Origem. Nomeamos o modelo como MODELO_ORACLE_DESTINO conforme Figura 6. Devemos reverter as tabelas repetindo os mesmos passos do modelo de dados Oracle da origem. Para isso, na aba Definição devemos selecionar a tecnologia Oracle e o esquema lógico ORACLE_DESTINO. Na aba Reverter selecionamos o contexto de Desenvolvimento e o tipo de armazenamento de dados a ser revertido (Tabela), e na aba Reversão Seletiva escolhemos as tabelas contidas no script disponível no site da SQL Magazine. Depois deste passo estamos prontos para iniciar o desenvolvimento das interfaces. Iniciando o Desenvolvimento das Interfaces Neste ponto iniciamos efetivamente o desenvolvimento ETL. Vamos desenvolver as interfaces, procedimentos, variáveis e pacotes, que serão os objetos utilizados para a realização do ETL. Desenvolvimento da Interface - Carga Destino DIM_CLIENTE Para iniciarmos o desenvolvimento das interfaces vamos alternar da aba Modelos para a aba Projetos no Módulo Designer. Nesta aba vamos alterar o nome da “Primeira Pasta” para “DW”. Esta alteração pode ser feita dando duplo clique sobre a estrutura. Vamos iniciar carregando as dimensões do DW. A primeira interface a ser desenvolvida deverá fazer a carga de dados para a Dimensão Cliente. Ainda na aba Projetos devemos expandir a pasta DW e clicar com o botão direito sobre Interfaces selecionando a opção “Inserir Interface”, conforme Figura 7.
Figura 6. Criação do Modelo de destino Oracle.
Figura 7. Inserindo uma nova interface Vamos desenvolver a Interface para contemplar o ETL da Dimensão Cliente e, portanto, nomeamos a Interface como CLIENTES_IN. Neste passo também devemos selecionar o contexto de otimização, que serve para o ODI montar o fluxo de execução (Figura 8).
Figura 8. Criando a interface de clientes. Para melhorar a explicação sobre o contexto de otimização, vamos imaginar o seguinte exemplo: temos em desenvolvimento dois esquemas que apontam para uma mesma instancia de banco de dados.
Para o ODI, como os dois esquemas estão no mesmo banco não seria necessária a utilização de um LKM (o LKM busca os dados de data servers diferentes), pois o IKM (módulo de integração) conseguiria fazer sozinho a integração de dados, otimizando assim o código, pois diminuiria os “passos” do mesmo. Porém, se estes mesmos esquemas, em um contexto de Produção, estiverem em servidores fisicamente separados, o ODI necessitaria utilizar um LKM, LK M, pois a sua origem está fisicamente separada do destino. Se a interface fosse construída com o contexto contexto de otimização menos “fragmentado” (como o de desenvolvimento neste caso) teríamos um problema ao rodar esta interface em produção, pois o código gerado não contemplaria um LKM. Portanto, ao selecionar um contexto de otimização, devemos escolher sempre o contexto mais “fragmentado”, pois o ODI irá se basear neste contexto para montar o fluxo do ETL. No nosso caso, como temos apenas um contexto, pode-se manter o contexto de desenvolvimento. Outra opção que podemos selecionar nesta etapa (Figura 8) esta relacionada à área de Stagging, que pode ser diferente do destino. Por padrão, a área de Stagging é sempre no destino, ou seja, os objetos temporários necessários ao processo de ETL serão criados no Esquema de Trabalho do destino setado anteriormente, no momento da criação da topologia (ESQUEMA_TMP do banco ORACLE). Neste ponto poderíamos selecionar qualquer esquema para ser a Stagging, mas vamos mantê-lo no Esquema de Trabalho do destino. Após inserir esta nova Interface devemos acessar a aba “Diagrama”. Nesta estrutura serão armazenados todos os relacionamentos, regras e mapeamentos de origem e destino que deverão ser configurados. No lado direito (Figura 9) temos a tabela de destino, no esquerdo, teremos as tabelas de origem e seus relacionamentos. Na estrutura do Diagrama vamos montar a regra de ETL para o nosso destino. Primeiro devemos clicar na aba “Modelos” e selecionar a estrutura DESTINOS/ORACLE/MODELO_ ORACLE_DESTINO. Após localizar a estrutura basta clicar e arrastar a tabela DIM_CLIENTE para dentro da estrutura de armazenamento DESTINO, como pode ser visto na Figura 10.
Figura 9. Diagrama de uma Interface.
Figura 10. Adicionando as tabelas de Origem. Posteriormente devemos selecionar e arrastar a ORIGEM para o lado esquerdo do Diagrama. Neste momento o ODI pergunta se desejamos fazer o mapeamento automático dos campos. Como na nossa estrutura a nomenclatura das colunas são iguais, o mapeamento iria funcionar sem problemas. Na prática de desenvolvimento de um projeto, o mapeamento automático não é recomendado. Na grande maioria dos casos, as nomenclaturas de origem e destino são diferentes e\ou existirá alguma regra de transformação. Desta forma o ODI pode mapear campos para os locais errados, gerando re-trabalho para mapeá-los novamente. Portanto, selecione “Não” e vamos mapear manualmente. Porém, antes disso, temos que fazer um join entre tabelas de origem com o objetivo de popular a tabela DIM_CLIENTE. A DIM_CLIENTE recebe tanto as informações dos clientes quanto do seu tipo. Para isso, clique e arraste TIPOCLI para o diagrama. Podemos ver pela Figura 11 que o ODI identificou as colunas que fazem relacionamento entre as tabelas e já colocou o join joi n automaticamente. Se o processo de montagem dos joins não acontecesse de forma automática teríamos que clicar sobre a primeira coluna do relacionamento, arrastar e soltar em cima da segunda coluna do relacionamento. Este é o processo manual quando o m apeamento automatizado não acontece.
Figura 11. Montando os Joins entre as tabelas de Origem.
Podemos notar ao clicar no join (Figura 12) que várias opções são apresentadas (todas são autoexplicativas), como por exemplo, se o join vai ser um inner join ou um left outer join. Clicando nos diferentes tipos de joins, o ODI nos diz o que irá acontecer em cada caso. No caso apresentado para a construção da DIM_CLIENTE utilizamos um inner join. Esta tarefa avisa que retornará “Todas as linhas emparelhadas pela condição de união entre CLIENTE e TIPOCLI”. IMPORTANTE: Neste ponto temos a opção de executar este join na origem ou na área de teste (stagging). Se for na stagging, o ODI trará as duas tabelas inteiras para o esquema de trabalho e depois fará o join entre elas. Se a opção é na origem, o ODI fará o join na origem e trará apenas o resultado daquele join para o esquema de trabalho. Esta escolha depende de cada caso. No nosso exemplo é mais eficiente resolver o join na origem e trazer resolvido para o destino, pois isso resultará em trazer apenas os registros que obedeceram à regra do join, tornando assim o volume de dados trafegados de uma ponta a outra menor. Para mapear um campo no ODI o processo é relativamente simples. Deve-se clicar no campo de destino que se deseja mapear, clicar no campo de origem a ser mapeado, arrastar e soltar na área branca “Implementação”, que fica na parte parte de baixo do diagrama. O resultado pode ser visto na Figura 13.
Figura 12. Opções de Join para montagem da interface de carga.
Figura 13. Mapeando uma coluna no ODI. Faltou apenas o mapeamento do campo ID_CLIENTE e neste passo faremos algo diferente. Todas as tabelas de destino têm um ID próprio e único que é a PK da tabela. Estas PKs devem ser populadas com um número único de uma sequence chamada SEQ_DESTINOS, S EQ_DESTINOS, que se encontra criada no banco de destino. Agora, devemos clicar sobre a coluna ID_CLIENTE e clicar diretamente no ícone do “lápis” para abrir o editor de expressões (Figura 14). O editor de expressões auxilia a montar as expressões que estarão mapeadas nas colunas. Neste caso, mapeamos uma sequence na coluna ID_CLIENTE. Para isso, prefixamos o esquema onde a mesma se encontra no banco, por exemplo, ESQUEMA_DESTINO.SEQ_DESTINOS. O procedimento de manter prefixado (ESQUEMA.OBJETO) o esquema na Interface desenvolvida não é recomendado para grandes projetos. Exemplo: o esquema principal está nomeado como ESQUEMA_DESTINO em desenvolvimento, mas em outro ambiente (produção) o esquema pode variar de nome.
Esta alteração faria com que a Interface não executasse de maneira correta. A solução deste problema seria utilizar uma função própria do ODI que retorna o nome do esquema em que a interface esta sendo executada. Esta função pode ser encontrada dentro do Editor de Expressões (Figura 15), mais precisamente em Funções OdiRef. O ODI possui várias funções muito úteis. A lista completa destas funções podem ser encontradas no manual de referência da ferramenta. Para este exemplo em vez de ter uma sequence com o esquema prefixado (ESQUEMA_DESTINO.SEQ_DESTINOS) substituiríamos pela função denominada getShemaName, Figura 15.
Figura 14. Editor de expressões.
Figura 15. Editor de Expressões. Após escrever o comando a ser mapeado confirmamos com um “OK” na janela. Voltamos para a montagem da Interface. Notamos na Figura 16 que, ao lado do nome das colunas, encontram-se pequenos ícones, como uma pequena janela, um martelo (que ainda ai nda não se encontra na tela), um alvo e uma chave. Cada símbolo possui um significado: - Janela: indica que o campo será resolvido na origem e será avaliado durante o processo do ETL; - Martelo: indica que o campo será “resolvido” na área de stagging e será avaliado durante o processo do ETL; - Alvo: indica que o campo será “resolvido” apenas no destino, o que significa que ele não será avaliado durante o ETL e será apenas inserido no destino; - Chave: indica a chave da tabela. Por default, o ODI escolhe para ser a chave a própria chave primária (PK) da tabela, mas, como veremos neste caso, podemos modificar a chave para fazer com que o ODI resolva o ETL da maneira que nós desejamos.
Podemos trocar o local que o campo será executado (resolvido) clicando na coluna que desejamos modificar e em seguida na opção “Executar em:”, selecionando o local escolhido. No caso da sequence, iremos especificar que irá executar no ambiente de destino. Esta troca de diretório tem um motivo: a sequence não deve ser avaliada durante o processo de ETL e deve ser executada somente no momento da inserção do novo registro no destino. Se não for estruturada desta maneira causará um erro na sua execução. Outra tarefa necessária é a alteração da chave da tabela Cliente. Esta tabela tem como PK o campo ID_CLIENTE e é populado por uma sequence. Isso significa que o valor da PK sempre muda e novos registros seriam inseridos na tabela sempre que a Interface fosse executada. Se executássemos dez vezes a carga, os clientes estariam dez vezes duplicados na tabela de destino
Figura 16. Mapeamento completo para DIM_CLIENTE. O correto para a tabela Cliente é existir apenas um código por cliente, ou seja, precisamos que a coluna CDCLI seja a chave natural (NK - Natural Key). Para o ODI levar em consideração a coluna CDCLI como chave e não a atual PK ID_CLIENTE devemos proceder com a alteração conforme a Figura 17. Ao clicar sobre a tabela de destino DIM_CLIENTE percebemos que na opção “Atualizar Chave” está selecionado “DIM_CLIENTE_PK” que representa a PK da tabela no ODI.
Figura 17. Chave de DIM_CLIENTE. Trocamos o “Atualizar Chave” para a opção “sem definição” e agora temos a liberdade de selecionar a chave que necessitamos. Selecionamos então a coluna C DCLI e clicamos em “chave”, conforme Figura 18.
Figura 18. Mapeamento de DIM_CLIENTE. Com isso a chave para o ODI passa a ser CDCLI. Clicando sobre as colunas, podemos notar na estrutura “Atualizar”, checkcheck-boxes de “Inserir”, “Atualizar”, “UD1”, “UD2”, etc. (Figura 19). E stes checks
funcionam para configurar se o campo será inserido no destino, se ele será atualizado no destino ou se ele executará alguma das funções definidas pelo usuário (UD - User Defined). No nosso caso, todos os campos por padrão estão marcados como “Inserir” e “Atualizar”. Porém, no caso da coluna ID_CLIENTE devemos desmarcar a opção “Atualizar” (Figura 19), pois a sequence não pode participar do passo de update gerado pelo KM sob o risco de erros serem gerados na execução. Este processo ficará mais claro no momento da execução da interface que será explicado a seguir.
Figura 19. Configurando o comportamento dos campos. Concluída as configurações vamos para a aba “Fluxo”. Na tela de d e Fluxo (Figura 20) é representada a forma como a ferramenta irá fazer a execução da Interface.
Figura 20. Fluxo de trabalho do ODI.
Para este caso o ODI demonstra apenas um único exemplo com a utilização do IKM, que por si só irá resolver todo processo de ETL. Esta estrutura é única devido às tabelas que estamos utilizando como origem e as tabelas que queremos popular (tabelas de destino) se encontrarem em um mesmo Data Server (uma mesma Origem) configurado na topologia. Se esta estrutura estivesse em Data Servers diferentes, a ferramenta nos mostraria duas estruturas distintas, uma com a composição de um LKM responsável pela carga dos dados para as áreas de stage e outra com o IKM que realizaria os demais processos de ETL. Este caso será explorado no momento da construção das Interfaces que carregam os dados oriundos dos arquivos do tipo texto e do banco de dados Firebird. Ao clicar sobre a caixa denominada “Alvo“Alvo -Área de Teste” (Figura 20) podemos observar que o KM utilizado por padrão é o IKM (Oracle incremental Update). Resumidamente este KM faz cargas incrementais, ou seja, ele verifica a chave definida defini da na interface (CDCLI neste caso). E se esta chave ainda não existe no destino o processo faz a inserção da mesma de forma automática. Se esta chave já existe o processo apenas faz o Update nas colunas selecionadas com a opção “Atualizar” (Figura 19). Podemos notar também que o KM vem com várias opções de valores padrões. Ao clicar sobre cada opção, ao lado, apresenta-se a sua descrição. Para este trabalho iremos modificar apenas a opção “Flow Control” que devemos mudar para opção “não” (Figura 20). Quando a opção descrita estiver selecionada como “Sim” o ODI irá invocar o CKM (Validações - Ver explicação sobre CKM neste artigo) selecionado e fará a verificação dos dados durante o processo de ETL. Como não criamos nenhuma validação para esta tabela, podemos retirar a opção op ção de “Flow Control” desta interface. Para realizar a execução da interface basta clicar sobre o botão “Executar” no canto inferior direito da interface (Figura 21). Neste momento será apresentada uma tela questionando em qual contexto executar, neste caso o contexto de Desenvolvimento; qual o agente, vamos executar no agente local; e o nível de registro, que indica o grau de informações que deve ser gerado g erado no log do ODI, que podemos deixar o valor padrão 5.
Figura 21. Execução de uma Interface Durante a execução execução da Interface podemos acessar a “Lista de sessões” do módulo Operator e acompanhar o processo de execução das cargas (Figura 22). Verificando a execução (Figura 22), podemos observar os passos criados pelos KMs do ODI. Reparamos que a primeira palavra escrita es crita é “Integração”. Isto significa que todos os passos gerados por esta Interface foram de um IKM. Para carregar a tabela DIM_CLIENTES, a ferramenta gerou onze passos distintos. Os ícones em verde indicam comandos executados com sucesso. Ícones em amarelo indicam que o comando falhou, porém a execução continua normalmente. Ícones em vermelho significam erros que interrompem a execução da carga, que não foi o caso. No exemplo da Figura 22 percebe-se percebe-se que o passo indicou “atenção”. Isto aconteceu porque o O DI tentou dropar uma tabela temporária que ainda não existia no banco. Clicando duas vezes sobre qualquer passo é possível ver o que executou, quanto tempo levou para executar a carga, quantas linhas l inhas foram inseridas, entre outros.
Esta Interface (CLIENTES_IN) inseriu sete linhas na tabela de destino. Se esta Interface fosse executada novamente veríamos novamente os mesmos onze passos, mas no processo nenhuma nova linha seria inserida. Como esta Interface é incremental, ela carrega apenas as linhas que ainda não foram carregadas e faz a atualização de linhas quando a mesma não existir. DICA: Para compreender melhor como funcionam as configurações feitas no ODI, tente marcar a opção “Atualização” no campo ID_CLIENTE que é carregada juntamente com a s equence ou mude o local de execução de “Destino” para “Stagging” e compare os passos de uma execução e outra. No começo parece complicado, mas depois que aprendemos os “pequenos truques” da ferramenta verificamos que o ODI é uma poderosa e flexível ferramenta para processos ETL.
Figura 22. Execução da Interface CLIENTES_IN. Desenvolvimento da Interface - Carga Destino DIM_PRODUTO O próximo passo para o projeto é criar a Interface que carrega a tabela DIM_PRODUTO. A tarefa para montagem da carga é a mesma explanada anteriormente. Desta forma, vamos direto para o Diagrama da Interface (Figura 23). Todas as tabelas desta estrutura são provenientes da origem FIREBIRD. Importante: Devemos efetuar a modificação da coluna ID_PRODUTO I D_PRODUTO para ser executada no banco de destino (Ícone do “Alvo” da coluna ID_PRODUTO na Figura 23). Também devemos desmarcar a opção “Atualizar” para este atributo. Outra modificação que deverá ser efetuada é a troca da chav e da tabela (DIM_PRODUTO) para ser CDITEM e CDGRUPO, pois estes dois atributos referenciam a NK (Natural Key Chave Natural) da tabela. Outro ponto importante é que ao clicar no ícone do “lápis”, o ODI perguntará qual é a tecnologia a ser considerada no editor, pois temos duas tecnologias no diagrama (Firebird e Oracle). Selecionaremos o Oracle pois a sequence está no banco Oracle. Clicando na estrutura da aba “Fluxo” temos uma novidade: a “caixa” do LKM (Figura 24). Esta estrutura se encontra presente devido à necessidade de carregar dados que se encontram em outro banco de dados (neste caso o Firebird).
Figura 23. Diagrama de PRODUTOS_IN.
Figura 24. Fluxo de PRODUTOS_IN. Com isso o ODI primeiro extrai estes dados da base de origem repassando os mesmos para a stagging área. Em relação ao IKM, este terá o papel de pegar os dados e inserir nas tabelas de destino. Para a carga da tabela destino DIM_PRODUTO, vamos utilizar o LKM SQL to Oracle. Já em relação ao IKM selecionamos o IKM Oracle Incremental Update não esquecendo que neste devemos modificar a opção de “Flow Control” para “Não”. Ao executar esta Interface os resultados resul tados podem ser consultados na “lista de sessões” do Operator (veja a Figura 25). Notamos na Figura 25 que o número de passos de execuções aumentou para dezessete e que temos descrições das ações como “Carregando” e “Integração”. Os passos com as descrições carregando se referem aos passos gerados pelo LKM e os passos com “Integração” se referem aos passos gerados pelo IKM. Desenvolvimento da Interface - Carga Destino DIM_VENDEDORES Para criar a interface de vendedores basta seguir os mesmos passos das interfaces anteriores: selecionamos o nosso destino, a nossa origem, mapeamos os campos, colocamos a execução da sequence no alvo, desmarcamos a opção de “Atualizar” e trocamos a chave para CDVEND (Figura 26).
Figura 25. Execução de PRODUTOS_IN.
Figura 26. Mapeamento de VENDEDORES_IN. Em alguns casos a utilização de um filtro para os dados se torna necessária e pode auxiliar no processo de carga. Para exemplificar a utilização de um filtro na Interface de carga vamos inserir para esta interface, especificamente, um filtro na nossa origem (representada por um funil amarelo no diagrama -( Figura 26). Para fazer um filtro, basta clicar no campo que se deseja filtrar, arrastá-lo para o lado e soltar na área livre do diagrama. Após isso, podemos montar a estrutura e escrever o filtro que desejamos fazer. Neste caso colocaremos que o campo PERCCOM deve possuir valor menor a 50 (Figura 27). Esta carga possui somente o IKM, pois se trata do mesmo banco de dados e fará a carga com a estratégia incremental (IKM Oracle Incremental Update). Modificamos a opção do “Flow Control” para “Não” e executamos a interface.
Desenvolvimento da Interface - Carga Destino DIM_TEMPO Para a carga da dimensão tempo temos uma particularidade. A origem para esta carga é um arquivo texto com uma estrutura simples (Figura 28).
Figura 27. Utilizando filtro no ODI.
Figura 28. Mapeamento para TEMPO_IN. Aqui temos uma novidade: no mapeamento da coluna DATA_DIA utilizamos a função TO_DATE do Oracle (Figura 29), pois estamos lendo uma string do arquivo texto e estamos populando um campo do tipo DATE (TO_DATE(DTE.DATA_DIA,DD/MM/YYYY)). Neste caso não iremos utilizar a sequence do banco e sim a própria sequence existente no arquivo texto. Na aba fluxo para este caso teremos um LKM e um IKM. O LKM que iremos utilizar será o LKM File to SQL. Para P ara o IKM utilizaremos o Oracle Incremental, onde devemos setar a opção “Flow Control” igual a “Não”. Executando a interface podemos ver o resultado no Operator, como explicado anteriormente. Desenvolvimento da Interface - Carga Destino FATO_VENDAS Esta interface já tem uma lógica mais elaborada (Figura 30): estamos buscando as informações de duas origens: a tabela VENDA que tem sua origem proveniente do banco de dados Oracle e da tabela ITVENDA que vem do banco de dados Firebird. Além dessas origens ainda fazemos joins com as nossas tabelas de Dimensões, pois precisamos buscar os IDs que foram gravados anteriormente nas nossas interfaces. Os joins que são realizados são os seguintes:
- VENDA.NUMNF=ITVENDA.NUMNF; - VENDA.CDCLI=DIM_CLIENTE.CDCLI; VENDA.CDCLI=DIM_CLIENTE.CDCLI; - (DIM_PRODUTO.CDITEM =ITVENDA.CDITEM) AND DIM_PRODUTO.CDGRUPO =ITVENDA.CDGRUPO; - DIM_VENDEDOR.CDVEND DIM_VENDEDOR.CDVEND =VENDA.CDVEND; - VENDA.DTVENDA =DIM_TEMPO.DATA_DIA. Para este caso vamos inserir outro filtro (para reforçar o exemplo de utilização): DIM_TEMPO.TURNO = Manhã. Notamos na Figura 30 que a estrutura DIM_TEMPO possui, assim como explicado anteriormente, um pequeno “funil” amarelo representando que existe um filtro no processo de carga desta estrutura.
Figura 29. Diagrama de FATO_VENDAS_IN.
Figura 30. Mapeamento utilizando procedimento TO_DATE.
No fluxo selecionamos o LKM SQL to Oracle para ler as tabelas do banco Firebird e o IKM Oracle Incremental Update para fazer a carga. Marcamos também a opção “Flow Control” no IKM para “Não”. Como padrão, podemos executar a interface e ver o seu se u resultado no Operator.
Desenvolvimento do Pacote para Carga de Dados Após executar individualmente cada Interface podemos consultar as tabelas de destino e conferir que todas estão carregadas. Mesmo com a eficiência comprovada para cada carga este não é um modo prático para execução de cargas. Em um grande projeto, por exemplo, estas Interfaces não poderiam ser enviadas para outros ambientes, pois não são estruturas compiladas para execução em outros ambientes. Neste sentido necessitamos criar Pacotes para controlar o fluxo e criar cenários compilados para que a execução em outros ambientes seja garantida. Para inserir um novo Pacote, no projeto DW, clique com o botão direito sobre a opção “Pacotes” e em seguida selecione “Inserir Pacote”. Na aba “Definição” nomeamos o pacote. É na aba “Diagrama” que será desenvolvido o fluxo do processo de ETL. Nesta mesma tela pode-se encontrar várias funcionalidades (em forma de botões) que podem ser detalhados com o simples “passar” do mouse sobre cada um. A caixa de ferramentas do ODI contém diversos objetos que podem ser incluídos no fluxo ETL do nosso pacote. Entre eles temos objetos de envio de e-mail, execução de comandos do sistema operacional, processo de espera de eventos (tempo limite ou espera de algum registro em alguma tabela específica), manipulação de arquivos, entre outros. O detalhamento de cada componente pode ser visto no arquivo de ajuda do ODI, que se encontra no menu Ajuda na parte superior da tela. Para montar o fluxo devemos colocar as interfaces no diagrama do pacote. Para isso, clicamos sobre alguma interface e arrastamos para dentro do diagrama, conforme Figura 31. Podemos notar na Figura 31 que a interface CLIENTES_IN possui uma pequena “flecha verde” que indica que ela vai ser o primeiro objeto a ser executado. Para modificar qual objeto será o primeiro a ser executado é possível clicar em cima do objeto escolhido com o botão direito e escolher a opção “Primeira etapa”. Se executássemos o pacote pac ote neste momento somente a interface i nterface CLIENTES_IN seria executada, pois ainda não criamos o fluxo de execução completo do pacote.
Figura 31. Adicionando as Interfaces ao Pacote. Para criar este fluxo devemos clicar no botão bot ão “ok” (Etapa seguinte ao êxito) que contém uma flecha verde, na barra superior. Após este passo deve-se clicar sobre o objeto de origem e arrastar até o objeto de destino, conforme Figura 32. Temos também o botão “ko” (Próxima etapa ao falhar) que contém uma flecha vermelha, que desviará o fluxo se algum erro acontecer. Aplicaremos o fluxo de erro em momentos onde for pertinente.
Figura 32. Criando Fluxo de Execução. O mesmo procedimento deve ser repetido para o restante das Interfaces (Figura 33). Após isso, executaremos o pacote clicando no botão “Executar” (canto inferior direito).
OBSERVAÇÃO: Para manipular o local dos objetos no pacote, escolha o primeiro botão (o cursor branco - “Escolha livre”) na barra superior.
Figura 33. Fluxo do Pacote. Observando a execução da Interface no módulo Operator (Figura 34) podemos verificar que agora todas as nossas interfaces estão agrupadas em uma única execução do pacote, evitando a execução individual de cada uma. Outra tarefa importante pode ser realizada neste Pacote. Vamos implementar um LOG personalizado para guardar as informações importantes relacionadas a execução deste Pacote. Para isso usaremos a tabela LOG_CARGA que conterá o ID da sessão do ODI correspondente à execução e uma descrição informando se todos os processos da carga executaram com sucesso ou com erro. Para completar esta demanda vamos precisar criar uma Variável e dois novos Procedimentos: um para inserir os dados e outro para retornar o ID da sessão. Para completar esta tarefa precisamos entender melhor o que é uma Variável e um Procedimento no ODI. Criando Variáveis Para criar uma Variável devemos acessar o projeto PROJETO_ETL, na aba projetos, clicar com o botão direito sobre a opção “Variáveis” e escolher “Inserir Variável”. Na aba “Definição”, colocamos o nome da variável, escolhemos o seu tipo de dado e a sua Ação (Figura 35).
Figura 34. Execução do Pacote.
Figura 35. Criação de Variáveis no ODI. Para a opção Ação, A ção, temos as seguintes opções: - Historiar: O ODI manterá na aba “Histórico” todos os valores que a var iável já recebeu durante as suas execuções; - Valor mais recente: O ODI manterá na aba “Histórico” o último valor que a variável recebeu durante as suas execuções; - Não persistente: O ODI não não manterá nenhum histórico. A Ação escolhida neste caso é a “Não persistente”, pois não temos a necessidade de manter histórico para esta tarefa. Na aba “Atualizando” vamos adicionar um comando DDL que retornará o valor para a variável, ou seja, o comando é executado no banco de dados e o resultado é atribuído para a variável. Para este exemplo utilizamos um select simples na tabela “dual” (que retornará apenas um registro) utilizando a função do ODI <%=odiRef.getSession("SESS_NO")%>, que retornará o número da sessão. No combobox “Esquema” escolhemos em qual esquema e squema queremos executar esta DDL, que neste caso é o ORACLE_DESTINO (Figura 36). O teste para verificar se o procedimento foi realizado com sucesso pode ser feito ao clicar no botão Renovar. Se a Ação A ção da variável é “Historiar” ou “Valor mais recente”, podemos podemos ver o valor da variável na aba Histórico (Figura 37).
Figura 36. Configurando a variável.
Figura 37. Histórico da Variável. Nosso próximo passo é adicionar a variável no pacote e setarmos a mesma para ser executada como demanda inicial, pois queremos ter o número da sessão para gravar no log antes de começar o processo de ETL. Quando clicamos sobre a variável, podemos observar as suas propriedades, entre elas o “Tipo”, que pode ser setado de várias formas (o ícone no pacote e suas propriedades mudarão conforme o que for setado). As opções de Tipo são: são : - Declarar variável: é utilizado para receber um valor passado por parâmetro parâmetro quando executamos um cenário compilado; - Avaliar variável: é utilizado para fazer um teste lógico (=, <>, >, <, etc.) etc.) sobre o valor da variável. Se o teste lógico retornar verdadeiro, o fluxo segue para a próxima etapa seguinte ao êxito (flecha verde). Se retornar falso, o fluxo segue a próxima etapa ao falhar (flecha vermelha); - Renovar variável: executa o select colocado na aba “Atualizando” da variável, atribuindo o resultado do select à variável (o select deve retornar apenas um valor, ou um erro ocorrerá); - Definir variável: atribui manualmente manualmente o valor desejado à variável. Para o nosso pacote, escolheremos o tipo Renovar variável, pois queremos que a variável contenha o valor retornado do select da aba “Atualizando”. Isto faz com que tenhamos tenh amos o valor da sessão do ODI atribuída a nossa variável, com o objetivo de gravarmos posteriormente no log (Figura 38).
Figura 38. Tipos de Variáveis.
Criando Procedimentos Para criar Procedimentos no ODI devemos acessar a pasta DW, clicar com o botão direito sobre a opção “Procedimentos” e depois em “Inserir Procedimento” (Figura 39). Na aba “Definição” devemos apenas colocar o nome do nosso Procedimento. Já na aba “Detalhes”, devemos clicar no primeiro botão “Adicionar” na parte superior. Após este passo será aberta uma janela onde deve ser inserido o comando que queremos que este Procedimento execute. Percebemos aqui o nível de flexibilidade de trabalhar com o ODI. Nesta tela que foi apresentada é possível adicionar qualquer tipo de comando de qualquer tipo de tecnologia suportada pelo ODI, entre elas Oracle, Java, DBase, Hyperion Essbase, Java Script, entre outros. A lista completa de tecnologias suportadas pode ser vista no combobox “Tecnologia”. Para este exemplo, faremos apenas um simples insert em uma tabela, mas as possibilidades são muito maiores, podendo ter blocos inteiros de PL/SQL com uma lógica muito mais complexa, tudo dependendo da necessidade do projeto. Portanto, escolhemos a tecnologia Oracle, o esquema ORACLE_DESTINO (onde está a tabela de log) e escrevemos o comando a ser realizado, conforme a Figura 40.
Figura 39. Inserindo novo procedimento.
Figura 40. Criando novo Procedimento.
Notamos alguns detalhes diferentes di ferentes neste procedimento: - <%=odiRef.getSchemaName <%=odiRef.getSchemaName(( )%>: Função que retorna o nome do esquema esquema do banco de dados referente ao esquema lógico escolhido (ORACLE_DESTINO). Isso se faz necessário pois podemos ter nomes de esquemas diferentes em contextos diferentes. Em desenvolvimento podemos ter ORACLE_DESTINO e em produção podemos ter ORACLE_DESTINO_PROD. Assim, não podemos deixar o nome do esquema fixo, pois em produção geraria um erro; - #SESSAO_ODI: Nome da variável que criamos criamos que conterá o número número da sessão sessão do ODI, prefixada com #. No momento de execução, a ferramenta procurará e substituirá as variáveis que ele encontrar no código pelo seu valor no momento da execução. Devemos ter apenas cuidado para que a variável contenha algum valor, caso contrário um erro será gerado.
Podemos clicar em OK para fechar esta janela (Figura 40). Observe que poderíamos incluir quantos comandos fossem necessários, bastando apenas clicar no botão “Adicionar”. Poderíamos inclusive executar comandos de N tecnologias diferentes em ordem seqüencial. Nossa próxima tarefa é realizar a inclusão de outro procedimento. Para criar procedimentos no ODI devemos acessar novamente novamen te a pasta DW, clicar com o botão direito sobre a opção “Procedimentos” e clicar em “Inserir Procedimento”. Para esta estrutura basta nomeá -la e clicar em OK, pois iremos inserir uma nova Opção para este Procedimento. Opções são parâmetros que são repassados para o Procedimento. Para inserirmos uma Opção clicamos com o botão direito sobre o Procedimento e em seguida “Inserir Opção”. Será inserida uma Opção para indicar ao Procedimento se desejamos gravar uma mensagem de sucesso ou erro. Uma Opção pode ser de três tipos: - Marcar Caixa: Opção do tipo checkbox, onde é possível escolher entre as opções opções SIM/NÃO; - Valor: Recebe um valor alfanumérico com capacidade capacidade máxima de 250 caracteres; - Texto: Recebe um valor alfanumérico com capacidade ilimitada. ilimitada. O acesso a este tipo de opção é mais lenta do que o tipo Valor. Escolheremos o tipo “Valor” (ver Figura 41).
Figura 41. Criando uma nova Opção.
Vamos abrir novamente o procedimento, agora para criar um comando. Escolhemos neste sentido a tecnologia Oracle, o esquema ORACLE_DESTINO e digitamos o comando conforme a Figura 42. Este comando fará com que a tabela de log seja atualizada com uma mensagem de Erro ou de Sucesso, conforme o parâmetro passado para ele.
Figura 42. Procedimento para gravar detalhes em LOG. Neste comando temos o <%=odiRef.getOption("STATUS")%> que irá buscar o valor passado para o parâmetro através da Opção que criamos no passo anterior. Clicamos em OK e vamos inserir os Procedimentos no nosso fluxo do pacote. Na Figura 43 visualizamos o Fluxo de nossa carga.
Figura 43. Fluxo Final do Pacote. A leitura deste Fluxo pode ser feita desta forma: 1- Comece executando a atualização da variável SESSAO_ODI; SESSAO_ODI; 2- Insira um registro na tabela de LOG; 3- Execute as cinco interfaces e grave o status final na tabela do LOG; LOG;
4- Se algum procedimento der errado (flechas vermelhas), grave no LOG LOG o status de erro. As flechas verdes indicam o fluxo sem erros no pacote. As flechas vermelhas indicam o fluxo a ser tomado se algum erro ocorrer. Para incluir as flechas vermelhas, clique no botão “ko” na barra superior, clique no objeto origem e arraste para o objeto destino. Para as flechas verdes, funciona da mesma forma, mas selecionando o botão “ok”. A última tarefa necessária para execução do pacote é setar a Opção de cada procedimento de Update conforme a sua finalidade. Temos, portanto dois procedimentos, um que registrará as mensagens de erro e outro as mensagens de sucesso. Clicando no Procedimento que irá gravar a mensagem de erro (UPDATE_LOG_pr), vamos na aba “Opções” para inserir o valor de STATUS que este Procedimento deve receber quando for executado, que neste caso é ‘E´ (ERRO) (Figura 44). Seguiremos os mesmos passos para outro procedimento (também UPDATE_LOG_pr), onde adicionamos adicionamos o STATUS para ‘S´ (SUCESSO). Pronto, agora podemos executar o nosso pacote clicando no botão Executar na parte inferior da tela. Executando um Pacote Executando uma carga com sucesso (Figura 45) podemos notar na nossa tabela de log (LOG_CARGA) o seguinte seguinte registro: “A CARGA DA SESSAO 77001 TERMINOU COM SUCESSO!”
Figura 44. Setando o Status do procedimento de erro.
Figura 45. Execução com sucesso do pacote. Neste ponto podemos simular um erro para verificar a diferença com o processo de carga anterior. Para esta simulação vamos dropar a tabela FATO_VENDAS do banco de destino. Executando o cenário observamos que o fluxo foi desviado para o procedimento de LOG e foi gravado o seguinte registro (Figura 46): “A CARGA DA SESSAO 79001 TERMINOU COM ERRO! VEJA OPERATOR PARA MAIS DETALHES.” Percebe-se que existe uma diferença entre a Figura 45, que teve a execução da carga aplicada com sucesso e a Figura 46 que resultou em erro. Gerando um Cenário Agora que temos nosso pacote completo, falta apenas criar um cenário, que nada mais é do que a versão “compilada” do pacote. É este cenário que será mandado para outros ambientes (testes, produção, etc.) e que será utilizado para rodar as cargas. Para gerar um cenário, basta clicar com o botão direito sobre o pacote e depois em “Gerar cenário” (Figura 47).
Figura 46. Execução com erro do pacote.
Figura 47. Gerando um cenário.
Quando geramos um cenário, temos a opção de colocar uma versão para o mesmo e também a opção de dizer quais são as variáveis que o cenário receberá de entrada. Neste exemplo não temos variáveis de entrada, logo, podemos desmarcá-las. Pronto! Temos nosso cenário criado, como pode ser visto na Figura 48.
Figura 48. Cenário Criado. Este cenário funciona como qualquer programa compilado, onde não sofre mais alterações. É possível então fazer modificações nas nossas interfaces, modificar o fluxo do pacote, etc., porém este cenário continuará com a versão compilada anteriormente. Podemos, no entanto, recriar o cenário para refletir as modificações que por ventura foram realizadas, bastando para isso clicar com o botão direito sobre o cenário gerado e escolher a opção “Regenerar...”. Nota Devman - Sequence. No Oracle é possível gerar de forma automática uma seqüência de números, usando o comando sequence. Isto pode ser bastante útil quando se pretende criar um número único para uma chave primária. Conclusão Vimos neste artigo a facilidade e a versatilidade do ODI para construir processos de ETL. Sem muito esforço, conseguimos integrar diferentes origens de dados (Oracle, Firebird e arquivo texto) para um destino único Oracle. Fora a facilidade de se trabalhar com uma ferramenta visual, vimos que os Módulos de Conhecimento (KMs) nos facilitam a manutenção e a padronização dos códigos, tornando assim o ODI uma grande ferramenta para o desenvolvimento dos processos de ETL.
Oracle RAC Instalação - Parte 2 Ricardo Portilho Proni Com 20 anos de experiência profissional, Ricardo Portilho Proni é Oracle ACE e já trabalhou em grande parte dos maiores bancos de dados Oracle e MySQL do Brasil. É certificado em Oracle, MySQL, SQL Server, DB2, Sybase e WebSphere. Consultor e Instrutor da Nerv Informática Ltda (http://nervinformatica.com.br), também é conselheiro dos grupos de usuários GPO e GUOB, e palestrante dos eventos ENPO, GUOB Tech Day e Oracle Open World LAD. De que se trata o artigo? Instalação de um Banco de Dados em Cluster simulado, baseado na arquitetura Oracle RAC. Este artigo é o segundo de uma série sobre RAC. Para que serve? Para disponibilizar o acesso a um único Banco de Dados a partir de várias Instâncias, acomodadas em computadores diferentes. Em que situação o tema é útil? Em casos onde a disponibilidade e o poder de processamento são características fundamentais do ambiente. Nesta segunda parte, iremos finalizar a instalação de um banco de dados em Cluster utilizando o Oracle RAC. Na edição anterior, terminamos de instalar e configurar o Linux CentOS 4.7, e a máquina virtual foi desligada para iniciar a criação dos Shared Disks (Discos Compartilhados). Os Shared Disks são necessários porque, como o Cluster trata de vários computadores acessando o mesmo banco de dados, este precisa estar em um local que permita o acesso contínuo a todos os nós. Além do banco de dados - que compreende os data files, temporary files, redo logs, control files, entre outros - dois componentes muito importantes do Oracle RAC necessitam de armazenamento em Shared Disks. Estes dois componentes são o OCR, ou Oracle Cluster Registry e o Voting Disk, que serão melhor explicados mais adiante. O Oracle RAC suporta vários vár ios tipos de configuração para os Shared Disks. Podem ser utilizados Raw Devices, NFS (Nota DevMan), LVM, OCFS (Nota DevMan), OCFS2, ASM, ou mesmo filesystems proprietários de Cluster, desde que seja homologado pela Oracle para uso com Oracle RAC. Nota Devman - NFS NFS (acrônimo para Network File System) é um sistema de arquivos distribuídos desenvolvido inicialmente pela Sun Microsystems, Inc., a fim de compartilhar arquivos e diretórios entre computadores conectados em rede, formando assim um diretório virtual. O protocolo N etwork File System é especificado nas seguintes RFCs: RFC 1094, RFC 1813 e RFC 3530 (que (q ue tornou obsoleta a RFC 3010). Nota Devman - OCFS OCFS significa Oracle Cluster File System. É um sistema de arquivos compartilhado desenvolvido pela Oracle Corporation e lançado sob a licença GNU General Public License. A primeira versão do OCFS foi desenvolvida com o principal foco de acomodar arquivos de bancos de dados Oracle para bancos em Cluster. Por este motivo não era um sistema de arquivos compatível com POSIX. Com a versão 2, as funcionalidades POSIX foram incluídas. OCFS2 (versão 2) foi integrada na versão 2.6.16 do kernel do Linux. Inicialmente, foi marcada como código experimental. Esta restrição foi removida na versão 2.6.19. Com a versão 2.6.29 mais funcionalidades foram incluídas no ocfs2, especialmente controle de acesso e cotas. OCFS2 usa um gerenciador distribuído de arquivos que lembra o OpenVMS DLM, mas é muito mais simples.
Nesta série de artigos, iremos utilizar principalmente o ASM, ou Automatic Storage Management, que é um sistema de arquivos de uso específico para bancos de dados desenvolvido pela própria Oracle. O ASM será preferido por sua ampla adoção atual no mercado, sendo que a Oracle está visivelmente favorecendo seu desenvolvimento desde o início em detrimento do OCFS e OCFS2. No nosso ambiente de testes, para evitar que o leitor precise adquirir um Storage para utilizar os Shared Disks, iremos emulá-los utilizando os arquivos de discos do próprio VMware. Criação dos Shared Disks Iremos a seguir criar cinco discos virtuais: um para o OCR (Nota DevMan), um para o Voting Disk (Nota DevMan), e três para o ASM. Nota Devman – Devman – OCR O OCR é utilizado no Oracle RAC para armazenar configurações do Cluster e as informações de status de cada recurso que é administrado por ele. Por exemplo: os nomes dos nós, os endereços IPs e VIPs, qual a localização dos voting disks, nomes dos bancos de dados e instâncias, nomes dos listeners e etc. O OCR é um arquivo de formato binário, mantido pelo daemon CRS, que deve ser armazenado em uma partição - um Raw Device - ou um arquivo em um Cluster File System. Nota Devman - Voting Disk Um Voting Disk (Disco de Voto) é um disco compartilhado, uma partição ou um arquivo usado para determinar a disponibilidade de um nó do Cluster. Todas as instâncias do RAC gravam no Voting Disk regularmente para indicar que estão ativas. Isso é exigido, pois no caso de uma das instâncias não poder se comunicar com a outra, as informações de quais instâncias estão ativas ainda estão disponíveis para o Cluster em um local compartilhado. O Voting Disk pode ser armazenado, por exemplo, em um Raw Device ou um Cluster File System. Eles podem e devem ser espalhados para evitar-se evitar -se um ponto único de falhas no Cluster. Para criar o primeiro disco, o do OCR, siga os passos: 1. Desligue a máquina virtual; 2. No VMware, clique em “Edit Virtual Vi rtual Machine Settings”, e depois, em “Add”; 3. Em “Add Hardware Wizard”, clique em “Next”; 4. Em “Hardware Type”, selecione “Hard Disk”; 5. Em “Select a Disk”, selecione “Create a Virtual Disk”; 6. Em “Select a Disk Type”, selecione “SCSI”; 7. Em “Specify Disk Capacity”, selecione “10 GB” em “Disk Size”. Deselecione “Allocate all disk space now”, e selecione “Split into 2 GB files”; 8. Em Specify Disk File, coloque “C:\Virtual “C: \Virtual Machines\Shared\ocr.vmdk”. Machines\Shared\ocr.vmdk”. Não clique em Finish ainda, e clique em Advanced; 9. Em “Advanced”, em “Virtual device node”, selecione “SCSI 1:1”. Selecione também “Independent”, e depois “Persistent”; 10. Agora sim, clique em “Finish”. Para criar o segundo disco, o do Voting Voting Disk, siga os passos 1 a 7 anteriores e logo após: 8. Em Specify Disk File, coloque “C:\Virtual “C: \Virtual Machines\Shared\votingdisk.vmdk”. Machines\Shared\votingdisk.vmdk”. Não clique em Finish ainda, e clique em Advanced; 9. Em “Advanced”, em “Virtual device node”, selecione “SCSI 1:2”. Selecione também “Independent”, e depois “Persistent”; 10. Agora sim, clique em “Finish”.
Para criar o terceiro disco, o primeiro do ASM, siga os passos 1 a 7 anteriores e logo após: 8. Em Specify Disk File, coloque “C:\Virtual “C: \Virtual Machines\Shared\asm1.vmdk”. Machines\Shared\asm1.vmdk”. Não clique em Finish ainda, e clique em Advanced; 9. Em “Advanced”, em “Virtual device node”, selecione “SCSI 1:3”. Selecione também “Independent”, e depois “Persistent”; 10. Agora sim, clique em “Finish”. Para criar o quarto disco, que será o segundo do ASM, siga os passos 1 a 7 anteriores e logo após: 8. Em Specify Disk Disk File, coloque “C:\Virtual “C: \Virtual Machines\Shared\asm2.vmdk”. Machines\Shared\asm2.vmdk”. Não clique em Finish ainda, e clique em Advanced; 9. Em “Advanced”, em “Virtual device node”, selecione “SCSI 1:4”. Selecione também “Independent”, e depois “Persistent”; 10. Agora sim, clique em “Finish”; Da mesma forma, para criar o quinto disco, que será o terceiro do ASM, siga os passos 1 a 7 anteriores e logo após: 8. Em Specify Disk File, coloque “C:\Virtual “C: \Virtual Machines\Shared\asm3.vmdk”. Machines\Shared\asm3.vmdk”. Não clique em Finish ainda, e clique em Advanced; 9. Em “Advanced”, em “Virtual device node”, selecione “SCSI 1:5”. Selecione também “Independent”, e depois “Persistent”; 10. Agora sim, clique em “Finish”.
Configuração dos Shared Disks Depois de criar os discos na máquina virtual, iremos editar o seu arquivo de configuração para a simulação dos Shared Disks. Verifique se as linhas da Listagem 1 estão no arquivo C:\Virtual Machines\RAC1\Red Hat Enterprise Linux 4.vmx. Olhe linha a linha. Se a linha já existir, deixe-a intacta. Se não existir, adicione-a. Particionamento dos Shared Disks Agora precisamos configurar estes discos novos dentro do Linux. Para isto, inicie o RAC1. Durante a inicialização do CentOS, quando o Kudzu (um serviço do CentOS que verifica se ocorreram mudanças no hardware) avisar avisar que um novo hardware foi encontrado, selecione a opção “Configure”. Após a inicialização, execute logon com o usuário root, e execute os comandos da Listagem 2. Estes comandos, com o utilitário fdisk, irão criar uma partição (Nota DevMan) em cada disco. Nota Devman - Partições Uma partição é uma divisão de um disco rígido (SCSI ou ATA). Cada partição pode conter um sistema de arquivos diferente. Conseqüentemente, vários sistemas operacionais podem ser instalados na mesma unidade de disco.
Listagem 1. Configuração do arquivo C:\Virtual Machines\RAC1\Red Hat Enterprise Linux 4.vmx. disk.locking = "FALSE" diskLib.dataCacheMaxSize = "0" diskLib.dataCacheMaxReadAheadSize = "0" diskLib.dataCacheMinReadAheadSize = "0" diskLib.dataCachePageSize = "4096" diskLib.maxUnsyncedWrites = "0" scsi1.sharedBus = "VIRTUAL" tools.syncTime.period = "1" timeTracker.periodicStats="TRUE" timeTracker.statsIntercal="10" reslck.timeout = "300" scsi1:1.deviceType = "plainDisk" scsi1:1.redo = "" scsi1:2.deviceType = "plainDisk" scsi1:2.redo = "" scsi1:3.deviceType = "plainDisk" scsi1:3.redo = "" scsi1:4.deviceType = "plainDisk" scsi1:4.redo = "" scsi1:5.deviceType = "plainDisk" scsi1:5.redo = "" Listagem 2. Comandos a serem executados como root.
1. 2. 3. 4.
5.
fdisk /dev/sdb - Aperte a tecla “n”, para criar uma nova partição; - Em seguida, aperte a tecla “p”, para que a nova partição seja primária; - Em seguida, aperte a tecla “1”, pois poi s será a primeira partição do disco; - Em seguida, aperte “Enter”, para aceitar as opções padrão de tamanho, usando todo o disco; - Em seguida, aperte “w”, para gravar as alterações. fdisk /dev/sdc -- siga os passos passos 1 a 5 fdisk /dev/sdd -- siga os passos 1 a 5 fdisk /dev/sde -- siga os passos 1 a 5 fdisk /dev/sdf -- siga os passos passos 1 a 5
Em seguida, para que cada partição seja relacionada a um raw device, edite o arquivo /etc/sysconfig/rawdevices e adicione as linhas da Listagem 3. Listagem 3. Configuração do arquivo /etc/sysconfig/rawdevices. /dev/raw/raw1 /dev/sdb1 /dev/raw/raw2 /dev/sdc1 /dev/raw/raw3 /dev/sdd1 /dev/raw/raw4 /dev/sde1 /dev/raw/raw5 /dev/sdf1
Depois, execute os comando da Listagem 4 como root. roo t. Listagem 4. Comandos a serem executados como root. service rawdevices restart ln -s /dev/raw/raw1 /u01/oradata/ocr ln -s -s /dev/raw/raw2 /u01/oradata/votingdisk ln -s -s /dev/raw/raw3 /u01/oradata/asm1 ln -s -s /dev/raw/raw4 /u01/oradata/asm2 ln -s -s /dev/raw/raw5 /u01/oradata/asm3 chown oracle:oinstall /dev/raw/raw1 chown oracle:oinstall /dev/raw/raw2 chown oracle:oinstall /dev/raw/raw3 chown oracle:oinstall /dev/raw/raw4 chown oracle:oinstall /dev/raw/raw5 chmod 600 /dev/raw/raw1 chmod 600 /dev/raw/raw2 chmod 600 /dev/raw/raw3 chmod 600 /dev/raw/raw4 chmod 600 /dev/raw/raw5 Pronto, o primeiro nó do Cluster Cl uster está pronto para receber o Clusterware. Cl usterware. Clonando a Máquina Virtual Para não termos que instalar o outro nó desde o início, iremos simplesmente clonar o primeiro nó, que já está configurado da forma correta. Para fazer a clonagem: - Desligue o primeiro nó. A partir de agora, iremos nos referir a ele pelo nome da máquina, ou seja, RAC1; - Copie a pasta “C:\Virtual “C:\Virtual Machines\RAC1” Machines\RAC1” para “C:\Virtual “C:\Virtual Machines\RAC2”; Machines\RAC2”; - No arquivo “C:\Virtual “C:\Virtual Machines\ RAC2\Red RAC2\Red Hat Enterprise Linux 4.vmx”, procure pela linha que contém “displayName = "RAC1"” e mude para ”DisplayName = "RAC2"”. - No Vmware, importe o RAC2 (File → Open - Browse) - Inicie o RAC2 (ainda não inicie o RAC1) - Ao iniciar a VM, escolha a opção “Create” - No Kudzu, escolha “Keep Configuration” e depois “Ignore” Agora, com o RAC2 ligado, precisamos ajustar suas configurações de rede, que neste momento estão iguais ao do RAC1, de onde foi clonado. Para isso: - Execute logonj no RAC2, como root, no ambiente gráfico; - Clique em “Applications”, depois em “System Settings”, e escolha o menu “Network”; - Clique em “DNS”. Em “Hostname”, coloque “rac2.localdomain”; - Clique na aba “Devices” e selecione “eth0”. Em “Edit”, clique em “Hardware Device”, depois em “Probe”, e “OK”. Este passo foi necessário para alterar-se alterar-se o MAC Address da placa de rede virtual, que deve ser trocado quando a máquina virtual é clonada; - Na aba “Devices”, selecione “eth0”, clique em “Edit”, e “General”. Em “Address”, mude o final do endereço IP de 101 para 102; - Clique na aba “Devices” e selecione “eth1”. Em “Edit”, clique em “Hardware Device”, depois em “Probe”, e “OK”. Este passo foi necessário para alterar-se alterar-se o MAC Address da placa de rede virtual, que deve ser trocado quando a máquina virtual é clonada. - Na aba “Devices”, selecione “eth1”, clique em “Edit”, e “General”. Em “Address”, mude o final do endereço IP de 101 para 102; - Clique em “File”, e em “Save”. Clique em “OK”; - Em “Devices”, selecione “eth0”, e clique em “Activate”; - Em “Devices”, selecione “eth1”, e clique em “Activate”; - Como usuário oracle, edite o arquivo /home/oracle/.bash_profile, e troque ORCL1 por ORCL2. Este será o SID do Oracle no no RAC2.
Pronto, o RAC 2 está com o nome da máquina e endereços IPs corretos. Neste momento, você pode ligar o RAC1 novamente, pois não haverá conflitos de IP. Instalação do Oracle Clusterware Agora iremos instalar o software que irá cuidar da administração do Cluster e todos os programas que funcionarão neste ambiente, incluindo o próprio Oracle (ler Nota DevMan ). Nota Devman - Oracle Clusterware Oracle Clusterware é um software de Cluster portável que agrupa servidores individuais para que cooperem como um único sistema. Componente fundamental do Oracle RAC, o Oracle Clusterware pode operar de forma independente e ajuda a assegurar a proteção de um aplicativo, seja da Oracle ou de terceiros. O Oracle Clusterware possibilita a alta disponibilidade, um componente essencial da continuidade dos negócios, para aplicativos e bancos de dados gerenciados no ambiente de Cluster incluindo bancos de dados Oracle de uma única instância, Oracle Application Server, componentes do Oracle Enterprise Manager, bancos de dados de outros fornecedores e outros apl icativos. A instalação do Oracle Clusterware só precisa ser feita em um dos nós, e automaticamente será replicada para todos os outros nós existentes no Cluster. Em um certo momento da instalação, serão solicitados que sejam executados scripts como o usuário root em todos os nós, mas a maior parte da instalação é replicada automaticamente por todo o Cluster. Cl uster. Para instalar o Oracle Clusterware, teremos que copiar os Softwares baixados anteriormente da máquina Host (Windows) para a máquina Virtual (Linux). Este tipo de cópia pode ser feita com um utilitário como o WinSCP. O WinSCP pode ser baixado em http://winscp.net. - Copie o arquivo 10201_clusterware_linux32.zip para o RAC1; - Copie o arquivo 10201_database_linux32.zip para o RAC1; - Execute logon no RAC1 com o usuário oracle, e descompacte os arquivos (Listagem 5). - Verifique se há uma linha no arquivo /etc/hosts que começa com 127.0.0.1. Esta linha deve estar como na Listagem 6. Se não estiver, edite-a para que fique correta: - Execute logon no RAC2, com o usuário root. Listagem 5. Comandos a serem executados como o usuário oracle. cd /home/oracle unzip 10201_clusterware_linux32.zip unzip 10201_database_linux32.zip Listagem 6. Configuração do arquivo /etc/hosts. 127.0.0.1 localhost Agora iremos iniciar o assistente para instalação do Oracle Clusterware. No RAC1, abra um terminal e execute os comandos da Listagem 7. O último comando da Listagem 7 irá lançar o instalador do Clusterware. Depois de iniciado o instalador do Clusterware, siga este passos: - Clique em “Next”; - Na tela “Specify Cluster Configuration”, você deve verificar se os nomes dos nós do Cluster estão corretos. Nesta tela, são informados os 3 nomes (sendo que cada um corresponde a um endereço IP, configurados anteriormente) de cada nó: um público (Public Node Name), um privado (Private Node Name), e um virtual (Virtual Node Name), conforme mostrado na Figura 1. Em seguida, clique em Next; - Na tela “Specify Network Interface Usage”, devem ser especificadas as red es públicas e privadas dos nós. Na linha que mostra a “Interface Name” como “eth0”, você deve colocar no campo “Subnet” o endereço “192.168.202.0”. Da mesma forma, na linha que mostra a “Interface Name” a “eth1”, você deve colocar no campo “Subnet” o enredeço “192.168.203.0”.
Listagem 7. Comandos a serem executados como o usuário us uário oracle. export $ORACLE_HOME=$CRS_HOME /home/oracle/clusterware/runInstaller.sh
Figura 1. Tela “Specify Cluster Configuration”. - Na tela “Specify Network Interface Usage”, devem ser especificadas as redes públicas e privadas dos nós. Na linha que mostra a “Interface Name” como “eth0”, você deve colocar no campo “Subnet” o endereço “192.168.202.0”. Da mesma forma, na linha que mostra a “Interface Name” a “eth1”, você deve colocar no campo “Subnet” o enredeço “192.168.203.0”.(Figura 2. )
Figura 2. Tela “Specify Network Usage Configuration”.
- Na tela “Specify Oracle Cluster Registry (OCR) Location”, deve ser especificado o link simbólico para o raw device que configuramos anteriormente para o OCR, o “/u01/oradata/ocr”, como está na Figura 3. Em seguida, clique em Next;
Figura 3. Tela “Specify Oracle Cluster Registry (OCR) Location”. - Na tela “Specify Voting Disk Location”, deve ser especificado o link simbólico para o raw device que configuramos anteriormente para o Voting Disk, o “/u01/oradata/votingdisk”, como está na Figura 4. Em seguida, clique em Next;
Figura 4. Tela “Specify Voting Disk Location”. - Após executados estes passos, a instalação do Clusterware irá prosseguir nos dois nós, e o instalador irá executar uma verificação ao término da instalação. Em alguns ambientes, o VIPCA não é configurado automaticamente, e isto será acusado nesta última verificação, como na Figura 5. Mas isto não é um problema: quando o último check acusar problemas, como root, execute o VIPCA (Virtual IP Configuration Assistant).
Figura 5. Erro na verificação final do Clusterware.
Para executar o VIPCA, abra um terminal, e como root, execute o comando da Listagem 8. Listagem 8. Comandos a serem executados como o usuário us uário root. /u01/app/oracle/product/10.2.0/crs/bin/vipca - No VIPCA, devem ser configurados os nomes e IPs dos VIPs (192.168.202.111 e 192.168.202.112), como demonstrado na Figura 6.
Figura 6. Configuração do VIPCA. Após executar o VIPCA, volte para a tela de instalação do Clusterware, clique em “Retry” na verificação, em seguida, em “Finish”. Pronto, o Clusterware foi instalado com sucesso e está pronto para receber o Oracle.
Instalação do Oracle Agora o Cluster está pronto para receber o software que irá gerenciar o banco de dados. A instalação do Oracle em ambiente RAC só precisa ser feita em um dos nós, e automaticamente será replicada para todos os outros nós existentes ex istentes no Cluster. Para instalar o Software Oracle, execute logon no RAC1, e execute o comando da Listagem 9. Será iniciado então o Oracle Universal Installer. Na tela inicial do OWI, clique em Next. Selecione a opção "Enterprise Edition", e depois clique em "Next"; - Aceite as opções para o ORACLE_HOME, ORACLE_HOME, clicando em “Next”; - Selecione a opção "Cluster Installation", e verifique se os dois nós do Cluster estão selecionados. Depois, clique em “Next”; - Aguarde a verificação dos pré-requisitos. Tudo deve ocorrer bem nesta verificação se todos os passos passos anteriores forem cumpridos. Depois, clique em “Next”. Se houver algum “warning”, provavelmente será pelos pré-requisitos pré- requisitos de memória, mas isso não será problema neste ambiente. Pode clicar em “Yes”, se este tipo de alerta aparecer; - Em “Select Configuration Option”, escolha “Configure Automatic Storage Management (ASM)”, escolha uma senha para administrar o ASM (Nota DevMan), e clique em Next. Esta opção fará com que, além de instalar o Software, o ASM já seja configurado. As instâncias ASM irão rodar nos dois nós, sobre os discos virtuais criados e gerenciar os arquivos do banco de dados. Também será necessário criarmos um Disk Group, que é a unidade básica de gerenciamento de discos no ASM, onde poderemos colocar os dados. Listagem 9. Comandos a serem executados como o usuário us uário oracle. /home/oracle/database/runInstaller.sh
Nota Devman – Devman – ASM Automatic Storage Management (ASM) é uma funcionalidade provida pelo Oracle a partir da versão 10g revisão 1. ASM procura simplificar o gerenciamento de arquivos de bancos de dados. Para isso, ele oferece ferramentas para gerenciar sistemas de arquivos e volumes diretamente dentro do kernel do banco de dados, permitindo que administradores de bancos de dados (DBAs) controlem volumes e discos com familiares comandos SQL no Oracle. Desta forma o DBA não precisa de conhecimentos extras em sistemas de arquivos específicos ou gerenciadores de volume, que geralmente operam no nível do sistema operacional. Com ASM: - IOs podem tomar vantagem de striping striping de dados e mirroring via software. - DBAs podem podem automatizar automatizar redistribuições sem paradas, com a adição ou remoção de discos. - O sistema mantém cópias redundantes e oferece funcionalidades de RAID de terceiros. terceiros. - Oracle suporta tecnologias de multipathing IO de terceiros (failover ou load balancing para acessos a SAN, etc.) - A necessidade de hot spares diminui. - Em “Configure Automatic Storage Management”, como em “Disk Group Name”, colo que “DATA”. Este será o nome do Disk Group que usaremos para o Banco de Dados. Selecione o nível de “Redundancy” como “External”, que é nenhuma redundância, o que é apropriado para nosso teste, mas não para um ambiente produtivo. No quadro “Candidate Disks”, verifique se os três raw devices que criamos anteriormente estão selecionados (/dev/raw/raw3, /dev/raw/raw4 e /dev/raw/raw5). - Clique em Install, e aguarde a finalização da instalação e configuração do ASM. - Ao término da instalação, será solicitado que um script seja executa como root. Execute conforme esta tela lhe mostrar, e depois prossiga.
Criação do banco de dados Iremos agora criar o banco de dados. Esta operação, na versão 10g, é bem mais tranquila, pois pode ser feita com o assistente DBCA, ou Database Configuration Assistant. Esta operação só precisa ser feita em um dos nós do cluster, e as instâncias serão criadas automaticamente em todos os nós. Neste artigo iremos executar esta configuração a partir do RAC1. RA C1. Para criar o banco de dados: - Verifique se o RAC1 e RAC2 estão iniciados; - Execute logon no RAC1 com o usuário oracle, e inicie o “Database Configuration Assistant”, digitando F2, e em seguida, “dbca”; - Em "Welcome", selecione a opção "Oracle Real Application Clusters database", e depois clique em "Next"; - Selecione "Create a Database", e depois clique em "Next"; - Selecione os dois nós do RAC, e depois clique em “Next”; - Selecione "Custom Database", e depois clique em "Next"; - Em “Global Database Names”, coloque “ORCL”. Em SID, também coloque “ORCL”. Este será o nome do Banco de Dados. Depois, clique em “Next”; - Aceite as opções de gerenciamento, e clique cli que em “Next”; - Selecione senhas para as contas do Banco de Dados. Depois, clique em “Next”; - Selecione a opção “Automatic Storage Management ( ASM)", e depois clique em "Next"; - Selecione “DATA” como Disk Group, e depois clique cli que em “Next”; - Aceite a opção Use Oracle-Managed Oracle-Managed Files", e depois clique em “Next”; “Nex t”; - Selecione a opção "Specify Flash Recovery Area" e "Enable Archiving”. Coloque "+DATA" como a “Flash Recovery Area”, e depois clique em "Next"; - Aceite as configurações padrão de “Database Services”, clicando em “Next”; - Em “Memory Management”, ”Selecione a opção "Custom", e depois clique em “Next”; - Aceite as opções de “Storage Settings”, clicando em “Next”; - Aceite as opções de “Database Creation”, clicando em e m “Next”; - Aceite as informações do “Summary Information”, clicando em “Next”; Espere enquanto o DBCA finaliza a criação do banco de dados. Quando a instalação terminar, pode clicar em “Finish”. Pronto, P ronto, seu banco de dados em Cluster C luster está rodando!
Conclusões Neste artigo finalizamos a instalação de um banco de dados em Cluster utilizando Oracle RAC, rodando sobre máquinas virtuais em VMware Server, e utilizando util izando o Linux CentOS. No próximo artigo, iremos abordar as tarefas de administração do Oracle RAC, e ver como elas diferem de uma implementação Single Instance do Oracle. Veremos como funciona o Backup e Restore, os Archived Redo Logs, as manutenções no ASM, Backup e Restore do OCR e Voting Disk, como adicionar e remover nós, e executar Rolling Upgrades, que são as aplicações de patch sem indisponiblidade. i ndisponiblidade. Abraços e até a próxima!
