Profile option FND_INIT_SQL may cause error

When switching Responsibility, forms gave error:

Oracle error -6550: ORA-06550 : line 1, column 7:
 PLS-00306: wrong number or types of arguments in call to 'INIT'
 ORA-06550 : line 1, column 7:
 PL/SQL: Statement ignored
 has been detected in fnd_global.initialize[fnd_init_sql].

I checked profile 'Initialization SQL Statement - Custom' as suggested by Doc ID 577753.1 and did see anything on Site level. But when I ran below code, I sew one at Application level (by ID 10002).

SQL> select PROFILE_OPTION_ID, LEVEL_ID, LEVEL_VALUE, PROFILE_OPTION_VALUE
    from  apps.fnd_profile_option_values
    where profile_option_id = (
     select   profile_option_id   from  apps.fnd_profile_options
    where  profile_option_name = 'FND_INIT_SQL' );
PROFILE_OPTION_ID   LEVEL_ID LEVEL_VALUE   PROFILE_OPTION_VALUE
---------------------------  --------------- --------------------  --------------------------------
             3157                  10002               20004       begin my_global.init; end;

I ran a script in check R12 profile options to got the custom Application name. After I changed profile 'Initialization SQL Statement - Custom' on that Application level to null, which deletes that row from table apps.fnd_profile_option_values, the Oracle errors on forms were fixed.

Run FTP and submit concurrent job by SQL

This is a SQL script to FTP get a file from a remote Windows server to database server, and then to submit an EBS concurrent job which will read that file. The database version is Oracle 11.2.0.4.

/** pre setups:
SQL> select fnd_profile.value('WIN_UPS_FILE_INFO') from dual;
FND_PROFILE.VALUE('WIN_UPS_FILE_INFO')
--------------------------------------------------------------------------------
PUL/NT_USERID passWD WIN_HOSTNAME.domain.com

SQL> select * from all_directories where directory_name = 'CUSTOM_FTPIN';
OWNER   DIRECTORY_NAME  DIRECTORY_PATH
---------   -----------------------   -----------------------------
SYS         CUSTOM_FTPIN        /path/to/ftp/in
**/

SQL> set serveroutput on
SQL> DECLARE
      --  l_utl_file_handler          UTL_FILE.FILE_TYPE;
      l_conn  UTL_TCP.connection;
      l_chr_file_line varchar2(200);
      l_chr_user_id varchar2(20);
      l_chr_password varchar2(20);
      l_chr_directory_path varchar2(200);
      l_chr_server_name varchar2(40);
      l_chr_file_name varchar2(20) := 'WIN07-24-2016.csv';
      -- l_chr_file_name varchar2(20) := '&1';
      l_request_id Number;
 BEGIN
    l_chr_file_line := fnd_profile.value('WIN_UPS_FILE_INFO');
    select substr(l_chr_file_line,1,12) into l_chr_user_id from dual;
    select substr(l_chr_file_line,14,7) into l_chr_password from dual;
    select substr(l_chr_file_line,22,30) into l_chr_server_name from dual;

    l_conn := ftp.login(l_chr_server_name, '21',l_chr_user_id,l_chr_password);
    ftp.ascii(p_conn => l_conn);
    ftp.get(p_conn      => l_conn,
               p_from_file => l_chr_file_name,
               p_to_dir    => 'CUSTOM_FTPIN',
               p_to_file   => l_chr_file_name);
     ftp.logout(l_conn);
    utl_tcp.close_all_connections;

/**  commend this out for testing FTP
 l_request_id := FND_REQUEST.SUBMIT_REQUEST('MYAP','MYAPUFVW','','',FALSE,''||l_chr_file_name||'',
chr(0), '', '','','',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '', '',
                  '', '', '', '', '', '', '', '', '');

**/
commit;
END;
 /

SQL>
220 Microsoft FTP Service
331 Password required for PUL/NT_USERID.
230 User logged in.
200 Type set to A.
227 Entering Passive Mode (177,79,176,170,278,170).
125 Data connection already open; Transfer starting.
226 Transfer complete.
221 Goodbye.

PL/SQL procedure successfully completed.

SQL> exit

Network error FRM-92102 and F5

A R12.1.3 EBS site has one concurrent server and two web/forms servers which are monitored by F5 for load balance. If I started Apache services on two web hosts, forms got network error very quick. But if I started services just on one host, this problem would not happen. The error is

FRM-92102: A network error has occurred. The Forms Client has attempted to reestablish its
connection to the server 5 times(s) without success. Please check the network connection and try again later.

The Java Console log showed " oracle.forms.net.ConnectionException: 500 " error.

