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


segunda-feira, 2 de maio de 2016

Abordando a técnica de tablespaces transportáveis no Oracle (Transportable Tablespaces)

Por Eduardo Legatti

Olá,

No artigo de Agosto/2013, eu mostrei como mover ou renomear arquivos de bancos de dados (control files, datafiles, redo log files). Neste artigo irei demonstrar através de um exemplo prático, como podemos plugar tablespaces de um banco de dados em outro banco de dados através do recurso Transportable Tablespaces (TTS) disponível desde a versão do Oracle 8i. Este recurso pode vir a ser útil quando precisamos transferir um grande volume de dados de um banco de dados para outro banco de dados. Ao invés de usar métodos de exportação/importação através dos utilitários exp/imp ou expdp/impdp que poderiam consumir um tempo considerável, usando o método TTS, o tempo para transferir os dados seria praticamente o tempo de realização da cópia dos datafiles do banco de dados de origem para o banco de dados de destino. A figura abaixo ilustra o que será abordado a seguir.



As tablespaces DATA_TTS, INDX_TTS e LOB_TTS pertencentes ao banco de dados BD01 demonstrados abaixo serão alvos da migração para o banco de dados BD02.
 
SQL> select tablespace_name,file_name
  2    from dba_data_files
  3   where tablespace_name in ('DATA_TTS','INDX_TTS','LOB_TTS');

TABLESPACE_NAME          FILE_NAME
------------------------ -----------------------------------------
DATA_TTS                 /oradata/BD01/DATA_TTS_01_001.dbf
INDX_TTS                 /oradata/BD01/INDX_TTS_01_001.dbf
LOB_TTS                  /oradata/BD01/LOB_TTS_01_001.dbf

3 linhas selecionadas.

Segue abaixo os objetos de propriedade do usuário SCOTT que possuem segmentos nessas tablespaces.
 
SQL> select owner,segment_name,segment_type,tablespace_name
  2    from dba_segments
  3   where owner='SCOTT';

OWNER        SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------ ------------------------------ ------------------ ----------------
SCOTT        T1                             TABLE              DATA_TTS
SCOTT        PK_T1                          INDEX              INDX_TTS
SCOTT        IDX2                           INDEX              INDX_TTS
SCOTT        SYS_IL0000225643C00003$$       LOBINDEX           LOB_TTS
SCOTT        SYS_LOB0000225643C00003$$      LOBSEGMENT         LOB_TTS

5 linhas selecionadas.


Para que o transporte de tablespaces seja possível, os bancos de dados de origem e destino precisam ter o mesmo CHARACTERSET. Isto é necessário para evitar um erro durante o processo de plugar as tablespaces na qual uma mensagem ORA-29345 é emitida dizendo que não é possível conectar uma tablespace em um banco de dados usando um conjunto de caracteres incompatível.
 
SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   BRAZILIAN PORTUGUESE
NLS_TERRITORY                  BRAZIL
NLS_CURRENCY                   R$
NLS_ISO_CURRENCY               BRAZIL
NLS_NUMERIC_CHARACTERS         ,.
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              BRAZILIAN PORTUGUESE
NLS_SORT                       WEST_EUROPEAN
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              Cr$
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              11.2.0.3.0

20 linhas selecionadas.

O primeiro passo é verificar se as tablespaces são passíveis para utilização do método TTS.
 
SQL> exec SYS.DBMS_TTS.TRANSPORT_SET_CHECK
  2  (ts_list => 'DATA_TTS', incl_constraints => TRUE);

Procedimento PL/SQL concluído com sucesso.
  
SQL> select * from SYS.transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------------------------------------
ORA-39908: O índice SCOTT.PK_T1 do tablespace INDX_TTS valida as restrições principais  da tabela SCOTT.T1 no tablespace DATA_TTS.
ORA-39905: A tabela SCOTT.SYS_LOB0000225643C00003$$ do tablespace LOB_TTS aponta para o segmento da LOB SCOTT.T1 no tablespace DATA_TTS.

2 linhas selecionadas.

Pelo resultado acima, é possível perceber que a tablespace DATA_TTS sozinha não pode ser transportada pelo fato de violar algumas restrições de dependência com as outras tablespaces. Neste caso, iremos verificar todas as tablespaces ao mesmo tempo conforme demonstrado abaixo.

SQL> exec SYS.DBMS_TTS.TRANSPORT_SET_CHECK
  2  (ts_list => 'DATA_TTS,INDX_TTS,LOB_TTS', incl_constraints => TRUE);

Procedimento PL/SQL concluído com sucesso.

SQL> select * from SYS.transport_set_violations;

não há linhas selecionadas


=====================================

Banco de dados BD01 (Source Database)
=====================================


O primeiro passo a ser realizado é colocar as tablespaces no modo READ ONLY no banco de dados de origem conforme demonstrado abaixo.

SQL> alter tablespace DATA_TTS read only;

Tablespace alterado.

SQL> alter tablespace INDX_TTS read only;

Tablespace alterado.

SQL> alter tablespace LOB_TTS read only;

Tablespace alterado.

A seguir, será necessário exportar os metadados utilizando o Datapump Export (expdp) utilizando a cláusula TRANSPORT_TABLESPACES e indicando as tablespaces que serão transportadas.

export ORACLE_SID=BD01
expdp system/#5ydl3db# dumpfile=BD01_transport_tablespaces.dmp
transport_tablespaces=DATA_TTS,INDX_TTS,LOB_TTS
nologfile=y

Export: Release 11.2.0.3.0 - Production on Mon Mai 2 10:02:58 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=BD01_transport_tablespaces.dmp transport_tablespaces=DATA_TTS,INDX_TTS,LOB_TTS nologfile=y
Processando o tipo de objeto TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processando o tipo de objeto TRANSPORTABLE_EXPORT/TABLE
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX/INDEX
Processando o tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processando o tipo de objeto TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Tabela-mestre "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" carregada/descarregada com sucesso
******************************************************************************
Conjunto de arquivos de dump para SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 e:
  /tmp/BD01_transport_tablespaces.dmp
