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.

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

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.

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.