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

How to get details about patch applied in Oracle Applications by OAM & SQL?

There are some tables in oracle apps (AD tables especially) involved when applying patches. Some of them are very useful when we need specific information about patch already applied. I will show the main tables and afterwards some handy related SQL’s to retrieve patch applied details and how we can also get all this information via OAM. AD_APPLIED_PATCHES – The main table when we are talking about patches that applied in Oracle Apps. This table holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (e.g. "merged" patches), then they are considered distinct and this table will therefore hold 2 records (eTRM). I also found that if the applications tier node is separate from the concurrent manager node, and the patch applied on both nodes, this table will hold 2 records, one for each node. AD_PATCH_DRIVERS – This table holds information about all patch drivers included in specific patch. For example if patch contain only one unified driver like u[patch_name].drv then ad_patch_drivers will hold 1 record. On the other hand, if patch contain more than 1 driver, for example d[patch_name].drv and c[patch_name].drv, this table will hold 2 records. AD_PATCH_RUNS – holds information about each execution of adpatch for a specific patch driver. In case a patch contains more than one driver, this table will hold a record for each driver. This table also holds one record for each node the patch driver has been applied on (column APPL_TOP_ID). AD_PATCH_RUN_BUGS – holds information about all the bugs fixed as a part of specific run of adpatch. AD_BUGS – this table holds information about all bug fixes that have been applied. We have 2 options to view applied patch information: 1) via OAM – Oracle Applications Manager 2) Via SQL queries With OAM it’s easy and very intuitive, from OAM site map -> “Maintenance” tab -> “Applied Patches” under Patching and Utilities. Search by Patch ID will get all information about this patch; In addition, drill down by clicking on details will show the driver details. For each driver we can use the buttons (Timing Details, Files Copied, etc.) to get more detailed information. With SQL we can retrieve all the above information, sometimes more easily. For example: How to know which modules affected by specific patch? With OAM: 1) search patch by Patch ID 2) click on Details 3) For each driver click on “Bug Fixes” and look on product column. With SQL: Run the following query, it will show you all modules affected by specific patch in one click… select distinct aprb.application_short_name as "Affected Modules" from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_patch_run_bugs aprb where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and apr.patch_run_id = aprb.patch_run_id and aprb.applied_flag = 'Y' and aap.patch_name = '&PatchName'; Another SQL will retrieve basic information regarding patch applied, useful when you need to know when and where (node) you applied specific patch: select aap.patch_name, aat.name, apr.end_date from ad_applied_patches aap, ad_patch_drivers apd, ad_patch_runs apr, ad_appl_tops aat where aap.applied_patch_id = apd.applied_patch_id and apd.patch_driver_id = apr.patch_driver_id and aat.appl_top_id = apr.appl_top_id and aap.patch_name = '&PatchName'; To check if specific bug fix is applied, you need to query the AD_BUGS table only. This table contains all patches and all superseded patches ever applied: select ab.bug_number, ab.creation_date from ad_bugs ab where ab.bug_number = '&BugNumber'; For any question or additional information you are welcome to leave a comment... Aviad

Apps User Connection Details

This is going to be my first post... so I decided to bring a very useful SQL for apps DBA's. This SQL shows details about users connected to the system (sid, responsibilty, form name, Oracle pid, Application pid and more..) The parameters are Apps Username or Oracle Session Id (put % where you don't pass a parameter). Very useful when you have a heavy session or when you have a DB lock and you want to know who is standing from behind (the applicative user)... It contains 3 SQL's with unions: 1) 1st sql - OA Framework screens (login screen, iSupport etc...) 2) 2nd sql - Responsibility connection details (for Java Applet) 3) 3rd sql - Forms connection details select usr.user_name "Apps Username" ,i.first_connect "First Connect Date" ,ses.sid ,ses.serial# ,ses.module ,v.spid "Oracle Server Process" ,ses.process "Application Server Process" ,rsp.responsibility_name "Responsibility Name" ,null "Responsibility Start Time" ,fuc.function_name "Function Name" ,i.function_type "Function Type" ,i.last_connect "Function Start Time" from icx_sessions i ,fnd_logins l ,fnd_appl_sessions a ,fnd_user usr ,fnd_responsibility_tl rsp ,fnd_form_functions fuc ,gv$process v ,gv$session ses where i.disabled_flag = 'N' and i.login_id = l.login_id and l.end_time is null and i.user_id = usr.user_id and l.login_id = a.login_id and a.audsid = ses.audsid and l.pid = v.pid and l.serial# = v.serial# and i.responsibility_application_id = rsp.application_id(+) and i.responsibility_id = rsp.responsibility_id(+) and i.function_id = fuc.function_id(+) and i.responsibility_id not in (select t1.responsibility_id from fnd_login_responsibilities t1 where t1.login_id = l.login_id) and rsp.language(+) = 'US' and usr.user_name like '&APPS_USER_NAME' and ses.sid like '&SID' union select usr.user_name ,l.start_time ,ses.sid ,ses.serial# ,ses.module ,v.spid ,ses.process ,rsp.responsibility_name ,r.start_time ,null ,null ,null form_start_time from fnd_logins l ,fnd_login_responsibilities r ,fnd_user usr ,fnd_responsibility_tl rsp ,gv$process v ,gv$session ses where l.end_time is null and l.user_id = usr.user_id and l.pid = v.pid and l.serial# = v.serial# and v.addr = ses.paddr and l.login_id = r.login_id(+) and r.end_time is null and r.responsibility_id = rsp.responsibility_id(+) and r.resp_appl_id = rsp.application_id(+) and rsp.language(+) = 'US' and r.audsid = ses.audsid and usr.user_name like '&APPS_USER_NAME' and ses.sid like '&SID' union select usr.user_name ,l.start_time ,ses.sid ,ses.serial# ,ses.module ,v.spid ,ses.process ,null ,null ,frm.user_form_name ,ff.type ,f.start_time from fnd_logins l ,fnd_login_resp_forms f ,fnd_user usr ,fnd_form_tl frm ,fnd_form_functions ff ,gv$process v ,gv$session ses where l.end_time is null and l.user_id = usr.user_id and l.pid = v.pid and l.serial# = v.serial# and v.addr = ses.paddr and l.login_id = f.login_id(+) and f.end_time is null and f.form_id = frm.form_id(+) and f.form_appl_id = frm.application_id(+) and frm.language(+) = 'US' and f.audsid = ses.audsid and ff.form_id = frm.form_id and usr.user_name like '&APPS_USER_NAME' and ses.sid like '&SID';