How to find all the applied patches details from oracle apps R12

In this post, i will show you how to know all the applied patches details including (Patch number,creation date and patch type).

If you are looking for details of specific patch that was applied, you can use the below script to find the details.

Script:

col PATCH_NAME format a10 col PATCH_TYPE format a10 col DRIVER_FILE_NAME format a15 col PLATFORM format a10 select AP.PATCH_NAME, AP.PATCH_TYPE, AD.DRIVER_FILE_NAME, AD.CREATION_DATE, AD.PLATFORM,AL.LANGUAGE from AD_APPLIED_PATCHES AP, AD_PATCH_DRIVERS AD, AD_PATCH_DRIVER_LANGS AL where AP.APPLIED_PATCH_ID = AD.APPLIED_PATCH_ID and AD.PATCH_DRIVER_ID = AL.PATCH_DRIVER_ID and AP.PATCH_NAME = ‘1423433’;

Output:

PATCH_NAME PATCH_TYPE DRIVER_FILE_NAM CREATION_DATE   PLATFORM   LANG

---------- ---------- --------------- --------------- ---------- ----
1423433      PATCH-SET     u1423433.drv     01-SEP-15             GENERIC      US

If you are looking for the details of all patches that was applied, you can use the below script to find the details.

Script:

col PRD format a40 trunc col PRDID format a5 col PRDSTATUS format a10 col PATCHSET format a20 SELECT V.APPLICATION_NAME PRD, to_char(V.APPLICATION_ID) PRDID, L.MEANING PRDSTATUS, DECODE(I.PATCH_LEVEL, NULL, ’11i.’ || v.APPLICATION_SHORT_NAME || ‘.?’, I.PATCH_LEVEL) patchset FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L WHERE (V.APPLICATION_ID = I.APPLICATION_ID) AND (L.LOOKUP_TYPE = ‘FND_PRODUCT_STATUS’) AND (L.LOOKUP_CODE = I.Status ) ORDER BY 1;

Output:

PATCH_NO	PATCH_TYPE	DRIVER_FILE_NAME	CREATION_DATE	PLATFORM	LANGUAGE
10422612	ONE-OFF	u10422612.drv	5-Aug-12	GENERIC	US
9922442	ONE-OFF	u9922442.drv	15-Feb-13	GENERIC	US
6524778	ONE-OFF	u6524778.drv	3-Jul-13	GENERIC	US
12609242	ONE-OFF	u12609242.drv	14-Jul-13	GENERIC	US
9839039	ONE-OFF	u9839039.drv	14-Aug-14	GENERIC	US
9239089	PATCH-SET	u9239089.drv	15-Aug-14	SUN_OS5	US
9239090	MAINTENANCE-PACK

Retrieve apps password in oracle apps R12

Below steps to get the forgotten apps user password in oracle apps R12.

Step 1:  Connect to sys user 

SQL> show user
USER is "SYS"

Step 2:  Create function to know the encrypted password

SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/

Function created.

Step 3:  Query for password

SQL> set linesize 200 long 300
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD
----------------------------------------------------------------------------------------------------
ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391

Step 4:  Apps password using encrypted guest password

SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZGC679A64D8394F23E12CA4EB288F264FC09EBC9144C06181E921F88A972E231E9B530E7810DE42AC6103FC3CCD317CA3391')
---------------------------------------------------------------------------------------------------------------------------------------------
APPS

Step 5:  Test the password is working fine or not

SQL> conn apps/APPS123;
Connected.

How to clear Cache in EBS 11i and R12 environments and in similar environments?

* How to clear Cache in Apache/iAS, Cabo, browser, Java, Jinitiator, portal, webadi?


Apache / iAS
For 11i and earlier versions:
– shutdown iAS server
– go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
– backup the directory _pages and delete its contents by running for instance:

rm -rf $COMMON_TOP/_pages/*

– for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory
– restart iAS server

To clear middle tier cache in release 12:

– go to “Functional Administrator” responsibility
– select Core Services => Caching Framework => Global Configuration => Clear cache
(please review Note 759038.1 for details).
In case you have login issue after accidentally cleared the _pages instead of using the method above for r12
please review Note 433386.1 to recompile jsp files.

Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache

Web Browser

for Internet Explorer:
– go to menu Tools => Internet Options,
– select ‘General’ tab,
– click on button ‘Delete Files’ in ‘Temporary Internet files’ area
or
– click on button ‘Delete…’ then in ‘Delete Browsing History’ pop-up window click on ‘Delete Files…’
– close all IE windows and restart new browser session.

for Mozilla Firefox:
– go to menu Tools
– select ‘Clear Private Data…’ or ‘Clear Recent History…’ then check ‘Cache’
or
– go to menu Edit or Tools
– select Preferences or Options.
– expand the ‘Advance’ options and choose ‘Cache’ or Privacy
– click the button called ‘Clear Cache’.

for Safari:
– go to Safari menu
– select Empty Cache => Click ‘Empty’ in the dialogue box
for Netscape:
– go to menu Edit => Preferences,
– choose ‘Cache’ in ‘Advanced’ category,
– click on buttons ‘Clear Memory Cache’ and ‘Clear Disk Cache’.
– close all Netscape windows and restart new browser session.

Jinitiator

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:
– delete all files in directory:

C:\Program Files\Oracle\Jinitiator \jcache\

for 1.3.1.x versions:
– go to Start => Parameters => Control Panel
– double-click on “Jinitiator ” icon
– in the new pop-up window, click on “Cache” tab
– click on “Clear Jar Cache ” button. On prompt, click Yes.

(you can also delete directly all files under directory:
C:\Documents and Settings\\Oracle Jar Cache)

Java
Java/JRE plug-in (Windows)
– go to Start => Parameters => Control Panel
– double-click on ‘Java’ icon
– in the new pop-up window, click on ‘General’ tab
– click on ‘Setting…’ button in ‘Temporary Internet files’ area then click on ‘Delete Files…’ button
(you can select Applets, Applications or other files)

JVM

– go to responsibility “Functional Administrator”
– click on “Core Services” tab then the “Caching Framework” sub-tab
– click on “Global Configuration” link then click on “Clear all cache” button to clear all of the Java Cache’s

Portal
– go to the url: http://:/pls/admin_/gateway.htm or cache.htm
– click on “Cache Settings” option and note the “Cache Directory”
– go to this directory and delete all the cache files in the directory and sub directories

See also modplsql caches in Apache/iAS section above.

WebADI / BNE cache
For instance when enabling BNE log
– go to url: http://:/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
– click on the “clear-cache” link
– at the bottom of the page you should see ‘Cache Cleared’

Ref:
How To Clear Caches (Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI) for E-Business Suite? [ID 742107.1]

A script to make autoclone automatically everynight

#!/bin/sh

#############################################################Pay Attention Please############################################################
###This script only tested on RHEL 3.0 for E-business Suite 11i. If you want to use it for E-business Suite R12, You have to Replace the ###
###path of start and stop scripts. Before running the script, make sure all variable has been changed according to your system!!!!!!!!!!!!###
###And because of it is designed to do clone from a backup through Physical Standby and Archivelogs, so you also need to create a Physical###
###Standby on your test server and write a scripts for your Physical Standby to: 1) sync the archive logs; 2) then switch to primary every###
###day; 3) before switchover you should backup the Physical Standby. You should also create a script to: 1) run prelclone on the source db###
###tier, 2) copy $ORACLE_HOME/appsutil/clone from the source db to the target. The last, you should create a backup after your first clone###
###for some files, such as /etc/oraInst.loc, pfile, $CONTEXT_NAME.xml, wdbsvr.app, CGIcmd.dat, etc………………………………….###

oruser=ortest
apuser=aptest
appspw=apps ###Apps password will be changed to this after refreshed###
apclpw=abcdefg ###Apps password of source database###
systpw=xyz ###System password of source database###
CONTEXT_NAME=TEST_testserver
ORACLE_HOME=/test/oracle/product/920
ORACLE_SID=TEST
export oruser apuser appspw apclpw systpw CONTEXT_NAME ORACLE_HOME ORACLE_SID

echo “”
echo “Shutting down services on Middle Tier …please wait”

su – $apuser -c “$COMMON_TOP/admin/scripts/$CONTEXT_NAME/adstpall.sh apps/$appspw”

echo “Waiting for concurrent manager processes to exit…”

echo “Waiting for another 10 munite”

sleep 600

pkill -9 -u $apuser

echo “”
echo “Middle tier services shut down”

echo “”
echo “Shutting down database listener as $oruser user…please wait”
su – $oruser -c “. $ORACLE_HOME/$CONTEXT_NAME.env;$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addlnctl.sh stop $ORACLE_SID”

echo “”
echo “Shutting down database as $oruser user…please wait”
su – $oruser -c “. $ORACLE_HOME/$CONTEXT_NAME.env;$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addbctl.sh stop abort”

echo “”
echo “All services shut down.”

echo “Start to recover database.”

cp –reply=yes /etc/oraInst.loc.db.$ORACLE_SID /etc/oraInst.loc

su – $oruser -c “sh $HOME/rman_standby_recover.sh”

su – $oruser -c “sh $HOME/auto_copy_clone.sh”

echo “Start to Clone database”

su – $oruser -c “cd $ORACLE_HOME/appsutil/clone/bin;perl adcfgclone.pl dbTier $ORACLE_HOME/appsutil/$CONTEXT_NAME.xml <<EOF
$apclpw
EOF
”

su – $oruser -c “. $ORACLE_HOME/$CONTEXT_NAME.env;$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addbctl.sh stop immediate”

su – $oruser -c “. $ORACLE_HOME/$CONTEXT_NAME.env;cp –reply=yes $ORACLE_HOME/dbs/init$ORACLE_SID.ora.bck $ORACLE_HOME/dbs/init$ORACLE_SID.ora”

su – $oruser -c “. $ORACLE_HOME/$CONTEXT_NAME.env;$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/addbctl.sh start”

echo “”
echo “Start to Clone apps”

cp –reply=yes /etc/oraInst.loc.ap.$ORACLE_SID /etc/oraInst.loc

su – $apuser -c “cd $COMMON_TOP/clone/bin;perl adcfgclone.pl appsTier $HOME/$CONTEXT_NAME.xml <<EOF
$apclpw
EOF
”

echo “waiting for application startup”

sleep 1800

echo “”
echo “Shutting down services on Middle Tier …please wait”

su – $apuser -c “$COMMON_TOP/admin/scripts/$CONTEXT_NAME/adstpall.sh apps/$apclpw”

echo “Waiting for concurrent manager processes to exit…”

echo “Waiting for another 10 munite”

sleep 600

pkill -9 -u $apuser

echo “”
echo “Middle tier services shut down,now to change apps password”

su – $apuser -c “FNDCPASS apps/$apclpw 0 Y system/$systpw SYSTEM APPLSYS apps”

su – $apuser -c “cp –reply=yes $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app.bck $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app”

su – $apuser -c “cp –reply=yes $ORACLE_HOME/reports60/server/CGIcmd.dat.bck $ORACLE_HOME/reports60/server/CGIcmd.dat”

echo “”
echo “Rebuild workflow mailer queue And change SITE NAME”

su – $apuser -c “sqlplus apps/$appspw @$FND_TOP/patch/115/sql/wfntfqup apps apps applsys”

su – $apuser -c “sqlplus apps/$appspw @$HOME/auto_change_site_name.sql”

echo “”
echo “Now to restart apps”

su – $apuser -c “$COMMON_TOP/admin/scripts/$CONTEXT_NAME/adstrtal.sh apps/$appspw”

How To Migrate Responsibility / Menu / Form (Personalizations) From One ERP Apps (11i / R12) Instance To Another Using FNDLOAD?

===========================
RESPONSIBILITY
===========================

For migrating a responsibility we need the responsibility key value.

Steps to get the RESPONSIBILITY KEY VALUE

Login as Sysadmin
System Administrator -> Security : Responsibility -> Define
Press F11 -> Enter the Responsibility Name -> Get the responsibility key value from the Responsibility Key Text Field.

To Download

FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct [give some name].ldt FND_RESPONSIBILITY RESP_KEY=”[RESPONSIBILITY_KEY_VALUE]“

To Upload

FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct [name given above].ldt

===========================
MENU
===========================

To Download

FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct [give some name].ldt MENU MENU_NAME=”[MENU_NAME]“

To Upload

FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct [name given above].ldt

=======================
FORMS PERSONALIZATIONS
=======================

For migrating the forms personalizations we need the function name that is associated with the form.

Steps to get the form function name (eg. Users Form)

Login as Sysadmin
System Administrator -> Security : User -> Define
Once the form opens up
On the Tool Bar Click Help -> Diagnostics -> Custom Code -> Personalize
This will open a new form with function name. This is the value we require.

You can perform simillar step for any form that you want to migrate by opening the form to be migrated.

To Download

FNDLOAD apps/[APPS_PWD] 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct [give some name].ldt FND_FORM_CUSTOM_RULES function_name=”[FUNCTION_NAME]“

To Upload

FNDLOAD apps/[APPS_PWD] 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct [give some name].ldt