Script for Changing Oracle Application Users Password

2 comments
We can use the below API for changing the EBS(fnd_user) user password.
 
 apps.fnd_user_pkg.changepassword
 
Sample Script:
 
DECLARE
   v_user_name      VARCHAR2 (100) := 'HSINGH';

   v_new_password   VARCHAR2 (100) := :NEWPASSWORD;
   v_status         BOOLEAN := NULL;
BEGIN
   v_status := fnd_user_pkg.changepassword (v_user_name, v_new_password);

 
   COMMIT;
   DBMS_OUTPUT.put_line (
      'Password is changed successfully for the user=> ' || v_user_name);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            'Error encountered while restting password for user and the Error Detail is '
         || SQLERRM);
END;

 
The script can also be modified to reset bulk ebs user passworda. The below is a sample script, You need to modify as per you need.
 
Refer below syntax
 
DECLARE
   v_status   BOOLEAN;
   CURSOR c_user
   IS
      SELECT user_name
        FROM fnd_user
       WHERE     NVL (end_date, SYSDATE + 1) > SYSDATE
             AND user_name NOT IN ('SYSADMIN',
                                   'GUEST',
                                   'XML_USER',
                                   'APPSMGR',
                                   'PORTAL30',
                                   'PORTAL30_SSO');
BEGIN
   FOR user_name_list IN c_user
   LOOP
      BEGIN
         v_status :=
            fnd_user_pkg.ChangePassword (
               username      => user_name_list.user_name,
               newpassword   => 'welcome123');
      --  dbms_output.put_line('password sucessfully changed for' || user_name_list.user_name);

      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  'Error encountered while restting password for users and the Error Detail is '
               || SQLERRM);
      END;
   END LOOP;
END;


2 comments :

Post a Comment

Change\Expire all application users password forcefully

1 comment
We can forcefully expire password for all EBS user.This feature is available after RUP4.We need to apply  Patch 4676589 ATG RUP 4 enabling this feature.

Script:

The below script can be used to expire all passwords in the fnd_user table $FND_TOP/sql/AFCPEXPIRE.sql.

Execution Method

Connect to SQL*Plus
sqlplus  APPS/ APPSPASSWORD
@AFCPEXPIRE.sql

OR

Submit Concurrent Program

 CP SQL*Plus Expire FND_USER Passwords

Note:
This script sets the fnd_user.password_date to null for all users which causes all user passwords to expire.The user will need to create a new password upon the next login.

1 comment :

Post a Comment

How to Enable\Disable large number of EBS users

No comments
To Disable/Enable bulk number of users in Oracle Applications, we can use the below API

apps.fnd_user_pkg.EnableUser =>To Enable Users
apps.fnd_user_pkg.DisableUser =>To Disable Users\End Date Users


Syntax:

The below synatx can be used for performing this activity.
We can modify the select statement in Cursor to customize according to need.

declare cursor cur1 is
select user_name from apps.fnd_user where LOWER(user_name) Not IN ('username','username', .......);
begin
for all_user in cur1 loop
apps.fnd_user_pkg.EnableUser(all_user.user_name);
commit;
end loop;
End



DECLARE
   CURSOR cur1
   IS
      SELECT user_name
        FROM fnd_user
       WHERE person_party_id IS NOT NULL;
BEGIN
   FOR all_user IN cur1
   LOOP
      apps.fnd_user_pkg.DisableUser (all_user.user_name);
      COMMIT;
   END LOOP;
END;

No comments :

Post a Comment

Not able to connect to Apps user in Database due to library cache locks

2 comments
If there are many applications integrated with EBS then this issue arises, when we change apps password but the integrated applications keep trying to connect with old password causes library cache locks in the Database.
This prevent apps user from connecting to database.This scenario also arises when EBS instance is refreshed.
Numerous failed logins attempts can cause row cache lock waits and/or library cache lock waits.