I believed this was something in the network, not in the EBS apps config. I worked with F5 admin on this and they found Persistence profile in F5 setups was null for this site somehow. After they set the value to two hours, this problem does not happen again in this site.

I had similar problem in another R12 site before that, when forms got FRM-92102 errors after user opened forms for a few minutes. That problem was caused by a wrong timeout value for Persistence profile in F5.

JSP files for OAF pages

Our developers coded a custom OAF (Oracle Application Framework) apps. After code files were saved to folders under $JAVA_TOP/oracle/apps/custom/Rebate/webui and .xml files were loaded into the database, webpage https://sitename.domain.com/OA_HTML/OA.jsp?page=/oracle/apps/custom/Rebate/webui/RebatePG&language_code=US&.... got error:

Error Page  You have encountered an unexpected error. Please contact the System Administrator for assistance

 I enabled Profile option "FND: Diagnostics" to see more detailed exception.

 Exception Details. 
 oracle.apps.fnd.framework.OAException: oracle.adf.mds.exception.MDSRuntimeException:
 Unable to find component with absolute reference = /oracle/apps/aear/Rebate/webui/SearchPage, XML Path = null.
 Please verify that the reference is valid and the definition of the component exists either on the File System or in the MDS Repository.
    at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:912)
    ... ... ...

We verified file $JAVA_TOP/oracle/apps/custom/Rebate/webui/SearchPage.xml does exist on in the OS folder, and then we ran a short script to upload it to the database

APPS_USER_PARAM=$1
APPS_PASS_PARAM=$2
HOST_NAME_PARAM=$3
DB_SID_PARAM=$4
DB_PORT_PARAM=$5
LOG_CONTROL=$LOG_TOP/log/SearchPage`date '+%Y%m%d%H%M'`.log  
... ... ....
 java oracle.jrad.tools.xml.importer.XMLImporter $JAVA_TOP/oracle/apps/custom/Rebate/webui/SearchPage.xml \
-username $APPS_USER_PARAM -password $APPS_PASS_PARAM -rootdir . \
-dbconnection "(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=$HOST_NAME_PARAM)(PORT=$DB_PORT_PARAM)) (CONNECT_DATA= (SID=$DB_SID_PARAM)))"

After the xml is imported successfully, below code should display its definition stored in MDS repository.

SQL> set serveroutput on
SQL> Begin
SQL> apps.jdr_utils.printdocument('/oracle/apps/custom/Rebate/webui/SearchPage');
SQL> End;

Now, after we click on the OAF page again, it works! The problem was fixed.

Additional notes:

1. all .java files in a folder can be compiled to .class file by javac. For example,

$ cd $JAVA_TOP/oracle/apps/custom/Rebate/webui
$ javac *.java

2. For JSP apps, Java .class file is not loaded into the database. All compiled JSP files are stored in $COMMON/_pages. In R12, the jsp files does not get compiled automatically. If OC4J doesn’t find the .class file in the _pages directory, it will just display a blank webpage and will not even attempt to compile the JSP. This is different from 11i. So, do not modify or delete files in folder $COMMON/_pages in R12 if you do not know how to compile JSP files.

3. The Perl script to compile JSP file is $FND_TOP/patch/115/bin/ojspCompile.pl. Run below line will clear cache and force compile all jsp pages

 $ perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush

Good documents on compiling JSP files:
JSP Pages Hanging in R12 After Removing Cached Class Files in _pages (Doc ID 433386.1)
How to Enable Automatic Compilation of JSP pages in R12 Environment (Doc ID 458338.1)

UPDATES:  How jsp file works in R12.2.10?

For R12.2, JSP file is saved in $OA_HTML (same as $EBS_APPS_DEPLOYMENT_DIR/oacore/html). They are not uploaded to database. You can use same command to compile a single custom .jsp file, such as 

$ cd $OA_HTML

$ more JDKtest.jsp

The JDK version is: <%= System.getProperty("java.version") %>

$ perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'JDKtest.jsp' --flush 

logfile set: $LOG_HOME/appl/rgf/ojsp/ojspc_error.log

starting...(compiling delta)

using 10i internal ojsp ver: 10.3.6.0

synchronizing dependency file:

  loading deplist...7874

  enumerating jsps...7875

  updating dependency...7875

  parsing jsp...7875

  writing deplist...7875

initializing compilation:

  files to compile...1

  eliminating children...1 (-0)

  searching uncompiled...1

translating and compiling:

  searching untranslated...1

  translating jsps...1/1 in 23s

  compiling jsps...1/1 in 3s

Finished!

After compilation, file jdktest.class is saved in $OA_HTML/WEB-INF/classes/_pages. But it may give error on page https://[web node]:[port]/OA_HTML/JDKtest.jsp :

