Developer Access To 10046 Trace Files

Lets suppose you are a DBA at a large company. You have some great developers, and they’re learning all about how to turn on full logging of their code through the 10046 database trace. They just learned how to use this data in summary form to find out – at a very detailed level – what’s REALLY taking up all the time during their big batch program which runs too long. They’re salivating over this trace data – but you work for a big company with security policies that can’t be easily changed, where developers rarely get any kind of shell-level or filesystem-level access to a database server. You WANT them to have the ability to profile their own database code… but every time they run a trace, you get dragged into a long email exchange to locate their tracefile and transfer it to a network drive where they can access it. We’re so close to a great situation… but this last part is such a drag!!!

However: please notice the Security Addendum at the end of this article.

Of course if you’re lucky enough that your developers use certain tools from Oracle, then there are some slick 3rd party plugins that will help download and manage tracefiles for you. But what if your developers don’t want to add a whole new environment to their already memory-bound workstations? What if there are corporate policies making this difficult – such as a time-consuming review and approval process for any new software installs?

Wouldn’t it be nice of Oracle had a system view that your developers could just QUERY to find tracefiles? Maybe something like this (which of course displayed data in real time, reflecting the current status of the filesystem):

SQL> desc all_trace_files
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 DIRECTORY                              CHAR(5)
 FILE_NAME                              VARCHAR2(400)
 FILE_TIME                              DATE
 FILE_BYTES                             NUMBER
 FILE_TRACEID                           VARCHAR2(100)
 FIRST_TIME                             DATE
 FIRST_ACTION                           VARCHAR2(100)
 FIRST_MODULE                           VARCHAR2(100)
 FIRST_SERVICE                          VARCHAR2(100)
 SID                                    NUMBER
 SERIAL                                 NUMBER
 ERROR                                  VARCHAR2(400)

Sure, this looks nice – maybe in the next version of Oracle. When you upgrade to it, 10 years from now. But what about NOW – your old 10g database running on a huge mainframe?

We all know how nice it would be if your developers run a query like this… today… on almost any database:

SQL> select directory, file_name, file_traceid, first_action from all_trace_files 
  2  where first_action is not null;

DIREC FILE_NAME                                FILE_TRACE FIRST_ACTION
----- ---------------------------------------- ---------- ----------------------------------------
UDUMP ardentp1_ora_12386502.trc                           Test Window - New
UDUMP ardentp1_ora_22347974.trc                           Test Window - New
UDUMP ardentp1_ora_22675498.trc                           SQL Window - xml_file_writing_te
UDUMP ardentp1_ora_24248536.trc                           SQL Window - test_cases.sql
UDUMP ardentp1_ora_24641726.trc                           DEQ
UDUMP ardentp1_ora_2621852.trc                            Test Window - New
UDUMP ardentp1_ora_3342766.trc                            problemTbsp
UDUMP ardentp1_ora_4063428.trc                            SQL Window - New
UDUMP ardentp1_ora_45350944.trc                           Debug Test Window - New
UDUMP ardentp1_ora_45547538.trc                           Main session
UDUMP ardentp1_ora_48103606.trc                           SQL Window - SELECT * FROM ext.e
UDUMP ardentp1_ora_50593842.trc                           SQL Window - test_cases.sql
UDUMP ardentp1_ora_50790578.trc                           SQL Window - New
UDUMP ardentp1_ora_51380240.trc                           Debug Test Window - New
UDUMP ardentp1_ora_52429026.trc                           UserBlock
UDUMP ardentp1_ora_52691004.trc                           SQL Window - New
UDUMP ardentp1_ora_60817584.trc                           Test Window - New
UDUMP ardentp1_ora_61538330.trc                           Test Window - New
UDUMP ardentp1_ora_62783512_schn_02.trc        schn_02    Test Window - New
UDUMP ardentp1_ora_62783512_schn_03.trc        schn_03    Test Window - New
UDUMP ardentp1_ora_6291778.trc                            Test Window - New
UDUMP ardentp1_ora_63176754.trc                           Test Window - New
UDUMP ardentp1_ora_66388158.trc                           Test Window - New
UDUMP ardentp1_ora_8192480.trc                            Test Window - New
UDUMP ardentp1_ora_8651144.trc                            SQL Window - test_cases.sql
BDUMP ardentp1_m000_12517418.trc                          Monitor Tablespace Thresholds
BDUMP ardentp1_m000_20119784.trc                          Monitor Tablespace Thresholds
BDUMP ardentp1_m000_24248408.trc                          Monitor Tablespace Thresholds
BDUMP ardentp1_m000_45154330.trc                          Monitor Tablespace Thresholds
BDUMP ardentp1_mmnl_52560064.trc                          Monitor Tablespace Thresholds
BDUMP ardentp1_mmon_52756580.trc                          Monitor Tablespace Thresholds

