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