Value Chain Planning

ORACLE SOLUTION: COMPLETE, OPEN, MODULAR AND INTEGRATED

Advanced Planning Command Center Advanced Supply Chain Planning Collaborative Planning Demand Management Demand Signal Repository Global Order Promising Inventory Optimization Predictive Trade Planning and Optimization Production Scheduling Rapid Planning Real-Time Sales and Operations Planning Service Parts Planning Strategic Network Optimization Value Chain Planning for JD Edwards EnterpriseOne Customers (PDF)

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 :: adoafmctl.sh version 120.6.12000000.2

06/20/08-19:12:59 :: adoafmctl.sh: starting OPMN if it is not running
opmnctl: opmn is already running.
06/20/08-19:12:59 :: adoafmctl.sh: Starting OPMN managed OAFM OC4J instance
opmnctl: starting opmn managed processes...
===========================================================
opmn id=eit.enrichit.com:6210
0 of 1 processes started.
ias-instance id=VIS_eit.eit.enrichit.com
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------------------------------------------------------------------------
ias-component/process-type/process-set:
OC4J/oafm/default_group/
Error
--> Process (index=1,uid=1675454223,pid=29758)
failed to start a managed process after the maximum retry limit
Log:
/u02/oracle/VIS/inst/apps/VIS_eit/logs/ora/10.1.3/opmn/OC4J~oafm~default_group~1

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

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

Processes in Instance: VIS_eit.eit.enrichit.com---------------+--------------+--------+---------+---------
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

Cause
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

Solution
1 - open a new shell and set your apps environment

2 - cd $ADMIN_SCRIPTS_HOME

3 - adopmnctl.sh 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 - adopmnctl.sh start

7 - check if the issue has been resolved: adapcctl.sh status

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

Processes in Instance: VIS_eit.eit.enrichit.com
---------------+--------------+--------+---------+---------
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
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /u02/oracle/VIS/inst/apps/VIS_eit/logs/appl/admin/log/adopmnctl.txt for more information ...

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

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

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

 

Concurrent Manager

FND_CONCURRENT_QUEUES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUE_SIZE

 

FND

FND_APPL_TOPS
FND_LOGINS
FND_USER
FND_DM_NODES
FND_TNS_ALIASES
FND_NODES
FND_RESPONSIBILITY
FND_DATABASES
FND_UNSUCCESSFUL_LOGINS
FND_LANGUAGES
FND_APPLICATION
FND_PROFILE_OPTION_VALUES

 

 AD / Patches

AD_APPLIED_PATCHES
AD_PATCH_DRIVERS
AD_BUGS
AD_INSTALL_PROCESSES
AD_SESSIONS
AD_APPL_TOPS

****************************************************************************************

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

****************************************************************************************

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.

select
b.user_profile_option_name "Long Name"
, a.profile_option_name "Short Name"
, decode(to_char(c.level_id),'10001','Site'
,'10002','Application'
,'10003','Responsibility'
,'10004','User'
,'Unknown') "Level"
, decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'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"
from
apps.fnd_profile_options a
, apps.FND_PROFILE_OPTIONS_VL b
, apps.FND_PROFILE_OPTION_VALUES c
, apps.FND_USER d
, apps.FND_USER e
, apps.FND_RESPONSIBILITY_VL g
, apps.FND_APPLICATION h
where
--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,
decode(to_char(c.level_id),'10001','Site'
,'10002',nvl(h.application_short_name,to_char(c.level_value))
,'10003',nvl(g.responsibility_name,to_char(c.level_value))
,'10004',nvl(e.user_name,to_char(c.level_value))
,'Unknown');


******************************************************************************************

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)

Definition:

 

• 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.

Types:

 

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

• SYSTEM PROFILE: 

Responsibility SYSTEM ADMINISTRATOR -->

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.

  

• PERSONAL PROFILE:

Responsibility SYSTEM ADMINISTRATOR -->

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:

 

Responsibility APPLICATION DEVELOPER -->

Use of the API FND_PROFILE

 

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);

Example

SELECT fnd_profile.value('PROFILEOPTION')

      ,fnd_profile.value('MFG_ORGANIZATION_ID')

      ,fnd_profile.value('ORG_ID')

      ,fnd_profile.value('LOGIN_ID')

      ,fnd_profile.value('USER_ID')

      ,fnd_profile.value('USERNAME')

      ,fnd_profile.value('CONCURRENT_REQUEST_ID')

      ,fnd_profile.value('GL_SET_OF_BKS_ID')

      ,fnd_profile.value('SO_ORGANIZATION_ID')

      ,fnd_profile.value('APPL_SHRT_NAME')

      ,fnd_profile.value('RESP_NAME')

      ,fnd_profile.value('RESP_ID')

  FROM DUAL;

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

3. FND_PROFILE.PUT(‘Name of the profile’, value of the profile);

Example

SET SERVEROUTPUT ON;

DECLARE

   v_conc_login_id      NUMBER;

BEGIN

   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);

END;

Output:

1425

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 fnd_profile.save.

DECLARE
   stat   BOOLEAN;
BEGIN
   DBMS_OUTPUT.DISABLE;
   DBMS_OUTPUT.ENABLE (100000);
   stat := fnd_profile.SAVE ('ORG_ID', 286, 'SITE');
   IF stat
   THEN
      DBMS_OUTPUT.put_line ('Stat = TRUE - profile updated');
   ELSE
      DBMS_OUTPUT.put_line ('Stat = FALSE - profile NOT updated');
   END IF;
   COMMIT;
END;

 

 ***********************************************************************************

 

 

 

Different Modes of Data guard Protection

Introduction

• 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	SYNC/ ASYNC	Benefits	Drawbacks
Maximum Protection	SYNC	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	SYNC	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	ASYNC	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.

 

Conclusion

• 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.

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 root.sh 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.

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;

TABLESPACE_NAME       EXTENT_MAN        ALLOCATION
------------------------------     ----------------------       --------------------
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

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 zone.properties 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.