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


quarta-feira, 15 de fevereiro de 2017

Tuning de intruções SQL: Prestar atenção ao plano de execução é o primeiro passo

Por Eduardo Legatti

Olá,

Em algum momento, todo DBA vai passar por aquela experiência de tentar melhorar a performance de uma instrução SQL e acreditar que o que foi feito até então vai resolver o problema de performance. Mas aí, após fazer as melhorias e executar o SQL, percebe-se que o mesmo plano de execução de baixa performance continua sendo utilizado. O objetivo desse artigo é chamar a atenção para que prestemos realmente mais atenção ao plano de execução gerado por uma instrução SQL antes de quebrar a cabeça com outras tentativas.

Avaliando a consulta abaixo na qual a performance não estava aceitável, é possível perceber alguns filtros e um JOIN entre as tabelas T1 e T2.

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND B.VALUE = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

Analisando estruturalmente as tabelas, verifiquei que as colunas que estão fazendo JOIN (B.VALUE = A.EMP_CODE)  são de tipos de dados e tamanhos diferentes.

SQL> desc T1
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 EMP_CODE                         NOT NULL NUMBER(15)
 NAME                                      VARCHAR2(255)
 HASH                                      VARCHAR2(32)

SQL> desc T2
 Nome                            Nulo?    Tipo
 ------------------------------- -------- ---------------------------
 DEP_CODE                        NOT NULL NUMBER(20)
 VALUE                                    VARCHAR2(4000)

Verificando os dados da tabela T2, verifiquei que não seria possível criar um índice na coluna VALUE pois o erro "ORA-01450: maximum key length (string) exceeded" seria emitido devido a limitação existente no Oracle do tamanho máximo de valores indexados de acordo com o tamanho do bloco de dados usado pela tablespace da tabela (8 KB neste caso). Bom, a idéia então foi criar um índice baseado em função na qual a mesma limitaria o tamanho da coluna VARCHAR2(4000) para o tamanho da coluna numérica da tabela T1 (EMP_CODE). Como o tamanho da coluna EMP_CODE da tabela T1 é NUMBER(15), irei criar um índice de função SUBSTR(VALUE,1,15) na tabela T2. Como na instrução SQL existem outros filtros, irei criar 2 índices conforme demonstrado abaixo.

SQL> create index idx_name_hash on t1 (name,hash) tablespace tbs_indx noparallel;
SQL> create index idx_substr_value_depcode on t2 (substr(value,1,15),dep_code) tablespace tbs_indx noparallel;

Após criados os índices e alterada a instrução SQL para utilizar a função SUBSTR, segue abaixo o plano de execução gerado:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = A.EMP_CODE
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926

11 linhas selecionadas.

Decorrido: 00:00:42

Plano de Execução
----------------------------------------------------------
Plan hash value: 540149683

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   1 |  COUNT                        |                            |       |       |            |          |
|*  2 |   HASH JOIN                   |                            |     3 |   258 |   103K  (1)| 00:20:42 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                         |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH              |     3 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | T2                         |  5251K|    88M|   103K  (1)| 00:20:42 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."EMP_CODE"=TO_NUMBER(SUBSTR("VALUE",1,15)))
   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   5 - filter("B"."DEP_CODE"=13)

Foi possível verificar acima que a instrução SQL executou em 42 segundos, o que é péssimo, e que o índice IDX_NAME_HASH foi utilizado. No entanto, o índice de função criado na tabela T2 não foi usado e por isso está sendo realizado uma operação de TABLE ACCESS FULL na mesma em cerca de 5 milhões de linhas ao realizar uma operação de HASH JOIN. A questão é: porque o otimizador não utilizou o índice de função? Após alguns testes utilizando HINTS para forçar o uso do índice entre outras tentativas, consegui a tempo perceber que a resposta estava no próprio plano de execução na seção "Predicate Information".

É possível perceber que o Oracle implicitamente converteu o tipo de dado utilizando a função TO_NUMBER de modo a fazer o JOIN com a coluna EMP_CODE que é NUMBER(15), o que é compreensível. Neste caso, o problema pode ser resolvido com 2 opções: criar o índice como TO_NUMBER(SUBSTR(VALUE,1,15)) na tabela T1 ou utilizar a função TO_CHAR na coluna EMP_CODE como demonstrado abaixo:

SQL> SELECT ROWNUM id,
  2         NAME,
  3         HASH,
  4         EMP_CODE,
  5         DEP_CODE,
  6         VALUE
  7    FROM T1 A, T2 B
  8   WHERE     A.NAME = 'file.pdf'
  9         AND A.HASH = 'F847F75E563EC732C61DB76C239BC34C'
 10         AND SUBSTR(B.VALUE,1,15) = TO_CHAR(A.EMP_CODE)
 11         AND B.DEP_CODE = 13;

     ID NAME                 HASH                               EMP_CODE   DEP_CODE VALUE
