Patching - Applications

ADPatch 

Applying a patch updates your existing system in various ways, from adding a new
feature or product to improving system performance.

patches are applied for a number of reasons, including:

■ Fixing an existing issue

■ Adding a new feature or functionality

■ Updating to a higher maintenance level
■ Applying the latest product enhancements
■ Providing interoperability to new technology stacks
■ Determining the source of an issue
■ Applying online help

Depending on the type of patch, it may update the file system, or the database, or
both.

Unified Driver File

The unified driver, named u.drv, contains the commands necessary to
change files and database objects, and to generate new objects. It contains copy,
database, and generate portions and performs the copy, database, and generate actions
in the stated order. You typically run the unified driver on all APPL_TOPs. AutoPatch
runs only the actions that are required for the current APPL_TOP.

C,D and G Driver File
C – copy driver
D – database driver
G – forms and reports generation driver

Command Line Patching Utilities
You run the following utilities from the command line.

AutoPatch

AutoPatch is the utility used to apply all patches to the Oracle Applications file system or database.

AD Merge Patch

When you apply patches individually, you must perform patching tasks multiple
times. For example, for every individual patch there may be duplicate link and
generate processes. AD Merge Patch merges multiple patches into a single patch so
that the required patching tasks and processes are performed only once.

i.e. admrgpch -s ./source -d ./target -merge_name merged001

AutoPatch

Use AutoPatch to apply patches to the Oracle Applications file system or database. It
gathers necessary information about your system through a series of prompts. When
you have completed the prompts, AutoPatch performs all the tasks required to apply
the patch, including the following:

■ Reads patch metadata to determine patch dependencies and requirements.
■ Uploads patch information from a prior patch session to the database (if needed).
■ Reads and validates the patch driver file and reads the product driver files.
■ Compares version numbers of object modules from the product libraries and
version numbers of the existing files against the patch files.
■ Backs up all existing files that will be changed by the patch.
■ Copies files.
■ Archives files in libraries.
■ Relinks executables.
■ Generates forms, reports, message, graphics, and Java archive (JAR) files.
■ Compiles JSP files and invalid database objects.
■ Updates database objects.
■ Runs AutoConfig to update configuration files, if any template files are introduced
or updated by the patch.
■ Saves patch information to the database.

AutoPatch takes no action if a patch contains no new updates to files or database
objects in your system. If AutoPatch detects that there is a previously failed AutoPatch session, it will attempt to recover that session.Preparing your System for Patching.

Before you begin a patching session, there are some important tasks you need to
complete.

Enable Maintenance Mode

Before you initiate an AutoPatch session, you should enable maintenance mode and Shutdown concurrent manager. During a maintenance mode downtime, user login is restricted. Users are redirected to a system downtime URL, which informs them that the maintenance session is in progress.

To enable or disable maintenance mode, use the Change Maintenance Mode menu in
AD Administration.

Shut Down Services

If you are applying a patch that updates or relinks files, shut down the corresponding concurrent manager, Web server listeners.

Log File Description

adpatch.log main AutoPatch log file (default name)
adpatch.lgi for AutoPatch informational messages (default name)
adrelink.log for relinking

Command Line Arguments

You can direct the way the AutoPatch operates by adding modifiers to the AutoPatch
start command. These modifiers may be in the form of arguments or options.

$ adpatch logfile=test.log
You can enter more than one token=value argument on a single command line by
separating them with one blank space as in the following AutoPatch command.

$ adpatch printdebug=y flags=hidepw
In some cases, you can include more than one value for a token. In this case, separatethe values with commas.

$ adpatch flags=nohidepw,trace
Comma-separated lists must not contain blank spaces. For example, this command is
not valid:

$ adpatch flags=nohidepw, trace
The following arguments are specific to AutoPatch and can be used to modify and
refine its behavior.

