Accessing 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. <...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle database administrator Understanding SQL string functions What is the difference between a database engineer, architect and administrator? Import on one table from dump file Error during RMAN backup Can I drop a column in SYS schema? STATSPACK tool: transaction vs. execution measurement Should I port from Microsoft Access? How can I find statistics on total memory usage and database connections? Installing multiple Oracle homes Modifying SYS password in a RAC environment

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

p>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.

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.

Rate this Tip To rate tips, you must be a member of SearchOracle.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.