Upgrade R12.1.1 To R12.1.3

To Upgrade Oracle EBS R12.1.1 To R12.1.3 Follow the Below Steps :

From https://support.oracle.com you have to download the following patch :

  • p9239090_R12_LINUX_1of6.zip
  • p9239090_R12_LINUX_2of6.zip
  • p9239090_R12_LINUX_3of6.zip
  • p9239090_R12_LINUX_4of6.zip
  • p9239090_R12_LINUX_5of6.zip
  • p9239090_R12_LINUX_6of6.zip
  • p9239095_R12_GENERIC.zip
  • p9822544_R12.MSC.B_R12_GENERIC.zip(Optional)
  • p10349415_R12.AD.B_R12_GENERIC.zip (Optional)
  • p9239089_R12.AD.B_R12_LINUX.zip
Before Start Working You have to shutdown all application Services using  ( adstpall.sh ) and enable maintenance mode using adadmin 
Run the Patch in Order like the following, before apply Any Patch you need to check README.txt for any Pre-Install Step/Post-Install Step 
1- Start with patch Number 9239089 , before do that on Readme.txt you have to do the below :
-Open Two Session ( Terminal ) Run Apps Env On Terminal #1, Terminal #2 run database Env.
-On DB Terminal Create under $ORACLE_HOME/Appsutil/admin 
-Copy adgrants.sql (Unix)  from patch folder to Above folder.
-From $ORACLE_HOME/appsutil/admin Run adgrants.sql
-sqlplus /nolog
-SQL> @$ORACLE_HOME/appsutil/admin/adgrants.sql
2-Apply Patch 10349415
3-Apply Patch 9239090
4-Apply Patch 9239095
5-Apply patch 9822544 
6-Apply Patch  9817770 
7-Apply Patch 9966055
After you Applied all the above patch Do the below steps :
On Application Tier :
  • Run Autoconfig ( Which is Located $ADMIN_SCRIPT_HOME )
  • Run the admkappsutil.pl ( Located in $AD_TOP/bin/admkappustil.pl ) Using the below command 

perl /bin/admkappsutil.pl

This will generate new appsutil.zip located in  $INST_TOP/admin/out

On Database Tier :

Copy appsutil from Application tier to $ORACLE_HOME,Unzip in under $ORACLE_HOME Like the below

  • cd $ORACLE_HOME
  • unzip appsutil.zip
  • Run autoconfig for Database Tier located in ( $ORACLE_HOME/appsutil/admin) –> adauto.sh
Finally two more simple step, you need to run adpreclone on Database Tier & application Tier using the below command :
  • perl adpreclone.pl dbTier (located in $ORACLE_HOME/appsutil/admin)
  • perl adpreclone.pl appsTier (Located in $ADMIN_SCRIPT_HOME)
Disable Maintenance mode now. 

Sqlplus apps/***
SQL> select release_name from fnd_product_groups;
RELEASE_NAME
————————————————–
12.1.3

Start Application Services using ( adstrtall.sh )

Thank you
Osama mustafa

Oracle General Ledge/Posting Single Ledger program takes too long

After suffering about 6 hours of tuning EBS R12 on Customer Side , Since Posting not not working , but status on Concurrent Is Running and hang in some where .

Cause

This is Bug 9707317.

Posting Performance issue caused by Rule Hint being hard coded in the glsasi.lpc file.

The program uses the “/*+ rule */” hint, but it should be using “/*+ CHOOSE */” or none, which is shown in the tkprof’d trace file explain plan.

Also the wrong index is being used, it is using GL_ACCOUNT_HIERARCHIES_01 when it should be using the GL.GL_ACCOUNT_HIERARCHIES_U2 index.

Solution

To implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for the relevant patch.

For 12.0.x patch 9707317:R12.GL.A
For 12.1.x patch 9707317:R12.GL.B

Note: If a password is required to download the patch, please open a service request to get the proper password.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:

For 12.0 Patch 9707317:R12.GL.A

src/post glpmai.opc 120.21.12000000.2
src/summary glsasi.lpc 120.3.12000000.2

For 12.1 Patch 9707317:R12.GL.B

src/post glpmai.opc 120.24.12010000.1
src/summary glsasi.lpc 120.3.12010000.1
You can use the commands like the following:
strings -a $XX_TOP/filename |grep ‘$Header’

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

Enjoy

thank you
Osama mustafa

All About Oracle WorkFlow Mailer

In this Topic We will discuss the Workflow mailer In Oracle Apps ad how to check it Let Start :

Workflow: version

  $FND_TOP/sql/wfver.sql

check workflow status.

set linesize 120
set pagesize 50
column COMPONENT_NAME format a45
column STARTUP_MODE format a15
column COMPONENT_STATUS format a15

select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS,fcq.last_update_date
from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc
where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+)
order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME;

check if workflow is used by only one instance
col value format a20
select p.parameter_id,
p.parameter_name,
v.parameter_value value
from apps.fnd_svc_comp_param_vals_v v,
apps.fnd_svc_comp_params_b p,
apps.fnd_svc_components c
where c.component_type = ‘WF_MAILER’
and v.component_id = c.component_id
and v.parameter_id = p.parameter_id
and p.parameter_name in ( ‘INBOUND_SERVER’,’ACCOUNT’, ‘REPLYTO’)

order by p.parameter_name;

check for bad e-mail address

set linesize 170
col name format a40
col email_address format a80
select name, email_address from apps.wf_local_roles where email_address like ‘% %’;

select name, email_address from apps.wf_local_roles where email_address like ‘%%’;

 “If this retuning Row The there’s bad Email”

How to know mail sent to a user with details:

select name, display_name, notification_preference, email_address
 from wf_local_roles where name = ”;

To see failed, open notifications

SELECT message_type, COUNT(1)
FROM apps.wf_notifications
WHERE 1 = 1 AND mail_status = ‘FAILED’ AND status = ‘OPEN’
GROUP BY message_type;

To check if email address, notification preference, display_name

select DISPLAY_NAME, NOTIFICATION_PREFERENCE from wf_users where EMAIL_ADDRESS = ‘HR@domainname.com‘ and STATUS = ‘ACTIVE’ and DISPLAY_NAME = ‘xxxxx’;
select DISPLAY_NAME, NOTIFICATION_PREFERENCE, EMAIL_ADDRESS from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’;
select distinct(count(*)) from wf_users where NOTIFICATION_PREFERENCE=’MAILTEXT’ and STATUS = ‘ACTIVE’ and EMAIL_ADDRESS = ‘HR@Domainname.com‘;

Thank you
Osama mustafa