November 2005

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

November 2005
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Setting the Client Identifier

You’re invited to submit your Oracle-related questions to us at askdbspecialists11@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Tom in Northern California: I was excited to see that Oracle 10g provides a method for tracing user sessions in applications that use connection pooling. However, it appears as if the application has to call dbms_session.set_identifier before every database call. This doesn’t seem very practical. Is there a more efficient way to set the client identifier in Java applications that use connection pooling?

Roger Schrag of the Database Specialists team responds: Starting in Oracle 9i, the v$session view has a column called client_identifier that displays a string up to 64 bytes long. Applications can set this field to any desired value by calling dbms_session.set_identifier. Like the module and action columns in v$session, applications can use the client_identifier column to identify specific database sessions to a database administrator or system monitoring tools.

Starting in Oracle 10g, sessions can be traced based on the client identifier they possess. This enables meaningful tracing of end-user sessions in a connection pooled environment. Suppose an application uses a cookie stored in each user’s web browser to track session state. The application could be coded to set the client identifier for an Oracle session to the end user’s cookie value before using the database connection to process the user’s request.

Once this application change has been made, it becomes easy to trace end user sessions in Oracle 10g—even if connection pooling is used. A call to dbms_monitor.client_id_trace_enable will cause any Oracle process to write to a trace file when the client identifier for its database session is set to the specified value. The trcsess utility can then be used to scan all trace files and extract only the entries that go with the specified client identifier.

However, modifying an application to call dbms_session.set_identifier before every database call does seem inefficient. It will generate extra roundtrips over the network and increased overhead. Fortunately, Oracle provides a more efficient method for setting the client identifier in Java- and Oracle Call Interface-based programs.

Java programs using Oracle’s JDBC driver can use the Oracle.jdbc.OracleConnection.setClientIdentifier class in order to set the client identifier for the session. This will not generate an extra roundtrip to the database. Instead, the request will get bundled into the next database request to come along. This feature of the JDBC driver is referenced in Chapter 5 of the JDBC Developer’s Guide and Reference for Oracle Database 10g Release 2. Also, posting #616673.999 on Metalink comes from an Oracle user who has gotten this feature to work.

OCI programs can call OCIAttrSet() to set the OCI attribute oci_attr_client_identifier in the session handle. This will not cause an extra roundtrip to the database. As in the case of the Java class just discussed, the client identifier will get propagated to the database on the next request to generate a database roundtrip .This technique is discussed in Chapter 8 of the Oracle Call Interface Programmer’s Guide for Oracle Database 10g Release 2.

Oracle 10g’s ability to trace database activity based on the client identifier setting opens the door to session tracing in connection pooling environments. This requires that applications set and maintain the client identifier in each database session consistently. The good news is that, done properly, managing the client identifier for database sessions does not cause extra roundtrips to the database or unreasonable overhead.

Connor’s Hints, Tips, and Observations

Connor McDonald, author of Mastering Oracle PL/SQL from APress and contributing author to Oracle Insights: Tales of the Oak Table also from APress, is an accomplished Oracle consultant who has spoken at numerous conferences including UKOUG, AUSOUG, and the Hotsos Performance Symposium.

Connor maintains a website where he publishes various hints, tips, and observations about the Oracle database. On his website, you’ll find resources ranging from brief tips (such as a script to collect INDEX_STATS for multiple indexes) to in-depth presentations (such as a close look at Oracle’s buffer cache in Oracle 7 through 10g) to observations (such as the value of the OCP certification).

Connor’s material is non-speculative and insightful. Performance-related claims and interesting observations include sample code that you can run on your own database to reproduce the results discussed in his writings. You can browse Connor’s material at http://www.oracledba.co.uk.

Securing Your Oracle Databases

The importance of database security is the topic of a new document written by Pete Finnigan who is known for specializing in Oracle security. Finnigan is the author of Oracle Security Step-by-Step – A Survival Guide to Oracle Security published by the SANS Institute in 2003.

Finnigan’s 37-page paper entitled, Many Ways to Become a DBA begins by outlining security problems that can be encountered whether or not someone is granted DBA access. The paper was written to complement a recent Oracle users group presentation. Following are the key topics addressed:

  • The problems—why Oracle can be insecure and what problems these security risks can cause
  • Some examples of how to exploit Oracle
  • Finding and auditing for security problems
  • Some basic guidelines to follow in order to secure your Oracle database

You’ll find Pete Finnigan’s paper at http://www.petefinnigan.com/orasec.htm.

Linux Technology Center

In our Database Specialists, Inc. consulting practice, we have continued to see Oracle on Linux grow in popularity since it was first released in 1998. We’ve even published several of our own step-by-step guides for installing Oracle on Linux, which you’ll find at http://dbspecialists.wpengine.com/presentations.html. NorCalOAUG 2006 Training Day

Oracle Corporation maintains a Linux Technology Center at http://www.oracle.com/technology/tech/linux/index.html. It offers the latest information on using Oracle on Linux.

Here you can learn about Oracle Database 10g Express Edition for Linux, the entry-level database that’s free to develop, deploy, and distribute. You can also download Oracle OpenWorld presentations on the topic of Linux, along with numerous installation guides. You’ll also find a number of technical white papers on a variety of topics including the following:

  • Converting a single-instance database to Oracle RAC 10g
  • Building your own Oracle RAC 10g cluster on Linux and FireWire
  • An introduction to Linux cluster filesystems
  • Mastering Linux filesystems

 

Mark your calendars for the fifth annual Northern California Oracle Applications Users Group Training Day at the San Ramon Valley Conference Center. It will be held Tuesday, January 24th, 2006. The group expects to have over 500 Oracle Applications users who can choose from among 48 presentations throughout the day. Most of the focus at these meetings is on the functional side of Oracle Applications.

Leave a Reply

Your email address will not be published. Required fields are marked *