AutoPatch Options
The options= argument is used to pass generic options to AutoPatch. It takes the form
of a comma-separated list. Enter one option or a comma-separated list of options. For
example, options=nocopyportion,nogenerateportion. Do not include a space after the

Option Description

autoconfig Purpose: Tells AutoPatch to run AutoConfig automatically.

Default: autoconfig

Use options=noautoconfig if you are applying a number of patches in
sequence and want to run AutoConfig once, after applying the last patch of
the sequence.

Default: checkfile
Use options=nocheckfile to turn off the checkfile feature.

Default: compiledb for standard patches. nocompiledb for standard patch
translations, documentation patches, and documentation patch translations.
Use options=nocompiledb to save time when multiple non-merged patches are
applied in a maintenance window.

Default: compilejsp for standard patches. nocompilejsp for standard patch
translations, documentation patches, and documentation patch translations.
Use options=nocompilejsp to save time when multiple non-merged patches
are applied in a maintenance window.

Default: copyportion
Use options=nocopyportion to tell AutoPatch not to perform copy actions of
the driver.

Default: databaseportion
Use options=nodatabaseportion to tell AutoPatch not to perform database
actions. of the driver

Default: generateportion
Use options=nogenerateportion to tell AutoPatch not to perform generate
actions of the driver.

Default: nohotpatch
integrity Purpose: Tells AutoPatch whether to verify that the version of each file
referenced in a copy action matches the version present in the patch.

Default: nointegrity
Comments: Using options=nointegrity is safe and avoids some AutoPatch
overhead.

Default: nophtofile
Use options=phtofile to tell AutoPatch not to upload patch history
information to the database.

Interactive or Non-interactive Patching
You can apply patches interactively or non-interactively.

Interactive patching means that you supply basic information that AutoPatch needs by responding to a series of prompts.

Non-interactively substantially reduces the need for user intervention when AutoPatch processes patching tasks. You create a defaults file that contains much of the information you would have supplied at the AutoPatch prompts. Then, when you run AutoPatch, you specify the name of the defaults file, the location of the patch top directory, the name of a driver file, and other parameters on the command line.

Create the defaults file.

Start AutoPatch, using the defaultsfile= argument, and specify the file name and the
path to the defaults file. This creates a defaults file for the current environment.

UNIX:
The file must be under the $APPL_TOP/admin/ directory, where is
the database name (ORACLE_SID/TWO_TASK). For example:

$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/adpatchdef.txt

$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=1234567.log \
patchtop=$APPL_TOP/patches/1234567 driver=u1234567.drv workers=3 \
interactive=no

Restarting a Non-interactive AutoPatch Session

When AutoPatch is running non-interactively and encounters an error, it exits to the
operating system and reports a failure. The restart argument is intended specifically
for this circumstance. When AutoPatch sees the restart=yes argument, it assumes that
there is an old session, and expects to find one. If it cannot, it will fail. Do not indicate restart=yes to start a new AutoPatch session.

Complete the following steps:

1. Look through the log files, diagnose the error, and fix it.
2. Use the same command line options that you used initially, but add restart=yes.

UNIX:
$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=1234567.log \
patchtop=$APPL_TOP/patches/1234567 driver=u1234567.drv workers=3 \
interactive=no restart=yes

Windows:
C:\> adpatch defaultsfile=%APPL_TOP%\admin\testdb1\def.txt \
logfile=1234567.log patchtop=%APPL_TOP%\patches\1234567 \
driver=u1234567.drv workers=3 interactive=no restart=yes

Abandoning a Non-interactive AutoPatch Session

When you specify interactive=no on the AutoPatch command line, AutoPatch expects
that there is no existing failed session. AutoPatch aborts if it finds restart files from a failed session. Running AutoPatch with the interactive=no and restart=yes command line arguments restarts the previously incomplete session.

To start a completely new AutoPatch session when there is an existing failed session,
specify interactive=no and abandon=yes on the AutoPatch command line. With this
command, AutoPatch deletes the restart files and any leftover database information
from the failed session.

