I had to configure an Oracle R12 (12.1.1/12.1.3) E-Business Suite and two node Oracle 11gR2 RAC environment recently to take advantage of load balancing.
However, instead of using a hardware load balancer such as Cisco ACE, we had to find a manual process to implement this goal.
First stop was to review the documentation listed below on My Oracle Support (http://support.oracle.com):
Note # 380489.1 Using Load Balancers with Oracle E-Business Suite Release 12
Note # 603325.1 Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite 12
Note # 226880.1 Configuration of Load Balancing and Transparent Application Failover
Note # 262298.1 Load Balancing Doesn't Balance the Number of Sessions Across All Available Nodes
Note # 887522.1 11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained
Note #1080674.1 JDBC Drivers and Failover Mechanisms (FCF, TAF, SCAN )
Note #1100024.1 Master Note for Oracle JDBC High Availability Features
Note #247135.1 How to Implement Load Balancing With RAC Configured System Using JDBC
Now, based on my experience with load balancers and Oracle, you should in most cases take the default setup of running your application middle tier server nodes in the default servlet mode to work with Cisco ACE and other hardware load balancers.
There is no compatibility with socket mode for hardware load balancers after discussions with Oracle support, you MUST run in servlet mode for hardware load balancing.
With that said, you could also use a web cache 10gAS application server to use software load balancing instead of the expensive hardware load balancer.
Our challenge was to implement a pure software load balance setup with Oracle R12 EBS and 11gR2 RAC environment with the ability to run the application middle tier nodes in socket mode for performance and application compatibility reasons.
Configure application middle tier environment for load balancing
First, we had to edit the following context variables on the apps tier for load balancing using context editor in the Oracle Applications Manager (OAM):
s_tools_twotask=VIS_BALANCE
s_weboh_twotask=VIS_BALANCE
s_apps_jdbc_connect_alias=VIS_BALANCE
Make sure to run the Oracle R12 EBS autoconfig utility ($ADMIN_SCRIPTS_HOME/adautocfg.sh) after changing the above context variables in your app tier context file.
Note: take a backup of the context file before making changes in case you need to revert back to original configuration!
The above context variables for the apps tier should point to the load balance setup in tnsnames.ora on the two RAC cluster nodes as shown below:
VIS_BALANCE=
(DESCRIPTION=
(ADDRESS_LIST=
(LOAD_BALANCE=YES)
(FAILOVER=YES)
(ADDRESS=(PROTOCOL=tcp)(HOST=node1-vip)(PORT=1521))
(ADDRESS=(PROTOCOL=tcp)(HOST=node2-vip)(PORT=1521)) )
(CONNECT_DATA= (SERVICE_NAME=VIS)
) )
So you will need to create the entry called VIS_BALANCE for example as shown in the above tnsnames.ora file for the app tier nodes and database tier RAC Nodes.
Configure listener.ora
Sometimes even if you setup load balancing correctly, the distribution will not be sent equally across the cluster nodes. To resolve this issue, there is an undocumented parameter you can set to avoid this problem in your listener.ora file.
vi listener.ora
PREFER_LEAST_LOADED_NODE_listener_name=OFF
Register listener and services
Now that we have completed these changes, we need to register the listener services for all nodes.
Then unset and reset the remote_listener parameter for node1.
sqlplus '/as sysdba'
alter system set remote_listener='' scope=both;
alter system register;
alter system set remote_listener='VIS_REMOTE' scope=both;
alter system register;
Stop and restart the database and listeners.
lsnrctl stop
lsnrctl start
lsnrctl reload
Verify that both listeners display both instances in the status and services output.
lsnrctl services
lsnrctl status
Since load balancing is a complex topic, we just scratched the surface here in this post to highlight a quick and easy way to implement manual load balancing at the database layer with Oracle R12 EBS and Oracle 11gR2 RAC.
Future post will cover how to load balance the application middle tier.
Tuesday, September 20, 2011
Oracle 11g agent fails to upload to Oracle 11g Grid Control
Recently, I built a new Oracle 11gR1 Grid Control Enterprise Manager (OEM) server at work to monitor our complex environments.
All worked fine with agents, OMS and Grid Control until the other day when the agent failed to upload the status to Grid control.
After checking status for the agent and trying to perform a manual upload as listed below,
I received the following errors:
"emctl upload failed: collection status disabled by upload manager"
So I checked on disk space and plenty was available on the filesystem where the agent was located.
After finding a hit on My Oracle Support (MOS) site under Doc ID 396238.1,
I reviewed the following parameters in the emd.properties file under the
AGENT_HOME/sysman/emd directory:
UploadFileSize
UploadMaxBytes
UploadMaxDiskUsedPct
These were set to 2048, 50, and 98 for UploadFileSize, UploadMaxBytes, and UploadMaxDiskUsed by default when I checked the emd.properties file.
Sure enough the default values were too small thus causing the agent upload to fail.
First, I shutdown the agent with the emctl stop agent command.
Next, I changed the parameters to larger values for size of files:
$ cd /home/oracle/agent11g/sysman/config
vi emd.propertiesUploadFileSize=4096
## The maximum number of megabytes(MB) the upload manager will support in the
# upload directory before temporarily disabling collections, logging and tracing
##
UploadMaxBytesXML=500
# The maximum amount (%) of disk space that can be used on the EMD filesystem
# before the following is disabled:
# - Collection of data (upload manager)
# - Logging and tracing
# - Diagnosability traces
#@description=
#@valueType=Int
#@LOV=#@default=98#
UploadMaxDiskUsedPct=99
Then I started the agent and performed manual upload to OEM Grid OMS server:
./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /home/oracle/agent11g/node1
Agent binaries : /home/oracle/agent11g
Agent Process ID : 16864
Parent Process ID : 16828
Agent URL : https://node1:3872/emd/main
Repository URL : https://oem:4900/em/upload
Started at : 2011-09-20 11:33:05
Started by user : oracle
Last Reload : 2011-09-20 11:33:05
Last successful upload : 2011-09-20 13:22:32
Total Megabytes of XML files uploaded so far : 6.27
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 5.30%
Last successful heartbeat to OMS : 2011-09-20 13:36:26
---------------------------------------------------------------
Agent is Running and Ready
$ ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
Now it works and OEM Grid Control can receive the updates from the agent on the server monitored.Cheers,Ben
All worked fine with agents, OMS and Grid Control until the other day when the agent failed to upload the status to Grid control.
After checking status for the agent and trying to perform a manual upload as listed below,
I received the following errors:
"emctl upload failed: collection status disabled by upload manager"
So I checked on disk space and plenty was available on the filesystem where the agent was located.
After finding a hit on My Oracle Support (MOS) site under Doc ID 396238.1,
I reviewed the following parameters in the emd.properties file under the
AGENT_HOME/sysman/emd directory:
UploadFileSize
UploadMaxBytes
UploadMaxDiskUsedPct
These were set to 2048, 50, and 98 for UploadFileSize, UploadMaxBytes, and UploadMaxDiskUsed by default when I checked the emd.properties file.
Sure enough the default values were too small thus causing the agent upload to fail.
First, I shutdown the agent with the emctl stop agent command.
Next, I changed the parameters to larger values for size of files:
$ cd /home/oracle/agent11g/sysman/config
vi emd.propertiesUploadFileSize=4096
## The maximum number of megabytes(MB) the upload manager will support in the
# upload directory before temporarily disabling collections, logging and tracing
##
UploadMaxBytesXML=500
# The maximum amount (%) of disk space that can be used on the EMD filesystem
# before the following is disabled:
# - Collection of data (upload manager)
# - Logging and tracing
# - Diagnosability traces
#@description=
#@valueType=Int
#@LOV=#@default=98#
UploadMaxDiskUsedPct=99
Then I started the agent and performed manual upload to OEM Grid OMS server:
./emctl status agent
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 11.1.0.1.0
OMS Version : 11.1.0.1.0
Protocol Version : 11.1.0.0.0
Agent Home : /home/oracle/agent11g/node1
Agent binaries : /home/oracle/agent11g
Agent Process ID : 16864
Parent Process ID : 16828
Agent URL : https://node1:3872/emd/main
Repository URL : https://oem:4900/em/upload
Started at : 2011-09-20 11:33:05
Started by user : oracle
Last Reload : 2011-09-20 11:33:05
Last successful upload : 2011-09-20 13:22:32
Total Megabytes of XML files uploaded so far : 6.27
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 5.30%
Last successful heartbeat to OMS : 2011-09-20 13:36:26
---------------------------------------------------------------
Agent is Running and Ready
$ ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
Now it works and OEM Grid Control can receive the updates from the agent on the server monitored.Cheers,Ben
Thursday, July 28, 2011
Using PL/SQL and SQL to change profile options for Oracle R12 EBS
Recently I had to fix an unusual problem with Oracle R12 (12.1.1) E-Business Suite system after changing some default timeout parameters. Since I was unable to login to the Oracle Applications Manager (OAM) graphical interface and had weird errors below, I needed a way to switch these profile values back to the lower default values. I set the ICX timeout parameters (ICX: Session Timeout, ICX: Limit Time, and ICX: Limit Connect) way too high in our test environment to experiment with user logout issues. SO I had the below errors:
Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION.
Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at "
APPS.FND_SESSION_MANAGEMENT", line 1236 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421 ORA-06512: at line 1 has been detected in FND_AOLJ_UTIL.is_Valid_ICX.
Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.
Needless to say this was extremely frustrating problem to solve as the few hits on My Oracle Support (formerly Metalink) along with an SR to support were unable to provide me with the solution and root cause. Finally after some research and discussion with a very smart support manager from Oracle (thank you Warwick!), I used a SQL and PL/SQL script to reset the values to much lower timeout settings and then I cleared the web browser cache. This solved the issue.
Sometimes you may not want to login or be able to access the OAM browser due to such issues. In this case, you want to use a SQL and/or PL/SQL wrapper script to modify profile settings. Below is the script to check for current values of profiles within Oracle R12 EBS:
set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
spool profile_options.txt
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;
The above script queries the Oracle R12 EBS database for values against the FND_PROFILE tables in the APPS schema.
Ok so now that we have our settings for profiles, let's show how we changed these for ICX profile settings to provide the solution.
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('ICX_LIMIT_CONNECT', '2000', '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;
/
To use the above PL/SQL script to change profile parameters, it uses the FND_PROFILE package for Oracle R12 EBS with the SAVE function within PL/SQL which in turn takes three parameters as shown by the package definition listed below:
SQL> desc FND_PROFILE
FUNCTION BUMPCACHEVERSION_RF RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SUBSCRIPTION_GUID RAW IN
P_EVENT WF_EVENT_T IN/OUT
FUNCTION DEFINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FUNCTION DELETE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT
PROCEDURE GET
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 OUT
FUNCTION GET_ALL_TABLE_VALUES RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DELIM VARCHAR2 IN
PROCEDURE GET_SPECIFIC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_Z VARCHAR2 IN
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
VAL_Z VARCHAR2 OUT
DEFINED_Z BOOLEAN OUT
ORG_ID_Z NUMBER IN DEFAULT
SERVER_ID_Z NUMBER IN DEFAULT
FUNCTION GET_TABLE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
SITE_ID_Z NUMBER IN DEFAULT
PROCEDURE INITIALIZE_ORG_CONTEXT
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 IN
PROCEDURE PUTMULTIPLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMES VARCHAR2 IN
VALS VARCHAR2 IN
NUM NUMBER IN
FUNCTION SAVE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT
FUNCTION SAVE_USER RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
FUNCTION VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FUNCTION VALUE_SPECIFIC RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
USER_ID NUMBER IN DEFAULT
RESPONSIBILITY_ID NUMBER IN DEFAULT
APPLICATION_ID NUMBER IN DEFAULT
ORG_ID NUMBER IN DEFAULT
SERVER_ID NUMBER IN DEFAULT
FUNCTION VALUE_WNPS RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
In our above example, we gave FND_PROFILE.SAVE the required three parameters to set the profile values for ICX:Limit Connect based on time duration of 2000 which allows for up to a maximum of 2000 connections permitted in a single session. We want a large value so that the many batch processes and concurrent jobs processed by functional users can take place without terminating abnormally while processing such things as invoices and order shipments for Oracle Financials within the Oracle R12 E-Business Suite.
Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at line 1 has been detected in FND_SESSION_MANAGEMENT.CHECK_SESSION.
Oracle error 1841: java.sql.SQLDataException: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "APPS.FND_SESSION_MANAGEMENT", line 1468 ORA-06512: at "
APPS.FND_SESSION_MANAGEMENT", line 1236 ORA-06512: at "APPS.FND_AOLJ_UTIL", line 421 ORA-06512: at line 1 has been detected in FND_AOLJ_UTIL.is_Valid_ICX.
Servlet error: An exception occurred. The current application deployment descriptors do not allow for including it in this response. Please consult the application log for details.
Needless to say this was extremely frustrating problem to solve as the few hits on My Oracle Support (formerly Metalink) along with an SR to support were unable to provide me with the solution and root cause. Finally after some research and discussion with a very smart support manager from Oracle (thank you Warwick!), I used a SQL and PL/SQL script to reset the values to much lower timeout settings and then I cleared the web browser cache. This solved the issue.
Sometimes you may not want to login or be able to access the OAM browser due to such issues. In this case, you want to use a SQL and/or PL/SQL wrapper script to modify profile settings. Below is the script to check for current values of profiles within Oracle R12 EBS:
set serveroutput on
set echo on
set timing on
set feedback on
set long 10000
set linesize 120
set pagesize 132
column SHORT_NAME format A30
column NAME format A40
column LEVEL_SET format a15
column CONTEXT format a30
column VALUE format A60 wrap
spool profile_options.txt
select p.profile_option_name SHORT_NAME, n.user_profile_option_name NAME,
decode(v.level_id, 10001, 'Site', 10002, 'Application',
10003, 'Responsibility', 10004, 'User', 10005, 'Server',
10007, 'SERVRESP', 'UnDef') LEVEL_SET,
decode(to_char(v.level_id), '10001', '',
'10002', app.application_short_name,
'10003', rsp.responsibility_key,
'10005', svr.node_name,
'10006', org.name,
'10004', usr.user_name,
'10007', 'Serv/resp',
'UnDef') "CONTEXT",
v.profile_option_value VALUE
from fnd_profile_options p,
fnd_profile_option_values v,
fnd_profile_options_tl n,
fnd_user usr,
fnd_application app,
fnd_responsibility rsp,
fnd_nodes svr,
hr_operating_units org
where p.profile_option_id = v.profile_option_id (+)
and p.profile_option_name = n.profile_option_name
and upper(n.user_profile_option_name) like upper('%&profile_name%')
and usr.user_id (+) = v.level_value
and rsp.application_id (+) = v.level_value_application_id
and rsp.responsibility_id (+) = v.level_value
and app.application_id (+) = v.level_value
and svr.node_id (+) = v.level_value
and org.organization_id (+) = v.level_value
order by short_name, level_set;
The above script queries the Oracle R12 EBS database for values against the FND_PROFILE tables in the APPS schema.
Ok so now that we have our settings for profiles, let's show how we changed these for ICX profile settings to provide the solution.
DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('ICX_LIMIT_CONNECT', '2000', '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;
/
To use the above PL/SQL script to change profile parameters, it uses the FND_PROFILE package for Oracle R12 EBS with the SAVE function within PL/SQL which in turn takes three parameters as shown by the package definition listed below:
SQL> desc FND_PROFILE
FUNCTION BUMPCACHEVERSION_RF RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_SUBSCRIPTION_GUID RAW IN
P_EVENT WF_EVENT_T IN/OUT
FUNCTION DEFINED RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FUNCTION DELETE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT
PROCEDURE GET
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 OUT
FUNCTION GET_ALL_TABLE_VALUES RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DELIM VARCHAR2 IN
PROCEDURE GET_SPECIFIC
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME_Z VARCHAR2 IN
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
VAL_Z VARCHAR2 OUT
DEFINED_Z BOOLEAN OUT
ORG_ID_Z NUMBER IN DEFAULT
SERVER_ID_Z NUMBER IN DEFAULT
FUNCTION GET_TABLE_VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
PROCEDURE INITIALIZE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
USER_ID_Z NUMBER IN DEFAULT
RESPONSIBILITY_ID_Z NUMBER IN DEFAULT
APPLICATION_ID_Z NUMBER IN DEFAULT
SITE_ID_Z NUMBER IN DEFAULT
PROCEDURE INITIALIZE_ORG_CONTEXT
PROCEDURE PUT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
VAL VARCHAR2 IN
PROCEDURE PUTMULTIPLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAMES VARCHAR2 IN
VALS VARCHAR2 IN
NUM NUMBER IN
FUNCTION SAVE RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
X_LEVEL_NAME VARCHAR2 IN
X_LEVEL_VALUE VARCHAR2 IN DEFAULT
X_LEVEL_VALUE_APP_ID VARCHAR2 IN DEFAULT
X_LEVEL_VALUE2 VARCHAR2 IN DEFAULT
FUNCTION SAVE_USER RETURNS BOOLEAN
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
X_NAME VARCHAR2 IN
X_VALUE VARCHAR2 IN
FUNCTION VALUE RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
FUNCTION VALUE_SPECIFIC RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
USER_ID NUMBER IN DEFAULT
RESPONSIBILITY_ID NUMBER IN DEFAULT
APPLICATION_ID NUMBER IN DEFAULT
ORG_ID NUMBER IN DEFAULT
SERVER_ID NUMBER IN DEFAULT
FUNCTION VALUE_WNPS RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
In our above example, we gave FND_PROFILE.SAVE the required three parameters to set the profile values for ICX:Limit Connect based on time duration of 2000 which allows for up to a maximum of 2000 connections permitted in a single session. We want a large value so that the many batch processes and concurrent jobs processed by functional users can take place without terminating abnormally while processing such things as invoices and order shipments for Oracle Financials within the Oracle R12 E-Business Suite.
Tuesday, July 12, 2011
Add node fails due to time synchronization issue with NTPD and CTSSD
Today while checking the status of a four node Oracle 11gR2 RAC environment, I noticed something was wrong with time synchronization between the cluster nodes. Even though I had our system administrator configure NTPD for the environment, the Cluster Verification Utility (CVU) failed on NTPD errors and showed the Cluster Synchronization Services Daemon (CTSSD) in Observer mode when I ran a check of the clock synchronization:
oracle@rac1 ~]$ cluvfy comp clocksync -n all
Verifying Clock Synchronization across the cluster nodes
Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed
Checking if CTSS Resource is running on all nodes...
CTSS resource check passed
Querying CTSS for time offset on all nodes...
Query of CTSS for time offset passed
Check CTSS state started...
CTSS is in Observer state. Switching over to clock synchronization checks using NTP
Starting Clock synchronization checks using Network Time Protocol(NTP)...
NTP Configuration file check started...
NTP Configuration file check passed
Checking daemon liveness...
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes
NTP daemon slewing option check failed on some nodes
Check failed on nodes:
rac1,rac2,rac3,rac4
PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"
Clock synchronization check using Network Time Protocol(NTP) failed
PRVF-9652 : Cluster Time Synchronization Services check failed
Verification of Clock Synchronization across the cluster nodes was unsuccessful on all the specified nodes.
Aha! So CTSSD must have an NTPD server in slewing option mode or it will fail to synchronize the cluster nodes correctly.
The solution to this is to shutdown the Oracle RAC database environment as well as to shutdown ASM and the clusterware and then to restart ntpd on the Oracle RAC cluster nodes and app tier server host with the –x option
Previous to this, I had verified that ntpd was running however it was started by default mode and not with the -x option as shown below:
[root@rac1 ~]# service ntpd status
ntpd (pid 24396) is running...
[root@rac1 ~]# ps -ef|grep ntpd
root 15495 8369 0 09:52 pts/1 00:00:00 grep ntpd
ntp 24396 1 0 Jul11 ? 00:00:00 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g
You can check to see if the -x flag has been set by examination of the /etc/sysconfig/ntpd file.
[root@rac1 ~]# grep OPTIONS /etc/sysconfig/ntpd
OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"
NTPDATE_OPTIONS=""
I found the following two My Oracle Support (http://support.oracle.com) notes useful while solving this issue with Oracle 11gR2 RAC and time synchronization issues.
MOS 1054006.1- CTSSD Runs in Observer Mode Even Though No Time Sync Software is Running
MOS 1056693.1- How to Configure NTP or Windows Time to Resolve CLUVFY Error PRVF-5436 PRV-9652
oracle@rac1 ~]$ cluvfy comp clocksync -n all
Verifying Clock Synchronization across the cluster nodes
Checking if Clusterware is installed on all nodes...
Check of Clusterware install passed
Checking if CTSS Resource is running on all nodes...
CTSS resource check passed
Querying CTSS for time offset on all nodes...
Query of CTSS for time offset passed
Check CTSS state started...
CTSS is in Observer state. Switching over to clock synchronization checks using NTP
Starting Clock synchronization checks using Network Time Protocol(NTP)...
NTP Configuration file check started...
NTP Configuration file check passed
Checking daemon liveness...
Liveness check passed for "ntpd"
Check for NTP daemon or service alive passed on all nodes
NTP daemon slewing option check failed on some nodes
Check failed on nodes:
rac1,rac2,rac3,rac4
PRVF-5436 : The NTP daemon running on one or more nodes lacks the slewing option "-x"
Clock synchronization check using Network Time Protocol(NTP) failed
PRVF-9652 : Cluster Time Synchronization Services check failed
Verification of Clock Synchronization across the cluster nodes was unsuccessful on all the specified nodes.
Aha! So CTSSD must have an NTPD server in slewing option mode or it will fail to synchronize the cluster nodes correctly.
The solution to this is to shutdown the Oracle RAC database environment as well as to shutdown ASM and the clusterware and then to restart ntpd on the Oracle RAC cluster nodes and app tier server host with the –x option
Previous to this, I had verified that ntpd was running however it was started by default mode and not with the -x option as shown below:
[root@rac1 ~]# service ntpd status
ntpd (pid 24396) is running...
[root@rac1 ~]# ps -ef|grep ntpd
root 15495 8369 0 09:52 pts/1 00:00:00 grep ntpd
ntp 24396 1 0 Jul11 ? 00:00:00 ntpd -u ntp:ntp -p /var/run/ntpd.pid -g
You can check to see if the -x flag has been set by examination of the /etc/sysconfig/ntpd file.
[root@rac1 ~]# grep OPTIONS /etc/sysconfig/ntpd
OPTIONS="-u ntp:ntp -p /var/run/ntpd.pid"
NTPDATE_OPTIONS=""
I found the following two My Oracle Support (http://support.oracle.com) notes useful while solving this issue with Oracle 11gR2 RAC and time synchronization issues.
MOS 1054006.1- CTSSD Runs in Observer Mode Even Though No Time Sync Software is Running
MOS 1056693.1- How to Configure NTP or Windows Time to Resolve CLUVFY Error PRVF-5436 PRV-9652
Logon failure to Oracle R12 EBS in 11gR2 RAC Environment and Solution
Yesterday while working on a four node Oracle 11gR2 RAC environment with Oracle R12 eBusiness Suite, I encountered a weird login authentication error when I tried to open a few forms to manage concurrent processing as system administrator:
"Cannot Complete Application logon. You may have entered an invalid applications password or there may have been a database connect error."
I checked the password by logging into the database tier with the apps account and that worked. Then I searched on My Oracle Support and found more clues to help me solve the issue.
Starting Forms Intermittently Raises "Cannot Complete Applications Logon" and "APP-FND-01496" Error When Using RAC [ID 753028.1]
Sure enough, when I logged into the Oracle 11gR2 RAC cluster nodes and checked for their time, they were out of sync! So, we had to configure ntpd (Network Time Protocol Daemon) and restart the cluster nodes. Here is what is puzzling- with 11gR2, there is a new process called the CTSSD or Cluster Time Synchronization Service Daemon which is supposed to replace the functionality of NTPD. However, you still need to run NTPD to have RAC function in 11gR2 without complaining. Also, NTPD is required to some extent to maintain a time synchronization with the Oracle RAC nodes. Once we re-synched the clocks for the Oracle 11gR2 RAC hosts, we were able to resolve the forms login errors with our Oracle R12 eBusiness Suite and RAC environment.
"Cannot Complete Application logon. You may have entered an invalid applications password or there may have been a database connect error."
I checked the password by logging into the database tier with the apps account and that worked. Then I searched on My Oracle Support and found more clues to help me solve the issue.
Starting Forms Intermittently Raises "Cannot Complete Applications Logon" and "APP-FND-01496" Error When Using RAC [ID 753028.1]
Sure enough, when I logged into the Oracle 11gR2 RAC cluster nodes and checked for their time, they were out of sync! So, we had to configure ntpd (Network Time Protocol Daemon) and restart the cluster nodes. Here is what is puzzling- with 11gR2, there is a new process called the CTSSD or Cluster Time Synchronization Service Daemon which is supposed to replace the functionality of NTPD. However, you still need to run NTPD to have RAC function in 11gR2 without complaining. Also, NTPD is required to some extent to maintain a time synchronization with the Oracle RAC nodes. Once we re-synched the clocks for the Oracle 11gR2 RAC hosts, we were able to resolve the forms login errors with our Oracle R12 eBusiness Suite and RAC environment.
Data Recovery Advisor Feature for 11g Not Supported with 11gR2 RAC
Today while checking and validating our database backups for our Oracle 11gR2 RAC and R12 EBS Oracle environments, I wanted to test out the Data Recovery Advisor with 11gR2 RAC. Well, turns out, it is not supported! When you try to issue the DRA commands from RMAN, you get the following message:
[oracle@rac1 ~]$ rman target RAC1
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 12 14:09:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: VIS (DBID=296736794)
RMAN> list failure low;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/12/2011 14:09:50
RMAN-05533: LIST FAILURE is not supported on RAC database
RMAN> advise failure low;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of advise command at 07/12/2011 14:09:56
RMAN-05533: ADVISE FAILURE is not supported on RAC database
After checking this on My Oracle Support (http://support.oracle.com), I discovered that indeed, Oracle does not support their new feature for backup and recovery (Data Recovery Advisor) on RAC!
Now this is confusing because in the Oracle 11gR2 documentation, it specifies that DRA is supported by RAC when in fact, it is not supported and does not work!
Reference:
Documentation wrong with RAC and DRA - Data Recovery Advisor. (Oracle Database Administrator's Guide 11.2) [ID 1297071.1]
So folks, you cannot use the cool new 11g feature data recovery advisor with RAC! I was bummed to find this out as it is a cool feature and worked in 11gR1.
[oracle@rac1 ~]$ rman target RAC1
Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 12 14:09:39 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: VIS (DBID=296736794)
RMAN> list failure low;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/12/2011 14:09:50
RMAN-05533: LIST FAILURE is not supported on RAC database
RMAN> advise failure low;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of advise command at 07/12/2011 14:09:56
RMAN-05533: ADVISE FAILURE is not supported on RAC database
After checking this on My Oracle Support (http://support.oracle.com), I discovered that indeed, Oracle does not support their new feature for backup and recovery (Data Recovery Advisor) on RAC!
Now this is confusing because in the Oracle 11gR2 documentation, it specifies that DRA is supported by RAC when in fact, it is not supported and does not work!
Reference:
Documentation wrong with RAC and DRA - Data Recovery Advisor. (Oracle Database Administrator's Guide 11.2) [ID 1297071.1]
So folks, you cannot use the cool new 11g feature data recovery advisor with RAC! I was bummed to find this out as it is a cool feature and worked in 11gR1.
Wednesday, May 25, 2011
Oracle 11gR1/R2 RAC Essentials Book now available
My new Oracle 11g RAC book is now available from PACKT Press:
http://www.packtpub.com/oracle-11g-r1-r2-real-application-clusters-essentials/book

If you are an Oracle DBA or technical manager looking to implement and design an Oracle 11g RAC environment, then you will find this book critical to successful implementation!
http://www.packtpub.com/oracle-11g-r1-r2-real-application-clusters-essentials/book

If you are an Oracle DBA or technical manager looking to implement and design an Oracle 11g RAC environment, then you will find this book critical to successful implementation!
Subscribe to:
Posts (Atom)