We can observe  'Library cache lock' or 'row cache lock' when concurrent users login with wrong password to the database.
'row cache lock' is seen in 10.2 and 11.1
'library cache lock' is seen in 11.2.

Solution

1. Check for bad or incorrect password or login attack by running following sql:

select username,
os_username,
userhost,
client_id,
trunc(timestamp),
count(*) failed_logins
from dba_audit_trail
where returncode=1017 and --1017 is invalid username/password
timestamp < sysdate -7
group by username,os_username,userhost, client_id,trunc(timestamp);

 
2. Set the below event in the spfile or init.ora file and restart the database:

alter system set event ="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1" scope=spfile;

or

EVENT="28401 TRACE NAME CONTEXT FOREVER, LEVEL 1"

REF:Library Cache Locks Due to Invalid Login Attempts (Doc ID 1309738.1)

2 comments :

Post a Comment

TKPROF Overview

No comments
TKPROF is used for diagnosing performance issues.  It formats a trace file into readable format for performance analysis.

Syntax:
tkprof tracefile_name.trc  tracefileoutput.txt sys=no  sort='(prsela,exeela,fchela)'  explain=apps/apps
  
Options for tkprof
 print – Lists only the first n SQL statements in the output file.  If nothing is specified, all statements will be listed.  Use this option when the list needs to be limited to the 'Top n' statements.  This is useful when combined with a sorting option to enable the top n statements by CPU, or disk reads, or parses, etc.
aggregate – When 'Yes', tkprof will combine the statistics from multiple user executions of the same SQL statement.  When 'No', the statistics will be listed each time the statement is executed.
insert – Creates a file that will load the statistics into a table in the database for further processing.  Choose this option if you want to perform any advanced analysis of the tkprof output.
sys – Enables or disables the inclusion of SQL statements executed by the SYS user, including recursive SQL statements.  Default=enable. 
table – Used in the Explain Plan command (if specified) for Oracle to load data temporarily into an Oracle table.  The user must specify the schema and table name for the plan table.  If the table exists all rows will be deleted otherwise tkprof will create the table and use it.
record - creates a SQL script with the specified filename that contains all non-recursive SQL statements from the trace file.  Log the SQL statements in a separate *.sql file.
explain – Executes an Explain Plan for each statement in the trace file and displays the output. Explain Plan provides the predicted optimizer execution path without actually executing the statement.  tkprof shows you the actual execution path and statistics after the statement is executed.
sort – Sorts the SQL statements in the trace file by the criteria required.It provides SQL statements that consume the most resources at the top of the file, rather than searching the entire file contents for the poor performers.

Sort options are as below
  •  prscnt – The number of times the SQL was parsed.
  •  prscpu – The CPU time spent parsing.
  •  prsela – The elapsed time spent parsing the SQL.
  •  prsdsk – The number of physical reads required for the parse.
  •  prsmis – The number of consistent block reads required for the parse.
  •  prscu - The number of current block reads required for the parse.
  •  execnt – The number of times the SQL statement was executed. 
  •  execpu – The CPU time spent executing the SQL. 
  •  exeela – The elapsed time spent executing the SQL.
  •  exedsk – The number of physical reads during execution.
  •  exeqry – The number of consistent block reads during execution.
  •  execu – The number of current block reads during execution.
  •  exerow – The number of rows processed during execution.
  •  exemis – The number of library cache misses during execution.
  •  fchcnt – The number of fetches performed.
  •  fchcpu – The CPU time spent fetching rows.
  •  fchela – The elapsed time spent fetching rows.
  •  fchdsk – The number of physical disk reads during the fetch.
  •  fchqry – The number of consistent block reads during the fetch.
  •  fchcu – The number of current block reads during the fetch.
  •  fchrow – The number of rows fetched for the query.
 

No comments :

Post a Comment

Profiles used for setting up various URLs in an E-Business Suite

No comments
There are many profiles used in EBS. But below are the few profiles which are used to setting up the correct URL in EBS.
Mostly values for these profiles are populated from context files when we run autoconfig.
 
