Find the apps or sysadmin password in oracle EBS 12 if you lost or forgotten

To find the apps password ::

Step #1 :: sqlplus / as sysdba

Step #2 :: Create Function to decrypt the encrypted password

                  create or replace 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';

/

Step#3: Query for Apps password

select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';

ENCRYPTED_FOUNDATION_PASSWORD

--------------------------------------------------------------------------------

ZG3038D54765ACE934DC0A38BEB448D156FC8E6861B0C26794FFA4E9EFBA84297CA7E41E58E78FA7A951ED6670EF445D2131

SELECTapps.decrypt_pin_func('GUEST/ORACLE','ZG3038D54765AC0A38BEB448D156FC8E6861B0C26794FFA4E9EFBA84297CA7E41E58E78FA7A951ED6670EF445D2131') from dual;

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG3038D54765ACE934DC0A38BEB448D156FC8E6861

--------------------------------------------------------------------------------

GOODBYE

Step #4 : Test apps password

           sql>conn apps/goodbye;

                 Connected.

To find SYSADMIN Password or any Front end user in ORACLE EBS

Step 1 # :: SQL> conn apps/goodbye;

                             Connected.

Step 2 # :: create the decrypt package

CREATE OR REPLACE PACKAGE get_pwd AS FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2) RETURN VARCHAR2; END get_pwd;

/

Step# 3: 

CREATE OR REPLACE PACKAGE BODY 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 get_pwd;

 /

Step #4: To get user password use below query

SELECT usr.user_name,get_pwd.decrypt((SELECT (SELECT get_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 = '&USERNAME';

USER_NAME   PASSWORD

--------------------------------------------------------------------------------

SYSADMIN       Admin@123