Requested resource or page is not allowed in this site

This feature is controlled by new profile options in R12.2. To resolve the issue, change "Security: Allowed Resources" to ALL (and may also change "Allow Unrestricted JSP Access [FND_SEC_ALLOW_JSP_UNRESTRICTED_ACCESS] to Yes).  

Then, bounce all Apps services. Now page http://[web node]:[port]/OA_HTML/JDKtest.jsp shall say "The JDK version is: 1.7.0_xxx".

s_oacore_nprocs and s_forms_nprocs

An EBS R12.1.1 instance had a lot of database sessions kept in INACTIVE for a long time (15+ days) and never got closed. So, the number of db sessions increased daily and easily exceeded database parameter PROCESSES (which is set to 3000). I had to recycle Apps services each two weeks to kill the idle DB sessions. Most idle sessions were from "e::bes:oracle.apps.icx.security.session.created" by JDBC Thin Client. Modules AR and GL are used most in this instance.

After the instance was upgraded to R12.1.3 and its database was upgraded to 12c, the idle session problem went away surprisingly. Before that, I tried below setting parameters and did NOT get much luck.

Two documents were used to tune CONTEXT variables:
- How To Prevent Inactive JDBC Connections In Oracle Applications 11i and R12 ( Doc ID 427759.1 )
- JVM: Guidelines to setup the Java Virtual Machine in Apps Ebusiness Suite 11i and R12 ( Doc ID 362851.1 )

The number of jvms (oc4j in R12) is configured by the autoconfig variables s_oacore_nprocs, s_forms_nprocs, s_disco_nprocs, and s_xmlsvcs_nprocs. They can be updated in the $CONTEXT_FILE (using the autoconfig editor from OAM).

The document suggests use 1 JVM per CPU for 100 active connected users to OACoreGroup. Use the script to determine "active users" for OACoreGroup :

REM
REM SQL to count number of Apps 11i users
REM Run as APPS user
REM
 select 'Number of user sessions : ' || count( distinct session_id) How_many_user_sessions
  from icx_sessions icx
where disabled_flag != 'Y' and PSEUDO_FLAG = 'N'
and (last_connect + decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'), NULL,limit_time, 0,limit_time, FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT')/60)/24) > sysdate and counter < limit_connects;
REM
REM END OF SQL
REM

I got "Number of user sessions : 372" in my instance. So, I set s_oacore_nprocs to 4 to handle the volume ( assuming that 372 is peak level), and set s_forms_nprocs to 4 as well.

s_oacore_nprocs will decide how the number of sub-folders under $LOG_HOME/ora/10.1.3/j2ee/oacore

I set Heap configuration (4 parameters forms_jvm_start_options, oacore_jvm_start_options, forms_jvm_stop_options, oacore_jvm_stop_options in $CONTEXT_FILE) to
-Xmx1024M -Xms512M -XX:MaxPermSize=256M -XX:NewRatio=2 -XX:+PrintGCTimeStamps

I also added the following parameter to the DBC file:
JDBC\:oracle.jdbc.maxCachedBufferSize=262144

Changed the DBC file settings for dbc file under $FND_SECURE directory:
FND_JDBC_BUFFER_DECAY_INTERVAL=120
FND_JDBC_BUFFER_MIN=0
FND_JDBC_BUFFER_MAX=0
FND_MAX_JDBC_CONNECTIONS=256
FND_JDBC_USABLE_CHECK=true
FND_JDBC_BUFFER_DECAY_SIZE=5

But all above changes did not help much :(

UPDATE in November 2016:
After we moved EBS R12.1.3 instance to a new Linux 6 server (from Linux 5), adstrtal.sh got error when starting services.

Executing service control script:
$ADMIN_SCRIPTS_HOME/adoacorectl.sh start
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception
You are running adoacorectl.sh version 120.13
Starting OPMN managed OACORE OC4J instance  ...
****************************************************

Executing service control script:$ADMIN_SCRIPTS_HOME/adformsctl.sh start
Timeout specified in context file: 100 second(s)
script returned:
****************************************************
ERROR : Timed out( 100000 ): Interrupted Exception
You are running adformsctl.sh  version 120.16.12010000.3
Starting OPMN managed FORMS OC4J instance  ...

But I had no problem in log onto the EBS site and launching Forms.

The error (or warning) could be fixed by increasing CONTEXT FILE parameters for "Timeout specified in context file: 100 second(s)".   Since parameters s_oacore_nprocs=4 and s_forms_nprocs=4 did not help, I just re-set them back to 2 (and fnd_jdbc_usable_check to false) in $CONTEXT_FILE and ran adautocfg.sh. After that, the adstrtal.sh Timed-out error did not show up.