How to enable trace for a CRM session

I was being asked to examine a performance issue within one of our CRM application screens, after some users complained about a specific long time action. First thing, I tried to enable trace for the CRM session, but It turned out that it’s definitely not simple to identify a CRM session. Especially in my case, when a session opens two (sometimes more) database sessions. It’s quite impossible actually. So how it is possible to trace those CRM sessions anyway? Oracle has provided an option to execute custom code for every session opened in the database through a system profile. This profile called “Initialization SQL Statement - Custom” (the short name is 'FND_INIT_SQL') and allows customize sql/pl*sql code. Once setting this profile in user level, each session opened for this user will first execute the code within the profile. No matter which type of activity the user does – Forms, CRM, Concurrent request, or anything else that opens a database session – the content of this profile will be executed. So, clearly we can use this capability to enable Trace for users sessions. Steps to enable trace for specific user: Login with “Application Developer” responsibility Open the “Create Profile” form –> Query the profile “FND_INIT_SQL” Make sure that “visible” and “updateable” are checked in user level.   Switch responsibility to “System Administrator” Navigate to Profile –> System –> Query the profile “Initialization SQL Statement - Custom” in user level for the user we would like to enable trace for.   Update the profile option value in user level to the following: BEGIN FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||'AVIADE' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); END;     ** Just replace AVIADE with the user you enable trace for.    Now, after the user logout from the application (the user you enabled trace for), the user can login and reproduce the issue.   When finish to reproduce the issue, you should disable the trace by clearing the profile option value and update it to NULL. (profile “Initialization SQL Statement – Custom” of course..) The trace file/s will wait for you in your udump (user_dump_dest init’ parameter) directory. Since I enabled and disabled the trace quite a few times while investigating my performance issue, I wrote these handy simple programs which enable and disable the trace for a user in a quick and easy manner. Execute this program to enable trace for a specific user: (substitute step 6 above) DECLARE   l_ret     boolean;   l_user_id number; BEGIN   select user_id     into l_user_id     from fnd_user    where user_name = '&&USER_NAME';   l_ret := fnd_profile.SAVE(X_NAME        => 'FND_INIT_SQL',                             X_VALUE       => 'BEGIN FND_CTL.FND_SESS_CTL('''','''','''', ''TRUE'','''',''ALTER SESSION SET TRACEFILE_IDENTIFIER=''||''''''''||''&&USER_NAME'' ||''''''''||'' EVENTS =''||''''''''||'' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 ''||''''''''); END;',                             X_LEVEL_NAME  => 'USER',                             X_LEVEL_VALUE => l_user_id);   commit;   dbms_output.put_line('Profile has updated successfully'); EXCEPTION   when others then     dbms_output.put_line('Failed to update the profile: '||sqlerrm); END;   Execute this program to disable trace for a specific user: (substitute step 8 above) DECLARE   l_ret     boolean;   l_user_id number; BEGIN   select user_id     into l_user_id     from fnd_user    where user_name = '&USER_NAME';   l_ret := fnd_profile.DELETE(X_NAME        => 'FND_INIT_SQL',                               X_LEVEL_NAME  => 'USER',                               X_LEVEL_VALUE => l_user_id);   commit;   dbms_output.put_line('Profile has erased successfully'); EXCEPTION   when others then     dbms_output.put_line('Failed to erase the profile: '||sqlerrm); END; Hope you find it helpful… Feel free to leave a comment or share your thought about this issue. Aviad

Oracle Database Resource Manager 11g - Undocumented New Parameters