------- -------------------- -------------------------------- ---------- ---------- --------------
      1 file.pdf             F847F75E563EC732C61DB76C239BC34C     468922   33918406 468922
      2 file.pdf             F847F75E563EC732C61DB76C239BC34C     468926   33918658 468926
      3 file.pdf             F847F75E563EC732C61DB76C239BC34C     363423   23650082 363423
      4 file.pdf             F847F75E563EC732C61DB76C239BC34C     363427   23652704 363427
      5 file.pdf             F847F75E563EC732C61DB76C239BC34C     363428   23653157 363428
      6 file.pdf             F847F75E563EC732C61DB76C239BC34C     363430   23654312 363430
      7 file.pdf             F847F75E563EC732C61DB76C239BC34C     363770   23759064 363770
      8 file.pdf             F847F75E563EC732C61DB76C239BC34C     363793   23765071 363793
      9 file.pdf             F847F75E563EC732C61DB76C239BC34C     372161   24406201 372161
     10 file.pdf             F847F75E563EC732C61DB76C239BC34C     372165   24406453 372165
     11 file.pdf             F847F75E563EC732C61DB76C239BC34C     372169   24406707 372169

11 linhas selecionadas.

Decorrido: 00:00:00.09

Plano de Execução
----------------------------------------------------------
Plan hash value: 424040904

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   1 |  COUNT                        |                              |       |       |            |          |
|   2 |   NESTED LOOPS                |                              |     3 |   258 |    21   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1                           |     3 |   177 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_NAME_HASH                |     3 |       |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2                           |     1 |    27 |     5   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_SUBSTR_VALUE_DEPCODE     |     4 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."NAME"='file.pdf' AND "A"."HASH"='F847F75E563EC732C61DB76C239BC34C')
   6 - access(SUBSTR("VALUE",1,15)=TO_CHAR("A"."EMP_CODE") AND "B"."DEP_CODE"=13)


Pronto. Agora a instrução SQL executou em menos de 1 segundo com a utilização do índice IDX_SUBSTR_VALUE_DEPCODE.

sexta-feira, 13 de janeiro de 2017

Funções analíticas no Oracle: RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG e LEAD

Por Eduardo Legatti

Olá,

Neste artigo irei abordar de forma simples e direta exemplos de uso de algumas funções analíticas que podemos utilizar nas instruções SQL com o Oracle. Ás vezes muitas dessas funções são ignoradas por quem está construindo uma instrução SQL. O uso de funções analíticas podem ajudar muito a tornar uma instrução SQL que até então é complexa em uma versão muito mais simples. Dentre os exemplos de funções analíticas que irei abordar estão RANK, DENSE_RANK, ROW_NUMBER, FIRST_VALUE, LAST_VALUE, LAG e LEAD.

Todos os exemplos das funções analíticas terão como base a tabela T1 abaixo na qual existem 3 grupos de ID (10, 20 e 30) com seus respectivos valores na coluna VALUE. Vale a pena salientar que a tabela possui uma linha duplicada intencionalmente (ID: 20 e VALUE: 202).

SQL> select * from t1 order by 1,2;

        ID      VALUE
---------- ----------
        10        101
        10        102
        10        103
        20        201
        20        202
        20        202
        20        203
        30        301
        30        302
        30        303

10 linhas selecionadas.

RANK


A função analítica RANK tem como objetivo retornar a classificação de cada linha de um conjunto de resultados. Por exemplo, abaixo irei criar classificar ou criar um rank para as linhas da tabela T1 de acordo com os valores da coluna VALUE ordenados de forma ascendente. Vale a pena salientar que valores repetidos terão o mesmo rank conforme observado nas linhas de ID: 20 e VALUE: 202 o que irá gerar uma quebra na sequência do rank, ou seja, a sequencia de número 6 foi perdida.

