Search This Blog

Saturday, December 1, 2012

Value Chain Planning


Thursday, August 30, 2012

oafm, forms and oacore not starting up -- PRESISENCE

oafm, forms and oacore not starting up after IP adress change of the R12 and Oracle Applications Server

Error Message
06/20/08-19:12:59 :: version 120.6.12000000.2

06/20/08-19:12:59 :: starting OPMN if it is not running
opmnctl: opmn is already running.
06/20/08-19:12:59 :: Starting OPMN managed OAFM OC4J instance
opmnctl: starting opmn managed processes...
0 of 1 processes started.
--> Process (index=1,uid=1675454223,pid=29758)
failed to start a managed process after the maximum retry limit

06/20/08-19:13:29 :: exiting with status 204
06/20/08-19:36:14 :: version 120.4.12000000.3

06/20/08-19:36:14 :: Checking the status of Oracle Process Manager (OPMN)

Processes in Instance:
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 29416 Alive

The OC4J lock files contains the IP adress, after the ip adress change the lock files includes the wrong Ip adress which caused the OJ4J to fail

1 - open a new shell and set your apps environment


3 - stop

4 - check if it is really down: ps -ef grep grep opm

5 - delete the following:

rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*

6 - start

7 - check if the issue has been resolved: status

06/20/08-19:36:14 :: exiting with status 0
[oramgr@eit scripts]$ status
You are running version 120.4.12000000.3
Checking status of OPMN managed processes...

Processes in Instance:
ias-component process-type pid status
OC4J oafm N/A Init
OC4J forms N/A Alive
OC4J oacore N/A Alive
HTTP_Server HTTP_Server 5012 Alive exiting with status 0 check the logfile /u02/oracle/VIS/inst/apps/VIS_eit/logs/appl/admin/log/adopmnctl.txt for more information ...

Wednesday, August 8, 2012

Queries to Check Versions

Simple Queries to retrieve the Version Information

Oracle Applications Version

SQL> select RELEASE_NAME from fnd_product_groups;

 Oracle Database Version

  • SQL> select * from v$version;
  • SQL> select * from v$version where banner like ‘Oracle%’; 
  • Version information can also be checked from Installed Products from the Oracle Universal Installer. 
    It will tell you what products is installed on the machine and also its version information. 
    The installer is located at $ORACLE_HOME/bin/runInstaller  
  •  Connect to sqlplus as sysdba it will give you the database version 
Operating System 32/64 Bit?

  • In Solaris Systems

/usr/bin/isainfo -kv

  • In Linux Systems

uname -m

Tuesday, August 7, 2012

Important Tables for Conc Mgrs, FND, and AD/Patches

Below are some Important Tables for Concurrent Managers, FND,  and AD/Patches


Concurrent Manager






 AD / Patches



Profile Options in Oracle Apps

Below are some useful Profile Options for an Apps DBA

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
perating 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
Viewer: Text


Query to check Profile Options at all levels

Values of a profile option

  • It is sometimes hard to know where a profile option is set. A user might have a profile option set, an application, responsibility, and these might result in unexpected results. 

  • The following prompts for the profile name that a user sees. 
  • You could also query for the internal profile code instead by using the column a.profile_option_name instead.

