Retrieve Apps password in R12 or Forgot Apps Password in R12

Steps to retrieve forgotten apps password in R12

Step 1 : login to DB-Node

sqlplus / as sysdba

STEP 2 : Create Function for to decrypt 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> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';


Output

ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A

STEP 4:

SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A') from dual;




Output

APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG37E123746712BDB2D99E048FE44AE662F2713E2EDB443391BABA0414CADDB7A2E6DD45BBAFA7270A663E5EEBA837F5548A')
--------------------------------------------------------------------------------
DEMONSTRATE

STEP 5: (Test apps password)

SQL> conn apps/DEMONSTRATE;
Connected.

CHEERS :)

Query to find all current Oracle Application user logged in R12 & 11i

There are 2 Queries:-

1) SELECT DISTINCT icx.session_id,
icx.user_id,
fu.user_name,
fu.description
FROM icx_sessions icx, fnd_user fu
WHERE disabled_flag != 'Y'
AND icx.pseudo_flag = 'N'
AND (last_connect +
DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
NULL, limit_time,
0 , limit_time,
fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
AND icx.counter < limit_connects
AND icx.user_id = fu.user_id;

2) SELECT DISTINCT ic.disabled_flag,
fu.user_name User_Name,
fr.RESPONSIBILITY_KEY Responsibility,
fu.user_id,
fu.description,
fu.employee_id,
ic.responsibility_application_id,
ic.responsibility_id,
ic.org_id,
ic.function_type,
ic.counter,
ic.first_connect,
ic.last_connect,
ic.nls_territory,
ic.time_out,
fr.menu_id,
fr.responsibility_key
FROM fnd_user fu,
fnd_responsibility fr,
icx_sessions ic
WHERE fu.user_id = ic.user_id
AND fr.responsibility_id = ic.responsibility_id
AND ic.disabled_flag ='N'
and IC.RESPONSIBILITY_ID is not null
AND ic.last_connect > sysdate - (ic.time_out/60)/96;

How to clean FND_NODES table in Oracle Applications

Step 1: Always apply the latest cloning patches to avoid all the bugs and fixes

Step 2: SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;
It will delete all old data from the table.

Step 3: Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers to repopulate the required system tables.

How to migrate responsibility / menu / form (personalizations) from one ERP apps (11i / R12) instance to another using FNDLOAD?

Below are the commands

===========================
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

 

WAS error while installing UPK Professional Release 11.1

Hello Everybody,

While starting the User Productivity Kit Professional Release 11.1 installtion on Windows 2003 R2 Server with SP2 the most common error comes is:-



To resolve the issue we nned to install IIS on Windows 2003. Following are the steps:-

 

1. Click Start, point to Control Panel, and then click Add or Remove Programs.
2. In Add or Remove Programs, click Add/Remove Windows Components.
3. In the Windows Components Wizard, under Components, select Application Server.
4. After the wizard completes the installation, click Finish.
5. Click Next.

 

NOTE:- For thus you need Windows Server 2003 CD

 

After that you can continue with the UPK Server installation.

 

R12 installation 64 bit on OEL 5 update 5(From Scratch to END)

1. Download R12 64 bit software from

https://edelivery.oracle.com (create your account if you using this site first time).
Select product E-business suite on 64 Bit.

Oracle E-Business Suite Release 12.1.1 Media Pack for Linux x86-64-bit ; download first 39 cd. and unzip them on the host where you want to perform the installation.

2. Set Up Stage Area Directory

$ cd /u01
$ mkdir StageR12

3. Unzip the Files

$ cd /u01/download

$ unzip -d /u01/StageR12 <ZipFile>

Prerequisites before Starting Installation:-

a. Make sure these package has been installed manually:

