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:
- 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.
- 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.
#!/bin/ksh
# Module: ctf - Cp Trc Files
# Exit Codes:
# 0 - Success
#
cd /local_logs/oracle/traces
for file in *.trc
do
if grep "^MARK_TRACE_END: some unique text" ${file} 2>&1 > /dev/null
then
# Copy file
rcp ${file} servername:shared_area
fi
done
# 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.
