Apps Password Expired

Issue:- Apps Passowrd expired.
Overview:-
We have defined 2 dba profiles, one for admin user(apps & system) other one for application users(all other users). Below values are for admin profile:-
PROFILE RESOURCE_NAME LIMIT
ADMIN PASSWORD_REUSE_TIME 180
ADMIN PASSWORD_REUSE_MAX UNLIMITED
ADMIN PASSWORD_VERIFY_FUNCTION NULL
ADMIN PASSWORD_LOCK_TIME 7
ADMIN PASSWORD_GRACE_TIME 14

DBA missed the to check the apps password expiry date and user reported error to us. Forms were not opening as expected.

Since this was apps user, we were bit concerned:-

Troubleshooting:-
In EBUS? apps password got expired, while trying to login we were getting error as apps password has been expired and on prompt it was asking to be changed:-
0) Stop all services prior to start troubleshooting.
1) since apps is not normal user we exited the prompt without changing the password
2) We checked if applysys has been also expired but it has not so no problem their. Since Database policies impact only RDBMS part (DBA_*) and does not impact FND_* realm, we thought of changing password value in dba_user and see if we can move forward.
3) Take backup of dba_users, fnd_user, FND_ORACLE_USERID.
4) Check profile connected to apps user:-
EBUS> select USERNAME,PROFILE from dba_users where username like 'APPS';

USERNAME PROFILE
------------ ------------------------------
APPS ADMIN
5) Check profile values:-
PROFILE RESOURCE_NAME LIMIT
---------- -------------------------------- ----------------------------------------
ADMIN PASSWORD_REUSE_TIME 180
ADMIN PASSWORD_REUSE_MAX UNLIMITED
ADMIN PASSWORD_VERIFY_FUNCTION NULL
ADMIN PASSWORD_LOCK_TIME 7
ADMIN PASSWORD_GRACE_TIME 14
ADMIN PASSWORD_LIFE_TIME 90

As we wanted to set same password for apps, we need to modify password_* profile values, we assigned DEFAULT profile to apps user thus password_* profile value set to unlimited.

6) Check the current value of password from dba_users table for apps user:-
select username,password from dba_users where username like 'APPS';

USERNAME PASSWORD
------------ ------------------------------
APPS 6B6F7C13B5D0

Note:- we are assuming here that password value is same as it was prior to expire state i.e. nobody has changed the password since then, neither on prompt, nor issuing alter user, nor FNDCPASS.

Now issue:-
alter user apps identified by values 'Passowrd from above step';
7) Try to login using apps user.
8) Start application services.

Mailer Fails to Send Email Notifications after cloning.

After cloning, mails were not floating through workflow mailer although all agent listener and notification mailer was up and running.

Issue:-
After some time we checked latest mailer log FNDCPGSCXXXXX.txt at $APPLCSF/$APPLLOG.
Below error was their in logs:-
'Problem obtaining the HTML content oracle.apps.fnd.wf.common.HTTPClientException: Unable to invoke method HTTPClient.HTTPConnection.Get caused by: java.net.ConnectException: Connection refused'

Investigation:-
what i got to know after bit of investigation is at the time we brought down source instance for cold back up, some of the workflows were in queue and they might be using source instance webserver address. After clone Since the mailer still had some events with the old url in the queue, the mailer fails with connection refused error and goes down after 10 attempts.

Solution:-
Solution is to purge the wf_notification_out outbound message queue and repopulates from the WF_NOTIFICATION table.

PFB complete solution:-

1. Stop the Workflow mailer.

2. Purge the wf_notification_out queue and recreate it out of the wf_notifications table by executing:
cd $FND_TOP/patch/115/sql
sqlplus APPS/ @wfntfqup.sql APPS APPLSYS

3. Start the mailer

4. Retest the issue by submitting the same workflow again.

Encounter with Oracle ADI

Oracle ADI and its integration with E-Business Suite