******************************************************************************
Os arquivos de dados necessarios para o tablespace transportavel DATA_TTS:
  /oradata/BD01/DATA_TTS_01_001.dbf
Os arquivos de dados necessarios para o tablespace transportavel INDX_TTS:
  /oradata/BD01/INDX_TTS_01_001.dbf
Os arquivos de dados necessarios para o tablespace transportavel LOB_TTS:
  /oradata/BD01/LOB_TTS_01_001.dbf
O job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" foi concluido com sucesso em 10:04:57

Após a geração do arquivo de dump com os metadados das tablespaces, poderemos copiar os arquivos de dados para o destino conforme demonstrado abaixo.

cp -a /oradata/BD01/DATA_TTS_01_001.dbf /oradata/BD02
cp -a /oradata/BD01/INDX_TTS_01_001.dbf /oradata/BD02
cp -a /oradata/BD01/LOB_TTS_01_001.dbf /oradata/BD02

Após a realização da cópia, as tablespaces no banco de dados de origem já podem voltar a estado READ WRITE.

SQL> alter tablespace DATA_TTS read write;

Tablespace alterado.

SQL> alter tablespace INDX_TTS read write;

Tablespace alterado.

SQL> alter tablespace LOB_TTS read write;

Tablespace alterado.


=====================================

Banco de dados BD02 (Target Database)
=====================================


No banco de dados de destino, o primeiro passo é criar os usuários que possuem objetos nas tablespaces que serão importadas. No meu caso, apenas o usuário SCOTT será criado. Caso o usuário não seja criado, o erro ORA-29342 será emitido com a mensagem que algum usuário não existe no banco de dados.

SQL> create user SCOTT identified by tiger;

Usuário criado.

SQL> grant connect,resource to SCOTT;

Concessão bem-sucedida.

Após a criação do usuário SCOTT no banco de dados BD02, poderemos proceder com a importação do dump utilizando o Datapump Import (impdp) utilizando a cláusula TRANSPORT_DATAFILES e indicando os caminho completo dos datafiles que serão transportados conforme demonstrado abaixo.

export ORACLE_SID=BD02
impdp system/#5ydl3db# dumpfile=BD01_transport_tablespaces.dmp
transport_datafiles='/oradata/BD02/DATA_TTS_01_001.dbf',
                    '/oradata/BD02/INDX_TTS_01_001.dbf',
                    '/oradata/BD02/LOB_TTS_01_001.dbf'
nologfile=y


[oracle@beast TTS2]$ export ORACLE_SID=BD02
[oracle@beast TTS2]$ impdp system/#5ydl3db# dumpfile=BD01_transport_tablespaces.dmp transport_datafiles='/oradata/BD02/DATA_TTS_01_001.dbf','/oradata/BD02/INDX_TTS_01_001.dbf','/oradata/BD02/LOB_TTS_01_001.dbf' nologfile=y

Import: Release 11.2.0.3.0 - Production on Mon Mai 2 10:27:24 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Tabela-mestre "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" carregada/descarregada com sucesso
Iniciando "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=BD01_transport_tablespaces.dmp transport_datafiles=/oradata/BD02/DATA_TTS_01_001.dbf,/oradata/BD02/INDX_TTS_01_001.dbf,/oradata/BD02/LOB_TTS_01_001.dbf nologfile=y
Processando o tipo de objeto TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processando o tipo de objeto TRANSPORTABLE_EXPORT/TABLE
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX/INDEX
Processando o tipo de objeto TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processando o tipo de objeto TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processando o tipo de objeto TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
O job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" foi concluido com sucesso em 10:28:49

Pronto. O último passo é colocar as tablespaces no modo READ WRITE.

SQL> alter tablespace DATA_TTS read write;

Tablespace alterado.

SQL> alter tablespace INDX_TTS read write;

Tablespace alterado.

SQL> alter tablespace LOB_TTS read write;

Tablespace alterado.

Após a importação, podemos verificar abaixo que as tablespaces foram transportadas com sucesso no banco de dados BD02.

SQL> select tablespace_name,file_name from dba_data_files
  2   where tablespace_name in ('DATA_TTS','INDX_TTS','LOB_TTS');

TABLESPACE_NAME          FILE_NAME
------------------------ -----------------------------------------
DATA_TTS                 /oradata/BD02/DATA_TTS_01_001.dbf
INDX_TTS                 /oradata/BD02/INDX_TTS_01_001.dbf
LOB_TTS                  /oradata/BD02/LOB_TTS_01_001.dbf

3 linhas selecionadas.

SQL> select owner,segment_name,segment_type,tablespace_name
  2    from dba_segments
  3   where owner='SCOTT';

OWNER        SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------ ------------------------------ ------------------ ----------------
SCOTT        T1                             TABLE              DATA_TTS
SCOTT        PK_T1                          INDEX              INDX_TTS
SCOTT        IDX2                           INDEX              INDX_TTS
SCOTT        SYS_IL0000225643C00003$$       LOBINDEX           LOB_TTS
SCOTT        SYS_LOB0000225643C00003$$      LOBSEGMENT         LOB_TTS

5 linhas selecionadas.

Por fim, poderemos revogar o privilégio UNLIMITED TABLESPACE e conceder os privilégios de cota para cada tablespace conforme a seguir.

SQL> revoke unlimited tablespace from SCOTT;

Revogação bem-sucedida.

SQL> alter user SCOTT quota unlimited on DATA_TTS;

Usuário alterado.

SQL> alter user SCOTT quota unlimited on INDX_TTS;

Usuário alterado.

SQL> alter user SCOTT quota unlimited on LOB_TTS;

Usuário alterado.

sexta-feira, 1 de abril de 2016

Conceito de transação em blocos PL/SQL no Oracle

Por Eduardo Legatti

Olá,

Não é raro vermos iniciantes em Oracle, principalmente desenvolvedores, tendo algumas dúvidas em relação ao conceito de transações no banco de dados. Sabemos que o Oracle abre uma transação implicitamente quando executamos a primeira instrução SQL (DML) e que, para finalizar esta transação, deveremos emitir o comando COMMIT ou ROLLBACK. Vale a pena salientar que qualquer comando DDL (CREATE, ALTER, DROP, etc.) irá finalizar a transação com COMMIT mesmo que a instrução SQL execute com falha. Mas como fica a transação quando as instruções SQL estão dentro de um bloco PL/SQL ou de uma stored procedure? Neste artigo irei demonstrar exemplos da execução de blocos PL/SQL para demonstrar como as instruções SQL são afetadas dentro do mesmo.


Conceito de transação em blocos PL/SQL

Veremos abaixo como as instruções SQL irão se comportar dentro de um bloco PL/SQL padrão.
 
SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11  END;
 12  /
*
ERRO na linha 1:
ORA-01722: número inválido
ORA-06512: em line 8


O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 
SQL> select * from T1;

não há linhas selecionadas

SQL> select * from T2;

não há linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas


Pelo resultado demonstrado acima, podemos perceber que por padrão, um procedimento PL/SQL fará rollback de toda a transação caso aconteça algum erro, independente se alguma instrução SQL foi executada com sucesso.


Conceito de transação em blocos PL/SQL (EXCEPTION) 

Veremos abaixo como as instruções SQL irão se comportar caso usemos a cláusula EXCEPTION dentro do bloco PL/SQL.
 
SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11   exception
 12    when others then
 13     dbms_output.put_line('Error msg: '||sqlerrm);
 15  END;
 16  /
Error msg: ORA-01722: número inválido

Procedimento PL/SQL concluído com sucesso.


O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 
SQL> select * from T1;

3 linhas selecionadas

SQL> select * from T2;

3 linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas
 
Pelo resultado demonstrado acima, podemos perceber que criando uma EXCEPTION, as instruções SQL serão executadas até dar algum erro. Após o erro, a execução irá parar e a transação ficará aberta até a execução da instrução COMMIT ou ROLLBACK.


Conceito de transação em blocos PL/SQL (RAISE)

Veremos abaixo como as instruções SQL irão se comportar caso usemos a cláusula RAISE_APPLICATION_ERROR dentro do bloco PL/SQL.

SQL> BEGIN
  2   insert into T1 values (1);
  3   insert into T1 values (2);
  4   insert into T1 values (3);
  5   insert into T2 values (1);
  6   insert into T2 values (2);
  7   insert into T2 values (3);
  8   insert into T3 values ('A');
  9   insert into T3 values (1);
 10   insert into T3 values (2);
 11   exception
 12    when others then
 13     raise_application_error(-20000, 'Ocorreu um erro');
 14  END;
 15  /
*
ERRO na linha 1:
ORA-20000: Ocorreu um erro
ORA-06512: em line 13

O que contém cada tabela (T1, T2 e T3) após a execução do bloco PL/SQL acima? Segue o resultado abaixo.
 
SQL> select * from T1;

não há linhas selecionadas

SQL> select * from T2;

não há linhas selecionadas

SQL> select * from T3;

não há linhas selecionadas
 
Pelo resultado demonstrado acima, podemos perceber que criando uma EXCEPTION e usando o comando RAISE_APPLICATION_ERROR, será feito rollback de toda transação da mesma forma que se não tivesse sendo utilizado a cláusula EXCEPTION.

terça-feira, 1 de março de 2016

Monitoramento do tamanho e da taxa de crescimento dos bancos de dados Oracle

Por Eduardo Legatti

Olá,

Monitorar o crescimento dos bancos de dados de uma organização é uma das tarefas de um DBA. Embora existam várias formas de monitorar o tamanho dos bancos de dados individualmente, eu não abro mão de um email que seja enviado todo mês para a minha caixa de entrada com informações de tamanho e crescimento dos principais bancos de produção Oracle das quais eu monitoro. As informações contidas nesse nesse email nada mais são do que uma página HTML com os nomes dos bancos de dados, tamanhos e taxa de crescimento em relação ao mês anterior. Em relação ao tamanho dos bancos de dados, estão incluídos o tamanho atual dos bancos de dados (físico e lógico), o tamanho dos bancos de dados nos últimos 30 dias (físico e lógico) e o crescimento (em MB e %) nesses últimos 30 dias. Com essas informações em mão, é possível tomas algumas atitudes e fazer uma previsão em relação à infraestrutura caso seja necessário. Vale a pena salientar que o que eu entendo por tamanho lógico de um bancos de dados é a soma em bytes de todos os segmentos (tabelas, índices, lobs, etc.) do mesmo. O tamanho físico se refere à soma do tamanho de todos os datafiles, dos redo log files e dos control files.

Para que esse relatório seja gerado, eu utilizarei um script shell no Linux que irá acessar os bancos de dados diariamente, coletar as informações e gravar em uma tabela. Para centralizar essas informações, irei utilizar o banco de dados de um servidor que será o próprio banco de catálogo de recuperação do RMAN. Recomendo criar um usuário específico no banco de dados para a tabela que irá armazenar as informações dos bancos de dados. O meu caso, irei criar o usuário MONITOR.

Ok, mas como será feita a conexão em cada banco de dados? Nesse caso, irei criar vários Database Links (dblinks) no usuário MONITOR. Cada dblink irá apontar para um banco de dados específico e a rotina shell será responsável em fazer um loop nesses dblinks e coletar a informações de cada banco de dados. Para demonstrar o funcionamento desta rotina, segue abaixo alguns scripts.

Após criar o usuário MONITOR no banco de dados escolhido para ser o centralizador das informações, será necessário criar a tabela que armazenará as informações dos banco de dados como demonstrado abaixo.
 
