Pre-requisites for Oracle Apps R12 installation

R12 installation is one of the crucial task for Apps DBA.Below are the steps described:Preparation for Oracle Apps R12 Installation:
User & Mount point/File system :

We have created two users for installing Oracle R12 Application (one for database tier & another for middle tier also called as application tier). We need 73 GB space (45GB DB & 28GB application tier)

1)Create groups & Users for Oracle R12 Application software installation:
su - root
#groupadd dba => group of users to be granted SYSDBA system privilege
#groupadd oinstall => group owner of Oracle files
#useradd -m -c -g oinstall -G dba oracle =>For Database tier
#useradd -m -c -g oinstall -G dba applmgr =>For Application tier
passwd oracle
passwd applmgr

2) Minimum Requirement for Oracle R12 Application installation:
CPU & RAM
CPU 1.6 GHz
RAM 1GB
Staging Area

We need to use adautostg.pl available in Start Here CD.Advantage of using staging area is that after start CD you don’t have to manually insert CD’s and Installer will automatically pick next CD from staging Area. We can pass on configuration setting on first 3-4 screens & once installer starts we can see complete installation. In case of any failure or subsequent installations (If you have to install more than one Instance its always better to install using Staging Area)

[root@test]# more /etc/hosts
127.0.0.1 localhost.localdomain localhost
Open your hosts file wiht editor(I will use vi editor in this article),put your info in this file and save it. In this article my localhost will be test, my localdomain will be helios.com
PS: If you are using standalone server or pc you can give use any domain name you want. But if your server or pc on any domain you must use localdomain.
[root@test]# vi /etc/hosts
127.0.0.1 localhost.localdomain localhost
10.100.x.x test.helios.com test

2. Set Kernel Parameters:
Add following line in end of the /etc/sysctl.conf file.
[root@test]# vi /etc/ sysctl.conf
#R12.1.1 kernel settings
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 256 32000 100 142
fs.file-max = 327679
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=4194304
#full qualified hostname(format is host.localdomain) << optional
kernel.hostname = test.helios .com << optional
#correct domain name !! << optional
kernel.domainname = helios.com << optional
After this steps you must set your new settings wiht below command:
/sbin/sysctl –p

3. Update /var/tmp/.oracle with 777 permissions
chmod 777 /var/tmp/.oracle
4. Edit /etc/security/limits.conf file:
Add the following lines to the /etc/security/limits.conf file:
[root@test]# vi /etc/ security/limits.conf
* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047

5. Edit /etc/resolv.conf file:
Add the following lines to the /etc/resolv.conf file:
options attempts:5
options timeout:15

6. Check /etc/sysconfig/network files format:
Verify that the /etc/sysconfig/network file is formatted as follows:
HOSTNAME=[node_name].[domain_name]

7. If the /etc/sysconfig/networking/profiles/default/network file exists, remove it.
If you changed any files in the previous steps, restart the system.
Be sure we have below rpms on our system:
The following i386 packages must be installed from the OEL 5 or RHEL 5 distribution media:
• compat-glibc-2.3.4-2.26
• gcc-4.1.2-14.el5
• gcc-c++-4.1.2-14.el5
• glibc-2.5-123
• glibc-common-2.5-123
• glibc-devel-2.5-12
• libgcc-4.1.2-14.el53
• libstdc++-devel-4.1.2-14.el5
• libstdc++-4.1.2-14.el53
• make-3.81-1.13
• gdbm-1.8.0-26.2.13
• libXp-1.0.0-8.1.el5
• libaio-0.3.106-3.23
• libgomp-4.1.2-14.el5
• sysstat-7.0.0-3.el5
• compat-libstdc++-296-2.96-138
• compat-libstdc++-33-3.2.3-61
Additionally, the following RPMs are required for an 11gR1 Database (which is bundled with the 12.1.1 release) on the database tier:
• elfutils-libelf-devel-0.125
• elfutils-libelf-devel-static-0.125
• libaio-devel-0.3.106
• unixODBC-2.2.11
• unixODBC-devel-2.2.11
• kernel-headers-2.6

Create folder for Oracle E-Business Suite R12.1.1 installation
Firstly check the mount point size of server.
As we can see i have free space, so i will create my stage folder and my installation folder on this mount point.
[root@test]# cd /data
[root@test data ]#mkdir TESTR12
Here we are doing Fresh Installation .Give 777 to installation folder.
[root@test]# chmod 777 /data/TESTR12
Create related folder for Application and Database tier(optional):
we want to put application tier installation folder together(also database tier installation folder) together we can create 2 subfolders under my main installation folder TESTR12.
[root@test]# cd /data/TESTR12
[root@test TESTR12]# mkdir apps
[root@test TESTR12]#mkdir db

We must give permisson to our user for can make installation of those folder
[root@testR12]# chown –R appltest:dba /data/TESTR12/apps
[root@testR12]# chown –R oratest:dba /data/TESTR12/db

C. Installation wizard steps:

D. After Installing of R12 Application:
Perform the following steps after installing or upgrading to Release 12.1.1 and before allowing users to access the system.
Relink Advanced Supply Chain Planning executables (for SLES 10 and OEL/RHEL 5.4 or higher only)
During the relink phase of the installation of EBS Release 12 (12.1.1) on OEL/RHEL 5.4 (failures will result while relinking the Advanced Supply Chain Planning (ASCP) executables. To fix this problem, users are required to replace the following line under the Linux section of the $AD_TOP/bin/adrelinknew.sh:
CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh'
with
CPP_LDFLAGS=' -L$(ORACLE_HOME)/lib -L$(ORACLE_HOME)/lib/stubs -lclntsh -Wl,--noinhibit-exec'
After making this change, users are then required to run the adadmin utility and relink application executables.

How to relink all binaries after a OS Upgrade to ensure that everything is working as expected on a Release 11i or a Release 12 Installation on Unix ?

To get everything relinked and to ensure that the adadmin Utility was relinked before it is used, follow these steps :

Release 11i (Autoconfig enabled) :

Technology Stack Binaries inclusive Database :

Source the RDBMS ORACLE_HOME Environment
Execute the script $ORACLE_HOME/appsutil/install/adlnkoh.sh
Source the iAS ORACLE_HOME Envrionment
Execute the script $ORACLE_HOME/bin/adlnkiAS.sh
Source the APPS Environment
Execute the script $ORACLE_HOME/bin/adlnk806.sh

Oracle E-Business-Suite Binaries :
1. Source the APPS Environment

2. Execute following command :
adrelink.sh force=y "ad adadmin"
If you are using the command
adrelink.sh force=y "ad all"you are relinking all binaries under the $AD_TOP/bin - otherwise the AD Utilities are relinked via the adadmin Utility.

3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink

Before starting the Instance, please open a new shell and source the Environment.
Release 12 :

Technology Stack Binaries inclusive Database :

Source the RDBMS ORACLE_HOME Environment
Execute the script $ORACLE_HOME/appsutil/clone/adlnkoh.sh
Source the APPS Envrionment
Execute the script $IAS_ORACLE_HOME/appsutil/clone/adlnkweboh.sh
Source the APPS Environment
Execute the script $ORACLE_HOME/appsutil/clone/adlnktools.sh

Oracle E-Business-Suite Binaries :

1. Source the APPS Environment

2. Execute following command
adrelink.sh force=y "ad adadmin"

If you are using the command
adrelink.sh force=y "ad all"

you are relinking all binaries under the $AD_TOP/bin - otherwise the AD Utilities are relinked via the adadmin Utility.

3. Execute adadmin
select --> '2. Maintain Applications Files menu'
select --> '1. Relink Applications programs'
--> select all modules to relink

Before starting the Instance, please open a new shell and source the Environment.

Relinking Oracle Application Server 10g R2 (10.1.2) and 10g R3 (10.1.3) on UNIX

All Oracle Application Server 10g processes from the Oracle Home to be relinked need to be stopped using normal
administrative steps prior to beginning the relink.
Setup your environment correctly. Since both OracleAS 10g R2 and R3 are based on Oracle 10.1 database software,
pick the environment variable settings that apply to this version.

Relink Oracle Application Server 10g R2 (10.1.2/10.1.4.0.1)

Relinking the Infrastructure (Identity Management (IM) + Metadata Repository (MR), or MR Only)

1. Relink Database software
   $ORACLE_HOME/bin/relink all
   cd $ORACLE_HOME/ldap/lib (Not required for MR Only)
   make -f ins_ldap.mk install
   make -f ins_ldap.mk hragentinstall
2. Relink Application Server Control
   cd $ORACLE_HOME/sysman/lib
   make -f ins_sysman.mk agent
3. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists.

This is needed to correctly set the permissions on the OID, and Application Server Control executables.
Relinking the Infrastructure (IM Only)

1. Relink Database software
   cd $ORACLE_HOME/network/lib
   make -f ins_net_client.mk client_sharedlib
   make -f ins_net_client.mk install
   cd $ORACLE_HOME/rdbms/lib
   make -f ins_rdbms.mk utilities
   cd $ORACLE_HOME/plsql/lib
   make -f ins_plsql.mk install
   cd $ORACLE_HOME/sqlplus/lib
   make -f ins_sqlplus.mk install
   cd $ORACLE_HOME/ldap/lib
   make -f ins_ldap.mk install
   make -f ins_ldap.mk hragentinstall
2. Relink Application Server Control
   cd $ORACLE_HOME/sysman/lib
   make -f ins_sysman.mk agent
3. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists.

This is needed to correctly set the permissions on the OID, and Application Server Control executables.
Relinking the Middle Tier

1. Relink Database software:
   cd $ORACLE_HOME/network/lib
   make -f ins_net_client.mk client_sharedlib
   make -f ins_net_client.mk install
   make -f ins_net_server.mk install (Business Intelligence & Forms only)
   cd $ORACLE_HOME/rdbms/lib (Not required for J2EE & Web Cache)
   make -f ins_rdbms.mk iexp
   make -f ins_rdbms.mk iimp
   make -f ins_rdbms.mk iloadpsp
   cd $ORACLE_HOME/plsql/lib (Not required for J2EE & Web Cache)
   make -f ins_plsql.mk install
   cd $ORACLE_HOME/sqlplus/lib
   make -f ins_sqlplus.mk install
   cd $ORACLE_HOME/ldap/lib (Not required for J2EE & Web Cache)
   make -f ins_ldap.mk toolsinstall
2. Relink Application Server Control
   cd $ORACLE_HOME/sysman/lib
   make -f ins_sysman.mk agent
3. Relink Web Cache
   cd $ORACLE_HOME/webcache/lib
   make -f ins_calypso.mk install
4. Relink Forms (Business Intelligence & Forms only)
   cd $ORACLE_HOME/forms/lib
   make -f ins_forms.mk frmweb_install
   make -f ins_forms.mk frmcmp_install
   make -f ins_forms.mk frmcmpb_install
5. Relink Reports (Business Intelligence & Forms only)
   cd $ORACLE_HOME/reports/lib
   make -f ins_reports.mk install
6. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists.

This is needed to correctly set the permissions on the Application Server Control executables.

If permissions were previously modified for Web Cache, you will need to re-run any scripts (or apply manual changes)
to obtain any original functionality. For more information about webcache_setuser.sh,
Please refer to the following documentation:
Oracle Application Server Web Cache Administrator's Guide 10g (10.1.2)
http://download.oracle.com/docs/cd/B14099_19/caching.1012/b14046/toc.htm
8 Setup and Configuration
Running webcached with Root Privilege

Relink Oracle Application Server 10g R3 (10.1.3)

Relinking the Middle Tier

1. Relink Database software:
   cd $ORACLE_HOME/network/lib
   make -f ins_net_client.mk client_sharedlib
   make -f ins_net_client.mk install
   cd $ORACLE_HOME/sqlplus/lib
   make -f ins_sqlplus.mk install

2. As root execute $ORACLE_HOME/root.sh, and $ORACLE_HOME/root.sh.old if it exists. This is needed to correctly
set the permissions on the newly relinked executables.

How to Enable Automatic Compilation of JSP pages in R12

1. Login into E-Business suite and select System Administrator responsibility

2. Select function AutoConfig (under Oracle Applications Manager)
For each web tier server perform the following:
Click on pencil icon under Edit Parameters
Select tab System
Expand section jtff_server

3. Change value for the entry s_jsp_main_mode from justrun to recompile
Confirm the change by clicking Save button


4. Run AutoConfig to propagate the changes to the configuration files
Verify that the $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml  has the following:

Check param-name "main_mode" under init-param variables
Its changed into "recompile"

5. Restart the web tier services

Output Post Processor

Concurrent processing uses the Output Post Processor (OPP) to enforce post-processing
actions for concurrent requests.

Post-processing actions are actions taken on concurrent
request output. An example of a post-processing action is that used in publishing
concurrent requests with XML Publisher.

For example, say a request is submitted with
an XML Publisher template specified as a layout for the concurrent request output.
After the concurrent manager finishes running the concurrent program, it will contact
the OPP to apply the XML Publisher template and create the final output.


A concurrent manager contacts an available OPP process when a running concurrent
request needs an OPP processing action. A concurrent manager uses a local OPP
process (that, is, on the same node) by default, but will choose a remote OPP if no local
OPP process is available.

There should always be at least one OPP process active in the system. If no OPP service
is available, completed requests that require OPP processing will complete with a status
of Warning.

An OPP service is multi-threaded and will start a new thread for each concurrent
request it processes. You can control the number of simultaneous threads for an OPP
service instance by adjusting the Threads per Process parameter for the instance.

If all the OPP services have reached their respective maximum number of threads, the
requests waiting to be processed remain in a queue to be processed as soon as threads
become available.

If request throughput has become slow, you may want to increase the
number of threads per process for the OPP. It is recommended that you keep the
number of threads per process between 1 and 20.

R12: adgentns.pl fails with AC-50480: Internal error occurred: java.lang.Exception: Error while generating

R12: adgentns.pl fails with AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora [ID 453901.1]

Applies to:

Oracle Applications Manager - Version: 12.0.2
Information in this document applies to any platform.
Checked for relevance on 02-FEB-2010

Symptoms

When attempting to run autoconfig, the following error occurs :
...
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using
templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated

adgentns.pl exiting with status 512
ERRORCODE = 512 ERRORCODE_END
...

Cause

Same issue is addressed in non-public Bug 5592152 -  'ARE120.13:OAM:APPS LISTENER FAILS TO COME UP AFTER R12 INSTALL'.

Issue seems to be occurring due to wrong node_name values in fnd_concurrent_queues table.

Solution

To implement the solution, please execute the following steps :

1. Open a new shell and source the APPS Environment.

2. Start the sqlplus Utility and execute following commands :

sqlplus apps/<Password>
exec fnd_conc_clone.setup_clean

3. Open a new shell and source the DB-Tier Environment.

4. Execute Autoconfig at the DB-Tier.

5. Switch to the Shell with the APPS Enviornment sourced or start a new Shell and source the APPS Environment.

6. Execute Autoconfig at the APPS-Tier.

7. Retest the Services startup.

HOW TO Trace a concurrent program

1. Go to System Administrator >> Concurrent >> Program >> Define
2. Query back the concurrent program that you want to trace.
3. Click ‘Enable Trace’ and save it.
4. Run the concurrent program as a concurrent request
5. A SQL trace file will be saved in the ../udump directory on the database server

(OR)

How do you enable/disable a trace to a Concurrent Program?
1. Connect to Oracle Applications
2. Navigate to System Administrator->Concurrent->Program->Define
3. Query the concurrent program on which you want to enable trace.
4. Check the enable trace check box bottom of the screen, Save it.
5. Ask the developer to submit the request, Once the request got submitted and completed normal.
6. Get the spid as select oracle_process_id from apps.fnd_concurrent_requests where request_id=456624.
7. You will get a spid like 12340.
8. Goto Udump and ls -ltr *12344*.
9 . You will get trace file.
Query to Get the Udump Location:

SQL>SELECT name,value from v$parameter WHERE name like ‘user_dump_dest’;
NAME
——————————————————————————–
VALUE
——————————————————————————–
user_dump_dest
/u2190/app/oracle/product/11.1.0/db_1/admin/orq11_qmnalx1109/diag/rdbms/orq1/orq
11/trace

Profile Options for Oracle Apps DBA

Here is the list of few profile options which Apps DBA use frequently. It is not necessary that you as Apps DBA must know all profile options, it depends on your implemnetation. I am goingto update more about Profile Options.
Applications Help Web Agent  Applications Servlet Agent  Applications Web Agent  Concurrent: Active Request Limit  Concurrent: Hold Requests  Concurrent: Multiple Time Zones  Concurrent: Report Access Level  Concurrent: Report Copies  Concurrent: Request priority  Database Instance  Enable Security Group FND: Debug Log Filename  FND: Debug Log Level  Forms Runtime Parameters  Gateway User ID ICX: Discoverer Launcher ICX: Forms Launcher ICX: Report Launcher ICX: Limit Connect ICX: Limit time ICX: Session Timeout MO Operating Unit Node Trust Level RRA: Delete Temporary Files RRA: Enabled RRA: Service Prefix RRA: Maximum Transfer Size Self Service Personal Home Page Mode Sign-On: Audit Level Signon Password Failure Limit Signon Password Hard to Guess Signon Password Length Signon Password No Reuse Site Name Socket Listener Port TCF: Host TCF: Port TWO TASK Viewer: Text Applications Help Web Agent

Service Management

R12 uses 10.1.3 fusion middle-ware AS stack, hence services in R12 is managed by OPMN (Oracle Process Manager and Notification Server).
OPMN consists of two main components (Oracle Process Manager) and (Oracle Notification Server).
Oracle Process Manager is responsible for
1) starting
2) stopping
3) restarting
4) monitoring the services it manages (this includes death detection and automatic restart of the process)
Oracle Notification Server is the transport mechanism for failure, recovery, startup, and other related notifications between components in AS.
Single configuration file(opmn.xml) is used OPMN to manage the services. Config file location is given as $ORA_CONFIG_HOME/10.1.3/opmn/conf/opmn.xml
Services managed by opmn are (grep process-type opmn.xml)
1) HTTP_Server
2) oacore
3) forms
4) oafm
Getting OPMN managed running process(es) status
bash-2.05$ ./adopmnctl.sh status
You are running adopmnctl.sh version 120.4
Checking status of OPMN managed processes…
Processes in Instance: SID_host.host.domain.com
———————+——————–+———–+———
ias-component | process-type | pid | status
———————+——————–+———–+———
OC4J | oafm | 13500 | Alive
OC4J | forms | 28358 | Alive
OC4J | oacore | 15899 | Alive
HTTP_Server | HTTP_Server | 23530 | Alive
ASG | ASG | N/A | Down
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /ebiz/oracle/SID/inst/apps/SID_host/logs/appl/admin/log/adopmnctl.txt for more information
Starting OPMN managed Services
Starting Complete OC4J container
bash-2.05$ ./adopmnctl.sh startproc ias-component=OC4J
Starting individual process-type(s)
bash-2.05$ ./adopmnctl.sh startproc ias-component=OC4J process-type= oafm
bash-2.05$ ./adopmnctl.sh startproc ias-component=OC4J process-type= forms
bash-2.05$ ./adopmnctl.sh startproc ias-component=OC4J process-type= oacore
Starting HTTP Server (Apache)
bash-2.05$ ./adopmnctl.sh startproc ias-component=HTTP_Server
Stoping OPMN managed Services
Stoping Complete OC4J container
bash-2.05$ ./adopmnctl.sh stopproc ias-component=OC4J
Stoping individual process-type(s)
bash-2.05$ ./adopmnctl.sh stopproc ias-component=OC4J process-type= oafm
bash-2.05$ ./adopmnctl.sh stopproc ias-component=OC4J process-type= forms
bash-2.05$ ./adopmnctl.sh stopproc ias-component=OC4J process-type= oacore
Stoping HTTP Server (Apache)
bash-2.05$ ./adopmnctl.sh stopproc ias-component=HTTP_Server
Benefits of OPMN
There are many benefits because of OPMN. To give an example,
Consider the scenario where one of your OC4J process has died. OPMN detects the death of the process which it manages and brings up in almost no time.
To elucidate this here is an example
bash-2.05$ ./adopmnctl.sh status
You are running adopmnctl.sh version 120.4
Checking status of OPMN managed processes…
Processes in Instance:  SID_host.host.domain.com
———————+——————–+———–+———
ias-component | process-type | pid | status
———————+——————–+———–+———
OC4J | oafm | 13500 | Alive
OC4J | forms | 15898 | Alive
OC4J | oacore | 15899 | Alive
HTTP_Server | HTTP_Server | 23530 | Alive
ASG | ASG | N/A | Down
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /ebiz/oracle/SID/inst/apps/SID_host/logs/appl/admin/log/adopmnctl.txt for more information …
All the OPMN managed processes are alive. Lets see, how opmn reacts to the death of the oacore OC4J process. I have killed the process with PID – 15898
bash-2.05$ kill -9 15898
bash-2.05$ ./adopmnctl.sh status
You are running adopmnctl.sh version 120.4
Checking status of OPMN managed processes…
Processes in Instance:  SID_host.host.domain.com
———————+——————–+———–+———
ias-component | process-type | pid | status
———————+——————–+———–+———
OC4J | oafm | 13500 | Alive
OC4J | forms | 28358 | Init
OC4J | oacore | 15899 | Alive
HTTP_Server | HTTP_Server | 23530 | Alive
ASG | ASG | N/A | Down
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /ebiz/oracle/SID/inst/apps/SID_host/logs/appl/admin/log/adopmnctl.txt for more information …
Within no time, OPMN detects the death and restarts the process it manages.(one or two seconds users will face the failures due to the process unavailability to serve the requests)
$LOG_HOME/ora/10.1.3/opmn/opmn.log is appened with the information ( it dumps the death detection and restart information)
08/05/02 12:34:04 [pm-process] Process Crashed: OC4J~forms~default_group~1 (691565399:15898) – Restarting
08/05/02 12:34:04 [pm-process] Starting Process: OC4J~forms~default_group~1 (691565400:0)
08/05/02 12:34:24 [pm-process] Process Alive: OC4J~forms~default_group~1 (691565400:28358)
This is one of greatest advantage you will get when you have R12, Even if oacore JVM crashes due to out of memory issue, opmn restarts after it detects OC4J has died.
What algorithm OPMN uses to detect the death?
1) OS process is checked by OPMN for every 2 seconds.
2) forward ping: periodically OPMN pings the process for every 20 seconds and expects response
3) reverse ping: every 20 seconds managed process sends OPMN a ping notification
Script Details
All the admin scripts are running the opmnctl in the background from the $ORACLE_CONFIG_HOME/opmn/bin directory. This environment variable is set in the $INST_TOP/apps/VIS_appl_node1/ora/10.1.3/VIS_appl_node1.env file. This contains all the required environment variables needed by opmnctl to run. So this file needs to be sourced before running the opmnctl for Ebiz.
bash-2.05# pwd
/ebiz/oracle/SID/inst/apps/SID_host/ora/10.1.3
bash-2.05# ls
Apache cfgtoollogs deconfig javacache opmn
VIS_appl_node1.env config j2ee network
bash-2.05# opmn/bin/opmnctl status
Unable to connect to opmn.
Opmn may not be up.
bash-2.05# source VIS_appl_node1.env
bash-2.05# opmn/bin/opmnctl status
Processes in Instance:  SID_host.host.domain.com
——————-+——————–+———+———
ias-component | process-type | pid | status
——————-+——————–+———+———
OC4J | oafm | N/A | Down
OC4J | forms | 8071 | Alive
OC4J | oacore | N/A | Down
HTTP_Server | HTTP_Server | 16572 | Alive
ASG | ASG | N/A | Down

