Pre-requisites for Oracle Apps R12 installation

Pre-requisites for Oracle Apps R12 installation

R12 installation is one of the crucial task for Apps DBA.Below are the steps described:Preparation for Oracle Apps R12 Installation:
User & Mount point/File system :

We have created two users for installing Oracle R12 Application (one for database tier & another for middle tier also called as application tier). We need 73 GB space (45GB DB & 28GB application tier)

1)Create groups & Users for Oracle R12 Application software installation:
su - root
#groupadd dba => group of users to be granted SYSDBA system privilege
#groupadd oinstall => group owner of Oracle files
#useradd -m -c -g oinstall -G dba oracle =>For Database tier
#useradd -m -c -g oinstall -G dba applmgr =>For Application tier
passwd oracle
passwd applmgr

2) Minimum Requirement for Oracle R12 Application installation:
CPU & RAM
CPU 1.6 GHz
RAM 1GB
Staging Area

We need to use adautostg.pl available in Start Here CD.Advantage of using staging area is that after start CD you don’t have to manually insert CD’s and Installer will automatically pick next CD from staging Area. We can pass on configuration setting on first 3-4 screens & once installer starts we can see complete installation. In case of any failure or subsequent installations (If you have to install more than one Instance its always better to install using Staging Area)

[root@test]# more /etc/hosts
127.0.0.1 localhost.localdomain localhost
Open your hosts file wiht editor(I will use vi editor in this article),put your info in this file and save it. In this article my localhost will be test, my localdomain will be helios.com
PS: If you are using standalone server or pc you can give use any domain name you want. But if your server or pc on any domain you must use localdomain.
[root@test]# vi /etc/hosts
127.0.0.1 localhost.localdomain localhost
10.100.x.x test.helios.com test

2. Set Kernel Parameters:
Add following line in end of the /etc/sysctl.conf file.
[root@test]# vi /etc/ sysctl.conf
#R12.1.1 kernel settings
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 256 32000 100 142
fs.file-max = 327679
net.ipv4.ip_local_port_range = 10000 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=4194304
#full qualified hostname(format is host.localdomain) << optional
kernel.hostname = test.helios .com << optional
#correct domain name !! << optional
kernel.domainname = helios.com << optional
After this steps you must set your new settings wiht below command:
/sbin/sysctl –p

3. Update /var/tmp/.oracle with 777 permissions
chmod 777 /var/tmp/.oracle
4. Edit /etc/security/limits.conf file:
Add the following lines to the /etc/security/limits.conf file:
[root@test]# vi /etc/ security/limits.conf
* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047

5. Edit /etc/resolv.conf file:
Add the following lines to the /etc/resolv.conf file:
options attempts:5
options timeout:15

6. Check /etc/sysconfig/network files format:
Verify that the /etc/sysconfig/network file is formatted as follows:
HOSTNAME=[node_name].[domain_name]

7. If the /etc/sysconfig/networking/profiles/default/network file exists, remove it.
If you changed any files in the previous steps, restart the system.
Be sure we have below rpms on our system:
The following i386 packages must be installed from the OEL 5 or RHEL 5 distribution media:
• compat-glibc-2.3.4-2.26
• gcc-4.1.2-14.el5
• gcc-c++-4.1.2-14.el5
• glibc-2.5-123
• glibc-common-2.5-123
• glibc-devel-2.5-12
• libgcc-4.1.2-14.el53
• libstdc++-devel-4.1.2-14.el5
• libstdc++-4.1.2-14.el53
• make-3.81-1.13
• gdbm-1.8.0-26.2.13
• libXp-1.0.0-8.1.el5
• libaio-0.3.106-3.23
• libgomp-4.1.2-14.el5
• sysstat-7.0.0-3.el5
• compat-libstdc++-296-2.96-138
• compat-libstdc++-33-3.2.3-61
Additionally, the following RPMs are required for an 11gR1 Database (which is bundled with the 12.1.1 release) on the database tier:
• elfutils-libelf-devel-0.125
• elfutils-libelf-devel-static-0.125
• libaio-devel-0.3.106
• unixODBC-2.2.11
• unixODBC-devel-2.2.11
• kernel-headers-2.6

