Query to monitor Alert Log report of last n days.

SET linesize 500 pagesize 500
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
col ORIGINATING_TIMESTAMP for a20 head Date


SELECT to_char(originating_timestamp,'DD-MM-RR HH24:MI:SS') "Time", message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - &Days
AND
regexp_like(message_text, '(TNS-|ORA-|error)');



Enjoy :)
Gaurav Sahi

How to access Oracle Apps R12/11i on Linux Machine (Mozilla Firefox)

How to access Oracle Apps 11i/R12 forms from Linux VM or Linux machine?

In you to access forms from Linux client, Three steps are installed (You can skip first if Mozilla is installed on your Linux client) –

1. Install Mozilla Browser

2. Install JRE on client machine (JRE version to install will depend on Server configuration, 

Identify which version of JRE you need on client machine

– For 11i/R12 : Login to server as application o.s. user (applmgr), set environment variable and run grep plugin $CONTEXT_FILE   (If you get output like 1.5.0_13 you need JRE 1.5.0 patchset 13, if its 1.5.0_10 you need 1.5.0 patchset 10; or for 1.4.2_04 you need 1.4.2 patchset 4 )

3. Use JRE installed above on client machine in Browser (Mozilla) Plugin 

1. Install FireFox Mozilla

– Download Mozilla for Linux from http://www.mozilla.com/en-US/firefox/all.html#en  firefox-2.0.0.7.tar.gz

– “gunzip -d firefox-2.0.0.7.tar.gz” (You will get unzipped file with name as firefox-2.0.0.7.tar)

– Extract tar file as “tar -xvf  firefox-2.0.0.7.tar”

– This will create directory called “firefox” which is under you will see executable called “firefox“; Use this executable to start FireFox on Linux machine.

2. Install JRE on client machine

–Download JRE  5.0 Update 10 (change this as per your server plugin version shown above; I am on 12.0.0 hence 1_5.0.10) for Linux from http://java.sun.com/products/archive/j2se/5.0_10/index.html  ”jre-1_5_0_10-linux-i586-rpm.bin”

– ”chmod 755 jre-1_5_0_10-linux-i586-rpm.bin”

– ./jre-1_5_0_10-linux-i586-rpm.bin  (You will be asked to accept license agreement , click enter and when prompted to accept license enter Yes)

– This will install jre 1.5.10 in linux machine under /usr/java  and plugin is installed under /usr/java/jre1.5.0_10/plugin/i386/ns7/libjavaplugin_oji.so

3. Configure JRE Plug-in in browser

– Go to Mozilla FireFox plug-in directory which is $location_where_you_unziped_firefox_directory/plugins and create soft link like

– ln -s /usr/java/jre1.5.0_10/plugin/i386/ns7/libjavaplugin_oji.so ./libjavaplugin_oji.so

Oracle Applications (11.5.10.2) Installation on Red Hat Enterprise Linux

 

Before start installation:

 

1. The installation of E-Business Suite R11 needs minimum of 2G swap, secure Linux disabled and the following package groups installed:

• X Window System
• GNOME Desktop Environment
• Editors
• Graphical Internet
• Server Configuration Tools
• Development Tools
• Administration Tools
• System Tools

I strongly suggest , choose “install everything” option while you are making Redhat Enterprise Linux installation. Its little hard to try install-add some rpms and other tools on Redhat Enterprise Linux.In this article, i assume that you have been installed everything.

2.The installation size of Oracle E-Business Suite R11 is:

• 57 GB for a fresh install with a production database:

Application tier file system – 26 GB
Database tier file system (fresh install with a production database) – 31 GB

• 91 GB for a fresh install with a Vision Demo database.

Application tier file system – 26 GB
Database tier file system (fresh install with a production database) – 65 GB

3. Create stage area folder:

You need to setups file for can make Oracle E-Business Suite R11 installation. You can use edelivery.oracle.com site for can download Oracle E-Business Suite the lastest version.

PS: It’s not avaliable to download Oracle E-Business Suite R11 setup file from edelivery.oracle.com for a now. If you need setups file please log a SR(service request) or please contact with your local office)

Oracle E-Business Suite R11 setup files almost have 31 GB size.

You can create stage folder by following:

Oracle E-Business Suite R11 installation manual – Page 1-14 Setting Up the Stage Area

Mainly;

Put all the files under one directory (i.e. /data/R11stage), and extract all the files under the same directory and give it 777 permission For instance:

[root@test]# mkdir /data/R11Stage

 

[root@test]# chown –R 777 /data/R11Stage

 

[root@test]# cd /data/R11Stage

For Start Here DVD create below:

mkdir startCD

For APPL_TOP DVD create below:

mkdir oraApps

For RDBMS DVD create below:

mkdir oraDB

For Tools DVD create below:

mkdir oraiAS

For Databases DVD create below:

mkdir oraAppDB

Before start to installation, i strongly suggest check below:

MD5 Checksums for 11i10.2 Rapid Install Media [ID 316843.1] note for can observe you have any problem wiht your stage or not(Corrupted zip etc)

 

Ensure that /etc/hosts and /etc/oraInst.loc are set right:

[oracle@erp ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost.localdomain localhost
192.168.1.50    erp.freeoraclehelp.com erp
[oracle@erp ~]$ cat /etc/oraInst.loc 
inventory_loc=/oracle/erp/R12/oraInventory
inst_group=oinstall
[oracle@erp ~]$ 

Create OS User:

# groupadd oinstall -g 2000
# useradd -g oinstall -u 2000 oracle
# chown -R oracle:oinstall /oracle

 

Set Kernel Parameters:

[root@erp ~]#  tail /etc/sysctl.conf
# Oracle Recommendations for Apps  #
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 256 32000 100 142
fs.file-max = 131072
net.ipv4.ip_local_port_range = 10000 65000
kernel.msgmni = 2878
kernel.msgmax = 8192
kernel.msgmnb = 65535
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 262144
# Oracle Recommendations for Apps  #

[root@erp ~]# sysctl -p

Increase user limits: Add the following to /etc/security/limits.conf

  * hard nofile 65535
  * soft nofile 4096
  * hard nproc 16384
  * soft nproc 2047

Add the following to /etc/profile

if [ $USER = "oracle" ]; then 
        if [ $SHELL = "/bin/ksh" ]; then 
              ulimit -p 16384 
              ulimit -n 65536 
        else 
              ulimit -u 16384 -n 65536 
        fi 
fi 

Ensure the following RPMs are installed: compat-db-4.1.25-9
compat-gcc-32-3.2.3-47.3
compat-gcc-32-c++-3.2.3-47.3
compat-libgcc-296-2.96-132.7.2
compat-libstdc++-296-2.96-132.7.2
compat-libstdc++-33-3.2.3-47.3
xorg-x11-deprecated-libs-devel-6.8.1-23.EL
xorg-x11-deprecated-libs-6.8.1-23.EL
openmotif-2.1.30-x

[root@erp RPM_OEL4]# ls -ltr
total 8
-rwxr-xr-x  1 root root 5814 Feb  5  2011 p4198954_40_LINUX.zip
[root@erp RPM_OEL4]# pwd
/dumps/ERP/11i/RPM_OEL4
[root@erp RPM_OEL4]# unzip p4198954_40_LINUX.zip 
Archive:  p4198954_40_LINUX.zip
  inflating: compat-libcwait-2.1-1.i386.rpm  
  inflating: compat-oracle-rhel4-1.0-5.i386.rpm  
  inflating: README.txt              
[root@erp RPM_OEL4]# rpm -ivh compat-*
Preparing...                ########################################### [100%]
   1:compat-oracle-rhel4    ########################################### [ 50%]
   2:compat-libcwait        ########################################### [100%]
[root@erp RPM_OEL4]#  

Installation

Post-Install Checks

Once installation is complete, verify the logins at http://erp.freeoraclehelp.com:8010. Here are the default passwords.
Database:
SYS/change_on_install
SYSTEM/manager
Application Schema passwords:
APPS Schema: APPS/APPS
GWYUID User: APPLSYSPUB/PUB
Guest User: GUEST/ORACLE
Application Users:
SYSADMIN/SYSADMIN
MFG/WELCOME
OPERATIONS/WELCOME
SERVICES/WELCOME
MRC/WELCOME
HRMS/WELCOME

Admin scripts

RDBMS ORACLE_HOME Control scripts
(located under <RDBMS ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>)

Control Script	Functionality
addbctl.sh	Control database server
addlnctl.sh	Control Oracle Net listener for the database server

RDBMS ORACLE_HOME Install scripts
(located under <RDBMS ORACLE_HOME>/appsutil/install/<CONTEXT_NAME>)

Install Script	Functionality
adsvdlsn.sh	Start Oracle Net listener during installation
adcrdb.sh	Start database and create database control files
addbprf.sh	Set profile option values
adsvdcnv.sh	Perform character set conversion and licensing tasks
adsvdb.sh	Start database during installation

 

 

COMMON_TOP Control scripts
(located under <COMMON_TOP>/admin/scripts/<CONTEXT_NAME>)

Control Script	Functionality	Node
adalnctl.sh	Control Oracle Net8 listener for Applications services	All application tier server nodes
adstrtal.sh	Start all Applications server processes	All application tier server nodes
adstpall.sh	Stop all Applications server processes	All application tier server nodes
adfrmctl.sh	Control Forms server	Forms server node
adfmcctl.sh	Control Forms Metrics Client	Forms server node
adfmsctl.sh	Control Forms Metrics Server	HTTP server node
adtcfctl.sh	Control TCF SocketServer	Concurrent processing server node
adcmctl.sh	Control Concurrent managers	Concurrent processing server node
adrepctl.sh	Control Reports server	Concurrent processing server node
adapcctl.sh	Control Apache processes	HTTP server node
jtffmctl.sh	Control Oracle fulfillment server	HTTP server node

COMMON_TOP Install scripts
(located under <COMMON_TOP>/admin/install/<CONTEXT_NAME>)

Install Script	Functionality	Node
adsvalsn.sh	Start Net8 listener processes for Applications services	All application tier server nodes
adsvfrm.sh	Start Forms server during install	Forms server node
adsvfmc.sh	Start Forms server during install	Forms server node
adsvfms.sh	Start Forms metric server during install	HTTP server node
adsvcm.sh	Start Concurrent manager during install	Concurrent processing server node
adsvtcf.sh	Start TCF server during install	Concurrent processing server node
adsvrep.sh	Start Reports server during install	Concurrent processing server node
adsvapc.sh	Start Apache server during install	HTTP server node
jtfsvfm.sh	Start Fulfillment server during install	HTTP server node

 

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