Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


quinta-feira, 2 de julho de 2015

Gerando hash MD5 do conteúdo de colunas do tipo LOB no Oracle com a package DBMS_CRYPTO

Por Eduardo Legatti

Olá,

Nos artigos de Novembro/2007 e Dezembro/2009 e eu demonstrei de forma prática como gerar arquivos externos ao banco de dados através das packages UTL_FILE e DBMS_LOB, a utilização do objeto DIRECTORY, bem como a apresentação de uma função para geração de hash MD5 através da package DBMS_OBFUSCATION_TOOLKIT para geração de senhas e pequenos textos. Neste artigo, irei abordar novamente a utilização dos objetos DIRECTORY e da package DBMS_LOB quanto a geração de hash MD5. A diferença é que agora irei demonstrar como gerar um hash MD5 para dados do tipo LOB (BLOB, CLOB) através da package DBMS_CRYPTO disponível à partir do Oracle 10g.

O objetivo do artigo será demonstrar uma função de banco de dados que chamarei de md5_blob que receberá como parâmetro a coluna de uma tabela do tipo de dado BLOB. Em alguns casos ela poderá ser muito útil, como foi pra mim, quando precisei verificar em uma tabela a quantidade de documentos binários duplicados (*.doc, *.xls, *.pdf, *.jpg, etc.) que estavam armazenados em uma coluna do tipo BLOB.

Segue abaixo os comandos executados para criar um ambiente de forma a simular uma tabela contendo documentos binários. No caso em questão, irei armazenar um arquivo chamado imagem.jpg.
 
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:04:59 2015

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

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

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

Diretório criado.

-- 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.

SQL> grant write,read on directory arquivo_dir to scott;

Concessão bem-sucedida.

SQL> grant execute on dbms_crypto to scott;

Concessão bem-sucedida.

-- Listando o arquivo que será carregado para a tabela tab_imagem
[oracle@linux tmp]$ ls -l
total 4
-rwxr--r-- 1 oracle oinstall 820 Jul 2 10:09 imagem.jpg

$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 2 10:14:32 2015

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

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

-- 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.
  
Pronto. O arquivo imagem.jpg foi carregado para a tabela conforme demonstrado abaixo.
 
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

Segue abaixo a função de banco de dados que será utilizada para gerar o hash MD5 da coluna do tipo BLOB.
 
SQL> create or replace function md5_blob (p_blob in blob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => p_blob,
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.
  
Após a criação da função, poderemos ver abaixo através da instrução SELECT que o hash MD5 do conteúdo da coluna IMAGEM da tabela TAB_IMAGEM foi gerado com sucesso.

SQL> select id,nome,md5_blob(imagem) from tab_imagem;

        ID NOME            MD5_BLOB(IMAGEM)
---------- --------------- --------------------------------
         1 imagem.jpg      4B812BDC6240D0770A12DA89E4630BF2

1 linha selecionada.

Apenas para comprovar que o hash MD5 que foi gerado é válido, utilizarei o comando md5sum no Linux para verificar que o valor do hash é o mesmo que foi gerado pelo Oracle.
 
$ md5sum imagem.jpg
4b812bdc6240d0770a12da89e4630bf2  imagem.jpg

Em relação à colunas do tipo CLOB, a função é praticamente idêntica, o que muda um  pouco é a utilização da procedure utl_raw.cast_to_raw como demonstrado abaixo.

SQL> create or replace function md5_clob (p_clob in clob)
  2  return varchar2
  3  is
  4  begin
  5     return dbms_crypto.hash (src   => utl_raw.cast_to_raw (p_clob),
  6                              typ   => dbms_crypto.hash_md5);
  7  end;
  8  /

Função criada.
  

segunda-feira, 1 de junho de 2015

Oracle Data Pump Export: Ignorando múltiplas tabelas ao gerar o dump de exportação sem fazer uso da cláusula EXCLUDE

Por Eduardo Legatti

Olá,

Em alguns momentos nós, DBAs, somos solicitados a realizar um dump de exportação de um schema de banco de dados. Na maioria das vezes esse dump será importado para um outro ambiente, como por exemplo, no ambiente de DEV ou HOM. O que acontece às vezes é que somos solicitados a fazer o dump de forma que algumas tabelas não precisem estar no dump de exportação. Muitas vezes são tabelas grandes (de muitos gigabytes) que são usadas para armazenar logs, históricos, imagens através de colunas LOB. Enfim, dependendo da situação, são tabelas que não serão necessárias em um novo ambiente. No utilitário expdp (Datapump Export), podemos utilizar a cláusula EXCLUDE para ignorar alguns objetos que não queremos que sejam exportados. A desvantagem em utilizar a opção EXCLUDE para ignorar tabelas durante a exportação é que ao importar novamente o dump, em alguns casos, seremos acionados para criar no novo ambiente as tabelas que não foram exportadas. Isso porque existe uma grande possibilidade de alguma aplicação ser executada e conectando no schema importado, e esta aplicação dar falta das tabelas que não foram ignoradas durante a geração do dump.

No mais, o objetivo desse artigo será demonstrar o uso da cláusula EXCLUDE para ignorar tabelas durante uma exportação através do Data Pump Export (expdp), bem como demonstrar uma outra forma, através da cláusula QUERY de ignorar tabelas durante uma exportação sem a necessidade de excluí-las do dump. Segue a demonstração abaixo.
 
$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Seg Jun 1 10:13:21 2015

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

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

SQL> create table t1 (cod number constraint pk_t1 primary key);

Tabela criada.

SQL> create table t2 (cod number constraint fk_t2_t1 references t1);

Tabela criada.

SQL> create table t3 (cod number);

Tabela criada.

SQL> insert into t1 values (1);

1 linha criada.

SQL> insert into t2 values (1);

1 linha criada.

SQL> insert into t3 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

 
Acima, eu criei 3 tabelas no schema SCOTT para poder realizar a demonstração. Poderia ter criado dezenas de tabelas, mas não há necessidade. Segue abaixo o exemplo de como poderíamos exportar todas aos objetos do schema SCOTT menos as tabelas T1 e T3 utilizando a cláusula EXCLUDE.
 
$ expdp system/manager schemas=SCOTT
  dumpfile=scott.dmp 
  EXCLUDE=TABLE:\"IN \(\'T1\',\'T3\'\)\"
  nologfile=y

Export: Release 11.2.0.3.0 - Production on Seg Jun 1 10:23:21 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=SCOTT
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 64 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
Processando o tipo de objeto SCHEMA_EXPORT/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exportou "SCOTT"."T2"                                    5 KB       1 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/scott.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:23:26

Pronto. Podemos ver acima que apenas a tabela T2 foi exportada, ou seja, ao importar o dump gerado com o utilitário impdp (Datapump Import), apenas a tabela T2 será criada no destino. Como a tabela T2 tem uma foreign key para a tabela T1, a mesma será importada sem essa restrição de integridade já que ao final da importação o erro "ORA-00942: a tabela ou view não existe" será emitido pelo fato a tabela T1 não existir. Segue abaixo um outra forma de fazer a mesma coisa, só que com a vantagem de que apenas as linhas das tabelas ignoradas não serão exportadas, ou seja, a estrutura da tabela será exportada para o dump, mas as linhas da tabela não. Para isso, será usado a cláusula QUERY onde será passado para cada tabela um predicado que não retorne nenhuma linha, como por exemplo "WHERE rownum=0". Segue exemplo abaixo.
 
$ expdp system/manager schemas=SCOTT
  dumpfile=scott.dmp
  QUERY=SCOTT.T1:\"WHERE rownum=0\"
  QUERY=SCOTT.T3:\"WHERE rownum=0\"
  nologfile=y

Export: Release 11.2.0.3.0 - Production on Seg Jun 1 10:25:56 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=SCOTT
Estimativa em andamento com o metodo BLOCKS...
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE_DATA
Estimativa total usando o metodo de BLOCKS: 128 KB
Processando o tipo de objeto SCHEMA_EXPORT/USER
Processando o tipo de objeto SCHEMA_EXPORT/SYSTEM_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/ROLE_GRANT
Processando o tipo de objeto SCHEMA_EXPORT/DEFAULT_ROLE
Processando o tipo de objeto SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/TABLE
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/VIEW/VIEW
Processando o tipo de objeto SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exportou "SCOTT"."T1"                                    0 KB       0 linhas
. . exportou "SCOTT"."T2"                                    5 KB       1 linhas
. . exportou "SCOTT"."T3"                                    0 KB       0 linhas
Tabela-mestre "SYSTEM"."SYS_EXPORT_SCHEMA_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_SCHEMA_01 e:
  /tmp/scott.dmp
O job "SYSTEM"."SYS_EXPORT_SCHEMA_01" foi concluido com sucesso em 10:26:05

Pronto. Podemos acima ver que todas as 3 tabelas foram exportadas. A diferença é que as tabelas T1 e T3 estão vazias, ou seja, nenhuma linha das tabelas T1 e T3 foram exportadas. Ao importar o dump gerado, todas as 3 tabelas serão importadas mas, da mesma forma que no primeiro exemplo, como a tabela T2 tem uma Foreign Key para a tabela T1, a mesma será importada sem essa restrição de integridade. Vale a pena salientar que durante a importação, não será mais emitido o erro "ORA-00942: a tabela ou view não existe", mas o erro "ORA-02298: não é possível validar (SCOTT.FK_T2_T1) - chaves mães não localizadas".

segunda-feira, 4 de maio de 2015

Detectando sessões bloqueadoras e sessões bloqueadas no Oracle: DBA_WAITERS, V$LOCKED_OBJECT, V$LOCK, V$SESSION

Por Eduardo Legatti

Olá,

Uma das tarefas de um DBA é monitorar o banco de dados de forma que seja identificado possíveis problemas que possam vir a comprometer a performance ou a disponibilidade do mesmo. Dentre alguns problemas que podem ser identificados antecipadamente, estão aos bloqueios (locks), em especial, aqueles onde existam sessões bloqueadoras e sessões bloqueadas. Dependendo da situação, descobrir de forma rápida a sessão (SID) no banco de dados que está bloqueando por muito tempo um recurso, é fundamental para se evitar maiores problemas. Dependendo do recurso bloqueado, (tabela, linha, etc.) várias sessões ficarão bloqueadas até a liberação do bloqueio do recurso pela sessão bloqueadora. O problema em si não é o bloqueio, mas por quanto tempo a sessão fica bloqueada. Em determinados casos, por falhas na aplicação, entre outros, o bloqueio pode ficar eternamente e, em casos extremos, somente matando a sessão bloqueadora (kill session ...) irá liberar o recursos e resolver o problema.

Enfim, o objetivo deste artigo será demonstrar algumas formas de identificar as sessões que estão bloqueando outras sessões. Serão apresentadas algumas views e consultas SQL que poderão ser úteis para este fim. Irei simular abaixo um ambiente na qual uma sessão no Oracle irá bloquear outras sessões. Segue demonstração abaixo:

SESSÃO 1

 
C:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Seg Mai 4 10:17:20 2015

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

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select distinct sid from v$mystat;

       SID
----------
       278

SQL> create table t1 (id number);

Tabela criada.

SQL> insert into t1 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> update t1 set id=10;

1 linha atualizada.


Acima, a sessão de SID 278 realizou um UPDATE na tabela T1 e não efetuou COMMIT.


SESSÃO 2


SQL> select distinct sid from v$mystat;

       SID
----------
       165

SQL> create table t2 (id number);

Tabela criada.

SQL> insert into t2 values (1);

1 linha criada.

SQL> commit;

Commit concluído.

SQL> update t2 set id=100;

1 linha atualizada.

SQL> update t1 set id=20;
-- Aguardando


Na sessão de SID 165 acima, tanto na tabela T2 quanto na tabela T1 foram realizados UPDATEs. No entanto, ao realizar o UPDATE na tabela T1, a sessão ficou bloqueada, ou seja, aguardando que a sessão de SID 278 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T1.


SESSÃO 3


SQL> select distinct sid from v$mystat;

       SID
----------
        41

SQL> update t2 set id=30;
-- Aguardando

 
A sessão de SID 41 efetuou um UPDATE na tabela T2 e ficou bloqueada, ou seja, aguardando que a sessão de SID 165 efetue COMMIT ou ROLLBACK de sua transação, para então liberar o lock de linha na tabela T2. Enfim, diante da situação das sessões de banco de dados acima, poderemos ver abaixo, consultando a view dinâmica de desempenho V$SESSION, quais sessões estão bloqueadas. Caso deseje ver por quanto tempo a sessão está bloqueada, basta adicionar a coluna SECONDS_IN_WAIT na instrução SELECT a seguir. 

SQL> select sid,
  2 serial#,
  3 status,
  4 username,
  5 osuser,
  6 program,
  7 blocking_session blocking,
  8 event
  9  from v$session
 10  where blocking_session is not null;

   SID  SERIAL# STATUS   USERNAME   PROGRAM      BLOCKING EVENT
------ -------- -------- ---------- ------------ -------- -----------------------------
    41    17090 ACTIVE   SYS        sqlplus.exe       165 enq: TX - row lock contention
   165     6770 ACTIVE   SYS        sqlplus.exe       278 enq: TX - row lock contention


Pelo resultado acima, é possível verificar que a sessão de SID 41 está sendo bloqueada pela sessão de SID 165, que está sendo bloqueada pela sessão de SID 278. Uma outra forma de ver essa situação é consultar as views DBA_WAITERS e DBA_BLOCKERS. Caso estas views não estejam criadas no banco de dados, as mesmas poderão ser criadas através do script $ORACLE_HOME/rdbms/admin/catblock.sql. Segue o resultado da view DBA_WAITERS conforme demonstrado abaixo.
 
SQL> select waiting_session,holding_session from dba_waiters;

WAITING_SESSION HOLDING_SESSION
--------------- ---------------
             41             165
            165             278

Uma outra forma que temos para visualizar situações com esta é executar o script $ORACLE_HOME/rdbms/admin/utllockt.sql. Este script é muito útil quando existem várias sessões bloqueadoras e bloqueadas e precisamos saber qual sessão iniciou todo o processo de bloqueio. A visualização do resultado é em forma de uma Tree View, que demonstra em uma hierarquia, as sessões bloqueadoras e as sessões bloqueadas.
 
SQL> @utllockt.sql

Tabela criada.

Tabela criada.

2 linhas criadas.

Commit concluído.

Tabela eliminada.

1 linha criada.

Commit concluído.

WAITING_SESSION   LOCK_TYPE      MODE_REQUESTED MODE_HELD    LOCK_ID1   LOCK_ID2
----------------- -------------- -------------- ------------ ---------- ---------
278               None
   165            Transaction    Exclusive      Exclusive    393227     454
      41          Transaction    Exclusive      Exclusive    524312     454

Tabela eliminada.

Acima, é possível determinar que a sessão de SID 278 iniciou o processo de bloqueio que fez com que a sessão de SID 165 ficasse bloqueada. Como consequência, a sessão de SID 165 também bloqueou a sessão de SID 41. Em relação ao objeto (tabela) que está sofrendo bloqueio, é possível determiná-lo consultando as views DBA_OBJECT, V$LOCKED_OBJECT, V$LOCK. O código PL/SQL abaixo retorna um resultado na qual é possível ver o SID da sessão bloqueadora, o SID da sessão bloqueada e o recurso (objeto) que detém o bloqueio (lock).
  
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     DBMS_OUTPUT.enable (1000000);
  3
  4     FOR do_loop IN (SELECT session_id,
  5                            a.object_id,
  6                            xidsqn,
  7                            oracle_username,
  8                            b.owner owner,
  9                            b.object_name object_name,
 10                            b.object_type object_type
 11                       FROM v$locked_object a, dba_objects b
 12                      WHERE xidsqn != 0 AND b.object_id = a.object_id)
 13     LOOP
 14        DBMS_OUTPUT.put_line ('.');
 15        DBMS_OUTPUT.put_line ('Blocking Session   : ' || do_loop.session_id);
 16        DBMS_OUTPUT.put_line (
 17              'Object (Owner/Name): '
 18           || do_loop.owner
 19           || '.'
 20           || do_loop.object_name);
 21        DBMS_OUTPUT.put_line ('Object Type        : ' || do_loop.object_type);
 22
 23        FOR next_loop
 24           IN (SELECT sid
 25                 FROM v$lock
 26                WHERE id2 = do_loop.xidsqn AND sid != do_loop.session_id)
 27        LOOP
 28           DBMS_OUTPUT.put_line (
 29              'Sessions being blocked   :  ' || next_loop.sid);
 30        END LOOP;
 31     END LOOP;
 32  END;
 33  /
.
Blocking Session   : 278
Object (Owner/Name): SYS.T1
Object Type        : TABLE
Sessions being blocked   :  165
.
Blocking Session   : 165
Object (Owner/Name): SYS.T1
Object Type        : TABLE
Sessions being blocked   :  41
.
Blocking Session   : 165
Object (Owner/Name): SYS.T2
Object Type        : TABLE
Sessions being blocked   :  41

Procedimento PL/SQL concluído com sucesso.
 

quarta-feira, 1 de abril de 2015

Abordando o COMMIT Assíncrono disponível a partir do Oracle 10g R2 (Asynchronous Commit)

Por Eduardo Legatti

Olá,

Este artigo foi escrito por mim e pelo meu colega Carlos H. Y. Furushima (ACE A), e tem como objetivo fazer uma abordagem sobre o commit assíncrono do Oracle (Asynchronous Commit), de forma a tratar suas principais características e diferenças em relação ao commit síncrono. Esta funcionalidade (commit assíncrono) surgiu no Oracle 10g Release 2 com o intuito de alterar o comportamento de uma transação com o propósito de oferecer ganhos de performance.


O que é transação de banco de dados?

Uma transação é uma sequência (1 ou mais) de operações de leitura (read) e escrita (write) executadas por um programa (unidade lógica de trabalho) sobre um sistema gerenciador de banco de dados (SGDB), ou seja, trata-se de uma visão abstrata que o sistema gerenciador de banco de dados (SGDB) tem de um programa. Esta transação em um SGBD de modelo transacional clássico (banco de dados relacional) possui quatro importantes propriedades cujo objetivo é manter os dados protegidos de acesso concorrente e de falhas de sistema. Uma transação possui quatro diferentes propriedades chamadas de ACID, um acrônimo derivado da primeira letra das seguintes propriedades: Atomicidade, Consistência, Isolamento e Durabilidade.

A transação em seu ciclo de vida faz uso de três principais recursos da arquitetura computacional, tais como: área de armazenamento volátil (memoria), CPU e área de armazenamento permanente (Disco - Storage). O fato de uma transação ser interrompida no meio de seu ciclo de vida pode deixar o banco de dados em um estado vulnerável e inconsistente. O intuito das propriedades ACID é impor ao SGDB o tratamento dos efeitos de transações parciais (transações interrompidas) do banco de dados.


Atomicidade e Durabilidade de uma transação

Uma transação é completada somente após sua confirmação ou cancelamento, conforme as propriedades de atomicidade (A) e durabilidade (D). Para manter este mecanismo, um SGBD de modelo transacional clássico (banco de dados relacional) mantém um registro de log para operações de escritas no banco de dados, nomeado em contexto Oracle, como "redo logs". Essa estrutura é responsável por garantir as propriedades de atomicidade (A) e durabilidade (D) de modo que se o banco de dados sofrer uma eventual queda antes que os dados alterados sejam escritos de forma persistente em disco, o log será utilizado para restaurar essas informações quando o sistema for normalizado. Deste modo, o SGDB garante a atomicidade, desfazendo as ações de transações que não realizaram a confirmação (operação de COMMIT) e a durabilidade, garantindo que todas as ações de transações que realizaram a confirmação (operação de COMMIT) fiquem persistentes em disco e se tornem tolerantes às falhas do sistema (banco de dados). 

  • Atomicidade (A): A execução de toda transação deve ser considerada atômica (indivisível), ou seja, ou todas as ações são executadas ou nenhuma delas é. Em caso de não confirmação ou interrupção abrupta o banco de dados deve voltar ao mesmo estado em que estava antes do início da transação.
  • Durabilidade (D): Se uma transação é concluída com sucesso (através de uma operação commit bem sucedida), então seus efeitos são persistentes (duráveis), mesmo que o sistema sofra uma queda antes que esses resultados (blocos ou dados modificados) sejam persistidos no disco.

Entendendo e modificando o comportamento de um COMMIT no Oracle

A execução da operação de COMMIT encadeia um exercício de gravação sequencial das entradas de redo (redo entries) para uma área permanente (disco - redo log files). No Oracle, o processo background LGWR (Log Writer) é o executor desta ação que visa proteger o banco de dados em caso de queda da instância, uma vez que os dados (blocos sujos no database buffer cache) em área volátil (SGA - Memória) possivelmente não foram gravados nos datafiles. Assim, o intuito é recuperar o "trabalho não salvo" (dados que não foram para os datafiles devido à queda da instância), utilizando os arquivos de redo logs gerados. Ao iniciar uma instância que sofreu uma falha (crash recovery), o processo background SMON será o responsável por refazer todas as ações do "trabalho não salvo nos datafiles".
No Oracle, o registro de redo online, também conhecido como grupo de redo, é um conjunto de no mínimo dois ou mais arquivos que tem como função primária registrar todas as alterações feitas no banco de dados, incluindo as alterações com e sem commit. As entradas de redo são armazenadas temporariamente nos buffers de registro de redo (Redo Log Buffer) da SGA (System Global Area) onde o processo de segundo plano log writer (LGWR) grava essas entradas sequencialmente em um arquivo de registro de redo online.

Estas gravações do buffer de redo para os arquivos ocorrem nas seguintes situações: (1) a cada 3 segundos, (2) quando 1/3 do buffer estiver cheio, (3) quando o comando commit for emitido, (4) quando as entradas de redo no buffer atingir 1 MB, (5) antes do processo de segundo plano DBWn gravar as alterações do cache de banco de dados nos datafiles. Os arquivos de redo log online são utilizados de forma cíclica, por exemplo, se dois arquivos constituem o registro de redo online, o primeiro arquivo é preenchido, o segundo arquivo é preenchido, o primeiro arquivo é reutilizado e preenchido, o segundo arquivo é reutilizado e preenchido e assim por diante. Cada vez que um arquivo é preenchido, ele recebe um número de sequência de registro para identificar o conjunto de entradas de redo.  Como já mencionado anteriormente, comitar uma transação significa apenas a garantia de sua recuperação em caso de falha, e não a gravação dos dados diretamente nos datafiles como muitos imaginam.

Partindo do princípio que uma operação de COMMIT escreve as entradas de redo (redo entries) para os redo log files a fim de oferecer uma recuperabilidade para o banco de dados, é importante salientar que a existência desta sinergia não garante a durabilidade (D) da transação. Seu mecanismo (modo de funcionamento) é quem irá garantir a propriedade "D". O comportamento default (padrão) da operação de COMMIT consiste na execução de escritas síncronas das entradas de redo (redo entries) para os redo log files. Neste modo de funcionamento o Oracle garante a durabilidade (D) da transação, porém é possível modificar este comportamento a partir da versão Oracle 10g Release 2 com uma funcionalidade chamada de "Asynchronous Commit" (Commit Assíncrono), possibilitando a execução de escritas assíncronas das entradas de redo (redo entries) para os redo log files. Utilizando o "Asynchronous Commit" (Commit Assíncrono), a propriedade durabilidade (D) não é mais garantida.



Como demonstrado na figura acima, o comportamento do processo background LGWR pode ser configurado/alterado de forma a otimizar e diminuir o tempo de gravação das informações de uma transação nos Redo Log Files. Vale a pena salientar que o próprio comando COMMIT também oferece este recurso através das cláusulas demonstradas pela figura abaixo.


 



Como alterar o comportamento da operação de COMMIT no Oracle Database?

A alteração do mecanismo (modo de funcionamento) da operação de COMMIT é feita modificando dois comportamentos, ou seja, "quando o LGWR escreve as entradas de redo" e "como o LGWR escreve as entradas de redo". Estes dois comportamentos são configurados por parâmetros de instância (pfile e spfile), são eles COMMIT_WRITE (Oracle 10g), COMMIT_LOGGING e COMMIT_WAIT (Ambos Oracle 11g). É importante ressaltar que a partir da versão 11g R1 o parâmetro COMMIT_WRITE se tornou obsoleto, porém ainda funcional para a manutenção da compatibilidade com a versão 10g R2 sendo desmembrado em dois parâmetros: COMMIT_LOGGING e COMMIT_WAIT.


Oracle 10g R2

COMMIT_WRITE = "Quando e como o LGWR escreve as entradas de redo"

No Oracle 10g R2, o uso da funcionalidade Asynchronous Commit é feita pelo parâmetro COMMIT_WRITE. Seu uso é feito tanto no nível de sessão (ALTER SESSION) quanto no nível de sistema (ALTER SYSTEM). O parâmetro COMMIT_WRITE pode receber até dois argumentos, onde é determinado "quando" (de forma imediata IMMEDIATE ou agrupada BATCH) e "como" (de forma síncrona WAIT ou assíncrona NOWAIT).

Importante:
  • Se o parâmetro não receber qualquer argumento, o Oracle assumirá seu comportamento default (IMMEDIATE e WAIT).
  • Se o parâmetro receber somente o argumento IMMEDIATE ou BATCH (quando), o Oracle assumirá o argumento default referente ao comportamento "como" (WAIT).
  • Se o parâmetro receber somente o argumento WAIT ou NOWAIT (como), o Oracle assumirá o argumento default referente ao comportamento "quando" (IMMEDIATE).

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_WRITE = '{ [IMMEDIATE] | [BATCH] },{ [NOWAIT] | [WAIT] }';

Oracle 11g R1 ou Superior

COMMIT_LOGGING = "Quando o LGWR escreve as entradas de redo".

O Oracle Database por meio do processo background LGWR escreve as entradas de redo sequencialmente em um redo log file. Existem duas opções de "quando" as entradas de redo são escritas para o redo log file: de forma imediata (IMMEDIATE) após a execução da operação de COMMIT ou de forma agrupada (BATCH), onde várias operações de COMMIT são escritas em uma única requisição de I/O, ou seja, o Oracle troca intensa quantidade de I/O com baixa volumetria de blocos por uma tênue quantidade de I/O com grande volumetria de blocos. O resultado disso é a diminuição de requisições de I/O oriundas de operações de COMMIT.

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_LOGGING = '{ [IMMEDIATE] | [BATCH] }';

COMMIT_WAIT = "Como o LGWR escreve as entradas de redo".

O Oracle Database por meio do processo background LGWR escreve as entradas de redo sequencialmente em um redo log file. Existem duas opções de "como" as entradas de redo são escritas para o redo log file: de forma síncrona (WAIT) após a execução da operação de COMMIT na qual o processo background LGWR espera pela confirmação de que as entradas de redo sejam escritas nos redo log files e a forma assíncrona (NOWAIT), após a execução da operação de COMMIT. Com isso, o processo background LGWR não espera pela confirmação de que as entradas de redo sejam escritas nos redo log files.

ALTER { [SESSION] | [SYSTEM] } SET COMMIT_LOGGING = '{ [IMMEDIATE] | [BATCH] }';
 

Teste de desempenho (COMMIT)

Para medir a performance de escrita em um banco de dados Oracle no que se refere ao fechamento de uma transação com o comando COMMIT, foram realizados alguns testes de inserção (INSERTS) em uma tabela no banco de dados. O teste consistiu em medir o tempo gasto para inserção de 10, 100, 1.000 e 10.000 linhas respectivamente, de acordo com a configuração dos parâmetros COMMIT_LOGGING e COMMIT_WAIT na sessão do usuário.  Para simular as inserções, foram utilizados algumas stored procedures e functions de banco de dados, bem como a chamada de um bloco PL/SQL através do SQL*Plus. Após a execução dos testes, os resultados abaixo foram obtidos.

Obs: a vertente de tempo do gráfico foi transformada em um intervalo de referência entre 0 e 10 de forma a simplificar a sua visualização, ou seja, quanto menor o valor, melhor a performance.
  • Simulação de inserção de 10 linhas 


  • Simulação de inserção de 100 linhas




  • Simulação de inserção de 1.000 linhas


   
  • Simulação de inserção de 10.000 linhas  

     

    segunda-feira, 2 de março de 2015

    Abordando uma "New Feature" do particionamento por referência (Reference Partitioning) do Oracle 12c

    Por Eduardo Legatti

    Olá,

    No artigo de Fevereiro/2011 fiz uma breve introdução sobre o conceito de tabelas particionadas de forma a demonstrar as operações de manutenção envolvidas nas mesmas e no artigo de Abril/2011 demonstrei como particionar uma tabela de forma on-line. Já nos artigos de Junho/2011 e Maio/2012 eu demonstrei como utilizar o Interval Partitioning e o Reference Partitioning respectivamente, ambos introduzidos no Oracle 11g.

    Neste artigo, irei abordar uma inovação feita no Oracle 12c que permite utilizar o método de particionamento Reference Partitioning em uma tabela filha, tendo como pai, uma tabela particionada pelo método Interval Partitioning. No Oracle 11g isso não é possível.

    Apenas para recapitular, o interessante sobre o particionamento por referência (Reference Partitioning), é que o mesmo permite que uma restrição de integridade (Foreign Key) definida na tabela filha, seja utilizada como chave de partição. Isso permite que a tabela filha herde a chave de particionamento da tabela pai sem a necessidade de duplicar a coluna chave de partição da tabela pai na tabela filha. Em outras palavras, a tabela filha é particionada da mesma maneira que a tabela pai. Consultas SQL executadas nas tabelas pai e filha através de "Joins" terão seu desempenho melhorado, pois dependendo do predicado utilizado na consulta, o otimizador do Oracle irá determinar a partição envolvida na tabela pai (partition pruning) e identificar partição da tabela filha referente. No caso do particionamento por intervalo (Interval Partitioning), o mesmo automatiza a criação de novas partições de uma tabela por uma determinada faixa ou intervalo (RANGE), de forma que o DBA não precise se preocupar em criar novas partições manualmente.

    Abaixo, irei criar a tabela PEDIDO com 3 partições iniciais, tendo como coluna chave de partição, o campo DATA_PEDIDO. O tipo de particionamento utilizado será por faixa de valores (RANGE) utilizando o Interval Partitioning de forma que cada partição armazene os registros de um mês específico. Depois irei criar a tabela ITEM_PEDIDO utilizando o tipo de particionamento por referência (Reference Partitioning).

    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:37:34 2015
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Conectado a:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> create table pedido (
      2     cod_pedido  number primary key,
      3     data_pedido date not null,
      4     cod_cliente number not null)
      5  partition by range (data_pedido)
      6  interval(numtoyminterval(1,'month'))
      7  store in (users)
      8  (
      9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
     10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
     11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
     12  );
    
    Tabela criada.
    
    SQL> create table item_pedido (
      2     cod_pedido  number not null,
      3     cod_produto number not null,
      4     quantidade  number,
      5     constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
      6  partition by reference (fk_itempedido_pedido);
    create table item_pedido (
    *
    ERRO na linha 1:
    ORA-14659: O método de particionamento da tabela mãe não é suportado
    

    Com o erro demonstrado acima, é possível perceber que com o Oracle 11g não é possível criar uma tabela particionada por referência na qual a tabela pai (referenciada), esteja particionada por intervalo (Interval Partitioning). Iremos verificar abaixo que à partir do Oracle 12c isto já será possível.

    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 12.1.0.2.0 Production on Seg Mar 2 09:48:20 2015
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Conectado a:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> create table pedido (
      2     cod_pedido  number primary key,
      3     data_pedido date not null,
      4     cod_cliente number not null)
      5  partition by range (data_pedido)
      6  interval(numtoyminterval(1,'month'))
      7  store in (users)
      8  (
      9    partition p_2015_01 values less than (to_date('01/02/2015', 'dd/mm/yyyy')),
     10    partition p_2015_02 values less than (to_date('01/03/2015', 'dd/mm/yyyy')),
     11    partition p_2015_03 values less than (to_date('01/04/2015', 'dd/mm/yyyy'))
     12  );
    
    Tabela criada.
    
    SQL> create table item_pedido (
      2     cod_pedido  number not null,
      3     cod_produto number not null,
      4     quantidade  number,
      5     constraint fk_itempedido_pedido foreign key (cod_pedido) references pedido)
      6  partition by reference (fk_itempedido_pedido);
    
    Tabela criada.  
    

    Pronto. Como demonstrado acima, no Oracle 12c é possível criar uma tabela particionada por referência tendo como tabela referenciada, uma tabela particionada por RANGE através do Interval Partitioning.
    SQL>  select table_name,
      2          partitioning_type,
      3          partition_count,
      4          interval
      5     from user_part_tables;
    
    TABLE_NAME           PARTITION PARTITION_COUNT INTERVAL
    -------------------- --------- --------------- --------------------------
    PEDIDO               RANGE             1048575 NUMTOYMINTERVAL(1,'MONTH')
    ITEM_PEDIDO          REFERENCE         1048575 YES 

    Agora irei inserir dados nas duas tabelas de forma a demonstrar como o Oracle será capaz de criar automaticamente uma partição na tabela PEDIDO para acomodar registros com datas de um determinado mês, bem como demonstrar que a partição criada automaticamente será herdada pela tabela ITEM_PEDIDO.
     
    SQL> insert into pedido
      2  select level,
      3         to_date('31/12/2014')+level,
      4         trunc(dbms_random.value(1,1000))
      5    from dual
      6  connect by level <= 120;
    
    120 linhas criadas.
    
    SQL> insert into item_pedido
      2  select level,
      3         trunc(dbms_random.value(1,1000)),
      4         trunc(dbms_random.value(1,100))
      5    from dual
      6  connect by level <= 120;
    
    120 linhas criadas.
    
    SQL> exec dbms_stats.gather_schema_stats('SCOTT');
    
    Procedimento PL/SQL concluído com sucesso.
    
    SQL> select table_name,
      2         partition_name,
      3         high_value,
      4         num_rows
      5    from user_tab_partitions;
    
    TABLE_NAME           PARTITION_NAME  HIGH_VALUE                       NUM_ROWS
    -------------------- --------------- ------------------------------ ----------
    PEDIDO               P_2015_01       TO_DATE(' 2015-02-01 00:00:00'         31
    PEDIDO               P_2015_02       TO_DATE(' 2015-03-01 00:00:00'         28
    PEDIDO               P_2015_03       TO_DATE(' 2015-04-01 00:00:00'         31
    PEDIDO               SYS_P501        TO_DATE(' 2015-05-01 00:00:00'         30
    ITEM_PEDIDO          P_2015_01                                              31
    ITEM_PEDIDO          P_2015_02                                              28
    ITEM_PEDIDO          P_2015_03                                              31
    ITEM_PEDIDO          SYS_P501                                               30
    
    8 linhas selecionadas.
      
    Podemos ver pelo resultado acima que a partição SYS_P501 foi criada automaticamente na tabela PEDIDO para acomodar os registros referentes ao mês de Abril, e que essa mesma partição também foi herdada pela tabela ITEM_PEDIDO.

    segunda-feira, 2 de fevereiro de 2015

    Abordando a recuperação de um datafile sem backup através do RMAN e do SQL*Plus

    Por Eduardo Legatti

    Olá,

    Imagine um cenário na qual em uma manhã de segunda-feira às 08:00, foi criada uma nova tablespace em um banco de dados qualquer, operando no modo ARCHIVELOG. Tabelas foram criadas nessa tablespace, os dados foram inseridos e o sistema começou a funcionar. Imagine que neste ambiente existe uma rotina que executa um backup físico FULL via RMAN todas as noites às 22:00. Agora imagine que às 19:00 neste mesmo dia, o datafile pertencente à essa tablespace foi apagado acidentalmente ou, até mesmo, corrompido. Como não foi realizado nenhum backup desse datafile, já que faltavam ainda 3 horas para o início da rotina de backup, alguém poderia imaginar que todo trabalho realizado no dia estaria perdido.

    A boa notícia é que há uma luz no fim do túnel! O objetivo principal deste artigo será demonstrar como podemos recuperar um datafile que não possui backup. Embora se possa utilizar uma técnica em ambientes Linux/Unix para recuperar um arquivo deletado fazendo uso de "file descriptors", irei abordar neste artigo apenas técnicas que envolvam o uso do RMAN e do SQL*Plus para recuperar um arquivo de dados que não possui backup. Vale a pena salientar que para esta técnica funcionar, o banco de dados precisa estar operando no modo ARCHIVELOG e que os archive logs precisam existir desde a criação do datafile.

    Segue abaixo o ambiente de simulação na qual existe uma tabela T1 com cerca de 8 milhões de registros e de propriedade do schema SCOTT. A tabela T1 está devidamente criada na tablespace TBS_DATA_01, conforme demonstrado abaixo.
     
    SQL> select owner,table_name,tablespace_name,num_rows
      2    from dba_tables
      3   where owner='SCOTT';
    
    OWNER                TABLE_NAME           TABLESPACE_NAME           NUM_ROWS
    -------------------- -------------------- ----------------------- ----------
    SCOTT                T1                   TBS_DATA_01                8000000
    
    1 linha selecionada. 
      
    Em relação a tablespace TBS_DATA_01, foi criado apenas um arquivo de dados conforme demonstrado a seguir.

    SQL> select file#,name,status from v$datafile where file#=49;
    
    FILE# NAME                                              STATUS
    ----- ------------------------------------------------- -------
       49 /data/oradata/BD01/tbs_data_01.dbf                ONLINE
    
    1 linha selecionada.
     
    Recuperando um datafile sem backup usando o RMAN


    Utilizando o RMAN, irei me certificar da existência, ou não, de algum backup relacionado ao datafile pertencente à tablespace TBS_DATA_01.
     
    [oracle@linux1 ~]$ rman target /
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 2 10:19:05 2015
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: BD01 (DBID=618743438)
    
    RMAN> list backup of tablespace tbs_data_01;
    
    using target database control file instead of recovery catalog
    specification does not match any backup in the repository

    Confirmado que não há qualquer backup do datafile 49, irei simular a perda do mesmo apagando o arquivo.

    [oracle@linux1 ~]$ rm /data/oradata/BD01/tbs_data_01.dbf

    Após apagar o arquivo, podemos verificar abaixo que o Oracle já começa a refletir a ausência do mesmo.

    SQL> select count(*) from scott.t1;
    select count(*) from scott.t1
    *
    ERRO na linha 1:
    ORA-01116: erro ao abrir o arquivo 49 do banco de dados
    ORA-01110: 49 do arquivo de dados: '/data/oradata/BD01/tbs_data_01.dbf'
    ORA-27041: não é possível abrir arquivo
    Linux-x86_64 Error: 2: No such file or directory
    Additional information: 3

    Para comprovar que o datafile realmente foi apagado, irei colocar o mesmo no estado OFFLINE e imediatamente ONLINE.

    SQL> alter database datafile 49 offline;
    
    Banco de dados alterado.
    
    SQL> alter database datafile 49 online;
    alter database datafile 49 online
    *
    ERRO na linha 1:
    ORA-01157: não é possível identificar/bloquear arquivo de dados 49
    ORA-01110: 49 do arquivo de dados: '/data/oradata/BD01/tbs_data_01.dbf'
    
    SQL> select name,status from v$datafile where file#=49;
    
    NAME                                                STATUS
    --------------------------------------------------- -------
    /data/oradata/BD01/tbs_data_01.dbf                  RECOVER
    
    1 linha selecionada.

    Consultando novamente a view dinâmica de desempenho V$DATAFILE, podemos verificar que a coluna STATUS apresenta a palavra RECOVER, ou seja, que o arquivo de dados para ficar ONLINE novamente precisa de um restore/recover. Para demonstrar como poderemos recuperar o datafile, irei utilizar o RMAN (Data Recovery Advisor) conforme demonstrado a seguir.
     
    [oracle@linux1 ~]$ rman target / 
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 2 10:27:18 2015
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: BD01 (DBID=618743438)
    
    RMAN> list failure;
    
    using target database control file instead of recovery catalog
    
    List of Database Failures
    =========================
    
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- ------------------------------------------------------------------
    44802      HIGH     OPEN      02/02/2015    Um ou mais arquivos de dados que não são do sistema estão ausentes
    
    RMAN> advise failure;
    
    List of Database Failures
    =========================
    
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- ------------------------------------------------------------------
    44802      HIGH     OPEN      02/02/2015    Um ou mais arquivos de dados que não são do sistema estão ausentes
    
    analyzing automatic repair options; this may take some time
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=70 device type=DISK
    analyzing automatic repair options complete
    
    Not all specified failures can currently be repaired.
    The following failures must be repaired before advise for others can be given.
    
    Failure ID Priority Status    Time Detected Summary
    ---------- -------- --------- ------------- ------------------------------------------------------------------
    44802      HIGH     OPEN      02/02/2015    Um ou mais arquivos de dados que não são do sistema estão ausentes
    
    Mandatory Manual Actions
    ========================
    no manual actions available
    
    Optional Manual Actions
    =======================
    1. Se o arquivo /data/oradata/BD01/tbs_data_01.dbf foi renomeado ou movido intencionalmente, restaure-o
    
    Automated Repair Options
    ========================
    Option Repair Description
    ------ ------------------
    1      Restaurar e recuperar o arquivo de dados 49
      Strategy: O reparo inclui recuperacão completa de midia sem perda de dados
      Repair script: /u01/app/oracle/diag/rdbms/bd01/BD01/hm/reco_4238280548.hm
    


    Após a execução do comando "advise failure", foi criado o arquivo "reco_4238280548.hm" contendo instruções SQL que precisarão ser executadas conforme demonstrado abaixo: 

    [oracle@linux1 ~]$ cat /u01/app/oracle/diag/rdbms/bd01/BD01/hm/reco_4238280548.hm
       # restore and recover datafile
       sql 'alter database datafile 49 offline';
       restore datafile 49;
       recover datafile 49;
       sql 'alter database datafile 49 online';
     
    Como orientado pelo RMAN - Data Recovery Advisor, irei executar os comandos a seguir.

    RMAN> sql 'alter database datafile 49 offline';
    
    using target database control file instead of recovery catalog
    sql statement: alter database datafile 49 offline
    
    RMAN> restore datafile 49;
    
    Starting restore at 02/02/2015
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 device type=DISK
    
    creating datafile file number=49 name=/data/oradata/BD01/tbs_data_01.dbf
    restore not done; all files read only, offline, or already restored
    Finished restore at 02/02/2015
    
    RMAN> recover datafile 49; 
    Starting recover at 02/02/2015
    using channel ORA_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 7261 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7261_bcb23hpc_.arc
    archived log for thread 1 with sequence 7262 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7262_bcb2402b_.arc
    archived log for thread 1 with sequence 7263 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7263_bcb2qs11_.arc
    archived log for thread 1 with sequence 7264 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7264_bcb2qty2_.arc
    archived log for thread 1 with sequence 7265 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7265_bcb2r1kl_.arc
    archived log for thread 1 with sequence 7266 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7266_bcb2r3p6_.arc
    archived log for thread 1 with sequence 7267 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7267_bcb2r5ro_.arc
    archived log for thread 1 with sequence 7268 is already on disk as file /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7268_bcb2r7yg_.arc
    archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7261_bcb23hpc_.arc thread=1 sequence=7261
    archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7262_bcb2402b_.arc thread=1 sequence=7262
    archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7263_bcb2qs11_.arc thread=1 sequence=7263
    archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7264_bcb2qty2_.arc thread=1 sequence=7264
    archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7265_bcb2r1kl_.arc thread=1 sequence=7265
    archived log file name=/FRA/BD01/archivelog/2015_02_02/o1_mf_1_7266_bcb2r3p6_.arc thread=1 sequence=7266
    media recovery complete, elapsed time: 00:00:05
    Finished recover at 02/02/2015
    

    RMAN> sql 'alter database datafile 49 online';
    
    sql statement: alter database datafile 49 online
    

    Pronto. Após a execução das instruções SQL acima foi possível notar que o RMAN criou um novo datafile e aplicou os archive logs da sequência 7261 até 7266. Para comprovar que a recuperação do datafile foi bem sucedida, segue abaixo o resultado de um SELECT COUNT(*) na tabela T1 de propriedade do schema SCOTT.

    SQL> select count(*) from scott.t1;
    
      COUNT(*)
    ----------
       8000000
    
    1 linha selecionada.
    


    Recuperando um datafile sem backup usando o SQL*Plus


    Agora irei utilizar o SQL*PLus para demonstrar como recuperar o datafile que foi apagado. Para recuperar o arquivo de dados, será necessário basicamente criar um novo datafile com o comando ALTER DATABASE CREATE DATAFILE e aplicar os archive logs necessários utilizando a opção AUTO, conforme demonstração a seguir:
     
    [oracle@linux1 ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 2 10:44:21 2015
    
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
    Conectado a:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> alter database datafile 49 offline drop;
    
    Banco de dados alterado.
    
    SQL> alter database create datafile '/data/oradata/BD01/tbs_data_01.dbf';
    
    Banco de dados alterado.
    
    SQL> recover datafile 49;
    ORA-00279: alterar 7840579434155 gerado em 01/13/2015 09:52:10 necessario para o thread 1
    ORA-00289: sugestão :
    /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7261_bcb23hpc_.arc
    ORA-00280: alterar 7840579434155 para o thread 1 esta na sequencia  #7261
    
    Especificar log: {=nome de arquivo | sugerido | AUTO | CANCEL}
    AUTO
    ORA-00279: alterar 7840579434734 gerado em 01/13/2015 09:58:39 necessario para o thread 1
    ORA-00289: sugestão :
    /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7262_bcb2402b_.arc
    ORA-00280: alterar 7840579434734 para o thread 1 esta na sequencia  #7262
    
    ORA-00279: alterar 7840579434929 gerado em 01/13/2015 09:58:55 necessario para o thread 1
    ORA-00289: sugestão :
    /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7263_bcb2qs11_.arc
    ORA-00280: alterar 7840579434929 para o thread 1 esta na sequencia  #7263
    
    ORA-00279: alterar 7840579435636 gerado em 01/13/2015 10:09:29 necessario para o thread 1
    ORA-00289: sugestão :
    /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7264_bcb2qty2_.arc
    ORA-00280: alterar 7840579435636 para o thread 1 esta na sequencia  #7264
    
    ORA-00279: alterar 7840579435639 gerado em 01/13/2015 10:09:30 necessario para o thread 1
    ORA-00289: sugestão :
    /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7265_bcb2r1kl_.arc
    ORA-00280: alterar 7840579435639 para o thread 1 esta na sequencia  #7265
    
    ORA-00279: alterar 7840579435644 gerado em 01/13/2015 10:09:37 necessario para o thread 1
    ORA-00289: sugestão :
    /FRA/BD01/archivelog/2015_02_02/o1_mf_1_7266_bcb2r3p6_.arc
    ORA-00280: alterar 7840579435644 para o thread 1 esta na sequencia  #7266
    
    Log aplicado.
    Recuperação de midia concluida.
    

    SQL> alter database datafile 49 online;
    
    Banco de dados alterado.

    Pronto. Após a execução das instruções SQL acima podemos verificar abaixo, consultado a tabela T1, que o datafile foi recuperado com sucesso.

    SQL> select count(*) from scott.t1;
    
      COUNT(*)
    ----------
       8000000
    
    1 linha selecionada.
     

    segunda-feira, 5 de janeiro de 2015

    Capturando erros DML/DDL através da opção "Error Logging" disponível no SQL*Plus do Oracle 11g

    Por Eduardo Legatti

    Olá,

    No artigo de Julho/2010 eu demonstrei de forma prática, como capturar os erros gerados pelas instruções SQL executadas no banco de dados, fazendo uso do gatilho (trigger) de sistema chamado AFTER SERVERERROR. O objetivo da trigger é capturar quaisquer erros gerados pelas instruções SQL executadas de todas as sessões conectadas no Oracle. O SQL*Plus do Oracle 11g veio com uma inovação um pouco parecida com esse propósito. A diferença é que somente as instruções executadas pela sessão que está executando o SQL*PLus é que será auditada. O parâmetro ERRORLOGGING quando setado para ON, irá criar uma tabela chamada de SPERRORLOG no schema do usuário que está conectado pleo SQL*Plus. Par padrão, o parâmetro é setado para OFF. Segue abaixo uma demonstração na qual irei conectar com o usuário SCOTT.
     
    C:\>sqlplus scott/tiger
    
    SQL*Plus: Release 11.2.0.1.0 Production on Seg Jan 5 08:17:09 2015
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Conectado a:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> show errorlogging;
    errorlogging is OFF
    
    SQL> set errorlogging ON;
    
    SQL> show errorlogging;
    errorlogging is ON TABLE SCOTT.SPERRORLOG
    

    Após setar o parâmetro ERRORLOGGING para ON acima, podemos ver abaixo qua a tabela SPERRORLOG foi criada. Vale a pena salientar que essa tabela ficará criada até que a mesma seja dropada manualmente.

    SQL> desc SPERRORLOG
     Nome                          Nulo?    Tipo
     ----------------------------- -------- ------------------------
     USERNAME                               VARCHAR2(256)
     TIMESTAMP                              TIMESTAMP(6)
     SCRIPT                                 VARCHAR2(1024)
     IDENTIFIER                             VARCHAR2(256)
     MESSAGE                                CLOB
     STATEMENT                              CLOB
    

    Irei simular a execução de uma instrução SQL e também a execução de um script SQL afim de gerar alguns erros:

    SQL> create table t1 (id numberr);
    create table t1 (id numberr)
    *
    ERRO na linha 1:
    ORA-00902: tipo de dados inválido
    
    SQL> @script01.sql
    select * from t2 where id=1
    *
    ERRO na linha 1:
    ORA-00942: a tabela ou view não existe
    

    Após a execução das instruções SQL acima, poderemos consultar a tabela SPERRORLOG.

    SQL> select timestamp,script,message,statement from sperrorlog; 
    TIMESTAMP         SCRIPT        MESSAGE                                STATEMENT
    ----------------- ------------- -------------------------------------- ----------------------------
    05/01/15 08:21:54               ORA-00902: tipo de dados inválido      create table t1 (id numberr)
    05/01/15 08:22:59 script01.sql  ORA-00942: a tabela ou view não existe select * from t2 where id=1
    
    2 linhas selecionadas.
    

    Por fim, vale a pena salientar que os registros inseridos na tabela SPERRORLOG participam da mesma transação da instrução SQL que originou o erro. Portanto, caso um comando de ROLLBACK seja emitido após uma instrução gerar algum erro, a instrução não será persistida na tabela SPERRORLOG.

    Postagens populares