Oracle ADI is a spreadsheet-based extension to Oracle Applications that offers full cycle accounting and asset management within the comfort and familiarity of a spreadsheet. Oracle ADI combines a spreadsheet’s ease of use with the power of Oracle Applications, to provide true desktop integration during every phase of your accounting cycle. You can create budgets, record transactions, add assets, reconcile inventory, and run financial statements and inventory reports all without leaving your spreadsheet.

You can run Oracle ADI as a stand-alone application, without installing the full client version of Oracle General Ledger (GL) or Oracle Assets on your PC. This gives you the ability to confirm and reconcile inventory, revise budgets, create journal entries, and define financial and asset reports from any location, without being connected to your server. You only need to connect when you want to transfer data to or from Oracle Applications.

DBA perspective: - From DBA point of view, fundamentals are quite simple, For publishing report, ADI will take value of profile option "RRA: Service Prefix" from e-biz and connect it with node name from fnd_concurrent_request table for that particular request. Say, I have my CP tier on EBUS_CM (hostname) and RRA:prefix profile option set to FNDFS_ then ADI will search for FNDFS_EBUS_CM in its tnsnames.ora( i.e c:/orant/network/admin or c:/orant/net80/admin) and produce the output file.
Your tnsnames.ora and sqlnet.ora on client and server side should exactly match for instance and FNDFS_hostname entry.

Install ADI: - ADI can be installed on users machine using patch from metalink. Oracle ADI 7.2 base release patch is 3966101. Current version of ADI is 7.2.11.07.29(patch 7286213). Installation is straight forward. Just go through the patch readme and that’s all.

Integrate ADI with Oracle Application E-Business Suite: -
Changes to be done as part of Oracle ADI.
Edit tnsnames.ora file at c:/orant/network/admin and put tns entries of your ebusiness suite server.
Basically two entries are required(per instance), one for resolving instance name while connecting to E-Business Suite(initial sign on) and other for publishing report, viewing out and log file. This entry should be like FNDFS_hostname.

Sample tnsnames.ora:
#for initial sign-on
EBUS = (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EBUS_CM)(PORT=1531))
(CONNECT_DATA=(SID=EBUS))
)
#for publishing report, viewing out and log files.
FNDFS_EBUS_CM= (DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=EBUS_CM)(PORT=1636))
(CONNECT_DATA=(SID=FNDFS))
)

Edit sqlnet.ora file at c:/orant/network/admin. Sample file
AUTOMATIC_IPC = OFF
TRACE_LEVEL_CLIENT = OFF
names.directory_path = (TNSNAMES)
names.default_domain = oracle.com
name.default_zone = oracle.com
names.default_domain=oracle.com

Copy tnsnames.ora and sqlnet.ora from c:/orant/network/admin to c:/orant/net80/admin

Changes to be done as part of E-BIZ: - Change "RRA: Service Prefix" profile option at site level to FNDFS_

Using ADI: - You will get Request centre and ADI icons on your machine once it has been installed.
(i) Click on Application Desktop integrator button and screen will be presented to you.
(ii) Click on "hand" icon
If this is fresh installation then you need to define database connect string in ADI
Click on Cylinder button and database details screen will be presented to you:
NAME: Any name as per your convenience
Description: As per convenience
GWYUID: applsyspub/pub in most of the cases.
FNDNAM: apps
Connect String: E-BIZ database connect string
NCA Connection: - Not Required
Server ID: - Not required in most of the case but if required take this value from dbc file located on e-biz server at $FND_TOP/secure

(iii) Click on tick button and signon details screen will be presented to you.
Username: E-BIZ application user name
Passowrd: Password of username used for connecting ADI
Database: Select your E-BIZ instance name from drop down
After providing details you will screen saying Gathering set of books information blah blah...
If above screen continue to appear for more then couple of minutes then you are hitting bug and need to upgrade ADI. Check metalink for more.
Click on "Submit Report" green button i.e. first button from upper-left
click at Report area
select report
click on "tick" button at upper-right corner
Click ok

