This document will explain a procedure that may be used to associate a specific Oracle database session from the V$SESSION table with a specific Siebel user session. This is typically done to troubleshoot specific issues (for example a database process that is using unusually high resources) or to support levels of user auditing not currently available through the Siebel user interface.

The need for this kind of procedure is especially important if using LDAP or Active Directory (ADSI) authentication with shared database credentials. When implementing the LDAPSecAdpt or ADSISecAdpt it is common to used a single shared database account for all Siebel users. This greatly reduces the amount of administrative overhead since you do not have to maintain accounts in the Siebel application, the external directory, and the database server.

The problem with using shared database credentials, however, is that all Siebel user sessions appear as the same user at the database level. This makes it difficult — if not impossible — to match a specific database session with a specific Siebel user session.

Although less of an issue when database authentication is being used with the DBSecAdpt, there are some instances when this procedure might still be helpful. In particular, situations where the same user has logged in multiple times or where a shared userID is being utilized by an outside system through Siebel Enterprise Application Integration (EAI).

Scope
This reference document is intended for system administrators and database administrators working with the Siebel application and Siebel OLTP database. Individuals attempting to utilize this procedure must have adequate rights on both the Oracle database server and the Siebel application. Furthermore, basic Oracle SQL knowledge is required.

Determining which Oracle database session is being used by a specific Siebel user session
IMPORTANT — Please note that the following procedure is only applicable to Siebel implementations using an Oracle database server for their Siebel OLTP database. Furthermore, the Siebel Application Server(s) must be running on Microsoft Windows due to limitations on how the Oracle database client writes information to the V$SESSION table from non-Windows servers. Note that this restriction applies to the Siebel Application Server(s) and not to the Oracle database server which can be on any supported operating system.
In order to associate the Oracle database session with the Siebel user session, you need to use the a combination of SQL*Plus, the session server management screen, and the application object manager logs. The application manager logs provide the key link. If you look at the very beginning of an application object manager log, you will see something similar to:

2021 2009-07-24 14:15:28 0000-00-00 00:00:00 -0600 00000000 001 003f 0001 09 SSEObjMgr_enu 5207 3948 1356

The last three sets of numbers are the ones we care about.

The first (5207 in this case) is the Siebel Session ID.
The second (3948 in this case) is the Server PID.
The third (1356) in this case is the Thread ID.

You can use the Site Map > Administration – Server Management > Sessions view to find the Session ID and then look at the OM login field to see what user is logged into that session.

Alternatively use the srvrmgr command - list sessions for comp SCCObjMgr_enu . - and compare the task id to determine the user.

On the Oracle database side, you can query the V$SESSION table (make sure you are logged in as the tableowner) using the following WHERE clause format:

WHERE PROCESS=’XXXX:YYYY’

XXXX = Server PID
YYYY = Thread ID

For example, the following SQL query (using the specific values from above) would return the most commonly requested information:

select USERNAME, STATUS, TYPE, TERMINAL, PROGRAM, LOGON_TIME
from v$session where PROCESS='3948:1356';

You can also do the process in reverse by examining the PROCESS field in V$SESSION and then using the returned Server PID and Thread ID to find the Siebel Session ID in the application object manager logs.

The DEP feature (Data Execution Prevention) must be turned OFF when used with Siebel, as Oracle has not yet included the specialized code required to fully implement this feature. This may be done by unchecking the relevant box on the individual client browsers, or by using the Group Policy tools, or by using the relevant Vista control panel. A description of how to turn off DEP with Vista may be found here: http://www.vistax64.com/tutorials/65790-dep-turn-off-programs.html.

DEP can be disabled in the following ways:

1.IE8 > Tools > Internet Options > Advanced tab
2.Starting CMD "As Administrator" and running the command
bcdedit.exe /set {current} nx AlwaysOff
3.IE Group Policy administration tool

In order to support DEP under Windows, a computer's processor must support hardware-enforced DEP. You can determine this by running

wmic OS Get DataExecutionPrevention_Available

in a CMD window; "TRUE" means hardware support exists. The level of DEP running can be determined by running

wmic OS Get DataExecutionPrevention_SupportPolicy

This returns an integer from 0-3.

The default value is "2" in which case only Windows system components and services have DEP applied. A value of "0" means DEP is always off and "1" is always on for all processes.