Complete the following steps:

1. Verify that you do not want to restart the previous failed session.
2. Start AutoPatch with the abandon=yes option:

UNIX:
$ adpatch defaultsfile=$APPL_TOP/admin/testdb1/def.txt logfile=7654321.log \
patchtop=$APPL_TOP/patches/7654321 driver=c7654321.drv workers=3 \
interactive=no abandon=yes

Windows:
C:/> adpatch defaultsfile=%APPL_TOP%\admin\testdb1\def.txt \
logfile=7654321.log patchtop=%APPL_TOP%\patches\7654321 \
driver=c7654321.drv workers=3 interactive=no abandon=yes

/* Script to find out Patch level of mini Pack */ 

Select product_version,patch_level from fnd_product_installations
where patch_level like '%FND%';

I.e.
PATCH_LEVEL

11i.FND.H
11i.AX.I
11i.AK.G
11i.XLA.H
11i.GL.J
11i.FA.O

AD Patches Tables

• AD_APPLIED_PATCHES
• AD_PATCH_DRIVERS
• AD_PATCH_RUNS
• AD_APPL_TOPS
• AD_RELEASES 
• AD_FILES
• AD_FILE_VERSIONS
• AD_PATCH_RUN_BUGS
• AD_BUGS
• AD_PATCH_COMMON_ACTIONS 
• AD_PATCH_RUN_BUG_ACTIONS
• ad_comprising_patches

Concurrent Manager 

• FND_CONCURRENT_QUEUES 
• FND_CONCURRENT_PROGRAMS 
• FND_CONCURRENT_REQUESTS 
• FND_CONCURRENT_PROCESSES 
• FND_CONCURRENT_QUEUE_SIZE

FND Tables

• FND_APPL_TOPS 
• FND_LOGINS 
• FND_USER 
• FND_DM_NODES 
• FND_TNS_ALIASES 
• FND_NODES 
• FND_RESPONSIBILITY 
• FND_DATABASES 
• FND_UNSUCCESSFUL_LOGINS 
• FND_LANGUAGES 
• FND_APPLICATION 
• FND_PROFILE_OPTION_VALUES

Concurrent Manager Issues

Gather Schema Statistics fails with Ora-20001 errors after 11G database upgrade :

Gather Schema Statistics” program reported following errors in request log files :

 +—————————————————————————+

Start of log messages from FND_FILE

+—————————————————————————+

In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP

stats on table FND_CP_GSM_IPC_AQTBL is locked

stats on table FND_SOA_JMS_IN is locked

stats on table FND_SOA_JMS_OUT is locked

Error #1: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LINE_TYPE_MAP***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #2: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_LOGS***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

Error #3: ERROR: While GATHER_TABLE_STATS:

object_name=GL.JE_BE_VAT_REP_RULES***ORA-20001: invalid column name or duplicate columns/column groups/expressions in method_opt***

+—————————————————————————+

End of log messages from FND_FILE

+—————————————————————————+

  Action :

To Check which objects or tables are locked 

 sql > SELECT OWNER,TABLE_NAME,STATTYPE_LOCKED

         FROM DBA_TAB_STATISTICS

         WHERE STATTYPE_LOCKED IS NOT NULL;

TO Unlock all the tables in a schema at once :

sql> exec dbms_stats.unlock_schema_stats(‘schema_owner’);

e.g : sql> exec dbms_stats.unlock_schema_stats(‘apps’);

TO Unlock all Individual  tables in a schema at once 

