Oracle Metasolv solution is hosted on Oracle WebLogic Server and use JDBC data source connection pools to manage database transactions. These JDBC resources connect to the database with an
underlying user ID.
For troubleshooting purpose if someone tries to pull up the connection information from Database(v$session table) it will actually not point to individual application users making connections rather give picture of connections made by these underlying users. MetaSolv M6 uses user IDs are APP_MSLV, APP_API, and APP_INT. In some cases APP_INT which is responsible for WebLogic Integration transactions could be specific to WebLogic domain like in below diagram. Refer the architecture we will discuss as below.
PROBLEM
So the challenge is to identify individual end user connection to monitor database activity done by him/her.As the architecture shown above makes the problem more difficult as compared to single
server architecture because now 1 domain is carrying multiple WebLogic server instances on 1 machine.
Now we will do a deep dive in the issue.
SELECT machine,
program,
username,
terminal,
osuser,
--client_identifier,
status,
count(*)
FROM v$session
WHERE username in ('APP_MSLV','APP_INT','APP_API')
GROUP BY machine, program, username, terminal, osuser, status
ORDER BY count(*) desc;
The result of the above query shows that PROGRAM and TERMINAL values of 'JDBC Thin Client' and 'unknown' are quite generic and are of less use. These values does not specifically tell which JDBC DataSource is the PROGRAM and which DOMAIN is shown as terminal.
SOLUTION
1. Access the WebLogic Server Administration Console
2. Access the JDBC Data Sources
3. Select any of the Data Sources
4. Select the Connection Pool tab
5. Enter "v$session.terminal={YOUR SERVER NAME}" and "v$session.program={DATA SOURCE NAME}" in the Properties: field directly below "user" property like the example in the image below.
6. Click Save
7. Repeat steps 3 through 6 for remaining Data Sources
8. Restart the WebLogic server
RESULT
Configuration of connection properties of each DataSource facilitates effective troubleshooting by providing the ability to view the source of each connection.
MetaSolv Solution sets the v$session.client_identifier as a method to instantiate the physical end user's user ID which is used to set LAST_MODIFIED_USERID columns throughout the database. The results below were obtained by editing the query and uncommenting the client_identifier column. The results show the active APP_MSLV connection is being used by the application's BPADMIN1 user ID. The actual JDBC data source connection pool will remain unknown until the configuration changes outlined in the solution section of this document have been properly applied.
NOTE: The console indicates that the changes have been activated and no restarts are necessary. However, the connection property values will NOT be visible in the V$SESSION view until the server is restarted.
OTHER IMPORTANT QUERIES
1.
SELECT v$session.machine as "Machine Name",
v$session.program as "MetaSolv Solution DataSource",
v$session.username as "DataSource User",
v$session.terminal as "WebLogic Server Name",
v$session.osuser as "OS User of WebLogic Process",
asap.security_users.description as "MetaSolv Solution User",
v$session.sid as "Session ID",
v$session.status as "JDBC Connection Status"
FROM v$session, asap.security_users
/*
View connection pools used only by GUI. Comment out as necessary.
*/
WHERE v$session.program in ('mslvNoneTxDataSource','mslvDataSource')
/*
View all connection pools.
*/
-- WHERE v$session.program in ('mslvNoneTxDataSource',
-- 'mslvDataSource',
-- 'mslvProcDataSource',
-- 'mslvWliDataSource',
-- 'cgDataSource-nonXA',
-- 'p13nDataSource',
-- 'cgDataSource',
-- 'bpmArchDataSource')
/*
uncomment for only ACTIVE connections
*/
--AND v$session.status = 'ACTIVE'
AND upper(asap.security_users.name(+)) = v$session.client_identifier
ORDER BY v$session.program desc;
The results below shows the MetaSolv Solution application's user (Job Master Account 1) has an active connection, SID 5357, associated to the mslvNoneTxDataSource JDBC DataSource coming from the Machine 1 WebLogic server hosted on known machine.
2.
SELECT nvl(asap.security_users.description,'ORACLE PROC')||' ('||SES.sid||')' as "MetaSolv Solution User (SID)",
MACHINE as "Machine Name",
PROGRAM as "MetaSolv Solution DataSource",
TERMINAL as "WebLogic Server Name",
REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
FROM V$SESSION SES,
V$SQLtext_with_newlines SQL,
asap.security_users
WHERE SES.STATUS = 'ACTIVE'
AND SES.USERNAME is NOT NULL
AND SES.SQL_ADDRESS = SQL.ADDRESS
AND SES.SQL_HASH_VALUE = SQL.HASH_VALUE
AND upper(asap.security_users.name(+)) = SES.client_identifier
AND SES.AUDSID <> userenv('SESSIONID')
/*
View connection pools used only by GUI. Comment out as necessary.
*/
AND SES.program in ('mslvNoneTxDataSource','mslvDataSource')
/*
View all connection pools.
*/
--AND SES.program in ('mslvNoneTxDataSource',
-- 'mslvDataSource',
-- 'mslvProcDataSource',
-- 'mslvWliDataSource',
-- 'cgDataSource-nonXA',
-- 'p13nDataSource',
-- 'cgDataSource',
-- 'bpmArchDataSource')
ORDER BY runt desc, 1, sql.piece;
The results below shows similar information, but now includes the current SQL statement.
3.
SELECT v$session.machine as "Machine Name",
v$session.terminal as "Terminal",
v$session.client_identifier,
asap.security_users.description as "MetaSolv Solution User",
v$session.program as "Program",
v$session.sid,
v$session.status as "Status",
v$lock.type,
v$lock.id1,
v$lock.id2,
decode(v$lock.lmode,
0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
v$lock.lmode) as "Locked Mode",
decode(v$lock.request,
0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
v$lock.request) as "Request",
dba_objects.owner AS "Object Owner",
dba_objects.object_name AS "Object Name",
dba_objects.object_type AS "Object Type",
v$lock.ctime,
v$lock.block
FROM v$lock,
v$session,
v$locked_object,
dba_objects,
asap.security_users
WHERE v$lock.sid = v$session.sid
AND v$session.sid = v$locked_object.session_id
AND v$locked_object.object_id = dba_objects.object_id
AND upper(asap.security_users.name(+)) = v$session.client_identifier
AND v$lock.type = 'TX';
The results below shows similar information in a blocking/blocked transaction scenario which include each of their locked objects. Note that blocking lock was achieved by issuing an UPDATE statement against an ASAP.CUST_ACCT.CUST_ACCT_ID from a SQL Developer session (SID = 2194).
Hope above helps in effective troubleshooting the database connections and Identify/Monitor
JDBC Connections on Metasolv Database, please leave your feedback or query.