Setting this value to "3" is a potential work-around. The policy in this case is that DEP is enabled for all processes but Administrators can manually create a list of specific applications which do not have DEP applied.

Implementation of the DEP feature is dependent on using the Microsoft VS 2008 compiler. Siebel is currently compiled with the VS 2003 compiler but plans to move to the VS 2008 compiler in an upcoming release. Until then it is not technically possible for Siebel to support DEP/NX.

It is important to understand that the DEP feature only helps with one kind of security vulnerability - code injection. Siebel software includes protection against this specific mechanism. The ENCODEDATA parameter prevents execution of any code that is in Siebel data, and this is the default setting for Siebel.

Following new IE8 features are not supported in Siebel 8

1.Containers
2.Accessibility
3.Enhanced security (downloads, XSS, DEP - buffer overflow prevention)
4.Tab-level crash recovery.
5.The DEP feature (Data Execution Prevention)

The following configuration changes need to be made on the Siebel Application Server for each Siebel installation, so that it knows the capabilities of the IE8 browser. This configuration step is mandatory for all Siebel versions whenever IE8 is used in any environment, regardless of patch level:

1.Go to SiteMap (shortcut: control+shift+A)
2.Click on Adminstration > "Web Browser" > Browsers
3.Create a Browser with name : MSIE 8.0
4.For the browser add the following capabilites
oUser-Agent -- Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0)
oParent-- IE 8.0
oAccept-- image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/vnd.ms-powerpoint, application/vnd.ms-excel, application/msword, */*
oJumpTab > True
5.Create another browser with name: IE 8.0.
The capabilites for this will be the same as those displayed for the IE 6.0 browser; only the version will change to 8.0
6.Reload all Siebel Application servers

Client Requirement is to automatically logg off Siebel client if an Inactive User/Ex-Employee tries to access the application 

Solution:  

Javascripts can be used to address these kind of requirements. Write the following script in the Browser script method Applet_Load () of Home Page Applet. 

functionApplet_Load ()

{

varEmployeeStatus = TheApplication().GetProfileAttr(“EmpStatus”);

if(EmployeeStatus == “Inactive”)

{

top.location= "start.swe?SWECmd=Logoff";

}

}

 The snipet can be modified to automatically log-off the Siebel Application as per Client requirement. 

Note: - It should be noted that we need to run the Browser Script (Genbscript.bat) batch file since the code is executed in the Applet Browser Script. 

Also note that this code snippet will not only close the Siebel Web Client, but will actually kill the siebel.exe process running on the machine for remote Siebel application.

To check the performance of the application we get the spool and run the queries against the database to check the time it is taking. This check does not return the correct value of the time taken by query when executed through Siebel application. The reason being  when the query is executed Siebel application sets certain parameters at each session level.  Until you set the parameters in the database before execution of the query, correct performance cannot be evaluated.

 Solution :

•  Set the parameters at the logged in Database session with the following commands
  • alter session set optimizer_mode = first_rows_10;
  • alter session set "_hash_join_enabled" = FALSE;
  • alter session set "_optimizer_sortmerge_join_enabled" = FALSE;
  • alter session set "_optimizer_join_sel_sanity_check" = TRUE;
•  Run the SQL query (Spool file -  SQL query set)
•  Capture the performance time related to the specific query with different data set.
• Analyze the bottleneck areas and use Performance optimizer tips to improve the Turn Around time 

• Name - The filename of the object (relative to the URL).
• Finished - The time when the download of the object was completed.
• Requested - The time when the HTTP request for the object was sent to the server.
• Delta Time - The span between the time that this object completed downloading, and the time that the base HTML page was requested.
• Size - The size of the object in bytes
• Elapsed - The amount of time in seconds necessary to download the object. The accuracy of this value is 1 millisecond.
• Bits/Sec - The average data transfer speed in number of bits per second, otherwise known as the baud rate.
• Status - Displays the HTTP return code found in the HTTP response header for this object.

1. The largest improvement was to the login time which was decreased by 37% for the GA connection and 55% for the DA connection.  (GA and DA are NM Client specific network used by the user)
2. All other transactions tested were decreased on average 25%.

1. Total average hit reduction on web server:  34%.
2. translates to 700,000 fewer hits per day, or nearly 3,000,000 hits for Monday - Thursday
3. Most of the impact is during the busiest time frame - 8:00am to 3:00pm