Function not available for this responsibility

Error Faced:
Function is not available for this responsibility.Contact your system
Administrator or change the responsibility
Reason:
When a custom module is defined and forms are being run from this custom_top,
Forms dont run from custom top in servlet mode but run in socket mode….this is because in servlet mode the environment is read from the file
$ORA_CONFIG_HOME/10.1.2/forms/server/default.env
The location of the FMX files is determined by the value set for CUSTOM_TOP. If
this location is not found the error as seen is raised.
To get the custom_top defined in default.env the following steps are to be followed:

Solution:
1.Submit the “Compile Security” Concurrent program with the parameter everything set to Yes to verify that all the menu functions are compiled properly.
2.Create your-own AutoConfig template file
2.1 Define a product_top
Use the Oracle Applications Manager Context Editor to add your custom product_top to the context file.
This will add the CUSTOM_TOP to the .xml file, so it picked up when the default.env is created by Autoconfig
Or if you are well acquainted with Auto-config and Context_name.xml, add the Custom top manually to Context_name.xml using any editor and save.
3.Run AutoConfig
4.Restart apps services
5.Retest.

INTERMITTENT ORA-06502 DURING PEAK LOADING

In 10.2.0.3,
non reproducible ORA-06502 arises from
time to time when the production has the peak loading.