b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'Unknown') "Level Value"
, c.PROFILE_OPTION_VALUE "Profile Value"
, c.profile_option_id "Profile ID"
, to_char(c.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI') "Updated Date"
, nvl(d.user_name,to_char(c.last_updated_by)) "Updated By"
apps.fnd_profile_options a
, apps.FND_USER d
, apps.FND_USER e
--a.application_id = nvl(401, a.application_id)
--and a.profile_option_name = nvl('INV', a.profile_option_name)
b.user_profile_option_name like '&ProfileName' -- 'AFLOG_ENABLED'
and a.profile_option_name = b.profile_option_name
and a.profile_option_id = c.profile_option_id
and a.application_id = c.application_id
and c.last_updated_by = d.user_id (+)
and c.level_value = e.user_id (+)
and c.level_value = g.responsibility_id (+)
and c.level_value = h.application_id (+)
order by
b.user_profile_option_name, c.level_id,


Changing Oracle Apps user password for Oracle E-Business Suite R12 Application.

We can follow the below steps  for changing or resetting Oracle Application user password:

Step 1: Login with 'sysadmin' user into Oracle E-Business Suite frontend page.

Step 2: In the Oracle Applications Home Page go to Navigator section, select the User Management responsibility

Step 3: Click the User Management Responsibility, under User Management, click Users

Step 4: In the Oracle User Management of page, we find lot of search options available for use, we can use % also to find the name of the Oracle Application user. If you now the employee id than you can directly enter here.

Let us say we want to change password for Employee id 'E0111' ,than I will enter 'EO111' in the place where User Name is present

Step 5: Search the user
After entering the User Name, click Go

We can see the details as specified below:
Last Name First Name Email User Name Status Create User Reset Password Update

Step 6: Reset the password
Click the Reset Password tab and Choose Enter Manually option of resetting the password. Now, we can enter the password and confirm the password and Click Submit.

Step 7: Verify and inform the User
After resetting or changing the password, we can verify  by logging from the Oracle E-Business Suite front end page.

Let us say we have reset the password of 'E0111' to welcome123,than we can login successfully and inform the user for the change.


Profile Options in Oracle Apps R12 - Overview (FND PROFILE)



  • Profiles are the changeable options which affect the way Oracle Applications run. 
  • The profile option acts like a Global Variable in Oracle, to provide the flexibility in Oracle Applications.



The profiles are of two types those are given below.
  1. System Profile and
  2. User Profile.

Different Levels a Profile value can be set:

The Profile values will be set in different levels as given below.
  1. Site
  2. Application
  3. Responsibility
  4. User
  5. Server
  6. Organization
  •  Site: This field displays the current value, if set, for all users at the installation site. 
  • Application: This field displays the current value, if set, for all users working under responsibilities owned by the application identified in the Find Profile Values block. 
  • Responsibility: This field displays the current value, if set, for all users working under the responsibility identified in the Find Profile Values block.  
  • User: This field displays the current value, if set, for the application user identified in the Find Profile Values block.  
  • Profile: Enter the name of the profile option whose values you wish to display.

Navigation to set Profile Values


You may search for profile options using character strings and the wildcard symbol (%). For example, to find all the profile options prefixed by "Concurrent:” you could enter "Conc%" and press the Find button.



To view personal profiles enter into the query mode and enter the profile name which we have already then we get profile value details.

To check the validation done against the Profile value while setting:





It is used to perform various actions related to profile values through PL/SQL. Some of the important ones are listed below

1. FND_PROFILE.GET(‘Name of the Profile’, variable name);
SELECT fnd_profile.value('PROFILEOPTION')

2. variable name := FND_PROFILE.VALUE(‘Name of the profile’);
3. FND_PROFILE.PUT(‘Name of the profile’, value of the profile);

   v_conc_login_id      NUMBER;
   FND_PROFILE.put ('CONC_LOGIN_ID',1425);
   fnd_profile.get ('CONC_LOGIN_ID', v_conc_login_id);
   DBMS_OUTPUT.put_line (v_conc_login_id);
PL/SQL procedure successfully completed

The 1st and 2nd are same but, the only difference is FND_PROFILE.GET is the procedure and FND_PROFILE.VALUE is the function so, it return a value.  

Apart from the above procedures we have another important one named FND_PROFILE.SAVE used to set the profile values from backend. A detail regarding this API is as below. 

Changing Profile Options from Backend

The table fnd_profile_options_tl, profile options names are kept.  Now find the corresponding the Profile option name for which you need to update from backend. For this example I took my favorite “ORG_ID”
SELECT profile_option_name
 FROM fnd_profile_options_tl
 WHERE user_profile_option_name LIKE 'MO%'

It returns more than one row but i can make out that "ORG_ID" is the PROFILE_OPTION_NAME for MO: Operating Unit. Now I need to know the Org_ID of the Org whose value is to be set in MO: Operating Unit. SO I use the simple select as below
SELECT organization_id, NAME
  FROM hr_all_organization_units;

From the organization name I find the one which will be the default Operating Unit, and I note the ID. In my case the ID for my default Operating Unit is 286. Now with the code below I set the profile option value using

   stat   BOOLEAN;
   stat := fnd_profile.SAVE ('ORG_ID', 286, 'SITE');
   IF stat
      DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');
      DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');
   END IF;

Sunday, August 5, 2012

Different Modes of Data guard Protection


  • When using an Oracle standby database for Business Continuity purposes there are 3 possible modes of operation for determining how the data is sent from the primary (the database currently being used to support the business queries) database to the standby (failover database to be used upon invocation of business continuity) database.
There are 2 synchronous modes and 1 asynchronous mode.

The various modes of operation of a standby are a real trade-off between data protection and impact on the primary systems.


Modes of Operation

The following table shows the options giving the benefits and drawbacks.

Mode of Operation
Maximum Protection
Never any data loss, no data ever accepted on primary
without being on standby.
Slower response times on primary. Network disruption
between primary and secondary causes downtime of primary database.
Maximum Availability
Updates must be on standby before accepted on primary,
network disruption means the primary continues running with updates sent to
standby when it is available again.
Slower response times on primary. Data loss possible if
network fails and then site fails.
Maximum Performance
No performance slowdown for applications on primary.
Data loss will happen

  • The customer is currently running in Maximum Performance mode, and thus the standby solution has minimal impact on the response times of the various applications.
  • There are 2 factors that would impact the application response if they moved to a higher level of data protection, the network latency and the network bandwidth. During normal operation, the response time of applications that make data changes would be impacted by the round trip time (rtt) to their standby
  • During periods of high volume activity, the bandwidth can become congested and this could lead to significant delay in response time.



  • If a higher level of data protection was deemed a business requirement it is advised that you have a significantly larger network bandwidth to the Business Continuity site.
  • During normal running we typically see a transport lag (of data between the sites) of around 10 seconds and this would be the typical amount of data we would lose in a disaster situation. During high peaks of activity this can increase.
  • In determining to increase the protection mode of the configuration there is a real trade off between how much you value your data (for this customer it is a reputation issue as much as a financial consideration) and the impact on your normal day-to-day performance.

Monday, July 30, 2012

Virtual IP (VIP) in Oracle Real Application Clusters (RAC)

Use of Virtual IP (VIP) in Oracle Real Application Clusters (RAC)

When installing Oracle 10g/11g R1 RAC, three network interfaces (IPs) are required for each node in the RAC cluster, they are:
  • Public Interface:  Used for normal network communications to the node
  • Private Interface:  Used as the cluster interconnect
  • Virtual (Public) Interface: Used for failover and RAC management
When installing Oracle 11g R2 RAC, we need one more network interface (IP) for each node in the RAC cluster.
SCAN Interface (IP):  Single Client Access Name (SCAN) is a new feature in Oracle Real Application Clusters (RAC) 11g Release 2, which provides a single name for all clients to access an Oracle Database running in a cluster. The benefit of SCAN is clients using SCAN do not need to change if you add or remove nodes in the cluster.

  • When a client connects to a tns-alias, it uses a TCP connection to an IP address, defined in the tnsnames.ora file. When using RAC, we define multiple addresses in our tns-alias, to be able to failover when an IP address, listener or instance is unavailable. TCP timeouts can differ from platform to platform or implementation to implementation. This makes it difficult to predict the failover time. 
Oracle 10g Cluster Ready Services enables databases to use a Virtual IP address to configure the listener ON. This feature is to assure that oracle clients quickly failover when a node fails. In Oracle Database 10g RAC, the use of a virtual IP address to mask the individual IPO addresses of the clustered nodes is required. The virtual IP addresses are used to simplify failover and are automatically managed by CRS.

  • To create a Virtual IP (VIP) address, the Virtual IP Configuration Assistant (VIPCA) is called from the script of a RAC install, which then configures the virtual IP addresses for each node specified during the installation process. In order to be able to run VIPCA, we need unused public IP addresses available for each node that has been configured in the /etc/hosts file.
  • One public IP address for each node to use for its Virtual IP address for client connections and for connection failover. This IP address is in addition to the operating system managed public host IP address that is already assigned to the node by the operating system. This public Virtual IP must be associated with the same interface name on every node that is a part of the cluster. The IP addresses that are used for all of the nodes that are part of a cluster must be from the same subnet. The host names for the VIP addresses must be registered with the domain name server (DNS). The Virtual IP address should not be in use at the time of the installation because this is a Virtual IP address that Oracle manages internally to the RAC processes. This virtual IP address does not require a separate NIC. The VIPs should be registered in the DNS. The VIP addresses must be on the same subnet as the public host network addresses. Each Virtual IP (VIP) configured requires an unused and resolvable IP address.
  • Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service (ONS) maintains communication between each nodes and listeners. Once ONS found any listener down or node down, it will notify another nodes and listeners. While new connection is trying to establish connection to failure node or listener, virtual IP of failure node automatically divert to surviving node and session will be establishing in another surviving node. This process doesn't wait for TCP/IP timeout event. Due to this, new connection gets faster session establishment to another surviving nodes/listener.
  • Virtual IP (VIP) is for fast connection establishment in failover dictation. Still we can use physical IP address in Oracle 10g in listener if we have no worry for failover timing. We can change default TCP/IP timeout using operating system utilities/commands and kept smaller. But taking advantage of VIP (Virtual IP address) in Oracle 10g RAC database is advisable.

Sunday, July 29, 2012

Locally vs Dictionary Managed Tablespaces

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself.

Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and

Tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

DEV> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

------------------------------     ----------------------       --------------------
SYSTEM                           DICTIONARY           USER
SYSAUX                          LOCAL                      SYSTEM
UNDOTBS1                      LOCAL                      SYSTEM
TEMP                               LOCAL                      UNIFORM
ABMD                              LOCAL                      USER
ESSO                                LOCAL                     USER
SIT                                   LOCAL                     USER
USERS                             LOCAL                     UNIFORM
USER_DATA                    LOCAL                     UNIFORM

Friday, June 29, 2012

Oracle Applications Idle Session Timeout

What is Session Idle time?

If Oracle Apps client is idle for some time (Eg. Application user goes for a coffee break) session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session (both forms & self service) after idle time value is reached to the one mentioned in configuration file.

To enter into application, profile option "ICX Session Timeout" is used.

ICX Session Time out mentioned in profile option ICX: Session Timeout is in minutes, so ICX session timeout=30

From where ICX: Session Timeout & session.timeout get values?

Autoconfig determines value for profile option "ICX: Session Timeout" and "session.timeout" from entry in context file ($APPL_TOP/admin/SID_hostname.xml) with parameter s_sesstimeout where value mentioned is in milliseconds so profile option ICX: Session Timeout value should be s_sesstimeout/ (1000 * 60) which means here its 10 Minutes. This value is also set in in $IAS_ORACLE_HOME/Apache/Jserv where number mentioned is in milli second i.e. 600000 (equal to 10 Minutes) session.timeout = 600000.

Eg.  Session Timeout is in minutes, so ICX session timeout=480 is 8Hrs.