I've played around with Oracle Database Resource Manager in 10g and it's quite nice and might be very useful for high CPU usage systems, but I found the inability to limit I/O as a drawback since in most cases I've faced the need to limit I/O is more necessary than CPU limit. When you have, let's say, 8 CPU's on your machine, you need all the 8 to be 100% utilized by Oracle sessions for the resource manager start limit sessions. However, if your machine I/O capabilities are 50 mbps, you need only one or two sessions which perform intensive I/O (batch job/heavy report) to make the database very heavy. In Oracle Database 11g Release 1, Resource Manager has gotten some new features related to I/O. So I've installed the 11g, made some tests and found some interesting issues. I'm not going to write about Resource Manager basics or about 11g enhancements as some great articles have already been published about it. For example, you can read Tim's blog post - "Resource Manager Enhancements in Oracle Database 11g Release 1" But... I'm going to discuss one missing capability (in my opinion) that will hopefully be available with Oracle Database 11g Release 2 with 2 new parameters which have already available but inactive and undocumented. For those who are not familiar with Oracle Database Resource Manager I'll try to give a short basic introduction: Oracle Database Resource Manager helps us to prioritize sessions to optimize resource allocation within our database by: Creating groups of sessions (Consumer Groups) based on similar resource requirements Allocate resources to those groups (Resource Plan Directive) Resource Plan - is a container of Resource Plan Directives that allocate resources to Consumer Groups Only one Resource Plan is active at a time. When Oracle Database 11g was introduced, some new features for Resource Manager related to I/O have been revealed. Among them: I/O Calibration - New procedure which helps to assess the I/O capabilities of the database storage. The output of this procedure (CALIBRATE_IO) is max_iops, max_mbps and actual_latency.   Per Session I/O Limits - Unlike previous versions, you can limit I/O requests (or I/O MB) for a session before it moved to a new consumer group. (switch_io_megabytes and switch_io_reqs have been added to CREATE_PLAN_DIRECTIVE procedure)   Oracle have added the option to "capture" Oracle sessions by the I/O requests or by the megabytes of I/O they issued in order to move them to a lower priority consumer group. I have a very fundamental doubt about this enhancements as I don't get the meaning of "capturing" an intensive I/O session and move it to a low priority consumer group which can have only CPU limit ... ?!  The reason we "capture" this session is the amount of I/O it makes, and when we move it to a low priority consumer group we can just limit its CPU resources. We can't limit the amount of I/O for a Consumer Group. It could have been very useful if Oracle had added the ability to limit I/O for a Consumer Group, like we can limit CPU (with mgmt_pN) for a Consumer Group. What is missing here is the ability to limit I/O for a specific Consumer Group in terms of Maximum I/O per second or Maximum megabytes per second. Will Oracle enhance Resource Manager in 11g Release 2 to fulfill this capability? I don't have a confident answer for this question but I assume they will. While playing around I've noticed two new parameters to the CREATE_PLAN procedure - MAX_IOPS and MAX_MBPS. On first sight it looked like the answer to my question - the ability to limit I/O for session within a plan, but it's not... Those two parameter are undocumented and totally ignored in Oracle 11g Release 1 Documentation but available in 11g Release 1 database: -- create a new resource plan -- -- Input arguments: --   plan                       - name of resource plan --   comment                    - user's comment --   cpu_mth                    - allocation method for CPU resources --                                (deprecated) --   active_sess_pool_mth       - allocation method for max. active sessions --   parallel_degree_limit_mth  - allocation method for degree of parallelism --   queueing_mth               - type of queueing policy to use --   mgmt_mth                   - allocation method for CPU and I/O resources --   sub_plan                   - whether plan is sub_plan --   max_iops                   - maximum I/O requests per second --   max_mbps                   - maximum megabytes of I/O per second -- PROCEDURE create_plan(plan IN VARCHAR2,                       comment IN VARCHAR2,                       cpu_mth IN VARCHAR2 DEFAULT NULL,                       active_sess_pool_mth IN VARCHAR2                       DEFAULT 'ACTIVE_SESS_POOL_ABSOLUTE',                       parallel_degree_limit_mth IN VARCHAR2 DEFAULT                       'PARALLEL_DEGREE_LIMIT_ABSOLUTE',                       queueing_mth IN VARCHAR2 DEFAULT 'FIFO_TIMEOUT',                       mgmt_mth IN VARCHAR2 DEFAULT 'EMPHASIS',                       sub_plan IN BOOLEAN DEFAULT FALSE,                       max_iops IN NUMBER DEFAULT NULL,                       max_mbps IN NUMBER DEFAULT NULL                       ); I tried to create a new plan using one of these two parameters, but it returned an error for each value I tried. It turned out that the only valid value to MAX_IOPS and MAX_MBPS is null. begin   dbms_resource_manager.create_plan(plan => 'PLAN_EX_1'                                    ,comment => 'Plan Example 1'                                    ,max_iops => 100); end; / begin * ERROR at line 1: ORA-29355: NULL or invalid MAX_IOPS argument specified ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 38 ORA-06512: at line 2 I've confirmed it with Oracle support and their answer was: "This is currently an expected behaviour, we can not explicitly set either max_iops or max_mbps to any value other than null, that's why these parameters are currently not included in the documentation." So here is my guess: It looks like Oracle are going to enhance Resource Manager to allow I/O limit, but unlike CPU limit through Plan Directive (mgmt_pN), it's going to be a limit in Plan level, so you can create a sub plan with limited I/O resources allocated. Maybe the allocation method parameter (mgmt_mth) will get more valid options except for EMPHASIS and RATIO. I'll keep track of it as soon as Oracle Database 11g Release 2 will be announced and I'll update. You are welcome to leave a comment and/or share your opinion about this topic. Aviad

