Practices for Lesson 9 Practices Overview
Practice 9-1: Investigating JDBC Problems
Duration: 40 minutes Skills Learned
At the end of this practice, you should be able to:
• Correct a JDBC configuration error
• Troubleshoot a connection leak scenario
• Configure JDBC diagnostic monitors
• Configure data source connection timeouts Overview
In this practice, you monitor and troubleshoot a data source whose connections do not appear to function as expected. This troubleshooting scenario involves both administrative and
application issues. Specifically, this practice focuses on WebLogic’s connection testing and connection leak detection features.
You use a combination of the server logs, WLST, and the diagnostic framework. The system under investigation is shown in the following diagram:
Instructions
1. Set up the practice.
a. Locate a Lab Framework prompt or start a new one. Change directories to
<CURRENT_LAB>.
b. Execute the following:
ant setup_exercise The Lab Framework:
− Deploys a diagnostic module to MedRecSvr1 if not already present
− Introduces a configuration problem in the MedRec data source
− Deploys a new version of the MedRec application c. Kill and restart the server MedRecSvr1.
2. Correct a JDBC configuration error.
a. After the server starts up, inspect the server output for several minutes. Verify that an error message is logged periodically:
<Error> <JDBC> <BEA-001112> <Test "select count(*) from
PATEINTS" set up for pool "MedRecGlobalDataSourceXA" failed with exception: "java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
".>
Can you guess the configuration mistake?
Oracle University and Sentra inversiones y servicios LTDA use only
b. Launch the console and Lock it.
c. From the Domain Structure panel, click Services > JDBC > Data Sources.
d. Edit MedRecGlobalDataSourceXA.
e. Click the Configuration > Connection Pool tab.
Tip: Note the current settings for Initial Capacity and Maximum Capacity.
f. Click Advanced.
g. For Test Table Name, change the current value from PATEINTS to PATIENTS.
h. Edit the following additional fields:
Field Value
Test Connections on Reserve <checked>
Test Frequency 0
Click Save.
i. Activate your changes.
j. Restart the server. Confirm that the error message no longer appears.
3. Observe a connection leak scenario.
a. From a Lab Framework prompt, execute <LAB_WORK>/client/runclients.sh to test the MedRec application under load.
b. Verify that several exceptions are generated on the server. Locate messages similar to:
Cannot obtain XAConnection
weblogic.common.resourcepool.ResourceLimitException: No
resources currently available in pool MedRecGlobalDataSourceXA to allocate to applications, please increase the size of the pool and retry.
c. Return to the administration console and select MedRecGlobalDataSourceXA again.
d. Click the Monitoring > Statistics tab.
e. Click Customize this table.
f. Remove the JDBC Driver column.
g. Add the following columns:
Current Capacity
Active Connections Current Count Num Available
Click Apply.
h. Verify that not only is the current number of connections in the pool equal to the maximum capacity, but the current number of active connections is the same as well.
For example:
Oracle University and Sentra inversiones y servicios LTDA use only
i. Shut down MedRecSvr1 by using Ctrl + C.
j. In addition to the standard server shut down log messages, notice the following warning:
<Warning> <Common> <BEA-000632> <Resource Pool
"MedRecGlobalDataSourceXA" shutting down, ignoring 10 resources still in use by applications..>.
Tip: This message implies that connections were reserved by applications but never released back to the connection pool.
k. Restart the server.
4. Configure JDBC diagnostic monitors.
a. Return to the console and Lock it.
b. Edit the diagnostic module MedRecDiagnostics.
c. Click the Configuration > Instrumentation tab.
d. If instrumentation is not already enabled, select Enable instrumentation and click Save.
e. Add a new diagnostic monitor and edit it by using the following criteria:
Field Value
f. Add another monitor, JDBC_After_Release_Connection_Internal. Configure the same action:
g. Remove any other Diagnostic Monitors.
h. Activate your changes.
i. Execute runclients.sh again.
5. Investigate connection leaks by using WLDF.
a. After the clients finish, return to the console and view the EventsDataArchive log file for MedRecSvr1.
b. Click Customize this table.
c. Display only these columns:
Date Method Payload
d. Increase the Number of Rows Displayed to 100 and click Apply.
Oracle University and Sentra inversiones y servicios LTDA use only
e. Confirm that several events were generated. The Method column indicates "reserve"
or "release" while the Payload column displays the stack trace for the event.
f. Customize the table again. For WLDF Query Expression, enter the following:
METHODNAME = 'reserve'
g. Use the browser’s search feature to locate the
“ViewingRecordSummaryController.viewRecordSummary” text in the event stack traces.
h. Count the number of “reserve” events originating from this code.
i. Customize the table a third time so that only "release" events are shown.
j. Repeat the previous steps to count the number of “release” events originating from this code.
Compare the results. Notice that the viewRecordSummary method seems to reserve more connections that it releases.
k. Edit MedRecDiagnostics again and disable all instrumentation.
6. Configure data source connection timeouts.
a. Edit MedRecGlobalDataSourceXA once again.
b. Lock the console. Click the Configuration > Connection Pool tab. Click Advanced.
c. Update the following fields:
Field Value
Maximum Capacity 20
Inactive Connection Timeout 30
Tip: Inactive Connection Timeout is in the Advanced section. Maximum Capacity is not.
d. Save. Activate your changes. Restart the server.
e. Inspect the contents of the
<CURRENT_LAB>/resources/wlst/monitorJDBC.py file.
f. Launch another Lab Framework prompt and execute the monitorJDBC.py WLST script.
g. Note the current capacity of the database as well as the number of connections in various states (active, available, leaked and so on). Leave the script running.
h. Execute the runclients.sh script a final time and continue monitoring the WLST output.
Immediately after the client script finishes, confirm that the number of active connections does not decrease to 0.
i. Verify that after an additional 30 seconds, the number of active connections is reset to 0 and the leaked connection count increases.
The previous “No resources currently available in pool” errors should no longer be generated as well.
j. Kill the WLST script.
k. Locate the following warning messages in the server log:
<Warning> <JDBC> <BEA-001153> <Forcibly releasing inactive connection ... back into the connection pool
"MedRecGlobalDataSourceXA" ...
Oracle University and Sentra inversiones y servicios LTDA use only
l. Inspect the stack trace associated with this log message. Notice that it once again refers to the suspected culprit in the MedRec application,
ViewingRecordSummaryController.viewRecordSummary.
Tip: For those with some Java EE knowledge, the culprit code can also be found at
<CURRENT_LAB>/resources.
Oracle University and Sentra inversiones y servicios LTDA use only
Solution Instructions
1. If the <LAB_WORK>/domains/MedRecDomain location does not yet exist, follow the Solution Instructions for the “Developing a Custom Monitoring Script” practice.
2. Launch the Lab Framework command shell by executing the
<STUDENT>/bin/prompt.sh file.
3. Change the current directory to <CURRENT_LAB>.
4. Execute the following:
ant setup_solution The Lab Framework:
− Makes a backup copy of your current work
− Creates a diagnostic module if it does not yet exist
− Adds JDBC monitors to the diagnostic module
− Updates data source settings
Note that all solution WLDF components will be disabled by default.