SQL> select id,
  2         value,
  3         rank() over (order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          4
        20        202          5
        20        202          5
        20        203          7
        30        301          8
        30        302          9
        30        303         10

10 linhas selecionadas.

Caso a intenção seja o de gerar a mesma classificação de acordo com os valores da coluna VALUE ordenados de forma ascendente, mas agora agrupando por ID, bastará apenas utilizar a palavra chave PARTITION BY. Como dito anteriormente, valores repetidos terão o mesmo rank conforme observado nas linhas de ID: 20 e VALUE: 202 o que irá gerar uma quebra na sequência do rank, ou seja, a sequencia de número 3 foi perdida.

SQL> select id,
  2         value,
  3         rank() over (partition by ID order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          1
        20        202          2
        20        202          2
        20        203          4
        30        301          1
        30        302          2
        30        303          3

10 linhas selecionadas.


DENSE_RANK

A função analítica DENSE_RANK age da mesma forma que a função RANK, porém com a diferença nos valores de classificação do rank. Os valores gerados serão consecutivos, mas os valores duplicados ainda continuarão com rank repetidos.
 
SQL> select id,
  2         value,
  3         dense_rank() over (order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          4
        20        202          5
        20        202          5
        20        203          6
        30        301          7
        30        302          8
        30        303          9

10 linhas selecionadas.

SQL> select id,
  2         value,
  3         dense_rank() over (partition by ID order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          1
        20        202          2
        20        202          2
        20        203          3
        30        301          1
        30        302          2
        30        303          3

10 linhas selecionadas.


ROW_NUMBER

A função analítica ROW_NUMBER tem como objetivo gerar um valor único para a linha retornada da mesma forma que a pseudo coluna ROWNUM faz. Neste caso os valores da classificação serão sempre consecutivos.

SQL> select rownum from dual connect by level <=10;

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 linhas selecionadas.


SQL> select id,
  2         value,
  3         row_number() over (order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          4
        20        202          5
        20        202          6
        20        203          7
        30        301          8
        30        302          9
        30        303         10

10 linhas selecionadas.


SQL> select id,
  2         value,
  3         row_number() over (partition by ID order by VALUE) rank
  4  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        10        103          3
        20        201          1
        20        202          2
        20        202          3
        20        203          4
        30        301          1
        30        302          2
        30        303          3

10 linhas selecionadas.

Um uso muito comum para uso da função analítica ROW_NUMBER é utilizá-la em instruções SQL que precisam obter valores de um conjunto de dados no qual precisam ser retornados os N maiores ou menores valores de cada grupo. Por exemplo, o SQL abaixo irá retornar as duas linhas com os maiores valores (VALUE) de cada grupo (ID).

SQL> select *
  2   from (select id,
  3                value,
  4                row_number () over (partition by ID order by VALUE) rank
  5           from t1)
  6  where rank <= 2;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101          1
        10        102          2
        20        201          1
        20        202          2
        30        301          1
        30        302          2

6 linhas selecionadas.


FIRST_VALUE

A função analítica FIRST_VALUE irá retornar o primeiro valor de um conjunto de dados ordenado. Por exemplo, abaixo irei mostrar na coluna RANK o primeiro valor de VALUE retornado de cada grupo ID. Como a ordenação é ascendente pela coluna VALUE (order by VALUE), então o menor valor (primeiro) de cada grupo de ID será retornado para cada linha.

SQL> select id,
  2         value,
  3         first_value(value) over (partition by ID order by VALUE range
  4                                  between unbounded preceding and unbounded following) rank
  5  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101        101
        10        102        101
        10        103        101
        20        201        201
        20        202        201
        20        202        201
        20        203        201
        30        301        301
        30        302        301
        30        303        301

10 linhas selecionadas.


LAST_VALUE


A função analítica LAST_VALUE irá retornar o último valor de um conjunto de dados ordenado. Por exemplo, abaixo irei mostrar na coluna RANK o último valor de VALUE retornado de cada grupo ID. Como a ordenação é ascendente pela coluna VALUE (order by VALUE), então o maior valor (último) de cada grupo de ID será retornado para cada linha.
 
SQL> select id,
  2         value,
  3         last_value(value) over (partition by ID order by VALUE range
  4                                 between unbounded preceding and unbounded following) rank
  5  from   t1;

        ID      VALUE       RANK
---------- ---------- ----------
        10        101        103
        10        102        103
        10        103        103
        20        201        203
        20        202        203
        20        202        203
        20        203        203
        30        301        303
        30        302        303
        30        303        303

10 linhas selecionadas.


LAG

A função analítica LAG tem como objetivo acessar os dados de uma linha anterior a partir da linha atual retornada. No exemplo abaixo irei retornar os valores das linhas anteriores da coluna VALUE  (1º, 3º e 9º). Por exemplo, no resultado abaixo a linha com VALUE 303 mostrou na coluna VALUE_PREVIOUS_1 o valor 302 que é exatamente o valor da linha anterior ao valor 303. Já a coluna VALUE_PREVIOUS_3 mostrou o valor 203 que é exatamente o valor das 3 linhas anteriores ao valor 303. Já a coluna VALUE_PREVIOUS_9 mostrou o valor 101 que é exatamente o valor das 9 linhas anteriores ao valor 303.

SQL> select id,
  2         value,
  3         lag(value,1,0) over (order by value) AS value_previous_1,
  4         lag(value,3,0) over (order by value) AS value_previous_3,
  5         lag(value,9,0) over (order by value) AS value_previous_9
  6  from   t1;

        ID      VALUE VALUE_PREVIOUS_1 VALUE_PREVIOUS_3 VALUE_PREVIOUS_9
---------- ---------- ---------------- ---------------- ----------------
        10        101                0                0                0
        10        102              101                0                0
        10        103              102                0                0
        20        201              103              101                0
        20        202              201              102                0
        20        202              202              103                0
        20        203              202              201                0
        30        301              203              202                0
        30        302              301              202                0
        30        303              302              203               101

10 linhas selecionadas.


LEAD

A função analítica LEAD tem como objetivo acessar os dados de uma linha posterior a partir da linha atual retornada. No exemplo abaixo irei retornar os valores das linhas posteriores da coluna VALUE  (1º, 3º e 9º). Por exemplo, no resultado abaixo a linha com VALUE 101 mostrou na coluna VALUE_NEXT_1 o valor 102 que é exatamente o valor da linha posterior ao valor 101. Já a coluna VALUE_NEXT_3 mostrou o valor 201 que é exatamente o valor das 3 linhas posteriores ao 101. Já a coluna VALUE_NEXT_9 mostrou o valor 303 que é exatamente o valor das 9 linhas posteriores ao valor 101.

SQL> select id,
  2         value,
  3         lead(value,1,0) over (order by value) AS value_next_1,
  4         lead(value,3,0) over (order by value) AS value_next_3,
  5         lead(value,9,0) over (order by value) AS value_next_9
  6  from   t1;

        ID      VALUE VALUE_NEXT_1 VALUE_NEXT_3 VALUE_NEXT_9
---------- ---------- ------------ ------------ ------------
        10        101          102          201          303
        10        102          103          202            0
        10        103          201          202            0
        20        201          202          203            0
        20        202          202          301            0
        20        202          203          302            0
        20        203          301          303            0
        30        301          302            0            0
        30        302          303            0            0
        30        303            0            0            0

10 linhas selecionadas.

segunda-feira, 5 de dezembro de 2016

Tamanho máximo de um arquivo de dados no Oracle (Physical Database Limits)

Por Eduardo Legatti

Olá,

No artigo de Novembro/2016 eu abordei sobre o erro "ORA-00059: maximum number of DB_FILES exceeded". Existe um outro erro ORA- muito conhecido entre os DBAs Oracle que é o erro "ORA-01144 File Size exceeds maximum of 4194303 Blocks" que significa que o DBA tentou redimensionar o tamanho do datafile além do limite máximo. Lendo a documentação do Oracle 11g R2 e do Oracle 12c R1, é possível determinar que esses limites não se alteraram na nova versão (12c), ou seja, um arquivo de dados (datafile) em um banco de dados Oracle pode conter no máximo cerca de 4 milhões de blocos (2^22 que dá exatamente 4194303 blocos). Vale a pena salientar que à partir do Oracle 10g foi criada uma tablespace do tipo BIGFILE, ou seja, ela é um tipo especial de tablespace que pode conter apenas um único datafile. No entanto, este datafile pode conter no máximo cerca de 4 bilhões de blocos (2^32 que dá exatamente 4294967295 blocos). Como o tamanho de um datafile é definido pelo tamanho do bloco de dados (block size) utilizado pelo banco de dados ou pela tablespace, segue abaixo um quadro comparativo contendo o tamanho máximo que um datafile pode ter de acordo com o tamanho do bloco de dados utilizado.


No mais, vale a pena salientar que o número máximo de datafiles que um banco de dados Oracle pode conter é 65533. O número máximo de tablespaces não pode exceder 65536 e o número máximo de datafiles por tablespace geralmente é 1022.

segunda-feira, 7 de novembro de 2016

Abordando o erro ORA-00059: maximum number of DB_FILES exceeded

Por Eduardo Legatti

Olá,

Por padrão, quando criamos um banco de dados no Oracle, o número máximo de arquivos de dados (datafiles) que o mesmo pode suportar é 100. Essa informação fica armazenada no arquivo de controle (control file). Até o Oracle 8 quando esse limite era atingido o erro "ORA-1118: cannot add any more data files: limit of % exceeded" era emitido informando que não era mais possível adicionar novos datafiles. Para corrigir esse problema o control file deveria ser recriado de forma que o valor MAXDATAFILES fosse incrementado. A partir do Oracle 8i foi eliminado a necessidade de recriação do control file, bastando apenas setar o parâmetro de inicialização DB_FILES (valor padrão é 200) com um novo valor. Neste caso, o control file se expandirá automaticamente para acomodar novos registros dentro de suas seções. Vale a pena salientar que este parâmetro não é dinâmico, ou seja, o banco de dados precisa ser reinicializado após a alteração no mesmo. Caso o valor de DB_FILES seja atingido, o erro "ORA-00059: maximum number of DB_FILES exceeded" será emitido. Para simular o erro ORA-00059, irei recriar o control file setando MAXDATAFILES com um valor menor, bem como o parâmetro DB_FILES. Portanto, segue abaixo uma simulação.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Nov 7 10:06:11 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning option

SQL> alter database backup controlfile to trace as '/tmp/controlfile.sql';

Database altered.

SQL> STARTUP NOMOUNT;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             117441340 bytes
Database Buffers          503316480 bytes
Redo Buffers                5627904 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "BD01" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 5
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/oradata/BD01/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/oradata/BD01/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/oradata/BD01/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12    '/oradata/BD01/system01.dbf',
 13    '/oradata/BD01/sysaux01.dbf',
 14    '/oradata/BD01/undotbs01.dbf',
 15    '/oradata/BD01/users01.dbf'
 16  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> RECOVER DATABASE;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/BD01/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Como demonstrado acima, eu recriei o control file com o valor de MAXDATAFILES igual a 5. Logo abaixo irei setar o valor de DB_FILES para 6.

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
db_files                             integer     6

Realizando uma consulta na view dinâmica de desempenho V$CONTROLFILE_RECORD_SECTION abaixo é possível notar que a seção DATAFILE comporta no máximo 5 registros e que atualmente existem 4 registros em uso. Este valor bate com o número de datafiles existentes no banco de dados conforme demonstrado pela view DBA_DATA_FILES.

SQL> select type, record_size, records_total, records_used
  2    from v$controlfile_record_section
  3   where type = 'DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE                             520             5            4

SQL> select count(*) from dba_data_files;

  COUNT(*)
----------
         4

Agora irei simular a criação de arquivos de dados no banco de dados conforme a seguir.       

SQL> create tablespace tbs01 datafile '/oradata/BD01/tbs01.dbf' size 10M;

Tablespace created.

SQL> create tablespace tbs02 datafile '/oradata/BD01/tbs02.dbf' size 10M;

Tablespace created.

SQL> create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M;
create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

É possível perceber que na criação da tablespace TBS03 o erro ORA-00059 foi emitido, ou seja, o control file tentou se expandir para acomodar o novo datafile, mas foi impedido pela limitação do parâmetro DB_FILES que está setado para 6. Para resolver o problema, irei aumentar o valor do mesmo e reinicializar a instância.

SQL> alter system set db_files=200 scope=spfile;

System altered.

SQL> startup force;
ORACLE instance started.

Total System Global Area  627732480 bytes
Fixed Size                  1346756 bytes
Variable Size             117441340 bytes
Database Buffers          503316480 bytes
Redo Buffers                5627904 bytes
Database mounted.
Database opened.

Após a alteração do parâmetro DB_FILES e reinicialização da instância, irei tentar criar novamente a tablespace TBS03.

SQL> create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M;

Tablespace created.

Pronto. É possível verificar que o control file foi expandido para acomodar mais registros.

SQL> select type, record_size, records_total, records_used
  2    from v$controlfile_record_section
  3   where type = 'DATAFILE';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED
---------------------------- ----------- ------------- ------------
DATAFILE                             520            35            7

Para finalizar, segue abaixo um trecho do arquivo de alerta da instância mostrando que a seção 4 do control file foi expandida para acomodar novos registros.

Mon Nov 7 10:21:51 2016
create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M
Expanded controlfile section 4 from 5 to 35 records
Requested to grow by 30 records; added 1 blocks of records
Completed: create tablespace tbs03 datafile '/oradata/BD01/tbs03.dbf' size 10M

terça-feira, 4 de outubro de 2016

RMAN - Abordando Oracle Secure Backup Cloud Module para o Amazon AWS S3

Por Eduardo Legatti

Olá,

O Amazon S3 é um serviço de armazenamento na nuvem disponibilizado pela Amazon (AWS). Para DBAs que administram bancos de dados Oracle na infraestrutura da AWS usando uma máquina EC2, existe uma alternativa de armazenar backups físicos realizados pelo RMAN diretamente no S3 como se fosse uma fita (tape). Vale a pena salientar que os arquivos enviados pelo RMAN para este tape, no caso o S3, tem um formato próprio e só são reconhecidos pelo RMAN quando conectado diretamente utilizando a library OSB para o AWS S3, ou seja, os arquivos no S3 não serão reconhecidos pelo RMAN caso os mesmos sejam baixados diretamente do S3 para o sistema de arquivos. Para este propósito, existem outras formas como usar utilitários da AWS, como por exemplo, o AWS S3 sync para enviar arquivos diretamente da flash_recovery_area para um bucket no S3.

A simulação que irei realizar neste artigo é em um ambiente Linux. Não irei abordar aqui, mas vale a pena salientar, que é necessário ter uma conta no site da Oracle OTN, bem como ter uma conta no site da AWS já com um bucket S3 configurado e com as devidas permissões. No mais, o primeiro passo é instalar o java versão superior a 1.7 ou superior e realizar o download do módulo OSB (osbws_install.jar) no site da Oracle. Segue abaixo os procedimentos para instalação do módulo.

Verificando a versão do java.

$ /usr/local/jdk7/bin/java -version
java version "1.7.0_79"
Java(TM) SE Runtime Environment (build 1.7.0_79-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)

Após verificado a versão do java e da realização do download do módulo OSB, irei realizar a instalação do mesmo conforme a seguir. Irei configurar a integração do RMAN com S3 usando a instância BD01. Neste momento, será necessário informar o AWSID, AWSKey, otnUser e otnPass.

$ export ORACLE_SID=BD01
$ /usr/local/jdk7/bin/java -jar osbws_install.jar -AWSID [awsid] -AWSKey [awskey] -otnUser [otnuser] -otnPass [otnpass]
  -walletDir /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet
  -libDir /u01/app/oracle/product/11.2.0/dbhome_1/lib

Oracle Secure Backup Web Service Install Tool, build 2016-07-12
AWS credentials are valid.
Oracle Secure Backup Web Service wallet created in directory /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet.
Oracle Secure Backup Web Service initialization file /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsbd01.ora created.
Downloading Oracle Secure Backup Web Service Software Library from file osbws_linux64.zip.
Downloaded 27239574 bytes in 9 seconds. Transfer rate was 3026619 bytes/second.
Download complete.

Após realizada a instalação, será necessário criar um link simbólico no sistema operacional como demonstrado a seguir.

$ ln -s /u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so /u01/app/oracle/product/11.2.0/dbhome_1/lib/libobk.so

O próximo passo será realizar um teste de conectividade. É possível perceber que o arquivo osbwsBD01.ora foi criado em $ORACLE_HOME e este é o arquivo de configuração para conexão com o bucket S3 na AWS. Nele é possível configurar a credencial na AWS e o bucket S3 que deverá ser utilizado pelos backups com o RMAN. Abaixo segue alguns nomes fictícios para fins didáticos.

$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora

cat osbwsBD01.ora
OSB_WS_HOST=https://s3.amazonaws.com
OSB_WS_WALLET='location=file:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbws_wallet CREDENTIAL_ALIAS=rman_aws'
OSB_WS_BUCKET='rman_backup'

Agora irei realizar um teste de conectividade com o bucket S3.

$ export ORACLE_SID=BD01
$ sbttest osbws_readme.txt -dbname BD01

The sbt function pointers are loaded from libobk.so library.
-- sbtinit succeeded
-- sbtinit (2nd time) succeeded
sbtinit: vendor description string=Oracle Secure Backup
sbtinit: Media manager is version 3.16.7.12
sbtinit: Media manager supports SBT API version 2.0
sbtinit: allocated sbt context area of 1064 bytes
-- sbtinit2 succeeded
-- regular_backup_restore starts ................................
-- sbtbackup succeeded
write 100 blocks
-- sbtwrite2 succeeded
-- sbtclose2 succeeded
sbtinfo2: SBTBFINFO_NAME=osbws_readme.txt
sbtinfo2: SBTBFINFO_COMMENT=Oracle Secure Backup Web Services Library
sbtinfo2: SBTBFINFO_METHOD=stream
sbtinfo2: SBTBFINFO_ORDER=random access
sbtinfo2: SBTBFINFO_SHARE=multiple users
sbtinfo2: SBTBFINFO_LABEL=s3.amazonaws.com/rman_backup
-- sbtinfo2 succeeded
-- sbtrestore succeeded
file was created by this program:
     seed=1291994907, blk_size=16384, blk_count=100
read 100 buffers
-- sbtread2 succeeded
-- sbtclose2 succeeded
-- sbtremove2 succeeded
-- regular_backup_restore ends   ................................
-- sbtcommand succeeded
proxy copy is not supported
-- sbtend succeeded
*** The SBT API test was successful ***

Caso durante o teste de conectividade ocorra o erro [KBHS-00713: HTTP client error ''], é porque possivelmente o endereço no parâmetro OSB_WS_HOST deverá ser trocado de http:// para https://.

A seguir irei realizar algumas configurações e testes de backup/recover diretamente de dentro do RMAN acessando o bucket S3.

$ export ORACLE_SID=BD01
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 4 10:09:43 2016

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

connected to target database: BD01 (DBID=2858829824)

RMAN> configure channel device type sbt parms 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora)';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora)';
new RMAN configuration parameters are successfully stored

RMAN> configure default device type to sbt;

new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored

RMAN> configure device type sbt_tape parallelism 1 backup type to compressed backupset;

new RMAN configuration parameters:
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored


RMAN> show all;

RMAN configuration parameters for database with db_unique_name BD01 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BD01.f'; # default

Realizadas as configurações acima, irei prosseguir com algumas operações de backup/recover para fins de demonstração.

RMAN> backup tablespace USERS;

Starting backup at 04/10/2016
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=6 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library VER=3.16.7.12
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 04/10/2016
channel ORA_SBT_TAPE_1: finished piece 1 at 04/10/2016
piece handle=10rbtt89_1_1 tag=TAG20160729T143033 comment=API Version 2.0,MMS Version 3.16.7.12
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 04/10/2016

Starting Control File and SPFILE Autobackup at 04/10/2016
piece handle=c-2858829824-20160729-01 comment=API Version 2.0,MMS Version 3.16.7.12
Finished Control File and SPFILE Autobackup at 04/10/2016


RMAN> list backup;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4117    Full    3.50M      SBT_TAPE    00:00:24     04/10/2016
        BP Key: 4117   Status: AVAILABLE  Compressed: NO  Tag: TAG20160729T143033
        Handle: 10rbtt89_1_1   Media: s3.amazonaws.com/rman_backup
  List of Datafiles in backup set 4117
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  4       Full 7840655778587 04/10/2016 /u01/oradata/BD01/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4118    Full    17.25M     SBT_TAPE    00:01:05     04/10/2016
        BP Key: 4118   Status: AVAILABLE  Compressed: NO  Tag: TAG20160729T143058
        Handle: c-2858829824-20160729-01   Media: s3.amazonaws.com/rman_backup
  SPFILE Included: Modification time: 04/10/2016
  SPFILE db_unique_name: BD01
  Control File Included: Ckp SCN: 7840655778602   Ckp time: 04/10/2016

Após a realização do backup físico pelo RMAN, irei simular uma falha na instância do Oracle e iniciar o processo de restore/recover do banco de dados.

SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 4 10:15:32 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1459620040 bytes
Database Buffers          671088640 bytes
Redo Buffers                4947968 bytes
Banco de dados montado.
ORA-01157: n?o e possivel identificar/bloquear arquivo de dados 4 - consulte
arquivo de analise DBWR
ORA-01110: 4 do arquivo de dados: '/u01/oradata/BD01/users01.dbf'


Segue abaixo a simulação do restore e recover do banco de dados.

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 4 10:20:25 2016

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

connected to target database: BD01 (DBID=2858829824, not open)

RMAN> restore datafile 4;

Starting restore at 04/10/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=156 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=189 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library VER=3.16.7.12

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 10rbtt89_1_1
channel ORA_SBT_TAPE_1: piece handle=10rbtt89_1_1 tag=TAG20160729T143033
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
Finished restore at 04/10/2016

RMAN> recover datafile 4;

Starting recover at 04/10/2016
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 04/10/2016

RMAN> alter database open;

database opened

Caso seja necessário realizar um restore completo de todos os arquivos de dados incluindo control files e spfile segue um exemplo abaixo.

$ export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252
$ export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
$ export ORA_RMAN_SGA_TARGET=512
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Oct 4 10:36:12 2016

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

connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initBD01.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     534462464 bytes

Fixed Size                     2230072 bytes
Variable Size                192940232 bytes
Database Buffers             331350016 bytes
Redo Buffers                   7942144 bytes

RMAN> run {
2> allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
3> set DBID 2858829824
4> restore spfile from autobackup;
5> release channel d1_tape_backup;
6> }

using target database control file instead of recovery catalog
allocated channel: d1_tape_backup
channel d1_tape_backup: SID=396 device type=SBT_TAPE
channel d1_tape_backup: Oracle Secure Backup Web Services Library VER=3.16.7.12

executing command: SET DBID

Starting restore at 04/10/2016 10:46:13

channel d1_tape_backup: looking for AUTOBACKUP on day: 20160801
channel d1_tape_backup: AUTOBACKUP found: c-2858829824-20160801-02
channel d1_tape_backup: restoring spfile from AUTOBACKUP c-2858829824-20160801-02
channel d1_tape_backup: SPFILE restore from AUTOBACKUP complete
Finished restore at 04/10/2016 10:49:07

released channel: d1_tape_backup

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     730714112 bytes

Fixed Size                     2231952 bytes
Variable Size                205521264 bytes
Database Buffers             515899392 bytes
Redo Buffers                   7061504 bytes

RMAN> show all;

RMAN configuration parameters for database with db_unique_name BD01 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN> run {
2> allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
3> set DBID 2858829824
4> restore controlfile from autobackup;
5> release channel d1_tape_backup;
6> }

allocated channel: d1_tape_backup
channel d1_tape_backup: SID=96 device type=SBT_TAPE
channel d1_tape_backup: Oracle Secure Backup Web Services Library VER=3.16.7.12

executing command: SET DBID

Starting restore at 04/10/2016 10:53:20

channel d1_tape_backup: looking for AUTOBACKUP on day: 20160801
channel d1_tape_backup: AUTOBACKUP found: c-2858829824-20160801-02
channel d1_tape_backup: restoring control file from AUTOBACKUP c-2858829824-20160801-02
channel d1_tape_backup: control file restore from AUTOBACKUP complete
output file name=/u01/oradata/BD01/control01.ctl
output file name=/u01/oradata/BD01/control02.ctl
Finished restore at 04/10/2016 10:56:25

released channel: d1_tape_backup

RMAN> alter database mount;

database mounted

RMAN> restore database;

Starting restore at 04/10/2016 10:59:56
Starting implicit crosscheck backup at 04/10/2016 10:59:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK
Finished implicit crosscheck backup at 04/10/2016 10:59:56

Starting implicit crosscheck copy at 04/10/2016 10:59:56
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04/10/2016 10:59:56

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_39_csz3zwqn_.arc
File Name: /u01/flash_recovery_area/BD01/autobackup/2016_10_04/o1_mf_s_918747652_csz5xnol_.bkp
File Name: /u01/flash_recovery_area/BD01/autobackup/2016_10_04/o1_mf_s_918745687_csz407xh_.bkp

using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=127 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup Web Services Library VER=3.16.7.12

channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_SBT_TAPE_1: restoring datafile 00001 to /u01/oradata/BD01/system01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00002 to /u01/oradata/BD01/sysaux01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00003 to /u01/oradata/BD01/undotbs01.dbf
channel ORA_SBT_TAPE_1: restoring datafile 00004 to /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 02rc5mvi_1_1
channel ORA_SBT_TAPE_1: piece handle=02rc5mvi_1_1 tag=LEVEL-0
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:01:55
Finished restore at 04/10/2016 11:01:55

RMAN> recover database;

Starting recover at 04/10/2016 11:02:04
using channel ORA_DISK_1
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/BD01/system01.dbf
destination for restore of datafile 00002: /u01/oradata/BD01/sysaux01.dbf
destination for restore of datafile 00003: /u01/oradata/BD01/undotbs01.dbf
destination for restore of datafile 00004: /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 07rc5o8h_1_1
channel ORA_SBT_TAPE_1: piece handle=07rc5o8h_1_1 tag=LEVEL-1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
channel ORA_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/oradata/BD01/system01.dbf
destination for restore of datafile 00002: /u01/oradata/BD01/sysaux01.dbf
destination for restore of datafile 00003: /u01/oradata/BD01/undotbs01.dbf
destination for restore of datafile 00004: /u01/oradata/BD01/users01.dbf
channel ORA_SBT_TAPE_1: reading from backup piece 0crc5pad_1_1
channel ORA_SBT_TAPE_1: piece handle=0crc5pad_1_1 tag=LEVEL-1
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:07

starting media recovery

archived log for thread 1 with sequence 38 is already on disk as file /u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_38_csz0s6qq_.arc
archived log for thread 1 with sequence 39 is already on disk as file /u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_39_csz3zwqn_.arc
archived log file name=/u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_38_csz0s6qq_.arc thread=1 sequence=38
archived log file name=/u01/flash_recovery_area/BD01/archivelog/2016_10_04/o1_mf_1_39_csz3zwqn_.arc thread=1 sequence=39
media recovery complete, elapsed time: 00:00:00
Finished recover at 04/10/2016 11:02:30

RMAN> alter database open resetlogs;

database opened

Caso precise fazer um backup de toda a Flash Recovery Area para o S3 usando o RMAN, bastará utilizar um comando conforme exemplo a seguir.

run {
 allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
 backup recovery area;
 release channel d1_tape_backup;
 }

Para apagar os arquivos do S3 diretamente pelo RMAN, basta utilizar o comando abaixo.

run {
 allocate channel d1_tape_backup DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_PARMS=(OSB_WS_PFILE=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/osbwsBD01.ora),SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libosbws.so';
 delete backup device type sbt_tape;
}

terça-feira, 6 de setembro de 2016

RMAN - Analisando a mensagem "Bad check value found during backing up datafile" no arquivo de alerta do Oracle

Por Eduardo Legatti

Olá,

No artigo de Fevereiro/2016 foi abordado como realizar a recuperação física de um bloco corrompido em um arquivo de dados utilizando a técnica "Block Media Recovery" do RMAN. Recentemente, fazendo a análise do arquivo de alerta de um banco de dados como demonstrado abaixo, percebi uma mensagem informando que o bloco 334002 do datafile 70 estaria corrompido durante a tentativa de realização de um backup físico pelo RMAN. Se realmente o bloco estiver corrompido, poderemos recuperá-lo utilizando esta técnica.

Sat Sep 3 10:10:01 2016
Hex dump of (file 70, block 334002) in trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc
Corrupt block relative dba: 0x118518b2 (file 70, block 334002)
Bad check value found during backing up datafile
Data in bad block:
 type: 40 format: 2 rdba: 0x118518b2
 last change scn: 0x0c69.bc39d7ea seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7ea2802
 check value in block header: 0x3253
 computed block checksum: 0x0
Reread of blocknum=334002, file=/oradata/BD01/LOB_01_015.dbf. found valid data

Ao verificar o trecho acima no arquivo de alerta acima, realizei a validação do arquivo de dados pelo RMAN conforme a seguir, mas nenhuma mensagem de bloco corrompido foi emitida.

$ export ORACLE_SID=BD01
$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Sep 3 10:19:35 2016

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

connected to target database: BD01 (DBID=1637785486)

RMAN> validate datafile 70;

Starting validate at 03/09/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=485 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00070 name=/oradata/BD01/LOB_01_015.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:07:46

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- --------------
70   OK     0              124289       3171840         13656377431238

  File Name: /oradata/BD01/LOB_01_015.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              2418
  Other      0              3045133

Finished validate at 03/09/2016

Ao analisar o arquivo de trace, foi identificado que uma operação de backup incremental estava em execução durante o suposto problema de corrupção de bloco corrompido. No entanto, é possível perceber que logo após o bloco ser marcado como corrompido, existe uma mensagem indicando que o bloco foi novamente lido e que o seu estado estava válido. Vale a pena salientar que esta informação também está contida no arquivo de log de alerta.

$ cat /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc

Trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_26379.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:    server01
Release:    2.6.32-431.el6.x86_64
Version:    #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine:    x86_64
VM name:    Xen Version: 4.2 (HVM)
Instance name: BD01
Redo thread mounted by this instance: 1
Oracle process number: 136
Unix process pid: 26379, image: oracle@server01 (TNS V1-V3)


*** 2016-09-03 10:10:01.872
*** SESSION ID:(87.57015) 2016-09-03 10:10:01.872
*** CLIENT ID:() 2016-09-03 10:10:01.872
*** SERVICE NAME:(SYS$USERS) 2016-09-03 10:10:01.872
*** MODULE NAME:(backup incr datafile) 2016-09-03 10:10:01.872
*** ACTION NAME:(0002354 STARTED16) 2016-09-03 10:10:01.872

Hex dump of (file 70, block 334002)
Dump of memory from 0x00007F4158268000 to 0x00007F415826A000
7F4158268000 0000A228 118518B2 BC39D7EA 04020C69  [(.........9.i...]
7F4158269FB0 ACF6C7BB 701DB8EC B5F46AB4 E30FC56E  [.......p.j..n...]
7F4158269FC0 22758DDB B9452750 605107CF 39ECD3AD  [..u"P'E...Q`...9]
7F4158269FD0 387CC51C 9660182B 23606732 F1478AB3  [..|8+.`.2g`#..G.]
7F4158269FE0 1C17E72F C6082CF2 009DE777 6EABC849  [/....,..w...I..n]
7F4158269FF0 98D57134 E3D4B9E8 B511A853 D7EA2802  [4q......S....(..]
Corrupt block relative dba: 0x118518b2 (file 70, block 334002)
Bad check value found during backing up datafile
Data in bad block:
 type: 40 format: 2 rdba: 0x118518b2
 last change scn: 0x0c69.bc39d7ea seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xd7ea2802
 check value in block header: 0x3253
 computed block checksum: 0x0
Reread of blocknum=334002, file=/oradata/BD01/LOB_01_015.dbf. found valid data

Conclusão: Inicialmente eu achei muito estranho um erro no arquivo de alerta sem associação a um erro ORA-. Durante uma operação de backup usando o RMAN, o mesmo tenta obter uma imagem consistente do bloco de dados, e caso esse bloco seja alterado durante essa operação, o RMAN tentará obter novamente uma imagem consistente do mesmo bloco. Possivelmente esta foi a causa da mensagem no arquivo de alerta. Outro ponto importante a salientar é que toda essa operação é logada no arquivo de alerta (alert log file).

Postagens populares