Showing posts with label JDBC. Show all posts
Showing posts with label JDBC. Show all posts

Oracle WebLogic Server 12c: Configuring a JDBC Data Source

Setting Up the Schema in the Oracle Database

To set up the database schema required by the JDBC client application,  perform the following steps:
1.Download the jdbc_obe.zip file to the machine where your WebLogic Server domain and servers are. Extract the file contents to a local drive. In this tutorial, the files are extracted into the /home/oracle/jdbcobe/testds directory.
The zip file contains three files:
  • testds_oracle.sql 
    A SQL script to configure the required schema in the Oracle Database
  • testds.war 
    A simple web application used to test the configured data source
  • deploy_testds.py 
    A WebLogic Scripting Tool (WLST) script used to deploy the web application
2.Log in to the system as the user that started the Oracle Database. Open a Terminal  window and navigate to the location of the SQL script file, testds_oracle.sql. n this tutorial that is:
$ cd /home/oracle/jdbcobe/testds
Make sure that the database environment variables are set by printing them out:
$ echo $ORACLE_HOME 
/u01/app/oracle/product/11.2.0/xe 

$ echo $ORACLE_SID 
XE 

Note: The values of your environment variables may be different. If there are no  values printed, then set the variables to the proper values with:

How To Ensure No ResultSet/Connection/Statement Leaks Exist in Your JDBC Code

The classes shown below require explicit closure to release resources established at the database level. Connection objects leave database sessions open, and Statement and ResultSet objects leave cursors open at the database level. The corresponding JDBC API's are listed below.


java.sql.Connection
java.sql.Statement
java.sql.CallableStatement
java.sql.PreparedStatement
java.sql.ResultSet

In order to release resources associated with these objects the method close() must be called once the program has finished working with the objects. Below are some common problems encountered when closing resources and a simple utility class for closing these resources.


Dead Connection Detection (DCD) Explained

OVERVIEW 

Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, includingOracle Net8 and Oracle NET. DCD detects when a partner in a SQL*Net V2 client/serveror server/server connection has terminated unexpectedly, and flags the dead sessionso PMON can release the resources associated with it. DCD is intended primarily for environments in which clients power down their
systems without disconnecting from their Oracle sessions, a problemcharacteristic of networks with PC clients.


DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.


When the timer expires, SQL*Net on the server sends a "probe" packet to the client. (In the case of a database link, the destination of the linkconstitutes the server side of the connection.) The probe is essentially an empty SQL*Net packet and does not represent any form of SQL*Net level data,

but it creates data traffic on the underlying protocol. If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally,

the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources.


On Unix servers, the sqlnet.ora file must be in either $TNS_ADMIN  or  $ORACLE_HOME/network/admin. Neither /etc nor /var/opt/oracle alone is valid. It should be also be noted that in SQL*Net 2.1.x, an active orphan process (one processing a query, for example) will not be killed until the query completes. In SQL*Net 2.2, orphaned resources will be released regardless of activity. This is a server feature only. The client may be running any supported SQL *Net V2 release. 

Understanding Inactive Sessions From JDBC Connection Pool in Oracle Weblogic Server

JDBC connections are established with the database server when there is an application connection request coming from the client. The JDBC connection pool is intended to have a farm of open JDBC connections to the database which can be borrowed by the application. Performance-wise, this is more efficient, since it saves opening and closing a JDBC connection each time. However, this means that a connection can be idle for quite a long time when there is little activity in the system. Most connections will spend most of their time either sitting in the connection pool waiting for a Java thread to open them, or waiting on the Java thread to do something with the data, or waiting on the network to transfer data between the machines. That means that there will be a reasonable number of connections to the database where the STATUS in V$SESSION is "INACTIVE" at any given point in time. That is perfectly normal.
However, it is important to verify that there is no steady increase in the number of open connections, pointing to a leak of connections that prevent these from being reused and cause the maximum number of connections to be reached. To avoid this:


  • Connections used from the JDBC pool need to be closed after usage by the application code. If close() is not called, connections are not freed and not available for reuse. Please refer to: How To Ensure No ResultSet/Connection/Statement Leaks Exist in Your JDBC Code
  • Connection pools should be configured with timeout properties. Depending on the connection pool, there are particular properties for this. For example, for Oracle Universal Connection Pool (UCP), you can refer to: Controlling Stale Connections
  • There is no mechanism in the Connection pool to determine whether the JDBC connection to the database has been dropped. So the JDBC connection in the pool still seems to be valid until the application borrows it, and then finds out that the connection is invalid as it has been dropped.
  • For this, the connection pool has an option to validate the connection before passing it to the application. On this, for example for UCP, please refer to: Validating connections


