2015-05-11

Poor mans getTracefile

As you might know already, I'm a big fan of Oracle Traces. Consequently I'm a fan of Method-R tools to work with these files as well.
A very important part in the chain of tools is Method R Trace - an extension to SQL Developer - as it speeds up developers a lot: When they generate a tracefile, they can access it immediately and do not need to ask and wait for a DBA to get and provide the file. On the other side, I as a lazy dba want developers to serve themselves.
Therein Method R Trace specializes.
Beside the functionality to trace a current statement/script, in Version 2.0.0.43 of Method R Trace There was another functionality (called feature 2) to list all tracefiles

 and download them.

Unfortunately this functionality disappeared in Version 3.0.0.1 as I mentioned earlier.



As I really like that feature I implemented a poor mans getTracefile functionality in SQL Developer 4.1 with a mixture of Method R Trace Installation Script from version 2.0.0.43 and 3.0.0.1(+patch).



The outcome is not so nice looking or integrated, but it provides the general functionality.
You can list all the tracefiles in a User Defined Report:



When you click a Tracefile the child report gets populated with a bfile locator associated with the specific physical LOB file.


Here SQL Developer is very kind to provide a context menue for this bfile locator.




And you can decide to store the file locally or view it directly. There is no fancy integration into Method R Trace like tagging, filter or whatever, so you must process the file manually.



To get this functionality an extension to the MRTRACE schema is required, and of course the user defined Report must be installed in SQL Developer.

For a proper MRTRACE schema you need both installation scripts from Method R Trace 3.0.0.1 and 2.0.0.43. First install the 3.0.0.1 script (+patch). Afterwards install only the level two feature set objects from 2.0.0.43.

Additional grants are required as well:
grant select on dba_directories to mrtrace;

And as these objects, grants and synonyms needs to be created. I create them for user MRTRACE.

/* 0.3 */
/* 20150511 - bergerma - add full schema reference (MRTRACE)                */
/*                       as MRTRACE lacks create session and has NOPASSWORD */
/*                       add JAVA_GRANTS as trace directories changed       */
/* requires MrTrace 2.0.0.43 objects! */
/* http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html for details */

connect SYS
grant select on dba_directories to mrtrace; -- <- make sure this user is correct

connect SYSTEM/:system_pwd

create or replace TYPE MRTRACE.TRACEFILE AS OBJECT (
  trace_path varchar2(200),
  trace_name varchar2(200),
  filesize  NUMBER(15,0),
  filedate  date
);
/

create or replace TYPE MRTRACE.TRACEFILES IS TABLE OF  MRTRACE.TRACEFILE;
/

CREATE OR REPLACE 
PACKAGE MRTRACE.MRTRACE_BX AS 

/* ***************************************************************************
* berx - 20150507 - initial release - for testers only
* see http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html 
*   for details
*************************************************************************** */

function get_version return varchar2;

function get_tracefiles_java  return TRACEFILES pipelined;

function get_bfile(file_name IN varchar2, directory_name IN varchar2)
return bfile;

END MRTRACE_BX;
/

CREATE OR REPLACE
PACKAGE BODY MRTRACE.MRTRACE_BX AS

/* ***************************************************************************
* berx - 20150507 - initial release - for testers only
* berx - 20150508 - 0.2 - minor adaptions
* berx - 20150511 - 0.3 - add full schema reference
*
* see http://berxblog.blogspot.com/2015/05/poor-mans-gettracefile.html 
*   for details
* requirements
*   GRANT SELECT ON DBMS_DIRECTORIES TO :MRTRACE_USER
*************************************************************************** */

FUNCTION get_version
  RETURN VARCHAR2
AS
 v_myversion varchar2(10) := '0.3';
 v_required_mrtrace  varchar2(20) := '2.0.0.43';
 v_actual_mrtrace    varchar2(20);
 v_warning_string1   varchar2(60) := ' - MRTrace_bx requires MRTRACE level two feature set ';
 v_warning_string2   varchar2(30) := ' but the version is: ';
BEGIN
   v_actual_mrtrace := MRTRACE.get_version;
  if v_actual_mrtrace = v_required_mrtrace then RETURN v_myversion;
  else RETURN v_myversion || v_warning_string1 || v_required_mrtrace || 
              v_warning_string2 || v_actual_mrtrace;
  END IF;