Alter system flush shared pool helps the problem for a while....

Error:

ORA-06502:
numeric or value error: associative array shape is not consistent with
session parameters
(PLS-01910)
numeric or value error: character to number conversion error (PLS-01900)
Solution:
Apply patch 5890966 to resolve the error using opatch utility.

Upgrade R12 Database server

How to upgrade R12 Database server from 10.2.0 to 11.1.0 (10g to 11g) manually ?

We cannot upgrade the existing Oracle Home, since 11g is not a patchset. We have to install 11g oracle home
as a separate ORACLE_HOME in parallel to 10g Oracle Home.
We have to install the new database for upgrading the existing database.
But before installing check the software versions for…..
a)Oracle database version and check the appropriate upgrade path accordingly depending on the present version.
b)E-business suite version—minimum is 12.0.4 and apply interoperability patch for 11g on apps node using adpatch
c)Autoconfig has to be upgraded to latest version by applying 7207440.
I did not need to apply as my instance is on 12.0.6 and 6728000(12.0.6 upgrade patch) supersedes this autoconfig patch..
Now steps for database installation
1.Apply patch 6400501 to Apps 10.1.0.5 Oracle-home
2. Deregister the current database if you want to change database sid,host,port.
And update autoconfig for database also before that.
$ perl $ORACLE_HOME/appsutil/bin/adgentns.pl appspass=apps
contextfile=$CONTEXT_FILE -removeserver
3. Update applications context file with new database parameters….
s_dbhost
s_dbdomain
s_db_serv_sid
s_dbport
s_apps_jdbc_connect_descriptor
to new database values. Don’t run auto config on apps tier now….
otherwise apps will not be able to connect to database at all.
Run autoconfig only after complete upgrade process is finished..
Database Installation
The 11.1.0 Oracle home must be installed on the database server node
in a different directory other than the current Oracle home
Log in to the database server node as the owner
of the Oracle RDBMS file system and database instance. Ensure
that environment settings, such as ORACLE_HOME, are set for the new
Oracle home you are about to create, and not for any existing
Oracle homes on the database server node
Choose to install software only (without any default database) in the new location.
After the installation,
Run utlu111i.sql (located in 11g_ORACLE_HOME/rdbms/admin) on source 10g database
and check the output.
SQL>@/11g_oracle_home/rdbms/admin/utlu111i.sql
It displays warnings and recommends steps to clear the issues.
Time Zone Issue
select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 3
If time zone file version is less than 4 then apply time zone patch 5632264
This can be done using opatch.
unzip the patch and run opatch going into the unzipped patch directory.
or manually copy the .dat files under 5632264/files/oracore/zoneinfo into
$ORACLE_HOME/oracore/zoneinfo
Bounce the database and check the TIMEZONE version.
Do not forget to take a backup of old zoneinfo directory before this.
Re-run utlu111i.sql after patching the database to record
the new timezone file version.
This time it says something like Database contains stale optimizer statistics.
So..
Gather statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’SYS’);
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(’SYSMAN’);
And other schemas which ever it recommends for.
Run the pre-upgrade utility once again to make sure,
you don’t get any warnings.
Copy initialization file (pfile) from source (10g) to target (11g)
Modify initialization parameters
1.
background_dump_dest replaced by diagnostic_dest
user_dump_dest replaced by diagnostic_dest
core_dump_dest replaced by diagnostic_dest
comment above three deprecated parameters
and add
*.diagnostic_dest=’/11g_base’
2.
Change *.compatible=’10.2.0′
to *.compatible=’11.1.0′
Set Environment to new Oracle home
export ORACLE_HOME=/oraDB/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=TEST
export TNS_ADMIN=$ORACLE_HOME/network/admin/Context_Name
sqlplus ‘/as sysdba’
Startup the database in upgrade mode.
SQL> startup upgrade
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
This takes considerably a long time depending on the size of database.
Once the upgrade finishes,It will shut down the database automatically.
Login again as sysdba and startup in normal mode.
You will get NLS errors as nls directory under new oracle_home does not have 9idata directory
Run the $ORACLE_HOME/nls/data/old/cr9idata.pl script to create the $ORACLE_HOME/nls/data/9idata directory.
I , actually copied the 9idata directory from 10g oracle_home to new location.
After creating the directory,
make sure that the ORA_NLS10 environment variable is set to the full path of the 9idata directory whenever you enable the 11g Oracle home.
SQL>startup
Check the dba_registry for the components and its status
SQL> select comp_name,version, status from dba_registry;
Run Post-Upgrade Status Tool provides a summary of the upgrade
SQL>@?/rdbms/admin/utlu111s.sql
Perform upgrade actions that do not require the database to be in UPGRADE mode
SQL>@?/rdbms/admin/catuppst.sql
Check for invalid objects
SQL> select object_name, owner, object_type from all_objects where status= ‘INVALID’;
Compile Invalids
SQL> @?/rdbms/admin/utlrp.sql
Copy tnsnames.ora, listener.ora, sqlnet.ora and include file from source oracle_home to target oracle_home
This finishes upgrade of 10.2.0.3 database to 11.1.0.6 in E-Business Suite R12.
Further we have to upgrade the existing 11.1.0.6 to 11.1.0.7 using a patchset.
which I will discuss in my next post..
After Upgrade
Start the new database listener
Set the TNS_ADMIN environment variable to the directory where you created your listener.ora and tnsnames.ora files.
Run adgrants.sql
Copy $APPL_TOP/admin/adgrants.sql (adgrants_nt.sql for Windows) from the administration server node to the database server node.
$ sqlplus ‘/ as sysdba’
SQL> @adgrants.sql APPLSYS
Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch/115/sql/adctxprv.sql from
the administration server node to the database server node.
$ sqlplus apps/apps
SQL>@adctxprv.sql SYSTEM_password CTXSYS
Implement and run AutoConfig in the new database Oracle Home.
How to implement autoconfig in a new oracle_home …coming up…..