31 rows selected.

And of course – last but not least, you’d like your developers to actually be able to download the tracefile themselves, right? Actually, this has already been done – Dion Cho blogged some sample code about two and a half years ago. He has a very elegant solution using a pipelined PL/SQL function and UTL_FILE.

http://dioncho.wordpress.com/2009/03/19/another-way-to-use-trace-file/

Just one minor tweak would be needed, to grab files from more than one directory. (In addition to your session traces, you might need to get logs from parallel query slaves or DBMS_JOB/SCHEDULER processes.)

Not only can you write queries to your heart’s content on this, but just about every developer environment on the planet can easily take this query output and save it to a file – then they can run the profiler of their choice on the tracefile. That would be cool, right?!

SQL> select * from table(textfile('ardentp1_ora_52691004.trc','UDUMP')) where rownum<20;

COLUMN_VALUE
-------------------------------------------------------------------------------------------
/u0001/app/oracle/admin/ardentp/udump/ardentp1_ora_52691004.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u0001/app/oracle/product/db/10.2.0/ardentp
System name:    AIX
Node name:      ardent4
Release:        1
Version:        6
Machine:        00F66BE22C00
Instance name: ardentp1
Redo thread mounted by this instance: 1
Oracle process number: 59
Unix process pid: 52691004, image: oracle@ardent4

*** ACTION NAME:(SQL Window - New) 2011-08-15 16:35:24.791
*** MODULE NAME:(PL/SQL Developer) 2011-08-15 16:35:24.791
*** SERVICE NAME:(ardentp_dba) 2011-08-15 16:35:24.791
*** SESSION ID:(572.24734) 2011-08-15 16:35:24.791

19 rows selected.

Well I AGREE that this would be cool! And as it happens, the customer I’m working with now would find it very useful. And honestly… it’s really not that complicated to code something like this. So last night I stayed late a few hours to write it up and test it. It’s mainly build on a pipelined java function, and of course a minor tweak on Dion’s code for getting the file.

Basically, every time you query the view it scans the first 30 lines of all files in the bdump and udump directories for timestamps and identification information (module, action, service, sid, serial). This information is combined with basic file stats (name, size, last-modified-date) and the tracefile_identifier is calculated from the filename. Any errors encountered are reported in the error column (file permissions, etc).

You can download the entire thing at my github repo (specifically, the files tracefile_find.txt and tracefile_get.txt). All examples in this blog post are from a real system – nothing here is cooked, except that I changed database and host names to protect the innocent.

Enjoy!

Security Addendum

As pointed out in the comments after I published this post, there are some important security considerations with allowing access to trace files. A good explanation can be found in Pete Finnigan’s blog article: Is it possible to steal data with just ALTER SESSION? To summarize: if a clever hacker is able to access tracefiles then it is possible for them to see things that the database normally protects. This certainly includes table data, and it may even include passwords for other database accounts – sometimes even in cleartext. This is very important if you’re considering any tool that grants access to tracefiles – including the scripts I’ve published here. Although he generally disapproves of any tracefile access, Pete’s comment suggested a way to reduce the risk. The second table (which actually gives tracefile contents) can be limited to DBAs and then developers can get a view which only shows files that the DBAs explicitly grant. I think that the most useful part of my program is the first view anyway, which scans tracefiles and lets you use SQL to search on module/action/service.

One further quick postscript… lest you think I was terribly irresponsible with this client, we did consider security implications before endorsing this script. Admittedly I didn’t consider every scenario that Pete has written about, but I do still think we made an appropriate decision. (Also… the script wasn’t yet installed into production when I left the client, because it had to go through an approval process itself. ) In this particular case, the small group of developers getting access to tracefiles were people who already had full access to all data in the database and they had passwords for all key application accounts. Although they didn’t have SYSDBA passwords or shell access, they essentially had everything else – including all the data. Furthermore, they were in a stage of performance troubleshooting where their productivity could be significantly increased with the ability to run many traces and quickly access the tracefiles (think Method R). And finally, these developers were on the same small, tight team as the DBAs who took care of backups and patching, all sitting together in one corner of the office building. The DBAs were involved in the decision and seemed to find their reduced workload a very positive thing – especially since they know all the developers who would get access.

One could certainly comment on the security policies of this client, and their decisions about balancing trust and productivity. But I do not think that we were circumventing the company security policies in this case. And certainly my scripts are not intended to support such activity. Nonetheless, I do think there are cases where this is a very useful script. It’s much more convenient for developers who – even if they can get shell access – may not know much about navigating unix and are more comfortable working in their SQL development environment.