Anatomy of adpatch for ebs applicaitions

Adpatch Explained

Brief
As an application DBA we apply patches regularly on EBS environments and these patches might include one-off fixes,mini packs, family packs,etc., These patches might fix the exiting issue but it might be again vulnerable for new bug. So evry patch should be thoroughly tested and then should be moved to PROD.

But many of the DBA's are not aware of the structure of adpatch, its metadata and minimum requirements for the patch to be applied.

Below are the minimum files available in patch zip file and these are mandatory for applying any adpatch.

1) b<bug_number>.ldt - This file contains the bug metadata information and is used to  upload the bug metadata to the database after applying the patch and is based on afumsaru.lct
2) f<bug_number>.ldt - This file is used to upload the details of file versions changed or new files plugged into ebs as part of patching
3) marker1.txt  - Contains the name of patch zip file
4) patch_metadata.xml - This file contains information of patch contents and action steps to be performed. This is the master copy that will be used by adpatch to apply the patch
5) README.txt/README.html - These contain the instructions of applying adpatch to application
6) u<bug_number>.drv - This is the unified driver which contains various instructions of copy files,compiling objects,forms,etc.,
7) j<bug_number>_per.zip - This zip file may be included in Java patches. A Zipped Resource Unit or ZRU. jcopy command is used to unified driver to apply this j driver to ebs
8) ad_apply_patch.xml  - This is invoked from patch_metadata.xml and contains details of driver to be applied
9) product top files - contains tops with various fixes like updated fmb,packages.ldt's,etc.,

Detailed explanation on unified driver and its contents will be posted soon

Inputs are most appreciated.. :)

Concurrent Request completed with "sqlldr : Permission denied" error

Issue

Arguments
------------
/u01/files_in/BANK_SORT.pdv
------------
sqlldr: Permission denied
usdsop: exec failed during spawnusdsop cannot write to pipe

Cause: usdsop attempted to write data to another process using a pipe, but an error occurred during the write.

Action: Check the other process for abrupt termination. Check that you have sufficient resource: Broken pipe
usdsop cannot write to pipe

Cause: usdsop attempted to write data to another process using a pipe, but an error occurred during the write.

Action: Check the other process for abrupt termination. Check that you have sufficient resource
Concurrent Manager encountered an error while running SQL*Loader for your concurrent request 1336114.

Review your concurrent request log file for more detailed information.


+---------------------------------------------------------------------------+

No completion options were requested.

Solution:

1. Check the permission for sqlldr file in (ORACLE_HOME) ie) 10.1.2 ORACLE_HOME/bin directory.
2. Give executable permission to sqlldr file in $ORACLE_HOME/bin
chmod 755 $ORACLE_HOME/bin/sqlldr

2. Check the ORACLE_HOME path is set correctly for apps.

Useful Application & Database Scripts

Useful Application & Database Scripts

Script Name: Elapsed Time History of Concurrent Requests in a month

Script Details

SELECT pt.user_concurrent_program_name
       user_conc_program_name,
       r.priority,
       Count(*),
       Round(Avg(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       AVGG,
       Round(Max(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       MAXI,
       Round(Min(actual_completion_date - actual_start_date) * 1440 * 60, 2)
       MINI,
       p.concurrent_program_name
       concurrent_program_name
FROM   apps.fnd_concurrent_programs p,
       apps.fnd_concurrent_programs_tl pt,
       apps.fnd_concurrent_requests r
WHERE  r.concurrent_program_id = p.concurrent_program_id
       AND r.program_application_id = p.application_id
       AND r.concurrent_program_id = pt.concurrent_program_id
       AND r.program_application_id = pt.application_id
       AND r.status_code = 'C'
       AND r.actual_start_date IS NOT NULL
       AND To_char(actual_completion_date, 'MON') = '&1'
GROUP  BY pt.user_concurrent_program_name,
          r.priority,
          p.concurrent_program_name
ORDER  BY Count(*) DESC

Script Name: Current Logged on User count in EBS

Script Details

SELECT DISTINCT icx.session_id,

                  icx.user_id,

                  fu.user_name,

                  fu.description

    FROM icx_sessions icx, fnd_user fu

   WHERE     disabled_flag != 'Y'

         AND icx.pseudo_flag = 'N'

         AND (last_connect + 

              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),

                      NULL, limit_time,

                      0   , limit_time,

                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE

         AND icx.counter < limit_connects

         AND icx.user_id = fu.user_id;

Will add more..