Html or Self-service page not coming up

This happened on a Test instance.

A user today came up with this error .
On examining the exception details which looks something like this.
Exception Details.
oracle.apps.fnd.framework.OAException: Could not load application module ‘oracle.apps.pos.supplier.server.SuppSummAM’. at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:279) at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:78) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1283) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:536) at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:424) at _OA._jspService(_OA.java:212) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:335) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:478) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:401) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:702) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:359) at com.evermind.server.http.ServletRequestDispatcher.unprivileged_forward(ServletRequestDispatcher.java:252) at com.evermind.server.http.ServletRequestDispatcher.access$100(ServletRequestDispatcher.java:42) at com.evermind.server.http.ServletRequestDispatcher$2.oc4jRun(ServletRequestDispatcher.java:186) at oracle.oc4j.security.OC4JSecurity.doPrivileged(OC4JSecurity.java:283) at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequestDispatcher.java:191) at com.evermind.server.http.EvermindPageContext.forward(EvermindPageContext.java:322) at _RF._jspService(_RF.java:225) at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:59) at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:335) at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:478) at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:401) at javax.servlet.http.HttpServlet.service(HttpServlet.java:856) at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64) at oracle.apps.jtf.base.session.ReleaseResFilter.doFilter(ReleaseResFilter.java:26) at com.evermind.server.http.EvermindFilterChain.doFilter(EvermindFilterChain.java:15) at oracle.apps.fnd.security.AppsServletFilter.doFilter(AppsServletFilter.java:318) at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:610) at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:359) at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:870) at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:451) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:299) at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:187) at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260) at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:230) at oracle.oc4j.network.ServerSocketAcceptHandler.access$800(ServerSocketAcceptHandler.java:33) at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:831) at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303) at java.lang.Thread.run(Thread.java:595) ## Detail 0 ## JBO-30003: The application pool (parasaktidev.sinewloresoft.comDEVPCIL1527oracle.apps.pos.supplier.server.SuppSummAM) failed to checkout an application module due to the following exception: oracle.jbo.JboException: JBO-29000: Unexpected exception caught: oracle.apps.fnd.framework.OAException, msg=Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.sql.SQLException: ORA-20001: SQL_PLSQL_ERROR: N, ROUTINE, MO_GLOBAL.INIT, N, ERRNO, -20001, N, REASON, ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option. ORA-06512: at “APPS.FND_MESSAGE”, line 509 ORA-06512: at “APPS.MO_GLOBAL”, line 36 ORA-06512: at “APPS.MO_GLOBAL”, line 757 ORA-06512: at “APPS.MO_GLOBAL”, line 700 ORA-06512: at line 1
The highlighted part shows that the Profile options MO:Security Profile and MO:Operating Unit
need to be set correctly.
These profile options are being changed by some other user while testing.
On reverting to the relevant and appropriate values, everything went fine.

File System Interview Questions (FAQs)