sql> exec dbms_stats.unlock_schema_stats(‘table_owner’,'table_name’);

e.g : sql > exec dbms_stats.unlock_schema_stats(‘AR’,'AR_REV_REC_QT’);

There are two reasons for that error message:

1 ) There are duplicate rows on FND_HISTOGRAM_COLS table for JE_BE_LINE_TYPE_MAP table.

Because of this problem, FND_STATS tries to gather histogram information using wrong command and it fails with ora-20001 errors.

Following SQL should have returned one row, not two.

SQL> select a.column_name, nvl(a.hsize,254) hsize

from FND_HISTOGRAM_COLS a

where table_name = ‘JE_BE_LINE_TYPE_MAP’

order by column_name;

COLUMN_NAME HSIZE

—————————— ———-

SOURCE 254

SOURCE 254

 2) Column does not exist on the table but still listed in FND_HISTOGRAMS_COL table.

Solution:

Find out all duplicates and/or obsolete rows in FND_HISTOGRAM_COLS and delete one of them.

Remember to take backup of the FND_HISTOGRAM_COLS table before deleting any data.

– identify duplicate rows

select table_name, column_name, count(*)

from FND_HISTOGRAM_COLS

group by table_name, column_name

having count(*) > 1;

– Use above results on the following SQL to delete duplicates

delete from FND_HISTOGRAM_COLS

where table_name = ‘&TABLE_NAME’

and  column_name = ‘&COLUMN_NAME’

and rownum=1;

– Use following SQL to delete obsoleted rows

delete from FND_HISTOGRAM_COLS

where (table_name, column_name) in

  (

   select hc.table_name, hc.column_name

   from FND_HISTOGRAM_COLS hc , dba_tab_columns tc

   where hc.table_name  =’&TABLE_NAME’

   and hc.table_name= tc.table_name (+)

   and hc.column_name = tc.column_name (+)

   and tc.column_name is null

  );

There are no active responsibilities available for this user – Oracle Apps 11i:

Problem Statement: Recently I encountered a problem in Oracle Apps 11i (11.5.10.2). When a user login into the application he/she gets error “There are no active responsibilities available for this user “and user is not able to see any responsibility in the home page.

The problem is same even for sysadmin user. Now the challenge is that, if the system administrator is also not able to see any responsibility then its very difficult to administer other users facing same problem. So here is the solution that I found out as per metalink note ID 316959.1

Solution :

Part 1: Verification

Execute following queries for verification as per metalink note ID 335487.1

1) Please check if table FND_USER_RESP_GROUPS was backed up to table FND_USER_RESP_GROUPS_OLD in the APPLSYS schema, and verify that there is a SYNONYM  FND_USER_RESP_GROUPS_OLD in the APPS schema.

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE ’FND_USER_RESP_GROUPS_OLD’
AND OBJECT_TYPE IN (‘VIEW’,’TABLE’, ‘SYNONYM’);

OWNER                           OBJECT_NAME                                         OBJECT_TYPE
——————————     ——————————                                       ——————
APPLSYS                        FND_USER_RESP_GROUPS_OLD       TABLE
APPS                                FND_USER_RESP_GROUPS_OLD       SYNONYM

2) Check if object FND_USER_RESP_GROUPS is a view or a table

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM ALL_OBJECTS WHERE OBJECT_NAME LIKE ‘FND_USER_RESP_GROUPS’;  2

OWNER                          OBJECT_NAME                                  OBJECT_TYPE
——————————    ——————————                               ——————
APPS                               FND_USER_RESP_GROUPS           VIEW
APPS_MRC                   FND_USER_RESP_GROUPS           SYNONYM
RTREADONLY            FND_USER_RESP_GROUPS           SYNONYM

FND_USER_RESP_GROUPS should be a view in APPS schema.

If FND_USER_RESP_GROUPS is of type TABLE in the APPLSYS schema  please apply solution from step 3 from metalink note ID 335487.1

Else execute following steps from metalink note ID 316959.1

Part 2: Fix

Run following SQLs

1)

SELECT *
FROM Fnd_Responsibility_vl
WHERE Application_Id = 1
AND Responsibility_Name = ‘System Administrator’
AND Start_Date <= Sysdate
AND ( End_Date is NULL OR End_Date > Sysdate );

2)