How to enable SSL for Agile Proxy server

Following the last post about Agile PLM - Web Proxy Server Installation, in this post I'll show how to enable SSL for Agile Proxy server to make Web access to Agile more secure. Apply the SSL certificate Have a signed SSL certificate file (.pfx file) from a Well-known Certificate Authorities (such as VeriSign) Login to the Agile Proxy server with administrator user Navigate to: Start -> Administrative Tools -> Internet information services (IIS) Manager Right click on Agile PLM Web Site -> properties -> Select the Directory Security tab Click on Server Certificate -> next Select Import a certificate from a .pfx file -> next Enter Path and file name of the certificate file (.pfx file) -> next Enter the password for the certificate -> next Update the iFS_External File Manager Open the Agile Java Client Select 'Admin' Tab -> expand 'Server Settings' -> open 'Locations' Select the 'File Manager' tab -> Double click on the iFS_External file manager. Update the File Manager URL from http://... to -> https://... Now you can login to Agile through the Agile Proxy server via https://servername.domain/Agile/PLMServlet. When you'll try to work with files (get file or add new) you might encounter the following error: "The preferred file server : https://servername.domain/Filemgr/AttachmentServlet for user : UserName is down. Please contact the system administrator." You'll although see the following error within OC4J~home~defaulet~island~1 (at [OASHome]/opmn/logs): 08/09/23 10:23:11 javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: No trusted certificate found 08/09/23 10:23:11     at com.sun.net.ssl.internal.ssl.BaseSSLSocketImpl.a(DashoA12275) 08/09/23 10:23:11     at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275) 08/09/23 10:23:11     at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA12275) 08/09/23 10:23:11     at com.sun.net.ssl.internal.ssl.SunJSSE_az.a(DashoA12275) ..... .....   The reason for this error is the certificate for root authority that is not installed on the OAS. The solution is to import Root CA certificate in the JDK embedded inside OAS: Open a web browser and access the Agile web client via https://... Double click on the padlock in the lower right corner of the browser window Select Details and then select the "Copy to File" button Answer the prompts using the defaults and select a filename for the .cer file. Open a command window (Start -> Run -> cmd) Navigate to the [OASHome]\jdk\bin directory Type the following command: keytool -import -keystore [OASHome]\jdk\jre\lib\security\cacerts -file c:\temp\AgileProxy.cer When prompted for the Certificate store password type changeit and then hit Enter When prompted to load the certificate, type yes and hit enter Restart OAS Related: Note 569235.1 - Unable to add or get file attachments because OAS cannot recognize SSL Cert loaded on Proxy server You are welcome to leave a comment.. Aviad