on pending tab you can view your request running normal
for publishing report click yes

Published report willl be presented to you.



Useful Metalink Note ID’s for troubleshooting:
Note.117012.1 - Troubleshooting the "Error Occurred While Attempting to Establish an Applications File Server Connection"
Note.469724.1 Ext/Mod Cannot View Published Report In Request Center Gen
Note.311349.1 Ext/Pub Not able to to publish reports when connecting to other Application Instances on the same Application Server
Note.201492.1 Ext/Pub ADI RRA Prefix When Connecting to Server with Multiple Apps Instances

ORA-20002: 3207: User 'SYSADMIN' does not have access to notification 1621941. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 82 ORA-06512: at line 1

Error:- ORA-20002: 3207: User 'SYSADMIN' does not have access to notification 1621941. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 82 ORA-06512: at line 1

Cause:- SYSADMIN user does not have Workflow administrator role.

Resolution:- Following options can be used to get the desired result

Option A:
1. Login to E-Business as the user who can modify the Workflow System Administrator and navigate:
Workflow Administrator Web Applications > Administration
Then select either the SYSADMIN user or the Workflow Administrator Web Applications responsibility as the Workflow System Administrator.
Note: Below sql can be used to get it done from backend
Run below query to check the resp/role name that has been assigned with WF_ADMIN_ROLE.

select text from wf_resources where name='WF_ADMIN_ROLE';

If output of above query is not ‘SYSADMIN’ or ‘Workflow Administrator Web Applications’ responsibility then you got the issue
Update the table as per your requirement.
update wf_resources set text='*' where name='WF_ADMIN_ROLE';
commit:

2. Send another type of the failing notification and confirm that the issue has been fixed.


Option B:

1. Login to E-Business as the user who can modify the Workflow System Administrator and navigate:
Workflow Administrator Web Applications > Administration
Then select your user or responsibility as the Workflow System Administrator.
2. Change the Workflow Mailer Framework User parameter from FND_USER.USER_ID = 0 to the USER_ID of the Workflow System Administrator user or to a user that is assigned to the responsibility you assigned as Workflow System Administrator.

a. Obtain the FND_USER.USER_ID:
select user_id, user_name from fnd_user where user_name=upper('');
b. Run $FND_TOP/sql/afsvcpup.sql to change the Framework User parameter under the Workflow Notification Mailer Component ID to the selected user_id.

c. Shutdown and restart the Workflow Mailer Service.

3. Send another type of the failing notification and confirm that the issue has been fixed.

Reference: Metalink Note ID - 344936.1

Could not initialize the Service Manager

Issue: After cloning, all the concurrent managers are not getting started, ICM is coming up but ICM is not able to bring up other defined concurrent managers.
Error: manger log has below error:
Could not initialize the Service Manager FNDSM_EBUS_CM. Verify that EBUS_CM has been registered for concurrent processing.
Check that your system has enough resources to start a concurrent manager process. Contact your system administrator: 27-JAN-2009 18:43:08

Step to Reproduce Issue: start CM, check FNDLIBR processes on server ( related to concerned instance), it will show you only one FNDLIBR process ( i.e. ICM)
Ps –ef grep FNDLIBR grep EBUS_CM
Resolution:
Service managers are not coming up on each node and due to this other concurrent managers (including Standard Managers)are not getting started.
As error suggest FNDSM_EBUS_CM is not registered for concurrent processing.
First of all check if each node exists in fnd_nodes table or not. If not then run autoconfig on each node.
Use below query to confirm:
SQL> select concurrent_queue_name, node_name from fnd_concurrent_queues where
manager_type=6;
2
CONCURRENT_QUEUE_NAME NODE_NAME
------------------------------ ------------------------------
FNDSM_EBUS_WEB EBUS_WEB
FNDSM_EBUS_CM EBUS_CM
If any of server’s Service manager is not there, then you got the issue.
Service Manager can be created for nodes using below method:
From the Application tier:SQL> sqlplus apps/apps1. Log in as applmgr2. cd to $FND_TOP/patch/115/sql3. Run the script: afdcm037.sql4. This script make libraries for FNDSM and create Managers for Preexisting Nodes.
Again Run
select concurrent_queue_name, node_name from fnd_concurrent_queues where
manager_type=6;
Now start your CM and your worries are gone.


