How to check if a patch is applied in Oracle E Business Suite 11i, R12.1.x and R12.2.x

In Oracle EBS R12.2.x :

In Oracle E Business Suite (ebs erp) R12.2.x you cannot query the AD_BUGS table to check if patches have been applied..
The AD_BUGS table may have entries for patches that were applied but later the patching cycle was aborted (not really applied).


The way to check whether a patch is really applied is to use the AD_PATCH.IS_PATCH_APPLIED PL/SQL function.

Usage:

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 (20034256);

or for single app tier installations:

select ad_patch.is_patch_applied('R12',-1,20034256) from dual;

Expected results:

EXPLICIT = applied
NOT APPLIED = not applied / aborted

Note: If you are sure patch is applied, but showing as not applied then do the following workaround.

1. Start adadmin after source the RUN FS env.
2. Select "2. Maintain Applications Files menu" in "AD Administration Main Menu".
3. In "Maintain Applications Files", select "4. Maintain snapshot information".
4. Select "2. Update current view snapshot" in the "Maintain Snapshot Information".
5. Select "1. Update Complete APPL_TOP" in the "Maintain Current View Snapshot Information".

In EBS ERP 11i and R12.1.x:

Below Queries can be used to check if patch is applied:

select * from ad_bugs 
where bug_number = '&bug_number'; 

select * from ad_applied_patches 
where patch_name = '&bug_number'; 

SELECT DISTINCT a.bug_number, e.patch_name, c.end_date, b.applied_flag 
FROM ad_bugs a, 
  ad_patch_run_bugs b, 
  ad_patch_runs c, 
  ad_patch_drivers d, 
  ad_applied_patches e 
WHERE a.bug_id = b.bug_id 
AND b.patch_run_id = c.patch_run_id 
AND c.patch_driver_id = d.patch_driver_id 
AND d.applied_patch_id = e.applied_patch_id 
AND a.bug_number LIKE '&bug_number' 
ORDER BY 1 DESC ;

Reference:

Useful Scripts for E-Business Suite Applications Analysts (Doc ID 887438.1)
Tips and Queries for Troubleshooting Advanced Topologies (Doc ID 364439.1)
How To Check if a Patch is Applied in 12.2.x? (Doc ID 1963046.1)

Unable to open Oracle Apex Login Page / Oracle APEX Login Page Blank

Description of Issue

Oracle APEX 5.1 new installation(Embedded plsql gateway configuration) completed successfully, However, Unable to open Oracle Apex Login Page / Oracle APEX Login Page Blank. Tried to access below URL still same issue http://<Machine-Name>:<port-number>/i/apex_version.txt

Cause

Database parameters [shared_servers,DISPATCHERS] are not properly set which are required for XDB

Solution

Verify Listener and below Database Parameters:     $ lsnrctl status     SQL> show parameter local listener     SQL> show parameter dispatchers     SQL> show parameters shared_servers 1) We should have shared_servers > 0, please set the following      alter system set shared_servers = 5;    2) You will also need to set dispatchers      Reference:     How to Setup XDB Protocol Server: FTP, HTTP, WebDAV ( Doc ID 362540.1 )      Specifically:      *************************************************************************      Set the DISPATCHERS parameter.      The DISPATCHERS system parameter is required for XDB protocol registration with the                 Listener. The DISPATCHERS parameter is set in the init.ora.      Run the following in SQLPlus to determine if the dispatchers parameter is already set:      show parameter dispatchers      If XDB dispatchers is not already set, add the following line to the init.ora:      Non-RAC:      dispatchers="(PROTOCOL=TCP)(SERVICE=<sid>XDB)"      Or from the SQL*Plus prompt:      SQL> alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)" scope=both                 sid='<sid>';      RAC:      instanceid1.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid1>XDB)"      instanceid2.dispatchers="(PROTOCOL=TCP) (SERVICE=<instanceid2>XDB)"      Or from the SQL*Plus prompt:       Execute this for each node:       alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=XDB)" scope=both sid='<sid>';       NOTE: Replace <sid> ,<instanceid1>, etc. with the actual values.       For example:       dispatchers="(PROTOCOL=TCP) (SERVICE=OrclXDB)"       If the DISPATCHERS parameter is set then the default value for SHARED_SERVERS is 1.            The value of SHARED_SERVERS parameter should always be >= 1 for this setup to work.

Script To Find Versions of Oracle E Business Suite R12.2 Forms & Reports, FMW WebTier & oracle_common, Weblogic Server Products and other technology stack components

Execute below commands to find the version of Oracle Forms and Reports, Oracle Fusion Middleware (FMW) - Web Tier & oracle_common and Oracle WebLogic Server (WLS) product in EBS R12.2

Source the Oracle EBS Applications environment file as the owner of the application tier file system.


========================================================================
Oracle Forms and Reports - Product version
========================================================================

export ORACLE_HOME=`grep s_tools_oh $CONTEXT_FILE | sed 's/^.*s_tools_oh[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`
${ORACLE_HOME}/bin/frmcmp_batch help=y |grep 'Forms 10.1 (Form Compiler) Version' |awk '{ print "Oracle Forms and Reports Product version : " $6 }'

========================================================================
Oracle Fusion Middleware (FMW) - Web Tier & oracle_common - Product version
========================================================================