Create folder for Oracle E-Business Suite R12.1.1 installation
Firstly check the mount point size of server.
As we can see i have free space, so i will create my stage folder and my installation folder on this mount point.
[root@test]# cd /data
[root@test data ]#mkdir TESTR12
Here we are doing Fresh Installation .Give 777 to installation folder.
[root@test]# chmod 777 /data/TESTR12
Create related folder for Application and Database tier(optional):
we want to put application tier installation folder together(also database tier installation folder) together we can create 2 subfolders under my main installation folder TESTR12.
[root@test]# cd /data/TESTR12
[root@test TESTR12]# mkdir apps
[root@test TESTR12]#mkdir db

We must give permisson to our user for can make installation of those folder
[root@testR12]# chown –R appltest:dba /data/TESTR12/apps
[root@testR12]# chown –R oratest:dba /data/TESTR12/db

C. Installation wizard steps:

D. After Installing of R12 Application:
Perform the following steps after installing or upgrading to Release 12.1.1 and before allowing users to access the system.
Relink Advanced Supply Chain Planning executables (for SLES 10 and OEL/RHEL 5.4 or higher only)
During the relink phase of the installation of EBS Release 12 (12.1.1) on OEL/RHEL 5.4 (failures will result while relinking the Advanced Supply Chain Planning (ASCP) executables. To fix this problem, users are required to replace the following line under the Linux section of the $AD_TOP/bin/adrelinknew.sh:
CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh'
with
CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh -Wl,--noinhibit-exec'
After making this change, users are then required to run the adadmin utility and relink application executables.

Oracle Workflow Related.

Purge WF_NOTIFICATION_OUT queue cd $FND_TOP/patch/115/sql
sqlplus apps/ @wfntfqup.sql APPS (appspwd) APPLSYS
e.g sqlplus apps/apps @wfntfqup.sql APPS apps APPLSYS

This purges the WF_NOTIFICATION_OUT queue and rebuilds it with data currently in the WF_NOTIFICATIONS table. This is what purges all notifications
waiting in the queue to be sent. It will then populate the queue with the current data in the WF_NOTIFICATIONS table.
Only notifications with mail_status = 'MAIL' and status = 'OPEN' will be re-enqueued in the WF_NOTIFICATION_OUT queue and sent by the mailer.

Workflow TEST Address Update
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10093

Enter a value for the parameter : WFdevUsers@abc.com

Setup Test address/override address for WF
Below is the script to update the override address from backend. You do not need the verification code to set the override address using the below script

update fnd_svc_comp_param_vals
set    parameter_value = '&EnterEmailID'
where  parameter_id =
( select parameter_id
 from   fnd_svc_comp_params_tl
 where  display_name = 'Test Address'
);


Workflow From Address Update :
sqlplus apps/ @$FND_TOP/sql/afsvcpup.sql

Enter Component Id: 10006

Enter the Comp Param Id to update : 10065

Enter a value for the parameter : Lenovo DEV Workflow Mailer"

set overriding address:
update FND_SVC_COMP_PARAM_VALS
set parameter_value = 'Configuration.Workflows@abc.com'
where PARAMETER_ID = 10057;


Scipt to see workflow configuration
SQL> select p.parameter_id,p.parameter_name,v.parameter_value value
from fnd_svc_comp_param_vals_v v,
fnd_svc_comp_params_b p,
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
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


SQL to monitor (check status) of Workflow Notification Mailer (Java)

SELECT COMPONENT_STATUS from APPS.FND_SVC_COMPONENTS where COMPONENT_ID=10006;

sqlplus -s system/${PASSWD}@${ORACLE_SID} << SQLEND >${script}/apps/wf_status.out
set linesize 90
col COMPONENT_NAME format a50
col COMPONENT_status format a20
spool ${script}/apps/wf_status.lst
select COMPONENT_ID,COMPONENT_NAME,COMPONENT_STATUS from apps.fnd_svc_components
where COMPONENT_ID not in (10000,10001,10020,10021,10022) and COMPONENT_STATUS <> 'RUNNING';

select mail_status, count(*) from apps.wf_notifications
where status = 'OPEN'
and mail_status in ('MAIL','ERROR')
group by mail_status;
spool off"



To see error message for a workflow notification
SQL> select ERROR_MESSAGE from wf_item_activity_statuses_v WHERE NOTIFICATION_ID = 7377659;

Here are steps/events for Oracle Workflow Notification Outbound Processing(eMail from Oracle Applications Workflow to Users)

1.When workflow Engine determines that a notification message must be sent, it raises an event in BES (Business Event System) oracle.apps.wf.notifications.send
Event is raised with Notification ID (NID) as event key
2. There is seeded subscription to this Event
3. Event is placed on WF_DEFERRED agent
4.Event is dequeued from WF_DEFERRED and subscription is processed
5. Subscription places event message to WF_NOTIFICATION_OUT agent.
6.Notification Mailer dequeues message from WF_NOTIFICATION_OUT agent and
6.1convert XML representation of notification into MIME encoded message (Multi-purpose Internet Mail Extensions) and
6.2 Send message by SMTP (Simple Mail Transfer Protocol) to intended user (If Test Address/Override Address is set then email is sent to Test Address

E-Mail Notification is sent if all below conditions are true
a) Notification status is OPEN or CANCELED and
b) Notification mail_status is MAIL or INVALID and
c) Recipient Role has a valid e-mail address and Notification Preference is in the format MAIL%
d) Workflow Deferred Agent Listener is running
e) Workflow Notification Mailer is running

