Monday, September 16, 2013

trca gotchas -- Directory alias does not exist in DBA_DIRECTORIES

This is the scenario:

  1. You traced the sql
  2. You trca-ed the trace file, which showed: Trace Analyzer executed successfully. There are no fatal errors in this log file.
  3. Yet when you open the html, there is nothing in there except “Directory alias does not exist in DBA_DIRECTORIES”. has one reason and solution, but that didn’t work for me. My oracle is installed in Windows, TRCA$INPUT and TRCA$OUTPUT are in lower case:
  2      from dba_directories
  3      where DIRECTORY_NAME like 'TRC%';

------------------------------ ---------------------------------------------------------------------------
TRCA$OUTPUT                    c:\oracle\diag\rdbms\orcl\orcl\trace
TRCA$INPUT                     c:\oracle\diag\rdbms\orcl\orcl\trace
But this didn't pose any problems, so I had to search in other places.

After debugging trca packages, I found out that if the size of the trace file is too small, trcanlzr.sql would consider it is not really a trace file, instead a text file containing the list of the trace files. Remember there are 2 ways to use trcanlzr.sql:

SQL> start trcanlzr.sql largesql.trc  <== your trace file
SQL> start trcanlzr.sql control_file.txt  <== your text file
If the trace file size is small, trcanlzr.sql will think it is the second usage. The logic is in tacpkg.lis:

-- if file is small then it means it is a control file with trace filenames in it, else is just one trace
IF l_file_length < TO_NUMBER(trca$g.g_trace_file_min_size_bytes) THEN -- control file with multiple traces
Obviously this is a very stupid assumption. The second stupid mistake trca made is that the output “Directory alias does not exist in DBA_DIRECTORIES” bears no relationship with the real root cause!

The default value for this small size threshold is:
SQL> select p.value,p.default_value,p.low_value, p.high_value from TRCANLZR.TRCA$_TOOL_PARAMETER p where name='trace_file_min_size_bytes';

------  --------------  ---------- ----------
9999    9999             0          99999

You might think “LOW_VALUE” is the low threshold, but according to the logic is in tacpkg.lis, “VALUE” is actually the threshold. Update “VALUE” to 1, trcanlzr.sql can now successfully generate a report:

SQL> update TRCANLZR.TRCA$_TOOL_PARAMETER  set value=1 where name='trace_file_min_size_bytes';

No comments:

Post a Comment