Pular para o conteúdo

Reproduzindo SQL problems com o Oracle SQL Test Case Builder

Reproduzindo SQL problems com o Oracle SQL Test Case Builder

Para a maioria dos problemas envolvendo um comando SQL, uma das principais causas que compromete a rápida resolução do problema é sua replicação para outro ambiente, seja para uma nova base de dados interna, onde testes e alterações poderiam ser conduzidas sem afetar diretamente o ambiente produtivo ou mesmo para encaminhá-lo ao suporte da Oracle em busca de apoio junto a resolução do problema.

Uma das novas características do banco de dados Oracle introduzida desde a versão 11g Release 1 (com backporting para a versão 10.2.0.4) é o SQL Test Case Builder (TCB), seu objetivo é reunir de forma automática o máximo de informações possíveis relacionadas a um SQL problemático/incident e permitir a criação um ambiente totalmente reproduzível para diagnostico e reparo do problema.

As informações coletadas automaticamente pelo SQL Test Case Builder incluem:

  • Definições de todos objetos envolvidos (tabelas, índices, funções, procedures e packages);
  • Estatísticas do otimizador;
  • Plano de execução;
  • Definições de parâmetros de inicialização;
  • SQL profiles, stored outlines e outros SQL Management Objects;
  • Dados da(s) tabela(s) sendo utilizada(s) no processo (por padrão não exporta os dados) – utiliza o expdp; entre outras.

A saída (output) do TCB consiste em uma coleção de arquivos e scripts contendo os comandos necessários para se recriar todos os objetos e configurações do ambiente envolvidos no processo e que podem ser posteriormente importados em outra base de dados ou enviados ao suporte Oracle (MOS).

Todo o funcionamento do SQL Test Case Builder está abaixo do pacote DBMS_SQLDIAG onde existem as opções para exportar e importar os SQL testcases. O export do SQL testcase pode ser realizado através de um texto SQL (sql_text), sql_id ou número de um incidente gerado na base de dados (incident_id).

Para que os arquivo sejam gerados em disco, em todos os casos será necessário a utilização de um diretório (directory).

SQL> create or replace directory TCB_DIR as '/u01/tcbcase';

Directory created.

Abaixo estará sendo criado um conjunto de arquivos do Test Case Builder (TCB) relativo ao comando SQL especificado no parâmetro sql_text. Está sendo utilizado o testcase_name que incluirá o prefixo informado no parâmetro no início do nome dos arquivos criados e também é especificado o parâmetro exportData como TRUE de modo que seja exportado os dados dos segmentos envolvidos e não apenas a estrutura.

O parâmetro user_name representa o schema dos objetos envolvidos no processo. É utilizado apenas quando se exporta o test case através de um texto SQL (sql_text).

SQL> DECLARE

 result clob;

 BEGIN

 dbms_sqldiag.export_sql_testcase(
        directory     => 'TCB_DIR',
        user_name     => 'ANDERSON',
        sql_text      => 'select * from colaboradores',
        exportData    => TRUE,
        testcase_name => 'tcb_file_',
        testcase      => result);

 END;
/

PL/SQL procedure successfully completed.

Conjunto de arquivos criados:

[oracle@db3 ~]$ cd /u01/tcbcase
[oracle@db3 tcbcase]$ ls -lrt
total 396

-rw-r--r--. 1 oracle oinstall   2783 Mar 31 15:50 tcb_file_README.txt
-rw-r--r--. 1 oracle oinstall    256 Mar 31 15:50 tcb_file_ol.xml
-rw-r--r--. 1 oracle oinstall    204 Mar 31 15:50 tcb_file_sql.xml
-rw-r--r--. 1 oracle oinstall    402 Mar 31 15:50 tcb_file_prmimp.sql
-rw-r--r--. 1 oracle oinstall     63 Mar 31 15:50 tcb_file_ts.xml
-rw-r--r--. 1 oracle oinstall   4684 Mar 31 15:50 tcb_file_dpexp.sql
-rw-r-----. 1 oracle oinstall 335872 Mar 31 16:05 tcb_file_dpexp.dmp
-rw-r-----. 1 oracle oinstall    936 Mar 31 16:05 tcb_file_dpexp.log
-rw-r--r--. 1 oracle oinstall   4271 Mar 31 16:05 tcb_file_dpimp.sql
-rw-r--r--. 1 oracle oinstall    847 Mar 31 16:05 tcb_file_ssimp.sql
-rw-r--r--. 1 oracle oinstall   1066 Mar 31 16:05 tcb_file_smrpt.html
-rw-r--r--. 1 oracle oinstall    408 Mar 31 16:05 tcb_file_xpls.sql
-rw-r--r--. 1 oracle oinstall    699 Mar 31 16:05 tcb_file_xplo.sql
-rw-r--r--. 1 oracle oinstall    434 Mar 31 16:05 tcb_file_xplf.sql
-rw-r--r--. 1 oracle oinstall   2103 Mar 31 16:05 tcb_file_xpl.txt
-rw-r--r--. 1 oracle oinstall   2137 Mar 31 16:05 tcb_file_main.xml