To check a) & b) run below query
SELECT status, mail_status FROM wf_notifications WHERE notification_id = ‘&NID’;

mail_status >> SENT means mail has gone out of mailer to user

To check c) run below query
SELECT email_address, nvl(WF_PREF.get_pref(name, ‘MAILTYPE’),notification_preference)
FROM wf_roles
WHERE name = upper(’&recipient_role’);

To check d) & e) Use OAM (Oracle Application Manager)


Workflow version embedded in 11i
************************
Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0

You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.

To Configure Workflow Notification Mailer need below information

PARAMETER_NAME VALUE
------------------------------ ------------------------------
Inbound Server Name wfmailer.abc.com
Username wftst
Password oracle
Outbound Server Name wfmailer.abc.com
Reply-to Address wftst@abc.com


Taking a backup of the workflow configuration
cd $HOME/
sqlplus “/ as sysdba”
spool wf_mailer.log
set lines 130
set pages 200
col value format a30
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
('OUTBOUND_SERVER', 'INBOUND_SERVER',
'ACCOUNT', 'FROM', 'NODENAME', 'REPLYTO','DISCARD' ,'PROCESS','INBOX')
order by p.parameter_name;


To schdule Gather schema stats and purge workflow runtime data

Login into application as sysadmin and schedule below Requests.
Gather Schema Statistics (ALL , 20% , degree 6 ) to run every SUN and WED
Workflow Background Process to run every 10 mts and Apply the interval as :From the completion of the prior run
Purge Obsolete Workflow Runtime data - Every week
Workflow Control Queue Cleanup - Every 12 hours


To update WF notification status
IMPORTANT STEP ! Connect to SQL*PLUS as APPS user and do the following steps
update applsys.wf_notifications
set status ='CLOSED', mail_status ='SENT', end_date ='01-JAN-01'
where mail_status='MAIL';
update wf_notifications set status=’CLOSED’;
commit;"
Select distinct status from wf_notification" this should return only one value CLOSED.
or simple do
update wf_notifications set status = 'CLOSED', mail_status = 'SENT';
commit;


Update workflow a/c pwd from backend
set define off
update APPLSYS.FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE= '_@#0@##^90@#!4^86#!$^68$#9$4#@@$6!!!9#0@`$B9+}*0&9&@0&8#|'
where
PARAMETER_ID=
( select parameter_id from APPLSYS.FND_SVC_COMP_PARAMS_B where parameter_name = 'INBOUND_PASSWORD');

To check WF mail status
select count(*), mail_status
from wf_notifications
where begin_date > sysdate - 1
group by mail_status;

There are 141 messages with status `MAIL¿, this status should remain very short and then progress to status `SENT¿.
: OAM Login > Site Map > Workflow > Service Components.
These same detail are also given in the output from script $FND_TOP/sql/wfver.sql


To check the WF status from backend

check the status of Agent Listeners:

set pagesize 400
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
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;


Typical output from this

COMPONENT_NAME STARTUP_MODE COMPONENT_STATU
--------------------------------------------- --------------- ---------------
WF_JMS_IN Listener(M4U) AUTOMATIC RUNNING
Workflow Deferred Agent Listener AUTOMATIC RUNNING
Workflow Deferred Notification Agent Listener AUTOMATIC RUNNING
Workflow Error Agent Listener AUTOMATIC RUNNING
Workflow Inbound Notifications Agent Listener AUTOMATIC RUNNING
Workflow Java Deferred Agent Listener AUTOMATIC RUNNING
Workflow Java Error Agent Listener AUTOMATIC RUNNING
Workflow Notification Mailer AUTOMATIC RUNNING
ECX Inbound Agent Listener MANUAL STOPPED
ECX Transaction Agent Listener MANUAL STOPPED
Web Services IN Agent MANUAL STOPPED
Web Services OUT Agent MANUAL STOPPED
Workflow Inbound JMS Agent Listener MANUAL STOPPED

