Send concurrent output to email by SMTP

When submit a R12.1.3 concurrent job, we can send the Output file to email directly by simply click on "Delivery Opts" => tab "Email" => Fill it with email info => Ok

Please note that the "From" field has to be an email address (it can be the same as "To:" field). Otherwise, it may fail with a generic error:

Beginning post-processing of request 32163793 on node CM_host_name at 11-JUL-2017 12:10:11.
Post-processing of request 32163793 failed at 11-JUL-2017 12:10:12 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.

Three configuration steps before the email will be delivered. Note this is not related to Workflow Mailer. When Mailer is down, concurrent job can still deliver Output to email box.

1.  Test SMTP on concurrent node. If SMTP works on the node, it shall deliver an email to yourID@outlook_name.com mailbox. Below test was on RH Linux 6:
$ mail -v yourID@outlook_name.com
Subject: hi
Hello world
done
.                           <-- dot is very important
EOT
Mail Delivery Status Report will be mailed to <xxxxxx>.

2. Ask Linux Admin to confirm that port 25 is used for SMTP on the node

[root@CM_host_name  ~]# lsof -i:25
COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
master  6656 root   12u  IPv4  18028      0t0  TCP localhost:smtp (LISTEN)
master  6656 root   13u  IPv6  18029      0t0  TCP localhost:smtp (LISTEN)
[root@CM_host_name  ~]# ps -ef|grep 6656
root       6656      1  0 May01 ?        00:00:12 /usr/libexec/postfix/master
postfix   6663    6656  0 May01 ?   00:00:02 qmgr -l -t fifo -u
postfix   87114   6656  0 09:32 ?    00:00:00 pickup -l -t fifo -u

3. Setup SMTP in EBS
a. As System Administrator responsibility, navigate to Profile => System
b. Query the %fnd%smtp% profiles
c. Set the following profile values
    FND: SMTP Host (CM_host_name or IP)
    FND: SMTP Port (port number, default 25)

Troubleshooting: Even after about 3 steps worked, the Output was still not delivered to email box. The errors in OPP files under $APPLCSF/log:

[6/16/17 12:41:39 PM] [253931:RT31303430] Beginning email delivery

[6/16/17 12:41:39 PM] [UNEXPECTED] [253931:RT31303430] javax.mail.MessagingException: Could not connect to SMTP host: CM_host_name, port: 25;
  nested exception is:
        java.net.ConnectException: Connection refused
        at com.sun.mail.smtp.SMTPTransport.openServer(SMTPTransport.java:1213)
        at com.sun.mail.smtp.SMTPTransport.protocolConnect(SMTPTransport.java:311)
        at javax.mail.Service.connect(Service.java:233)
        at javax.mail.Service.connect(Service.java:134)
        at javax.mail.Service.connect(Service.java:86)
        at com.sun.mail.smtp.SMTPTransport.connect(SMTPTransport.java:144)
        at oracle.apps.xdo.delivery.smtp.SMTPDeliveryRequestHandler.submitReques(SMTPDeliveryRequestHandler.java:781)
        at oracle.apps.xdo.delivery.AbstractDeliveryRequest.submi(AbstractDeliveryRequest.java:1270)
        at oracle.apps.fnd.cp.opp.EmailDeliveryProcessor.deliver(EmailDeliveryProcessor.java:98)
        at oracle.apps.fnd.cp.opp.DeliveryProcessor.process(DeliveryProcessor.java:91)
        at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:176)
[6/16/17 12:41:39 PM] [253931:RT31303430] Completed post-processing actions for request 31303430.

It was fixed after Linux Admin replaced localhost in postfix configuration since EBS uses CM_host_name as mailserver.
/etc/postfix/main.cf
#change from localhost to all
inet_interfaces = all
#inet_interfaces = localhost

[root@CM_host_name  ~]# lsof -i:25
COMMAND  PID USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
master  6632 root   12u  IPv4  197167      0t0  TCP *:smtp (LISTEN)
master  6632 root   13u  IPv6  197168      0t0  TCP *:smtp (LISTEN)

Change APPLSYSPUB and APPS password in R12.1.3

Extra steps are needed for changing APPLSYSPUB's password. Oracle document ID 437260.1 gives various command lines to change passwords for EBS R12.1. It also says, as of today, "Do not use any special characters in password because FNDCPASS utility does not support special characters".  In situations, we have to include special character in the password because EBS's Oracle database profile may have password complexity requirements asking for at least one special character.

I tested and found if APPLSYSPUB's password has # or $ in it, EBS forms will hang and after 10 seconds get frozen. However it works with "_" in it, and it works with upper or lower cases in it.

So, the steps to change APPLSYSPUB's password in R12.1.3 are

1. Modify $CONTEXT_FILE (on each node) and put the new password in s_gwyuid_pass.
2. Stop EBS apps services.

3. $ FNDCPASS APPS/apps_PWD 0 Y SYSTEM/[system_pwd] ORACLE APPLSYSPUB New_PWD