SQL> create table rpt_database_size
  2  (
  3    date_time                     date,
  4    db_name                       varchar2(9),
  5    host_name                     varchar2(64),
  6    database_used_size_gb         number,
  7    database_free_size_gb         number,
  8    file_system_size_disk_gb      number,
  9    log_mode                      varchar2(12),
 10    flashback_on                  varchar2(18),
 11    restore_point                 number,
 12    block_change_tracking_status  varchar2(10),
 13    block_change_tracking_file    varchar2(513)
 14  );

Tabela criada.

SQL> alter table rpt_database_size
  2  add constraint
  3  pk_rpt_database_size primary key (date_time, db_name);

Tabela alterada.


Após criação da tabela acima, o próximo passo será criar os database links (dblinks) para os bancos de dados que serão monitorados. Segue um exemplo.

SQL> select db_link,username,host from user_db_links order by 1;

DB_LINK    USERNAME      HOST
---------- ------------- ---------------------------------------------------------------------------------------------------------
PRD01      SYSTEM        (description = (address = (protocol = tcp) (host = server01)(port = 1521))(connect_data = (sid = PRD01)))
PRD02      SYSTEM        (description = (address = (protocol = tcp) (host = server01)(port = 1521))(connect_data = (sid = PRD02)))
PRD03      SYSTEM        (description = (address = (protocol = tcp) (host = server02)(port = 1521))(connect_data = (sid = PRD03)))
PRD04      SYSTEM        (description = (address = (protocol = tcp) (host = server03)(port = 1521))(connect_data = (sid = PRD04)))
PRD05      SYSTEM        (description = (address = (protocol = tcp) (host = server03)(port = 1521))(connect_data = (sid = PRD05)))
PRD06      SYSTEM        (description = (address = (protocol = tcp) (host = server03)(port = 1521))(connect_data = (sid = PRD06)))
PRD07      SYSTEM        (description = (address = (protocol = tcp) (host = server04)(port = 1521))(connect_data = (sid = PRD07)))
PRD08      SYSTEM        (description = (address = (protocol = tcp) (host = server04)(port = 1521))(connect_data = (sid = PRD08)))
PRD09      SYSTEM        (description = (address = (protocol = tcp) (host = server05)(port = 1521))(connect_data = (sid = PRD09)))
PRD10      SYSTEM        (description = (address = (protocol = tcp) (host = server06)(port = 1521))(connect_data = (sid = PRD10)))

10 linhas selecionadas.


Em seguida, segue abaixo o script SQL que será usado no script shell para fazer a coleta diária do tamanho dos bancos de dados listados acima.

