Some importent querys from application monitoring

Finding time required for Gather Stats

If we run gather schema stats and want to the progression then we can check by the below query. 

SELECT *
FROM v$session_longops 
WHERE opname LIKE '%Gather Schema Statistics%';

For gather table stats the opname will be like Table Scan.
 
We can get the unique session id and then also we can check the time required.

SELECT
SID,SERIAL#,OPNAME,TOTALWORK,START_TIME,TIME_REMAINING,ELAPSED_SECONDS,message FROM  v$session_longops
WHERE   SID=6844;

Check the Concurrent requests which are running

Use the below query for finding all the concurrent requests which are running.

Select /*+ RULE */ substr(Concurrent_Queue_Name,1,12) Manager Name,
       Request_Id Request, User_Name,
       fpro.OS_PROCESS_ID OSprocess,
      fcr.oracle_process_id LocalProcess,
       substr(Concurrent_Program_Name,1,35) Program, Status_code,
       To_Char(Actual_Start_Date, 'DD-MON-YY HH24:MI') Started
       from apps.Fnd_Concurrent_Queues fcq, apps.Fnd_Concurrent_Requests fcr,
      apps.Fnd_Concurrent_Programs fcp, apps.Fnd_User Fu, apps.Fnd_Concurrent_Processes fpro
       WHERE
       Phase_Code = 'R' And
       Status_Code <> 'W' And
       fcr.Controlling_Manager = Concurrent_Process_Id       And
      (fcq.Concurrent_Queue_Id = fpro.Concurrent_Queue_Id    And
       fcq.Application_Id      = fpro.Queue_Application_Id ) And
      (fcr.Concurrent_Program_Id = fcp.Concurrent_Program_Id And
       fcr.Program_Application_Id = fcp.Application_Id )     And
       fcr.Requested_By = User_Id
       order by Started

Compiling Objects in Database

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

Terminating a Concurrent Requests

Concurrent Requests can be terminated from front end. But we can also terminate the Concurrent Request from the back end.

update apps.fnd_concurrent_requests set phase_code='C' , status_code='X' where request_id=&REQUEST_ID;

Note: Please remember to kill the process for that request first before terminating the concurrent request .

The related process Id for the Concurrent Requests can be determined by

select ORACLE_PROCESS_ID from apps.fnd_concurrent_requests where REQUEST_ID=&REQUEST_ID;

 

Stopping Concurrent Manager

Concurrent manager can be stopped by running 

adcmctl.sh stop  <appsusername/password>

We can check if the Concurrent processes are still running by 

ps -ef |grep FNDLIBR 

If there are processes running the we can manually kill the processes.

kill -9 `ps -ef|grep FNDLIBR|awk '{print $2}'`

or

ps -ef|grep FNDLIBR|awk '{print $2}'|xargs kill -9

 

Getting current Session ID

• select sid from v$session where audsid = sys_context('userenv','sessionid');

• select distinct sid from v$mystat;

 

 

Thanks 

Srini

 

 

Monitoring Concurrent Managers

Oracle supplies several useful scripts (located in $FND_TOP/sql directory) for monitoring the concurrent managers:

afcmstat.sql: Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql: Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.

afcmcreq.sql: Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql: Displays the requests that are pending, held, and scheduled.

afrqstat.sql: Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql: Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.

afimlock.sql: Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.

 

Thanks 

Srini

Finding Debug Enabled on Profiles at DB level and Validating Font End user password in Oracle Apps

Finding Trace Enabled on Concurrent Program at DB level

We can use the below query to find the trace enabled at DB level.

 Select user_concurrent_program_name

 from applsys.fnd_concurrent_programs fp,

 applsys.fnd_concurrent_programs_tl fct

 where fp.concurrent_program_id = fct.concurrent_program_id

 and enable_trace <> 'N';

 

 

Validating Font End user password in Oracle Apps

If we need to validate the front end user password is working fine or not then we can fire the below query to check the authentication of password.

Login to Database as Apps user.

SQL> select fnd_web_sec.validate_login('GUEST','welcome') from dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','WELCOME')
--------------------------------------------------------------------------------
Y

 

 

 

Finding Debug Enabled on Profiles at DB level

Below query can be useful for finding the Debug Enabled on Profiles at Site,Application,Responsibilty and User Level.

select fpot.user_profile_option_name profile_name
, 'Site' lo
, 'SITE' lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10001 /* Site Level */
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'Apps' lo
, fa.application_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_APPLICATION_TL fa
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
and fpov.level_id = 10002 /* Application Level */
and fpov.level_value = fa.application_id
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'Resp' lo
, frt.responsibility_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_RESPONSIBILITY_TL frt
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and frt.language = Userenv('Lang')
and fpot.language = Userenv('Lang')
and fpov.level_id = 10003 /* Responsibility Level */
and fpov.level_value = frt.responsibility_id
and fpov.level_value_application_id = frt.application_id
and fpo.end_date_active is null
union all
select fpot.user_profile_option_name profile_name
, 'User' lo
, fu2.user_name lov
, fpov.profile_option_value pov
from applsys.FND_PROFILE_OPTIONS_TL fpot
, applsys.FND_PROFILE_OPTIONS fpo
, applsys.FND_PROFILE_OPTION_VALUES fpov
, applsys.FND_USER fu
, applsys.FND_USER fu2
where
(fpo.profile_option_name like '%DEBUG%' or
 fpo.profile_option_name like '%TRACE%' or
 fpo.profile_option_name like '%DIAG%' or
 fpo.profile_option_name like '%SQL%')
and fpot.profile_option_name = fpo.profile_option_name
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpov.level_id = 10004 /* User Level */
and fpov.level_value = fu2.user_id
and fpot.language = Userenv('Lang')
and fpo.end_date_active is null
order by profile_name, lo, lov;

 

 

Thanks 

Srini

 

 

Finding Responsibilities for a User in Apps

We can use the below query for finding the all the responsibility for a particular user.

SELECT fu.user_name,
         fr.responsibility_name,
         furg.start_date,
         furg.end_date
    FROM fnd_user_resp_groups_direct furg,
         fnd_user fu,
         fnd_responsibility_tl fr
   WHERE     fu.user_name = '&user_name'
         AND furg.user_id = fu.user_id
         AND furg.responsibility_id = fr.responsibility_id
         AND fr.language = USERENV ('LANG')
ORDER BY start_date;

We can modify the above query to find all the User associated with a particular responsibility too.

SELECT fu.user_name,
         fr.responsibility_name,
         furg.start_date,
         furg.end_date
    FROM fnd_user_resp_groups_direct furg,
         fnd_user fu,
         fnd_responsibility_tl fr
   WHERE      fr.responsibility_name = '&responsibilty_name'
         AND furg.user_id = fu.user_id
         AND furg.responsibility_id = fr.responsibility_id
         AND fr.language = USERENV ('LANG')
ORDER BY start_date;

 

 

Thanks 

Srini

Finding Request Group associated for a Program

The below query can be useful for finding request group associated for a program.

SELECT
rg.application_id "Request Group Application ID",
rg.request_group_id "Request Group - Group ID",
rg.request_group_name,
rg.description,
rgu.unit_application_id,
rgu.request_group_id "Request Group Unit - Group ID",
rgu.request_unit_id,cp.concurrent_program_id,
cp.concurrent_program_name,
cpt.user_concurrent_program_name,
DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND cpt.user_concurrent_program_name ='Full/Long Program Name';

 

Thanks 

Srini

Finding Scheduled Concurrent Program

The below query can be used for getting all the details for a scheduled concurrent program.

select a.requested_by,a.status_code,a.phase_code,a.request_id, b.user_concurrent_program_name,c.concurrent_program_name,a.requested_start_date, c.execution_method_code,
d.execution_file_name,d.execution_file_path
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_concurrent_programs c,
apps.fnd_executables d
where a.status_code in ('Q','I')
and a.concurrent_program_id = b.concurrent_program_id
and b.concurrent_program_id = c.concurrent_program_id
and c.application_id=d.application_id
and c.executable_id=d.executable_id
and a.requested_start_date > SYSDATE
and a.hold_flag = 'N'
order by 1;

Finding the schedules for a particular program we can use the below query

 

select a.requested_by,a.status_code,a.phase_code,a.request_id, b.user_concurrent_program_name,c.concurrent_program_name,a.requested_start_date, c.execution_method_code,
d.execution_file_name,d.execution_file_path
from apps.fnd_concurrent_requests a, apps.fnd_concurrent_programs_tl b, apps.fnd_concurrent_programs c,
apps.fnd_executables d
where a.status_code in ('Q','I')
and a.concurrent_program_id = b.concurrent_program_id
and b.concurrent_program_id = c.concurrent_program_id
and c.application_id=d.application_id
and c.executable_id=d.executable_id
and a.requested_start_date > SYSDATE
and a.hold_flag = 'N'
and b.user_concurrent_program_name='&Program_Full_Name'
order by 1;

 

 

Thanks 

Srini

Creating Custom Top in Oracle Application

Below are the steps to create custom top in Oracle Application 

Step 1: Go to  APPL_TOP Dircetory in UNIX

Cd $APPL_TOP

Step 2: Create Custom Top Under APPL_TOP Dir

Mkdir CUSTNAME_TOP (Name of Directory)

Make sure all the files and Directories are same as Other Product Dir.

Step 3: Add Custom Top entry in APPLSYS.env file

Step 4: Login Oracle apps using SYSADMIN or AOL

Navigate: Applications->Register

Add your Custom top Entry Here

Application: CustomeApplication name

Short name: CUSTNAME

BasePath: CUSTNAME_TOP

Description: CustomApplication.

Step 5: Restart the Internal Concurrent Manager (ICM) for New Changes to take effect.

Step 6: Validate Custom Entry in

Select BASEPATH,PRODUCT_CODE,APPLICATION_SHORT_NAME

From fnd_application

Where application_Short_name like 'CUSTNAME_TOP%'

 

Thanks 

Srini

Finding the Languages Installed in Oracle Applications

Finding the Languages Installed in Oracle Applications

The below query can be used for the finding the languages installed in Oracle Application.

SELECT Language_code, NLS_language, Installed_flag
  FROM fnd_languages
 WHERE installed_flag IN ('I', 'B')

The Installed_Flag has 3 values

I- Installed

B- Base

D- Disabled

 

 

 

Tables which are purged with FND_CONC_CLONE.SETUP_CLEAN

When EXEC FND_CONC_CLONE.SETUP_CLEAN is executed various tables are purged.

To know which tables are purged/cleaned we can view $FND_TOP/patch/115/sql/AFCPCLNB.pls

 

Thanks 

Srini 

Finding the Blocking Session at Database

The below query can be used to find the blocking sessions at the database level.

Query

SELECT (SELECT username
          FROM v$session
         WHERE sid = a.sid)
          blocker,
       a.sid,
       ' is blocking ' "IS BLOCKING",
       (SELECT username
          FROM v$session
         WHERE sid = b.sid)
          blockee,
       b.sid,
       a.TYPE "WITH LOCK TYPE"
  FROM v$lock a, v$lock b
 WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2

 

Thanks 

Srini

Finding Concurrent Program Past Execution Details

If we need to find the Concurrent Program previous\past execution details then the below query will be useful.

Script:

select request_id, phase_code, status_code ,
to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start date",
to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started date",
to_char(actual_completion_date,'DD-MM-YY hh24:mi:ss') "Actually completed date",
substr(ARGUMENT_TEXT,1,100) Parameter_Passed ,
TRUNC (MOD ((nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date) * 24,
                     24
                    )
               ) "Hr",
         TRUNC (MOD (  (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
                     * 24
                     * 60,
                     60
                    )
               ) "Mins",
         TRUNC (MOD (  (nvl(cr.actual_completion_date,sysdate) - cr.actual_start_date)
                     * 24
                     * 60
                     * 60,
                     60
                    )
               ) "Sec"
from fnd_concurrent_requests  cr where concurrent_program_id in
( select concurrent_program_id
  from fnd_concurrent_programs
  where concurrent_program_name = '&prog_short_name' )
and actual_start_date>sysdate - &noofdaysago
order by phase_code,actual_start_date asc

Thanks 
Srini

Finding Patch Level of a Product in Oracle Application

If we need to find the latest patch level for a particular product then we can use the below query.

Syntax:

select patch_level
from fnd_product_installations where patch_level like upper('%&product_name%')

Output:

PATCH_LEVEL                  
------------------------------
11i.AD.I.7                    

 

Thanks 

Srini

End Date a Responsibility for Users

If we need to end date a responsibility for a user then we can use the below syntax.

Syntax:

DECLARE
   p_user_name           VARCHAR2 (50);
   p_resp_name           VARCHAR2 (50) := 'Application Developer'; /*Responsibility Name*/
   v_user_id             NUMBER (10) := 0;
   v_responsibility_id   NUMBER (10) := 0;
   v_application_id      NUMBER (10) := 0;
BEGIN
   BEGIN
      SELECT application_id, responsibility_id
        INTO v_application_id, v_responsibility_id
        FROM fnd_responsibility_vl
       WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line ('Responsibility not found.');
         RAISE;
      WHEN TOO_MANY_ROWS
      THEN
         DBMS_OUTPUT.
         put_line ('More than one responsibility found with this name.');
         RAISE;
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Error finding responsibility.');
         RAISE;
   END;

   /*DBMS_OUTPUT.
   put_line (
         'Responsibility ID'
      || v_responsibility_id
      || 'and'
      || 'Application ID'
      || v_application_id);*/
  
   FOR u
      IN (SELECT fu.user_id, furg.start_date,furg.description
            FROM fnd_user_resp_groups_direct furg,
                 fnd_user fu,
                 fnd_responsibility_tl fr
           WHERE     fr.responsibility_name = p_resp_name
                 AND furg.user_id = fu.user_id
                 AND fu.user_name <> 'SYSADMIN'
                 AND furg.responsibility_id = fr.responsibility_id
                 AND fr.language = USERENV ('LANG'))
   LOOP
      v_user_id := u.user_id;
      /*DBMS_OUTPUT.put_line ('User_name' || v_user_id);*/

      BEGIN
         DBMS_OUTPUT.put_line ('Initializing The Application');
         fnd_global.
         apps_initialize (user_id        => v_user_id,
                          resp_id        => v_responsibility_id,
                          resp_appl_id   => v_application_id);
         DBMS_OUTPUT.
         put_line (
            'Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');
         fnd_user_resp_groups_api.
         update_assignment (
            user_id                         => v_user_id,
            responsibility_id               => v_responsibility_id,
            responsibility_application_id   => v_application_id,
            security_group_id               => 0,
            start_date                      => u.start_date,
            end_date                        => TRUNC (SYSDATE) - 1,
            description                     => u.description);
         DBMS_OUTPUT.
         put_line ('The End Date has been set for responsibility');
         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Error calling the API');
            RAISE;
      END;
   END LOOP;
END;

 

Thanks 

Srini

Scipt to Find the Concurrent Manager for a Concurrent Program

We can use the below query to find the Manager name for a Concurrent Program


Script

SELECT fcqc.INCLUDE_FLAG,
       fcqc.QUEUE_APPLICATION_ID,
       fcq.USER_CONCURRENT_QUEUE_NAME,
       fcp.CONCURRENT_PROGRAM_NAME
  FROM APPLSYS.FND_CONCURRENT_QUEUE_CONTENT fcqc,
       APPLSYS.FND_CONCURRENT_PROGRAMS fcp,
       APPS.FND_CONCURRENT_QUEUES_VL fcq
 WHERE     type_id = fcp.concurrent_program_id
       AND fcp.concurrent_program_name = '&PROGRAM_SHORT_NAME'
       AND fcq.concurrent_queue_id = fcqc.concurrent_queue_id;
 

Thanks

Srini

Change\Expire all application users password forcefully

We can forcefully expire password for all EBS user.This feature is available after RUP4.We need to apply  Patch 4676589 ATG RUP 4 enabling this feature.

Script:

The below script can be used to expire all passwords in the fnd_user table $FND_TOP/sql/AFCPEXPIRE.sql.

Execution Method

Connect to SQL*Plus
sqlplus  APPS/ APPSPASSWORD
@AFCPEXPIRE.sql

OR

Submit Concurrent Program

 CP SQL*Plus Expire FND_USER Passwords

Note:
This script sets the fnd_user.password_date to null for all users which causes all user passwords to expire.The user will need to create a new password upon the next login.

 

Thanks 

Srini

Script for Changing Oracle Application Users Password

We can use the below API for changing the EBS(fnd_user) user password.
 
 apps.fnd_user_pkg.changepassword
 
Sample Script:
 
DECLARE
   v_user_name      VARCHAR2 (100) := 'HSINGH';
   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status         BOOLEAN := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);
 
   COMMIT;
   DBMS_OUTPUT.put_line (
      'Password is changed successfully for the user=> ' || v_user_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while restting password for user and the Error Detail is '
         || SQLERRM);
END;
 
The script can also be modified to reset bulk ebs user passworda. The below is a sample script, You need to modify as per you need.
 
Refer below syntax
 
DECLARE
   v_status   BOOLEAN;
   CURSOR c_user
   IS
      SELECT user_name
        FROM fnd_user
       WHERE     NVL (end_date, SYSDATE + 1) > SYSDATE
             AND user_name NOT IN ('SYSADMIN',
                                   'GUEST',
                                   'XML_USER',
                                   'APPSMGR',
                                   'PORTAL30',
                                   'PORTAL30_SSO');
BEGIN
   FOR user_name_list IN c_user
   LOOP
      BEGIN
         v_status :=
            fnd_user_pkg.ChangePassword (
               username      => user_name_list.user_name,
               newpassword   => 'welcome123');
      --  dbms_output.put_line('password sucessfully changed for' || user_name_list.user_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Error encountered while restting password for users and the Error Detail is '
               || SQLERRM);
      END;
   END LOOP;
END;

Thanks 
Srini

Not able to connect to Apps user in Database due to library cache locks

If there are many applications integrated with EBS then this issue arises, when we change apps password but the integrated applications keep trying to connect with old password causes library cache locks in the Database.
This prevent apps user from connecting to database.This scenario also arises when EBS instance is refreshed.
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.

We can observe  'Library cache lock' or 'row cache lock' when concurrent users login with wrong password to the database.
'row cache lock' is seen in 10.2 and 11.1
'library cache lock' is seen in 11.2.

Solution

1. Check for bad or incorrect password or login attack by running following sql:

select username,
os_username,
userhost,
client_id,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp < sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);


2. Set the below event in the spfile or init.ora file and restart the database:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

REF:Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

 

Thanks

Srini

How to Enable\Disable large number of EBS users

To Disable/Enable bulk number of users in Oracle Applications, we can use the below API

apps.fnd_user_pkg.EnableUser =>To Enable Users
apps.fnd_user_pkg.DisableUser =>To Disable Users\End Date Users

Syntax:

The below synatx can be used for performing this activity.
We can modify the select statement in Cursor to customize according to need.

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.EnableUser(all_user.user_name);
commit;
end loop;
End


DECLARE
   CURSOR cur1
   IS
      SELECT user_name
        FROM fnd_user
       WHERE person_party_id IS NOT NULL;
BEGIN
   FOR all_user IN cur1
   LOOP
      apps.fnd_user_pkg.DisableUser (all_user.user_name);
      COMMIT;
   END LOOP;
END;

 

Thanks

Srini

Not able to view Responsibilities assigned to Users in Oracle Application

If the responsibilities assigned to user in Oracle Application is not appearing then we need new need to follow below steps.

1 .Login as System Administrator

2. Submit Concurrent Program ""Workflow Directory Services User/Role Validation'

Parameters to be passed
Batch Size - 10000(default value)
Username for the user having issue- SRINI
Check_Dangling - Yes (Default value No)
Add missing user/role assignments - Yes (Default Value No)
Update WHO columns in WF tables - No (Default Value No)

This request would check all users and assigned responsibilities and will sync up users with attached responsibilities .
Users should be able to view assigned responsibilty once the program is completed succesfully.

Thanks 
Srini

Adpatch Overview

Adpatch is a utility provide for applying patches in the Oracle Apps. Patching is perfomed for updating the files verison, bug fixing or while doing upgrades.

Patch can be applied 2 ways

1.Offline  (taking application services down) . We need to enable maintenance mode before applying patch in offline mode using adadmin utility.
2.Online  (with application services running). We need to use hotpatch option while applying patch in online mode

There are 3 modes in which adpatch runs.

Modes of ADPATCH

1) Pre-Install Mode
Pre-install mode is used to update AD utilities before an upgrade and to apply family consolidated upgrade packs.
AutoPatch Pre-AutoInstall mode allows you to apply patches when your installation is missing database information and/or filesystem information that AutoPatch requires to run in normal mode.
Examples of when you would run AutoPatch in Pre-AutoInstall mode (and cannot run it in normal mode) include:
    Prior to installing Oracle Applications for the first time
    Prior to upgrading Oracle Applications to the latest release.
    During an upgrade (to apply a bug fix that stopped your upgrade)
Applying patch in pre-install mode performs following tasks:
    Version checking
    File copy actions
    Relink FND and AD executables
    Save patch history information to file system
AutoPatch in pre-install mode will NOT:
    Run SQL of EXEC command
    Generate files
    Read product driver files
    Apply maintenance pack
To apply patch in pre-install mode, run  adpatch preinstall=y

2) Test Mode
AutoPatch provides a test mode in which it tells you everything it would have done in applying a patch, but doesn’t actually apply the patch.
To run AutoPatch in Test Mode, you must include ‘apply=no’ on the AutoPatch command line. For example:
$ adpatch apply=no
Instead of performing an action, AutoPatch indicates that it is not performing the action because “Apply=No”. In general, AutoPatch lists each file it would have copied, generated, relinked, or executed. This shows you exactly what actions it would have performed.
AutoPatch test mode works the same as normal mode, with the following exceptions:
    It does not copy any files from your patch directory into your installation area.
    It does not copy any files from your APPL_TOP to JAVA_TOP or OAH_TOP.
    It does not archive any object modules into your product libraries.
    It does not generate any forms or reports.
    It does not relink any executables.
    It does not run any ‘sql’ or ‘exec’ commands.
    It does not update the release version in the database.
    It does not update the patch history file.
AutoPatch asks you the same initial questions in test mode as in normal mode. It performs the following actions to determine what it would have done if run in normal mode:
    Reads and validates the patch driver file.
    Reads product file driver files.
    Extracts object modules from your product libraries (so it can perform version checking on the object modules it extracts).
    Performs version checking.
    Looks in the database to determine what ‘sql’ and ‘exec’ comands it would have run.
Its a good practice to run the patch in test mode and analyze the things before applying the patch in normal mode.

3) Non-Interactive Mode
Starting in Release 11.5, you can run AutoPatch non-interactively bycreating a defaults file

Before you can run AutoPatch non-interactively, you must first create an AutoPatch defaults file for your current environment.

Steps for creating AutoPatch defaults file for your current environment:
1. Specify defaultsfile=<New Defaults File Name> on the AutoPatch command line. The defaults file must be located under $APPL_TOP/admin/<SID>.
For example:
adpatch defaultsfile=$APPL_TOP/admin/testdb1/my_def.txt
2. Run AutoPatch up to the point where it asks you for the directory where your Oracle Applications patch has been unloaded. Then type ‘abort’ at this prompt.
3. Verify that your defaults file exists.
Once you have an AutoPatch defaults file for your current environment, you can run AutoPatch non-interactively.
Applying a single patch driver file non-interactively
Before applying any Oracle Applications patch, either interactively or non-interactively, you should read the README file (usually called readme.txt) supplied with the patch. You should also read the documentation supplied with the patch (if any).
It is possible to apply just a single patch driver file non-interactively using AutoPatch. Here is an example:
Assume the following:
    defaults file is $APPL_TOP/admin/testdb1/def.txt
    Applying copy driver for patch 987654, which is located in directory $APPL_TOP/patch/987654.
    Using three parallel workers
    AutoPatch log file name is cpy987654.log
The AutoPatch command line would be:
adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt \
logfile=cpy987654.log \
patchtop=$APPL_TOP/patch/987654 \
driver=u987654.drv \
workers=3 \
interactive=no
If we dont give any of the mode as mentioned above and apply the patch simply using adpatch command then its a normal mode of patch application.

Various Options used during adpatch.

Either these can be passed like <option_name>=<value> or adpatch will ask these value once its start executing.

Syntax
adpatch option_name= value
ex. adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt
adpatch workers=16

1) defaultsfile
Purpose: This option is used when we are running the patch in non interactive mode. In that case we create defaults file and provide that file as an option for running patch in non-interactive mode.
Default: none. No default file read or written.

2) logfile
Purpose: This is the name of adpatch log file which it will write during patch application.
Default: none. Adpatch prompts for this value.

3) workers
Purpose: Specifies the number of workers to run. This value depends on number of CPU and other factors.
Default: none. Adpatch prompts for this value.

4) patchtop
Purpose: Top-level directory for the current patch. This is the directory after unzipping the patch. This directory will a patch number.
Default: none. Adpatch prompts for this value.

5) driver
Purpose: Name of the patch driver file. This comes with the patch and is present in patch directory.
Default - none. Adpatch prompts for this value.

6) restart
Purpose: To restart an existing session. Only valid when interactive=no is also specified
Default: No

7) localworkers
Purpose: Used in Distributed AD to specify the number of workers to be run on the current machine. If you have multi node instance (example RAC and shared APPL_TOP), then you can utilize this paramter to run the patch parallely in multiple nodes. You can start few workers on node 1, few on node 2 and so on. The way this can be done is that, you can start adpatch on one node with localworker=<some value less then total workers>. Then run adctrl on other node in distributed mode and start some mode workers. This will speed up the process and utilized the resources effectively.
Default: Value specified for workers.

8) printdebug
Purpose: To display extra debugging information.
Default: No.

Some other parameters which can be helpful for application of speeding up the patch application.

Syntax:
adpatch options=<value>
 
1) checkfile
Purpose: To skip running exec, SQL, and exectier commands if they are recorded as already run. Indicates that Autopatch should run the command *only* if a certain file is newer than the version of it that was last run. The idea behind it is to reduce the duration of an Autopatch session by skipping actions that don’t really need to be performed. When used in the right manner, it can dramatically improve Autopatch performance, especially for big patches and/or long running actions.
Default: checkfile (use ‘nocheckfile’ to skip)

2) compiledb
Purpose: To compile invalid objects in the database after running actions in the database driver.
Default: compiledb (use ‘nocompiledb’ to skip)

3) compilejsp
Purpose: To compile out-of-date JSP files, if the patch has copy actions for at least one JSP file.
Default: compilejsp (use’nocompilejsp’ to skip)

4) copyportion
Purpose: To run commands found in a copy driver. This will copy the higher version files from patch to product top.
Default: copyportion (Use ‘nocopyportion’ to skip. Use it only when mentioned in readme of patch)

5) databaseportion
Purpose: To run commands found in a database driver. This portion includes applying the files (like sql, pls etc) to database.
Default: databaseportion (use ‘nodatabaseportion’ to skip. Use it only when mentioned in readme of patch)

6) generateportion
Purpose: To run commands found in a generate driver. This portion will generate new executable files from the copied code of patch. For example if will generate new forms files (fmx) from new .fmb files.
Default: generateportion (use ‘nogenerateporation’ to skip)

7) integrity
Purpose: To perform patch integrity checking. Tells adpatch whether to perform patch integrity checking, which verifies that the version of each file referenced in a copy action matches the version present in the patch.
Default: nointegrity (By default the integrity is not checked)

8) maintainmrc
Purpose: To maintain the MRC schema after running actions found in the database driver.
Default: maintainmrc (use ‘nomaintainmrc’ to skip)

9) autoconfig
Purpose: Tells adpatch to run Autoconfig after patch installation.
Default: autoconfig (use ‘noautoconfig’ to skip)

10) parallel
Purpose: To run actions that update the database or actions (like SQL) that generate files in parallel (like genform).
Default: parallel (use ‘noparallel’ to skip)

11) prereq
Purpose: Tells adpatch whether to perform prerequisite patch checking prior to running patch driver files that contain actions normally found in the copy driver.
Default: prereq (use ‘noprereq’ to skip)

12) validate
Purpose: To connect to all registered Oracle Applications schemas at the start of the patch. Adpatch validates the passwords for each schema.
Default: novalidate (use ‘validate’ to validate schema passwords)

