Trace File Manager: Managing Distributed Trace Files with Oracle and PHP

This tool combines the power of PHP and the ease-of-use of PL/SQL for managing distributed trace files in a browser.
 

Downloads for this Article · Source code · Apache Version 2.0.49 or later · PHP Version 4.3.6 or later · Oracle Database Enterprise Edition Version 9.2.04 (for Windows) or Version 10.1.0.2 (for Linux) · JDK 1.4

If you're a DBA or a developer using Oracle-compliant tools, you almost certainly on occasion apply tracing to active database sessions, usually in the hunt for rogue SQL or some other coding misdemeanour.

Initiating the trace is easy and can be applied to a session using the standard dbms_system.set_sql_trace_in_session call spec or alternately by invoking the "deeper" trace utility dbms_support.start_trace_in_session, which in addition detects bind variable values and wait events.

However, getting access to the generated trace file is sometimes a little bit more difficult; you have to ensure that you have access to the USER_DUMP_DIRECTORY on the server where the database lives. When connections to the database are generally client based, this database server may not always be readily accessible. If you also want to issue a TKPROF instruction to convert your raw trace file into something more readable, you generally have to secure command line access to the database server. If you are a DBA supporting lots of different databases on lots of different servers, the job of accessing generated trace files and using TKPROF to format them becomes even more difficult.

In this article, I will describe a solution to this problem, known as the Trace File Manager (TFM), that we have developed in my own shop. Using a combination of the external calls interface available within the Oracle server to initiate TKPROF commands and the UTL_FILE utility to read in files that reside on the operating system, it can be surprisingly easy to bring your trace file data back into the database that generated the information in the first place.

Using a simple PHP front-end to make OCI calls into any or all of your databases where trace data is generated, you can easily retrieve trace files and issue TKPROF commands without the need to make direct connections to the servers where the databases reside.

Although the utility has been developed for use in the local environment here, it has been written relatively generically and should be deployable in any Windows or Unix/Linux environment without too much trouble. It has been deployed successfully here on Windows NT/Windows 2000 using Oracle 9.2.0.4 and Red Hat Enterprise Linux 3 using Oracle 10.1.0.2.

Usage

This TFM utility has three core functions:

• To display Alert, Background and User trace files from one or more databases
• To allow for archiving of Alert files and the deletion of Background and User trace files
• To provide a GUI dialogue for initiating TKPROF calls.

Deployment

For full deployment instructions, see the source code in TFMdeployment.zip. It contains all the necessary PHP, PL/SQL, Java source code, and grants and environmental settings required here; it also contains a comprehensive Readme.txt.

Security

Although this utility is designed primarily to display distributed trace files, the fact that it also offers the ability to delete unwanted files means that at least a basic degree of access control should be considered.

Consequently we have opted for a simple HTTP-server based access protection. (See this Apache tutorial.) This approach involves a htpasswd file and a .htaccess file pointing to it, enforcing all the necessary restrictions.

To avoid packet sniffing one could also further protect the installation using HTTPS or even a login component (for audit trails and so on.). However, with respect to the environment where the utility is currently deployed, simple HTTP-server based protection is sufficient.

I also recommend that you disable directory browsing on the directory where the user.conf lives, and possibly even apply a degree of operating system protection to it.

Workflow

The home page of the utility is the Targets Screen. This screen contains a list of the databases into which you have deployed the TFDADMIN (see the "Deployment" section) account and loaded the relevant objects.

This screen offers you the choice of looking at the Alert file, the Background Trace Files, or the User Trace Files. If you choose "Show Background Trace Files," you will be presented with a list of the contents of the directory specified in the BACKGROUND_DUMP_DEST parameter. The Alert Log will not be shown in this list.  

If you choose "Show User Trace Files," you will be presented with a list of the contents of the directory specified in the USER_DUMP_DEST parameter.

As you can see on this screen, for each .trc file there are two options, SRT and TKP. The SRT option means "Show Raw Trace file" and will retrieve the contents of the file for you and display it on the browser screen.

The other option, TKP, means "TKPROF" and results in a dialog screen for calling the TKPROF utility, the standard method of converting the contents of a .trc into a more user-friendly format. (See Figure 5.) This dialog screen allows you to switch off the display of recursive SQL, to specify a user for generating explain plans in the trace files, and to set any of the sort parameters that TKPROF accepts, purposes of which are many and varied and will depend on what you are actually tracing and what it is that you are looking for.

When the required TKPROF parameters have been entered (or the defaults used), the utility will generate and fire a command line instruction to run tkprof on the server where the database resides. It will then re-show you the user trace file screen and will align the new .prf on the display with its parent .trc file.

You also then have another option, SFT, which means "Show Formatted Trace File." This screen will show you the post-TKPROF formatted contents.  

You will also see the option to delete any or all the files. Choosing this option, you will be presented with a further "Are You Sure?" screen showing the chosen files and the choice to confirm or cancel. After choosing Confirm you will be presented back with a list of the remaining files. Formatted trace files are deleted along with their parent raw trace files. This delete functionality works in the same way on the SFT screen.

If from the Targets screen you choose "Show Alert File," you will be presented with the contents of the current alert file.

An additional option at the top left of this screen is "Archive this file." If you choose this option, the entire contents of the current alert file are copied to a backup and a new, fresh alert file is generated.

If you look at the "Background Trace Files" screen, you will see an already archived alert file called 0040527144418_alert_berlindev01.log. This "Archive Alert File" functionality may or may not be useful to you (depending on how clean you like to keep your alert log).

Select the Oracle.DataAccess.dll from the list, then click the Select button, and finally click the OK button to make the ODP.NET data provider known to your project.

Limitations

This utility assumes that the remote database has read access to the directories specified in the parameters USER_DUMP_DEST and BACKGROUND_DUMP_DEST. It also needs these two directories to be specified as UTL_FILE directives in the init.ora. If these two conditions cannot be guaranteed in your environment, you will need to find an alternative way of reading the trace file data into the database.

As the utility uses the database itself as the transfer mechanism for reading trace files on the remote server(s) and serving the data up centrally, the remote database must be active—that is, the utility cannot show you trace files from databases that are crashed or down for any other reason.

Conclusion

One of the main reasons for the development of TFM is the proliferation of Oracle databases, both production and development/test, that the team here at Skandia is being asked to support. The session trace utility can very easily be applied remotely to any session via SQL*Plus, but the subsequent viewing of the trace file generated requires at least a disk-map to the server where the database resides. With 10 or 15 databases to manage, this approach has become something of a chore. The task is made even more difficult when we want to issue a TKPROF against the trace file, as command-line access to the appropriate server is generally then required.

Used in combination, the ease of use of PL/SQL and the power of PHP make it easy to access Oracle databases and then render the retrieved data in a browser. This capability gives you the opportunity to build a central "command post" from which to expose and control all your distributed trace information. By harnessing the power of PHP and Oracle we have been able to put together a tasty little labour-saving device, useful to DBAs and developers alike.

Thanks to Karsten Gresch, also of Skandia, for his contribution in developing this tool.

References

Trace File Manager: Managing Distributed Trace Files with Oracle and PHP by Paul Gallagher