Also check this:
Run the following select on the target node only to check trigger status :

SELECT trigger_name , statusFROM user_triggersWHERE table_name = 'FND_NODES' ;

If status of the triggers show as DISABLED, then enable these following triggers as follows :
connect apps/
alter trigger UPNAME enable;
alter trigger FNDSM enable;

Also check enabled_flag in fnd_concurrent_queues...you should be having enabled flag set to Y for getting managers up on a particular node. if this is not set to Y then you can simply update the table and bounce CM and check.

Apache Bounce Concurrent Request.

Bounce Apache Server – A Concurrent Request

Background: Need of Apache bounce concurrent request has come out of following requirements:

(i) Development team requires access on server for bouncing apache. DBA team cannot share master login credentials with development team but developers require bouncing Apache very frequently during testing phase. This has created the deadlock.
(ii) Bouncing Apache using concurrent request is faster and easier approach.
Before starting let me give you a brief idea on our architecture:-
Tier 1 :- Web server :- EBUS_WEB
Tier 2 :- CP, Admin and DB server :- EBUS_CM
Brainstorming: After lot of brainstorming we came to conclusion that we can create a master shell script on Tier 2 (CM server) that will be called by concurrent request. Master shell script will internally call apache_stop, apache_start & clear_cache script placed on Tier1 (Web server). SSH is used for connection from one server Tier 2 to Tier 1(i.e. web server).

How we did it:
Whole thing is divided into 2 parts:

Shell scripting:

As we have concurrent processing and Web server on different tier so we need a medium to communicate between them. After discussion with UNIX team we got SSH as medium to get same.

Following Scripts are created:
APACHE Bounce Master Script: This script is created on Concurrent Processing server and will be in direct contact with concurrent request. This script will internally call apache_start, apache_stop and clear_cache script placed on web server.
Content of APACHE shell script:
#!/bin/ksh
#########################################################################
#
# Program Name: Apace Bounce Script
#
# Created By: Deepak Jha
#
# Creation Date: 22 Jul 2009
#
# Version: 1.0
#
#########################################################################
#
# Description
# -----------
# Generic Script to bounce Apache server using Concurrent request.
#
# Date User Description
# ----------------------------------------------------------------------
# 22 Jul 2009 Deepak Jha Initial Creation
#
#
#########################################################################
echo $PATH
#Apache stop script
#All script referenced in this script is placed at /tmp location. You #can place it as per your convenience.
echo "Script start time: `date`"
printf "Calling Apache stop script on Webserver.\n"
ssh EBUS_WEB /tmp/apache_stop.sh
printf "Apache has been stopped, putting the request to sleep for 5 sec, so that all hanged apache processes get stopped.\n"
echo "Sleep In time: `date`"
sleep 5
echo "Sleep Out time: `date`"
printf "checking if clear cache is required.\n"
if [ "$5" = "y" ]
then
printf "Clear cache required.\n"
printf "clearing cache.......\n"
ssh EBUS_WEB /tmp/apache_clear_cache.sh
else
printf "Clear cache not required.\n"
fi
#Apache start script
ssh EBUS_WEB /tmp/apache_start.sh
sleep 30
echo "Script End time: `date`"

