How to get the patch numbers from merged patch:

How to get the patch numbers from merged patch:

In this post, I will explain you how to get patch numbers from ad_applied_patches when you previousply applied merged patch.
You can apply multiple patches in oracle applications using merged patch utility.  If you apply merged patch, it will be updated in the ad_applied_patches table with patch_name like ‘merged,’ but it won’t show what are the patches you have applied previously and it’s numbers.  To know what are the patches you have merged, see the procedure here:
First Step
1.  Query the ad_applied_patches with the following the details:
select APPLIED_PATCH_ID,PATCH_NAME,MERGE_DATE from ad_applied_patches where PATCH_NAME like ‘%merg%’;
2.  Now using patch_id from 1st step, get driver_id from AD_PATCH_DRIVERS table.
select PATCH_DRIVER_ID,APPLIED_PATCH_ID,DRIVER_FILE_NAME,MERGED_DRIVER_FLAG from AD_PATCH_DRIVERS where APPLIED_PATCH_ID like ’524′;
3.  Now, using driver_id from 2nd step, get bug_id from AD_COMPRISING_PATCHES table.

select COMPRISING_PATCH_ID,BUG_ID from  AD_COMPRISING_PATCHES where PATCH_DRIVER_ID like ’562′;
4.  Now using Bug_id from the 3rd step, get details of patches applied as part of a merged patch from ad_pugs table.
select BUG_NUMBER,CREATION_DATE,BUG_ID from ad_bugs where BUG_ID in (’118552′,’118532′,’118522′,’118510′);

How to know which modules affected by a patch???

How to know which modules affected by a patch???
Query this sql…..
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’;

How to find an AD patchset level in Oracle Applications

How to find an AD patchset level in Oracle Applications
SQL> select PATCH_LEVEL from fnd_product_installations where APPLICATION_ID=50;
PATCH_LEVEL
——————————
11i.AD.I

Implementing AutoConfig Customizations

How to customize AutoConfig in Oracle Apps 11i
Create the custom template directory
Create a directory named “custom” at the location where the AutoConfig template file resides.
For example, if you want to customize <FND_TOP>/admin/template/appsweb.cfg, execute the following command as the applmgr user:
mkdir $FND_TOP/admin/template/custom

Copy the AutoConfig template file
Copy the AutoConfig template file to the custom template file.
Execute the following command as the applmgr user:
cp -i <AutoConfig template file> <custom template file>
For example:
cp -i $FND_TOP/admin/template/appsweb.cfg \
$FND_TOP/admin/template/custom/appsweb.cfg
Edit the custom template file
Edit the custom template file with the editor of your choice, such as vi on UNIX or Wordpad on Windows.
Verify your customizations
Execute the adchkcfg utility. When this utility runs, it instantiates any custom template files in place of the corresponding AutoConfig template file. The adchkcfg utility generates a report with information about all files and profile options that will be changed during the next normal execution of AutoConfig. Verify that your customizations would be applied as expected in your next AutoConfig run.
Run AutoConfig
Run AutoConfig. When AutoConfig runs, it instantiates any custom template file in place of the corresponding AutoConfig template file.

Applying a Patch

 Applying a Patch

How to apply a Patch in Oracle Applications???
Hi, in this post, I will explain how to apply a patch to oracle applications and patching procedure.
Consider you have 3-node architecture (1-db; 1-concurrent node(db-ap), 1-AP node).
If you want to apply patch 6824767, follow these steps:
1.  Shutdown db-ap and ap nodes and keep db and db listener up.
2.  Take Invalid object List before patching.
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = ‘INVALID’
ORDER BY owner, object_type, object_name;
3.  Enable maintenance mode using adadmin.
4.  Apply patch on db-ap node.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt  options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8
5.  Apply patch on ap node.
adpatch defaultsfile=$APPL_TOP/admin/$TWO_TASK/defaults_file.txt options=noautoconfig,nomaintainmrc,nocompilejsp \
patchtop=/patch/6824767 driver=u6824767.drv logfile=eap1_6824767.log workers=8
6.  After applying the patch, again check for invalid objects and compare it with the before applying patch result.
If any new invalid found then run adadmin and compile apps schema.
7.  Disable maintenance mode using adadmin.
8.  Start db-ap and ap nodes.
9.  Do health check by logging into front-end and by submitting a simple concurrent request like “print environment variables”.

How to find Release version of Oracle Apps?

Login to sqlplus as apps/apps
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
————————————————–
12.0.0

Find Blocking Session details

Find Blocking Session details

How to find Oracle Database Blocking Session Details

In first step, find SID from v$session.

SQL> select process,sid, blocking_session from v$session where blocking_session is not null;
 PROCESS SID BLOCKING_SESSION
 ———— ———- —————-
 1234 365 366
 1234 366 365

In second step find the serial number for the Blocking Session to kill using SID
 SQL> select SERIAL# from v$session where SID=365;
 SERIAL#
 ———-
 130

In third step, kill the blocking session using SID and serial number

SQL> alter system kill session ’365,130′;
 System altered.