13 rows selected.


Steps to start/stop notification mailer

1.a Check workflow mailer service current status
sqlplus apps/

select running_processes
from fnd_concurrent_queues
where concurrent_queue_name = 'WFMLRSVC';

Number of running processes should be greater than 0

1.b Find current mailer status
sqlplus apps/

select component_status
from fnd_svc_components
where component_id =
(select component_id
from fnd_svc_components
where component_name = 'Workflow Notification Mailer');

Possible values:
RUNNING
STARTING
STOPPED_ERROR
DEACTIVATED_USER
DEACTIVATED_SYSTEM

2. Stop notification mailer
sqlplus apps/

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Stop Mailer
--------------
fnd_svc_component.stop_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/

3. Start notification mailer
sqlplus apps/

declare
p_retcode number;
p_errbuf varchar2(100);
m_mailerid fnd_svc_components.component_id%TYPE;
begin
-- Find mailer Id
-----------------
select component_id
into m_mailerid
from fnd_svc_components
where component_name = 'Workflow Notification Mailer';
--------------
-- Start Mailer
--------------
fnd_svc_component.start_component(m_mailerid, p_retcode, p_errbuf);
commit;
end;
/


TO configure workflow
Workflow Mailer Notification settings.
Log on to OAM
Click on Workflow Administrator -> Business Event Local System
Change VIS.ABC.COM to PROD.ABC.COM

Click on Workflow Manager ->Notification Mailer -> Workflow Notification Mailer -> Edit
Edit outbound Email Account (SMTP) -> Server Name =Concurrent Tier
IMAP Server -> Server Name = xxx.xxx.x.xx
Username/password: wfvis/xxxxx :
Reply-To Address: wfvis@abc.com

Click on Advance -> Next->Next
Under General Tab
Mailer Node =WFVIS
Under IMAP: Enter server IP address, username and password of wfvis
Under Outbound Email Account
Outbound Server Name= concurrent tier
Click Next –>Next….Save and Apply

Workflow Version with Apps
Connect to Database as apps user
SQL> select TEXT Version from WF_RESOURCES where NAME = ‘WF_VERSION’;
Output like 2.6.0 means workflow version 2.6.0


Workflow logfile location
$APPLCSF/$APPLLOG with filename like FNDCPGSC[pid].txt

After executing autoconfig make sure Workflow System Administrator is NOT set to SYSADMIN .
Please set this to ""Workflow Administrator Web (New)""
Following script will take care of this .

sqlplus apps/""pwd""
SQL> update wf_resources set text = 'FND_RESP|FND|FNDWF_ADMIN_WEB_NEW|STANDARD' where name = 'WF_ADMIN_ROLE';

1 row updated.

SQL> commit;

Commit complete.

or xml changes can be done link this.
$ grep -i s_wf_admin_role PROD_mdsxaorit.xml
username oa_var=""s_wf_admin_role"">FND_RESP|SYSADMIN|SYSTEM_ADMINISTRATOR|STANDARD

To set the Workflow Administrator from Sys Admin to Workflow Administrator
Navigate to Responsibility -- Workflow Administarator
then Administrator Workflow -- Administration

On top we will find Workflow Configuration :

Workflow System Administrator :: This value we need to change to Workflow Administrator

Or we can update from backend
Update wf_resources set text='FND_RESP|FND|FNDWF_ADMIN|STANDARD' where name = 'WF_ADMIN_ROLE';

Or can be set in xml,
grep -i s_wf_admin_role TRAIN2_vcosxaor09.xml username oa_var=""s_wf_admin_role"">FND_RESP|FND|FNDWF_ADMIN|STANDARD


modify this profile option to have the value of an actual App Server
WF: Workflow Mailer Framework Web Agent : http://hostname.domainname:8000

Make sure following parameters are set for Workflow :
As system administrator –
Oracle Applications Manager
Workflow Manager

Inbound Server Name wfmailer.domain.com
Outbound Server Name wfmailer.domain.com
From The lenovo PROD Workflow Mailer
Reply-to Address wfvis@domain.com


Workflow smtp port 25 and imap port 143
[root@hostname]# telnet wfmailer 143

 

Thanks

Srini