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


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.

    segunda-feira, 1 de dezembro de 2014

    Mover segmentos de LOB deixam os índices da tabela inutilizáveis (UNUSABLE)

    Por Eduardo Legatti

    Olá,

    No artigo de Janeiro/2014 eu abordei sobre a realocação de objetos de bancos de dados entre tablespace distintas. Dentre os objetos que foram abordados no artigo, estão as tabelas, os índices e os segmentos de LOB. Neste artigo irei tratar mais especificamente sobre as consequências de se mover um segmento LOB. Vale a pena salientar que segmentos LOB tem sua origem em colunas de tabelas com tipos de dados (CLOB e BLOB). Abaixo, realizarei uma simulação na qual irei mover um segmento de LOB proveniente de uma coluna BLOB, utilizando o Oracle 12c (12.1.0.2).
     
    C:\>sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.2.0 Production on Seg Dez 1 13:14:21 2014
    
    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> alter session set current_schema=SCOTT;
    
    Sessão alterada.
    
    SQL> create table t1 (id number, nome varchar2(100), imagem blob);
    
    Tabela criada.
    
    SQL> alter table t1 add constraint t1_pk primary key (id);
    
    Tabela alterada.
    
    SQL> create index t1_idx_nome on t1 (nome);
    
    Índice criado.
    
    SQL> insert into t1 values (1,'Miguel',empty_blob());
    
    1 linha criada.
    
    SQL> insert into t1 values (2,'Laura',empty_blob());
    
    1 linha criada.
    
    SQL> commit;
    
    Commit concluído.
     
    Após a criação da tabela T1 no schema SCOTT, podemos verificar abaixo que os índices que foram criados na tabela estão com os status VALID.

    SQL> select rowid,owner,index_name,status from dba_indexes where owner='SCOTT';
    
    OWNER      INDEX_NAME                     STATUS
    ---------- ------------------------------ --------
    SCOTT      T1_PK                          VALID
    SCOTT      T1_IDX_NOME                    VALID
    SCOTT      SYS_IL0000020439C00003$$       VALID
      
    Abaixo estão listados os registros da tabela com os respectivos ROWIDs de cada linha.
     
    SQL> select rowid,id,nome from t1;
    
    ROWID                      ID NOME
    ------------------ ---------- --------------------
    AAAFAFAAEAAAACHAAA          1 Miguel
    AAAFAFAAEAAAACHAAB          2 Laura
    


    O objetivo agora será mover o segmento de LOB, proveniente da coluna IMAGEM que está atualmente armazenado na tablespace USERS, para a tablespaces TS_LOB. Como demonstrado abaixo, poderemos consultar a view DBA_LOBS para verificar em qual tablespace está armazenado o segmento de LOB.

    SQL> select owner,table_name,column_name,tablespace_name
      2  from dba_lobs
      3  where owner='SCOTT';
    
    OWNER      TABLE_NAME      COLUMN_NAME     TABLESPACE_NAME
    ---------- --------------- --------------- --------------------
    SCOTT      T1              IMAGEM          USERS
    


    Apenas para fins de demonstração, irei utilizar a instrução SQL abaixo para automatizar a criação do comando ALTER TABLE que irá mover o segmento de LOB para a tablespace TS_LOB. Vale a pena salientar que essa instrução é útil quando queremos mover vários segmentos de LOB.

    SQL>   SELECT   'alter table '
      2           || owner
      3           || '.'
      4           || table_name
      5           || ' move lob ('
      6           || column_name
      7           || ') store as (tablespace TS_LOB);' as DDL
      8      FROM dba_lobs
      9      where owner='SCOTT'
     10      ORDER BY owner, table_name;
    
    DDL
    --------------------------------------------------------------------
    alter table SCOTT.T1 move lob (IMAGEM) store as (tablespace TS_LOB);
      
    Vamos então a execução do comando ALTER TABLE ... MOVE LOB.
     
    SQL> alter table SCOTT.T1 move lob (IMAGEM) store as (tablespace TS_LOB);
    
    Tabela alterada.
    
    SQL> select rowid,id,nome from t1;
    
    ROWID                      ID NOME
    ------------------ ---------- ---------------------
    AAAFALAAEAAAACzAAA          1 Miguel
    AAAFALAAEAAAACzAAB          2 Laura  

    Após a execução do comando ALTER TABLE, podemos verificar abaixo que os ROWIDs das linhas da tabela T1 mudaram. Como consequência, os índices da tabela ficaram com status UNUSABLE e assim como acontece quando movemos uma tabela, será necessário reconstruir os índices (REBUILD). 
     
    SQL> select owner,index_name,status from dba_indexes where owner='SCOTT';
    
    OWNER      INDEX_NAME                     STATUS
    ---------- ------------------------------ --------
    SCOTT      T1_PK                          UNUSABLE
    SCOTT      T1_IDX_NOME                    UNUSABLE
    SCOTT      SYS_IL0000020439C00003$$       VALID
    


    A não ser que o parâmetro SKIP_UNUSABLE_INDEXES esteja setado como TRUE, qualquer comando DML efetuado na tabela irá ocasionar o erro ORA-01502 como demonstrado abaixo.

    SQL> insert into t1 values (3,'Angela',empty_blob());
    insert into t1 values (3,'Angela',empty_blob())
    *
    ERRO na linha 1:
    ORA-01502: índice 'SCOTT.T1_PK' ou a sua partição está em estado não utilizável 
    

    Para resolver o problema, será necessário realizar o REBUILD dos índices conforme demonstrado abaixo.

    SQL> alter index t1_pk rebuild;
    
    Índice alterado.
    
    SQL> alter index t1_idx_nome rebuild;
    
    Índice alterado.
    
    SQL> select owner,index_name,status from dba_indexes where owner='SCOTT';
    
    OWNER      INDEX_NAME                     STATUS
    ---------- ------------------------------ --------
    SCOTT      T1_PK                          VALID
    SCOTT      T1_IDX_NOME                    VALID
    SCOTT      SYS_IL0000020439C00003$$       VALID
    

    Dependendo do tamanho de uma tabela e do tamanho dos segmentos de LOB envolvidos, mover um segmento de LOB de modo OFFLINE poderá ocasionar efeitos indesejados no ambiente pois a tabela ficará bloqueada (LOCK) durante a realocação do segmento de LOB. Para evitar esse problema eu aconselho realizar a realocação ONLINE através do uso da package DBMS_REDEFINITION. Os artigos de Abril/2011 e Dezembro/2012 eu abordo o uso desta package.

    Por fim, existe uma nota (Doc ID 1228324.1) no My Oracle Support (Metalink) que aborda esse comportamento dos índices ficarem no estado UNUSABLE ao mover um segmento de LOB. A nota não é muito clara se isso seria um bug do Oracle ou um bug na documentação que não chama a atenção para esse comportamento.
     

    domingo, 2 de novembro de 2014

    Abordando o RMAN - MULTISECTION BACKUP disponível a partir do Oracle 11g

    Por Eduardo Legatti

    Olá,

    Quando falamos de paralelismo de backups no RMAN, geralmente estamos falando de paralelização de arquivos, ou seja, cada canal (channel) trabalhando em um determinado arquivo, seja ele um datafile, controlfile ou spfile. No RMAN, quando executamos um comando, a quantidade de canais disponíveis para uso em um determinado dispositivo (device) é que determina se a operação de leitura ou escrita realizada será feita em paralelo. Quando uma tarefa é realizada em paralelo, o backup dos arquivos de banco de dados é realizada por mais de um canal (cada canal trabalhando em um arquivo). Bom, e se quisermos paralelizar o backup de apenas um arquivo, é possível? A partir do Oracle 11g é possível paralelizar a realização de backups de um único arquivo, ou seja, mais de um canal realizando backup de um único arquivo. Esta característica se chama MULTISECTION BACKUP e pode ser realizada através da cláusula SECTION SIZE do comando BACKUP. No mais, o objetivo deste artigo será demonstrar como os Multisection backups podem melhorar a performance em relação ao tempo na realização de backups físicos.

    [oracle]$ sqlplus / as sysdba
    
    SQL*Plus: Release 11.2.0.3.0 Production on Sun Nov 2 11:30:14 2014
    
    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> select file#,name,bytes/1024/1024/1024 as GB from v$datafile where file#=10;
    
         FILE# NAME                                       GB
    ---------- ---------------------------------- ----------
            10 /oradata/BD01/users01.dbf          7,67773438
    

    Como exemplo, irei realizar o backup do datafile 10 listado acima que possui tamanho de 7,6 GB.


    [oracle]$ rman target /
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Nov Sun 2 11:41:30 2014
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: BD01 (DBID=618743438)
    
    RMAN> show all;
    
    using target database control file instead of recovery catalog
    RMAN configuration parameters for database with db_unique_name BD01 are:
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
    CONFIGURE BACKUP OPTIMIZATION ON;
    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 BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 7;
    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 

    Como demonstrado acima pelo comando SHOW ALL, atualmente o paralelismo está configurado para 7, ou seja, 7 canais serão alocados pelo RMAN no início do processo de backup ou recover.

    RMAN> backup datafile 10;
    
    Starting backup at 02/11/2014 11:41:51
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 device type=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: SID=69 device type=DISK
    allocated channel: ORA_DISK_3
    channel ORA_DISK_3: SID=70 device type=DISK
    allocated channel: ORA_DISK_4
    channel ORA_DISK_4: SID=101 device type=DISK
    allocated channel: ORA_DISK_5
    channel ORA_DISK_5: SID=132 device type=DISK
    allocated channel: ORA_DISK_6
    channel ORA_DISK_6: SID=162 device type=DISK
    allocated channel: ORA_DISK_7
    channel ORA_DISK_7: SID=193 device type=DISK
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 02/11/2014 11:41:53
    channel ORA_DISK_1: finished piece 1 at 02/11/2014 11:43:41
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T114156_b3bm9ng8_.bkp
    channel ORA_DISK_1: backup set complete, elapsed time: 00:01:50
    Finished backup at 02/11/2014 11:43:41

    Após a finalização do backup acima, podemos perceber que realmente foram alocados 7 canais (ORA_DISK_1-7), mas somente o canal ORA_DISK_1 foi utilizado na realização do backup do datafile, ou seja, não houve paralelismo na operação. Foram gastos 00:01:50 (110 segundos) para realização do backup. Abaixo irei executar a mesma operação, mas agora informando a opção SECTION SIZE 1G, o que significa que eu quero que os backup pieces gerados tenham no máximo 1 GB de tamanho cada.

    [oracle]$ rman target /
    
    Recovery Manager: Release 11.2.0.3.0 - Production on Sun Nov 2 11:50:52 2014
    
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    
    connected to target database: BD01 (DBID=618743438)
    
    RMAN> backup datafile 10 section size 1G;
    
    Starting backup at 02/11/2014 11:51:09
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 device type=DISK
    allocated channel: ORA_DISK_2
    channel ORA_DISK_2: SID=70 device type=DISK
    allocated channel: ORA_DISK_3
    channel ORA_DISK_3: SID=69 device type=DISK
    allocated channel: ORA_DISK_4
    channel ORA_DISK_4: SID=102 device type=DISK
    allocated channel: ORA_DISK_5
    channel ORA_DISK_5: SID=131 device type=DISK
    allocated channel: ORA_DISK_6
    channel ORA_DISK_6: SID=162 device type=DISK
    allocated channel: ORA_DISK_7
    channel ORA_DISK_7: SID=193 device type=DISK
    channel ORA_DISK_1: starting compressed full datafile backup set
    channel ORA_DISK_1: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 1 through 131072
    channel ORA_DISK_1: starting piece 1 at 02/11/2014 11:51:11
    channel ORA_DISK_2: starting compressed full datafile backup set
    channel ORA_DISK_2: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 131073 through 262144
    channel ORA_DISK_2: starting piece 2 at 02/11/2014 11:51:12
    channel ORA_DISK_3: starting compressed full datafile backup set
    channel ORA_DISK_3: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 262145 through 393216
    channel ORA_DISK_3: starting piece 3 at 02/11/2014 11:51:12
    channel ORA_DISK_4: starting compressed full datafile backup set
    channel ORA_DISK_4: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 393217 through 524288
    channel ORA_DISK_4: starting piece 4 at 02/11/2014 11:51:12
    channel ORA_DISK_5: starting compressed full datafile backup set
    channel ORA_DISK_5: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 524289 through 655360
    channel ORA_DISK_5: starting piece 5 at 02/11/2014 11:51:12
    channel ORA_DISK_6: starting compressed full datafile backup set
    channel ORA_DISK_6: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 655361 through 786432
    channel ORA_DISK_6: starting piece 6 at 02/11/2014 11:51:12
    channel ORA_DISK_7: starting compressed full datafile backup set
    channel ORA_DISK_7: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 786433 through 917504
    channel ORA_DISK_7: starting piece 7 at 02/11/2014 11:51:12
    channel ORA_DISK_2: finished piece 2 at 02/11/2014 11:51:12
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv07h_.bkp
    channel ORA_DISK_2: backup set complete, elapsed time: 00:00:00
    channel ORA_DISK_2: starting compressed full datafile backup set
    channel ORA_DISK_2: specifying datafile(s) in backup set
    input datafile file number=00010 name=/oradata/BD01/users01.dbf
    backing up blocks 917505 through 1006336
    channel ORA_DISK_2: starting piece 8 at 02/11/2014 11:51:12
    channel ORA_DISK_3: finished piece 3 at 02/11/2014 11:51:12
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv08z_.bkp
    channel ORA_DISK_3: backup set complete, elapsed time: 00:00:00
    channel ORA_DISK_4: finished piece 4 at 02/11/2014 11:51:12
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cl_.bkp
    channel ORA_DISK_4: backup set complete, elapsed time: 00:00:00
    channel ORA_DISK_5: finished piece 5 at 02/11/2014 11:51:12
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cx_.bkp
    channel ORA_DISK_5: backup set complete, elapsed time: 00:00:00
    channel ORA_DISK_6: finished piece 6 at 02/11/2014 11:51:12
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0f8_.bkp
    channel ORA_DISK_6: backup set complete, elapsed time: 00:00:00
    channel ORA_DISK_1: finished piece 1 at 02/11/2014 11:51:15
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmtzp4_.bkp
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
    channel ORA_DISK_2: finished piece 8 at 02/11/2014 11:51:27
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0lj_.bkp
    channel ORA_DISK_2: backup set complete, elapsed time: 00:00:15
    channel ORA_DISK_7: finished piece 7 at 02/11/2014 11:52:34
    piece handle=/FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0h1_.bkp
    channel ORA_DISK_7: backup set complete, elapsed time: 00:01:25
    Finished backup at 02/11/2014 11:52:34

    Após a finalização do backup acima, podemos perceber que foram alocados 7 canais (ORA_DISK_1-7), e que todos eles foram utilizados na realização do backup do datafile, ou seja, houve paralelismo na operação. Cada canal iniciou a operação de backup de uma determinada faixa de blocos do datafile. Foram gastos 00:01:25 (85 segundos) para realização do backup, ou seja, 25 segundos a menos comparado com a primeira simulação, o que representa uma melhora na performance de cerca de 30%. A figura abaixo demonstra a ação dos canais durante a operação de backup. Vale a pena salientar que é possível constatar que o canal ORA_DISK_2 iniciou a operação do backup piece 8 após finalizar o backup piece 2.


    Para finalizar, segue abaixo a evidência dos backups pieces que foram gerados durante a realização do backup do datafile 10. O comando LIST mostra que foram gerados 8 backup pieces.
    RMAN> list backup of datafile 10; 
    using target database control file instead of recovery catalog
    
    List of Backup Sets
    ===================
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ -------------------
    5480    Full    544.80M    DISK        00:01:25     02/11/2014 11:52:34
      List of Datafiles in backup set 5480
      File LV Type Ckp SCN       Ckp Time            Name
      ---- -- ---- ------------- ------------------- -------------------------
      10      Full 7840571897377 02/11/2014 11:51:11 /oradata/BD01/users01.dbf
    
      Backup Set Copy #1 of backup set 5480
      Device Type Elapsed Time Completion Time     Compressed Tag
      ----------- ------------ ------------------- ---------- ------------------
      DISK        00:01:25     02/11/2014 11:52:34 YES        TAG20141008T115111
    
        List of Backup Pieces for backup set 5480 Copy #1
        BP Key  Pc# Status      Piece Name
        ------- --- ----------- ---------------------------------------------------------------------------
        5499    1   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmtzp4_.bkp
        5494    2   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv07h_.bkp
        5495    3   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv08z_.bkp
        5496    4   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cl_.bkp
        5497    5   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0cx_.bkp
        5498    6   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0f8_.bkp
        5501    7   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0h1_.bkp
        5500    8   AVAILABLE   /FRA/BD01/backupset/2014_11_02/o1_mf_nnndf_TAG20141008T115111_b3bmv0lj_.bkp
    

    Postagens populares