Problem solve Get help with specific problems with your technologies, process and projects.

Accessing trace files

This tip offers techniques for establishing developer access to trace files.

Trace files are a useful mechanism for developers and DBAs to tune applications. Here are two ways to safely give developers access to trace files.

Many parts of this tip apply to Unix platforms, but the database parts will work on 8.1.7 and 9i. This tip does not discuss how to generate trace files.

For security reasons and to prevent unauthorized personnel from accessing the ORACLE_HOME area, it is a good idea to keep the trace file area (identified by the USER_DUMP_DEST init.ora parameter) separate from the ORACLE_HOME area.

When trace files are created, the permissions on the files allow only the Oracle DBA group to access them. An INIT.ORA parameter can be used to extend the permissions to all other groups. This parameter is _trace_files_public, which is set to false by default. If this parameter is set to true, then everyone on the server has access to read the trace files.

That is all well and good. However, if you operate as my organization does, where database servers are strictly off-limits for all developers, how do you grant access to trace files? Read on...

You need a mechanism for copying the files into an area that the database as well as the developers can access. Broadly speaking, you can do this in two ways:

  1. Have a shared area of disk which the USER_DUMP_DEST points to. This area is a shared drive between the database server and the development server.
  2. Have some mechanism for copying the trace files off the database server.

I am going to discuss the second option and try to highlight some of the issues you may face in this.

Copying the trace files off to an accessible area on another server is simple enough with a CRON job and the rcp command, but you will realize soon enough that this copies the complete and incomplete trace files, which is less than ideal! How can we identify the trace file that has been completed? Fortunately, Oracle provides some packages to make this easier. For the purposes of this tip, I am assuming that the trace file is for the entire session and not for individual actions within the session.

To start with, you need database or SCHEMA LOGON and LOGOFF triggers. In the LOGON trigger, you can start the trace. In the LOGOFF trigger, call the Dbms_System.KsdWrt procedure to write an end-line marker to the trace file and then end the trace. Syntax for KsdWrt is Dbms_System.Ksdwrt (1, 'MARK_END_TRACE: some unique text');. This will cause the string in quotes to be written to the trace file just before the end thus identifying that the database has finished with the trace file. Now run something like the shell script given below to copy the completed trace files to a shared accessible area on another server. For this to be really robust, you need a way of identifying (not discussed here) which schema needs the trace on to prevent a global turning on of trace, which can impact performance severely.

# Module: ctf - Cp Trc Files 
# Exit Codes: 
#   0 - Success 
cd /local_logs/oracle/traces 
for file in *.trc 
   if grep "^MARK_TRACE_END: some unique text" ${file} 2>&1 > /dev/null 
      # Copy file 
      rcp ${file} servername:shared_area 
# Change permissions 
rsh servername 'chmod 777 shared_area/*.trc' 
exit 0 

Reader Feedback

Petr S. writes: This is a very good article. One thing to keep in mind is that trace files can be appended to by different processes. I am not sure how that affects the EOF markers and what happens to a file with an EOF marker, which is subsequently appended.

This was last published in June 2004

Dig Deeper on Oracle database security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.