Author: Vineet Kumar
Introduction
As a Database Administrator (DBA) working with Oracle E-Business Suite (EBS) applications, ensuring the effective management and troubleshooting the environment is of paramount importance. One crucial aspect is capturing the managed server name in the PROGRAM column of the V$SESSION view. This enables improved configuration management and facilitates the identification and monitoring of specific server connections within the Oracle EBS application. In this blog, we will explore how to capture the managed server name in the PROGRAM column, leveraging the Oracle Thin Driver and configuring connection properties.
Understanding the Importance of the PROGRAM Column in the V$SESSION View
Every Oracle database connection operates within a session. The V$SESSION view provides comprehensive information about all active sessions. However, the default value of the V$SESSION.PROGRAM column – set as “JDBC Thin Client” when using the Oracle Thin Driver – does not offer much insight, especially when dealing with multiple Java connections that appear identical. Capturing the managed server name in this column enhances the visibility and management of the EBS environment.
Configuring the Connection Properties in the Oracle Thin Driver
To capture the managed server name in the PROGRAM column of the V$SESSION view, you can leverage the Oracle Thin Driver’s flexibility in configuring connection properties. The following connection properties are supported: V$SESSION.OSUSER, V$SESSION.PROCESS, V$SESSION.MACHINE, V$SESSION.TERMINAL, and V$SESSION.PROGRAM. By setting these properties, you can update the corresponding values in the database session.
Steps to Capture the Managed Server Name
To obtain the managed server name in the PROGRAM column value of the V$SESSION view, follow these steps:
- Access the WebLogic console by logging in
- Navigate to Services > Data Sources > [YourDataSource] > Configuration > Connection Pool
- Modify the “System Property” field by including the entry:
- V$SESSION.PROGRAM=weblogic.Name
- Save the changes and restart the Java Virtual Machines (JVM) for the managed servers
- Utilize the below query to monitor the processes of the managed servers
SELECT SID,SQL_ID,EVENT,PROCESS,MODULE,PROGRAM,LOGON_TIME,STATUS,( SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_ID=ROW_WAIT_OBJ#)OBJ,MACHINE,LAST_CALL_ET,CLIENT_IDENTIFIER,ACTION FROM GV$SESSION WHERE PROGRAM LIKE ‘%SERVER%’ ORDER BY PROGRAM;
Monitoring and Troubleshooting Benefits
By capturing the managed server name in the PROGRAM column, you will gain valuable insights into the EBS environment. This information enhances configuration management and allows for better monitoring and troubleshooting. Specifically, the PROGRAM column value in the V$SESSION view will correspond to the WebLogic Server (WLS) that established the connection. This clarity helps DBAs identify and isolate specific server connections, making it easier to handle stuck threads or investigate performance issues.
Conclusion
Capturing the managed server name in the PROGRAM column of the V$SESSION view significantly improves the configuration management and monitoring capabilities of Oracle EBS applications. By configuring the appropriate connection properties in the Oracle Thin Driver and restarting the JVMs of the managed servers, administrators can ensure the PROGRAM column value reflects the corresponding WebLogic Server. This enhancement empowers DBAs to efficiently manage and troubleshoot the EBS environment, leading to enhanced performance, streamlined maintenance, and improved user experience.