Practices for Lesson 8: Overview
Practice 8-1: Investigating JDBC Problems
Scenario
The developers have once more created a new version of the contacts application. After deploying the new version of the application, however, database problems arise.
Overview
In this practice, you find and resolve a data source configuration problem, troubleshoot a database connection leak, and configure a data source’s connection timeout.
Assumptions
You completed “Practice 7-1: Investigating Application Problems.” All instances of WebLogic Server are running.
Tasks
1. Run the setup script.
a. Access host01. Open a Terminal Window and run the setup script in the current practice directory.
$> cd /practices/tshoot/practice08-01 $> ./setup.sh
Note: This script copies the new version of the application to the domain’s apps
directory and redeploys the application. It deploys a diagnostic module to server1, if it is not already there. It also makes some changes to the configuration of the data source (actually it deletes the old data source and creates a replacement). b. Minimize the VNC viewer for later.
2. Find a data source configuration error.
a. Use the admin console to shut down and restart server1.
b. After the server has started, wait a moment. Access the server1 server log file on host01. Verify that a JDBC error message is logged periodically.
####<Oct 30, 2013 3:56:18 PM UTC> <Error> <JDBC> <host01.example.com> <server1>
<[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'>
<<WLS Kernel>> <> <> <1383148578819>
<BEA-001112> <Test "SELECT 1 FROM DIAL" set up for pool "datasource1" failed with exception:
"java.sql.SQLSyntaxErrorException:
ORA-00942: table or view does not exist".>
Tip: Use the Find of gedit to search for: <Error> <JDBC>
Note: The timestamp and other details may be different.
c. What is the problem with the configuration? d. Exit the editor.
3. Fix the data source configuration error.
a. Access the admin console again and lock the configuration. b. Navigate to the data source name .
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 8: Troubleshooting JDBC
Chapter 8 - Page 4 c. Click the Configuration > Connection Pool tabs.
d. Click Advanced.
e. Correct the error in Test Table Name.
Tip: It should be: SQL SELECT 1 FROM DUAL
f. Save and activate your changes.
g. Use the admin console to shut down and restart server1 again.
h. <OPTIONAL> After the server has started, check the server1 log file to verify that the error message no longer appears.
Tip: One way to do that is to use gedit to edit the server1.log file on host01. Go to the bottom of the file. Use Find. Turn off Wrap around. Turn on Search backwards. Search for: class.path. (That will be where the server started again.) Then turn off
Search backwards. Search for: <Error> <JDBC> (you should not find it). 4. Configure data source diagnostic profiling.
a. Access the admin console and lock the configuration. b. Navigate to the data source name datasource1. c. Click the Configuration > Diagnostic tabs.
d. Select Profile Connection Usage, Profile Connection Reservation Wait, Profile Connection Leak, and Profile Connection Reservation Failed.
e. Set the Profile Harvest Frequency Seconds to 15. f. Save and activate your changes.
g. Minimize the admin console to use later.
5. Investigate a connection leak by using the admin console.
a. From a Terminal window on host01, run a Grinder script to simulate users accessing the contacts application.
$> cd /practices/tshoot/practice08-01 $> ./rungrinder.sh
b. Let the Grinder run for a minute or so before continuing.
c. Do not close the Terminal window in which the Grinder script is running.
d. In a new Terminal window (or the File Browser), use gedit to look at the server1 log file again.
e. Use the editor to search for: maximum capacity of pool
f. You should find multiple messages about the maximum capacity being reached for the data source:
…Reached maximum capacity of pool "datasource1"…
g. Return to the admin console and navigate to the datasource1 data source. h. Click the Monitoring > Statistics tabs.
i. Click Customize this table.
j. Ensure that the Chosen columns contain (the order does not matter): − Server
− Enabled
− Active Connections Current Count − Current Capacity
Oracle University and In Motion Servicios S.A. use only
− Number Available k. Click Apply.
l. Verify that the number of active connections is the same as the capacity. As you can see, this means that there are no connections available.
m. For example:
6. View data source diagnostic profiling data in the data source log file.
a. On host01, use a Terminal window or the File Browser to navigate to the log directory under the server1 directory.
Tip: /u01/domains/tshoot/wlsadmin/servers/server1/logs
b. Use gedit to edit the file datasource.log.
c. Scroll to the bottom of the file. Use the menu and click Search > Find. Turn on Search
backwards. Search for: WEBLOGIC.JDBC.CONN.
d. View some of the entries you find. Here is a sample entry:
####<datasource1> <WEBLOGIC.JDBC.CONN.RESV.FAIL> <timestamp> <weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool datasource1 to… java.lang.Exception
at weblogic.common.…
<[ACTIVE] ExecuteThread: '45' for queue: 'weblogic.kernel.Default (self-tuning)'>
Note:
− Notice each entry you find starts with ####. − Next, the data source name is listed.
− Following that the profile type is displayed (in this example,
WEBLOGIC.JDBC.CONN.RESV.FAIL, which corresponds to the Profile
Connection Reservation Failed data source diagnostic profiling attribute selected
earlier).
− Then a timestamp is displayed (actual timestamp not shown here).
− Next, an exception and its message are shown, detailing what happened. − The stack trace is displayed.
− Finally, the thread is listed.
− The details of the entries you view may be different. e. Exit the editor.
7. Update the maximum capacity of the data source connection pool and retest. a. In a new Terminal window, run the script to stop all the Grinder processes.
$> cd /practices/tshoot/practice08-01 $> ./killgrinder.sh
b. Use the admin console to update the maximum capacity of the data source. Lock the configuration. Increase the datasource1 connection pool Maximum Capacity to 10. Save and activate your changes.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 8: Troubleshooting JDBC
Chapter 8 - Page 6
Note: You conferred with the DBA and 10 is the value you were told to use. c. Use the admin console to shut down and restart server1. Wait for it to get to the
RUNNING state.
d. Return to the host01 Terminal window in which the rungrinder.sh script was run. Run it again.
e. Wait a moment and return to the admin console data source monitoring screen from earlier. Refresh the web browser. Do you have the same problem? Are the current number of active connections equal to the capacity, and no connections are available?
Note: Increasing the maximum number of connections did not help. There must be an
issue with the application. After talking with the development group, they admit there is a problem in their JDBC code, and they deliver an updated version of the application. f. On host01, run the script to kill the Grinder processes again.
g. Run the script that deploys the fixed version of the application.
$> cd /practices/tshoot/practice08-01 $> ./deploygood.sh
h. Use the admin console to shut down and restart server1. When the server stops, the connections that are still in use are closed. When the server comes back up, the connection pool starts fresh.
Note: Alternatively, you could shut down and restart just the data source: Navigate to
datasource1. Select the Control tab. Select the check box next to server1 and click
Shutdown > Force Shutdown. Click Yes. Select the check box next to server1 again
and click Start. Click Yes.
i. Verify that all is well with the data source by returning to its Monitoring > Statistics screen. You should see that for server1 the active connections is 0, the capacity is 1 (it will grow as needed), and the number available is 1.
8. Disable data source diagnostic profiling.
a. Access the admin console and lock the configuration. b. Navigate to the data source name datasource1. c. Click the Configuration > Diagnostic tabs.
d. Deselect Profile Connection Usage, Profile Connection Reservation Wait, Profile Connection Leak, and Profile Connection Reservation Failed.
e. Set the Profile Harvest Frequency Seconds to 300. f. Save and activate your changes.
9. Configure data source connection timeouts.
Note: You already increased the maximum capacity of the data source. Now you will
ensure “bad code” that does not release connections will have those connections automatically reclaimed by the data source.
a. In the admin console, lock the configuration. b. Edit the datasource1 connection pool again.
Oracle University and In Motion Servicios S.A. use only
c. Under Advanced, set Inactive Connection Timeout to 30.
Note: The default value of 0 means that inactive connections never time out. Now after
30 seconds of inactivity* a connection will be automatically reclaimed by the connection pool.
* Because of the way inactive connections are reclaimed, there could be, on average, a
delay of 50% more than the configured value. d. Save and activate your changes.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.
Practices for Lesson 8: Troubleshooting JDBC
Chapter 8 - Page 8