export ORACLE_HOME=`grep s_weboh_oh $CONTEXT_FILE | sed 's/^.*s_weboh_oh[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`
$ORACLE_HOME/OPatch/opatch lsinventory -detail | grep 'Oracle WebTier and Utilities CD' | awk NR==1{'print "FMW - WebTier & oracle_common Product version : " $6'}

========================================================================
Oracle WebLogic Server (WLS) - Product version
========================================================================

export MYJAVA=`grep s_adjvaprg $CONTEXT_FILE | sed 's/^.*s_adjvaprg[^>.]*>[ ]*\([^<]*\)<.*/\1/g; s/ *$//g'`
${MYJAVA} -cp $FMW_HOME/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:$FMW_HOME/wlserver_10.3/server/lib/weblogic.jar weblogic.version |grep PSU |awk {'print "Oracle WebLogic Server (WLS) Product Version : " $3'}

Execute below for Detailed versions of technology stack components (Forms, iAS, Framework, JDK, OJSP, Database, etc.):

On Application Tier

Source the Applications environment file as the owner of the application tier file system and run:

$ADPERLPRG $FND_TOP/patch/115/bin/TXKScript.pl \
 -script=$FND_TOP/patch/115/bin/txkInventory.pl \
 -txktop=$APPLTMP \
 -contextfile=$CONTEXT_FILE \
 -appspass=apps \
 -outfile=$APPLTMP/Report_App_Inventory.html -reporttype=text

Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script.

On Database Tier 

Source the Oracle Database environment file as the owner of the Database tier file system and run:

$ADPERLPRG $ORACLE_HOME/appsutil/bin/TXKScript.pl \
 -script=$ORACLE_HOME/appsutil/bin/txkInventory.pl -txktop=$ORACLE_HOME/appsutil/temp \
 -contextfile=$CONTEXT_FILE \
 -appspass=apps \
 -outfile=$ORACLE_HOME/appsutil/temp/Report_DB_Inventory.html -reporttype=text

Once the command executes successfully, it should generate the report file in the location specified for "outfile" parameter in above script.

AutoPatch error: The worker should not have status 'Running' or 'Restarted' at this point.

Description of Issue

R12.2 ADOP: AutoPatch error: The worker should not have status 'Running' or 'Restarted' at this point. AutoPatch error: Error running SQL and EXEC commands in parallel

Cause

ADOP Patch Session got terminated abnormally due to network issue or patch hung or Lost connectivity. Error encountered when tried to re-start adop patch.

Solution

1. Start Adctrl and look at the worker status.  Are workers running or started?   Select option: 1. Show Worker Status then 4. Tell manager that a worker failed its job   2. Restart the adop patch from the begining. Use below options: - If you want to restart a failed patch from the very beginning, you need to specify below options with adop restart=no abandon=yes - If you want to restart a failed patch from where it left off, you only need to specify below options with adop restart=yes abandon=no

datapatch : Database 12c Post Patch(Opatch) SQL Automation

Datapatch:

Database release 12c extends patch install automation for patches that contain post-patch SQL instructions.
Prior to Oracle 12c such patches required manual intervention to complete the post-patch SQL instructions after restarting the database.

Datapatch is the new tool that enables automation of post-patch SQL actions for RDBMS patches.

Datapatch usage:

All arguments are optional, if there are no arguments then datapatch will automatically determine which SQL scripts need to be run in order to complete the installation of any patches that contain post-patch SQL instructions.

Optional arguments:

-db <sid>
Use the specified database's SID rather than $ORACLE_SID

-apply <patch1,patch2,...,patchn>
Only consider the specified patch list for apply operations

-rollback <patch1,patch2,...,patchn>
Only consider the specified patch list for rollback operations

-force
Run the apply and/or rollback scripts even if not necessary per the SQL registry

-prereq
Run prerequisite checks only, do not actually run any scripts

-oh <oracle_home value>
Use the specified directory to determine what patches are installed

-verbose
Output additional information used for debugging

-help
Output usage information and exit

-version
Output build information and exit

Invoke datapatch:

$ cd $ORACLE_HOME/OPatch
$ datapatch

EBSapps.env environment does not exists in Oracle E Business Suite R12.2.0

EBSapps.env does not exists in Oracle E Business Suite R12.2.0 and it will be created after upgrading to R12.2.2 or later.

If you would like to create the environment file similar to EBSapps.env then refer below metalink

How To Automatically Set the Current Run or Patch Edition / File System for EBS 12.2 (Doc ID 1545584.1)

Please not R12.2.0 is not certified and you must consider of upgrading the Oracle E Business Suite R12.2.0 to R12.2.2 or later version ASAP.


EBSapps.env in R12.2.2 or later :

Change directory to the Base directory and run script EBSapps.env giving "run" or "patch" as argument, eg:

Ex:
cd /u01/oracle/EBS122
. ./EBSapps.env run

How to verify the Oracle E Business Suite R12.2 startCD Version

To verify the Rapid Install version, use the RapidWizVersion executable
located in the rapidwiz directory on "Start Here" CD.

$ cd /Stage/12.2/startCD/Disk1/rapidwiz
$ ./RapidWizVersion

[root@dbahost rapidwiz]# ./RapidWizVersion


Oracle E-Business Suite Rapid Install Wizard
Version 12.2.0.51
(c) Copyright 2000-2011 Oracle Corporation.  All rights reserved.

[root@dbahost rapidwiz]#