END get_version;


FUNCTION get_tracefiles_java
  RETURN TRACEFILES pipelined
AS
  VRETURNARRAY SimpleStringArrayType;
  v_cnt NUMBER := 0;
BEGIN
  MRTRACE.GET_TRACEFILES_JAVA(vReturnArray => VRETURNARRAY);
  v_cnt:=VRETURNARRAY.count;
  FOR i IN VRETURNARRAY.first..VRETURNARRAY.last
  LOOP
    pipe row( TRACEFILE( SUBSTR(VRETURNARRAY(i),1,instr(VRETURNARRAY(i), '|', 1, 1)-1)  -- PATH
            , SUBSTR(VRETURNARRAY(i),
                     instr(VRETURNARRAY(i), '|', 1, 1) + 1, 
                     instr(VRETURNARRAY(i), '|', 1, 2)-instr(VRETURNARRAY(i), '|', 1, 1)-1) -- FILENAME
            , to_number( SUBSTR(VRETURNARRAY(i), 
                                instr(VRETURNARRAY(i), '|', 1, 2) +1, 
                                instr(VRETURNARRAY(i), '|', 1, 3)-instr(VRETURNARRAY(i), '|', 1, 2)-1) 
                       ) -- SIZE
            , (TO_DATE('19700101000000', 'YYYYMMDDHH24MISS') + 
               to_number( SUBSTR(VRETURNARRAY(i), 
                                 instr(VRETURNARRAY(i), '|', 1, 3)+1, 
                                 LENGTH(VRETURNARRAY(i))-instr(VRETURNARRAY(i), '|', 1, 3))) / 86400000
              ) 
          ) ); -- pipe row ... 
  END LOOP;
  RETURN;
END get_tracefiles_java;

FUNCTION get_bfile(file_name IN varchar2, directory_name IN varchar2)
  RETURN bfile
AS
 v_path    VARCHAR2(200);
  v_dirname  VARCHAR2(100) := 'METHODR_UDUMP_1';
  v_bfile BFILE;
BEGIN
  v_path := directory_name;
  BEGIN
   select min(dir.DIRECTORY_NAME) into v_dirname 
   from dba_directories dir 
   where dir.directory_path = v_path 
     and dir.directory_name like 'METHODR_UDUMP%';
  EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line (
         'Error in get_bfile where raised:');
      DBMS_OUTPUT.put_line (
         DBMS_UTILITY.format_error_backtrace);
      RAISE;
  END;
  
  v_bfile := bfilename(v_dirname,file_name);
  RETURN v_bfile;
END get_bfile;

END MRTRACE_BX;
/

DECLARE -- JAVA_GRANTS
  v_path   varchar2(200); 
  c_path sys_refcursor; 