$ cat monitor_tamanho_bd.sql

  declare
   cursor c1 is select db_link from user_db_links order by 1;
   v_string_sql_1 varchar2(4000);
   v_date_time date := trunc(sysdate);
  begin

     /* ORACLE */
     FOR c1rec in c1 LOOP
       v_string_sql_1 := 'INSERT INTO RPT_DATABASE_SIZE
            SELECT '''||v_date_time||''' DATE_TIME,
           (SELECT name FROM v$database@'||c1rec.db_link||') "DB_NAME",
           (SELECT host_name FROM v$instance@'||c1rec.db_link||') "HOST_NAME",
           (SELECT ROUND (SUM (bytes) / 1024 / 1024, 2)
                      AS database_used_size
              FROM dba_segments@'||c1rec.db_link||')
              "DATABASE_USED_SIZE_MB",
           (SELECT ROUND (SUM (bytes) / 1024 / 1024, 2)
                      AS database_free_size
              FROM dba_free_space@'||c1rec.db_link||')
              "DATABASE_FREE_SIZE_MB",
           (SELECT SUM (total_size) "FILE_SYSTEM_SIZE_DISK"
              FROM (SELECT ROUND (SUM (bytes) / 1024 / 1024, 2) AS total_size FROM dba_data_files@'||c1rec.db_link||'
                    UNION ALL
                    SELECT ROUND (SUM (bytes) / 1024 / 1024, 2) AS total_size FROM dba_temp_files@'||c1rec.db_link||'
                    UNION ALL
                    SELECT ROUND (SUM (block_size * file_size_blks) / 1024 / 1024, 2) AS total_size FROM v$controlfile@'||c1rec.db_link||'
                    UNION ALL
                    SELECT ROUND (SUM (bytes / 1024 / 1024), 2) AS total_size FROM v$log@'||c1rec.db_link||' l, v$logfile@'||c1rec.db_link||' f WHERE l.group# = f.group#
                    UNION ALL
                    SELECT NVL (ROUND (SUM (bytes / 1024 / 1024), 2), 0) AS total_size FROM v$standby_log@'||c1rec.db_link||' l, v$logfile@'||c1rec.db_link||' f WHERE l.group# = f.group#))
              "FILE_SYSTEM_SIZE_DISK_MB",
           (SELECT LOG_MODE FROM V$DATABASE@'||c1rec.db_link||') "LOG_MODE",
           (SELECT FLASHBACK_ON FROM V$DATABASE@'||c1rec.db_link||') "FLASHBACK_ON",
           (SELECT COUNT(*) RESTORE_POINT FROM V$RESTORE_POINT@'||c1rec.db_link||') "RESTORE_POINT",
           (SELECT STATUS FROM V$BLOCK_CHANGE_TRACKING@'||c1rec.db_link||') "BLOCK_CHANGE_TRACKING_STATUS",
           (SELECT NVL(FILENAME,''NONE'') FROM V$BLOCK_CHANGE_TRACKING@'||c1rec.db_link||') "BLOCK_CHANGE_TRACKING_FILE"
           FROM DUAL';

        execute immediate v_string_sql_1;
        commit;

      end loop;

end;
/


Segue abaixo o script shell no Linux que será usado para executar o script acima.

$ cat monitor_tamanho_bd.sh

#!/bin/bash
. /home/oracle/.bash_profile

export ORACLE_SID=RCVCAT
export NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi:ss'
export NLS_LANG=AMERICAN_AMERICA

sqlplus /nolog <conn monitor/pwdmonitor
@/home/oracle/monitor_tamanho_bd/monitor_tamanho_bd.sql
quit
EOF


O ideal é que esse script shell seja agendado na crontab para ser executado diariamente. No meu caso, eu agendei o script shell acima para ser executado ao final de cada dia às 23:00. Apenas para demonstração, segue abaixo um exemplo dos dados inseridos na tabela RPT_DATABASE_SIZE após a execução do script shell monitor_tamanho_bd.sh.

SQL> select * from rpt_database_size where date_time <= '01/02/2016' order by 1,2;

DATE_TIM DB_NAME   HOST_NAME         DATABASE_USED_SIZE_MB DATABASE_FREE_SIZE_MB FILE_SYSTEM_SIZE_DISK_MB LOG_MODE     FLASHBACK_ON       RESTORE_POINT BLOCK_CHAN BLOCK_CHANGE_TRACKING_FILE
-------- --------- ----------------- --------------------- --------------------- ------------------------ ------------ ------------------ ------------- ---------- ----------------------------
28/01/16 PRD01     server01                         6296,5               2991,75                  9995,63 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD02     server01                       16907,81               5404,25                  27823,6 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD03     server02                      212235,75              34844,44                 250624,5 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD04     server03                       64790,19              11062,81                 78113,78 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD05     server03                        3057,94               3007,06                  7100,69 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD06     server03                        1746,44                  2483                   5253,6 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD07     server03                        1756,44               2898,56                  5693,59 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD08     server04                       12189,75              18094,13                 30990,84 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD09     server05                      150922,25              11498,75                165198,44 ARCHIVELOG   NO                             0 DISABLED   NONE
28/01/16 PRD10     server06                        1665,63               2532,56                   5798,2 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD01     server01                        6312,38                2985,5                 10005,63 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD02     server01                       16885,38               5426,69                  27823,6 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD03     server02                      213433,56              33796,63                 250774,5 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD04     server03                       64880,44              11182,56                 78323,78 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD05     server03                        3035,31               3029,69                  7100,69 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD06     server03                        1735,88               2503,56                   5263,6 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD07     server03                        1742,38               2912,63                  5693,59 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD08     server04                       12175,31              18108,56                 30990,84 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD09     server05                       151041,5               14199,5                168018,44 ARCHIVELOG   NO                             0 DISABLED   NONE
29/01/16 PRD10     server06                        1656,75               2551,44                   5808,2 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD01     server01                        6287,19               3011,06                 10005,63 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD02     server01                       16907,06               5454,94                  27873,6 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD03     server02                      215329,06              37193,13                 256066,5 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD04     server03                       64893,88              11169,13                 78323,78 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD05     server03                        3077,88               2987,13                  7100,69 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD06     server03                        1723,31               2516,13                   5263,6 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD07     server03                        1747,31               2907,69                  5693,59 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD08     server04                       12181,13              18102,75                 30990,84 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD09     server05                      151275,44              14115,56                168168,44 ARCHIVELOG   NO                             0 DISABLED   NONE
30/01/16 PRD10     server06                        1680,81               2547,38                   5828,2 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD01     server01                        6321,25                  2977                 10005,63 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD02     server01                       16972,75               5439,31                  27923,6 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD03     server02                      218533,94              35008,25                 257086,5 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD04     server03                       64903,44              11159,56                 78323,78 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD05     server03                        3088,56               2976,44                  7100,69 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD06     server03                           1725               2514,44                   5263,6 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD07     server03                        1754,06               2900,94                  5693,59 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD08     server04                        12192,5              18091,38                 30990,84 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD09     server05                      151604,44              13936,56                168318,44 ARCHIVELOG   NO                             0 DISABLED   NONE
31/01/16 PRD10     server06                        1691,94               2546,25                   5838,2 ARCHIVELOG   NO                             0 DISABLED   NONE

40 linhas selecionadas.


Vale a pena salientar que algumas informações gravadas nessa tabela não tem relação com o tamanho do banco, mas que poderá ser utilizada em algum momento no futuro. No mais, o próximo passo é criar o script SQL que será responsável por agrupar as informações dos bancos de dados no formato HTML de forma a mostrar as informações de tamanho e taxa de crescimento dos bancos de dados monitorados, como demonstrado a seguir. Para evitar problemas de visualização do código abaixo, eu troquei os sinais de maior/maior <> por colchetes [].

$ cat rel_crescimento_bd.sql

set echo off
set feedback off
set linesize 500
set trimspool on
set pagesize 10000

alter session set nls_territory='BRAZIL';
alter session set nls_date_format='dd/mm/yyyy';

column title heading ""
column db_name heading "Banco|de Dados" format a8
column used_seg0 heading "Tam. Logico (MB)" format a16
column used_seg30 heading "Tam. Logico|30 dias atras (MB)" format a30
column used_phys0 heading "Tam. Fisico (MB)" format a16
column used_phys30 heading "Tam. Fisico|30 dias atras (MB)" format a30
column mb_month heading "(MB) crescimento|nos ultimos 30 dias" format a30
column grow_month heading "(%) crescimento|nos ultimos 30 dias" format a30

select '[h1][center]RELATORIO DE CRESCIMENTO DE BANCO DE DADOS - '||host_name||' ('||sysdate||')[/center][/h1]' title from v$instance;

select
  db_name,
  used_seg0,
  used_seg30,
  used_phys0,
  used_phys30,
  mb_month,
  decode(grow_month,'%','N/A',grow_month) grow_month
from
(
select
  x.db_name,
  nvl(to_char(x.used_seg0,'FM9G999G990D0099'),'N/A') used_seg0,
  nvl(to_char(z.used_seg30,'FM9G999G990D0099'),'N/A') used_seg30,
  nvl(to_char(x.used_phys0,'FM9G999G990D0099'),'N/A') used_phys0,
  nvl(to_char(z.used_phys30,'FM9G999G990D0099'),'N/A') used_phys30,
  nvl(to_char(round(((x.used_seg0-decode(z.used_seg30,0,NULL,z.used_seg30)))),'FM9G999G990D0099'),'N/A') mb_month,
  to_char(round(((x.used_seg0/decode(z.used_seg30,0,NULL,z.used_seg30))-1)*100,2),'FM9G999G990D0099')||'%' grow_month
from
(select b.db_name,database_used_size_mb used_seg0,file_system_size_disk_mb used_phys0 from rpt_database_size a, (select db_name,max(date_time) date_time from RPT_DATABASE_SIZE group by db_name) b where a.db_name(+)=b.db_name and a.date_time(+)=b.date_time) x,
(select b.db_name,database_used_size_mb used_seg30,file_system_size_disk_mb used_phys30 from rpt_database_size a, (select db_name,max(date_time)-30 date_time from RPT_DATABASE_SIZE group by db_name) b where a.db_name(+)=b.db_name and a.date_time(+)=b.date_time) z
where
  x.db_name=z.db_name
order by 1
);

quit


Agora resta apenas criar o script shell que será responsável por executar o script SQL rel_crescimento_bd.sql e enviar o email para o destinatário. Segue abaixo o script shell que fará esse trabalho. No meu caso, ele está configurado para se conectar no banco de dados do catálogo de recuperação do RMAN (RCVCAT). O ideal é que esse script shell seja agendado na crontab para ser executado uma vez por mês. No meu caso, eu prefiro executar no primeiro dia de cada mês pela manhã.

$ cat rel_crescimento_bd.sh

#!/bin/bash
. /home/oracle/.bash_profile

HOST=`hostname -s`
CURRENTPATH=`dirname $0`
SQLQUERYFILE=$CURRENTPATH/rel_crescimento_bd.sql
RESULTADO=$CURRENTPATH/rel_crescimento_bd.html
MSG=$CURRENTPATH/rel_crescimento_bd.msg

echo "From: "$HOST"@server.com
To: dba@intranet.enterprise.com
Subject: Relatorio de acompanhamento de servidor de banco de dados - "$HOST"
Mime-Version: 1.0
Content-Type: text/html" > $MSG

export ORACLE_SID=RCVCAT
sqlplus -s -m "HTML ON ENTMAP OFF" monitor/pwdmonitor @$CURRENTPATH/rel_crescimento_bd.sql > $CURRENTPATH/rel_crescimento_bd.html

if [ -s $RESULTADO ]
then
    cat $RESULTADO >> $MSG
    sendmail -t < $MSG
fi

exit 0


Após a execução do script shell rel_crescimento_bd.sh, segue abaixo o resultado que estará no email enviado.

 
Para finalizar, segue o agendamento na crontab que eu aconselho que seja usado para cada script shell.

#Cron para execução da rotina de coleta de tamanhos dos bancos de dados
00     23     *       *       *     /home/oracle/rotinas/monitor_tamanho_bd/monitor_tamanho_bd.sh

#Cron para execução do relatório de crescimento dos bancos de dados
00     08     1       *       *     /home/oracle/rotinas/relatorios/rel_crescimento_bd.sh

segunda-feira, 1 de fevereiro de 2016

RMAN - Recuperando blocos de dados corrompidos (Block Media Recovery)

Por Eduardo Legatti

Olá,

Com o RMAN (Recovery Manager) é possível recuperar blocos de dados que foram corrompidos por algum motivo, seja por falhas de hardware, sistema operacional, ou até mesmo por problemas na própria instância do Oracle. Sempre que o erro "ORA-01578: bloco de dados ORACLE danificado (arquivo núm. string, bloco núm. string)" é reportado, significa que o Oracle tentou acessar algum bloco de dados corrompido em um arquivo de dados (datafile). Quando isso acontece, o DBA precisa identificar os blocos que foram corrompidos, a natureza da corrupção, e analisar as opções disponíveis para resolver o problema. Para um melhor entendimento sobre o conceito de tablespaces, segmentos (segments), extensões (extents) e blocos (blocks), acesse o artigo de Março/2008.

Quais opções temos para resolver o problema e suspender a emissão do erro ORA-01578?
  • Se for identificado que o segmento afetado é um índice de tabela, recriá-lo resolverá o problema.
  • Caso seja outro segmento como uma tabela, verificar se existe uma cópia da tabela em outro lugar de forma que seja possível reconstruir as linhas afetadas.
  • Executar CREATE TABLE...AS SELECT na tabela identificada com blocos corrompidos de forma a isolar as linhas saudáveis das linhas corrompidas.
  • Ignorar os blocos corrompidos fazendo uso de algumas procedures da package DBMS_REPAIR. (FIX_CORRUPT_BLOCKS e SKIP_CORRUPT_BLOCKS).
  • Realizar uma recuperação do bloco corrompido (Block Media Recovery).
Dentre as opções citadas acima, irei tratar neste artigo a recuperação física do bloco corrompido utilizando a técnica "Block Media Recovery" do RMAN. Para tanto, um backup físico do banco de dados, ou até mesmo um backup físico da tablespace afetada pela corrupção do bloco deverá estar disponível. Vale a pena salientar que o backup a ser utilizado deverá um que foi criado antes do bloco ter sido corrompido. Para realizar uma simulação, irei executar primeiro um backup da tablespace TBS_DATA_01, depois irei corromper alguns blocos no arquivo de dados pertencente a tablespace em questão, e por fim realizar a recuperação dos blocos corrompidos usando o RMAN através do comando BLOCKRECOVER.

[oracle@server01 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Feb 1 10:27:33 2016

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

connected to target database: BD01 (DBID=3046620119)

RMAN> backup tablespace TBS_DATA_01;

Starting backup at 01/02/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 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=00058 name=/data/oracle/BD01/TBS_DATA_01.dbf
channel ORA_DISK_1: starting piece 1 at 01/02/2016
channel ORA_DISK_1: finished piece 1 at 01/02/2016
piece handle=/backup/flash_recovery_area/BD01/backupset/2016_02_01/o1_mf_nnndf_TAG20160105T144408_c8qwps0q_.bkp tag=TAG20160105T144408 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01/02/2016

Após a realização do backup da tablespace TBS_DATA_01, alvo de blocos de dados que irei corromper mais a frente, segue abaixo a tabela que possivelmente será vítima destes blocos corrompidos. A tabela T1 está alocada na tablespace TBS_DATA_01, no arquivo de dados número 58, e possui atualmente 13 MB de tamanho com cerca de 1 milhão de linhas.
 
SQL> select segment_name,
  2         tablespace_name,
  3         header_file,
  4         header_block,
  5         blocks,
  6         bytes
  7    from dba_segments
  8   where segment_name = 'T1';

SEGMENT_NAME    TABLESPACE_NAME   HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES
--------------- ----------------- ----------- ------------ ---------- ----------
T1              TBS_DATA_01                58          130       1664   13631488

1 linha selecionada.

SQL> select count(*) from T1;

  COUNT(*)
----------
   1000000

1 linha selecionada.

Irei executar o comando ANALYZE para validar e certificar que o segmento T1 está íntegro e não possui qualquer problema de corrupção em seus blocos de dados. Se nenhuma mensagem de erro for emitida é porque a tabela está íntegra e consistente.
 
SQL> analyze table T1 validate structure;

Tabela analisada.

Agora irei corromper alguns blocos do arquivo de dados TBS_DATA_01.dbf. Como o bloco de cabeçalho do datafile é o 130, irei corromper os seguintes blocos acima dele: 140, 240, 340 e 440 conforme demonstrado abaixo.
 
[oracle@server01 ~]$ dd if=/dev/zero of=TBS_DATA_01.dbf bs=8192 seek=140 conv=notrunc count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.9809e-05 s, 164 MB/s

[oracle@server01 ~]$ dd if=/dev/zero of=TBS_DATA_01.dbf bs=8192 seek=240 conv=notrunc count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.5978e-05 s, 178 MB/s

[oracle@server01 ~]$ dd if=/dev/zero of=TBS_DATA_01.dbf bs=8192 seek=340 conv=notrunc count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.3908e-05 s, 187 MB/s

[oracle@server01 ~]$ dd if=/dev/zero of=TBS_DATA_01.dbf bs=8192 seek=440 conv=notrunc count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 4.3342e-05 s, 189 MB/s

Para garantir que os dados serão lidos diretamente do arquivo de dados TBS_DATA_01.dbf e não do buffer cache na SGA, irei forçar a limpeza do cache e realizar a execução de uma instrução SELECT na tabela T1.
 
SQL> alter system flush buffer_cache;

Sistema alterado.

Veremos abaixo que tanto a instrução SELECT quanto o comando ANALYZE irão falhar e emitir o erro ORA-01578. Percebe-se que a mensagem de erro virá acompanhada do número do arquivo de dados e do número do bloco afetado. Neste caso, (arquivo núm. 58, bloco núm. 140).
 
SQL> select count(*) from T1;
select count(*) from T1
*
ERRO na linha 1:
ORA-01578: bloco de dados ORACLE danificado (arquivo núm. 58, bloco núm. 140)
ORA-01110: 58 do arquivo de dados: '/data/oracle/BD01/TBS_DATA_01.dbf'

SQL> analyze table T1 validate structure;
analyze table T1 validate structure
*
ERRO na linha 1:
ORA-01578: bloco de dados ORACLE danificado (arquivo núm. 58, bloco núm. 140)
ORA-01110: 58 do arquivo de dados: '/data/oracle/BD01/TBS_DATA_01.dbf'

Para identificar qual objeto foi afetado pela corrupção do bloco, bastará executar o SQL abaixo que seleciona dados da view de dicionários de dados DBA_EXTENTS. Como demonstrado pelo resultado abaixo, foi comprovado que o segmento afetado pelos blocos corrompidos foi a tabela T1.
 
SQL> select segment_type,owner,segment_name from dba_extents
  2  where file_id = 58 and 131 between block_id
  3  and block_id+blocks -1;

SEGMENT_TYPE       OWNER                          SEGMENT_NAME
------------------ ------------------------------ ------------------------------
TABLE              SCOTT                          T1

1 linha selecionada.

Existem outras formas de diagnosticar e coletar maiores informações sobre blocos que foram corrompidos nos arquivos de dados do Oracle. Dentre elas posso citar a utilização do utilitário DBVERIFY e do próprio comando VALIDATE do utilitário RMAN. Para usar o DBVERIFY, irei me certificar do tamanho padrão do bloco utilizado pela tablespace TBS_DATA_01.
 
SQL> select tablespace_name,block_size
       from dba_tablespaces
      where tablespace_name = 'TBS_DATA_01';

TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
TBS_DATA_01                          8192

Abaixo irei fazer uma análise no arquivo de dados TBS_DATA_01.dbf. Para tanto, após entrar no diretório onde está localizado o arquivo de dados, executarei o DBVERIFY através do utilitário dbv.
 
[oracle@server01 ~]$ dbv blocksize=8192 file=TBS_DATA_01.dbf feedback=1000

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Feb 1 10:49:42 2016

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

DBVERIFY - Verification starting : FILE = /data/oracle/BD01/TBS_DATA_01.dbf
Page 140 is marked corrupt
Corrupt block relative dba: 0x0e80008c (file 58, block 140)
Completely zero block found during dbv:

Page 240 is marked corrupt
Corrupt block relative dba: 0x0e8000f0 (file 58, block 240)
Completely zero block found during dbv:

Page 340 is marked corrupt
Corrupt block relative dba: 0x0e800154 (file 58, block 340)
Completely zero block found during dbv:

Page 440 is marked corrupt
Corrupt block relative dba: 0x0e8001b8 (file 58, block 440)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined         : 1920
Total Pages Processed (Data) : 1520
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 160
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 236
Total Pages Marked Corrupt   : 4
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2253598227 (1825.2253598227)

Acima é possível perceber que os blocos corrompidos foram identificados e prontamente reportados pelo utilitário. Utilizando o comando VALIDATE do RMAN para validar a tablespace TBS_DATA_01, veremos que também será reportado informações sobre blocos corrompidos.

RMAN> validate tablespace TBS_DATA_01;

Starting validate at 01/02/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00058 name=/data/oracle/BD01/TBS_DATA_01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
58   FAILED 0              236          1920            7840568913427
  File Name: /data/oracle/BD01/TBS_DATA_01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1520
  Index      0              0
  Other      4              164

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/bd01/BD01/trace/BD01_ora_11127.trc for details
Finished validate at 01/02/2016

A partir do Oracle 11g, quando uma instrução SQL é abortada devido a emissão do erro ORA-01578 pelo fato de a mesma ter tentado acessar algum bloco corrompido, o Oracle prontamente já carrega informações na view dinâmica de desempenho V$DATABASE_BLOCK_CORRUPTION com informações do bloco corrompido. No entanto, se eu quiser saber todos os blocos que estão corrompidos, preferencialmente executo o comando VALIDATE DATABASE no RMAN. Desta forma a view será carregada de uma única vez com todos os blocos corrompidos encontrados no banco de dados.
 
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ --------------------
        58        140          1                  0 ALL ZERO
        58        240          1                  0 ALL ZERO
        58        340          1                  0 ALL ZERO
        58        440          1                  0 ALL ZERO

4 linhas selecionadas.

No resultado acima, os blocos corrompidos estão listado na coluna BLOCK#. Segue abaixo uma consulta que eu criei que retorna informações adicionais sobre os blocos corrompidos. Essas informações incluem o nome do arquivo de dados, nome da tablespace, nome do objeto que foi afetado, entre outras. Para fins estéticos, eu omiti algumas colunas do resultado do SQL abaixo.
 
SQL> SELECT file#,
  2         file_name,
  3         c.tablespace_name,
  4         block#,
  5         corruption_change#,
  6         corruption_type,
  7         segment_type,
  8         a.owner,
  9         segment_name,
 10         partition_name,
 11         skip_corrupt
 12    FROM dba_extents a, V$DATABASE_BLOCK_CORRUPTION b, dba_data_files c, dba_tables d
 13   WHERE     b.file# = c.file_id
 14         AND a.file_id = b.file#
 15         AND a.segment_name=d.table_name
 16         AND b.block# BETWEEN a.block_id AND a.block_id + a.blocks - 1;

FILE#   FILE_NAME                              TABLESPACE_NAME    BLOCK#
------- -------------------------------------- --------------- ---------
     58 /data/oracle/BD01/TBS_DATA_01.dbf      TBS_DATA_01           140
     58 /data/oracle/BD01/TBS_DATA_01.dbf      TBS_DATA_01           240
     58 /data/oracle/BD01/TBS_DATA_01.dbf      TBS_DATA_01           340
     58 /data/oracle/BD01/TBS_DATA_01.dbf      TBS_DATA_01           440

Agora irei me certificar que realmente possuo um backup da tablespace TBS_DATA_01 conforme demonstrado abaixo.

RMAN> list backup of tablespace TBS_DATA_01;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4095    Full    1.99M      DISK        00:00:01     01/02/2016
        BP Key: 4095   Status: AVAILABLE  Compressed: YES  Tag: TAG20160105T144408
        Piece Name: /backup/flash_recovery_area/BD01/backupset/2016_02_01/o1_mf_nnndf_TAG20160105T144408_c8qwps0q_.bkp
  List of Datafiles in backup set 4095
  File LV Type Ckp SCN    Ckp Time   Name
  ---- -- ---- ---------- ---------- ----
  58      Full 7840568913504 01/02/2016 /data/oracle/BD01/TBS_DATA_01.dbf

Por fim, irei realizar um recovery do bloco 140 do arquivo de dados 58 fazendo uso do RMAN através do comando BLOCKRECOVER como demonstrado a seguir.

RMAN> blockrecover datafile 58 block 140;

Starting recover at 01/02/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00058
channel ORA_DISK_1: reading from backup piece /backup/flash_recovery_area/BD01/backupset/2016_02_01/o1_mf_nnndf_TAG20160105T144408_c8qwps0q_.bkp
channel ORA_DISK_1: piece handle=/backup/flash_recovery_area/BD01/backupset/2016_02_01/o1_mf_nnndf_TAG20160105T144408_c8qwps0q_.bkp tag=TAG20160105T144408
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 01/02/2016

Ao acessar a view V$DATABASE_BLOCK_CORRUPTION, poderemos perceber que a linha referente ao bloco 140 desapareceu.
 
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ --------------------
        58        240          1                  0 ALL ZERO
        58        340          1                  0 ALL ZERO
        58        440          1                  0 ALL ZERO

3 linhas selecionadas.

Se quisermos recuperar todos os blocos listados na view V$DATABASE_BLOCK_CORRUPTION de uma única vez, bastará utilizar o comando abaixo.

RMAN> blockrecover corruption list;

Starting recover at 01/02/2016
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=223 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00058
channel ORA_DISK_1: reading from backup piece /backup/flash_recovery_area/BD01/backupset/2016_02_01/o1_mf_nnndf_TAG20160105T144408_c8qwps0q_.bkp
channel ORA_DISK_1: piece handle=/backup/flash_recovery_area/BD01/backupset/2016_02_01/o1_mf_nnndf_TAG20160105T144408_c8qwps0q_.bkp tag=TAG20160105T144408
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 01/02/2016


Pronto. Os blocos foram recuperados e a instrução SELECT na tabela T1 irá executar sem problemas.
 
SQL> select * from v$database_block_corruption;

não há linhas selecionadas

SQL> select count(*) from T1;

  COUNT(*)
----------
   1000000

1 linha selecionada.

Postagens populares