JDBC Weblogic server Issue

WebLogic Server Crashes because of native JDBC driver libraries

As type-2 JDBC drivers use native code, problems in these drivers may lead to a crash of the JVM and WebLogic Server. If your server crashes, check information provided in the Binary Core File Analysis . This information will help to track down the native library that causes the crash and provides tips on how to solve the problem.

WebLogic Server or application hangs in JDBC driver methods or functions

A JDBC connection uses an execute thread from WebLogic Server to perform its work. This means that a hanging request to a database blocks one thread in WebLogic Server. Problems with the JDBC connection or the database infrastructure can lead to a hang in WebLogic Server or the application. Information on analyzing this kind of problem is included in the JDBC Causes WebLogic Server Hang Support .


A memory leak of JDBC objects leads to an OutOfMemoryError or growing process size


JDBC objects used by connections from the JDBC connection pool or in application code, that connect directly to a database, are part of the heap or native memory of the process. If these objects are not closed and freed correctly, a memory leak is the consequence, which causes increased heap usage or growing process size, and finally will lead to an OutOfMemoryErrorafter the JVM or the operating system cannot provide free memory anymore.If you see OutOfMemoryErrorsin your system and suspect JDBC objects to be the cause, please check Oracle WebLogic Server Core Server/JVM/Memory Issues Support Patterns - Investigating Out of Memory/Memory Leak Problems .

Oracle Weblogic JDBC General Issue Topics


Troubleshooting JDBC problems by debugging or tracing JDBC

JDBC debugging and tracing sometimes is key in order to find out what is going on and analyze the SQL statements that actually are sent to the database. However JDBC is a multi-layered subsystem, of which only parts are inside WebLogic Server. Debugging and tracing of the JDBC driver layer is highly driver-dependent. Information regarding debug and trace flags for the drivers are available from the driver vendors.
Once you have narrowed the problem down to a specific area, you can activate WebLogic Server’s debugging features to track down the specific problem within the application. Debugging can be activated by setting the appropriate ServerDebug configuration attribute to true. See Debugging JDBC Data Sources

  • DebugJDBCConn (scope weblogic.jdbc.connection) will enable tracing of all connection reserve and release operations in data sources as well as all application requests to get or close connections.
  • DebugJDBCSQL (scope weblogic.jdbc.sql) will print information about all JDBC methods invoked, including their arguments and return values, and thrown exceptions.
  • DebugJDBCDriverLogging (scope weblogic.jdbc.driverlogging) will enable JDBC driver level logging. Note that to get driver level tracing for Oracle, you need to use ojdbc14_g.jar instead of ojdbc14.jar. Note that for this debug scope, it can be turned on once via the command line or configuration when the server is booted but cannot be turned on or off dynamically (due to the DriverManager interface).
These debug flags and tracing can be very verbose, so consider very carefully where you turn on those flags. They will create a lot of output and also possibly have a slight performance impact on your system. They should not be turned on in production systems.

How to tune JDBC connection pools for production environments?