Apache Stop Script: This script should be placed on Web server (EBUS_WEB) and will be used to stop apache. This script will first stop apache by calling adapcctl.sh with stop parameter. This script is also capable of killing any hanged apache processes (that have not been killed even after apache stop).
Content of apache_stop.sh
#START OF SCRIPT#
$COMMON_TOP/admin/scripts/$CONTEXT_NAME/adapcctl.sh stop
#Identify if Apache processes are still alive
#c=`date '+%d%m%y.%H.%M.%S'`
echo " Checking if Apache Processes are still alive"
x=`ps -ef grep Apachegrep -v grepawk '{print $2}' wc -l`
echo $x
if [ $x -eq 0 ]
then
echo "No alive apache process and apache has been stopped cleanly."
else
echo "Following processes are still alive:"
ps -ef grep Apachegrep -v grepawk '{print $2}'
#Kill identified process
echo "Killing identified process"
kill -9 `ps -ef grep Apachegrep -v grepawk '{print $2}'`
printf "Processes killed at O.S level.\n"
printf "Apache Stopped Successfully.\n"
fi
#END OF SCRIPT#

Apache Clear Cache Script:- This file will come into action if user wants to bounce apache with clear cache option. It should also be placed at EBUS_WEB.
Content of apache_clear_cache.sh:
#START OF FILE#
c=`date '+%d%m%y.%H.%M.%S'`
printf "clearing cache in progress....\n"
mv $COMMON_TOP/_pages $COMMON_TOP/_pages.$c
printf "Cache Cleared.\n"
#END OF FILE#
Apache Start Script: This script will call adapcctl.sh with start parameter. It should also be placed at EBUS_WEB.
Content of apache_start.sh:
#START OF SCRIPT#
. $COMMON_TOP/admin/scripts/$CONTEXT_NAME/adapcctl.sh start
if [ ${?} -eq 0 ]
then
printf "Apache Started Successfully.\n"
else
printf "Apache could not be started. Please check Apache error log for more information.\n"
exit 1
fi
#END OF SCRIPT#

Concurrent Program Definition and other steps to be done through Oracle Application front end.

Concurrent Program Definition:-

Define Concurrent Program Executable

Concurrent --> Program --> Executable
Executable --> Any name but remember it because it should be provided at time to concurrent program creation.
Short Name --> Give it as executable to avoid confusion.
Application --> Application Object Library
Execution Method --> Host
Execution File Name --> Name of shell script placed on EBUS_CM at $FND_TOP/bin

Define Concurrent Program:-
Concurrent --> Program --> Define
Program --> Concurrent Program Name. It can be any name of your choice.
Short Name --> Should be same as provided at time of executable creation.
Application --> Application Object Library
Name --> Should be same as provided at time of executable creation.
Method --> host

Place master APACHE shell script at EBUS_CM (CM server) $FND_TOP/bin location. Create simlink between master shell script and fndcpesr
ln –s fndcpesr APACHE
mv APACHE APACHE.prog
Note Apache is main shell script to be called by concurrent request.

Define Custom Value set: - This needs to be created so that we can pass parameter to concurrent request for bouncing it with "Clear Cache" option.
Application --> Validations --> Set
Value Set Name --> Any name of your choice. You need to remember this name.
Maximum Size --> 50
Other choices can be default.
Define Values for Value Set
Application --> Validation à Values
In pop up window provide Value set name that has been created in previous step:
Click Find
In Value Field provide required values. N for not bouncing apache with clear cache and y for bouncing apache with clear cache

Attach Value set to concurrent request:-
Concurrent --> Program --> Define
Query your concurrent request and click parameter tab
seq --> any number
parameter--> Name of parameter to be presented to user while submitting concurrent request. It can be any name
Value Set --> Name of Value set created previously.
Other fields will get populated automatically.

Now everything is in place and we are good to bounce our apache server using Concurrent request.

Testing:
Run Concurrent request with parameter y for apache bounce with clear cache.
Run Concurrent request with parameter n for not bouncing apache with clear cache.
Result: Check the log files of concurrent request. It will give you output in desired way. If request provide error then DBA need to troubleshoot.

How to check if Oracle Home is RAC OFF

1.Go to $ORACLE_HOME/rdbms/lib
2.run the following command: nm -r libknlopt.a grep -c kcsm.o
3.If above command returns 0, then RAC is not linked.
If it returns more than 0, then it is linked.