openmotif21-2.1.30-11.EL5.i386
xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386
These two rpm can be download from here:
http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/
For Update 1 or 2:
binutils-2.17.50.0.6-6.0.1.x86_642
For Update 3:
binutils-2.17.50.0.6-9.0.1.x86_642
For Update 4 (5.4) or higher, see workaround listed below in the 'After Installing or Upgrading' section.
The following packages must be installed from the Oracle Linux 5 or RHEL 5 distribution media:
gcc-4.1.2-14.el5.x86_64
gcc-c++-4.1.2-14.el5.x86_64
glibc-2.5-18.i686 (32-bit)
glibc-2.5-18.x86_64
glibc-common-2.5-18.x86_64
glibc-devel-2.5-18.i386 (32-bit)
glibc-devel-2.5-18.x86_64
libgcc-4.1.2-14.el5.i386
libgcc-4.1.2-14.el5.x86_64
libstdc++-devel-4.1.2-14.el5.i386
libstdc++-devel-4.1.2-14.el5.x86_64
libstdc++-4.1.2-14.el5.i386
libstdc++-4.1.2-14.el5.x86_64
make-3.81-1.1.x86_64
gdbm-1.8.0-26.2.1.i386
gdbm-1.8.0-26.2.1.x86_64
libXp-1.0.0-8.1.el5.i386
libXp-1.0.0-8.1.el5.x86_64
libaio-0.3.106-3.2.i386
libaio-0.3.106-3.2.x86_64
libgomp-4.1.2-14.el5.x86_64
sysstat-7.0.0-3.el5.x86_64
util-linux-2.13-0.45.el5.x86_64
compat-libstdc++-296-2.96-138.i386
compat-libstdc++-33-3.2.3-61.i386


b. Kernel Settings
Edit the /etc/sysctl.conf
Parameter Value
kernel.semmsl 256*
kernel.semmns 32000*
kernel.semopm 100*
kernel.semmni 142*
kernel.shmall 2097152
kernel.shmmax Half the size of the physical memory (in bytes), and at least 2147483648
kernel.shmmni 4096
kernel.msgmax 8192
kernel.msgmnb 65535
kernel.msgmni 2878
fs.file-max 65536
net.ipv4.ip_local_port_range 10000 65000**
net.core.rmem_default
262144
net.core.rmem_max 262144
net.core.wmem_default 262144
net.core.wmem_max 262144

c. Verify that the /etc/hosts
There should be information of hostname with IP address.

d. Modifying the Number of Open File Descriptors
Edit /etc/security/limits.conf
* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047

e. OS Library Patch for Oracle HTTP Server (on Oracle Linux 5 and RHEL 5 only)
Download and apply the patch 6078836 from My Oracle Support to fix an issue with the Oracle HTTP Server (missing libdb.so.2) bundled

Create User & Group:-

a) Create group dba and user oracle & .

# groupadd dba
#useradd -g dba oracle




b) Create directory

# mkdir -p /d01/oracle
# chown -R oracle:dba /d01/oracle
# chmod -R 775 /d01/oracle

INSTALLATION PROCESS:-

a. Login as applmgr and change directory to /stage/StageR12/StartCD/Disk1/rapidwiz
b. Run Rapidwiz ...Type './rapidwiz' and press Enter
c.  A screen appears which takes you through the installation steps .....Click Next on first 3 screens.
d. Enter the CSI number, Metalink account and country and click Next else Ignore that.
e. Choose create new configuration and Click Next
f. Choose a port pool and Click Next
g. Choose Database type Vision/fresh, Specify a SID,verify host and domain.....
Give the username as applmgr and group as dba and specify the directory as /oraDB and Click Next
h. Select Suite Licensing and Click Next
i. Click Next on the Next 2 screens to accept license products and country specific functionalities.
j. Choose Database character set as UTF8 and click Next
k. Verify the host,domain and specify the base directory as /oraAPP and instance directory as /oraAPP/inst and Click Next
l. Click Next ...The rapidwiz verifies and validates the system configuration and notifies if there are any errors...All the checks have to return a GREEN
tick mark if passed. Correct the errors if there are any RED cross marks and restart the rapidwiz.
m. Click Next and then Yes on the next screen to start the installation .
n. Once the installation is finished it performs post installation checks and notifies if there are any errors. It should return all GREEN marks if successfully installed
o. Click on Finish and that Ends the Installation Steps

Common Error after Installation:-

a) rw-50015 error - HTTP is not responding

ln -s /usr/lib/libgdbm.so /usr/lib/libdb.so.2

but in my case it just created a broken link.

I had to use this instead:

ln -s /usr/lib/libgdbm.so.2 /usr/lib/libdb.so.2

Restart the Application and database server.

Start rapid installation again with $rapidwiz -restart

 

 

How to Create Readonly User (for specific Schema) in Oracle Database

1) create user bolinf_readonly identified by readonly;

2) select username from dba_users
    where username like '%BOLINF%';

3) grant create session to BOLINF_READONLY

4) grant select any table to BOLINF_READONLY

5) create or replace trigger log_on_after_bolinf
    after logon ON BOLINF_READONLY.SCHEMA
    BEGIN
    EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = BOLINF';
    END;

Enjoy. . . . .
:)