Run concurrent jobs on EBS host in a different account

Users need to run shell scripts to submit concurrent jobs and then do other things. If you do not want to share the Applmgr password to other users, you can set up a separate OS account for users to run their scripts. Assume Oracle EBS is installed under /u01/app/ by OS user Applmgr.

$ hostname
hostname1q
$ echo $APPL_TOP
/u01/app/EBSQA/apps/apps_st/appl
$ echo $ORACLE_HOME
/u01/app/EBSQA/apps/tech_st/10.1.2
$ echo $TWO_TASK
EBSQA

1. create user batchuser on same host hostname1q

2. install Oracle client on the same host but under a different location 

$ whoami
batchuser
$ echo $ORACLE_HOME
/batchu01/app/product/11.2.0/EBIZ
$ echo ORACLE_SID
EBSQA

3. create a file to setup the environment variables
$ whoami
batchuser
$ more setenv.ebsqa
export ORACLE_HOME=/batchu01/app/product/11.2.0/EBIZ
export ORACLE_SID=EBSQA

export TNS_ADMIN=/batchu01/app/product/11.2.0/EBIZ/network/admin
export LD_LIBRARY_PATH=/batchu01/app/product/11.2.0/EBIZ/lib
export PATH=/batchu01/app/product/11.2.0/EBIZ/bin:/usr/bin:/usr/local/bin:/etc

. /u01/app/EBSQA/apps/apps_st/appl/EBSQA_hostname1q.env

export PATH=$PATH:/batchu01/app/product/11.2.0/EBIZ/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH

echo `which CONCSUB`
echo `which sqlldr`

$ . ./setenv.ebsqa
/u01/app/EBSQA/apps/apps_st/appl/fnd/12.0.0/bin/CONCSUB
/batchu01/app/product/11.2.0/EBIZ/bin/sqlldr

4. now, submit a concurrent job by $FND_TOP/bin/CONCSUB

$ CONCSUB APPS/appsPWD SYSADMIN "System Administrator" EBSadmin WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
Submitted request 4654627 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users"
Normal completion
 
$ more $APPLCSF/log/l4654627.req
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
FNDSCURS module: Active Users
+---------------------------------------------------------------------------+
Current system time is 28-JUL-2016 14:08:59
.. ... ...

5. Troubleshooting: if you get below error, the library file may be wrong or bad. Copy the file from EBS installation folder /u01/app/EBSQA/apps/tech_st/10.1.2/lib to current library folder /batchu01/app/product/11.2.0/EBIZ/lib.

CONCSUB: error while loading shared libraries: libclntsh.so.10.1: wrong ELF class: ELFCLASS64
CONCSUB: error while loading shared libraries: libnnz10.so: cannot open shared object file: No such file or directory

~~~~~~~~~~~~~~~~~~~~ A sample script ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#!/bin/ksh

# Some variables
CONC_APPUSR=EBSBATCH    # an EBS user who runs this cm job
CONC_LOGUSR=apps
MYFA_RESP_NAME=MY_FA_Resp
MYFA_SHORTNAME=MYFA

CONTROL_LOG=$APPLPTMP/log/${EBS_SYSCODE}.log 
SECU_HOME=$HOME/ebsbatch   # <- folder holds file orauser.pwd in which has apps password.
                                  # That means who has permission to run this script will know apps password
                                  # So, in production, put it a scheduler server with high security
date >> $CONTROL_LOG
print "CONCSUB $CONC_LOGUSR $MYFA_SHORTNAME $MYFA_RESP_NAME $CONC_APPUSR WAIT=Y CONCURRENT $MYFA_SHORTNAME FAB2RPT1" >> $CONTROL_LOG
## CONCSUB apps MYFA MY_FA_Resp EBSBATCH WAIT=Y CONCURRENT MYFA FAB2RPT1
submission=$(CONCSUB $CONC_LOGUSR $MYFA_SHORTNAME \
     ${MYFA_RESP_NAME} $CONC_APPUSR WAIT=Y CONCURRENT \
     $MYFA_SHORTNAME FABLD2RPT1 "BEFORE" < $SECU_HOME/orauser.pwd)
if (( $? != 0 ))
then
   print "Customization : CONCSUB invoke failed" >> $CONTROL_LOG
   exit 99
fi

print "Customization: Invoking CM for invoice lines from AP/PO report" >> $CONTROL_LOG

#-------------------------------------------------------------------------
#  Parse out request number from concsub output. Password prompt
#  may be included depending on invoke style, so trim up to key words.
#-------------------------------------------------------------------------

reqno=$( echo ${submission#*Submitted request}|awk {'print $1'} )

#-------------------------------------------------------------------------
#  Query Concurrent Mgr table selecting status code for this reqno
#-------------------------------------------------------------------------
MY_MASTER_PWD=apps/appsPWD@EBSQA   #Or, use Oracle Wallet to hide the password

completion_code=$(sqlplus -s $MY_MASTER_PWD <<-EOF
set heading off
set verify off
select fcr.status_code
from   applsys.fnd_concurrent_requests fcr
where  fcr.request_id = $reqno
/
exit
EOF)

# Trim off white space that sometimes occurs
completion_code=$( echo $completion_code|awk {'print $1'} )

#-------------------------------------------------------------------------
#  Exit based on completion code
#-------------------------------------------------------------------------
if [[ $completion_code = C ]]
then
  print "Concurrent Manager job "$reqno" completed succesfully" >> $CONTROL_LOG
  exit 0
else
  print "Concurrent Manager job #"$reqno" did not complete succesfully" >> $CONTROL_LOG
  cat $APPLCSF/$APPLLOG/l${reqno}.req $APPLCSF/$APPLOUT/o${reqno}.out  >> $CONTROL_LOG
  exit 1
fi

exit 0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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