Oracle Application Patch Steps

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.

Applying a Patch:

Step 1 -Download the patch from metalink

It is better to check the table 'AD_BUGS' if the patch is already applied then no need to apply again.

you can check list of applied patches as follows:

  select *from ad_bugs where bug_number = 'patch_number';

select *from ad_applied_patches where APPLIED_PATCH_ID = 'patch_number';

if this query returns any row then it means this patch is already applied.

to see the difference between 'AD_BUGS' and 'AD_APPLIED_PATCHES'.

AD_BUGS: 
=========
Holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.


AD_APPLIED_PATCHES:
===================
Holds information about the "distinct" Oracle Applications patches that have been applied. 

If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

Step 2 -Check all Per-requisites from read me file. First apply all the patche(s) which are listed in pre-requisites (if any) and then apply the required patch and if there is/are any patch suggested in post-requisite then apply those too.

  

Step -3 Check successful patch completion condition from read me file.

(If successful patch condition already meets then no need to apply the patch.)

Step -4 Upload the patch in patch directory (i.e /appshome/patch) in zip format

Extract the patch through following

unzip <file name>

Step -5 Down the application services of concerned instance. Or do exactly what else is written in "Pre-requisite" if any.

Step -6 Run .env file of concerned instance. (Optional)

e.g (/appshome/appsuat1/UAT1/apps/apps_st/appl/APPSUA T1_erpappdev.env)

Step -7 Go to the directory of patch location  /appshome/patch (i.e cd /appshome/patch)

  

Step -8 Enter the command adadmin, following command lines will appear after adadmin.

                                                                 

Press ENTER for default value.

   
(Enter the log file name with extension .log or Press Enter for default value)

Press ENTER for default value, if you enter yes means the failure activities occurred in mail.

Press ENTER for default batchsize value.

(Press ENTER for default value)

Enter the system password usually its password is  'manager'.

Enter the SYSTEMApps  Password or Press ENTER for DEFAULT.

 Enter 5 as your choice  Change Maintenance Mode.

Enter 1 as your choice  Enable Maintenance Mode.

Enter 3 as your choice  Return to main menu.

   
Enter 6 as your choice for Exit adadmin area location.

  

Step -9 Enter the command adpacth in that patch location.

Press ENTER for default APPL_TOP value.

Enter the adpatch log file name with extension .log

Press ENTER for default value.

 Press Enter for default batch size.

 Enter the SYSTEM Password by default MANAGER.

 Enter the apps password.

 Select or enter your directory where patch is placed.

Enter the driver file name placed in patch directory with .drv extension.

* Auto patch process will start now, follow the following steps after completion of auto patch.

The Final result will come like,

AutoPatch is complete.

AutoPatch may have written informational messages to the file

/appshome/appstest/apps_st/apps/admin/TEST/log/uxxxxx.drv.lgi

Errors and warnings are listed in the log file

/appshome/appstest/apps_st/apps/admin/TEST/log/uxxxxx.drv.log

and in other log files in the same directory.

Step -10 Enter the adadmin command again to disable maintenance mode.

         

         Repeat complete Step 8 and follow the following screen

Step -11 Bounce the services of DB.

Step -12 Bring up the application services.

Step -13 Check the error in patch log file in mentioned location.

Regards,

Vijay.

Workflow Notification mailer issue

Issue :

Workflow Differed Job got filled so the workflow notification mailer couldn't send mail.

Solution :

I saw the particular user mail status in that all status was shows expired.

So I submitted "Move Messages from Exception to Normal Queue of Workflow 

Agent" concurrent request. It will ask parameters then we need to give 

WF component name in my case I was given WF_NOTIFICATION_OUT.

Reference:

1. Howto Move Notifications From Exception Queue To Normal Queue?ID472204.1

2. FNDWF_MOVE_MSGS_EXCEP2NORMAL "The agent is not found"[ID 760983.1]

3. Error APP-FND-1030 Is Displayed When Trying To Submit "Move messages 

from Exception to Normal Queue of Workflow Agent" FNDWF_MOVE_MSGS

_EXCEP2NORMAL [ID 1451820.1]

For Enabling De-Queue we need run below procedure,

exec dbms_aqadm.start_queue(

queue_name=>'APPLSYS.AQ$_WF_NOTIFICATION_OUT_E',dequeue=>TRUE,

enqueue=>FALSE)

Regards,

Vijay.

Steps For Cloning Process

Cloning process:

Ans :

1. Run adpreclone as applmgr and oracle user on source Perl adpreclone.pl dbTier as oracle user Perl adpreclone.pl appsTier as applmgr user

2. Take the cold/hotbackup of source database

3. Copy the five directories appl,comn,ora , db,data to target

4. Rename the directories, and change the permisssion

5. Set the inventory in oraInst.loc

6. Run perl adcfgclone.pl dbTier as oracle user,if the backup type is cold

7. If the backup type is hotbackup then Perl adcfgclone.pl dbTechStack.
   Create the control file on target from the control script trace file from source Recover the database   Alter database open resetlogs

8. Run autoconfig with the ports changed as per requirement in xml.

9. Run perl adcfgclone.pl appsTier as applmgr

10. Run autoconfig with the ports changed as per requirement in xml.