O arquivo xxxxxxmain.xml, neste caso tcb_file_main.xml, é o arquivo de controle do SQL TCB, nele contém todas as descrições do test case e será utilizado como base para o processo de importação dos arquivos em outra base de dados.

Para criar o TCB através de um sql_id ou incident_id basta substituir o parâmetro sql_text por sql_id/incident_id especificando o identificador único do comando SQL (sql_id) ou o número do incidente SQL (incident_id).

SQL> select sql_id from v$sql where sql_text = 'select * from colaboradores';

SQL_ID
-------------
fnvr0vjw6f4ah

SQL> host rm -f /u01/tcbcase/*

SQL> DECLARE

 result clob;

 BEGIN

 dbms_sqldiag.export_sql_testcase(
        directory     => 'TCB_DIR',
        sql_id        => 'fnvr0vjw6f4ah',
        exportData    => TRUE,
        testcase_name => 'tcb_file_',
        testcase      => result);

 END;
/

PL/SQL procedure successfully completed.

Um SQL incident é gerado quando existe um SQL problemático em que múltiplos erros críticos são registrados (ORA-xxxxx). Através do Automatic Diagnostic Repository (ADR) a base de dados cria automaticamente um incidente para cada ocorrência do problema, sua localização é semelhante ao demonstrado abaixo onde o incnum representa o número do incident.

$ADR_HOME/incident/incdir_incnum

Exemplo de um incidente válido:

/orabin/app/oracle/diag/rdbms/cdb1/cdb1/incident/incdir_2557

SQL> host rm -f /u01/tcbcase/*
SQL> DECLARE

 result clob;

 BEGIN

 dbms_sqldiag.export_sql_testcase(
        directory     => 'TCB_DIR',
        incident_id   => 2557,
        exportData    => TRUE,
        testcase_name => 'tcb_file_',
        testcase      => result);

 END;
/

PL/SQL procedure successfully completed.

Afim de demonstração do processo de importação do SQL Test Case Builder em outra base de dados, foi gerado novamente o test case com base na consulta da tabela colaboradores (sql_text/sql_id).

Base de dados onde a estrutura ainda não existe.

SQL> desc anderson.colaboradores;

ERROR:

ORA-04043: object anderson.colaboradores does not exist

SQL> select username from dba_users where username='ANDERSON';

no rows selected

Criação do diretório (directory) apontando o caminho onde estão os arquivos:

SQL> create or replace directory TCB_DIR as '/u01/tcbcase';

Directory created.

Criação do usuário (schema) conforme a base de dados source:

SQL> create user anderson identified by anderson;

User created.

SQL> grant dba to anderson;

Grant succeeded.

Importando o test case:

SQL> conn anderson/anderson@pdb2;

Connected.

O parâmetro filename recebe como entrada o nome do arquivo de controle XML citado anteriormente que é identificado como *main.xml

SQL> BEGIN

 dbms_sqldiag.import_sql_testcase(
 directory     => 'TCB_DIR',
 filename      => 'tcb_file_main.xml');

 END;
/

PL/SQL procedure successfully completed.

Estando concluído o processo de importação a base de dados com o test case está pronta, basta agora executar os procedimentos/comandos problemáticos para que o impasse ocorra e que o diagnóstico e reparo possa ser empregado.

Algumas considerações importantes presentes no README gerado pelo TCB:

— Note:

 

—

 

—      !!! You should not run TCB under user SYS !!!

 

—      Use another user, such as tcb, who can be granted dba role

 

—

 

—     .The <DIRECTORY_PATH_4_TCB_IMPORT> is the CURRENT directory where

 

—      all the TCB files have resided. It must be an OS path on local

 

—      machine, such as ‘/tmp/bug8010101’. It cannot be a path to other

 

—      machine, for example by mounting over a network file system.

 

—

 

—     .By default for TCB, the data is NOT exported

 

—      In some case data is required, for example, to diagnose wrong

 

—      result problem.

 

—        To export data, call export_sql_testcase() with

 

—           exportData=>TRUE

 

—

 

—      Note the data will be imported by default, unless turned OFF by

 

—           importData=>FALSE

 

—

 

—     .TCB includes PL/SQL package spec by default , but not

 

—      the PL/SQL package body.

 

—      You may need to have the package body as well, for example,

 

—      to invoke the PL/SQL functions.

 

—        To export PL/SQL package body, call export_sql_testcase() with

 

—           exportPkgbody=>TRUE

 

—        To import PL/SQL package body, call import_sql_testcase() with

 

—           importPkgbody=>TRUE

 

—

 

—     .An example that you need to include PL/SQL package (body) is

 

—      you have VPD function defined in a package

Referências

Anderson Graf

Anderson Graf

Anderson Graf é Bacharel em Sistemas de Informação, MBA em Oracle Database Management e Senior DBA na Teiko Soluções em TI. Está envolvido em soluções multi-plataforma de alta disponibilidade, como implementações de RAC, Dataguard, Oracle Engineered Systems entre outros. É um entusiasta da tecnologia Oracle, possui certificações, OCP 10g, 11g e 12c, OCS Linux, banco de dados e cloud control, OCE Performance Tuning, OPNCS, Oracle ACE Associate e autor em vários blogs e OTN (Oracle Technology Network).

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Marcações:
plugins premium WordPress