If it works, the Lxxxxxx.log shall say (but not always)
Working...
Password is changed successfully for user APPLSYSPUB.
AFPASSWD completed successfully. (PROGNAME=FNDCPASS)

4. run autoconfig.sh on each apps node.
(without this step, login page will not work, with errors in $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log)

After all done, APPLSYSPUB's new password is saved in text $FND_SECURE/xxx.dbc file. So, use different passwords in non-prod instances and prod instance. Note about steps will not update files $FND_TOP/resource/appsweb.cfg and $OA_HTML/bin/appsweb.cfg.

Similar steps to change all schemas' password and APPS password:
1. Verify SYSTEM password and current APPS password work.
2. Stop EBS apps services
3. Change all EBS schemas' password (optional and only if needed)
$ FNDCPASS apps/current_APPSpwd 0 Y system/'system_PWD' ALLORACLE new_schPWD
4. Change APPS password
$ FNDCPASS apps/current_APPSpwd 0 Y system/'system_PWD' SYSTEM APPLSYS new_appsPWD
5. verify the new password and make sure three key accounts are not locked
$ sqlplus apps/new_appsPWD
SQL> select username,user_id,password,account_status,lock_date,expiry_date
from dba_users where USERNAME in ('APPS', 'APPLSYSPUB', 'SYSTEM');
6. Run autoconfig on each node. (that will re-create two database links used by E-business suite)
7. Start EBS apps services.

To change one EBS schema (such as POA and CUSTOM top/schema docXX) password:

$ FNDCPASS apps/appsPWD 0 Y system/system_PWD ORACLE POA newPOA_PWD

$ FNDCPASS apps/appsPWD 0 Y system/system_PWD ORACLE docXX newDOCXX_PWD

Troubleshooting:
After APPS (and APPLSYSPUB) password was changed, EBS failed in re-directing to the login page. In Firefox, the login page https://ebssite.domain.com/OA_HTML/AppsLogin shows generic error
500 Internal Server Error
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.
Page https://https://ebssite.domain.com/OA_HTML/ServletPing also does not work.
I followed Doc ID 1926189.1 (Login Page Cannot Be Accessed After Changing The APPLSYSPUB Password) without seeing "lock" keyword in any application.log (by command  locate application.log | egrep -i 'lock'). But I did find APPLSYSPUB account was locked. Seems some process kept trying the account with old password and made it "LOCKED(TIMED)". No any errors in logs by adstrtal.sh.

Here are lines from $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/server.log
17/06/23 08:40:26.286 Internal server error
java.lang.NoClassDefFoundError: Could not initialize class oracle.apps.fnd.common.WebAppsContext
        at oracle.apps.jtf.cache.ArchitectureWrapper.createAppsContextWithDBCFile(ArchitectureWrapper.java:143)
 Lines from $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
17/06/23 08:10:21.926 html: Error initializing servlet
java.lang.ExceptionInInitializerError
        at oracle.apps.fnd.sso.Utils.getAppsContext(Utils.java:549)
        at oracle.apps.fnd.sso.SessionMgr.loadInstalledLanguages(SessionMgr.java:4058)
        at oracle.apps.fnd.sso.SessionMgr.getInstalledLanguages(SessionMgr.java:3852)
        at oracle.apps.fnd.sso.Utils.getBaseInstalledLangCode(Utils.java:1490)
        at oracle.apps.fnd.sso.Authenticator.<clinit>(Authenticator.java:45)
        at oracle.apps.fnd.sso.Utils.<clinit>(Utils.java:674)

If  APPLSYSPUB account was locked after EBS services were started, end-users will suddenly be unable to launch forms. The forms popup will stay frozen forever when uses try to open it.

Also see http://erpondb.blogspot.com/2016/11/fndcpass-and-app-fnd-02704.html about special character in password.

oacore OC4J logfile application.log

$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log got 8GB in size due to 10.1.3.5.0 Container error entries (bug 10126440 ? ).

$ find . -type f -size +500000 -exec ls -alh {} \;
-rw-r----- 1 ebsuser users 8.1G April 17 13:55
./EBSP/inst/apps/EBSP_note1p/logs/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log
I tried to rename application.log, but oacore process kept writing entries to the re-named file. I also deleted it and found oacore process did not create a new one while the space was still getting filled up and "find" command did not find which file it write the log entries to.

The fix is to re-name/delete application.log file and then bounce OACORE process which will create a new application.log file.

$ ps -ef | grep oacore| wc -l
4
(in my another instance, it gets 6)

$ cd $ADMIN_SCRIPTS_HOME

$ ./adoacorectl.sh stop

$ ./adopmnctl.sh status
You are running adopmnctl.sh version 120.6.12010000.5
Checking status of OPMN managed processes...
Processes in Instance: xxxx.com
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |   20423 | Alive
OC4JGroup:default_group          | OC4J:forms         |   20354 | Alive
OC4JGroup:default_group          | OC4J:oacore       |     N/A | Down
HTTP_Server                                | HTTP_Server      |   20151 | Alive

adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /u02/app/EBSPROD/inst/apps/<CONTEXT_NAME>/logs/appl/admin/log/adopmnctl.txt for more information ...

$ ps -ef | grep oacore| wc -l
3

$ cd $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1

$ mv application.log application.log_Delete

$ cd $ADMIN_SCRIPTS_HOME

$ ./adoacorectl.sh start
You are running adoacorectl.sh version 120.13
Starting OPMN managed OACORE OC4J instance  ...
adoacorectl.sh: exiting with status 0
adoacorectl.sh: check the logfile $LOG_HOME/appl/admin/log/adoacorectl.txt for more information ...

$ ./adopmnctl.sh status
---------------------------------+--------------------+---------+---------
ias-component                    | process-type       |     pid | status
---------------------------------+--------------------+---------+---------
OC4JGroup:default_group          | OC4J:oafm          |   20423 | Alive
OC4JGroup:default_group          | OC4J:forms         |   20354 | Alive
OC4JGroup:default_group          | OC4J:oacore       |   23513 | Alive
HTTP_Server                              | HTTP_Server      |   20151 | Alive

adopmnctl.sh: exiting with status 0
adopmnctl.sh: $LOG_HOME/appl/admin/log/adopmnctl.txt for more information ...

$ ps -ef | grep oacore| wc -l
4

$ cd $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1

$ more application.log
17/04/19 23:38:50.411 10.1.3.5.0 Started
17/04/19 23:38:51.979 html: 10.1.3.5.0 Started

Apache does not start due to bad group name

When I logged onto a server to clone an EBS instance from production, it gave a message

id: cannot find name for group ID 148.

"id" command shows group name is missing.
$ id
uid=1172(ebsdev) gid=148() groups=148(ebs)

I ignored the message and completed the clone. After that, Apache service did not start and even did not create any log files in $LOG_HOME/ora/10.1.3/Apache folder. The only error is in $LOG_HOME/ora/10.1.3/opmn/HTTP_Server~1.log :

apachectl startssl: execing httpd
httpd: bad group name 148

I realized where the problem came from and asked Linux Admin to fix the Group name issue.

$ id
uid=1172(ebsdev) gid=148(ebs) groups=148(ebs)

Then, I replaced 148 with the group name in file $ORA_CONFIG_HOME/10.1.3/Apache/Apache/conf/httpd.conf

User ebsdev
Group ebs

I also verified file /etc/group is READable. After that, I started Apache without problem.

Concurrent job out of memory issue

Create Accounting (XLAACCPB) Program in one of our instances gave below error in log file:

Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***

Oracle Support recommended below action plan in a SR. That fixed the issue.

Action Plan:
 ==================
 1. In  Profile, please change following setting at ALL levels in System option:
 FND: Debug Log Enabled => NO
 AR: Enable Debug Message Output => NO
 SLA: Enable Diagnostics => No

2. Doc ID 1350719.1
 Access the "System Administrator" responsibility.

 Navigate to Concurrent > Program > Define.
 Search for the report (e.g., Create Accounting)
 Click on Parameters button.
 Move to the last parameter.

 Add the Scalable Flag as follows -

 Parameter- P_SCALABLE_FLAG
 Description - ScalableFlag
 Enabled - Check
 Value Set - XLA_SRS_NO_VALIDATION
 Default Type - Constant
 Default Value - Y
 Prompt - P_SCALABLE_FLAG
 Token - ScalableFlag (Case sensitive. Give this exact value)

 P_SCALABLE_FLAG - has a Default Value of 'Y'

3.
 A>  Increase the memory for Java used by the concurrent program.
Query the concurrent program in
 System Administrator> Concurrent> Program> Define >
 Query for the Create Accounting concurrent program >
 Enter value for Options field with : -Xmx2048M  (Note: Don't miss the dash before the X) .

 B> Time out parameters (Profile)
 Concurrent:OPP Response Timeout : 240
 Concurrent:OPP Process Timeout :   10800 sec (3 hours)

Additional Notes: Oracle Doc ID 838810.1 provides a similar solution to fix below error and it worked in my instance.

Subledger Accounting: Version : 12.0.0
XLAPEXRPT module: Subledger Period Close Exceptions Report
Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***

Set the following parameters in the concurrent program definition:
Under Concurrent > Program > Define, query for concurrent program
short name = XLAPEXRPT and application = Subledger Accounting.

1. In the 'Options' field enter -Xmx2048M
2. Add a parameter to the concurrent token = ScalableFlag, default value set to 'Y'. Same way
you add DebugFlag
In the application developer responsibility
-> Concurrent -> Program -> Define
-> Query for short name= XLAPEXRPT
-> Click on Parameters
->define a new parameter (if not existing)
parameter name=P_DEBUG_FLAG, attach any valueset that is YES_NO valueset.
Give Default value=Y
Token=DebugFlag
Save
3. Submit the request with scalable mode Yes.