Oracle Blog

Um pouco do pacote UTL_FILE, DBMS_LOB e objeto DIRECTORY. Gerando e carregando conteúdo de arquivos externos ao Oracle ...

2009-12-05T14:10:00.004-02:00

Olá,

Neste artigo irei demonstrar através de exemplos práticos como gerar arquivos externos ao banco de dados, bem como ler o conteúdo dos mesmos através da package UTL_FILE disponível desde a versão 7.3 do banco de dados Oracle. Em resumo, a package UTL_FILE permite que programas escritos em PL/SQL acessem arquivos do sistema operacional para leitura e escrita. Irei também demonstrar como carregar arquivos binários para dentro de tabelas de banco dados fazendo uso da package DBMS_LOB. Por falar em arquivos binários, o SQL*Plus da versão 11g já nos permite visualizar dados de colunas do tipo BLOB o que, nas versões anteriores, não era possível fazendo com que o SQL*Plus emitisse a mensagem "SP2-0678: Coluna ou tipo de atributo não pode ser exibido pelo SQL*Plus". Com isso, não precisaremos mais ficar removendo as colunas do tipo de dado BLOB em uma consulta SQL.

Enfim, será abordado tanto o uso do já obsoleto parâmetro UTL_FILE_DIR, quanto a criação do objeto DIRECTORY responsável por armazenar o caminho (diretório) de sistema operacional onde reside o banco de dados Oracle, de forma a permitir a escrita e leitura de arquivos.

No caso de se utilizar o parâmetro UTL_FILE_DIR, o comando abaixo deverá ser emitido pelo DBA (se estiver usando spfile) ou o arquivo de inicialização alterado manualmente init[SID].ora. Após a alteração, o banco de dados precisará ser reiniciado.

alter system set utl_file_dir = '[caminho_diretorio]' scope=spfile;

Vamos então aos exemplos práticos. Como demonstrado abaixo, o banco de dados está com o parâmetro UTL_FILE_DIR setado com o caminho "/tmp" que é um diretório na qual o usuário de sistema operacional "oracle" possui permissões de leitura/escrita. Para essas demonstrações, estou utilizando o sistema operacional Linux CentOS release 5.2 e o banco de dados Oracle 11g (11.1.0.7.0).

