To allow the Oracle Database to produce meaningful log, audit trail and trace information that allow problem analysis of issues and interpretation and optimization of run time behavior, it is necessary that the database session execution context is set. Without it, efficient administration of the RDBMS is not possible. This context that should be set by any consumer of the database consists of aspects such as who (is the user for which the database is doing this work), when (is the moment in time that the database should assume), what (is the application or component – and the specific version – that is currently executing logic that requires work from the database) and why (what is the functional background for this activity).
Context elements are associated with a database session. Some of these are [only] set at login time, when the connection is created and the session is initiated and others can be set throughout a session with alter session statements, calls to specific supplied PL/SQL packages or manipulation through OCI or JDBC interfaces. Some of the most valuable settings in the context are:
- MODULE – the (free format) name of the client application’s module or program unit
- ACTION- a free format indication of the feature or function in the client application from which the current activity was triggered
- CLIENT_INFO – additional free format information about the (state of) client (program) for which the session currently performs activities
- CLIENT_IDENTIFIER – identifier describing the light weight end user connected to the client application
- the ECID – the Fusion Middleware execution context identifier that identifies a cross-WebLogic-and-database-node chain of activities executed to service a single request
The values for these context attributes are set through calls to packages – dbms_application_info and dbms_session – except for the ECID which is set in a handshake between Fusion Middleware/WebLogic and the Database (see blog article).
Many of the predefined database context settings are available from the dynamic V$ views, such as V$SESSION, where DBAs can leverage them for monitoring and analysis purposes. They are also recorded in ADH, AWR, the database audit trail and in trace files. Additionally, the context values can be used in policies (VPD, Data Redaction, Fine Grained Auditing, Resource Plans/Consumer Groups) and in custom filters and logging mechanisms.
This article shows how the SOA Suite when it accesses the database through the database adapter could pass the appropriate database session context settings and how in the database these should be set.
The sample application is a SOA Composite called FlightService with three database adapter bindings that each call out to a procedure or function in a PL/SQL package in the database.
The application is introduced in some details in this article. We will add the pieces to set the proper database context for the database actions initiated from the SOA Composite FlightService.
Implementation of Database Session Context Propagation
The interaction with the PL/SQL package through the database adapter is based on User Defined Types – custom Database object definitions. I consider use of these types a best practice. They allow us to exchange nested data structures in single round trips, provide data structures that map very well to XML messages and that can very well be handled by the database adapter. Additionally, use of these UDTs allow the database developers to work in a rather elegant way – leveraging their tools to their full potential.
I have created an additional UDT for the session context details:
create or replace
type session_context_t force as object
( client_identifier varchar2 (100)
, module varchar2(48)
, action varchar2(32)
, client_info varchar2(32)
, ecid varchar2(50)
, flashback_time date
, edition varchar2(128)
);
I have added an input parameter based on this type to each of the three program units in the PL/SQL API, like this:
create or replace
package flight_service
as
function retrieve_flight_details
( p_flight_number in number
, p_airline_code in varchar2
, p_departure_date in date
, p_session_context in session_context_t
) return flight_t
;
function retrieve_passenger_list
( p_flight_number in number
, p_airline_code in varchar2
, p_departure_date in date
, p_session_context in session_context_t
) return passenger_list_t
;
procedure set_flight_status
( p_flight_number in number
, p_airline_code in varchar2
, p_departure_date in date
, p_flight_status in out varchar2
, p_session_context in session_context_t
)
;
end flight_service;
In the implementation of each of three units, I have added a call to a local, private procedure – for example:
function retrieve_flight_details
( p_flight_number in number
, p_airline_code in varchar2
, p_departure_date in date
, p_session_context in session_context_t
) return flight_t
is
l_flight flight_t;
begin
set_session_context(p_session_context => p_session_context);
select flight
into l_flight
from
The implementation of this procedure is the linking pin that actually sets the database session context:
procedure set_session_context
( p_session_context in session_context_t
) is
begin
if p_session_context is not null
then
DBMS_APPLICATION_INFO.SET_MODULE(p_session_context.module, p_session_context.action );
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(p_session_context.client_info);
DBMS_SESSION.SET_IDENTIFIER(p_session_context.client_identifier);
end if;
end set_session_context;
After adding the input parameter, I have re-run the database adapter wizard for each of the three bindings. Simply by clicking next on each page and finish on the last one, the database adapter binding configuration – and the associated XSD – were extended with the new parameter.
The XSD definition for each DB adapter binding was extended with the P_SESSION_CONTEXT element and associated type:
I need to make sure that the three routing rules in the Mediator take care of providing values for this element. It seemed easiest and most appropriate to do so through assigning values rather than by including in the transformation – although I am not yet sure about the best approach. Here is the Mediator editor:
and here are the four value assignment rules – one which is special for this routing rule (‘getFlightDetails’ for the value of the action element):
The next step could be to derive values from properties such as Flow (or Composite) Instance Title, the user identity set in the middleware context and perhaps composite sensor values.
Database Session Context in Action
Perform a request to the REST service
that through the Service Bus Project initiates a SOA Composite that can be traced in EM FMW Control:
The ECID is exposed in this management UI and this same ECID is passed by WebLogic to the database (see this article for more details ). Using the ECID, we can query the V$SESSION view – to learn about other session characteristics or session context settings for the database session that performed work on behalf of this ECID:
Note how the values for MODULE, ACTION, CLIENT_IDENTIFIER and CLIENT_INFO are available in this view and also in ADH, AWR, the database audit trail and in trace files. Additionally, the context values can be used in policies (VPD, Data Redaction, Fine Grained Auditing, Resource Plans/Consumer Groups) and in custom filters and logging mechanisms.
The values were passed from the Mediator to the Database Adapter (except for the ECID that is propagated by the FMW infrastructure automatically) , as the following trace overview shows:
