Showing posts with label EBSR12 Scripts. Show all posts
Showing posts with label EBSR12 Scripts. Show all posts

Thursday, July 15, 2021

How to find password of a User in Oracle Apps R12?

 How to find password of a User in Oracle Apps R12?

Decrypting User Password (ORACLE APPS R12)

In r12.2. Oracle seeded Procedure fnd_web_sec.get_guest_username_pwd which will help us to find out user password.

To achieve this you need to create a small package and run a query

--Package Specification


CREATE OR REPLACE PACKAGE get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2;
END get_pwd;
/


--Package Body



CREATE OR REPLACE PACKAGE BODY get_pwd
AS
   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
      RETURN VARCHAR2
   AS
      LANGUAGE JAVA
      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_pwd;
/


--Query to execute

SELECT usr.user_name, get_pwd.decrypt ((SELECT (SELECT get_pwd.decrypt (fnd_web_sec.get_guest_username_pwd, usertable.encrypted_foundation_password ) FROM DUAL) AS apps_password FROM fnd_user usertable WHERE usertable.user_name = (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd, 1, INSTR (fnd_web_sec.get_guest_username_pwd, '/' ) - 1 ) FROM DUAL)), usr.encrypted_user_password ) PASSWORD FROM fnd_user usr WHERE usr.user_name = ':USER_NAME';

Saturday, February 13, 2021

Query Applied Patches in Oracle EBS 12.2

 

Query Applied Patches in Oracle EBS 12.2


In releases prior to 12.2, we used to query ad_bugs or ad_applied_patches to find out if a patch is applied or not.
In 12.2, this is no longer the right way to do it due to the implications of online patching.
For example, if you apply a patch in an online patching cycle and aborted the cycle before it is complete, the records inserted in the above two tables will not be updated or removed.

The following query helps in identifying the patches applied:

SELECT adop_session_id, bug_number, session_type, 
       DECODE(status,'N','Applied on other nodes',
                     'R','Running',
                     'H','Failed (Hard)',
                     'F','Failed (Jobs Skipped)',
                     'S','Success (Jobs Skipped)',
                     'Y','Success',
                     'C','Clone Complete') status,
       applied_file_system_base, patch_file_system_base,
       node_name, start_date, end_date, 
       ROUND((end_date - start_date) * 24*60,2) exec_time,
       adpatch_options, autoconfig_status, driver_file_name
FROM ad_adop_session_patches
WHERE session_type IN ('ADPATCH','HOTPATCH','DOWNTIME','ONLINE')
ORDER BY adop_session_id, start_date, end_date;

Session type column was restricted to the following values:

  • ADPATCH: Patches applied using the traditional adpatch tool. It is used right after installing 12.2 and before online patching is enabled by applying patch 13543062.
  • HOTPATCH: Patches applied in hotpatch mode (aka. no online patching cycle is involved). It should be used only when instructed by Oracle.
  • DOWNTIME: Patches applied when all application services are down. Only used when instructed by Oracle, such as when applying 12.2.6 RUP.
  • ONLINE: Patches applied in a normal online patching cycle. This is the recommended method.

If you want to find out which patch fixed a specific bug, you can use the following query:

SELECT b.bug_number, asp.adop_session_id, asp.bug_number patch#,
       asp.session_type, asp.applied_file_system_base,
       asp.node_name, asp.start_date, asp.end_date
FROM ad_bugs b, ad_patch_run_bugs prb, ad_patch_runs pr,
     ad_patch_drivers pd, ad_adop_session_patches asp
WHERE b.bug_id = prb.bug_id
  AND prb.patch_run_id = pr.patch_run_id
  AND pr.patch_driver_id = pd.patch_driver_id
  AND pr.patch_run_id = asp.patchrun_id
  AND prb.applied_flag = 'Y'
  AND b.bug_number = '&bug_num';

A Script to Drop Obsolete Schemas in EBS 12.2

A Script to Drop Obsolete Schemas in EBS 12.2


After successful upgrade to 12.2.x, you should drop obsolete schemas. 

The following query generates a script to facilitate that:

select 'sqlplus apps/<apps_pwd> @$AD_TOP/patch/115/sql/addropschema.sql <system_pwd> applsys ' || username drop_schema from dba_users where username in ('ABM', 'AHM', 'AMF', 'AMW', 'BIL', 'BIV', 'BIX', 'BSC', 'CSS', 'CUE', 'CUF', 'CUI', 'CUN', 'CUP', 'CUS', 'DDD', 'EAA', 'EDWREP', 'EVM', 'FEM', 'FII', 'FPT', 'FTP', 'GCS', 'HCC', 'IBA', 'IBP', 'IGF', 'IGS', 'IGW', 'IMT', 'IPD', 'ISC', 'ITA', 'JTS', 'ME', 'MST', 'OKB', 'OKI', 'OKO', 'OKR', 'OZP', 'OZS', 'PFT', 'POA', 'PSB', 'RCM', 'RHX', 'RLA', 'VEH','XNC', 'XNI', 'XNM', 'XNS', 'ZFA', 'ZPB', 'ZSA');

Monday, February 19, 2018

CPU intensive long running Concurrent Program

long running Concurrent program



Find the process that are consuming high CPU on DB Node.


oracledb=>prstat

   PID USERNAME  SIZE   RSS STATE  PRI NICE      TIME  CPU PROCESS/NLWP
 26477 oracledb  2407M 2369M cpu2     0    0   2:59:40  13% oracle/39
  1958 oracledb  2393M 2355M cpu6     0    0   1:54:27  12% oracle/1
  2124 oracledb  2393M 2355M cpu2     0    0   1:53:35  12% oracle/1
  2202 oracledb  2393M 2355M cpu5     0    0   1:52:07  12% oracle/1



 Identify the PROCESS(Apps Node) from DB.


SQL>select s.sid, s.serial#, s.process, s.program, s.module 
    from v$session s, v$process p
    where s.paddr =p.addr and p.spid in ('26477','2124','1958','2202')
;


       SID    SERIAL# PROCESS      PROGRAM                                          MODULE
---------- ---------- ------------ ------------------------------------------------ --------------------
        93      35457 17991
       

On the APPLICATION Server, grep for the PROCESS:



apps=>ps -ef|grep 17991


apps 27982 25127  0 14:14:24 pts/2    0:00 grep 17991
apps 17991  9806  0 10:40:02 ?        0:02 ar60run P_CONC_REQUEST_ID=13318110 P_CORP='055' P_COMPANY='004' P_AS_OF_DATE='1


Get the Concurrent Request details



select /*+ CHOOSE*/
      'Node Name..............................: ' || q.node_name                          || chr(10) ||
      'Req id.................................: ' || Request_Id                           || chr(10) ||
      'Requestor..............................: ' || User_Name                            || chr(10) ||
      'Manager................................: ' || Q.User_Concurrent_Queue_Name         || chr(10) ||
      'Status code............................: ' || decode(status_code,'A', 'Waiting','B',
      'Resuming','D', 'Cancelled','E', 'Error', 'G', 'Warning', 'H',
      'On Hold', 'R', 'Normal','S', 'Suspended', 'T', 'Terminating',
                      'W', 'Paused', 'X','Terminated', status_code)                                       || chr(10) ||
      'Phase code.............................: '  || decode(phase_code, 'C', 'Completed',
      'I', 'Inactive', 'P', 'Pending', 'R', 'Running', phase_code)                        || chr(10) ||
      'Priority...............................: ' || Fcr.priority                         || chr(10) ||
      'Program................................: ' || Fcp.User_Concurrent_Program_Name     || chr(10) ||
      'Time so far ...........................: ' || trunc((sysdate-Fcr.actual_start_date)*24*60,2)||' min'|| chr(10) ||
      'Avg execution time in  30 days.........: ' ||  trunc(AVG_TIME,2)  ||' min'          || chr(10) ||
      'Max execution time in 30 days..........: ' ||  trunc(MAX_TIME,2)  ||' min'         || chr(10) ||
      'Fastest execution Time in 30 days......: ' ||trunc( MIN_TIME,2) || ' min'          || chr(10) ||
      'Number of executions in last 30 days...: ' ||occurance                             || chr(10) ||
      'ClientPID..............................: ' || Fcr.OS_PROCESS_ID                    || chr(10) ||
      'ServerPID..............................: ' || Fcr.ORACLE_PROCESS_ID     || chr(10) ||
      'Arguments passed to the program .......: ' ||Fcr.argument_text
       from apps.Fnd_Concurrent_Requests Fcr,
    apps.Fnd_Concurrent_Programs_vl Fcp,
    apps.Fnd_Oracle_Userid O,
    apps.Fnd_Concurrent_Processes P,
    apps.Fnd_Concurrent_Queues_vl Q,
    apps.Fnd_User,(select
    concurrent_program_id
    ,count(concurrent_program_id) occurance
    ,min(actual_completion_date-actual_start_date)*24*60 MIN_TIME
    ,max(actual_completion_date-actual_start_date)*24*60 MAX_TIME
    , avg(actual_completion_date-actual_start_date)*24*60 AVG_TIME
    from apps.fnd_concurrent_requests
    where status_code='C' and phase_code='C'
    and trunc(actual_start_date)>trunc(sysdate-30)
    group by concurrent_program_id
    having max(actual_completion_date-actual_start_date)*24*60 >5) CREQ
    where Controlling_Manager = Concurrent_Process_ID
    and ( P.Concurrent_Queue_ID=Q.Concurrent_Queue_ID AND P.Queue_Application_ID = Q.Application_ID )
    and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
    and( Fcr.Program_Application_Id=Fcp.Application_Id
    and  Fcr.Concurrent_Program_Id=Fcp.Concurrent_Program_Id)
    and Requested_By = User_Id
    and Phase_Code = 'R' and status_code in ('R','T')
    --adding joins with new
    and Fcr.Concurrent_Program_Id=CREQ.Concurrent_Program_Id(+)
    Order By   Q.User_Concurrent_Queue_Name,q.node_name, Actual_Start_Date,Request_Id;



Enter value for reqid: 13318110
old  40:     and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('&REQID')
new  40:     and O.Oracle_Id = Fcr.Oracle_Id and Request_Id in ('13318110')

Node Name..............................: server02
Req id.................................: 13318110
Requestor..............................: USER10
Manager................................: Standard Manager
Status code............................: Normal
Phase code.............................: Running
Priority...............................: 50
Program................................: Some Long Running Program
Time so far ...........................: 218.63 min
Avg execution time in  30 days.........:  min
Max execution time in 30 days..........:  min
Fastest execution Time in 30 days......:  min
Number of executions in last 30 days...:
ClientPID..............................:
ServerPID..............................:
Arguments passed to the program .......: , , , , 055, 004, , , , , 10-JUL-2014, , Merchant Number, , 71, Y, N