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

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:

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)