How to Check Patch Applied Status in Oracle Applications?

R11i,12.0.x,12.1.x
=============

From Oracle Applications Release 11i to 12.1.x, Patch application Status can be queried from ad_bugs or ad_applied_patches tables

Query to check whether patch is applied on a instance.

SQL>select bug_number,creation_date,last_update_date from apps.ad_bugs where bug_number='&patchnum';

SQL> select PATCH_NAME,CREATION_DATE from apps.ad_applied_patches where PATCH_NAME='&patchnum';

What is difference between above two Queries?


Suppose, we applied a patch which has multiple bugfixes.The details of all bugs fixed by the patch are listed in ad_bugs table.
In Contrast,ad_applied_patches table give info only about the patches which are applied through adpatch utility.


R12.2
=====

From Release 12.2, Online Patching utility(adop) is used for Patching application Filesystem.

Querying the data from ad_bugs or ad_applied_patches tables may not give correct information.Because, online patching can be aborted anytime prior to Cutover Phase.
ad_bugs or ad_applied_patches tables may contain entries for patches which are started through adop and later aborted.

The below query gives accurate Information about Patch Applied Status in R12.2

select AD_PATCH.IS_PATCH_APPLIED(\'$release\',\'$appltop_id\',\'$patch_no\',\'$language\') from dual;

example sql:
SELECT adb.bug_number,ad_patch.is_patch_applied('11i', 1045, adb.bug_number)
FROM ad_bugs adb
WHERE adb.bug_number in (22498647);

or for single app tier installations:
select ad_patch.is_patch_applied('R12',-1,22498647) from dual;

Sample Output:

EXPLICIT = applied
NOT APPLIED = not applied / aborted


Database
=========

Login to Database node as the database os user

Set environment using commands below

$export ORACLE_HOME=/u01/app/oracle/product/112

$export PATH=$PATH:$ORACLE_HOME/Opatch

$which opatch
==>It should give output like /u01/app/oracle/product/112/Opatch

$opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc

== This gives list of all patches applied to the ORACLE_HOME


$opatch lsinventory -invPtrLoc $ORACLE_HOME/oraInst.loc|grep 988765


==> To check if a particular patch is applied to the ORACLE_HOME

R12-Password Change Utility

Oracle has introduced a new utility to change the Oracle E-Business Suite passwords in R12.1.2.AFPASSWD is an enhanced version of FNDCPASS utility.
Differances between AFPASSWD and FNDCPASS

1) AFPASSWD only prompts for passwords required for the current operation. allowing separation of duties between applications administrators and database administrators.
Changing sysadmin Password using AFPASSWD prompts only for APPS Password. Changing APPLSYS password prompts for APPS and SYSTEM password.
Whereas, FNDCPASS requires SYSTEM and APPS password to change password for application and database users.

2) AFPASSWD can be run from the database tier as well as the application tier. Whereas, FNDCPASS can be run only on Concurrent Manager Node

3) When changing a password with AFPASSWD, the user is prompted to enter the new password twice to confirm.

Changing SYSADMIN Password  

Syntax:$AFPASSWD -f SYSADMIN
Output:Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.  Working…
Enter new password for user [SYSADMIN]:
Verify new password for user [SYSADMIN]:
Password is changed successfully for user SYSADMIN.
Password is changed successfully for user SYSADMIN.
AFPASSWD completed successfully.

Changing APPLSYS Password

Syntax: $AFPASSWD -c apps@PROD -s APPLSYS
Output: Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Enter the password for your ‘SYSTEM’ ORACLE schema:
Connected successfully to SYSTEM.  Working…
Enter new password for user:
Verify new password for user:
Working…
AFPASSWD completed successfully.

Usage

AFPASSWD
Usage:
AFPASSWD [-c [@]] -f
AFPASSWD [-c [@]] -o
AFPASSWD [-c [@]] -a
AFPASSWD [-c [@]] -l {TRUE|FALSE}
AFPASSWD [-c [@]] -L {TRUE|FALSE}
AFPASSWD [-c [@]] -s

Options:
-c [@]
Specify the connection string to be use, parameters

Application-manager user and/or TWO_TASK value,
this option can be use in combination with the others.
(If not provided, AFPASSWD will try to default the values
from the environment)
is the APPS schema owner.

-f
Change the password for an Application user.

-o
Change the password for an Oracle Applications Database user.

-a Modify ALLORACLE users.

-l {TRUE|FALSE}
Lock|Unlock account for a single Oracle Applications database user.

-L {TRUE|FALSE}
Lock|Unlock accounts for non-essential Oracle Applications database users

-s
Modify APPLSYS user. This requires the execution
of autoconfig (in ALL-TIERS) to distribute the
changes on your instance ie DB-TIER and ADMIN-TIER.

-h
Display this help.

Notes:
- AFPASSWD will prompt for the required passwords.
- AFPASSWD will prompt for new passwords twice for confirmation.
- Data entered for which contains spaces must be enclosed
in double quotes. For example, AFPASSWD -f "SCOTT TIGER"
- The only option permitted in combination with other options is the -c option.
Otherwise, there should only be one option per command (see usage above).
If additional options are provided on the command line, only the first valid
option after -c (if -c is present) will be executed.
$