1)      Which script is used to start/stop Concurrent Managers? What is the syntax? And where is the script located in R12?
2)      Where are the startup scripts log files located?
3)      What is the meaning of status 0, status 1, status 150 when starting services?
4)      Which is the default Oracle Home set in the apps R12 Environment?
5)      How do we set Default Oracle home to the home used by Apache, if required?
6)      Which Home is used for forms and reports in R12?
7)      Which Home is used for Apache in R12?
8)      What is the default Database version that comes with 12.0.4 Apps installation and 12.1.1 installation?
9)      How many products are brought in by default installation of Apps?
10)   Can a multi-user installation be done with a single mount point in linux?(yes or no)
11)   Why do we need to edit limits.conf under /etc/security for installation of Apps?
12)   What is an Instance Top used for? What are the practical advantages of instance top?
13)   A Shared file system has all directories mounted onto various nodes. Then do we need to have a separate instance top for all nodes? If so why ?
14)   What are the various directories under each Product_Top?
15)   What is difference of Common top in R12 and 11i?
16)   What is AF_JLIB parameter in R12 and what is it set to?
17)   Is FND_TOP/secure and FND_SECURE same in R12?
18)   What is the difference between httpd.pid and httpd.lock files?
19)   What are the runtime executables for forms and reports in R12?
20)   What is the plugin required to run forms in R12?
21)   What is OPMN and what are the processes it manages?
22)   What is the script used for managing OC4J processes?
23)   Where are the opmn log files located?
24)   What is the advantage of Jserv getting replaced by OC4J in R12?
25)   How many instances of OC4J exist in R12 and what are they? And how are they managed?
26)   What is the configuration file for OC4J and its location?
27)   What are the various configuration files and their location for Apache?
28)   What is the formservlet.ini file replaced by in R12?
29)   What are the configuration files for Forms in R12?
30)   What is the difference between APPL_CONFIG_HOME, ORA_CONFIG_HOME and ORACLE_CONFIG_HOME in R12?
31)   Where are the Diagnostic Log files located for Apache and OC4J?
32)   What is the difference between fndenv.env and devenv.env?
33)   What is the use of webapps directory under COMMON_TOP?
34)   What is the use of META-INF and WEB-INF directories under OA_HTML?
35)   What is an odf file and what is it used for?
36)   What are the following executables used for?
a)      FNDSM
b)      FNDFS
37)   What are the following parameters used for in an Environment?
a)      FNDNAM
b)      GWYUID
c)       TWO_TASK
d)      APPLCSF
e)      APPLDCP
f)       APPLTMP
g)      NLS_LANG
h)      PATH
i)        APPL_TOP
j)        FND_TOP
k)      AU_TOP
l)        APPLFENV
m)    OA_JAVA
n)      LD_LIBRARY_PATH
o)      CLASSPATH
38)   What are the extensions for the following files
a) Source forms
b) Runtime forms
c) Message files
d) pl/sql libraries
e) Concurrent Manager Log files
f) Concurrent Request Log files
g) SSL certificates
h) Network Configuration files
i) Report files
j) Driver files
k) Environment files
l) Perl scripts, Shell Scripts, SQL scripts
m) Java Archives, Java Class files
39)   Where are the log files located for
a) Apache
b) Adpatch
c) OC4J
d) Autoconfig
e)  Concurrent Manager
40)   What is the use and location of files in R12?
a)      Context file
b)      Consolidated env file
c)       DBC file
d)      server.xml
e)      opmn.xml
f)       Apache pids
g)      appsweb.cfg
h)      default.env
i)        orion-web.xml
j)        orion-application.xml
k)      SSL certificates
l)        Adconfig.txt
m)    Topfile.txt
n)      Adovars.env
o)      Adjborg.txt and adjborg2.txt

oafm is not coming up after APPS & DB Tier servers bouncing