SELECT count(*)
FROM Fnd_User_Resp_Groups;

3)

Back up the wf_role_hierarchies table into a new table you create.

CREATE TABLE wf_role_hierarchies_copy AS
SELECT * FROM wf_role_hierarchies;

Delete the entries in that table (2 rows exist)

TRUNCATE TABLE applsys.wf_role_hierarchies;

Run affurgol.sql FORCE

SQL> @$FND_TOP/patch/115/sql/affurgol.sql FORCE

Then replace those entries back into wf_role_hierarchies table

SQL> INSERT INTO wf_role_hierarchies
SELECT * FROM wf_role_hierarchies_copy;

Bounce Apache and try to login as sysadmin. You should be able to see the correct responsibilities.

CM not starting after Cloning ?

Once after doing Rapid cloning some of concurrent managers were not starting. The issue was that the node was wrongly defined as source node in Administer Manager screen. I then checked the FND_NODES table which wrongly had the source nodes as well apart from the target nodes.

I performed the following action plan which resolved the issue->

•          Shutdown the apps services

•          EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

EXIT;

•          Ran AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers.

•          Start the apps services

Actually FND_CONC_CLONE.SETUP_CLEAN clears up the tables with nodes information and when we run autoconfig, it repopulates these tables with correct node information.

Concurrent Manager Trouble shooting

Concurrent manager trouble shooting :

1. First check the CM is up or not by using below any one procedure.
      i. ps –ef|grep FNDLIBR
     ii. adcmctl.sh status apps/appsPWD
    iii. Login as system administrator responsibilities go to the below navigation Concurrent -> manager -> Administer -> see the  Actual and target for all the managers

2. If CM in down check the internal manager Logfile in $APPLCSF/APPLLOG location for errors.
3. If any errors related to FNDFS then check the Application listener STATUS , if it is not running start it.
4. If CM is up/running then log file and output file are not able see the user from the front end then check the Application listener is status if it is not running then start it.

5. If any user is complained some request is running long time then

   i. First check the CM status and ICM status.
  ii. Check any Locks on DB particularly related to that program.
 iii. Check any INVALID objects related to that program.
 iv. Check any incompatible requests/programs are running for that program.
Login as system administrator responsibility and query the long running request /program. Navigate to concurrent program define query that long running request in the same form select incompatible requests .
If any incompatible request is running then find the user name who is running that program inform them regarding the incompatibilities.

How to Purge Concurrent Request and/or Manager Data?

Loigin to frontend with sysadmin responsibility:

Navigate :

System Administrator– > Concurrent –> Requests

Click on Submit a New Request — >Select Single Request click ok

Enter Name as Purge Concurrent Request and/or Manager Data

 Entity – ALL / Request / Manager

 ALL - Purges records from database tables that record history information for concurrent requests,concurrent managers, and purges request log files, manager log files, and report output files from the operating system.

Request - Purges records from database tables that record history information for concurrent requests, and purges request log files and report output files from the operating system.

Manager – Purges records from database tables that record history information for concurrent managers, and purges manager log files from the operating system. 

 Mode - Age / Count

Age – Enter the number of days for which you want to save concurrent request history, log files, and report output files. The purge program deletes all records older (in days) than the number you enter. For example, if you enter “5″, then all concurrent request history, log files, and report output files older than five days is purged.

Count – Enter the number of (most recent) records for which you want to save concurrent request history, log file, and report output files. The purge program starts from the most recent records, retains the number you enter, and purges all remaining records. For example, if you enter “5″, then the five most recent concurrent request history records, request log files, manager log files, report output files are saved, and all remaining records are purged.

 Mode Value – Value

Mode Value – Enter a value to define the number of days for Mode=Age or the number of records for Mode=Count. The valid values are 1 – 9999999

 Then Submit.

 The database tables that are affected by running the Purge Concurrent Request and/or Manager Data program are:

 FND_CONCURRENT_REQUESTS : This table contains a complete history  of all concurrent requests.