BEGIN
open c_path for 'select DIRECTORY_PATH from dba_directories where directory_name like ''METHODR_%''';
      loop
        fetch c_path into v_path;
        exit when c_path%notfound;
        if ( v_path is null )then
          raise_application_error(-20009, 'Could not get the value of the "METHODR_" directory from dba_directories.', false);
        end if;
        execute immediate 'BEGIN DBMS_JAVA.GRANT_PERMISSION(''MRTRACE'',' || '''SYS:java.io.FilePermission'',''' || v_path || ''', ''read'' ); END;';
        execute immediate 'BEGIN DBMS_JAVA.GRANT_PERMISSION(''MRTRACE'',' || '''SYS:java.io.FilePermission'',''' || v_path || '/-'', ''read'' ); END;';        
      end loop;
close c_path;
END; 

grant execute on MRTRACE.MRTRACE_BX to PUBLIC;
create public synonym mrtrace_bx for mrtrace.mrtrace_bx;

At last this report is required in SQL Developer:

<?xml version="1.0" encoding="UTF-8" ?>
<displays>
<display id="04e5e018-014a-1000-8001-ac193119805b" type="" style="Table" enable="true">
<name><![CDATA[get tracefiles]]></name>
<description><![CDATA[create a list of tracefiles in Instances UDUMP directory and makes specific files available
it's based on Method-R Method R Trace functionality (version 2.0.0.43 needed) with an extension MrTrace_bx ]]></description>
<tooltip><![CDATA[create a list of tracefiles in Instances UDUMP directory and makes specific files available ]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[select TRACE_NAME ,
FILESIZE ,
FILEDATE ,
TRACE_PATH 
from table(mrtrace_bx.get_tracefiles_java)]]></sql>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="-" author="-" subject="-" keywords="-" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="EMBED" zip="false" />
</column>
<table font="Agency FB" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>

null                                                               </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="true" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
<display id="null" type="" style="Table" enable="true">
<name><![CDATA[get tracefile]]></name>
<description><![CDATA[access the specific tracefile ]]></description>
<tooltip><![CDATA[Specific tracefile from list above ]]></tooltip>
<drillclass><![CDATA[null]]></drillclass>
<CustomValues>
<TYPE>horizontal</TYPE>
</CustomValues>
<query>
<sql><![CDATA[select mrtrace_bx.get_bfile(:TRACE_NAME, :TRACE_PATH) as " Tracefile "
from dual]]></sql>
<binds>
<bind id="TRACE_NAME">
<prompt><![CDATA[TRACE_NAME]]></prompt>
<tooltip><![CDATA[TRACE_NAME]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
<bind id="TRACE_PATH">
<prompt><![CDATA[TRACE_PATH]]></prompt>
<tooltip><![CDATA[TRACE_PATH]]></tooltip>
<value><![CDATA[NULL_VALUE]]></value>
<bracket><![CDATA[null]]></bracket>
</bind>
</binds>
</query>
<pdf version="VERSION_1_7" compression="CONTENT">
<docproperty title="-" author="-" subject="-" keywords="-" />
<cell toppadding="2" bottompadding="2" leftpadding="2" rightpadding="2" horizontalalign="LEFT" verticalalign="TOP" wrap="true" />
<column>
<heading font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="FIRST_PAGE" />
<footing font="null" size="10" style="NORMAL" color="-16777216" rowshading="-1" labeling="NONE" />
<blob blob="EMBED" zip="false" />
</column>
<table font="null" size="10" style="NORMAL" color="-16777216" userowshading="false" oddrowshading="-1" evenrowshading="-1" showborders="true" spacingbefore="12" spacingafter="12" horizontalalign="LEFT" />
<header enable="false" generatedate="false">
<data>

null          </data>
</header>
<footer enable="false" generatedate="false">
<data value="null" />
</footer>
<security enable="false" useopenpassword="false" openpassword="null" encryption="EXCLUDE_METADATA">
<permission enable="false" permissionpassword="null" allowcopying="true" allowprinting="true" allowupdating="false" allowaccessdevices="false" />
</security>
<pagesetup papersize="LETTER" orientation="1" measurement="in" margintop="1.0" marginbottom="1.0" marginleft="1.0" marginright="1.0" />
</pdf>
</display>
</display>
</displays>


If you see any issues with these script and report please tell me, I only had limited possibilities to test them.

2015-05-06

SQL Developer 4.1 with Method R Trace 3

These days SQL Developer 4.1 GA was released. If you use SQL Developer, I'd recommend an update.

Nearly at the same time Method R Trace 3.0.0.1 extension for SQL Developer was released as well. Especially for developers that's a very important news - at least if they concern about performance. Method R Trace
A new Version of this extension is needed as SQL Developer changed it's extension framework at the upgrade from version 3 to 4. So I'm very glad Method R took the effort to upgrade Method R Trace. Because of this upgrade, the feature of the current release is limited, as they described in the announcement.
I follow their argumentation it's better to release Method R Trace with feature 1 right now and not wait some more months until feature 1 and 2 are released.

But this decision leads to some consequences: Feature 1 and 2 need different objects installed in the database. And as with version 3.0.0.1 only feature 1 is released, all the objects for feature 2 (it's called level two feature set object in the 2.0.0.43 installation script) is not released. As I did not find the old extension anymore available at Method R website, I just can advise to preserve a copy in case you need some of the level two feature set objects for any purpose.

One more little thing in case you use Method R Trace 3.0.0.1 on a RAC: in the installation script replace
open c_gvparameter for 'select i.inst_id, v.value 
from gv$instance i, v$diag_info v 
where v.name = ''Diag Trace''';
with
open c_gvparameter for 'select i.inst_id, v.value 
from gv$instance i, gv$diag_info v 
where i.inst_id = v.inst_id 
  and v.name = ''Diag Trace''';

to ensure you can access the local tracefiles on all instances.