But in some cases when the EBS instance is cloned/refreshed or DMZ setup is performed then we need to make sure that these profiles are properly set at site level and server level(in case of DMZ setup).
 
The hostname and web port should be defined properly.

User Profile Name
Internal Name
 Applications Web Agent
APPS_WEB_AGENT
 Applications Servlet Agent
APPS_SERVLET_AGENT
 Applications JSP Agent
APPS_JSP_AGENT
 Applications Framework Agent
APPS_FRAMEWORK_AGENT
 ICX:Forms Launcher
ICX_FORMS_LAUNCHER
 ICX: Oracle Discoverer Launcher
ICX_DISCOVERER_LAUNCHER
 ICX: Oracle Discoverer Viewer Launcher
ICX_DISCOVERER_VIEWER_LAUNCHER
 Applications Help Web Agent
HELP_WEB_AGENT
 Applications Portal
APPS_PORTAL
 BOM:Configurator URL of UI Manager
CZ_UIMGR_URL
 QP: Pricing Engine URL
QP_PRICING_ENGINE_URL
 TCF:HOST
TCF:HOST
 
The default hierarchy type value for the above profile options is of Security .

No comments :

Post a Comment

Change The Guest Password In E-Business Suite

1 comment
If we want to change the guest user password in E- Biz 11i or R12, the below steps can be followed.

STEPS

1. Login as the "applmgr"(or appserver user which is being used) Unix user and make sure the apps environment files are sourced.

2. Change the below parameter in Context file.
 Ensure the Guest password is entirely uppercase

AutoConfig variable name is "s_guest_pass"

3. Run AutoConfig.

4.  Verify the new guest password as follows:

  Login to sqlplus as apps user

 select fnd_web_sec.validate_login('GUEST','<NEW_PSWD>') from dual;

 this script should return 'Y'

5. Change the guest password used for diagnostics as described in Note 235307.1 "eBusiness Support Diagnostics FAQ and Troubleshooting Guide" and summarized below:

a. Login to applications as SYSADMIN user and choose "CRM HTML Administration" responsibility. (If you don't see this responsibility, it may be end dated for SYSADMIN user.)
b. Then follow this path and correct the GUEST password.
Settings : System : Properties
Self Service User
Settings
System
Properties > Advanced
Choose JTF from the "View" LOV.
Look for guest_password and correct it

6. Bounce(Stop / Start) Apache.

1 comment :

Post a Comment

Cloning running 'adcfgclone.pl appsTier' fails on script txkExecSetJaznCredentials.pl

2 comments
If while cloning the target system we are encountering the below error.

Error:

If we check the log file we can found that the clone is failing on script txkExecSetJaznCredentials.pl.

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/apps/ad/tools/configuration/JaznCredentialManager

Caused by: java.lang.ClassNotFoundException: oracle.apps.ad.tools.configuration.JaznCredentialManager

 at java.net.URLClassLoader$1.run(URLClassLoader.java:200)

 at java.security.AccessController.doPrivileged(Native Method)

 at java.net.URLClassLoader.findClass(URLClassLoader.java:188)

 at java.lang.ClassLoader.loadClass(ClassLoader.java:307)

 at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)

 at java.lang.ClassLoader.loadClass(ClassLoader.java:252)

 at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:320)

Could not find the main class: oracle.apps.ad.tools.configuration.JaznCredentialManager.  Program will exit.
 

.end err out.


Cause:

Environment variable $CLASSPATH is set before running  'adcfgclone.pl appsTier'.


If Environment variable $CLASSPATH is set before running  'adcfgclone.pl appsTier'. The script  txkExecSetJaznCredentials.pl will keep using it.

If the $CLASSPATH is not set, txkExecSetJaznCredentials.pl will set it according to the value passed in context file.

So if the $CLASSPATH is set incorrectly before running clone, the error occurs.
 

Solution:

1. Run command "unset CLASSPATH".

2. Retest the issue.

2 comments :

Post a Comment