WebLogic 12.2.1 - New Feature for Data Source System Properties

Within the new release of Oracle WebLogic Server 12.2.1 is a really cool new feature for Data Source System Properties.

In case you have mutliple data sources defined within your Oracle WebLogic Server pointing to the same Oracle Database and mostly using the same Oracle Database user, you have currently limited chances to identify in your v$session, which session belongs to which WebLogic Server Data Source.

For example, you have 2 Data Sources defined in your WebLogic Server, each Data Source is assigned to different Managed Servers within your WebLogic Domain and using the same Oracle User, so you normally see following:

select username, osuser, process, machine, terminal, program
from v$session where username = 'WLS_REPOS';
USERNAME    OSUSER    PROCESS    MACHINE       TERMINAL   PROGRAM
---------   --------  --------   -----------   ---------  ------------------
WLS_REPOS   oracle    1234       wls1221       unknown    JDBC Thin Client
WLS_REPOS   oracle    1234       wls1221       unknown    JDBC Thin Client
WLS_REPOS   oracle    1234       wls1221       unknown    JDBC Thin Client

You just see under the column program "JDBC Thin Client", but you don't see which defined Data Source is behind your connection in the Oracle Database :-(

Since Oracle WebLogic Server 12.2.1 you have now the possibilty to push a defined set of variables from your Data Source definition into the v$session.

Variable	Description
${pid}	First part (up to @) of ManagementFactory.getRuntimeMXBean().getName()
${machine}	Second part of ManagementFactory.getRuntimeMXBean().getName()
${user.name}	Java system property user.anem
${os.name}	System property os.name
${datasourcename}	Name of your Data Source
${partition}	Name of your Partition
${serverport}	Listen Port of your WebLogic Server
${serversslport}	SSL Listen Port of your WebLogic Server
${servername}	Name of your WebLogic Server
${domainname}	Name of your WebLogic Domain

With the above list of defined variables you have a wide range of possibilties to populate them into your v$session.

Snippet of your <datasource_name>-<internal_number>-jdbc.xml Configuration File

  user
  WLS_REPOS


  v$session.osuser
  ${user.name}


  v$session.process
  ${pid}


  v$session.machine
  ${machine}


  v$session.terminal
  ${datasourcename}


  v$session.program
  WLS ${servername} @ ${domainname}

Or you can also modify your data source definition within the Oracle WebLogic Server Console under "Services / Data Sources", just select your Data Source and under "Configuration / Connection Pool" modify the "System Properties"

After saving and activating your changes, simply restart your Managed Server(s) which are the target(s) for your modified Data Sources.

You have to consider following limitations for the v$session columns:

v$session Column	Length Limiations
osuser	30 characters
process	24 characters
machine	64 characters
terminal	30 characters
program	48 characters

With the above decribed changes on your Data Source definition, you are now able to identify easily your different Data Source connections into your Oracle Database :-)

select username, osuser, process, machine, terminal, program
from v$session where username = 'WLS_REPOS' order by 5;
USERNAME    OSUSER    PROCESS    MACHINE       TERMINAL   PROGRAM
---------   --------  --------   -----------   ---------  --------------------------------
WLS_REPOS   oracle    2959       wls1221       myDS       WLS ManagedServer1 @ base_domain
WLS_REPOS   oracle    2959       wls1221       myDS       WLS ManagedServer1 @ base_domain
WLS_REPOS   oracle    2959       wls1221       myTestDS   WLS ManagedServer2 @ base_domain

Read More

JDBC URL with Oracle SID or ServiceName

Today I face a small problem with a definition of a JDBC URL which is referencing an Oracle Database.

I just define:

jdbc:oracle:thin:scott/tiger@localhost:1521/PROD.OCZH.CH

where PROD.OCZH.CH is the ServiceName of my Database, but it fails :-(

After a some investigations, the solution is simple, if you want to use the ServiceName of your Database in the JDBC URL you have to place // in front of the servername:

jdbc:oracle:thin:scott/tiger@//localhost:1521/PROD.OCZH.CH

Read More

Identify your JDBC Connection in v$session

A major problem for DBA's is the fact that in the v$session view it's really hard to identify which session comes from which J2EE Application.
A select over the v$session view just shows the connected users and from which machine the connect is established, but we can't see from which J2EE Application the sessions are coming :-(

With Oracle AS 10.1.3.x we have the possibility to add a property in the data-source.xml, that is displayed in the column "PROGRAM" of the v$session view.

Following changes have to be done at the data-source.xml configuration file:

1. Check if the factory class oracle.jdbc.driver.OracleDriver is used
2. Add the line inside the connection-factory
3. After the changes, restart your OC4J Instance


<data-sources xsi="http://www.w3.org/2001/XMLSchema-instance" nonamespaceschemalocation="http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd" version="10">

<managed-data-source name="conn_pool_hr_app">

<managed-data-source name="conn_pool_hr_app_reporting">

<connection-pool name="conn_pool_hr_app" limit="3"
connections="3">

<connection-factory class="oracle.jdbc.driver.OracleDriver" user="scott" password="tiger" url="jdbc:oracle:thin:@//localhost:1521/PROD"> <property name="v$session.program" value="conn_pool_hr_app">
</property>
</connection-factory>
</connection-pool>

<connection-pool name="conn_pool_hr_app_reporting" limit="3" connections="3">
<connection-factory class="oracle.jdbc.driver.OracleDriver" user="scott" password="tiger" url="jdbc:oracle:thin:@//localhost:1521/PROD">
<property name="v$session.program" value="conn_pool_hr_app_reporting"></property>
</connection-factory>
</connection-pool>
</data-sources>


If we now select over our v$session view, we can see which session comes from which J2EE Application :-)

Read More