Reproduzindo SQL problems com o Oracle SQL Test Case Builder

Por Anderson Graf ,
Publicado em Março 2018

Revisado por Juan Pablo Guizado

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

Copy



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

Copy



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:

Copy



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

Copy



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.

Copy




 $ADR_HOME/incident/incdir_incnum

Exemplo de um incidente válido:

Copy




/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; 
  /

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.

Copy



 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:

Copy



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

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

Copy



SQL> create user anderson identified by anderson;
    User created.  
	SQL> grant dba to anderson;   
	Grant succeeded.

Importando o test case:

Copy




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

Copy



 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: https://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_overview.htm#PFGRF16001 https://docs.oracle.com/database/122/TGSQL/sql-test-case-builder.htm#TGSQL94870 How To Use SQL Test Case Builder [Video] (Doc ID 1208143.1)

Anderson Graf, Bacharel em Sistemas de Informação e MBA em Gestão de Banco de Dados Oracle. Trabalha com banco de dados Oracle desde 2009, é Oracle OCP 10g/11g/12c, OCS Linux, Database e Cloud Control. OCE Performance Tuning; OPNCS. É um entusiasta da tecnologia Oracle e autor dos blogs andersondba.com.br e oraclehome.com.br.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.