FND_RUN_REQUESTS :  When a user submits a report set, this table stores information about the reports in the report set and the parameter values for each report.

FND_CONC_REQUEST_ARGUMENTS : This table records arguments passed by the concurrent manager to each program it starts running.

FND_DUAL : This table records when requests do not update database tables.

FND_CONCURRENT_PROCESSES: This table records information about Oracle Applications and operating system processes.

FND_CONC_STAT_LIST : This table collects runtime performance statistics for concurrent requests.

FND_CONC_STAT_SUMMARY: This table contains the concurrent program performance statistics generated by the Purge Concurrent Request and/or Manager Data program. The Purge Concurrent Request and/or Manager Data program uses the data in FND_CONC_STAT_LIST to compute these statistics.

Purge Concurrent Request and/or Manager Data?

One of the important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space inFND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called “Purge Concurrent Requests And/Or Manager Data” should be scheduled to run on a regular basis. This request to purge can be configured  the request data from the FND tables as well as the log files and output files on accumulate on disk.

When the tables FND_CONCURRENT_REQUESTS and FND_CONCURRENT_PROCESSES reaches above 3000-4000 rows, the performance diminishes.You have to run Purge Concurrent Request and/or Manager Data program on a regular basis depending on the amount of request being run.

 Find Size in KB for FND_CONCURRENT_REQUESTS :

 sql> SELECT (BYTES/1024)/1024 “Size in KB” from dba_segments where SEGMENT_NAME=’FND_CONCURRENT_REQUESTS’;

O/P  : Size in KB
           ———-
           3.65

 Sql> SELECT (BYTES/1024)/1024 “Size in KB” from dba_segments WHERE     SEGMENT_NAME=’FND_CONCURRENT_PROCESSES’;

O/P :    Size in KB
              ———-
              10.75

 The Purge Concurrent Request and/or Manager Data program job can be used to purge Requests,Mgr Logs

and all requests depending on what is choosen.

 Use the following options: Enter=ALL, Mode =Age, Mode Value=15

What happens if you don’t give cache size while defining Concurrent Manager ?

Lets first understand what is cache size in Concurrent Manager. When Manager picks request from FND CONCURRENT REQUESTS Queues, it will pick up number of requests defined by cache size in one shot & will work on them before going to sleep. So in my views if you don’t define cache size while defining CM then it will take default value 1, i.e. picking up one request per cycle.

Why I need two Concurrent Processing Nodes or in what scenarios PCP is Used ?

Well If you are running GL Month end reports or taxation reports annually these reposrts might take couple of days. Some of these requests are very resource intensive so you can have one node running long running , resource intensive requests while other processing your day to day short running requets.

another scenario is when your requests are very critical and you want high resilience for your Concurrent Processing Node , you can configure PCP. So if node1 goes down you still have CM node available processing your requests.

What is PCP is Oracle Applications 11i ?

PCP is acronym for Parallel Concurrurent processing. Usually you have one Concurrent Manager executing your requests but if you can configure Concurrent Manager running on two machines (Yes you need to do some additional steps in order to configure Parallel Concurrent Processing) . So for some of your requests primary CM Node is on machine1 and secondary CM node on machine2 and for some requests primary CM is on machine2 & secondary CM on machine1.

What is Sleep Time?

The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal) . However, reducing this number to a very low value many cause excessive cpu utilization.

What is Queue Size?

 The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of one PMON cycle should be used.

What is PMON cycle?

 This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.

What happens if user kills ICM?

Applications will be down

If ICM is down, what happens?

All the other managers will keep working. ICM takes care of the queue control requests, which means starting up and shutting down other concurrent managers.

FNDCPASS Utility

FNDCPASS SYNTAX AND TROUBLE SHOOTING

In Oracle Applications, we have an FND functionality for changing the passwords for application users, product schema passwords, and “APPS and APPLSYS.”  The location of FNDCPASS utility is $FND_TOP/bin directory.