Location of adpreclone.pl for oracle user:

Ans : RDBMS_ORACLE_HOME/appsutil/scripts/

Location of adpreclone.pl for applmgr user:

Ans : $COMMON_TOP/admin/scripts/

Location of adcfgclone.pl for oracle user:

Ans : $RDBMS_ORACLE_HOME/appsutil/clone/bin

Location of adcfgclone.pl for applmgr user:

Ans : $COMMON_TOP/clone/bin


Reference : http://appsdba.info/index.php?module=pagemaster&PAGE_user_op=view_page&PAGE_id=58&MMN_position=53:53

Find Application User Password in Oracle E-Business Suite 11i & R12

This scripts allows to view the passwords which are stored encrypted in fnd_user table.

We can use different sqls to find apps password and application user passwords.

Connect as an Apps User to do the following,

Create Package

CREATE OR REPLACE PACKAGE XXARTO_GET_PWD

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2;

END XXARTO_GET_PWD;

/

 Package created.

Create Package Body
 

CREATE OR REPLACE PACKAGE BODY XXARTO_GET_PWD

AS

FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

RETURN VARCHAR2

AS

LANGUAGE JAVA

NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END XXARTO_GET_PWD;

/

Package body created.

Find apps user password in 11i

SELECT (SELECT XXARTO_GET_PWD.Decrypt (

UPPER( (SELECT UPPER (Fnd_Profile.VALUE ('Guest_User_Pwd'))

FROM DUAL)),

Usertable.Encrypted_Foundation_Password)

FROM DUAL)

AS Apps_Password

FROM applsys.Fnd_User Usertable

WHERE Usertable.User_Name LIKE

UPPER( (SELECT SUBSTR (

Fnd_Profile.VALUE ('Guest_User_Pwd'),

1,

INSTR (Fnd_Profile.VALUE ('Guest_User_Pwd'), '/')

- 1)

FROM DUAL));

APPS_PASSWORD
--------------------------------------------------------------------------------
APPS

Find other application user password in 11i

Select Usertable.User_Name , (Select XXARTO_GET_PWD.Decrypt (Upper ((Select
(Select XXARTO_GET_PWD.Decrypt (Upper ((Select Upper (Fnd_Profile.Value ('Guest_User_Pwd'))
From Dual)), Usertable.Encrypted_Foundation_Password) From Dual) As Apps_Password
From Fnd_User Usertable
Where Usertable.User_Name Like Upper ((Select Substr (Fnd_Profile.Value ('Guest_User_Pwd'),
1 , Instr (Fnd_Profile.Value ('Guest_User_Pwd'), '/') - 1 ) From Dual)))) ,Usertable.Encrypted_User_Password)
From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username');

Enter value for username: vijay
old   7:        From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('&Username')
new   7:        From Dual) As Encrypted_User_Password From Fnd_User Usertable Where Usertable.User_Name Like Upper ('vijay')

USER_NAME                  ENCRYPTED_USER_PASSWORD
-------------------------------------------------------------
VIJAY                               ORACLE

Find other application user password in R12

ALTER SESSION SET current_schema = apps;

Select Usr.User_Name, Usr.Description,

       Get_User_Pwd.Decrypt((Select (Select Get_User_Pwd.Decrypt

       (Fnd_Web_Sec.Get_Guest_Username_Pwd, Usertable.Encrypted_Foundation_Password)From Dual) As Apps_Password

       From Fnd_User Usertable  Where Usertable.User_Name = (Select Substr(Fnd_Web_Sec.Get_Guest_Username_Pwd,1,

       Instr(Fnd_Web_Sec.Get_Guest_Username_Pwd,'/') - 1) From Dual)), Usr.Encrypted_User_Password) Password

  From Fnd_User Usr

 Where Usr.User_Name = '&User_Name';

Find  application user password (for example SYSADMIN) in 11i
 

SELECT Usertable.User_Name,
(SELECT XXARTO_GET_PWD.Decrypt (
UPPER( (SELECT (SELECT XXARTO_GET_PWD.Decrypt (
UPPER( (SELECT UPPER(Fnd_Profile.VALUE('Guest_User_Pwd'))
FROM DUAL)),
Usertable.Encrypted_Foundation_Password)
FROM DUAL)
AS Apps_Password
FROM applsys.Fnd_User Usertable
WHERE Usertable.User_Name LIKE
UPPER( (SELECT SUBSTR (
Fnd_Profile.VALUE (
'Guest_User_Pwd'),1,
INSTR (
Fnd_Profile.VALUE (
'Guest_User_Pwd'),
'/')
- 1)
FROM DUAL)))),
Usertable.Encrypted_User_Password)
FROM DUAL)
AS Encrypted_User_Password
FROM Applsys.Fnd_User Usertable
WHERE Usertable.User_Name LIKE UPPER ('&Username');

Enter value for username: sysadmin
old  24: WHERE Usertable.User_Name LIKE UPPER ('&Username')
new  24: WHERE Usertable.User_Name LIKE UPPER ('sysadmin')

USER_NAME                     ENCRYPTED_USER_PASSWORD
--------------------------------------------------------------------------------
SYSADMIN                              SYSADMIN

Regards,
Vijay.