C:\>sqlplus system/*****

SQL*Plus: Release 11.1.0.6.0 - Production on Sab Dez 5 13:15:32 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter utl_file_dir

NAME                          TYPE        VALUE
----------------------------- ----------- ----------------------------
utl_file_dir                  string      /tmp

No exempo abaixo irei gerar um arquivo simples que irei chamar de arq_teste.txt contendo algumas linhas de texto. Para isso, usarei a procedure PUT_LINE da package UTL_FILE.


-- Gerando o arquivo de texto externo ao banco de dados
SQL> declare
  2    v_arquivo utl_file.file_type;
  3  begin
  4    v_arquivo := utl_file.fopen('/tmp', 'arq_teste.txt', 'W');
  5    utl_file.put_line(v_arquivo, 'linha 1');
  6    utl_file.put_line(v_arquivo, 'linha 2');
  7    utl_file.put_line(v_arquivo, 'linha 3');
  8    utl_file.put_line(v_arquivo, 'linha 4');
  9    utl_file.put_line(v_arquivo, 'linha 5');
 10    utl_file.fclose(v_arquivo);
 11  exception
 12    when utl_file.invalid_path then
 13       raise_application_error(-20000, 'Atenção! O caminho informado não existe.');
 14  end;
 15  /

Procedimento PL/SQL concluído com sucesso.

Acessando o sistema operacional, podemos ver que o arquivo arq_teste.txt foi criado e seu conteúdo populado conforme o script PL/SQL acima.

[oracle@linux tmp]$ ls -l
total 8
-rw-r--r-- 1 oracle oinstall   40 Dez 5 13:16 arq_teste.txt

[oracle@linux tmp]$ cat arq_teste.txt
linha 1
linha 2
linha 3
linha 4
linha 5

Para que possamos ler o conteúdo do arquivo que foi gerado no sistema operacional através de PL/SQL, farei uso da procedure GET_LINE da package UTL_FILE.


-- Lendo o conteúdo do arquivo de texto gerado
SQL> set serveroutput on
SQL> declare
  2    v_linha varchar2(2000) := '';
  3    v_arquivo utl_file.file_type;
  4  begin
  5    v_arquivo := utl_file.fopen('/tmp', 'arq_teste.txt', 'R');
  6    loop begin
  7      utl_file.get_line(v_arquivo, v_linha);
  8      dbms_output.put_line('linha lida=' || v_linha);
  9      exception
 10        when no_data_found then
 11        utl_file.fclose(v_arquivo);
 12        exit;
 13      end;
 14    end loop;
 15  end;
 16  /
linha lida=linha 1
linha lida=linha 2
linha lida=linha 3
linha lida=linha 4
linha lida=linha 5

Procedimento PL/SQL concluído com sucesso.

Da mesma forma que utilizamos o parâmetro UTL_FILE_DIR, poderíamos ter utilizado o objeto de banco de dados DIRECTORY. O objetivo do objeto directory (diretório) é armazenar o caminho de um diretório de sistema operacional, assim como o parâmetro UTL_FILE_DIR. A grande vantagem é que podemos criar vários objetos que apontam para diferentes diretórios do sistema operacional, além de não termos que ter de efetuar o shutdown/startup da instância após a sua criação como acontece com parâmetro UTL_FILE_DIR. Vale a pena salientar que podemos também configurar múltiplos caminhos com o parâmetro UTL_FILE_DIR bastando, para isso, definirmos mais de uma entrada no arquivo de inicialização da instância como demonstrado no exemplo abaixo:

utl_file_dir="/u01/oradata/prod/utl_dir/"
utl_file_dir="/u02/oradata/prod/utl_dir/"
utl_file_dir="/u03/oradata/prod/utl_dir/"

No mais, o objeto DIRECTORY é global no banco de dados, ou seja, não é de propriedade de nenhum usuário de banco de dados específico (a não ser do próprio SYS). Para que os usuário de banco de dados possam utilizá-lo, será necessário conceder os privilégios READ (leitura) e/ou WRITE (escrita) dependendo de cada uso.

Como já demonstrado nos exemplos acima, irei realizar a mesma operação de escrita e leitura em arquivos do sistema operacional mas, agora, irei fazer uso do objeto DIRECTORY (diretório).


SQL> create or replace directory arquivo_dir as '/tmp';

Diretório criado.

Vale a pena salientar que os demais usuários de banco de dados deverão ter os privilégios apropriados, como demonstrado pelo comando abaixo, afim de terem acesso ao objeto DIRECTORY.

grant read, write on directory [nome_diretorio] to [usuarios...];

Continuando com a demonstração, irei realizar os mesmos procedimentos, mas agora utilizando o objeto directory ARQUIVO_DIR em vez do caminho fixo definido no parâmetro UTL_FILE_DIR.


-- Gerando o arquivo de texto externo ao banco de dados
SQL> declare
  2    v_arquivo utl_file.file_type;
  3  begin
  4    v_arquivo := utl_file.fopen('ARQUIVO_DIR', 'arq_teste.txt', 'W');
  5    utl_file.put_line(v_arquivo, 'linha 1');
  6    utl_file.put_line(v_arquivo, 'linha 2');
  7    utl_file.put_line(v_arquivo, 'linha 3');
  8    utl_file.put_line(v_arquivo, 'linha 4');
  9    utl_file.put_line(v_arquivo, 'linha 5');
 10    utl_file.fclose(v_arquivo);
 11  exception
 12    when utl_file.invalid_path then
 13       raise_application_error(-20000, 'Atenção! O diretório informado não existe.');
 14  end;
 15  /

Procedimento PL/SQL concluído com sucesso.

-- Lendo o conteúdo do arquivo de texto gerado
SQL> set serveroutput on
SQL> declare
  2    v_linha varchar2(2000) := '';
  3    v_arquivo utl_file.file_type;
  4  begin
  5    v_arquivo := utl_file.fopen('ARQUIVO_DIR', 'arq_teste.txt', 'R');
  6    loop begin
  7      utl_file.get_line(v_arquivo, v_linha);
  8      dbms_output.put_line('linha lida=' || v_linha);
  9      exception
 10        when no_data_found then
 11        utl_file.fclose(v_arquivo);
 12        exit;
 13      end;
 14    end loop;
 15  end;
 16  /
linha lida=linha 1
linha lida=linha 2
linha lida=linha 3
linha lida=linha 4
linha lida=linha 5

Procedimento PL/SQL concluído com sucesso.

Caso seja informado um objeto DIRECTORY que não exista, a mensagem de erro abaixo será emitida:


SQL> declare
  2    v_arquivo utl_file.file_type;
  3  begin
  4    v_arquivo := utl_file.fopen('ARQUIVO_DIR_ERRO', 'arq_teste.txt', 'W');
  5    utl_file.put_line(v_arquivo, 'linha 1');
  6    utl_file.put_line(v_arquivo, 'linha 2');
  7    utl_file.put_line(v_arquivo, 'linha 3');
  8    utl_file.put_line(v_arquivo, 'linha 4');
  9    utl_file.put_line(v_arquivo, 'linha 5');
 10    utl_file.fclose(v_arquivo);
 11  exception
 12    when utl_file.invalid_path then
 13       raise_application_error(-20000, 'Atenção! O diretório informado não existe.');
 14  end;
 15  /
declare
*
ERRO na linha 1:
ORA-20000: Atenção! O diretório informado não existe.
ORA-06512: em line 13

Como parte de outra demonstração, irei agora criar uma stored procedure de banco de dados que carregará um arquivo binário (imagem.jpg) para dentro de uma tabela de teste. Para isso, vou criar uma coluna do tipo de dado BLOB e fazer uso de alguns sub-programas disponíveis na package DBMS_LOB. No mais, irei executar os procedimentos abaixo:


-- Criando o usuário de teste
SQL> create user scott identified by tiger
  2  default tablespace users
  3  quota unlimited on users;

Usuário criado.

-- Concedendo privilégios básicos
SQL> grant create session,create table, create procedure to scott;

Concessão bem-sucedida.

-- Concedendo privilégios para uso do diretório
SQL> grant read, write on directory ARQUIVO_DIR to scott;

Concessão bem-sucedida.

Na saída do SQL abaixo, podemos ver que o usuário SCOTT possui os privilégio READ (leitura) e WRITE (escrita) para o objeto directory ARQUIVO_DIR:


SQL> select grantor,grantee,table_name,privilege
  2  from
  3  all_tab_privs
  4  where table_name = 'ARQUIVO_DIR';

GRANTOR             GRANTEE              TABLE_NAME             PRIVILEGE
------------------- -------------------- ---------------------- ----------------------
SYS                 SYSTEM               ARQUIVO_DIR            EXECUTE
SYS                 SYSTEM               ARQUIVO_DIR            READ
SYS                 SYSTEM               ARQUIVO_DIR            WRITE
SYSTEM              SCOTT                ARQUIVO_DIR            READ
SYSTEM              SCOTT                ARQUIVO_DIR            WRITE

-- Conectando com o usuário SCOTT
SQL> connect scott/tiger
Conectado.

-- Criando a tabela de teste
SQL> create table tab_imagem (
  2    id number primary key,
  3    nome varchar2(20),
  4    imagem blob
  5  );

Tabela criada.

-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rw-r--r-- 1 oracle oinstall 809 Dez 5 13:25 imagem.jpg

-- Criando a stored procedure carrega_imagem
SQL> create or replace procedure carrega_imagem (p_nome_arquivo in tab_imagem.nome%type) as
  2   v_bfile bfile;
  3   v_blob blob;
  4  begin
  5    insert into tab_imagem (id,nome,imagem)
  6    values (1,p_nome_arquivo,empty_blob())
  7    return imagem into v_blob;
  8    v_bfile := bfilename('ARQUIVO_DIR',p_nome_arquivo);
  9    dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
 10    dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile));
 11    dbms_lob.fileclose(v_bfile);
 12    commit;
 13  end;
 14  /

Procedimento criado.

-- Carregando o arquivo para a tabela tab_imagem
SQL> execute carrega_imagem('imagem.jpg');

Procedimento PL/SQL concluído com sucesso.

-- Verificando o tamanho (em bytes) do arquivo carregado
SQL> select dbms_lob.getlength(imagem) from tab_imagem;

DBMS_LOB.GETLENGTH(IMAGEM)
--------------------------
                       809

SQL> exit
Desconectado de Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Aproveitando que utilizei função DBMS_LOB.GETLENGTH acima, tem muita gente que acha que o valor retornado é a quantidade de caracteres, o que não é verdade. O valor retornado nada mais é do que o tamanho do arquivo em bytes que foi carregado do sistema operacional, conforme mostrado abaixo:


[oracle@linux tmp]$ ls -l
total 4
-rw-r--r-- 1 oracle oinstall 809 Dez 5 13:25 imagem.jpg

Para finalizar, como mencionado no início do artigo, o SQL*Plus das versões anteriores ao Oracle 11g, não permitiam que o usuário visualizasse o conteúdo de um campo do tipo de dado BLOB:


-- Utilizando o SQL*Plus da versão 10g
C:\>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Sab Dez 5 13:28:19 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab_imagem;
SP2-0678: Coluna ou tipo de atributo não pode ser exibido pelo SQL*Plus

Na versão 11g do SQL*Plus, agora é possível visualizarmos o conteúdo de um campo do tipo de dados BLOB como demonstrado abaixo. Contudo, a única vantagem que eu vejo em relação ao SQL*Plus das versões anteriores é de que não precisaremos mais remover as colunas do tipo de dado BLOB na hora de emitirmos um comando SELECT, embora que, visualizar dados binários não faz lá muito sentido ...


-- Utilizando o SQL*Plus da versão 11g
C:\>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Sab Dez 5 13:32:06 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab_imagem;

        ID NOME             IMAGEM
---------- ---------------- ------------------------------------------------------------
         1 imagem.jpg       47494638396109000600F70000000000B1B1B1AFAFAFA3A3A39999997777
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            27CCCCCC191919171717111111C0C0C00D0D0D090909070707AAAAAAA4A4
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            58FFFFFF4848484444444040403838383232322E2E2EDDDDDD2828282626
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            B30000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            000000000000000000000000000000000000000000000000000000000000
                            262424242222221E1E1E181818141414121212101010BBBBBB080808B3B3
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            A49696968A8A8A8888887070706868686666666060605E5E5E5C5C5C5858
                            777171716B6B6B5555554B4B4B3F3F3F3D3D3DEEEEEE3B3B3B3333332727
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            000000000000000000000000000000000000000000000000000000000000
                            002C000000000900060000080E0049081C48B0A0C1830813260C08003B00

Realizando downloads do site da Oracle com o WGET ...

2009-11-07T17:39:00.006-02:00

Olá,

Recentemente, a Oracle colocou uma mensagem em seu servidor FTP (updates.oracle.com) informando que não será mais possível baixar atualizações (Patches & Updates) através do mesmo e que, as atualizações, só poderão ser baixadas diretamente do site My Oracle Support (antigo Metalink) como mostrado abaixo:

Connected to updates.oraclegha.com.
220 FTP server ready.
Name (updates.oracle.com:legatti): legatti
331 Username OK, please send password.
Password: *********
230-
230- Welcome to the Oracle Patch Download FTP Server
230-
230-*************************************************************************
230- This service will be retired on November 06, 2009 and "My Oracle Support"
230- will be the only interface for downloading the patches and updates. To
230- search and download patches from "My Oracle Support" users are required
230- to login to https://metalink.oracle.com and then go to "Patches & Updates"
230- tab.
230-
230- Alternatively users can download patches using command line download
230- manager utilities like "wget".
230-
230- Examples on how to use wget:
230-
230- - Check "wget" command is available OR install it
230- - Login to "My Oracle Support" on any browser
230- - Search for the patch
230- - Go to the download page
230- - Right Mouse Click on the Download Button
230- - Click on the properties
230- - Copy the address from the properties
230- - Run the following command using your metalink login and password
230-
230- wget --http-user= --http-password=
230- --output-document=
230- "Paste the above copied address here in quotes"
230-
230- For example, the wget command for patch# 6880880, for Linux Platform
230-
230- wget -http-user=john.doe@oracle.com -http-password=xxxxxxxx --output-document=p6880880_102000_LINUX.zip "http://updates.oracle.com/ARULink/Download/process_form/p6880880_102000_LINUX.zip?aru=11447016&file_id=23740960&patch_file=p6880880_102000_LINUX.zip&email=john.doe@oracle.com&userid=04394&"
230-
230-*************************************************************************
230-
230- For detailed help, use command "quote site help".
230
Remote system type is UNIX.
Using binary mode to transfer files.
ftp>

De acordo com a mensagem, uma outra alternativa será a de usar o utilitário wget que é um programa livre que propicia o download de dados da web muito utilizado em sistemas Linux/Unix, e que já foi portado para diversos ambientes como Mac OS X, Microsoft Windows e OpenVMS.

Mas, e quanto aos softwares disponíveis na seção de downloads do site Oracle? Bom, também poderemos utilizar o wget para baixá-los sem que tenhamos que utilizar o navegador para isso. Já que precisamos informar o nosso login e senha para baixar-mos o software em questão, isso não será um problema pois, com o wget, poderemos informar também os dados de login.

Abaixo está uma pequena demonstração da utilização do wget do qual farei uso para baixar o Oracle Database 11g do site da Oracle. No mais, para ambientes Windows, será necessário baixar o utilitário que poderá ser encontrado aqui.

C:\>wget --no-check-certificate -vv --post-data="globalId=&redirectUrl=http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_1of2.zip&username=[seu_usuario]&password=[sua_senha]&submit=Continue" https://profile.oracle.com/jsp/reg/loginHandler.jsp

--2009-11-07 17:30:08-- https://profile.oracle.com/jsp/reg/loginHandler.jsp
Resolving profile.oracle.com... 141.146.8.116
Connecting to profile.oracle.com|141.146.8.116|:443... connected.
WARNING: cannot verify profile.oracle.com's certificate, issued by '/C=US/O=VeriSign, Inc./OU=VeriSign Trust Network/OU=Terms of use at https://www.verisign.com/rpa (c)05/CN=VeriSign Class 3 Secure Server CA':
Unable to locally verify the issuer's authority.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_1of2.zip [following]
--2009-11-07 17:30:16-- http://download.oracle.com/otn/linux/oracle11g/R2/linux.x64_11gR2_database_1of2.zip
Resolving download.oracle.com... 208.111.133.53, 208.111.133.54
Connecting to download.oracle.com|208.111.133.53|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1239269270 (1.2G) [application/zip]
Saving to: 'linux.x64_11gR2_database_1of2.zip'


 9% [====>                                     ] 75,519,546  110K/s  eta 3h 12m

Programa de Certificação da Oracle lança o portal CertView

2009-10-28T21:55:00.006-02:00

Olá,

No início deste mês (Outubro/2009), a equipe do programa de certificação da Oracle lançou um sistema, ou melhor, um portal para todos aqueles profissionais, detentores  ou não, de alguma certificação da Oracle. O nome deste portal é o CertView e pode ser acessado através do endereço http://certview.oracle.com.

Bom, só agora estou abordando este assunto pelo fato de apenas recentemente, eu ter conseguido logar com sucesso no portal. Enfim, foram algumas semanas enviando e-mails para a equipe do programa de certificação da Oracle através do e-mail ocpexam_ww@oracle.com, até receber uma resposta informando que o meu acesso havia sido autorizado.

No mais, o CertView nos dará de forma online a capacidade de:

Visualizarmos nosso histórico de exames de certificação realizados
Visualizarmos nossas informações demográficas (endereço, cidade, país, etc...)
Baixar os logos autorizados e oficiais referentes aos nossos certificados
Visualizarmos nosso histórico de certificações
Obtermos informações sobre o status do nosso kit certificação que é enviado pela Oracle
Enviarmos informações sobre a nossa certificação à terceiros

Em relação a esta última, o interessante é que poderemos enviar por e-mail para qualquer pessoa, uma autorização para que a mesma visualize informações sobre a nossa certificação. Ao receber o e-mail, o destinatário poderá clicar em um link privado e fornecer uma chave (código) contendo a autorização para visualização das informações dos certificados. Esta autorização é enviada pelo próprio portal CertView ao e-mail do destinatário em questão. Vale a pena salientar que o link e a chave de autorização possuem uma data de validade, ou seja, ambas expiram em 5 dias à partir do recebimento do e-mail pelo destinatário.

Vale a pena salientar que o CertView está disponível gratuitamente a todos os candidatos certificados ou não, e poderá ser acessado usando uma conta Oracle Single Sign On (SSO) cadastrada previamente.

Para tal, o candidato precisará fornecer suas informações demográficas à Pearson VUE, a nova empresa parceira da Oracle e provedora dos exames de certificação. Como todos já sabem, a Prometric não é mais a provedora dos exames da Oracle.

Para finalizar, abaixo está um vídeo de boas vindas com mais informações sobre o novo portal CertView.

Gerando backups físicos de banco de dados com o RMAN através de um shell script no Linux ...

2009-10-06T11:20:00.002-03:00

Olá,

No artigo anterior eu disponibilizei um script feito em "perl" para geração de backup lógico (FULL export) do banco de dados através dos utilitários de exportação (exp/expdp). Neste artigo irei disponibilizar um shell script para geração de backup físico através do utilitário RMAN (Recovery Manager). Para quem já conhece o RMAN, não precisa nem dizer que o mesmo é um utilitário usado no gerenciamento de operações de backup e na restauração e recuperação de bancos de dados Oracle.

Apesar do OEM Database Console (10g/11g) disponibilizar de forma simples e fácil uma interface para gerenciamento, configuração e agendamento de backups físicos do banco de dados, um script para geração de backups, em alguns casos, poderá ser bem útil.

No mais, o shell script (ou script em shell) demonstrado logo mais abaixo, tem como base o mesmo script fornecido pela instalação do Oracle Express Edition para Linux, mas com algumas alterações realizadas por mim de forma que sejam realizadas algumas operações tais como, backup completo do banco de dados juntamente com os arquivos de redo log arquivados (caso o banco esteja no modo ARCHIVELOG), deleção destes mesmos arquivos de redo log arquivados (após a realização do backup, deleção de backups obsoletos (dependendo da política de retenção utilizada), remoção de arquivos de backup expirados, geração do arquivo de inicialização em modo texto (pfile), geração de arquivo de log com o resultado dos comandos realizados pelo RMAN, entre outros.

Vale a pena salientar que este script foi testado nas versões do Oracle 10g e 11g e funcionará supondo que apenas uma instância de banco de dados esteja em execução tendo o arquivo de controle (control file) como repositório do RMAN, e que a Flash Recovery Area (FRA) esteja habilitada. O script também detecta o modo de operação do banco de dados alvo (ARCHIVELOG ou NOARCHIVELOG), pois para bancos de dados operando no modo NOARCHIVELOG, é necessário que seja realizado o shutdown do mesmo antes da realização do backup. Após a realização do backup, o banco de dados será reaberto. Para bancos de dados operando no modo ARCHIVELOG, o backup será realizado de forma online.

Dependendo da política de retenção utilizada, os backups dos arquivos de banco de dados serão armazenados na Flash Recovery Area e dois arquivos textos de log serão criados para armazenar os procedimentos realizados. Os arquivos de log serão criados em $HOME/log_rman_ultimo.log e $HOME/log_rman_anterior.log conforme demonstração logo mais abaixo. O script deverá ser executado com algum usuário de sistema operacional membro do grupo "dba", de preferência o usuário "oracle" e, poderá também quando necessário, ser agendado a sua execução através do utilitário de sistema operacional "cron".


-- Script de backup para uso do utilitário RMAN
#!/bin/sh
#
# Este script assume que o usuário pode se conectar no banco de dados usando a sintaxe
# "/ as sysdba" e que a Flash Recovery Area esteja habilitada.
#
# =============================
# Procedimento de Backup - RMAN
# =============================
#
# Para bancos de dados operando no modo NOARCHIVELOG, será realizado o shutdown do banco
# de dados antes da realização do backup. Após a realização do backup, o banco de dados 
# será reaberto.
#    
# Para bancos de dados operando no modo ARCHIVELOG, o backup será realizado de forma
# online. Dependendo da política de retenção utilizada, os backups serão armazenados na
# Flash Recovery Area e dois arquivos textos de log serão criados para armazenar os 
# procedimentos realizados. Os arquivos de log serão criados em 
# $HOME/log_rman_ultimo.log e $HOME/log_rman_anterior.log

user=`/usr/bin/whoami`
group=`/usr/bin/groups $user | grep dba`
if test -z "$group"; then
   echo A operação falhou. $user não é membro do grupo \'dba\'.
   exit 0
fi

TMPDIR=/tmp

rman_backup_current=$HOME/log_rman_ultimo.log
rman_backup_prev=$HOME/log_rman_anterior.log

#Criando um log temporário para a execução
rman_backup=${TMPDIR}/rman_backup$$.log
echo 'Log de Backup do RMAN' > $rman_backup

#Verificando se a flash recovery area está habilitada
rman_fra=${TMPDIR}/rman_fra$$.log
sqlplus /nolog > $rman_fra <<>
   connect / as sysdba;
   set head off;
   set echo off;
   set trimspool on;
   set linesize 512;
   select '$' || count(*) || '$' from v\$parameter
    where upper(name)='DB_RECOVERY_FILE_DEST'
      and value is not null;
EOF
fra=`grep "^$.*$" $rman_fra`
rm -f $rman_fra

if [ X$fra = X\$1\$ ]; then
   failed=false;
else
   failed=true
   errstr="A área de recuperação flash não está habilitada"
fi;

#Verificando a localização da flash recovery area
rman_fralocation=${TMPDIR}/rman_fralocation$$.log
sqlplus /nolog > $rman_fralocation <<>
   connect / as sysdba;
   set head off;
   set echo off;
   set trimspool on;
   set linesize 512;
   select '$' || value || '$' from v\$parameter
    where upper(name)='DB_RECOVERY_FILE_DEST'
      and value is not null;
EOF
fralocation=`grep "^$.*$" $rman_fralocation`
fralocation=${fralocation#*$}
fralocation=${fralocation%"$"*}
rm -f $rman_fralocation

#Verificando o nome da instância do banco de dados
rman_instancename=${TMPDIR}/rman_instancename$$.log
sqlplus /nolog > $rman_instancename <<>
   connect / as sysdba;
   set head off;
   set echo off;
   set trimspool on;
   set linesize 512;
   select '$' || instance_name || '$' from v\$instance;
EOF
instancename=`grep "^$.*$" $rman_instancename`
instancename=${instancename#*$}
instancename=${instancename%"$"*}
rm -f $rman_instancename

if [ $failed = 'false' ] ; then
   #Verificando o modo de operação do banco de dados
   rman_log_mode=${TMPDIR}/rman_log_mode$$.log
   sqlplus /nolog > $rman_log_mode <<>
      connect / as sysdba;
      set head off;
      set echo off;
      set trimspool on;
      set linesize 512;
      select '$' || log_mode || '$' from v\$database;
EOF
   mode=`grep "^$.*$" $rman_log_mode`
   rm -f $rman_log_mode

   case $mode in
      \$ARCHIVELOG\$)
      echo "Realizando Backup Online (ARCHIVELOG) do Banco de Dados..."
      rman target / >> $rman_backup <<>
         set echo on;
         show all;
         report schema;
         sql "create pfile=''$fralocation/init$instancename.ora'' from spfile";
         crosscheck archivelog all;
         sql 'alter system archive log current';
         backup full database plus archivelog delete all input;
         crosscheck backup;
         delete noprompt expired backup;
         delete noprompt obsolete;
EOF
      if [ $? = 0 ]; then
         failed=false;
      else
         failed=true
         errstr="RMAN error: Veja o arquivo de log para mais detalhes"
      fi;
      ;;

      \$NOARCHIVELOG\$)
      echo "Realizando Backup Offline (NOARCHIVELOG) do Banco de Dados..."

      rman target / >> $rman_backup <<>
         set echo on; 
         show all;
         report schema;
         sql "create pfile=''$fralocation/init$instancename.ora'' from spfile";
         shutdown immediate;
         startup mount;
         backup as backupset device type disk database;
         alter database open;
         crosscheck backup;
         delete noprompt expired backup;
         delete noprompt obsolete;
EOF
      if [ $? = 0 ]; then
         failed=false;
      else
         failed=true
         errstr="RMAN error: Veja o arquivo de log para mais detalhes"
      fi;
      ;;
   
      *)
      errstr="Modo de Banco de dados desconhecido: $mode"
      failed=true;
      ;;
   esac;
fi;

#Gravando o erro no arquivo de log
if [ $failed = 'true' ]; then
   echo ${errstr}. >> $rman_backup
fi;

#Renomeando o último arquivo de log como anterior
if [ -f $rman_backup_current ]; then
   mv -f $rman_backup_current $rman_backup_prev
fi;

#Gravando o arquivo de log
mv -f $rman_backup $rman_backup_current

#Mostrando o resultado para o usuário em caso de erro
if [ $failed = 'true' ] ; then
   echo '===================== ERRO ========================='
   echo '         Falha de backup de banco de dados          '
   echo '===================== ERRO ========================='
   echo ${errstr}.
   echo O arquivo de log está em $rman_backup_current
else
   echo Backup do banco de dados realizado com sucesso.
   echo O arquivo de log está em $rman_backup_current
fi

O arquivo bkpora_rman criado abaixo contém o shell script acima que realizará o backup físico do banco de dados.


[oracle@linux u01]$ ls -l
total 44
drwxrwx--- 3 oracle dba  4096 Set 30 15:14 app
drwxr-xr-x 2 oracle dba 20480 Set 30 15:46 archive
drwxr-xr-x 2 oracle dba  4096 Out  3 14:14 export
drwxr-xr-x 3 oracle dba  4096 Out  5 14:22 flash
-rwxr--r-- 1 oracle dba  5406 Out  6 08:12 bkpora_rman

Como já dito anteriormente, caso o usuário que esteja executando o script não for membro do grupo "dba", então o erro abaixo será emitido:


[oracle@linux u01]$ su -
Senha: ******
[root@linux ~]# cd /u01
[root@linux u01]# ./bkpora_rman
A operação falhou. root não é membro do grupo 'dba'.

Bom, voltando ao usário "oracle", irei realizar a execução do script shell como demonstrado abaixo. A propósito, caso a Flash Recovery Area não esteja habilitada, então o erro abaixo será emitido:


[oracle@linux u01]$ ./bkpora_rman
===================== ERRO =========================
         Falha de backup de banco de dados
===================== ERRO =========================
A área de recuperação flash não está habilitada
O arquivo de log está em /home/oracle/log_rman_ultimo.log

No mais, irei checar algumas informações do banco de dados alvo antes de executar o script de backup:


[oracle@linux ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Ter Out 6 08:29:12 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

-- Verificando se a FRA está habilitada
SQL> show parameter db_recovery

NAME                           TYPE        VALUE
------------------------------ ----------- -------------------
db_recovery_file_dest          string      /u01/flash
db_recovery_file_dest_size     big integer 30G

-- Verificando o modo de operação do banco de dados
SQL> archive log list
Modo log de banco de dados     Modo de Arquivamento
Arquivamento automático        Ativado
Destino de arquivamento        /u01/archive/
A seqüência de log on-line mais antiga 7
Próxima seqüência de log a arquivar    9
Seqüência de log atual                 9

Após finalizada a verificação de algumas das informações acima, irei executar o script de backup como demonstrado abaixo:


[oracle@linux u01]$ ./bkpora_rman
Realizando Backup Online (ARCHIVELOG) do Banco de Dados...
Backup do banco de dados realizado com sucesso.
O arquivo de log está em /home/oracle/log_rman_ultimo.log

Após a execução do backup, irei checar as informações do arquivo de log criado.


[oracle@linux ~]$ ls -l $HOME/*.log
-rw-r--r-- 1 oracle oinstall 6755 Out 6 08:32 /home/oracle/log_rman_ultimo.log


[oracle@linux u01]$ cat /home/oracle/log_rman_ultimo.log
Log de Backup do RMAN

Gerenciador de Recuperação: Release 10.2.0.4.0 - Production on Ter Out 6 08:36:59 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

conectado ao banco de dados de destino: BD01 (DBID=2949935972)

RMAN>
eco ativado

RMAN> show all;
usar o arquivo de controle do banco de dados de destino em vez do catálogo de recuperação
os parâmetros de configuração RMAN são:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$ORACLE_HOME/dbs/snapcf_BD01.f'; # default

RMAN> report schema;
Relatório do esquema do banco de dados

Lista de Arquivos de Dados Permanentes
======================================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------------------------
1    300      SYSTEM               ***     /u01/app/oracle/oradata/BD01/system01.dbf
2    200      UNDOTBS1             ***     /u01/app/oracle/oradata/BD01/undotbs01.dbf
3    120      SYSAUX               ***     /u01/app/oracle/oradata/BD01/sysaux01.dbf
4    21       USERS                ***     /u01/app/oracle/oradata/BD01/users01.dbf

Lista de Arquivos Temporários
=============================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- ---------------------------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/BD01/temp01.dbf

RMAN> sql "create pfile=''/u01/flash/initBD01.ora'' from spfile";
instrução sql: create pfile=''/u01/flash/initBD01.ora'' from spfile

RMAN> crosscheck archivelog all;
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=147 devtype=DISK
validação bem-sucedida no log arquivado
nome do arquivo de log de arquivamento=/u01/archive/1_4_686230057.dbf id reg.=111 marc...
validação bem-sucedida no log arquivado
nome do arquivo de log de arquivamento=/u01/archive/1_5_686230057.dbf id reg.=112 marc...
validação bem-sucedida no log arquivado
nome do arquivo de log de arquivamento=/u01/archive/1_6_686230057.dbf id reg.=113 marc...
validação bem-sucedida no log arquivado
nome do arquivo de log de arquivamento=/u01/archive/1_7_686230057.dbf id reg.=114 marc...
validação bem-sucedida no log arquivado
nome do arquivo de log de arquivamento=/u01/archive/1_8_686230057.dbf id reg.=115 marc...
validação bem-sucedida no log arquivado
nome do arquivo de log de arquivamento=/u01/archive/1_9_686230057.dbf id reg.=116 marc...
Fez a verificação cruzada de 6 objetos

RMAN> sql 'alter system archive log current';
instrução sql: alter system archive log current

RMAN> backup full database plus archivelog delete all input;

Iniciando backup em 06/10/2009
log atual arquivado
utilizando o canal ORA_DISK_1
canal ORA_DISK_1: iniciando conjunto de backups de log de arquivamento
canal ORA_DISK_1: especificando log(s) de arquivamento no conjunto de backups
log de arquivamento thread de entrada=1 seqüência=4 id reg.=111 marcação=698859945
log de arquivamento thread de entrada=1 seqüência=5 id reg.=112 marcação=698859947
log de arquivamento thread de entrada=1 seqüência=6 id reg.=113 marcação=698859951
log de arquivamento thread de entrada=1 seqüência=7 id reg.=114 marcação=698859957
log de arquivamento thread de entrada=1 seqüência=8 id reg.=115 marcação=698859963
log de arquivamento thread de entrada=1 seqüência=9 id reg.=116 marcação=698927352
log de arquivamento thread de entrada=1 seqüência=10 id reg.=117 marcação=698929025
log de arquivamento thread de entrada=1 seqüência=11 id reg.=118 marcação=698929025
canal ORA_DISK_1: iniciando o componente 1 em 06/10/2009
canal ORA_DISK_1: componente 1 finalizado em 06/10/2009
handle de componente=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG20091006T1057...
canal ORA_DISK_1: conjunto de backups concluído, tempo decorrido: 00:00:16
canal ORA_DISK_1: deletando log(s) de arquivamento
nome do arquivo de log de arquivamento=/u01/archive/1_4_686230057.dbf id reg.=111 marc...
nome do arquivo de log de arquivamento=/u01/archive/1_5_686230057.dbf id reg.=112 marc...
nome do arquivo de log de arquivamento=/u01/archive/1_6_686230057.dbf id reg.=113 marc...
nome do arquivo de log de arquivamento=/u01/archive/1_7_686230057.dbf id reg.=114 marc...
nome do arquivo de log de arquivamento=/u01/archive/1_8_686230057.dbf id reg.=115 marc...
nome do arquivo de log de arquivamento=/u01/archive/1_9_686230057.dbf id reg.=116 marc...
nome do arquivo de log de arquivamento=/u01/archive/1_10_686230057.dbf id reg.=117 mar...
nome do arquivo de log de arquivamento=/u01/archive/1_11_686230057.dbf id reg.=118 mar...
Finalizado backup em 06/10/2009

Iniciando backup em 06/10/2009
utilizando o canal ORA_DISK_1
canal ORA_DISK_1: iniciando conjunto de backup completo de arquivo de dados
canal ORA_DISK_1: especificando arquivo(s) de dados no conjunto de backups
fno=00001 name=/u01/app/oracle/oradata/BD01/system01.dbf do arquivo de dados de entrada
fno=00002 name=/u01/app/oracle/oradata/BD01/undotbs01.dbf do arquivo de dados de entrada
fno=00003 name=/u01/app/oracle/oradata/BD01/sysaux01.dbf do arquivo de dados de entrada
fno=00004 name=/u01/app/oracle/oradata/BD01/users01.dbf do arquivo de dados de entrada
canal ORA_DISK_1: iniciando o componente 1 em 06/10/2009
canal ORA_DISK_1: componente 1 finalizado em 06/10/2009
handle de componente=/u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG20091006T1057...
canal ORA_DISK_1: conjunto de backups concluído, tempo decorrido: 00:04:05
Finalizado backup em 06/10/2009

Iniciando backup em 06/10/2009
log atual arquivado
utilizando o canal ORA_DISK_1
canal ORA_DISK_1: iniciando conjunto de backups de log de arquivamento
canal ORA_DISK_1: especificando log(s) de arquivamento no conjunto de backups
log de arquivamento thread de entrada=1 seqüência=12 id reg.=119 marcação=698929289
canal ORA_DISK_1: iniciando o componente 1 em 06/10/2009
canal ORA_DISK_1: componente 1 finalizado em 06/10/2009
handle de componente=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG20091006T1101...
canal ORA_DISK_1: conjunto de backups concluído, tempo decorrido: 00:00:05
canal ORA_DISK_1: deletando log(s) de arquivamento
nome do arquivo de log de arquivamento=/u01/archive/1_12_686230057.dbf id reg.=119 mar...
Finalizado backup em 06/10/2009

Iniciando Control File and SPFILE Autobackup em 06/10/2009
handle de componente=/u01/flash/BD01/autobackup/2009_10_06/o1_mf_s_698929294_5d6s61ws_...
Finalizado Control File and SPFILE Autobackup em 06/10/2009

RMAN> crosscheck backup;
utilizando o canal ORA_DISK_1
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG200...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG200...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG200...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/autobackup/2009_10_06/o1_mf_s_698929294...
Fez a verificação cruzada de 4 objetos

RMAN> delete noprompt expired backup;
utilizando o canal ORA_DISK_1

RMAN> delete noprompt obsolete;
a política de retenção RMAN será aplicada ao comando
a política de retenção RMAN está definida para a redundância 1
utilizando o canal ORA_DISK_1
Deletando os seguintes backups e cópias obsoletas:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ ------------------------------------------
Conjunto de Backups  61     06/10/2009
Parte de Backup      61     06/10/2009         /u01/flash/BD01/backupset/2009_10_06/o1...
componente de backup deletado
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG200...
Deletou 1 objetos

RMAN>

Recovery Manager completo.

Irei realizar outro teste só que, agora, alterando o modo de operação do banco de dados para NOARCHIVELOG.


[oracle@linux u01]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Ter Out 6 08:45:12 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Banco de dados fechado.
Banco de dados desmontado.
Instância ORACLE desativada.

SQL> startup mount
Instância ORACLE iniciada.

Total System Global Area  155189248 bytes
Fixed Size                  1266320 bytes
Variable Size             113249648 bytes
Database Buffers           37748736 bytes
Redo Buffers                2924544 bytes
Banco de dados montado.

SQL> alter database noarchivelog;

Banco de dados alterado.

SQL> alter database open;

Banco de dados alterado.

SQL> archive log list
Modo log de banco de dados   Modo Sem Arquivamento
Arquivamento automático      Desativado
Destino de arquivamento      /u01/archive/
A seqüência de log on-line mais antiga     11
Seqüência de log atual                     13

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Apenas para fins de demonstração, caso eu tente realizar o backup do banco de dados sem antes realizar o shutdown do mesmo, o RMAN irá emitir um erro como demonstrado abaixo:


[oracle@linux ~]$ rman target /

Gerenciador de Recuperação: Release 10.2.0.4.0 - Production on Ter Out 6 09:10:49 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

conectado ao banco de dados de destino: BD01 (DBID=2949935972)

RMAN> backup database;

Iniciando backup em 06/10/2009
usar o arquivo de controle do banco de dados de destino em vez do catálogo de recuperação
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=147 devtype=DISK
canal ORA_DISK_1: iniciando conjunto de backup completo de arquivo de dados
canal ORA_DISK_1: especificando arquivo(s) de dados no conjunto de backups
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: falha do comando backup no canal de ORA_DISK_1 em 06/10/2009 09:11:12
ORA-19602: não pode copiar ou fazer backup de arquivo ativo no modo NOARCHIVELOG

RMAN> exit

Recovery Manager completo.

Como mencionado anteriormente, o script de backup irá detectar o modo de operação do banco de dados, então o mesmo automaticamente irá realizar o shutdown da instância caso o banco de dados esteja configurado para operar no modo NOARCHIVELOG e, após a realização do backup, o script irá realizar o startup da instância e abertura do banco de dados.


[oracle@linux u01]$ ./bkpora_rman
Realizando Backup Offline (NOARCHIVELOG) do Banco de Dados...
Backup do banco de dados realizado com sucesso.
O arquivo de log está em /home/oracle/log_rman_ultimo.log

-- Verificando o arquivo de log gerado
[oracle@linux u01]$ cat /home/oracle/log_rman_ultimo.log
Log de Backup do RMAN

Gerenciador de Recuperação: Release 10.2.0.4.0 - Production on Ter Out 6 09:23:56 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

conectado ao banco de dados de destino: BD01 (DBID=2949935972)

RMAN>
eco ativado

RMAN> show all;
usar o arquivo de controle do banco de dados de destino em vez do catálogo de recuperação
os parâmetros de configuração RMAN são:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '$ORACLE_HOME/dbs/snapcf_BD01.f'; # default

RMAN> report schema;
Relatório do esquema do banco de dados

Lista de Arquivos de Dados Permanentes
======================================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------------------------
1    300      SYSTEM               ***     /u01/app/oracle/oradata/BD01/system01.dbf
2    200      UNDOTBS1             ***     /u01/app/oracle/oradata/BD01/undotbs01.dbf
3    120      SYSAUX               ***     /u01/app/oracle/oradata/BD01/sysaux01.dbf
4    21       USERS                ***     /u01/app/oracle/oradata/BD01/users01.dbf

Lista de Arquivos Temporários
=============================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- ---------------------------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/BD01/temp01.dbf

RMAN> sql "create pfile=''/u01/flash/initBD01.ora'' from spfile";
instrução sql: create pfile=''/u01/flash/initBD01.ora'' from spfile

RMAN> shutdown immediate;
banco de dados fechado
banco de dados desmontado
encerramento de instância Oracle

RMAN> startup mount;
conectado ao banco de dados de destino (não iniciado)
instância Oracle iniciada
banco de dados montado

Total da Área Global do Sistema     155189248 bytes

Fixed Size                     1266320 bytes
Variable Size                113249648 bytes
Database Buffers              37748736 bytes
Redo Buffers                   2924544 bytes

RMAN> backup as backupset device type disk database;
Iniciando backup em 06/10/2009
canal alocado: ORA_DISK_1
canal ORA_DISK_1: sid=157 devtype=DISK
canal ORA_DISK_1: iniciando conjunto de backup completo de arquivo de dados
canal ORA_DISK_1: especificando arquivo(s) de dados no conjunto de backups
fno=00001 name=/u01/app/oracle/oradata/BD01/system01.dbf do arquivo de dados de entrada
fno=00002 name=/u01/app/oracle/oradata/BD01/undotbs01.dbf do arquivo de dados de entrada
fno=00003 name=/u01/app/oracle/oradata/BD01/sysaux01.dbf do arquivo de dados de entrada
fno=00004 name=/u01/app/oracle/oradata/BD01/users01.dbf do arquivo de dados de entrada
canal ORA_DISK_1: iniciando o componente 1 em 06/10/2009
canal ORA_DISK_1: componente 1 finalizado em 06/10/2009
handle de componente=/u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG20091006T1118...
canal ORA_DISK_1: conjunto de backups concluído, tempo decorrido: 00:04:29
Finalizado backup em 06/10/2009

Iniciando Control File and SPFILE Autobackup em 06/10/2009
handle de componente=/u01/flash/BD01/autobackup/2009_10_06/o1_mf_s_698930288_5d6tfssw_...
Finalizado Control File and SPFILE Autobackup em 06/10/2009

RMAN> alter database open;
banco de dados aberto

RMAN> crosscheck backup;
utilizando o canal ORA_DISK_1
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG200...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG200...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/autobackup/2009_10_06/o1_mf_s_698929294...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG200...
componente de backup submetido a verificação cruzada: localizado como 'AVAILABLE'
handle de componente de backup=/u01/flash/BD01/autobackup/2009_10_06/o1_mf_s_698930288...
Fez a verificação cruzada de 5 objetos

RMAN> delete noprompt expired backup;
utilizando o canal ORA_DISK_1

RMAN> delete noprompt obsolete;
a política de retenção RMAN será aplicada ao comando
a política de retenção RMAN está definida para a redundância 1
utilizando o canal ORA_DISK_1
Deletando os seguintes backups e cópias obsoletas:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ ------------------------------------------
Conjunto de Backups  62     06/10/2009
Parte de Backup      62     06/10/2009         /u01/flash/BD01/backupset/2009_10_06/o1...
Conjunto de Backups  63     06/10/2009
Parte de Backup      63     06/10/2009         /u01/flash/BD01/backupset/2009_10_06/o1...
Conjunto de Backups  64     06/10/2009
Parte de Backup      64     06/10/2009         /u01/flash/BD01/autobackup/2009_10_06/o...
componente de backup deletado
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG200...
componente de backup deletado
handle de componente de backup=/u01/flash/BD01/backupset/2009_10_06/o1_mf_annnn_TAG200...
componente de backup deletado
handle de componente de backup=/u01/flash/BD01/autobackup/2009_10_06/o1_mf_s_698929294...
Deletou 3 objetos

RMAN>

Recovery Manager completo.

Após finalizado o backup físico, poderemos checar através do RMAN as informações sobre o backup que foi realizado.


[oracle@linux flash]$ rman target /

Gerenciador de Recuperação: Release 10.2.0.4.0 - Production on Ter Out 6 09:35:16 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

conectado ao banco de dados de destino: BD01 (DBID=2949935972)

RMAN> list backup of database;

usar o arquivo de controle do banco de dados de destino em vez do catálogo de recuperação

Lista de Conjuntos de Backup
============================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------------------------
67      Full    289.28M    DISK        00:03:01     06/10/2009
        Chave BP: 67   Status: AVAILABLE  Compactado: NO  Tag: TAG20091006T112920
        Nome do Componente: /u01/flash/BD01/backupset/2009_10_06/o1_mf_nnndf_TAG200910...
  Lista de Arquivos de Dados no conjunto de backup 67
  File LV Type Ckp SCN    Tempo de Verif. Name
  ---- -- ---- ---------- --------------- ------------------------------------------
  1       Full 258379     06/10/2009      /u01/app/oracle/oradata/BD01/system01.dbf
  2       Full 258379     06/10/2009      /u01/app/oracle/oradata/BD01/undotbs01.dbf
  3       Full 258379     06/10/2009      /u01/app/oracle/oradata/BD01/sysaux01.dbf
  4       Full 258379     06/10/2009      /u01/app/oracle/oradata/BD01/users01.dbf

RMAN> list backup summary;

Lista de Backups
================

Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ------------------
67      B  F  A DISK        06/10/2009      1       1       NO         TAG20091006T112920
68      B  F  A DISK        06/10/2009      1       1       NO         TAG20091006T113229


-- Verificando o conteúdo do diretório /u01/flash/
[oracle@linux /]$ cd /u01/flash/
[oracle@linux flash]$ ls -l
total 8
drwxr-x--- 4 oracle dba      4096 Out 6 08:37 BD01
-rw-r--r-- 1 oracle oinstall 1043 Out 6 08:38 initBD01.ora

Por fim, é altamente recomendável copiar os arquivos de backup para um local seguro, logo assim que o procedimento de backup for finalizado.

Gerando backups lógicos de banco de dados no Linux através de um script Perl ...

2009-09-15T13:05:00.002-03:00

Olá,

Antes de mostrar o script em "perl" para geração de backups lógicos do Oracle no Linux, através dos utilitários de exportação fornecidos junto com o banco de dados Oracle, irei repetir o que já foi comentado nos artigos de Outubro/2008 e Dezembro/2008: O desenvolvimento de um plano de backup e recuperação é extremamente importante para um servidor de banco de dados. É lógico que, dependendo do caso, um plano adequado de backup e recuperação não tem que ser necessariamente o mais elaborado e complexo ... na verdade, ele precisa ser aquele que irá servir melhor às necessidades de cada negócio. Em geral os tipos de backups dos bancos de dados Oracle se classificam em três categorias simples:

* Backup do sistem operacional (OS)
* Backups gerenciados pelo RMAN (Recovery Manager)
* Backups lógicos

Em resumo, cada tipo de método de backup tem suas próprias vantagens e desvantagens. O processo de criação de uma boa estratégia de backup e recuperação envolve a avaliação dos méritos e problemas desses diferentes tipos de backups, e sua combinação conforme a necessidade para criar um nível aceitável de proteção efetivo.

Vale a pena salientar que o utilitário de exportação não pode ser considerado como uma ferramenta para criação de um método efetivo de backup. Eu diria que ele possibilita um backup lógico, pelo fato de que não é possível aplicar um histórico de redo log nos objetos importados provenientes de um arquivo de exportação. Um backup lógico de um banco de dados envolve a leitura de um conjunto de registros do banco de dados e a gravação destes em um arquivo. Esses registros são lidos independentes das suas localizações físicas. Acredito que uma estratégia robusta de backup possa incluir tanto backups físicos como lógicos. Em geral, bancos de dados de produção contam com backups físicos como seu principal método de backup e backups lógicos servem como um método secundário. Por outro lado, para bancos de dados de desenvolvimento e para pequenos processamentos de movimentação de dados, os backups lógicos podem ser uma solução aceitável e viável.

Portanto, os arquivos dumps gerados pelo utilitário de exportação podem ser utilizados como um recurso complementar ao backup físico de banco de dados para proteção contra erros de usuário. Por exemplo, um backup lógico pode ser útil quando um usuário elimina ou trunca uma tabela acidentalmente ou quando o DBA precisa restaurar uma tabela que apresente erros lógicos, ou uma operação qualquer que tenha afetado somente um subconjunto do banco de dados. Neste caso, um dump de exportação atualizado seria uma alternativa mais rápida e menos traumática do que realizar uma recuperação incompleta de banco de dados, seja ela gerenciada pelo usuário ou através do recovery manager (RMAN). Em todo caso, e dependendo do cenário, isso dependerá muito da versão Oracle utilizada pelo fato de a partir do Oracle 10g, nós já termos a proteção da lixeira (recyclebin) para segmentos que foram dropados e inclusive recursos da tecnologia flashback.

No mais, abaixo existem dois scripts escritos em "perl" que realizam o backup lógico (FULL export) do banco de dados. Eu digo dois porque, o primeiro, faz uso do utilitário de exportação tradicional (exp) para uso no caso de versões Oracle anteriores ao Oracle 10g e, o segundo, faz uso do utilitário Export Data Pump (expdp) para uso nas versões à partir do Oracle 10g.

Em resumo, o script irá gerar o arquivo dump de exportação, compactá-lo através de um utilitário fornecido pelo sistema operacional, renomeá-lo com informações de data, hora e minuto de criação e, por fim, realizar a exclusão de arquivos dump de exportação (compactados) antigos, ou seja, somente os 20 últimos arquivos gerados serão mantidos. Utilizarei o "cron" do linux para agendar a execução diária do script, como demonstrarei mais abaixo:

-- Verificando se o pacote perl está instalado
[root@linux ~]# rpm -q perl
perl-5.8.8-10.el5_0.2

Afim de não expor a senha do usuário DBA de banco de dados SYSTEM no script, irei criar como demonstrado abaixo, um usuário de banco de dados com os privilégios necessários para a realização da exportação FULL.

SQL> create user adm identified by adm
  2  default tablespace users
  3  quota unlimited on users;

Usuário criado.

SQL> grant create session,create table,exp_full_database to adm;

Concessão bem-sucedida.

Vale a pena salientar que o privilégio de sistema "create table" concedido ao usuário ADM se faz necessário para uso do utilitário Export Data Pump. A única configuração necessária para se realizar no script será o de informar, através da variável $destinoBackup, o local onde deverão ser gerados os backups lógicos do banco de dados. Para quem for utilizar o Export Data Pump (expdp), esta variável deverá ter o mesmo caminho que foi definido no objeto "directory".

-- Script para uso do utilitário exp
#!/usr/bin/perl -w
$destinoBackup = '/backup/oracle/exp';
opendir($destinoBackup,'.') || die "O diretorio de backup($destinoBackup) não existe";
print "Iniciando backup lógico...\n";
system("su - oracle -c \"exp adm/adm file=$destinoBackup/bkpfull.dmp full=y\"");
print "Compactando backup...\n";
system("gzip $destinoBackup/bkpfull.dmp");
@time = localtime(time);
$dataHora = (1900+$time[5])."\-".($time[4]+1)."\-$time[3]_$time[2]-$time[1]h";
system("mv $destinoBackup/bkpfull.dmp.gz $destinoBackup/bkpfull_$dataHora.dmp.gz");
system("find $destinoBackup -mtime +20 -name \"*.dmp.gz\" -exec rm {} ".'\;');

Para uso do Export Data Pump, irei criar o objeto "directory" como demonstrado abaixo:

SQL> create directory exp_dir as '/backup/oracle/expdp';

Diretório criado.

SQL> grant read,write on directory exp_dir to adm;

Concessão bem-sucedida.

-- Script para uso do utilitário expdp
#!/usr/bin/perl -w
$destinoBackup = '/backup/oracle/expdp';
opendir($destinoBackup,'.') || die "O diretorio de backup($destinoBackup) não existe";
print "Iniciando backup lógico...\n";
system("su - oracle -c \"expdp adm/adm directory=exp_dir dumpfile=bkpfull.dmp full=y\"");
print "Compactando backup...\n";
system("gzip $destinoBackup/bkpfull.dmp");
@time = localtime(time);
$dataHora = (1900+$time[5])."\-".($time[4]+1)."\-$time[3]_$time[2]-$time[1]h";
system("mv $destinoBackup/bkpfull.dmp.gz $destinoBackup/bkpfull_$dataHora.dmp.gz");
system("find $destinoBackup -mtime +20 -name \"*.dmp.gz\" -exec rm {} ".'\;');

Independente do utilitário de exportação escolhido, irei mover o script de backup para o diretório /usr/bin e criar uma entrada no "crontab" de forma a agendar a execução do mesmo como, por exemplo, todos os dias às 22:00. Se porventura ocorrer de algum usuário vir a utilizar o banco de dados durante o horário de realização do backup, então seria prudente adicionar a cláusula CONSISTENT=Y ao comando do utilitário de exportação tradicional (exp).

[root@linux ~]# cd /usr/bin
[root@linux ~]# ls -l bkpora
-rwxr-xr-x 1 oracle dba 651 Set 15 08:20 bkpora

[root@linux ~]# crontab -l
00 22 * * * bkpora

Abaixo está um exemplo de execução do script na qual utilizei o Export Data Pump como utilitário de exportação:


[root@linux ~]# ./bkpora
Iniciando backup lógico...

Export: Release 10.2.0.1.0 - Production on Terça-Feira, 15 Setembro, 2009 08:25:57

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Conectado a: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Iniciando "ADM"."SYS_EXPORT_FULL_01":  adm/******** directory=exp_dir
dumpfile=bkpfull.dmp full=y
Estimativa em andamento com o método BLOCKS...
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 29.93 MB
Processando o tipo de objeto DATABASE_EXPORT/TABLESPACE
Processando o tipo de objeto DATABASE_EXPORT/SYS_USER/USER
Processando o tipo de objeto DATABASE_EXPORT/SCHEMA/USER
Processando o tipo de objeto DATABASE_EXPORT/ROLE
.
.
.
Conjunto de arquivos de dump para ADM.SYS_EXPORT_FULL_01 é:
  /backup/oracle/expdp/bkpfull.dmp
O job "ADM"."SYS_EXPORT_FULL_01" foi concluído com sucesso em 08:28:16

Compactando backup...
[root@linux ~]#

prod-web:~ # cd /backup/oracle/expdp
prod-web:/backup/oracle/expdp # ls -lh
total 13M
drwxr-xr-x  2 oracle oinstall 4,0K 2009-09-15 08:10 .
drwxr-xr-x  9 oracle oinstall 4,0K 2009-09-15 08:09 ..
-rw-r-----  1 oracle dba       13M 2009-09-15 08:28 bkpfull_2009-9-15_08-28h.dmp.gz
-rw-r--r--  1 oracle dba       23K 2009-08-26 08:28 export.log

No mais, ao longo dos dias, será possível visualizar os arquivos gerados como demonstrado no exemplo abaixo, mas, sempre que for possível eu recomendaria movê-los para um local seguro.

[root@linux ~]# ls -lhatr
total 3,8G
drwxr-xr-x 4 root   root 4,0K Mai 27 22:05 ..
-rw-r--r-- 1 oracle dba  182M Jul 31 22:05 bkpfull_2009-7-31_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  1 22:05 bkpfull_2009-8-1_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  2 22:05 bkpfull_2009-8-2_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  3 22:04 bkpfull_2009-8-3_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  4 22:05 bkpfull_2009-8-4_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  5 22:05 bkpfull_2009-8-5_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  6 22:04 bkpfull_2009-8-6_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  7 22:05 bkpfull_2009-8-7_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  8 22:05 bkpfull_2009-8-8_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago  9 22:05 bkpfull_2009-8-9_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  183M Ago 10 22:05 bkpfull_2009-8-10_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  183M Ago 11 22:05 bkpfull_2009-8-11_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 12 22:05 bkpfull_2009-8-12_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 13 22:05 bkpfull_2009-8-13_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 14 22:05 bkpfull_2009-8-14_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 15 22:05 bkpfull_2009-8-15_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 16 22:05 bkpfull_2009-8-16_22-7h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 17 22:05 bkpfull_2009-8-17_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 18 22:05 bkpfull_2009-8-18_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  182M Ago 19 22:05 bkpfull_2009-8-19_22-6h.dmp.gz
-rw-r--r-- 1 oracle dba  183M Ago 20 22:05 bkpfull_2009-8-20_22-6h.dmp.gz

Seja bem-vindo Pearson VUE .... Adeus Prometric ....

2009-09-01T13:27:00.007-03:00

Olá,

De acordo com a nota de Paul Sorensen, à partir de 25 de setembro de 2009, os exames para certificação não serão mais oferecidos pela Prometric. A Oracle e a equipe do programa de certificação está em discussão com um novo parceiro que substituirá a atual provedora dos exames da Oracle, a Prometric. Segundo outras fontes, esta empresa seria a Pearson VUE. No mais, os candidatos serão capazes de se registrarem na Prometric até o dia 13 de setembro de 2009, embora os exames ainda possam ser realizados até o dia 25.

Os candidatos poderão ser registrar através do site da Pearson VUE à partir de do dia 14. Assim que for disponibilizado, convém acessar o link www.pearsonvue.com/oracle e, quanto a questões e dúvidas em geral, recomendo acessar a nota do blog do Programa de certificação da Oracle.

Em meio a este anúncio e, aproveitando este post, foi disponibilizado para download no site da Oracle, o tão aguardado Oracle Database 11g Release 2 para as plataformas Linux x86/Linux x86-64.

Anunciado a aposentadoria dos exames de certificação OCP Oracle 8 e 8i

2009-08-25T09:13:00.010-03:00

Olá,

Estava lendo o Oracle Certification blog de Paul Sorensen, e resolvi compartilhar a informação aqui. Segundo o post, à partir de 25 setembro de 2009, os exames (1Z0-010) Oracle8: New Features for Administrators e (1Z0-020) Oracle8i: New Features for Administrators serão descontinuados (aposentados), ou seja, não estarão mais disponíveis como opções de exames para a obtenção de certificação OCP.

O exame 1Z0-010 foi criado para aquele profissional que é OCP Oracle 7.3 e que deseja atualizar sua certificação para a versão do Oracle 8. Já o exame 1Z0-020, foi criado aquele profissional que é OCP Oracle 8 e que deseja fazer a atualização da sua certificação para a versão Oracle 8i. Portanto, para quem ainda tem interesse em realizar esses dois exames, os mesmos ainda poderão ser realizados até o dia 25 de setembro de 2009.

No mais, para os profissionais que detém a certificação OCP Oracle 7.3 e/ou OCP Oracle 8, os mesmos ainda terão a opção de atualização de suas certificações para a versão OCP Oracle 9i através do exame 1Z0-035 DBA Oracle 9i New Features for Oracle7.3 e OCPs Oracle8.

Introdução ao tipo de dado TIMESTAMP e suas variações ...

2009-08-17T09:36:00.008-03:00

Olá,

Sabemos que o tipo de dado DATE do Oracle é um tipo especial capaz de armazenar datas que vão de 4712 a.C. a 9999 d.C, mas além de armazenar informações de século, ano, mês e dia, o mesmo também é capaz de armazenar informações de hora, minuto e segundo. Mas aí você poderia perguntar: Se o tipo de dado DATE é capaz de manter essas informações temporais, isso não poderia ser considerado um TIMESTAMP? Não necessariamente. O tipo de dados TIMESTAMP introduzido à partir do Oracle 9i, vai muito mais além do que simplesmente armazenar de forma "crua" informações de data e horários. Podemos dizer que o tipo de dado TIMESTAMP é uma extensão do tipo de dados DATE, capaz de manter informações de tempo com maior precisão. No mais, neste artigo farei uma breve introdução a este tipo de dado e suas variações (WITH TIME ZONE e WITH LOCAL TIME ZONE) que, muitas vezes, é fonte de dúvida entre desenvolvedores e administradores de banco de dados quanto à sua aplicação e uso.

Antes de começar a descrever sobre o tipo de dados TIMESTAMP, irei iniciar através do exemplo abaixo, como poderíamos verificar informações de século, data e horários atuais, selecionando-as direto do banco de dados através do uso da função SYSDATE:


SQL> select to_char(to_date(0,'J'), 'dd/mm/yyyy ad') from dual;
select to_char(to_date(0,'J'), 'dd/mm/yyyy ad') from dual
                       *
ERRO na linha 1:
ORA-01854: data juliana deve estar entre 1 e 5373484

SQL> select
  2  to_char(to_date(1,'J'), 'dd/mm/yyyy ad') de,
  3  to_char(to_date(5373484,'J'), 'dd/mm/yyyy ad') ate
  4  from dual;

DE              ATE
--------------- ---------------
01/01/4712 a.C. 31/12/9999 d.C.

SQL> select to_char(sysdate,'cc dd/mm/yyyy hh24:mi:ss') data from dual;

DATA
----------------------
21 17/08/2009 08:10:25

Talvez você também já tenha ouvido falar da função CURRENT_DATE. A diferença entre ela e o SYSDATE é que a função SYSDATE retorna a data e horário do servidor, enquanto que a função CURRENT_DATE retorna a data e horário de acordo com o fuso horário da sessão do usuário. Para comprovar tal explicação, no exemplo abaixo irei alterar o fuso horário da minha sessão para o fuso horário de Nova York nos EUA que é '-5:00'. No servidor Oracle, o fuso horário está definido em '-3:00', ou seja, três horas de atraso em relação à hora de Greenwich.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Sessão alterada.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------
-03:00

SQL> alter session set time_zone='-5:00';

Sessão alterada.

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
----------------
-05:00

SQL> select sysdate,current_date from dual;

SYSDATE             CURRENT_DATE
------------------- -------------------
17/08/2009 08:15:45 17/08/2009 06:15:45

Podemos perceber acima que SYSDATE retornou o horário do servidor, e CURRENT_DATE o horário de acordo com o fuso horário definido na minha sessão.

Bom, voltando ao assunto que originou este artigo e, como já dito anteriormente, o tipo de dados TIMESTAMP introduzido à partir do Oracle 9i é uma extensão do tipo de dados DATE. Além de armazenar o ano, mês, dia, horas, minutos e segundos do tipo de dados DATE, o mesmo é capaz também de armazenar o valor das frações de segundo. A precisão dessas frações, como o próprio nome já diz, nada mais é do que o número de dígitos da parte fracionária dos "segundos". Esta precisão pode ser um número de 0 a 9, mas quando não especificado, o seu valor padrão é 6.

SQL> create table t1 (ts_data timestamp);

Tabela criada.

SQL> desc t1
 Nome                         Nulo?    Tipo
 ---------------------------- -------- ------------------------
 TS_DATA                               TIMESTAMP(6)


SQL> insert into t1 values (localtimestamp);

1 linha criada.

SQL> alter session set nls_timestamp_format = 'dd/mm/yyyy hh24:mi:ss.ff';

Sessão alterada.

SQL> select * from t1;

TS_DATA
--------------------------
05/08/2009 08:20:32.627869

Além do exemplo demonstrado acima, existem ainda duas variações do tipo de dados TIMESTAMP:

O tipo TIMESTAMP WITH TIME ZONE é uma variante de TIMESTAMP que inclui um deslocamento de fuso horário (TIME ZONE) em seu valor. Podemos dizer que o deslocamento de fuso horário em questão é a diferença em horas e minutos entre o horário local e o UTC. UTC é um acrônimo do Inglês para "Coordinated Universal Time" ou "Tempo Universal Coordenado" que corresponde ao fuso horário de referência a partir do qual se calculam todas as outras zonas horárias do mundo. Segundo algumas bibliografias, o UTC é o sucessor do Tempo Médio de Greenwich (Greenwich Mean Time), abreviadamente mais conhecido como GMT. Esta nova denominação foi cunhada para basear a medida do tempo nos padrões atômicos, mais do que nos celestes. Em resumo, dois valores TIMESTAMP WITH TIME ZONE serão considerados idênticos se representarem o mesmo instante em UTC, independente dos deslocamentos de fusos horários (TIME ZONE) armazenados nos dados. Confuso? Bom, para que não haja dúvidas, poderemos ver abaixo um exemplo que demonstra uma informação de data e horário que deverá ser reunida ou coordenada entre diferentes regiões geográficas com fusos horários distintos:

Por exemplo:

TIMESTAMP '17/08/2009 08:00:00 -3:00'

é o mesmo que

TIMESTAMP '17/08/2009 06:00:00 -5:00'

Isto é, 08:00 horas da manhã no horário de Brasília é o mesmo que 06:00 horas da manhã no horário de Nova York nos EUA.

O outro tipo, TIMESTAMP WITH LOCAL TIME ZONE é outra variante de TIMESTAMP que inclui um deslocamento de fuso horário em seu valor. A diferença é que este deslocamento não é armazenado como parte dos dados da coluna, mas sim, normalizado para o fuso horário do banco de dados (DBTIMEZONE). O verbo "normalizar", neste caso, eu entendo como um cálculo que é realizado tendo como base o fuso horário definido no banco de dados afim de se mostrar o horário local do usuário. Portanto, o Oracle retornará a informação de data, hora, minuto e segundo no fuso horário local da sessão que está acessando o dado. Vale a pena salientar que o uso desta variante é apropriado para aplicações onde se deseja exibir as datas e os horários usando o fuso horário do sistema cliente. Para quem já acessou algum tipo fórum ou lista de discussão de nível global, notará que ao postar algum tópico no fórum, a data e horário mostrada será convertida para o fuso horário local. Caso um leitor que esteja no Japão acessar o tópico, verá a data e horário de acordo com o fuso horário do Japão. Então, utilizando como base o exemplo anterior:

Supondo que eu esteja no Brasil (fuso horário -3:00) e insira a data e horário abaixo:

TIMESTAMP '17/08/2009 08:00:00'

e um usuário que esteja em Nova York (fuso horário -5:00) acessar o mesmo dado, a data apresentada para este usuário será

TIMESTAMP '17/08/2009 06:00:00'

No mais, para demonstrar o uso do tipo TIMESTAMP e suas duas variações, irei realizar algumas operações abaixo. Vale a pena salientar que o Oracle possui além das funções SYSDATE e CURENT_DATE, outras funções embutidas para capturar informações de data e horários atuais como, LOCALTIMESTAMP, CURRENT_TIMESTAMP e SYSTIMESTAMP onde:

LOCALTIMESTAMP: retorna um valor do tipo TIMESTAMP como data e horário corrente (incluindo frações de segundos), de acordo com o fuso horário definido na sessão do usuário.

CURRENT_TIMESTAMP: retorna um valor do tipo TIMESTAMP WITH TIME ZONE como data e horário corrente (incluindo frações de segundos), de acordo com o fuso horário definido na sessão do usuário.

SYSTIMESTAMP: retorna um valor do tipo TIMESTAMP WITH TIME ZONE como data e horário corrente (incluindo frações de segundos), de acordo com o fuso horário definido no sistema onde o servidor de banco de dados reside.

Para demonstrar o resultado das funções acima, executarei os comandos SQL abaixo:

SQL> alter session set time_zone='-5:00';

Sessão alterada.

SQL> select localtimestamp,current_timestamp,systimestamp from dual;

LOCALTIMESTAMP           CURRENT_TIMESTAMP               SYSTIMESTAMP
------------------------ ------------------------------- -------------------------------
17/08/09 06:20:54,656000 17/08/09 06:20:54,656000 -05:00 17/08/09 08:20:54,656000 -03:00

Podemos perceber pelo resultado acima que LOCALTIMESTAMP e CURRENT_TIMESTAMP apresentaram o mesmo horário, a diferença é que CURRENT_TIMESTAMP trouxe a informação do fuso horário (-5:00). Já a função SYSTIMESTAMP, diferente de CURRENT_TIMESTAMP, trouxe o horário de acordo com o fuso horário definido no sistema do servidor de banco de dados.

Irei agora criar uma tabela de teste conforme abaixo:


SQL> create table t2 (
  2   a timestamp,
  3   b timestamp with time zone,
  4   c timestamp with local time zone
  5  );

Tabela criada.

SQL> desc t2
 Nome                      Nulo?    Tipo
 ------------------------- -------- ---------------------------------
 A                                  TIMESTAMP(6)
 B                                  TIMESTAMP(6) WITH TIME ZONE
 C                                  TIMESTAMP(6) WITH LOCAL TIME ZONE

SQL> insert into t2 values (localtimestamp,localtimestamp,localtimestamp);

1 linha criada.

SQL> select * from t2;

A                        B                               C
------------------------ ------------------------------- ------------------------
17/08/09 08:30:10,250000 17/08/09 08:30:10,250000 -03:00 17/08/09 08:30:10,250000

Independente da função utilizada, as informações de data e horários foram inseridas nas devidas colunas e mostram o mesmo horário. Agora irei realizar através de uma aplicação (no meu caso o SQL*Plus), a simulação do acesso remoto ao banco de dados na qual o usuário está localizado em uma região geográfica onde fuso horário desta região seja diferente como, por exemplo, Nova York nos EUA:


SQL> alter session set time_zone='-5:00';

Sessão alterada.

SQL> select dbtimezone,sessiontimezone from dual;

DBTIMEZONE SESSIONTIMEZONE
---------- ---------------
-03:00     -05:00

SQL> select * from t2;

A                         B                              C
------------------------ ------------------------------- ------------------------
17/08/09 08:30:10,250000 17/08/09 08:30:10,250000 -03:00 17/08/09 06:30:10,250000

De acordo com o resultado acima, podemos notar que os resultados das colunas A e B não se alteraram, ou seja, como o usuário Nova Yorkino não tem conhecimento do fuso horário da região de onde está localizado o banco de dados, o valor da coluna A seria um tanto impreciso. Já o valor da coluna B faria mais sentido pelo fato de apresentar a informação do fuso horário, ou seja, o usuário saberia de forma precisa, o horário em que o dado foi gravado. Por último, podemos perceber que o valor da coluna C foi automaticamente "convertido" para o fuso horário da sessão do usuário que, neste caso é '-5:00'.

Concluindo, o tipo de dado TIMESTAMP WITH LOCAL TIME ZONE é ideal e apropriado para aplicações onde se deseja exibir informações de data e horários usando o fuso horário do sistema cliente.

Oracle mostra sua liderança mais uma vez ...

2009-08-11T13:05:00.005-03:00

Olá,

Bom, não sou eu quem está dizendo isso. Essa notícia chegou a mim através de e-mail, pelo fato da empresa em que trabalho ter uma parceria com a Oracle. Acredito que todos os parceiros também tenham recebido esta notícia da Vice-presidente de Alianças e Canais para Oracle América Latina. Para quem não sabe, a Sra. Sandra Vaz é vice-presidente de Alianças e Canais da Oracle para a América Latina e está na Oracle desde 1997, quando assumiu a gerência de Alianças Globais no Brasil. Desde então, galgou diversas posições, entre as quais a de diretora de Alianças na Oracle do Brasil e de diretora de Alianças e Canais para a América Latina. Na atual posição, que ocupa desde maio de 2005, a executiva é responsável por definir a estratégia da Oracle em toda a região no que tange às iniciativas e cobertura de mercado por meio de parcerias com distribuidores, revendas, integradores, fornecedores de hardware, consultorias internacionais e desenvolvedores independentes de software.

No mais, deixo aqui o conteúdo da "newsletter" que recebi intitulada de "From the desk of Sandra Vaz - Oracle mostra sua liderança mais uma vez":

Caros parceiros,

Gostaria de compartilhar duas novidades importantes: em primeiro lugar, tenho o prazer de anunciar que, pela primeira vez na história, neste segundo trimestre, a Oracle superou amplamente a SAP em ganhos por licença. Após sofrer perdas de 11% de seus ganhos totais, a SAP perdeu 40% dos ganhos por venda de licença.

Enquanto a SAP orgulha-se de ter obtido 4% de ganhos líquidos neste trimestre, sua margem operacional continua decrescendo: nos últimos quatro trimestres a margem operacional da SAP chegou a 26%, uma baixa de dois pontos. No entanto, a margem operacional global da Oracle, apesar da conjuntura econômica internacional desfavorável, segue crescendo. Desta forma, teremos mais argumentos para convencer nossos clientes de que devem escolher a Oracle como principal provedor de tecnologia.

A segunda novidade é outra grande notícia: a Oracle adquiriu a GoldenGate Software, dando um passo estratégico para fortalecer produtos complementares e de padrão compatível. As soluções da GoldenGate capturam e oferecem atualizações de informações críticas em tempo real, além de prover informações contínuas e sincronizadas em ambientes heterogêneos. Também melhoram a replicação de dados e a sincronização entre sistemas heterogêneos, garantindo a disponibilidade de dados contínuos para aplicações de negócios por meio de sistemas múltiplos em tempo real. Ela traz às empresas uma excelente inteligência corporativa, com análises mais precisas e oportunas de rendimento. Com essa aquisição, a Oracle e seus parceiros de negócios contam com a mais veloz e escalável solução de integração de dados em tempo real do mercado, o que complementa o valor e as estratégias de negócios futuros de produtos Oracle.

Como todos sabem, também adquirimos a Sun Microsystems oficialmente, outra empresa pioneira no setor, com quem a Oracle mantinha parcerias há vinte anos. O valor da operação foi de cerca de US$ 7,4 bilhões de dólares. De acordo com Larry Ellison, diretor executivo (CEO) da Oracle, "a aquisição da Sun transforma o setor de TI, combinando um excelente software empresarial com os principais sistemas de informática para a missão dos negócios".

Aproveito a oportunidade para agradecer a todos mais uma vez pela dedicação e inteligência que dedicam para que nossas metas em comum sejam cumpridas no dia-a-dia.

Protegendo e compactando o arquivo dump de exportação com o Oracle Export Data Pump 11g ...

2009-08-03T10:43:00.019-03:00

Olá,

Neste artigo irei comentar um pouco das novas funcionalidades introduzidas no utilitário de exportação Export Data Pump (expdp) do Oracle 11g no que se refere à segurança e compressão dos dados exportados. É fato que os utilitários Data Pump (expdp/impdp) introduzidos à partir do Oracle 10g trouxeram vários recursos e vantagens sobre os utilitários tradicionais (exp/imp) como mais rapidez e flexibilidade nas operações de exportação/importação dos dados, entre outros. Portanto, destaquei alguns dos novos parâmetros adicionados ao utilitário Export Data Pump da versão 11g:

C:\> expdp help=y

Export: Release 11.1.0.6.0 - Production on Segunda-Feira, 03 Agosto, 2009 09:08:45

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Palavra-Chave         Descrição (Default)
--------------------- ------------------------------------------------------------------
COMPRESSION           Reduzir o tamanho do conteúdo do arquivo de dump onde houver 
                      palavra-chave válida. Os valores são: ALL, (METADATA_ONLY), 
                      DATA_ONLY e NONE.
ENCRYPTION            Criptografa parte ou todo o arquivo de dump onde houver a palavra-
                      chave válida. Os valores são: ALL, DATA_ONLY, METADATA_ONLY,
                      ENCRYPTED_COLUMNS_ONLY ou NONE.
ENCRYPTION_ALGORITHM  Especifica como a criptografia deve ser feita onde for válido. Os
                      valores de palavra-chave são: (AES128), AES192 e AES256.
ENCRYPTION_MODE       Método de gerar chave de criptografia onde houver palavra-chave 
                      válida. Os valores são: DUAL, PASSWORD e (TRANSPARENT).
ENCRYPTION_PASSWORD   Chave de senha para criar dados de coluna criptografados. 
REUSE_DUMPFILES       Sobregrava o arquivo de dump de destino, se existir (N).

Em versões anteriores, se quiséssemos proteger um arquivo dump de exportação de forma que somente pessoas autorizadas pudessem ter acesso ao seu conteúdo afim de realizar uma importação, teríamos que "compactar" o arquivo dump utilizando compactadores de terceiros e, através de senha, proteger o mesmo. No entanto, além de não ser uma tarefa muito interessante, mesmo assim isso não impediria que o arquivo compactado fosse "crackeado" através de softwares específicos para estes tipos de compactação. Uma outra forma seria a de exportar os dados utilizando um usuário de banco de dados com privilégios DBA na qual, para importar o arquivo, apenas outro usuário com privilégios DBA ou com privilégios IMP_FULL_DATABASE conseguiria importar o arquivo. Entretanto, nada impediria alguém de simplesmente carregar o arquivo dump para outro banco de dados e, com os privilégios apropriados, importar os dados.

A boa notícia é que à partir do Oracle 11g o utilitário de exportação Export Data Pump (expdp) veio com algumas inovações como o parâmetro ENCRYPTION_PASSWORD e ENCRYPTION_ALGORITHM na qual poderemos simplesmente definir uma senha criptografada que será armazenada no arquivo dump de exportação. Neste caso, a importação do arquivo através do utilitário Import Data Pump (impdp) somente poderá ser realizada diante da digitação da senha que foi definida na criação do arquivo dump de exportação. Vale a pena salientar que na versão do utilitário expdp 10g R2, o parâmetro ENCRYPTION_PASSWORD era aplicado apenas em colunas encriptadas.

Bom, antes de realizar um exemplo prático sobre este recurso, iniciarei o artigo com um outro recurso que foi adicionado ao utilitário de exportação (expdp) chamado COMPRESSION. Com esta opção poderemos definir um nível de compressão não só dos metadados, mas também dos dados. Na versão 10g, por padrão, a compressão é realizada somente no nível de metadados e, na versão 11g, esta compressão foi estendida também para o nível de dados.

Abaixo realizarei alguns testes comparando a taxa de compressão entre as opções disponíveis:


   compression={all | data_only | metadata_only | none}

onde,

* ALL: Ambos os metadados e dados são compactados;
* DATA_ONLY: Somente os dados são compactados.
* METADATA_ONLY: Somente os metadados são compactados. Esta é a opção padrão.
* NONE: Nada é compactado.

Apenas como ilustração, irei exportar um schema de banco de dados na qual atualmente possui aproximadamente 460 MB de dados (entre dados tabelas e índices).


SQL> select sum(bytes)/1024/1024 "TOTAL (MB)" from dba_segments where owner='SCOTT';

TOTAL (MB)
----------
   460,875


-- Utilizando o expdp da versão 11g (sem nenhuma compactação)
C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_compress_none.dmp
     compression=none

-- Utilizando o expdp da versão 10g (compactação padrão)
C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp10.dmp

-- Utilizando o expdp da versão 11g (compactando apenas metadados)
C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_compress_metadata_only.dmp
     compression=metadata_only

-- Utilizando o expdp da versão 11g (compactando apenas dados)
C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_compress_data_only.dmp
     compression=data_only

-- Utilizando o expdp da versão 11g (compactando dados e metadados)
C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_compress_all.dmp
     compression=all

-- Utilizando o exp 11g tradicional
C:\> exp scott/tiger file=exp_traditional.dmp

Abrindo um parênteses aqui, podemos ver abaixo que a estimativa de tamanho para o dump gerado será de aproximadamente 413 MB, bem próximo ao tamanho real dos arquivos de exportação gerados, que não passaram de 395 MB.


Export: Release 11.1.0.6.0 - Production on Segunda-Feira, 03 Agosto, 2009 09:10:35

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Estimativa em andamento com o método BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 413.8 MB

Vamos então aos resultados dos tamanhos dos arquivos dump gerados (em MB) pelas exportações:


[Linux tmp]$ ls -hlatr *.dmp
(1) -rw-r----- 1 oracle oinstall 395M Ago  3 09:12 expdp11_compress_none.dmp
(2) -rw-r----- 1 oracle oinstall 384M Ago  3 09:19 expdp10.dmp
(3) -rw-r----- 1 oracle oinstall 384M Ago  3 09:25 expdp11_compress_metadata_only.dmp
(4) -rw-r----- 1 oracle oinstall 137M Ago  3 09:32 expdp11_compress_data_only.dmp
(5) -rw-r----- 1 oracle oinstall 125M Ago  3 09:39 expdp11_compress_all.dmp
(6) -rw-r----- 1 oracle oinstall 382M Ago  3 09:42 exp_traditional.dmp

De acordo com a listagem acima, a primeira exportação (1) realizada utilizando o expdp da versão 11g com a opção (compression=none) gerou um arquivo dump de 395 MB de tamanho.

A segunda exportação (2) realizada utilizando o expdp da versão 10g gerou um arquivo dump de 384 MB de tamanho, ou seja, o arquivo foi compactado a uma proporção de cerca de 97% em relação ao arquivo dump gerado pela exportação (1) na qual não foi utilizada nenhuma opção de compressão. Como dito anteriormente, apenas os metadados são compactados na versão 10g:

A terceira exportação (3) realizada utilizando o expdp da versão 11g com a opção (compression=metadata_only) gerou um arquivo dump de 384 MB, ou seja, a mesma taxa de compressão que foi utilizada com o utilitário expdp da versão 10g.

A quarta exportação (4) realizada utilizando o expdp da versão 11g com a opção (compression=data_only) gerou um arquivo dump de 137 MB, ou seja, o arquivo foi compactado a uma proporção de cerca de 35% em relação ao arquivo dump gerado pela exportação (3), onde foi utilizada a opção (compression=metadata_only).

A quinta exportação (5) realizada utilizando o expdp da versão 11g com a opção (compression=all) gerou um arquivo dump de 125 MB, ou seja, o arquivo foi compactado a uma proporção de cerca de 32% em relação ao arquivo dump gerado pela exportação (4), onde foi utilizada a opção (compression=data_only).

Para não deixar o export tradicional (exp) de lado, a sexta exportação (6) foi realizada utilizando o exp da versão 11g que gerou um arquivo dump de 382 MB, ou seja, o arquivo criado teve uma proporção de cerca de 99% em relação ao arquivo dump gerado pela exportação (3), onde foi utilizado o utilitário expdp com a opção (compression=data_only).

Apenas para fins de comparação, irei utilizar um dos compactadores mais conhecidos do mercado para de compactar o arquivo de 384 MB (expdp11_compress_metadata_only.dmp).


[Linux tmp]$ zip expdp11_compress_metadata_only.zip expdp11_compress_metadata_only.dmp
  adding: expdp11_compress_metadata_only.dmp (deflated 69%)

[Linux tmp]$ ls -lh *.zip
-rw-r--r-- 1 oracle oinstall 119M Ago 03 09:45 expdp11_compress_metadata_only.zip

Podemos perceber acima que o arquivo foi compactado a uma proporção de cerca de 31% em relação ao arquivo original, ou seja, praticamente a mesma proporção utilizando a opção (compression=data_only) do utilitário expdp.

Já que utilizei o "zip" porque não não usar o famoso "Winrar"? Fazendo um teste, o arquivo original de 384 MB foi compactado a uma proporção de cerca de 17%, ou seja, o tamanho do arquivo de 384 MB foi reduzido para 65,8 MB.

No geral, e deixando de lado os compactadores de terceiros, podemos perceber uma diferença "gritante" de compressão do arquivo dump de exportação quando utilizamos as opções (data_only) ou (all) do utilitário Export Data Pump 11g.

Protegendo o arquivo dump de exportação com senha

Agora irei comentar um pouco sobre outro recurso muito interessante que, como dito anteriormente, à partir do Oracle 11g, o utilitário de exportação Export Data Pump (expdp) disponibilizou os parâmetros ENCRYPTION_PASSWORD e ENCRYPTION_ALGORITHM na qual poderemos definir de forma "nativa", uma senha criptografada que será armazenada no arquivo dump de exportação. Neste caso, a importação do arquivo através do utilitário Import Data Pump (impdp) somente poderá ser realizada diante da digitação da senha que foi definida na criação do arquivo dump de exportação. Meu foco aqui será demonstrar a utilização do método (modo) PASSWORD.

De acordo com a documentação, o "Data pump encryption" é um recurso relevante apenas para instalações do Oracle 11g Enterprise Edition. Para maiores detalhes sobre os parâmetros abaixo, recomendo acessar a documentação oficial.

O parâmetro ENCRYPTION possui as seguintes opções:


ENCRYPTION = {all | data_only | encrypted_columns_only | metadata_only | none}

O parâmetro ENCRYPTION_PASSWORD possui as seguintes opções de algoritmos para criptografia:

ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

O parâmetro ENCRYPTION_MODE especifica o método para geração da chave de criptografia:

ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

O parâmetro ENCRYPTION_PASSWORD é utilizado para informarmos a senha que será criptografada.

Vale a pena salientar que o valores padrões desses parâmetros dependerão de uma combinação dos mesmos. Se somente o parâmetro ENCRYPTION_PASSWORD for especificado, então o parâmetro ENCRYPTION terá por padrão o valor ALL. Se nenhum dos parâmetros ENCRYPTION e ENCRYPTION_PASSWORD for especificado, então o parâmetro ENCRYPTION terá por padrão o valor NONE. No caso do parâmetro ENCRYPTION_ALGORITHM, seu valor padrão é AES128.

Vamos então a um exemplo prático:

C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_senha.dmp 
     encryption=all 
     encryption_mode=password 
     encryption_password=minhasenha

Após a execução do comando acima o arquivo dump gerado foi criado como demonstrado abaixo:


[Linux tmp]$ ls -hlatr *.dmp
-rw-r----- 1 oracle oinstall 384M Ago  3 09:50 expdp11_senha.dmp

Irei simular agora a importação do arquivo dump para um outro schema de banco de dados. Para isso irei criar o usuário de banco de dados ADAM como demonstrado abaixo:


C:\> sqlplus system/manager

SQL*Plus: Release 11.1.0.6.0 - Production on Seg Ago 3 09:55:06 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user adam identified by jones default tablespace users;

Usuário criado.

SQL> grant connect,resource to adam;

Concessão bem-sucedida.

Após a criação do usuário ADAM no banco de dados, irei realizar a importação do arquivo dump criptografado:


-- Realizando a importação sem informar a senha
C:\> impdp adam/jones DIRECTORY=datapump_dir DUMPFILE=expdp11_senha.dmp

Import: Release 11.1.0.6.0 - Production on Segunda-Feira, 03 Agosto, 2009 09:57:46

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: operação inválida
ORA-39174: A senha de criptografia deve ser fornecida.


-- Realizando a importação especificando uma senha inválida
C:\> impdp adam/jones DIRECTORY=datapump_dir DUMPFILE=expdp11_senha.dmp 
     encryption_password=senhaerrada

Import: Release 11.1.0.6.0 - Production on Segunda-Feira, 03 Agosto, 2009 10:03:55

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: operação inválida
ORA-39176: A senha de criptografia está incorreta.


-- Realizando a importação especificando a senha correta
C:\> impdp adam/jones DIRECTORY=datapump_dir DUMPFILE=expdp11_senha.dmp 
     encryption_password=minhasenha 
     remap_schema=scott:adam

Import: Release 11.1.0.6.0 - Production on Segunda-Feira, 03 Agosto, 2009 10:05:48

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "ADAM"."SYS_IMPORT_FULL_01" carregada/descarregada com sucesso
Iniciando "ADAM"."SYS_IMPORT_FULL_01":  adam/******** DIRECTORY=datapump_dir
DUMPFILE=expdp11_senha.dmp encryption_password=******** remap_schema=scott:adam
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
. . importou "ADAM"."EMP"                        330.6 MB    1352 linhas
. . importou "ADAM"."DEPT"                       9.157 MB   55417 linhas
.
.
.

Para finalizar, o Export Data Pump da versão 11g, trouxe também um novo parâmetro chamado REUSE_DUMPFILES que poderá ser utilizado para que possamos sobrescrever um arquivo dump gerado anteriormente sem a necessidade de ter que exclui-lo manualmente como nas releases anteriores (10g R1/R2):


C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_senha.dmp 
     encryption=all 
     encryption_mode=password 
     encryption_password=minhasenha

Export: Release 11.1.0.6.0 - Production on Segunda-Feira, 03 Agosto, 2009 10:09:38

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: valor de argumento inválido
ORA-39000: especificação de arquivo de dump incorreto
ORA-31641: não é possível criar o arquivo de dump "/tmp/expdp11_senha.dmp"
ORA-27038: arquivo criado já existe
Additional information: 1


C:\> expdp scott/tiger DIRECTORY=datapump_dir DUMPFILE=expdp11_senha.dmp 
     encryption=all 
     encryption_mode=password 
     encryption_password=minhasenha 
     reuse_dumpfiles=y

Export: Release 11.1.0.6.0 - Production on Segunda-Feira, 01 Agosto, 2009 10:15:26

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** DIRECTORY=datapump_dir 
DUMPFILE=expdp11_senha.dmp encryption=all encryption_mode=password 
encryption_password=******** reuse_dumpfiles=y
Estimativa em andamento com o método BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o método de BLOCKS: 413.8 MB
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/COMMENT
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/FUNCTION
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processando o tipo de objeto SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER...
Processando o tipo de objeto SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processando o tipo de objeto SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TRIGGER
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exportou "SCOTT"."EMP"                  330.6 MB    1352 linhas
. . exportou "SCOTT"."DEPT"                 9.157 MB   55417 linhas
.
.
.

Oracle anuncia nova estratégia para aplicação de patches - Patch Set Updates (PSU)

2009-07-27T09:39:00.007-03:00

Olá,

Recentemente, a Oracle anunciou (14/07) um novo método para aplicação de correções em seus produtos. Esta nova categoria de pacote de correção se chama Patch Set Update (PSU) e, assim como o Critical Patch Update (CPU), será lançado trimestralmente (Janeiro, Abril, Julho, Outubro).

Este novo pacote de correção além de incluir o último CPU,  também será cumulativo. De acordo com o Oracle My Support (Metalink), o primeiro PSU foi lançado para o Oracle 10g (10.2.0.4) e, para o Oracle 11g, a previsão de lançamento do primeiro PSU será em Outubro/2009.

Como dito anteriormente, o PSU será cumulativo, ou seja, cada PSU conterá todas as correções do seu antecessor. Vale a pena salientar que estes pacotes de atualização só estão disponíveis para download através do site Oracle Metalink (My Oracle Support). Portanto, para quem quiser realizar o download das atualizações, terá que ter um contrato de suporte com a Oracle e ter em mãos um código de identificador de suporte válido (Customer Support Identifier – CSI).

Aplicações do PSU:

10.2.0.4.[0] --> 10.2.0.4.[1] --> 10.2.0.4.[2] --> 10.2.0.4.[3] ...

Para maiores informações, acesse as notas referentes ao PSU no site do My Oracle Support (Metalink):  

854428.1 - Introduction to Oracle Patch Set Updates (PSU)
850471.1 - Oracle Announces First Patch Set Update For Oracle Database Release 10.2
854473.1 - Known Issues with this Patch Set Update 10.2.0.4.1
8576156.8 - Bug 8576156 10.2.0.4.1 Patch Set Update (PSU)

Testando algumas das compatibilidade do Postgres Plus Advanced Server com o Oracle

2009-07-15T10:48:00.007-03:00

Olá,

Para quem teve a oportunidade de ler o artigo de Abril de 2009 intitulado de "Mercado de banco de dados mais acirrado ...", pôde perceber que o Postgres Plus Advanced Server (PAS) trouxe de forma embutida (através de um catálogo adicional), algumas funcionalidades compatíveis com o Oracle, como suporte ao Oracle PL/SQL, algumas views de dicionários de banco de dados USER/ALL/DBA_*, Public Synonyms, Sequences (CURRVAL, NEXTVAL), functions, packages, triggers, tipos de dados (CHAR, VARCHAR, VARCHAR2, NUMBER, BLOB, CLOB, DATE), pseudo-coluna ROWNUM, a tabela DUAL, entre algumas outras funcionalidades.

Apesar de não ser um artigo "diretamente" direcionado ao banco de dados Oracle, mas, já que envolve algumas funcionalidades do próprio Oracle, então irei demonstrar alguns testes que timidamente estou começando a realizar, afim de testar tais compatibilidades. Irei utilizar a versão 8.3 R2 (8.3.0.106) do Postgres PAS para realizar algumas operações básicas.

Vale a pena salientar que no caso do PostgreSQL, o mesmo utiliza um catálogo de dicionários de dados próprio (pg_catalog) e um outro catálogo conforme definido pelo padrão ANSI-92 (information_schema), na qual o Oracle não segue. Fazendo uma pequena comparação, um mesmo catálogo de dicionário de dados no Oracle, está sobre propriedade do usuário SYS através de inúmeras views de dicionário de dados. Uma outra forma de se obter informações de metadados no Oracle é utilizando o comando [DESC]RIBE no SQL*Plus ou através da package DBMS_METADATA disponível desde a versão Oracle 9i.

Portanto, no caso do Postgres PAS, foi criado um catálogo chamado de Redwood(sys) para fornecer algumas das views de dicionário de dados do Oracle. Em se tratando de algumas incompatibilidades conhecidas entre o entre o PostgreSQL e o Oracle, posso citar que o pacote de compatibilidade com o Oracle visa estender algumas funcionalidades do Postgres como:

Permitir o uso de views de dicionário de dados USER/ALL/DBA_* mais comuns;
Permitir a execução de códigos escritos em PL/SQL estendendo o uso de stored procedures, entre outros;
Permitir o uso do operador (+) em junções externas (outer joins);
Permitir o uso de comandos como CREATE USER, DROP USER, ALTER USER, entre outros;
Permitir o uso do tipo de dado VARCHAR2 pois no PostgreSQL apenas existe o tipo VARCHAR;
Permitir o uso do tipo de dado NUMBER pois no PostgreSQL apenas existe o tipo NUMERIC;
Permitir o uso do tipo de dado CLOB pois no PostgreSQL apenas existe o tipo TEXT;
Permitir o uso do tipo de dado BLOB pois no PostgreSQL o mesmo é implementado através dos tipos BYTEA e OID;
Permitir suporte à função DECODE, já que PostgreSQL isso é realizado através de expressões CASE;
Permitir o uso da função NVL já que PostgreSQL temos apenas o suporte da função COALESCE;
Permitir o uso das funções SYSDATE e SYSTIMESTAMP no PostgreSQL;
Permitir o uso do operador MINUS já que PostgreSQL implementa o operador EXCEPT;
Permitir o uso da pseudo-coluna ROWNUM já que o PostgreSQL implementa o uso da cláusula LIMIT e OFSET;
Permitir o uso das funções (CURRVAL, NEXTVAL) no uso de seqüências utilizando a mesma sintaxe do Oracle;
Permitir o uso da tabela DUAL já que no PostgreSQL a mesma não é necessária;
entre outras funcionalidades ...

Agora indo para a prática, após instalado e configurado o Postgres PAS, foi criado o banco de dados chamado de BD01 como demonstrado na figura abaixo. A partir de agora, irei utilizar o utilitário EDB*Plus fornecido junto com o Postgres PAS, na qual poderemos perceber mais abaixo, ser nada mais do que um "clone magro" do SQL*Plus ...

-- Conectando com o super-usuário
C:\>edbplus enterprisedb/manager@localhost:5444/bd01
Connected to EnterpriseDB 8.3.0.106 (localhost:5444/bd01) AS enterprisedb

EDB*Plus: Release 8.3 (Build 18)
Copyright (c) 2008-2009, EnterpriseDB Corporation.  All rights reserved.

-- Mostrando o usuário corrente que está logado
SQL> show user
USER is "enterprisedb"


-- Verificando a versão do banco de dados
SQL> select * from v$version;

BANNER
--------------------------------------------------
Oracle Database 10g Edition Release 10.2.0.1.0 - P
PL/SQL Release 10.2.0.1.0 - Production is similar
CORE 10.2.0.1.0 Production is similar

-- Mostrando a data atual
SQL> select to_char(sysdate,'dd/mm/yyyy') from dual;

TO_CHAR
----------
15/07/2009

-- Criando um usuário/schema de teste
SQL> create user scott identified by tiger;

Role created.

-- Concedendo algumas atribuições
SQL> grant connect,resource,dba to scott;

Role granted.

-- Concetando com o usuário SCOTT
SQL> connect scott/tiger@localhost:5444/bd01;
Disconnected from EnterpriseDB Database.
Connected to EnterpriseDB 8.3.0.106 (localhost:5444/bd01) AS scott

-- Mostrando o usuário corrente que está logado
SQL> show user
USER is "scott"

-- Mostrando as opções disponíveis no EDB*Plus
SQL> show all
autocommit      OFF
colsep          " "
define          "&"
echo            OFF
FEEDBACK ON for 6 row(s).
flush           ON
heading         ON
headsep         "|"
linesize        80
newpage         1
null            " "
pagesize        14
pause           OFF
serveroutput    OFF
spool           OFF
sqlcase         MIXED
sqlprompt       "SQL> "
sqlterminator   ";"
suffix          ".sql"
termout         ON
timing          OFF
verify          ON
USER is         "scott"
HOST is         "localhost"
PORT is         "5444"
DATABASE is     "bd01"
VERSION is      "8.3.0.106"

-- Obtendo informações da estrutura da view DBA_USERS
SQL> desc dba_users

 Name                                     Null?    Data Type
 ---------------------------------------- -------- ---------------------------
 USERNAME                                          CLOB
 USER_ID                                           OID
 PASSWORD                                          VARCHAR2(30)
 ACCOUNT_STATUS                                    VARCHAR2(32)
 LOCK_DATE                                         DATE
 EXPIRY_DATE                                       DATE
 DEFAULT_TABLESPACE                                VARCHAR2(30)
 TEMPORARY_TABLESPACE                              VARCHAR2(30)
 CREATED                                           DATE
 PROFILE                                           VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                       VARCHAR2(30)
 EXTERNAL_NAME                                     VARCHAR2(4000)

-- Verificando usuários/schemas disponíveis no banco de dados
SQL> set linesize 80
SQL> select username,password,default_tablespace,account_status from dba_users;

USERNAME             PASSWORD                       DEFAULT_TABLESPACE    ACCOUNT_STATUS
-------------------- ------------------------------ --------------------- --------------
ENTERPRISEDB         md59709ec8c91b5809603a24f09975 pg_default            OPEN
ADAM                 md546d5f32a35e4a038de7d0a7ae3c pg_default            OPEN
SCOTT                md5f5b3b8231a686f681fdee8f66bf pg_default            OPEN

-- Dropando o usuário/schema ADAM
SQL> drop user adam;

Role dropped.

-- Criando uma tabela de teste (T1)
SQL> create table t1 (a number, b varchar2(10), c date, d clob);

Table created.

-- Adicionando uma chave primária à tabela T1
SQL> alter table t1 add constraint pk_t1 primary key (a);

Table altered.

-- Verificando a estrutura da tabela T1
SQL> set linesize 80
SQL> desc t1

 Name                          Null?    Data Type
 ----------------------------- -------- ----------------------
 A                             NOT NULL NUMBER(38)
 B                                      VARCHAR2(10)
 C                                      DATE
 D                                      CLOB

-- Selecionando dados da view USER_CONSTRAINTS
SQL> select schema_name,constraint_name,table_name
  2  from user_constraints
  3  where table_name='T1';

SCHEMA_NAME   CONSTRAINT_NAME    TABLE_NAME
------------- ------------------ -------------------
SCOTT         PK_T1              T1

-- Realizando um procedimento PL/SQL simples
SQL> set serveroutput on
SQL> begin
  2    for i in 1..10
  3    loop
  4     insert into t1 values (i,to_char(sysdate,'mm/yyyy'),sysdate,'teste CLOB');
  5     dbms_output.put_line(i);
  6    end loop;
  7  end;
  8  /
1
2
3
4
5
6
7
8
9
10

EDB-SPL Procedure successfully completed.

SQL> commit;

COMMIT completed.

-- Selecionando registros da tabela T1
SQL> select * from t1 order by a;

         A B          C                             D
---------- ---------- ----------------------------- -----------
         1 07/2009    2009-07-15 10:13:17           teste CLOB
         2 07/2009    2009-07-15 10:13:17           teste CLOB
         3 07/2009    2009-07-15 10:13:17           teste CLOB
         4 07/2009    2009-07-15 10:13:17           teste CLOB
         5 07/2009    2009-07-15 10:13:17           teste CLOB
         6 07/2009    2009-07-15 10:13:17           teste CLOB
         7 07/2009    2009-07-15 10:13:17           teste CLOB
         8 07/2009    2009-07-15 10:13:17           teste CLOB
         9 07/2009    2009-07-15 10:13:17           teste CLOB
        10 07/2009    2009-07-15 10:13:17           teste CLOB

10 rows retrieved.

-- Testando a funcionalidade da pseudo-coluna ROWNUM
SQL> select * from t1 where rownum <=5;

         A B          C                             D
---------- ---------- ----------------------------- ----------
         1 07/2009    2009-07-15 10:13:17           teste CLOB
         2 07/2009    2009-07-15 10:13:17           teste CLOB
         3 07/2009    2009-07-15 10:13:17           teste CLOB
         4 07/2009    2009-07-15 10:13:17           teste CLOB
         5 07/2009    2009-07-15 10:13:17           teste CLOB


SQL> create table t2 as select * from t1;

Table created.

SQL> insert into t2 values (11,'teste',sysdate,'teste CLOB');

1 row INSERTED.

-- Testando a funcionalidade do operador MINUS
SQL> select * from t2
  2  minus
  3  select * from t1;

         A B          C                             D
---------- ---------- ----------------------------- ----------
        11 teste      2009-07-15 10:20:25           teste CLOB

-- Testando a funcionalidade da função DECODE
SQL> select decode(a,1,'um',2,'dois',3,'tres','outro') from t1;

DECODE
--------------------------------------------
um
dois
tres
outro
outro
outro
outro
outro
outro
outro

10 rows retrieved.

-- Testando a funcionalidade da função NVL
SQL> select nvl(null,0) from dual;

       NVL
----------
         0

-- Testando a funcionalidade da função NVL2
SQL> select nvl2(null,0,1) from dual;

       NVL2
-----------
          1

-- Testando a funcionalidade da função TRANSLATE, apesar do PostgreSQL já ter suporte
-- para a mesma. O SQL abaixo foi executado no pgAdmin III pelo fato de eu ainda não
-- ter conseguido habilitar caracteres especiais no EDB*Plus
SQL> select upper (
  2          translate( 'Exceção, Única, Rápido',
  3                     'ÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜáçéíóúàèìòùâêîôûãõëü',
  4                     'ACEIOUAEIOUAEIOUAOEUaceiouaeiouaeiouaoeu'))
  5  from   dual;

UPPER
--------------------------------------------
EXCECAO, UNICA, RAPIDO

SQL> drop table t2;

Table dropped.

-- Testando a compatibilidade em relação à sequências de banco de dados
SQL> create sequence seq_doc increment by 1 start with 1 nomaxvalue minvalue 0 nocache;

Sequence created.

SQL> select seq_doc.nextval from dual;

             NEXTVAL
--------------------
                   1

SQL> select seq_doc.nextval from dual;

             NEXTVAL
--------------------
                   2

SQL> select seq_doc.currval from dual;

             CURRVAL
--------------------
                   2

Agora irei testar a criação de procedimentos armazenados (stored procedures) pois no PostgreSQL esse conceito, e normalmente esses objetos são criados criados como funções (functions)


SQL> create table t3 (id number);

Table created.

SQL> create or replace procedure sp_insere (p_num number) as
  2    begin
  3      declare
  4         v_num number;
  5      begin
  6       v_num := p_num;
  7       insert into t3 values (v_num);
  8      end;
  9    end;
 10  /

Procedure created.

SQL> desc sp_insere

PROCEDURE SP_INSERE
 Argument Name                    Type                   In/Out Default?
 -------------------------------- ---------------------- ------ --------
 P_NUM                            NUMBER                 IN


SQL> exec sp_insere(100);

EDB-SPL Procedure successfully completed.

SQL> select * from t3;

        ID
----------
       100

Em relação ao famoso operador (+) do Oracle para construção de junções externas entre tabelas, e, já que o PostgreSQL suporta apenas o padrão ANSI LEFT/RIGHT JOIN, irei abaixo realiar um teste com o operador (+):


SQL> create table t4 (id number);

Table created.

SQL> create table t5 (id number);

Table created.

SQL> insert into t4 values (1);

1 row INSERTED.

SQL> insert into t4 values (2);

1 row INSERTED.

SQL> insert into t5 values (1);

1 row INSERTED.

-- Testando SQL join padrão ANSI
SQL> select t4.id,t5.id from t4 left outer join t5 on (t4.id = t5.id);

        ID         ID
---------- ----------
         1          1
         2

-- Testando SQL join padrão Oracle com o sinal (+)
SQL> select t4.id,t5.id  from t4,t5 where t4.id = t5.id(+);

        ID         ID
---------- ----------
         1          1
         2

Bom, após a realização de testes de alguns comandos básicos, deparei-me com uma de minhas funções de banco de dados que funciona perfeitamente no Oracle, mas que apresentou problemas ao ser executada no Postgres Plus Advanced Server. Percebi que os procedimentos PL/SQL armazenados no Postgres PAS são validados apenas quando os mesmos são executados. O problema que encontrei está relacionado ao tipo de dado retornado na realização de uma conta matemática como demonstrado abaixo:


-- Realizando o teste no Oracle 10g via SQL*Plus
ORACLE10g> select 2.2*100 VAL from dual;  
   
       VAL  
----------  
       220  
   
-- Realizando o teste no Postgres PAS via EDB*Plus
SQL> select 2.2*100 val from dual;  
  
       VAL  
----------  
     220.0

Portanto, sem entrar em muitos detalhes, a lógica na minha função não espera o resultado com "0" (zeros à direita) e para que seja retornado o mesmo valor retornado pelo Oracle, precisei realizar um CAST explícito como demonstrado abaixo:


SQL> select cast(2.2*100 as integer) val from dual;  
  
        VAL  
-----------  
        220

Apenas para ficar bem claro como isso impactou no resultado de retorno da função de banco de dados, abaixo está um trecho da função na qual o resultado precisaria ser exatamente como este:


-- Realizando no Oracle 10g via SQL*PLUS
ORACLE10g> select lpad(to_char(2.2*100),14,'0') from dual;  

LPAD(TO_CHAR(2  
--------------  
00000000000220

No Postgres Plus Advanced Server o tipo retornado não foi do tipo INTEGER:


-- Realizando no Postgres PAS via EDB*PLUS  
SQL> select lpad(to_char(2.2*100),14,'0') from dual;  
  
LPAD  
--------------------------------------------
000000000220.0

Como demonstrado abaixo, precisei realizar explicitamente um CAST de forma a alterar o tipo de dado retornado para o tipo INTEGER:


-- Realizando no Postgres PAS via EDB*PLUS  
SQL> select lpad((cast(2.2*100 as integer)),14,'0') from dual;  
  
LPAD  
--------------------------------------------
00000000000220  

SQL> exit
Disconnected from EnterpriseDB Database.

No mais, ainda precisarei realizar muitos testes, mas acredito que a compatiblidade atual do Postgres Plus Advanced Server com algumas funcionalidades do Oracle ainda irão me surpreender muito ...

Lançada a primeira versão de produção do Oracle SQL Developer Data Modeler

2009-07-08T16:26:00.003-03:00

Olá,

A equipe de desenvolvimento de ferramentas da Oracle, recentemente anunciou e disponibilizou para download, a primeira versão de produção do Oracle SQL Developer Data Modeler (2.0.0.57.0). Para quem ainda não conhece, o OSDM é uma ferramenta gráfica útil para a construção e modelagem de dados visando facilitar e melhorar a comunicação entre arquitetos e administradores de dados, DBAs, desenvolvedores de aplicações e usuários.

No mais, para quem teve problemas na utilização das releases anteriores, conforme comentários postados no artigo de Novembro de 2008 intitulado de "Lançado o Oracle SQL Developer Data Modeling", vale a pena baixar e conferir esta nova versão.

Abordando o uso de Database Links em conexões heterogêneas. Acessando o PostgreSQL à partir do Oracle ...

2009-07-02T13:36:00.003-03:00

Olá,

Para quem utiliza o serviço heterogêneo de conectividade "heterogeneous services" (hsodbc) para a realização de conexão entre um banco de dados Oracle e um banco de dados não-oracle, perceberá que no Oracle 11g o programa hsodbc foi substituído pelo dg4odbc (Database Gateway for ODBC). Percebi isso quando tentei, para fins de teste, "interligar" um banco de dados Oracle 11g à um banco de dados PostgreSQL. Lembro-me de que alguns anos atrás, não tive maiores problemas ao realizar este tipo de conexão entre um Oracle 10g (instalado em um Suse Linux 32 bits) e um banco de dados PostgreSQL 7x. Naquela época fiz uso das bibliotecas genéricas UNIXODBC. Hoje, utilizando o Oracle 11g em uma plataforma 64 bits, e após ter realizado algumas tentativas mal sucedidas utilizando o UNIXODBC, acabei apelando por fazer o uso de uma versão para avaliação de bibliotecas de terceiros. No mais, neste artigo, irei demonstrar os passos que realizei para acessar um banco de dados PostgreSQL 8.2.4 através de um objeto (database link) criado à partir do Oracle 11g (11.1.0.7) 64 bits instalado em um Linux CentOS release 5.2.

Como eu mencionei o termo (database link), primeiramente irei fazer uma breve apresentação sobre esse objeto:

Um database link (link de banco de dados) é um tipo especial de objeto criado em um esquema de banco de dados Oracle que permite o acesso direto a objetos de outro banco de dados, seja ele Oracle (homogêneo) ou não (heterogêneo).

Um database link geralmente é utilizado para suportar conexões utilizadas freqüentemente em bancos de dados remotos. Este objeto, na verdade, especifica o descritor de conexão a ser utilizado em uma conexão e também pode especificar o nome de usuário a ser conectado ao banco de dados remoto. Vale a pena salientar que, no geral, se utiliza links de banco de dados de forma a criar objetos locais (como views ou sinônimos) que acessam bancos de dados remotos.

Os sinônimos locais para objetos remotos, fornecem transparência de localização para os usuários locais do banco de dados, ou seja, quando um link de banco de dados é referenciado diretamente ou através de um sinônimo ou uma view, a instrução SQL é enviada e executada em uma nova sessão no banco de dados remoto. Ao ser executada, os dados são retornados para a sessão no banco de dados de origem.

Os links de banco de dados podem ser criados como links públicos (por DBA's) tornando os mesmos disponíveis para todos os usuários de banco de dados local ou como links privados. Para a criação deste objeto, se utiliza a sintaxe abaixo:


CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
  [ CONNECT TO
    { CURRENT_USER
    | user IDENTIFIED BY password [ dblink_authentication ]
    }
  | dblink_authentication
  ]
    [ CONNECT TO
      { CURRENT_USER
      | user IDENTIFIED BY password [ dblink_authentication ]
      }
    | dblink_authentication
    ]...
  [ USING connect_string ] ;

Por exemplo, o comando abaixo iria criar um link de banco de dados público chamado DBLINK:


SQL> create public database link DBLINK
  2  connect to SCOTT identified by TIGER 
  3  using 'DB02';

onde, 'DB02' é o nome de serviço de banco dados existente no arquivo TNSNAMES.ORA local do meu servidor Oracle, na qual estão configuradas as informações da localização do servidor de banco de dados remoto.

Para utilizar esse database link, bastaria apenas adicioná-lo como sufixo aos nomes das tabelas como demonstrado na criação da view abaixo:


SQL> create view emp_remoto
  2  as 
  3  select * FROM emp@DBLINK
  4  where cod_dept > 10;

Podemos ver no exemplo acima que a cláusula "FROM" refere-se à EMP@DBLINK, onde, EMP é a tabela de destino, e DBLINK o objeto (database link) que contém as informações "encapsuladas" das informações do banco de dados de destino.

A propósito, no exemplo anterior foi criada uma view de banco de dados (emp_remoto), com o objetivo de simular uma regra de negócio na qual seria limitado os registros que os usuários poderiam recuperar, mas, se nenhuma restrição desse tipo for necessária, então poderíamos criar um sinônimo público como mostrado pelo comando abaixo:


SQL> create public synonym emp_remoto for emp@DBLINK;

Os usuários locais que consultarem o sinônimo público local EMP_REMOTO terão suas consultas automaticamente redirecionadas à tabela EMP do banco de dados remoto e, portanto, de forma transparente.

Irei demonstrar abaixo um exemplo prático na qual criarei um database link em um banco de dados Oracle 10g Express Edition para permitir acesso a um banco de dados Oracle 11g instalado em outro servidor. Observe que passarei a string de conexão diretamente no comando de criação do database link, sem a necessidade de ter definido previamente um serviço TNS de banco de dados, referente à instância Oracle remota no arquivo local TNSNAMES.ORA:


oracle@xe-developer:~> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Qui Jul 02 08:03:52 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> create database link dblink11g
  2  connect to system identified by minhasenha
  3  using '(description = (address = (protocol = tcp)
  4  (host = 11g_production)(port = 1521))
  5  (connect_data = (sid = bdprod)))';

Vínculo de banco de dados criado.

-- Selecionando informações do banco de dados local
SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- --------------------------------------
XE               xe-developer


-- Selecionando informações do banco de dados remoto
SQL> select instance_name,host_name from v$instance@dblink11g;

INSTANCE_NAME    HOST_NAME   
---------------- --------------------------------------
BDPROD           11g_production

Bom, depois dessa breve introdução ao conceito de Database Links, vamos voltar ao assunto que originou este artigo ...

Para início, é bom ficar claro que na versão 11g do banco de dados Oracle, não será mais possível executar o utilitário abaixo como nas versões anteriores:


[oracle@Linux ~]$ hsodbc

Oracle Corporation --- QUINTA-FEIRA   JUL 02 2009 08:17:25.104

Heterogeneous Agent Release 10.2.0.1.0 - Production  Built with
   Driver for ODBC

Caso alguma tentativa de acesso ao programa hsodbc seja realizada utilizando o Oracle 11g, o erro abaixo será retornado:


[oracle@Linux ~]$ hsodbc
-bash: hsodbc: command not found

Portanto, deverá ser utilizado o programa dg4odbc que, inclusive, poderá ser instalado e utilizado em versões anteriores ao Oracle 11g conforme a nota 466228.1 do Oracle Metalink:

"Starting with 11g Oracle now provides a Database Gateway for ODBC for 32bit Linux operating systems. The difference between the older product (Generic Connectivity or sometimes also called HSODBC) is DG4ODBC is now a stand alone product. It is no longer required to have DG4ODBC running on the same machine as the Oracle database. The gateway is certified also for older Oracle releases 9.2.0.8, 10.1.0.5, or 10.2.0.3. But please be aware those pre-11g Oracle databases require a patch to work properly with V11 Gateways."


[oracle@Linux ~]$ dg4odbc

Oracle Corporation --- QUINTA-FEIRA   JUL 02 2009 08:20:35.379

Heterogeneous Agent Release 11.1.0.7.0 - 64bit Production  Built with
   Oracle Database Gateway for ODBC

[oracle@Linux ~]$

Após ter realizado o download das bibliotecas (64 bits) de avaliação do site em questão, irei iniciar a instalação das mesmas:

[root@Linux tmp]# tar xzvf evlinuxx64.tar.Z
autorun.dat
install.mi
unixmi.ksh
etc/lang/
etc/lang/msg.dat
etc/lang/license.txt
etc/lang/useng.msg
etc/lic/
etc/lic/makelica
etc/lic/makeliclx
etc/lic/makelicl
etc/lic/makelich
etc/lic/makelicsx
etc/lic/makelics
linuxx/ICULicense.txt
linuxx/mysqllicense.txt
linuxx/ODBC64FIXES.TXT
linuxx/ODBC64README.TXT
linuxx/scr1
linuxx/tarfiles/
linuxx/tarfiles/teradata.tar
linuxx/tarfiles/mssql.tar
linuxx/tarfiles/oracle.tar
linuxx/tarfiles/sybase.tar
linuxx/tarfiles/mysql.tar
linuxx/tarfiles/oraclewp.tar
linuxx/tarfiles/linuxx.tar
linuxx/tarfiles/greenplum.tar
linuxx/tarfiles/drda.tar
linuxx/tarfiles/postgresql.tar
linuxx/tarfiles/infcl.tar

[root@Linux tmp]# ksh unixmi.ksh
DataDirect Technologies Product Setup is preparing....

English has been set as the installation language.

Log file : /tmp/logfile.9216.1
----------------------------------------------------------------------
DataDirect Technologies Product Setup (DPS)
for UNIX operating systems
----------------------------------------------------------------------

The following operating system has been detected:

LinuxX64
Is this the current operating system on your machine (Y/N) ? [Y]
END USER PRODUCT LICENSE AGREEMENT ("AGREEMENT")

DataDirect EULA Revised 02/12/2009

Enter YES to accept the above agreement : yes
Please enter the following information for proper registration.

In the Key field, enter either EVAL or the Key provided.

Name          :EVAL
Company       :legatti - evaluation
Serial Number :EVAL
Key           :EVAL

You can install drivers for all supported databases or
install a single driver.

Drivers for All Supported Databases includes one driver for each supported database.
In the case of Oracle, DataDirect Connect64 for ODBC 6.0 SP1 has two available drivers
that support the database--a wire protocol driver that does not require any database
client software to connect to the database, and a client-based driver that does require
client software.  By default, the Oracle Wire Protocol driver is installed.

Single Driver allows you to choose one from any of the DataDirect Connect64 for 
ODBC 6.0 SP1 drivers, including the client-based Oracle driver.

If you want to evaluate more than one single driver, you will have the opportunity to do
so after completion of the initial installation.

1.   Drivers for All Supported Databases
2.   Single Driver
Please enter an option [1]:2


1.   DB2 Wire Protocol
2.   Greenplum Wire Protocol
3.   Informix Wire Protocol
4.   MySQL Wire Protocol
5.   Microsoft SQL Server Wire Protocol
6.   Oracle Wire Protocol
7.   PostgreSQL Wire Protocol
8.   Sybase Wire Protocol
9.   Teradata (XE)
10.  Oracle (client)
Please enter driver option:7

You have chosen to eval the PostgreSQL Wire Protocol driver.

To change this information, enter C. Otherwise, press Enter to continue. :

You have chosen to eval the PostgreSQL Wire Protocol driver.

To change this information, enter C. Otherwise, press Enter to continue. :
DataDirect Technologies Product Setup is preparing the installation.
Choose a temporary directory.

Enter the full path to the temporary install directory.[/tmp]:
Checking for available space...

There is enough space.
Extracting files...

Choose a destination directory.
Enter the full path to the install directory.[/opt/Connect64forODBC60]:
Checking for available space...

There is enough space.
Extracting files...

Creating license file......


DataDirect Technologies Product Setup successfully removed all of the temporary files.


Thank you for using DataDirect Technologies products.

Installation ended successfully.

Would you like to install another product (Y/N) ? [Y]N
Thank you for using DataDirect Technologies products.

Installation ended successfully.

Após realizada a instalação das bibliotecas, iniciarei a configuração dos arquivos odbc.ini e odbinst.ini como demonstrado abaixo:


[root@Linux /]# cd /opt/Connect64forODBC60/

-- Arquivo odbc.ini
[root@Linux Connect64forODBC60]# cat odbc.ini
[ODBC Data Sources]
PostgreSQL Wire Protocol=DataDirect 6.0 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=4
InstallDir=/opt/Connect64forODBC60
Trace=0
TraceFile=odbctrace.out
TraceDll=/opt/Connect64forODBC60/lib/odbctrac.so

[PostgreSQL Wire Protocol]
Driver=/opt/Connect64forODBC60/lib/ddpsql24.so
Description=DataDirect 6.0 PostgreSQL Wire Protocol
ApplicationUsingThreads=1
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=bdlegatti
EnableDescribeParam=1
EncryptionMethod=0
ExtendedColumnMetadata=0
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchRefCursor=1
FetchTSWTZasTimestamp=0
FetchTWFSasTime=0
HostName=10.10.10.2
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=15
LogonID=postgres
MaxPoolSize=100
MinPoolSize=0
Password=postgres
Pooling=0
PortNumber=5432
QueryTimeout=0
ReportCodepageConversionErrors=0
TransactionErrorBehavior=1
ValidateServerCertificate=1
XMLDescribeType=-10


-- Arquivo odbcinst.ini
[root@Linux Connect64forODBC60]# cat odbcinst.ini
[ODBC Drivers]
DataDirect 6.0 PostgreSQL Wire Protocol=Installed

[ODBC Translators]
OEM to ANSI=Installed

[Administrator]
HelpRootDirectory=/opt/Connect64forODBC60/adminhelp

[ODBC]
#This section must contain values for DSN-less connections
#if no odbc.ini file exists. If an odbc.ini file exists,
#the values from that [ODBC] section are used.

[DataDirect 6.0 PostgreSQL Wire Protocol]
Driver=/opt/Connect64forODBC60/lib/ddpsql24.so
Setup=/opt/Connect64forODBC60/lib/ddpsql24.so
APILevel=1
ConnectFunctions=YYY
DriverODBCVer=3.52
FileUsage=0
HelpRootDirectory=/opt/Connect64forODBC60/help
SQLLevel=0

O próximo passo é configurar os arquivos listener.ora e tnsnames.ora de forma a fazer referência ao serviço de banco de dados que chamarei de POSTGRESQL conforme demonstrado no exemplo abaixo:


[oracle@Linux ~]$ cd $ORACLE_HOME/network/admin

-- Arquivo listener.ora
[oracle@Linux admin]$ cat listener.ora
# listener.ora Network Configuration File: $ORACLE_HOME/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = BD01)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (SID_NAME = BD01)
    )
    (SID_DESC =
      (SID_NAME= POSTGRESQL)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (PROGRAM=dg4odbc)
      (ENVS=LD_LIBRARY_PATH = /opt/Connect64forODBC60/lib:
                              /u01/app/oracle/product/11.1.0/db_1/lib:
                              /lib:/usr/lib:/etc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

Apenas como observação, caso uma versão anterior do Oracle (10g, 9i ...) esteja sendo utilizada, no trecho do código acima deverá ser substituído a linha onde está (PROGRAM=dg4odbc) para (PROGRAM=hsodbc).


-- Arquivo tnsnames.ora
[oracle@Linux admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: $ORACLE_HOME/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BD01 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BD01)
    )
  )

PG_SERVICE =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST = Linux)(PORT=1521))
        (CONNECT_DATA=(SID=POSTGRESQL))
        (HS=OK)
      )

Após definida a configuração dos arquivos listener.ora e tnsnames.ora, irei inicializar o processo listener conforme demonstrado no exemplo abaixo:


-- Inicialização do processo listener
[oracle@Linux ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.1.0.7.0 - Production on 02-JUL-2009 09:10:53

Copyright (c) 1991, 2008, Oracle.  All rights reserved.

Iniciando /u01/app/oracle/product/11.1.0/bd01/bin/tnslsnr: aguarde...

TNSLSNR for Linux: Version 11.1.0.7.0 - Production
O arquivo de parâmetros do sistema é $ORACLE_HOME/network/admin/listener.ora
Atendendo em: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux)(PORT=1521)))
Atendendo em: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Estabelecendo conexão com (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux)(PORT=1521)))
STATUS do LISTENER
------------------------
Apelido              LISTENER
Versão               TNSLSNR for Linux: Version 11.1.0.7.0 - Production
Data Inicial         02-JUL-2009 09:10:53
Funcionamento        0 dias 0 hr. 0 min. 0 seg
Nível de Análise     off
Segurança            ON: Local OS Authentication
SNMP                 OFF
Arq. Parâm. Listn.   /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Resumo de Atendimento...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Resumo de Serviços...
O serviço "BD01" tem 1 instância(s).
  Instância "BD01", status UNKNOWN, tem 1 handler(s) para este serviço...
O serviço "POSTGRESQL" tem 1 instância(s).
  Instância "POSTGRESQL", status UNKNOWN, tem 1 handler(s) para este serviço...
O comando foi executado com êxito

Afim de evitar quaisquer erros de acesso às bibliotecas ODBC instaladas, ou qualquer outra surpresa, irei atualizar também arquivo de profile do usuário "oracle" com o caminho das bibliotecas ODBC instaladas anteriormente:


[oracle@Linux ~]$ cat .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

export TEMP=/tmp
export TMPDIR=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_SID=BD01
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/ctx/bin:/opt/Connect64forODBC60/lib
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/lib:
                       /opt/Connect64forODBC60/lib
export NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
export NLS_DATE_FORMAT=DD/MM/YYYY

Agora vem a parte em que criamos o arquivo de configuração para que seja possível a realização da conexão entre o Oracle e o banco de dados remoto (no meu caso, o PostgreSQL). Os programas dg4odbc/hsodbc geralmente procuram por um arquivo init[SID].ora localizado em $ORACLE_HOME/hs/admin. No meu caso, o SID que utilizei no arquivo listener.ora foi POSTGRESQL, portanto o arquivo a ser criado será initPOSTGRESQL.ora conforme demonstrado no trecho de código abaixo:


[oracle@Linux ~]$ cd $ORACLE_HOME/hs/admin

[oracle@Linux admin]$ cat initPOSTGRESQL.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

# HS init parameters
#
HS_FDS_CONNECT_INFO = "PostgreSQL Wire Protocol"
HS_FDS_TRACE_LEVEL = OFF
HS_LANGUAGE = "BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
HS_NLS_DATE_FORMAT = "DD/MM/YYYY HH24:MI:SS"
HS_OPEN_CURSORS = 200
HS_FDS_SHAREABLE_NAME = /opt/Connect64forODBC60/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/opt/Connect64forODBC60/odbc.ini
set ODBCINSTINI=/opt/Connect64forODBC60/odbcinst.ini
set LD_LIBRARY_PATH=/opt/Connect64forODBC60/lib:
                    /u01/app/oracle/product/11.1.0/bd01/lib:
                    /lib:/usr/lib
set PATH=/opt/Connect64forODBC60/lib:/u01/app/oracle/product/11.1.0/bd01/lib:
         /lib:/usr/lib
#
# Environment variables required for the non-Oracle system
#
#set [envvar]=[value]

Basicamente, os valores e parâmetros acima mais importantes são:

* set ODBCINI define o local onde está localizado o arquivo odbc.ini
* HS_FDS_CONNECT_INFO aponta para o nome da fonte de dados ODBC (DSN).
* HS_FDS_SHAREABLE_NAME aponta para a biblioteca de driver ODBC compartilhada.

Caso algum problema ocorra na tentativa de realizar a conexão com o banco de dados remoto, setando o parâmetro HS_FDS_TRACE_LEVEL com um valor maior que zero ou (ON ou DEBUG) é que nos permitirá verificar com mais detalhes o que aconteceu de errado. Para maiores informações sobre estes e outros parâmetros, acesse a página Oracle Database Gateway for ODBC User's Guide.

Por fim, após realizado todo este trabalho acima, bastará agora, para fins de teste, criar um link de banco de dados privado no meu banco de dados Oracle local que chamarei de "pgsql" para, então, realizar o teste de conexão com o banco de dados remoto. Vale a observação de que eu já criei e populei no banco de dados remoto (PostgreSQL), uma tabela de teste chamada "emp" (empregados) com a estrutura a seguir:


create table emp
(
    cod smallint NOT NULL,
    data_cadastro date NOT NULL,
    nome varchar(80) NOT NULL,
constraint pk_emp primary key (cod)
);

--  Criando o link de banco de dados (database link)
C:\>sqlplus system/*********

SQL*Plus: Release 11.1.0.6.0 - Production on Qui Jul 02 09:25:44 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link pgsql connect to "postgres" identified by "postgres"
  2  using 'PG_SERVICE';

Vínculo de banco de dados criado.

-- Realizando o teste de conexão com o PostgreSQL
SQL> select * from "emp"@pgsql;

       cod data_cadas nome
---------- ---------- ---------------------------------
         1 25/06/2009 EDUARDO
         2 28/06/2009 MARIA
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA


SQL> delete from "emp"@pg where "cod"=2;

1 linha deletada.

SQL> commit;

Commit concluído.

SQL> select * from "emp"@pgsql;

       cod data_cadas nome
---------- ---------- ---------------------------------
         1 25/06/2009 EDUARDO
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA

Em relação ao PostgreSQL, será necessário colocar os nomes das tabelas e colunas entre "aspas duplas", de forma a acessar os objetos do banco de dados, caso contrário, o erro abaixo será emitido:


SQL> select * from emp@pgsql;
select * from emp@pgsql
              *
ERRO na linha 1:
ORA-00942: a tabela ou view não existe
[DataDirect][ODBC PostgreSQL Wire Protocol driver][PostgreSQL]ERROR: relation "EMP" does 
not exist(File namespace.c;Line 221;Routine RangeVarGetRelid;)[DataDirect][ODBC 
PostgreSQL Wire Protocol driver][PostgreSQL]Failed transaction. 
The current transaction rolled back.
ORA-02063: precedendo 2 lines a partir de PGSQL


SQL> select owner,table_name from all_tables@pgsql where table_name='emp';

OWNER           TABLE_NAME
--------------- ------------------------------
public          emp


SQL> desc "emp"@pgsql
 Nome                       Nulo?     Tipo
 -------------------------- --------- ----------------------------
 cod                        NOT NULL  NUMBER(5)
 data_cadastro              NOT NULL  DATE
 nome                       NOT NULL  VARCHAR2(80)


SQL> select nome from "emp"@pgsql;
select nome from "emp"@pgsql
       *
ERRO na linha 1:
ORA-00904: "NOME": identificador inválido

SQL> select "nome" from "emp"@pgsql;

nome
-------------------------------------------------
EDUARDO
ANGELA
LAURA


SQL> create view emp_remoto
  2  as
  3  select * from "emp"@pgsql;

View criada.

SQL> select * from emp_remoto;

       cod data_cadas nome
---------- ---------- ---------------------------
         1 25/06/2009 EDUARDO
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA


SQL> create synonym emp for "emp"@pgsql;

Sinônimo criado.

SQL> select * from emp;

       cod data_cadas nome
---------- ---------- ---------------------------
         1 25/06/2009 EDUARDO
         3 21/06/2009 ANGELA
         4 29/06/2009 LAURA


SQL> create table "emp2"@pgsql (id integer);
create table "emp2"@pgsql (id integer)
                   *
ERRO na linha 1:
ORA-02021: as operações DDL não são permitidas em um banco de dados remoto

Para concluir, podemos ver acima que o último comando falhou, pelo fato de não ser possível qualquer operação DDL (Data Definition Language) no banco de dados remoto.

Atribuição ou Função?

2009-06-25T15:30:00.002-03:00

Olá,

Apesar de ser um assunto nada relevante no que se refere à administração de banco de dados Oracle, por incrível que pareça o mesmo sempre me intrigou e que, agora, parece que o "problema" era simplesmente um erro de tradução do termo "role" na camada de mensagens do Oracle Client 10g (release 1) e versões anteriores. No Oracle client 10g (release 2) e 11g, a tradução da mensagem informativa que aparece após a execução do comandos create role e drop role foi devidamente corrigida:


-- Oracle Client 10g release 1
C:\>sqlplus system/******

SQL*Plus: Release 10.1.0.2.0 - Production on Qui Jun 25 13:45:32 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role teste;

Função criada.

Podemos ver acima a mensagem "Função criada.", mas eu criei uma atribuição e não uma função! Se alterarmos a sessão para utilizar o idioma Inglês, poderemos perceber abaixo a mensagem "Role dropped." e não "Function dropped.":


SQL> alter session set nls_language=american;

Session altered.

SQL> drop role teste;

Role dropped.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit 
Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

Irei agora utilizar o SQL*Plus do Oracle Client 11g release 1 ...


-- Oracle Client 11g release 1
C:\>sqlplus system/******

SQL*Plus: Release 11.1.0.6.0 - Production on Qui Jun 25 13:47:02 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role teste;

Atribuição criada.

SQL> drop role teste;

Atribuição eliminada.

Realizando o teste utilizando o SQL*Plus do Oracle 10g (release 2) diretamente do servidor onde o banco de dados está instalado, o termo "Atribuição" é corretamente mostrado, o que indica que o "problema", ao meu ver, estava apenas no Oracle Client do Oracle 10g release 1 e versões anteriores:


[oracle@linux ~]$ export NLS_LANG="BRAZILIAN PORTUGUESE_BRAZIL.WE8ISO8859P1"
[oracle@linux ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Qui Jun 25 15:13:07 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create role teste;

Atribuição criada.

SQL> drop role teste;

Atribuição eliminada.

Definição de senhas case-sensitive no Oracle 11g ...

2009-06-17T10:48:00.004-03:00

Olá,

Para quem está planejando migrar o seu atual banco de dados Oracle para a mais recente versão Oracle 11g, ao utilizar o DBCA (Database Configuration Assistant), irá se deparar com uma nova tela de configuração intitulada de "Definições de Segurança". O objetivo desta tela é configurar, além de algumas definições padrão de auditoria, um novo perfil de senhas como mostrado na figura abaixo:

As configurações de segurança padrão ativam a auditoria para muitos privilégios de sistemas diferentes, como create session, create user, drop user, create any procedure, alter system, alter database, entre outras, de forma a seguir algumas exigências normativas definidas na legislação americana Sarbanes-Oxley (SOX).

O objetivo deste artigo é demonstrar a nova funcionalidade de senhas case-sensitive do Oracle 11g que, até então, eram case-insensitive, ou seja, no Oracle 11g existe a diferenciação entre letras maiúsculas e minúsculas na definição de uma senha de usuário de banco de dados. Por exemplo: Nas versões anteriores ao Oracle 11g, caso criássemos um usuário de banco de dados SCOTT e definíssemos sua senha para TIGER, poderíamos então nos conectar no banco de dados através deste usuário digitando a senha TIGER, Tiger, tiger, TiGer ou tiGER, sem qualquer distinção entre letras maiúsculas e minúsculas.

Agora no Oracle 11g, ao escolher a primeira opção conforme mostrado na figura acima, o banco de dados fará distinção entre letras maiúsculas e minúsculas ao entrar com a senha do usuário. No exemplo anterior, a senha do usuário SCOTT foi definida como TIGER (todas em maiúsculas), portanto, a conexão com o banco de dados só será bem sucedida caso a senha seja fornecida seja TIGER, não mais Tiger, tiger, TiGer ou tiGER.

Podemos ver abaixo o novo parâmetro de inicialização responsável por implementar este novo perfil de senhas case-sensitives no Oracle 11g:


SQL> show parameter sec_case_sensitive_logon

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
sec_case_sensitive_logon             boolean     TRUE

Como demonstrado acima, o parâmetro SEC_CASE_SENSITIVE_LOGON está atualmente configurado como TRUE, ou seja, para realizar uma conexão bem sucedida no banco de dados, um usuário deverá fornecer a senha exatamente como foi definida durante a criação do usuário (diferenciando maiúsculas e minúsculas), isso supondo que a mesma tenha sido definida já no próprio Oracle 11g. Vamos então a um exemplo prático:


C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Qua Jun 17 09:32:49 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS> create user scott identified by tiger;

Usuário criado.

SYS> grant connect to scott;

Concessão bem-sucedida.

SYS> connect scott/TIGER
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


Advertência: Você não está mais conectado ao ORACLE.
@> connect scott/Tiger
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


@> connect scott/TiGer
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


@> connect scott/tiGER
ERROR:
ORA-01017: senha/nome do usuário inválido; log-on negado


@> connect scott/tiger
Conectado.

De acordo com a demonstração acima, podemos perceber que o usuário SCOTT só conseguiu se logar no banco de dados quando a senha "tiger" (todas em minúsculas) foi fornecida. Ao consultarmos a view de dicionário de dados DBA_USERS, poderemos perceber duas mudanças significativas:


SYS> select username, password, password_versions from dba_users;

USERNAME              PASSWORD                 PASSWORD_VERSIONS
--------------------- ------------------------ -----------------
MGMT_VIEW                                      10G 11G
SYS                                            10G 11G
SYSTEM                                         10G 11G
DBSNMP                                         10G 11G
SYSMAN                                         10G 11G
SCOTT                                          10G 11G
OUTLN                                          10G 11G
ANONYMOUS
WMSYS                                          10G 11G
XDB                                            10G 11G
DIP                                            10G 11G
ORACLE_OC                                      10G 11G
TSMSYS                                         10G 11G
XS$NULL                                        10G 11G

14 linhas selecionadas.

A primeira mudança que podemos notar é que a informação do valor hash da senha na coluna PASSWORD não é mais mostrada como nas versões anteriores do Oracle, portanto, a mesma foi omitida no Oracle 11g, mas nada nos impede visualizar o seu valor hash na tabela base de dicionário de dados SYS.USER$.


SYS> select name,password from sys.user$ where name='SCOTT';

NAME                       PASSWORD
-------------------------- ------------------------------
SCOTT                      F894844C34402B67

Vale a pena salientar que o novo valor hash gerado pela senha case-sensitive é armazenada na coluna SPARE4 da tabela base SYS.USER$ do dicionário de dados.

Uma outra mudança perceptível na view de dicionário de dados DBA_USERS foi adição de uma nova coluna chamada PASSWORD_VERSIONS que indica em qual versão do Oracle a senha do usuário foi criada.

O valor "10g 11g" significa que a senha em questão possui informações hash tanto na coluna PASSWORD quanto na coluna SPARE4 da tabela base SYS.USER$, e que a mesma pode ou não ser case-sensitive (Isso vai depender se o parâmetro SEC_CASE_SENSITIVE_LOGON está ativado ou não).

O valor "10g" significa que somente a coluna PASSWORD possui a informação hash da senha do usuário, indicando que independente do parâmetro SEC_CASE_SENSITIVE_LOGON estar ativado ou não, a senha deste usuário vai ser case-insensitive até que a mesma seja alterada.

O valor "11g" indica que somente a coluna SPARE4 possui a informação hash da senha do usuário, indicando que a mesma é case-sensitive, portanto, o usuário conseguirá se conectar no banco de dados apenas quando o parâmetro SEC_CASE_SENSITIVE_LOGON estiver configurado com o valor TRUE (ativado). Abaixo irei realizar uma pequena demonstração:


SYS> alter user scott identified by TIGER;

Usuário alterado.

SYS> select password,spare4 from user$ where name='SCOTT';

PASSWORD                   SPARE4
-------------------------- --------------------------------------------------------------
F894844C34402B67           S:2CA14104071F06830C95527377C53B0DBF0349DE1B7BF4238EA663C806FD

SYS> select password_versions from dba_users where username='SCOTT';

PASSWORD
--------
10G 11G

SYS> alter user scott identified by values 'F894844C34402B67';

Usuário alterado.

SYS> select password,spare4 from user$ where name='SCOTT';

PASSWORD                   SPARE4
-------------------------- ----------------------------------------------------------
F894844C34402B67

SYS> select password_versions from dba_users where username='SCOTT';

PASSWORD
--------
10G

SYS> alter user scott identified 
  2  by values 'S:2CA14104071F06830C95527377C53B0DBF0349DE1B7BF4238EA663C806FD';

Usuário alterado.

SYS> select password,spare4 from user$ where name='SCOTT';

PASSWORD                   SPARE4
-------------------------- --------------------------------------------------------------
                           S:2CA14104071F06830C95527377C53B0DBF0349DE1B7BF4238EA663C806FD

SYS> select password_versions from dba_users where username='SCOTT';

PASSWORD
--------
11G

Em resumo, quando se define uma nova senha de usuário utilizando o Oracle 11g, a mesma irá atualizar os valores hash nas colunas PASSWORD e SPARE4 independente do parâmetro SEC_CASE_SENSITIVE_LOGON estiver ativado ou não.

Portanto, quando migramos usuários de versões Oracle anteriores (8i, 9i, 10g) para o Oracle 11g, seja utilizando uma importação FULL com o tradicional utilitário de importação (imp), ou usando o utilitário Import Datapump (impdp), nas quais as senhas dos usuários são case-insensitives, as senhas desses usuários continuarão case-insensitives, independente do parâmetro SEC_CASE_SENSITIVE_LOGON estar ativado ou não.

No caso de usuários com privilégios SYSDBA e SYSOPER, um outro parâmetro foi adicionado ao utilitário orapwd de forma a controlar senhas case-sensitives no arquivo de senhas:


$ orapwd file=orapwBD01 entries=5 ignorecase=y password=minha_senha

O parâmetro ignorecase, demonstrado no comando acima, pode ter o valor "n" (default), informando que as senhas serão tratadas como case-sensitives, ou "y" informando que as senhas serão tratadas como case-insensitives.

Para finalizar, e aproveitando o assunto de segurança de acesso e senhas de usuários, agora no Oracle 11g foi também disponibilizada uma view de dicionário de dados DBA_USERS_WITH_DEFPWD que ajuda o DBA a identificar quais usuários possuem a senha padrão "default" desde a criação do banco de dados, de forma que as mesmas possam ser alteradas quando possível:


SYS> select * from dba_users_with_defpwd;

USERNAME
------------------------------
DIP
XS$NULL
TSMSYS
OUTLN
EXFSYS
ORACLE_OCM
XDB
WMSYS

8 linhas selecionadas.

Este mercado é promissor? Vale a pena a certificação?

2009-06-07T13:23:00.008-03:00

Olá,

Recebi de um leitor uma questão relacionada ao mercado de banco de dados e certificações Oracle, e resolvi então publicar aqui de forma a compartilhar e expor o meu humilde ponto de vista.

Olá Eduardo Legatti,

primeiramente quero parabenizar pelo blog, sendo uma ótima fonte de estudos, cheguei até ele através do google, quando procurava informações sobre certificação Oracle OCA.

Estou começando meus estudos na área, faço Análise e Desenvolvimento de Sistemas e ao estudar pela primeira vez Banco de Dados, tive uma enorme vontade de me especializar em algum banco específico, pesquisando pela web percebi que é uma boa estudar Oracle, desde então venho buscando fontes de estudo.

Até mais.. e mais uma vez parabéns.

ps: ao seu ver, esse mercado é promissor, vale a pena a certificação?

Primeiramente, obrigado pelo comentário. Realmente acho que o mercado de banco de dados, seja ele Oracle, MS SQL Server, DB2, etc.. é promissor.

Na minha visão, o mercado de trabalho para um DBA é a mesma para qualquer outra área de tecnologia. Tudo depende da demanda do mercado. Por exemplo, às vezes acontece de empresas estarem muito precisando de um profissional trainee para trabalhar com administração de banco de dados Oracle e/ou MS SQL Server. Também, não é raro existirem muitas demandas de profissionais que tenham conhecimentos básicos em DB2 da IBM, ou PostgreSQL ou MySQL, ou Oracle para cargo de trainee.

Acredito que para entrar nesta área, o profissional necessitará ter o conhecimento mínimo básico para iniciar um trabalho de trainee ou DBA Júnior.

Olha, tem muita gente que tira certificação sem qualquer experiência na área achando que com isso irá conseguir um emprego rápido. Na verdade, a certificação vai dar uma maior visibilidade no currículo e chamar a atenção de quem está recrutando. Isso mostra que um profissional certificado tem mais oportunidades de quem não tem certificação. Quem tira certificação em uma determinada tecnologia, na verdade, está assinando que é um "expert" na mesma, ou seja, a certificação ajudou o profissional a ser chamado para uma entrevista. Agora, se nessa entrevista, o mesmo não demonstrar nenhuma experiência e/ou habilidades necessárias para ocupar o cargo, então pode ter certeza de que o mesmo não será contratado. Infelizmente ou felizmente, é o mercado quem dita as regras, portanto, é necessário que estejamos sempre preparados. No mais, com certeza vale a pena a certificação.

Em relação às fontes de estudo, quando eu estava estudando para tirar a certificação OCA/OCP 9i, eu estava estudando pelos manuais oficiais de quando eu fiz os cursos do Oracle 9i. Eu também tinha comprado os livros "OCA/OCP Introdução ao Oracle 9i SQL (1Z0-007)" e "OCA/OCP Oracle9i Database Fundamentos I (1Z0-031)" ambos da Oracle Press - Autor Jason Couchman. Posteriormente, quando estava estudando para atualizar a minha certificação OCP 9i para OCP 10g, passei mais tempo praticando no próprio banco de dados Oracle 10g e lendo mais a documentação oficial.

Agora, se você está estudando para tirar a certificação do Oracle 10g ou 11g (Recomendo o 11g), então eu sempre indico os manuais da SYBEX porque eles abordam todos os tópicos que caem nos exames. Outra coisa que também acho importante é a de praticar nos simulados da SelfTest ou Transcender. Caso você consiga algum material (no Google), os mesmos serão muito úteis para o seu aprendizado.

Aproveitando a sua questão, e já que você está cursando "Análise e Desenvolvimento de Sistemas", vou comentar um pouco de perfis de DBA's. Bom, acredito que existam no mínimo 2 tipos de DBA's hoje no mercado.

O tipo 1 seria aquele DBA que trabalha no servidor de produção, realizando manutenções necessárias de tuning, sugerindo ajustes de memória e I/O quando necessários, fazendo realocações de arquivos de banco de dados, criando e executando políticas de backup/recovery, realizando aplicações de patches e correções críticas, entre outras, sem ter uma noção clara de como os SQL’s foram codificados nas aplicações de terceiros que são executadas no servidor.

O outro tipo de DBA (tipo 2) que é mais o meu caso, é aquele voltado para o desenvolvimento de aplicações na qual, é de fundamental importância, o conhecimento das regras de negócios em questão, de forma a fornecer a melhor solução de banco de dados para um determinado tipo de problema, além de possuir conhecimentos sólidos de modelagem de dados, ser apto a sugerir a criação de um padrão de nomenclatura de objetos de banco de dados e, conseqüentemente, ser apto de fiscalizar e garantir o uso desse padrão pelos analistas de sistemas e desenvolvedores. Outra tarefa seria a criação de uma política de melhores práticas de construções de instruções SQL, realização de análise de performance dos SQL’s construídos de forma a capturar o melhor plano de acesso aos registros e, assim, realizar os ajustes necessários avaliando a necessidade de criação de índices (btree/bitmap), views materializadas, tabelas/índices particionados, etc…

Em resumo, o trabalho do DBA tipo 2 seria mais de prover suporte à equipe de desenvolvimento, garantindo asimm a qualidade das aplicações desenvolvidas no que se refere ao modelo de dados e acesso ao banco de dados.

Fazendo uma comparação, acredito que um bom trabalho realizado pelo DBA tipo 2 facilitaria muito, em parte, o trabalho realizado pelo DBA tipo 1.

Boa sorte e até mais ...

Dúvidas sobre modelagem de dados ...

2009-06-04T09:55:00.010-03:00

Olá,

Recentemente recebi de um colega, uma dúvida sobre modelagem de dados e resolvi então compartilhar aqui.

Cenário:

Possuo um cadastro de clientes que usam o serviço de hospedagem de sites, e um outro cadastro que utiliza o serviço de registro de domínios. A grande questão é como representar isso, os casos são os seguintes:

Um cliente pode ter somente o serviço de hospedagem de sites, logo, todos os seus dados estariam na tabela de hospedagem.

Um cliente pode ter somente o serviço de registro de domínios, logo, todos os seus dados estariam na tabela de registro de domínios.

Um cliente pode ter ambos os serviços, logo, teria dados nas 2 tabelas, mas, esses dados poderiam ser os mesmos, como também não poderiam.

Detalhe: Essa estrutura precisaria ser preparada para num futuro próximo aumentar os serviços, ou seja, além de hospedagem e domínios, possuir outros, loja virtual, etc.

Enfim, imaginei uma terceira tabela para controlar isso, mandei um esboço em anexo mas estou receoso devido ao fato de com isso existirem muitas chaves estrangeiras nulas e a aplicação precisaria descobrir em qual tabela estariam os dados do cliente.

Solução:

Bem, em relação ao último parágrafo do texto acima, posso dizer que chaves estrangeiras nulas não são problema algum. Imagine que eu tenha uma tabela de clientes onde a mesma possua uma coluna (id_servico) que armazene códigos de serviços, e que a mesma referencie a tabela de serviços.

Se pode haver clientes que não prestam nenhum serviço, então poderiam existir vários clientes onde a coluna id_serviço estaria NULA.

Não se pode confundir chave estrangeira com chave primária. Chave primária é obrigatória, mas chave estrangeira pode ou não ser obrigatória e isso vai depender da regra de negócio em questão.

Voltando ao cenário proposto, isso está me parecendo um relacionamento simples onde "Um Cliente pode prestar vários serviços" e "Um serviço pode ser prestado por vários clientes".

Se você quer uma estrutura flexível e, já pensando em um futuro próximo, permitir o registro de mais serviços, então criar tabelas específicas para armazenar cada serviço seria praticamente inviável e sem sentido. Acredito que com apenas 3 tabelas o seu problema seria solucionado, mas para complicar um pouco avalie se uma quarta tabela seria útil para você. Então, neste caso, avalie a solução abaixo:

1) criar uma tabela de clientes
2) criar uma tabela de serviços
3) criar uma tabela que armazenará os serviços que cada cliente poderá prestar

4) criar uma tabela com colunas adicionais que registrará todos os serviços de todos os clientes

Portanto, tome como exemplo os dados abaixo de acordo com o modelo acima proposto:


=======
CLIENTE
=======

ID_CLIENTE NOME_CLIENTE
---------- ------------
         1 Cliente A
         2 Cliente B
         3 Cliente C


=======
SERVICO
=======

ID_SERVICO NOME_SERVICO
---------- -------------------
         1 Hospedagem de sites 
         2 Registro de domínio
         3 Loja Virtual
         4 Outro, etc...

Abaixo irei definir quais serviços cada cliente poderá prestar:


===============
CLIENTE_SERVICO
===============

ID_CLIENTE ID_SERVICO
---------- ----------
         1          1
         1          2
         2          3
         3          2
         4          1

Para finalizar, a tabela REGISTRO abaixo armazenaria todos os serviços de todos os clientes sendo que a coluna ID_REGISTRO seria um seqüencial.


========
REGISTRO
========

ID_REGISTRO ID_CLIENTE ID_SERVICO ENDERECO_WEB ETC...
----------- ---------- ---------- ------------ ------
          1          1          1 http:// .... ... 
          2          1          2 http:// .... ...
          3          2          3 http:// .... ...
          4          3          2 http:// .... ...
          5          4          1 http:// .... ...

Como você viu acima, a tabela REGISTRO, a princípio, parece ser redundante no modelo pois faz o mesmo papel da tabela CLIENTE_SERVICO mas, neste caso, você poderia armazenar mais registros do mesmo serviço para o mesmo cliente. Por outro lado, bastaria também fazer um relacionamento não identificado na tabela CLIENTE_SERVICO onde as colunas ID_CLIENTE e ID_SERVICO seriam apenas chaves estrangeiras, e não chaves primárias.

Dependendo do caso, você poderá trabalhar somente com a tabela CLIENTE_SERVICO deixando a tabela REGISTRO fora do modelo, ou seja, a tabela REGISTRO seria opcional.

No mais, isso realmente vai depender das regras de negócios envolvidas na sua aplicação de forma a atingir os requisitos necessários.

Enfim, de acordo com o modelo e na minha visão, a tabela CLIENTE_SERVICO teria apenas o papel de armazenar de forma parametrizável o que cada cliente poderia fornecer, obedecendo assim, o requisito na qual você diz que "Um cliente poderá ter somente o serviço X ou Y ou ambos ..."

Um pouco do pacote DBMS_APPLICATION_INFO ...

2009-05-13T11:40:00.005-03:00

Olá,

Acredito que este artigo esteja mais direcionado para desenvolvedores de aplicações que fazem uso do banco de dados Oracle.

Problema:

Disponibilizar informações no Oracle, em tempo real, do que está sendo realizado por cada usuário de um sistema. Se um usuário acessar o banco de dado através de uma aplicação, deverá estar disponível para visualização, alguma informação que identifique o quê cada sessão está executando dentro da aplicação.

Solução:

Sabemos que a view dinâmica de desempenho V$SESSION nos oferece algumas informações das sessões que estão atualmente conectadas no banco de dados Oracle. Portanto, para resolver o problema acima, poderemos fazer o uso da view V$SESSION de forma a popular algumas colunas como MODULE, ACTION e CLIENT_INFO como demonstrarei mais abaixo.

Irei utilizar o pacote chamado DBMS_APPLICATION_INFO diponível no Oracle, na qual farei uso das procedures SET_MODULE e SET_CLIENT_INFO como demonstrado abaixo:


SQL> desc DBMS_APPLICATION_INFO;

PROCEDURE READ_CLIENT_INFO
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                OUT
PROCEDURE READ_MODULE
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                OUT
 ACTION_NAME                    VARCHAR2                OUT
PROCEDURE SET_ACTION
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_CLIENT_INFO
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                IN
PROCEDURE SET_MODULE
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_SESSION_LONGOPS
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT

Imagine que estamos desenvolvendo um sistema de Controle de Estoque. Agora imagine um relatório fornecido pelo sistema que consolida as entradas e saídas de todos os materiais de um almoxarifado e que esse relatório possa ou não levar um tempo considerável para ser executado. Se quisermos visualizar no banco de dados através da view V$SESSION as sessões que estão executando este relatório, precisaríamos então incluir no fonte do sistema (antes do usuário submeter a execução do relatório), o comando abaixo:

Irei realizar uma simulação como se o sistema em uso fosse o próprio SQL*PLUS, utilizando o usuário SCOTT para realizar a conexão com o banco de dados:


SQL> exec dbms_application_info.set_module(
          module_name => 'Controle de Estoque',
          action_name => 'Executando Relatório de Materiais');

Procedimento PL/SQL concluído com sucesso.

Ao executar o procedimento acima, poderemos então obter tais informações selecionando as mesmas da view dinâmica de desempenho V$SESSION:

SQL> select osuser,machine,program,module,action from 
  2  v$session where username='SCOTT';

OSUSER     MACHINE     PROGRAM      MODULE              ACTION
---------- ----------- ------------ ------------------- ---------------------------------
legatti    MICRO_001   sqlplus.exe  Controle de Estoque Executando Relatório de Materiais

Como demonstrado acima, podemos perceber que as colunas MODULE e ACTION foram populadas. Em resumo, poderemos então utilizar o pacote DBMS_APPLICATION_INFO em nossas aplicações de forma a disponibilizar ao administrador do sistema ou até mesmo ao DBA, uma opção para visualizar em tempo real "quem" está fazendo "o quê" dentro do sistema em questão.

Caso seja necessário disponibilizar mais informações, poderemos também fazer uso da coluna CLIENT_INFO como demonstrado abaixo:


SQL> exec dbms_application_info.set_client_info('data, hora, meu IP, etc...');

Procedimento PL/SQL concluído com sucesso.

SQL> select client_info from v$session where sid=(select sid from v$mystat where rownum=1);

CLIENT_INFO
--------------------------------
data, hora, meu IP, etc...

Como eu citei acima a informação de endereço IP, poderemos também fazer uso da função SYS_CONTEXT de forma a acessar esta e outras informações da sessão em questão:


SQL> select SYS_CONTEXT('USERENV', 'TERMINAL') micro,
  2             SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip,
  3             SYS_CONTEXT('USERENV', 'OS_USER') usuario_rede from dual;

MICRO          IP             USUARIO_REDE
-------------- -------------- --------------
MICRO_001      192.168.1.10   legatti

No mais, restaria ao desenvolvedor encontrar a forma mais apropriada de implementar tal solução na linguagem de programação ou framework em uso (Java, PHP, Delphi, etc...).