FNDCPASS Usage:

FNDCPASS logon 0 Y system/password mode username new_password
where logon is username/password[@connect]
system/password is password of the system account of that database
mode is SYSTEM/USER/ORACLE
username is the username where you want to change its password
new_password is the new password in unencrypted format
example FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
FNDCPASS apps/apps 0 Y system/manager ORACLE GL      GL1
FNDCPASS apps/apps 0 Y system/manager USER   VISION  WELCOME

If you just type FNDCPASS and press enter, it will give you these details.

The first usage

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME
is for changing the password for apps and applsys. These are the database schema users (most important for application to work). Password for both these users should be in synch. You can change the password of these users using this command. Note that this is the only way to change the password for apps and applsys. Please do not try any other method for changing apps and applsys password. Oracle recomends using FNDCPASS only to change apps and applsys password. Also note that using this command will change the password for both apps and applsys.

Following activities will take place

(1) applsys validation. (make sure APPLSYS name is correct)
(2) re-encrypt all password in FND_USER
(3) re-encrypt all password in FND_ORACLE_USERID
(4) update applsys’s password in FND_ORACLE_USERID table.
(5) Update apps password in FND_ORACLE_USERID table.

Also changes are made in DBA_USERS table.

The second usage

FNDCPASS apps/apps 0 Y system/manager ORACLE GL      GL1
is for changing password for any other product schema like MSC, GL etc.
Following activities will take place

(1) update GL’s password in FND_ORACLE_USERID table. The new password is re-encrypted with the current applsys password.

If GL does not exists, step (2) below does not happen. Message for invalid oracle user is written in the log file.

(2) alter user to change GL’s password.

The third usage

FNDCPASS apps/apps 0 Y system/manager USER   VISION  WELCOME
is for changing the application level passwords like sysadmin etc used for logging into application.

Following activities will take place

(1) update VISION’s password in FND_USER table. The new password is re-encrypted with the current applsys password.

If VISION does not exist, message for invalid application user is written in the log file.
No products affected by the patch

When you run FNDCPASS command it will check the integrity of all schema password in the application. If any of the password is corrupt then this will through and error and will not change the password.

The tables that it uses is FND_USER and FND_ORACLE_USERID. All the application passwords and schema passwords are stored in these two tables. Ofcourse DBA_USERS will have the schema users and password stored as well.

When we run FNDCPASS it will update all the above 3 tables.

Best usage of  FNDCPASS

Before using FNDCPASS:

1) Always keep the back of tables FND_USER and FND_ORACLE_USERID. You can take back of these tables using CREATE TABLE — AS SELECT * FROM —.
You must have backup of these tables before running FNDCPASS. In case if FNDCPASS fails then it might corrupt the passwords of your application and worst can happen that the application wont come up. So always be cautions about this command.

2) If possible also keep an export dump of these two tables.

3) verify each arguement you are providing to FNDCPASS. Like verify that apps and system passwords you are providing is correct.

4) Never update apps, applsys or any schema password directly from database using the alter command. Always use FNDCPASS. System password can be set directly using ALTER command in database.

Issue with APPLSYS and APPS password

Issue 1:

As you know that apps and applsys password should be in synch and should be changed using FNDCPASS.

There can be situation where a novice user changes applsys password from the backend database. In that case when you try to start the services it will show following error

APP-FND-01496: Cannot access application ORACLE password
Cause: Application Object Library was unable access your ORACLE password.

You can even reproduce this issue (ofcourse after taking the backup of FND_USER and FND_ORACLE_USERID table) using the following steps

1. Use the ALTER USER command to change the APPLSYS password

2. Try to run the adstrall.sh script to start Apps services.

3. You will get an error “Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.”

4. Then try FNDCPASS to fix password and you will get the error the APP-FND-01496 error.

If this situation happens then you cannot access the application. Infact the services even wont start.

