Fetching Oracle Trace Data from, Say, Amazon RDS

Last week, a Method R Workbench customer was having a hard time downloading Oracle trace files from Amazon RDS. The code that Amazon recommends doesn’t quite work right; it fails to copy trace file lines that are longer than 400 characters. So I wrote a replacement fetcher for him. This method should work for any Oracle implementation that permits you to access your trace data.

Of course, if our customer had been wanting to trace a script execution in Oracle SQL Developer, we have a Method R Trace extension for that. But he wasn’t using Oracle SQL Developer at the moment; he wanted a Perl solution. In this article, I’ll show you that Perl solution and also a Java solution.

For the execution of both implementations, I’ll use the Oracle directory object METHODR_UDUMP_1 and trace file ora193_dia0_8489_base_1.trc that I’ve already identified. I’ll show you at the end of the article how to find your own.

Here is a terminal session showing the Perl code and its use:

$ cat gettrc.pl
use strict;
use warnings;
use DBI;
my ($dbauth, $dirname, $filename) = @ARGV;
my $dbh = DBI->connect('dbi:Oracle:', $dbauth) or die;
my $bufsz = 512;
my $stm = "select bfilename(?, ?) from dual";
my $stmh = $dbh->prepare($stm, {ora_auto_lob=>0}) or die;
$stmh->execute($dirname, $filename);
my ($loc) = $stmh->fetchrow_array();
for (my $off=1; 1; $off += $bufsz) {
	my $piece = $dbh->ora_lob_read($loc, $off, $bufsz);
	last if length($piece) == 0;
	print $piece;
}
$stmh->finish;
$dbh->disconnect;
$ perl gettrc.pl jeff/jeff METHODR_UDUMP_1 ora193_dia0_8489_base_1.trc > x
$ head -1 x
Trace file /u01/app/oracle/diag/rdbms/ora193/ora193/trace/ora193_dia0_8489_base_1.trc
$ diff x /u01/app/oracle/diag/rdbms/ora193/ora193/trace/ora193_dia0_8489_base_1.trc
$

In the listing above, on the database server itself I show the code, execute it, show the filename from the fetched file, and then diff the fetched file with its origin (which should and does show no difference). The most significant “trick” to getting the Perl code to work is telling the driver to create a statement handle that will, when fetched, return a lob locator instead of the content of the lob. You can learn more about that by using perldoc DBD::Oracle. Our customer had to update his DBD::Oracle driver before this code would work.

A Java implementation is probably a better idea. It’s more lines of code, but the code is easier to package and reuse, and your Java environment probably already has everything you need to run it without having to install anything new:

$ cat GetTraceFile.java
import java.io.*;
import java.sql.*;
import oracle.jdbc.*;
public class GetTraceFile {
	private Connection conn;
	private CallableStatement stm;
	private String connurl;
	private String oradir;
	private int bufsz = 512;
	public GetTraceFile (String connurl, String oradir) {
		this.connurl = connurl;
		this.oradir = oradir;
	}
	private void read (OracleBfile b) throws Exception {
		b.openFile();
		InputStream s = b.getBinaryStream();
		InputStreamReader r = new InputStreamReader(s);
		int nb;
		byte[] ba = new byte[bufsz];
		while ((nb = s.read(ba)) != -1)
			System.out.write(ba, 0, nb);
		b.closeFile();
	}
	// Call this before calling get.
	public void init () throws Exception {
		if (conn == null)
			conn = DriverManager.getConnection(this.connurl);
	}
	// Call this after calling init.
	public void get (String trcfilename) throws Exception {
		if (this.stm == null)
			this.stm = conn.prepareCall("select bfilename(?,?) from dual");
		this.stm.setString(1, this.oradir);
		this.stm.setString(2, trcfilename);
		OracleResultSet r = (OracleResultSet) this.stm.executeQuery();
		while (r.next()) // By definition this has to iterate exactly 0 or 1 time
			read(r.getBFILE(1));
		r.close();
	}
	// Call this when you're done getting trace files.
	public void done () throws Exception {
		if (this.stm != null)
			this.stm.close();
		if (this.conn != null)
			this.conn.close();
	}
	// Use this if you want only one trace file.
	public static void main (String[] args) throws Exception {
		GetTraceFile gtf = new GetTraceFile(args[0], args[1]);
		gtf.init();
		gtf.get(args[2]);
		gtf.done();
	}
}
$ javac -cp .:ojdbc8.jar GetTraceFile.java
$ java -cp .:ojdbc8.jar GetTraceFile jdbc:oracle:thin:jeff/jeff@localhost:55210:ora193 METHODR_UDUMP_1 ora193_dia0_8489_base_1.trc > x
$ head -1 x
Trace file /u01/app/oracle/diag/rdbms/ora193/ora193/trace/ora193_dia0_8489_base_1.trc
$ diff x /u01/app/oracle/diag/rdbms/ora193/ora193/trace/ora193_dia0_8489_base_1.trc
$

As I mentioned at the beginning of the article, I knew my directory object name and trace file name in advance. But how will you know yours?

First, here’s how to find the directory object name. If you already have an Oracle directory object that covers the trace file directory, you can identify its name with this query:

select directory_name from all_directories where directory_path = (select value from v$diag_info where name = 'Diag Trace')

If the query returns no rows, then you’ll need to create a directory object and grant read privilege on it.

To identify your session’s own trace file name, use this query:

select value tracefile from v$diag_info where name = 'Default Trace File'

You can find the trace file name for a third-party session whose session ID you know, with this:

select tracefile from v$process where addr = (select paddr from v$session where sid = :target)

With the directory object name and the file name, you’ll have all the command line arguments you’ll need to get your trace file.

Here are some interesting questions to consider for taking your project to the next level:

1. Do you want your code to trace itself (e.g., begin dbms_monitor.session_trace_enable; end;)?
2. Do you want the RDBMS to automatically trace your code for you after you give it a standing order (e.g., begin dbms_monitor.serv_mod_act_trace_enable(…); end;)?
3. Do you want the code to broadcast a “now that I am done tracing, it is ok to process my trace file” message?
4. Do you want your code to fetch its own trace file?
5. What features might be required for a Real Application Cluster implementation?
6. How much post-execution processing do you want performed upon the trace file?
7. Would you want the same processing for all files, or only some?
8. If you want different processing for some files, then what might be an appropriate characteristic to distinguish one type of file from another?