=== ODM Action Plan ===
1. When did you start facing this issue ? what are the recent changes made ?
Do the below action plan, and upload the required information
2. Shutdown all middle tier services ( Makes sure all process are down )
3. Clean all old log files
mv $LOG_HOME/ora/10.1.3 $LOG_HOME/ora/10.1.3_bkp
rm -fr $LOG_HOME/ora/10.1.3/Apache/*
rm -fr $LOG_HOME/ora/10.1.3/j2ee/forms/*
rm -fr $LOG_HOME/ora/10.1.3/j2ee/oacore/*
rm -fr $LOG_HOME/ora/10.1.3/j2ee/oafm/*
rm -fr $LOG_HOME/ora/10.1.3/javacache/*
rm -fr $LOG_HOME/ora/10.1.3/opmn/*
rm -fr /u0192/oracle/orp/inst/apps/orp_pmnalx1095/logs/appl/admin/log/adstrtal.log
rm -fr /u0192/oracle/orp/inst/apps/orp_pmnalx1095/logs/appl/admin/log/adoafmctl.txt
4. Start the services
// the following below are not required.//
5. Provide output for
sh adopmnctl.sh status
6. Upload the latest logfiles
zip -r log_latest.zip $LOG_HOME/ora/10.1.3/*
7. Upload the below files
adstrtal.log
adoafmctl.txt

(OR)

To implement the solution, please execute the following steps:
Clear the TLD cache:
- stop all middle tier services
- Delete/backup all the files under directory:
$COMMON_TOP/_TldCache
- start all middle tier services

How to reset or Unlock the orcladmin password

Recently while upgrading Discoverer to 10.1.2.3 CP4 , we got stuck at one of the step.
This step asks for orcladmin password which was not known to us.
So , we need to reset that password.Here are the steps which we followed ;
Error:
orcladmin password lost or account locked
Cause:
orcladmin password not known to us.
Solution :
1. Login as OAS user [ here testbi ] and
sqlplus “/as sysdba”
SQL>> alter user ODS identified by [new password]
2. Execute $ORACLE_HOME/bin/oidpasswd create_wallet=true
3. oidpasswd connect=[connect string] change_oiddb_pwd=true
This will prompt us for old password and new password.
old password you can give the same as ODS user and unique new password.
4. To unlock the orcladmin account :
oidpasswd connect=[connect string] unlock_su_acct=trueThis will prompt for password ; enter the ODS password.Which will inturn unlock your orcladmin account.
5. To reset the password :
oidpasswd connect=[connect string] reset_su_password=true
This will prompt for new password. [keep the password same as ODS user.] and your are done.
You have successfully changed the orcladmin password.
Now, to test the password :
cd $ORACLE_HOME/bin/oidadmin
The window will prompt for orcladmin password.
Refer : http://eldapo.blogspot.com/2007/08/forcing-orcladmin-password.html

Report Builder:Warning: REP-0004: Unable to open user preference file

Oracle E-Business Suite R12 Report Builder:Release 10.1.2.2.0 Warning: REP-0004: Unable to open user preference file.

Error:
REP-0004: Warning: Unable to open user preference file.
Report Builder: Release 10.1.2.2.0 – Production on Mon Nov 17 14:43:42 2008
Solution
Copy prefs.ora from $ORACLE_HOME/tools/admin to $HOME
cp $ORACLE_HOME/tools/admin/prefs.ora $HOME/prefs.ora

After IP Change Oracle APPS R12 Web Application Services Failed

oafm,forms,oacore status down
ERROR Message…
Oracle Apps stratup Log
Root Service Oracle Process Manager for VIS_sys38 adopmnctl.sh Started
Web Entry Point Services Oracle HTTP Server VIS_sys38 adapcctl.sh Failed
Web Application Services OACORE OC4J Instance VIS_sys38 adoacorectl.sh Failed
Web Application Services FORMS OC4J Instance VIS_sys38 adformsctl.sh Failed
Web Application Services OAFM OC4J Instance VIS_sys38 adoafmctl.sh Failed
Batch Processing Services OracleTNSListenerAPPS_VIS_sys38 adalnctl.sh Started
Batch Processing Services OracleConcMgrVIS_sys38 adcmctl.sh Started
Batch Processing Services Oracle ICSM VIS_sys38 ieoicsm.sh Disabled
Batch Processing Services Oracle Fulfillment Server VIS_sys38 jtffmctl.sh Started
Other Services Disabled
Other Services OracleFormsServer-Forms VIS_sys38 adformsrvctl.sh Disabled
Other Services Oracle Metrics Client VIS_sys38 adfmcctl.sh Disabled
Other Services Oracle Metrics Server VIS_sys38 adfmsctl.sh Disabled
Other Services Oracle MWA Service VIS_sys38 mwactlwrpr.sh Disabled
================================================================================
APPS R12 Web Application Services Logs..
11/10/08-19:18:43 :: adoacorectl.sh version 120.11.12000000.2
11/10/08-19:18:43 :: adoacorectl.sh: starting OPMN if it is not running
opmnctl: opmn is already running.
11/10/08-19:18:43 :: adoacorectl.sh: Starting OPMN managed OACORE OC4J instance
opmnctl: starting opmn managed processes…
================================================================================
opmn id=sys38.doyen.in:6200
0 of 1 processes started.
ias-instance id=VIS_sys38.sys38.doyen.in
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
——————————————————————————–
ias-component/process-type/process-set:
OC4J/oacore/default_group/
Error
–> Process (index=1,uid=1832654593,pid=29902)
failed to start a managed process after the maximum retry limit
Log:
/u02/oracle/VIS/inst/apps/VIS_sys38/logs/ora/10.1.3/opmn/OC4J~oacore~default_group~1
11/10/08-19:19:14 :: adoacorectl.sh: exiting with status 204
================================================================================
OPMN managed OACORE OC4J instance Logs..
[applmgr@sys38 ~]$ sh /u02/oracle/VIS/inst/apps/VIS_sys38/admin/scripts/adoacorectl.sh status
You are running adoacorectl.sh version 120.11.12000000.2
Checking status of OPMN managed OACORE OC4J instance …
Processes in Instance: VIS_sys38.sys38.doyen.in
——————-+——————–+———+———
ias-component process-type pid status
——————-+——————–+———+———
OC4J oafm N/A Down
OC4J forms N/A Down
OC4J oacore N/A Down
HTTP_Server HTTP_Server 29760 Alive
adoacorectl.sh: exiting with status 0
adoacorectl.sh: check the logfile /u02/oracle/VIS/inst/apps/VIS_sys38/logs/appl/admin/log/adoacorectl.txt for more information …
Solution
1.Stop Oracle Apps Tier (Stop Oracle Apps Servies)
2. cd $INST_TOP/ora/10.1.3/j2ee
rm -fr oacore/persistence/*
rm -fr oafm/persistence/*
rm -fr forms/persistence/*
3.Start Oracle Apps Tier (Start Oracle Apps Servies)
4.adoacorectl.sh status
[applmgr@sys38 persistence]$ sh /u02/oracle/VIS/inst/apps/VIS_sys38/admin/scripts/adoacorectl.sh status
You are running adoacorectl.sh version 120.11.12000000.2
Checking status of OPMN managed OACORE OC4J instance …
Processes in Instance: VIS_sys38.sys38.doyen.in
——————-+——————–+———+———
ias-component process-type pid status
——————-+——————–+———+———
OC4J oafm 31945 Alive
OC4J forms 31874 Alive
OC4J oacore 31780 Alive
HTTP_Server HTTP_Server 29760 Alive
adoacorectl.sh: exiting with status 0

How to trace a Concurrent Request and generate the TKPROF file

I hope this document will help you to find-out the long-running concurrent request from an oracle applications 11i/R12.

1. Generate Trace File

Enable Tracing For The Concurrent Manager  Program
Select the Enable Trace Checkbox
Note : Checking the Trace Check box on the Concurrent Program gives an Event 10046 Level 8 trace. So even if the trace is set for Binds and Waits on the Submission form once the concurrent program is encountered in the trace it will reset to level 8 so no binds will be present in the trace after that point.
Turn On Tracing
•Responsibility: System Administrator
•Navigate: Profiles > System
•Query Profile Option Concurrent: Allow Debugging
•Set profile to Yes
Run Concurrent Program With Tracing Turned On
•Logon to the Responsibility that runs the Concurrent Program
•In the Submit Request Screen click on Debug Options (B)
•Select the Checkbox for SQL Trace

If the Debug option is greyed out & not updateable set the profile Concurrent: Allow Debugging to Yes
2. Find Trace File Name
Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id
prompt
accept request prompt ‘Please enter the concurrent request id for the appropriate concurrent program:’
prompt
column traceid format a8
column tracename format a80
column user_concurrent_program_name format a40
column execname format a15
column enable_trace format a12
set lines 80
set pages 22
set head off
SELECT ‘Request id: ‘||request_id ,
‘Trace id: ‘||oracle_Process_id,
‘Trace Flag: ‘||req.enable_trace,
‘Trace Name:
‘||dest.value||’/'||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,
‘Prog. Name: ‘||prog.user_concurrent_program_name,
‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,
‘Status : ‘||decode(phase_code,’R',’Running’)
||’-'||decode(status_code,’R',’Normal’),
‘SID Serial: ‘||ses.sid||’,'|| ses.serial#,
‘Module : ‘||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where req.request_id = &request
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name=’user_dump_dest’
and dbnm.name=’db_name’
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;
To check the timeline of the request :
SELECT request_id, TO_CHAR( request_date, ‘DD-MON-YYYY HH24:MI:SS’ )
request_date, TO_CHAR( requested_start_date,’DD-MON-YYYY HH24:MI:SS’ )
requested_start_date, TO_CHAR( actual_start_date, ‘DD-MON-YYYY HH24:MI:SS’ )
actual_start_date, TO_CHAR( actual_completion_date, ‘DD-MON-YYYY HH24:MI:SS’ )
actual_completion_date, TO_CHAR( sysdate, ‘DD-MON-YYYY HH24:MI:SS’ )
current_date, ROUND( ( NVL( actual_completion_date, sysdate ) – actual_start_date ) * 24, 2 ) duration
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(‘&p_request_id’);
You may produce a complete report reference of the request ID using the Note:187504.1 bde_request.sql Process and Session info for one Concurrent Request(11.5)

3. TKPROF Trace File
Once you have obtained the Raw trace file you need to format the file using TKPROF.
$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no
Where:
raw_trace_file.trc: Name of trace file
output_file: tkprof out file
explain:  This option provides the explain plan for the sql statements
sort:  This provides the sort criteria in which all sql  statements will be sorted.  This will bring the bad sql at  the top of the outputfile.
sys=no:  Disables sql statements issued by user SYS
Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10″ long running queries
$ tkprof sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10

Adding a Middle Tier to an Existing R12 Instance

The following steps will help in adding a Middle Tier to an Existing R12 Instance.

Pre-Step : Configure the Node to be added with equivant Operating System and Kernel version as of the Primary Node.
Create the Users/Group as that was done to the Primary Node

Step 1: Make the File System of the Primary Node accessible on the Secondary Node
The following mount points must be present on the Secondary Node
COMMON_TOP
APPL_TOP
10.1.2 ORACLE HOME
10.1.3 ORACLE HOME

Step 2: Log on the Second Node and move to the COMMON_TOP/clone/bin
adclonectx.pl needs to be run with the option addnode with the context file as a parameter.
The context file will be of the first node.
Syntax : perl adclonectx.pl addNode contextfile=
This step will display the path of the new context file that got generated.
[aptest@node02 bin]$ perl adclonectx.pl addnode
contextfile=…./TEST_node01/appl/admin/TEST_node01.xml
….//apps/apps_st/comn/clone/bin
Classpath: …./apps/apps_st/comn/clone/bin/
/home1/aptest/test/apps/apps_st/comn/clone/bin/../jre/bin/java
-Xmx600M -classpath /home1/aptest/test/apps/apps_st/comn…….
oracle.apps.ad.context.CloneContext -e
…./TEST_node01/appl/admin/TEST_node01.xml -addnode
Running command:
…./apps_st/comn/clone/bin/../jre/bin/java
-Xmx600M -classpath /home1/aptest/test/apps/apps_st/comn/clone/………….
oracle.apps.ad.context.CloneContext -e
…./TEST_node01/appl/admin/TEST_node01.xml -addnode
Enter the APPS password : apps
Log file located at …./apps_st/comn/clone/bin/CloneContext_02061014.log
Provide the values required for creation of the new APPL_TOP Context file.
Target hostname (virtual or normal) [node02]:
It is recommended that your inputs are validated by the program.
However you might choose not to validate your inputs under following circumstances:
-If cloning a context on source system for a remote system.
-If cloning a context on a machine where the ports are taken and you do not want to shutdown the services at this point.
-If cloning a context but the database it needs to connect is not available.
Do you want the inputs to be validated (y/n) [n] ?:
Target system service groups – enable Root Service Group [enabled] [enabled]:
Target system service groups – enable Web Entry Point Services [enabled] [enabled]:
Target system service groups – enable Web Application Services [enabled] [enabled]:
Target system service groups – enable Batch Processing Services [enabled] [disabled]:
Target system service groups – enable Other Service Group [disabled] [disabled]:
Do you want to preserve the Display set to inchmgmt101:0.0 (y/n) [y] ?:
Database port is 1526
Do you want the the target system to have the same port values as the source system (y/n) [y] ?:
Complete port information available at
…./apps_st/comn/clone/bin/out/TEST_node02/portpool.lst
Target system proxy hostname:www-proxy.us.test.com
Target system proxy port:80
UTL_FILE_DIR on database tier consists of the following directories.
1. /usr/tmp
2. /usr/tmp
3. /home1/ortest/test/db/tech_st/10.2.0/appsutil/outbound/TEST_node01
4. /usr/tmp
 
Choose a value which will be set as APPLPTMP value on the target node [1]:1
New context path and file name [..../apps/TEST_node02/appl/admin/TEST_node02.xml]:
Creating the new APPL_TOP Context file from : …./ad/12.0.0/admin/template/adxmlctx.tmp
The new APPL_TOP context file has been created : …./TEST_node02/appl/admin/TEST_node02.xml
Log file located at …./apps_st/comn/clone/bin/CloneContext_02061014.log
contextfile=…./apps/TEST_node02/appl/admin/TEST_node02.xml
Check logfile …./apps_st/comn/clone/bin/CloneContext_02061014.log for details.

[aptest@node02 bin]$
Note : Node 2 will act as a web node.
Step 3: On the Second Node move to the AD_TOP/bin directory
Run adconfig.sh with the context_file that got generated after Step2
[aptest@node02 admin]$ adconfig.sh
Enter the full path to the Context file: /home1/aptest/test/inst/apps/TEST_node02/appl/admin/TEST_node02.xml
Enter the APPS user password: AutoConfig is configuring the Applications environment…
AutoConfig will consider the custom templates if present.
Using CONFIG_HOME location : /home1/aptest/test/inst/apps/TEST_node02
Classpath : /home1/aptest/test/apps/apps_st/comn/java/lib/appsborg2.zip:
/home1/aptest/test/apps/apps_st/comn/java/classes
Using Context file : /home1/aptest/test/inst/apps/TEST_node02/appl/admin/TEST_node02.xml
Context Value Management will now update the Context file
Updating Context file…COMPLETED
Attempting upload of Context file and templates to database…COMPLETED
Configuring templates from all of the product tops…
Configuring AD_TOP……..COMPLETED
Configuring FND_TOP…….COMPLETED
Configuring ICX_TOP…….COMPLETED
Configuring IEO_TOP…….COMPLETED
Configuring BIS_TOP…….COMPLETED
Configuring AMS_TOP…….COMPLETED
Configuring CCT_TOP…….COMPLETED
Configuring WSH_TOP…….COMPLETED
Configuring CLN_TOP…….COMPLETED
Configuring OKE_TOP…….COMPLETED
Configuring OKL_TOP…….COMPLETED
Configuring OKS_TOP…….COMPLETED
Configuring CSF_TOP…….COMPLETED
Configuring IGS_TOP…….COMPLETED
Configuring IBY_TOP…….COMPLETED
Configuring JTF_TOP…….COMPLETED
Configuring MWA_TOP…….COMPLETED
Configuring CN_TOP……..COMPLETED
Configuring CSI_TOP…….COMPLETED
Configuring WIP_TOP…….COMPLETED
Configuring CSE_TOP…….COMPLETED
Configuring EAM_TOP…….COMPLETED
Configuring FTE_TOP…….COMPLETED
Configuring ONT_TOP…….COMPLETED
Configuring AR_TOP……..COMPLETED
Configuring AHL_TOP…….COMPLETED
Configuring OZF_TOP…….COMPLETED
Configuring IES_TOP…….COMPLETED
Configuring CSD_TOP…….COMPLETED
AutoConfig completed successfully.
The log file for this session is located at: /home1/aptest/test/inst/apps/TEST_node02/admin/log/02061035/adconfig.log
[aptest@node02 admin]$

Step 4: Perform a Health Check
Shutdown the Middle Tier Components on Node1 and Start the Services on Node2
The webentry on the context files would be pointing to Node 2 and simillarly all the Agent Profile Option would have been marked with Node 2.
Later Node 1 can be made as a primary node by running autoconfig.
The Setup will a complete one with a Load Balancer in place , since there are two web nodes added and have Web Entry Point Services enabled.

Must Read :
Note.384248.1 Ext/Pub Sharing The Application Tier File System in Oracle E-Business Suite Release 12