Resolution to such problem is to rollback the 2 tables FND_USER and FND_ORACLE_USERID. Once you rollback the tables, apps and applsys passwords will be in synch and password will be older one. You can then run FNDCPASS and change the password.

Issue 2:

Some times when you run FNDCPASS, you get following error

APP-FND-01502: Cannot encrypt application ORACLE password
Cause: Application Object Library was unable encrypt your ORACLE password.
Action: Contact your support representative. (ORACLEUSER=APPS_SERV)

The error comes because the table fnd_oracle_userid contain rows for schemas that does not exist. Those rows must be deleted from the table.

Use the following query to get the details of the schema that doest not exists

select * from fnd_oracle_userid
where oracle_username not in
(select username from all_users);

The rows returned by this query can be deleted from FND_ORACLE_USERID table. This will resolve this issue.

Issue 3:

There can be situation where users has update APPLSYS password using ALTER command in database directly and also you dont have backup of those tables. Under such situation, it is very difficult to recover the application and make it working. Still following methodology is proposed which might help you to restore the password back and make your application work fine.

For this to work you should have some other application (may be debug or UAT) which is having the same passwords or default passwords for schemas. If you have such application the following the below steps in the application which is affected by password mismatch.

This method is for resetting apps and applsys passwords. Below are the SQL statements that will help you reset the APPS and APPLSYS passwords to APPS, the APPLSYSPUB password to PUB, and the SYSADMIN password to SYSADMIN.

WARNING: This procedure will cause all user passwords to become invalid. ALL users passwords will need to be reset through the sysadmin responsibility.

Step 1) Reset the Oracle User IDs

Open a SQL*Plus as SYSTEM and reset the passwords for the APPS, APPLSYS, and the APPLSYSPUB Oracle user ID:

   ALTER USER apps IDENTIFIED BY apps;
ALTER USER applsys IDENTIFIED BY apps;
ALTER USER applsyspub IDENTIFIED BY pub;

Step 2) Backup the FND_ORACLE_USERID and FND_USER tables (even though these tables are right now corrupted, do take a backup. You can restore the same when ever you want).

Open a SQL*Plus session as APPLSYS and backup the tables:

create table FND_ORACLE_USERID_BAK as (select * from FND_ORACLE_USERID);

create table FND_USER_BAK as (select * from FND_USER);

Step 3) Reset the APPS and APPLSYS application encrypted passwords

Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.

update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZGA34EA20B5C4C9726CC95AA9D49EA4DBA8EDB705CB7673E645EED570D5447161491D78D444554655B87486EF537ED9843C8′
where ORACLE_USERNAME in (‘APPS’, ‘APPLSYS’);
commit;

This encrypted string we are updating is the default encrypted string for apps. So if your application is having apps password the encrypted string will look like this. We are updating this encrypted string here directly.

Verify the table update:

select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME IN (‘APPS’, ‘APPLSYS’);  

Step 4) Reset the APPLSYSPUB application encrypted password

Open a SQL*Plus session as APPLSYS and update the FND_ORACLE_USERID table.

update FND_ORACLE_USERID
set ENCRYPTED_ORACLE_PASSWORD = ‘ZG31EC3DD2BD7FB8AD2628CE87DDDF148C1D2F248BE88BE987FDF82830228A88EF44BC78BC7A9FAD4BFB8F09DAD49DF7280E’
where ORACLE_USERNAME = (‘APPLSYSPUB’);
commit;

The above encrypted string is the encrypted string for password pub. If your applsyspub password is pub then the encrypted string in FND_ORACLE_USERID will look like this.

Verify the table update:

select ENCRYPTED_ORACLE_PASSWORD
from FND_ORACLE_USERID
where ORACLE_USERNAME = ‘APPLSYSPUB’;

Once these updates are done, try your luck by running FNDCPASS and it should work fine.

References

Metalink note ID 445153.1

Metalink note ID 429244