Configuration of JDBC connection pools for production systems is a critical and important task to ensure stability and performance. Some general recommendations may help as a starting point for administrators:

  • Set InitialCapacity = MaxCapacity: This will ensure that all connections are opened during WebLogic Server startup. As creation of a physical database connection is very expensive, all needed connections should be opened immediately and kept open.  Before doing so, first insure that the physical memory of the database can handle all connections as they could easily add up in memory and lead to swaps.
  • Disable shrinking by setting ShrinkingEnabledto false: As mentioned above, creation of physical database connections is expensive, so connections should be established once and kept during the complete lifetime of the WebLogic Server instance.
  • Turn off refresh functionality if it is not needed: 
  • Set TestConnectionsOnReserveto true. This will ensure that connections are tested before they go to the application and are reopened if needed.
  • Set the number of connections in the JDBC pool equal to the number of execute threads that use the connections. This helps to avoid ResourceExceptions.

JDBC Error Causes in WebLogic Server and troubleshoot

Even a transaction timeout will not kill or timeout any action that is done by the resources that are enlisted in this transaction. The actions will run as long as they take, without interruption. A transaction timeout will set a flag on the transaction that will mark it as rollback only, so that any subsequent request to commit this transaction will fail with a TimedOutException or RollbackException. However, as mentioned above, the long running JDBC calls can lead to blocked WebLogic Server execute threads, which can finally lead to a hanging instance, if all threads are blocked and no execute thread remains available for handling incoming requests.

More recent WebLogic Server versions have a health check functionality that regularly checks if a thread does not react for a certain period of time (the default is 600 seconds). If this happens, an error message is printed to your log file similar to following:

The time interval for the health check functionality is configurable. Please check the StuckThreadMaxTime property in the <Server> tag of your config.xml file or the "Detecting stuck threads" section in the WebLogic Server administration console help

The following are some different possible reasons that can cause JDBC calls to lead to a hanging WebLogic Server instance:

WebLogic Server : Binary Core File Analysis

Problem Description

An application gets a binary core file produced when the WebLogic Server process terminates due to some invalid native core (machine specific code). A server crash, JVM crash, machine crash, or HotSpot error may also be associated with this occurrence. This article will describe what steps are needed to gather information from a core file on various platforms.

Problem Troubleshooting

Please note that not all of the following items would need to be done. Some issues can be solved by only following a few of the items.

Troubleshooting Steps

Why does the problem occur?

In order to determine the cause of such an error you need to determine all potential sources of native code used by the WebLogic Server process. The places to focus on are:
  1. The WebLogic Server performance pack. The WebLogic Server performance pack is native code and when enabled could potentially produce such an error. Disable this feature to determine if that is the cause. You can do this via the console or via the command line. Using the console look under the Server tab by setting NativeIOEnabled to false. See the documentation section Enabling Performance Packs to get the exact sequence of steps under the Server tab in the console. The steps are:
    1. Start the Administration Server if it is not already running.
    2. Access the Administration Console for the domain.
    3. Expand the Servers node in the left pane to display the servers configured in your domain.
    4. Click the name of the server instance that you want to configure.
    5. Select the Configuration --> Tuning tab.
    6. If the Enable Native IO check box is selected, please deselect it in the check box so it is now *not* enabled.
    7. Click Apply.
    8. Restart the server.

Investigating ORA-01000: maximum open cursors exceeded WebLogic Server

Problem Description

Oracle uses the OPEN_CURSORS parameter to specify the maximum number of open cursors a session can have at once. When this number is exceeded, Oracle reports an ORA-01000 error. When this error is propagated to WebLogic Server, a SQLException is thrown.
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
This pattern discusses possible causes and solutions of this error when using WebLogic Server.

Problem Troubleshooting

Please note that not all of the following items would need to be done. Some issues can be solved by only following a few of the items.

Weblogic JDBC issue : Too Many Open Cursors in the Oracle database


If the configured amount of allowed open cursors in an Oracle database is exceeded, the following error message will be thrown:

java.sql.SQLException: ORA-01000: maximum open cursors exceeded
This can be due to the following possible causes:

  • Incorrect configuration of the JDBC pool regarding the prepared statement cache. Every prepared statement will use one open cursor in the Oracle database. The statement cache holds prepared statements on a connection basis. This means that the Oracle database will use up to (StatementCacheSize)x(MaxCapacity)open cursors for every configured pool. As open cursors will be used for other objects also (e.g., stored procedures or result sets), the number of open cursors needs to be configured high enough to hold all the statements in the statement cache. The setting for OPEN_CURSORS is per session/connection.

    For additional information on statement cache configuration, please see Monitoring JDBC Resources

getVendorConnection()is called and physical connection is automatically refreshed after each usage by the application (when RemoveInfectedConnections="true")

As this means that connection pooling loses its effect, i.e., every connection is closed and reopened after each usage, please consider carefully if the application code changes or destroys something on the physical connection that makes such a reopen necessary. If and only if this is not the case, set the property RemoveInfectedConnectionsto false.

More more details : http://middlewaretimes.blogspot.com/2014/08/investigating-ora-01000-maximum-open.html


Investigating Weblogic JDBC Issues : Datasource/Pooling


ResourceException during JDBCDataSource.getConnection() (weblogic.common.ResourceException: No resources available)

ResourceExceptionsare thrown when a getConnection()request via a DataSourceto a JDBC connection pool cannot be satisfied because either no connection in the pool or no thread is available to handle the connection request. Cause of the missing resource could be either one of:

  • Connection leak in the application.
    Connections used from the JDBC pool need to be closed after usage by the application code. If close() is not called, connections are not freed and not available for reuse. A possible symptom for a connection leak for Oracle JDBC connection pools can be an error message:
    ORA-00020 - maximum number of processes (%s) exceeded
    Enabling WLS JDBC trace logging is a good way to identify potential leaks. Messages such as the following get logged when the WebLogic pool has been watching its connections in use, and this connection is currently reserved, but has not been used at all for the inactive-connection-timeout period.
    <Warning><Common><BEA-000620><Forcibly releasing inactive resource "weblogic.jdbc.common.internal.ConnectionEnv" back in the pool "mypool"
    This typically means the application leaked the connection by losing the reference to the connection without/before closing it. The WebLogic logging will include a full stacktrace of where the connection was reserved. This information should help in reviewing how this connection was managed (lost, missed or unused) without closing.

WebLogic Server : Investigating JDBC Issues

The following topics will be covered in this blog.

  • JDBC Datasource/Pooling Issues
    Weblogic Server obtains JDBC connections from a Datasource. Each configured datasource contains a pool of database connections. Configuring it correctly ensures performance and stability of applications and the server itself. Some misconfiguration could lead to many problems such as, but not limited to:
    • weblogic.jdbc.extensions.PoolLimitSQLException
    • weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool
    • Leaks caused by incorrect management of connections in application code
    • Poor performance of the DBMS or the network, so that connection requests to the underlying database lead to very long startup or communication times for the WebLogic Server
    • Errors or java exceptions during JDBC connectivity setup
    • Connection refresh/reconnect problems after the database was down

WebLogic Server JDBC Profiling Options

Connection Usage

            Collect information about threads currently using connections from the pool
             Data Collected:
            * PoolName - name of the data source to which this connection belongs
            * ID - connection ID
            * User - stack trace of the thread using the connection
            * Timestamp - time stamp showing when the connection was given to the thread

             Sample:  

  ####<DemoDataSource1 > <WEBLOGIC.JDBC.CONN.USAGE> <Wed Apr 23 07:39:02 EST 2014> <java.lang.Exception
            at weblogic.jdbc.common.internal.ConnectionEnv.setup(ConnectionEnv.java:356)
            at weblogic.common.resourcepool.ResourcePoolImpl.reserveResource(ResourcePoolImpl.java:364)
            at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:471)
            at weblogic.jdbc.common.internal.ConnectionPool.reserve(ConnectionPool.java:363)

            …