Below Flags can be passed to adpatch

1) hidepw
Purpose: This argument is used to hide the passwords in log files
Default: nohidepw
adpatch flags=nohidepw
2) trace
Purpose: Tells the adpatch utility whether to log all database operations to a trace file
Default: notrace
adpatch flags=notrace
3) logging
Purpose: Tells the adpatch utility whether to create indexes using the logging or nologging mode.
Default: logging
adpatch flags=logging


Log File Location

Patch log file location:
$APPL_TOP/admin/PROD/log

Worker Log file location:
$APPL_TOP/admin/PROD/log

Thanks 
Srini

ASM disk and raw device mapping

If we need to know about the ASM disk and raw device mapping we can use the below script.

Script:

#!/bin/bash
for asmlibdisk in `ls /dev/oracleasm/disks/*`
do
echo "ASMLIB disk name: $asmlibdisk"
asmdisk=`kfed read $asmlibdisk | grep dskname | tr -s ' '| cut -f2 -d' '`
echo "ASM disk name: $asmdisk"
majorminor=`ls -l $asmlibdisk | tr -s ' ' | cut -f5,6 -d' '`
device=`ls -l /dev | tr -s ' ' | grep -w "$majorminor" | cut -f10 -d' '`
echo "Device path: /dev/$device"
done


If an ASMLIB disk was already deleted, then we cannot find it in /dev/oracleasm/disks. We can use below script to check for devices that are (or were) associated with ASM.
Script:

#!/bin/bash
for device in `ls /dev/sd*`
do
asmdisk=`kfed read $device | grep ORCL | tr -s ' ' | cut -f2 -d' ' | cut -c1-4`
if [ "$asmdisk" = "ORCL" ]
then
echo "Disk device $device may be an ASM disk"
fi
done

Thanks 
Srini

Finding Patch related to a Product in Oracle Apps

If we need to find patches applied related to a product then we can use the below script to find them.

SELECT *
  FROM apps.ad_bugs
 WHERE trackable_entity_abbr = '&product_name'

Example:

SELECT *
  FROM apps.ad_bugs
 WHERE trackable_entity_abbr = 'fnd'

Thanks 
Srini

Special charctaer when using backspace in Unix/Linux

There is problem when using backspace in unix/linux environment. Hitting backspace  is typing special character.

Example:

test^?^?^?^?

Solution:

type below command and backspace will work fine again,

stty erase ^?

 

 

Thanks

Srini

FND_CONCURRENT_PROCESSES Table process_status_code columns meaning

To get the details of the process_status_code column present in the FND_CONCURRENT_PROCESSES table we can use below script.

Query:
select lookup_code, meaning from  apps.fnd_lookups where lookup_type='CP_PROCESS_STATUS_CODE'

LOOKUP_CODE                    MEANING
------------------------------ ------------------------------
A                              Active
C                              Connecting
D                              Deactiviating
G                              Awaiting Discovery
K                              Terminated
M                              Migrating
P                              Suspended
R                              Running
S                              Deactivated
T                              Terminating
U                              Unreachable
Z                              Initializing

Thanks 
Srini

FND_CONCURRENT_QUEUE Control Code Meaning

In the FND_CONCURRENT_QUEUE table we have a column named control_code.

To get the details of the code present in the column we can use below script.

SQL> select lookup_code,meaning from apps.fnd_lookups where lookup_type = 'CP_CONTROL_CODE' order by lookup_code;

LOOKUP_CODE    MEANING
----------                    ------------------------------
A                               Activating
B                               Activated
D                               Deactivating
E                               Deactivated
H                               System Hold, Fix Manager before resetting counters
N                               Target node/queue unavailable
O                               Suspending concurrent manager
P                                Suspended
Q                               Resuming concurrent manager
R                               Restarting
T                               Terminating
U                               Updating environment information
V                               Verifying
X                               Terminated

Thanks 
Srini

Query to Check Workflow Mailer Status

Query:
 
SELECT component_status
  FROM apps.fnd_svc_components
 WHERE component_id =
          (SELECT component_id
             FROM apps.fnd_svc_components
            WHERE component_name = 'Workflow Notification Mailer');

Thanks 
Srini