Infrastructure at your Service

Franck Pachot

Get trace file from server to client

The old way to get a user dump trace file, for sql_trace (10046), Optimizer compilation trace (10053), lock trace (10704), Optimizer execution trace (10507),… is to go to the server trace directory. But if you don’t have access to the server (as in the ☁) the modern (12cR2) way is to select from V$DIAG_TRACE_FILE_CONTENTS. Before everybody is on 12.2 I’m sharing here a sqlplus script that I use for a long time to get the trace file to the client.

This is a script to run with sqlplus passing as a parameter the name of the trace file you want to write to (because the original name on the server is not very meaningful). I close all cursors to get all info such as STAT lines. I get the trace file name from v$diag_info. If a directory exists I use it. If not I create one, named UDUMP, which I’ll remove at the end. Then the file is read by utl_file and spooled on client though dbms_output. At the end, I remove the trace file from the server.

Here is the script – comments welcome.

set serveroutput on feedback off verify off termout off linesize 1024 trimspool on echo off
spool &1..trc
declare
fd utl_file.file_type;
line varchar2(1024);
l_tracename varchar2(512);
l_directory_path all_directories.directory_path%TYPE;
l_directory_name all_directories.directory_name%TYPE;
l_directory_created boolean;
procedure t (msg in varchar2) is begin dbms_output.put_line(msg); end;
begin
/* use old parameter _cached_open_cursors to close all open cursors */
for r in (select 1 from v$session_cursor_cache where count>0) loop
dbms_session.set_close_cached_open_cursors(true);
rollback;
commit;
dbms_session.set_close_cached_open_cursors(false);
end loop;
/* get trace directory and trace file name */
select value into l_directory_path from v$diag_info where name='Diag Trace';
select substr(replace(value,l_directory_path,''),2) into l_tracename from v$diag_info where name='Default Trace File';
/* get directory name for it, or try to create it */
l_directory_created:=false;
begin
select directory_name into l_directory_name from all_directories where directory_path = l_directory_path and rownum=1;
exception
when no_data_found then
begin
l_directory_name:='UDUMP';
execute immediate 'create directory '||l_directory_name||' as '''||l_directory_path||'''';
l_directory_created:=true;
exception when others then
raise_application_error(-20000,'You must CREATE DIRECTORY '||l_directory_name||' as ''' ||l_directory_path||'''; or be granted CREATE DIRECTORY.');
end;
end;
/* opens the trace file */
begin
fd:=utl_file.fopen(l_directory_name,l_tracename,'R');
exception when others then
raise_application_error(-20001,'Impossible to open file: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
/* read the trace file and prints it */
begin
loop
begin
utl_file.get_line(fd,line);
dbms_output.put_line(line);
exception
when no_data_found then exit;
when others then
dbms_output.put_line('!!! error while reading file '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
end loop;
/* close and remove the file from the server */
utl_file.fclose(fd);
utl_file.fremove(l_directory_name,l_tracename);
exception
when others then
raise_application_error(-20002,'Impossible to remove: '||l_tracename||' in '||l_directory_name||' ( '||l_directory_path||' )');
end;
begin
/* drop directory if created */
if l_directory_created then execute immediate 'drop directory '||l_directory_name; end if;
exception
when others then
raise_application_error(-20002,'Impossible to remove directory: '||l_directory_name||' ( '||l_directory_path||' )'||sqlerrm);
end;
end;
/
spool off

 

2 Comments

  • Javier says:

    Hi Franck,
    I was checking your code as I’m in need of getting trace files located my remote DB machine.
    As I don’t have access to this remote server, Thought your code could help.

    Anyway, after launching this piece of code, I’ve been returned this error message:

    ERROR in line 1:
    ORA-20001: Impossible to open file: SPR01_ora_43123096.trc in UDUMP ( /oradesa11g/diag/rdbms/spr01/SPR01/trace )
    ORA-06512: in line 38

    Just changed the code (adding sqlerrm and sqlcode) a bit to show the real error happening at the following sentence: utl_file.fopen(l_directory_name,l_tracename,’R’);

    I ended up with the error:

    ORA-20001: Impossible to open file: SPR01_ora_24314224.trc in UDUMP ( /oradesa11g/diag/rdbms/spr01/SPR01/trace ) ORA-29283: invalid file operation

    Checked on Don Burleson site for a quick error explanation (http://www.dba-oracle.com/t_ora_29283_invalid_file_operation.htm) and it seems to be a problem with privilege access at SO level.

    If I have not misunderstand you, the only way to do what I am expecting would be having access privileges to the DB server OR having 12cR2 in order to use that mentioned view?

    Thanks in advance and thanks for this recipe.

    Btw, I would delete the t procedure you are not longer using.

     
    • You should not waste time on this dba-oracle.com in my opinion ;)
      Access privileges cannot be a problem because the file is written and read by same user and even same process.
      Are you sure the file is there? Maybe you didn’t trace anything.
      You can run this before to be sure there’s something written to the trace:

      exec dbms_system.ksdwrt(1,'hello');

       

Leave a Reply

Franck Pachot
Franck Pachot

Technology Leader