Before Linux 5 support becomes expired, we have to upgrade it. But, RHEL5 does not have an option to "upgrade" it on the same server. We had to build a fresh new Linux 6 server and then migrate Oracle EBS R12.1.3 to the new RHEL6 server.
1. Server requirements by Oracle
Linux Admin followed Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 (Doc ID 761566.1) to install all required components.
- list of RPMs
To make openmotif21 installation work, follow Doc ID 2057494.1 (Oracle Linux: Dependency Resolution Error "Package oracle-ebs-server-R12-preinstall requires openmotif21 and that has been obsoleted by lesstif") to delete "lesstif" package from Linux server first. Some of the installed RPMs:
$ rpm -q openmotif
package openmotif is not installed
$ rpm -q openmotif21
openmotif21-2.1.30-11.EL6.i686
$ rpm -q xorg-x11-libs-compat
xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386
$ rpm -q libXrender
libXrender-0.9.8-2.1.el6_8.1.x86_64
libXrender-0.9.8-2.1.el6_8.1.i686
... ... ... ...
Also, download and install Oracle patch 6078836 (p6078836_101330_LINUX.ZIP) to Linux.
$ ls -al /usr/lib/libdb.so.2
-rw-r--r-- 1 root root 5825 Sep 6 21:12 /usr/lib/libdb.so.2
Without it, starting Apache will fail with error:
$IAS_ORACLE_HOME/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
- maintenance tools:
ar, gcc, g++, ksh, ld, linux32, make, XDisplay Server
- kernel settings (in /ect/sysctl.conf)
- Number of Open File Descriptors (/etc/security/limits.conf)
* hard nofile 65535
* soft nofile 4096
2. Steps during the migration
2.1 Stop EBS apps clearly on old hosts and run a file counts
$ find EBS_DIR -type f | wc -l
2.2 Wait Linux Admin to rename the new server name to the "old" server name, and do a sync on file systems including user accounts, cron jobs, etc.
2.3 On the new host, make sure it connects to the right database
$ sqlpuse apps/appsPWD
SQL> select instance_name from v$instance
2.4 Run autoconfig on each nodes
$ cd $ADMIN_SCRIPTS_HOME
$ ./adautocfg.sh
2.5. To avoid "adoafmctl.sh (&adoacorectl.sh): exiting with status 150"
$ mv $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence_DELETE
$ mkdir $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/persistence
$ mv $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence_DELETE
$ mkdir $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/persistence
$ mv $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence_DELETE
$ mkdir $ORA_CONFIG_HOME/10.1.3/j2ee/forms/persistence
If still getting "status 150", stop all services cleanly and start them again.
2.6 Open a F5 ticket to change old IP address to new one in F5.
2.7 Start apps services
With above steps in many instances I did not get any problem in starting EBS services.
2.8. Copy file libgcc_s-2.3.2-stub.so from patch 12415211 ( see Doc ID 761566.1)
3. Troubleshooting
3.1 scp and sftp failed with error:
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: POSSIBLE DNS SPOOFING DETECTED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
The RSA host key for host_name.domain.com has changed,
and the key for the according IP address 177.321.89.56
is unchanged. This could either mean that
DNS SPOOFING is happening or the IP address for the host
and its host key have changed at the same time.
Offending key for IP in /users/userID/.ssh/known_hosts:28
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that the RSA host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
e4:5d:1c:12:e5:ab:f5:03:b4:cd:d8:cd:eb:f3:a7:23.
Please contact your system administrator.
Add correct host key in /users/userID/.ssh/known_hosts to get rid of this message.
Offending key in /users/userID/.ssh/known_hosts:7
RSA host key for host_name.domain.com has changed and you have requested strict checking.
Host key verification failed.
lost connection
Fix: $ ssh-keygen -R host_name.domain.com
3.2 Environment variable $PATH does not have the path to its home (such as /users/userID) included
Symptom: Scheduled jobs does not run because scheduler account logs in but does not "see" the script at all.
Fix: add "export PATH=$PATH:$HOME" to file .bashrc
3.3 Shell script can see env variable $HOME.
Symptom: command, such as "cd $HOME", failed.
Fix: hard code the path in the script. (not a good solution).
3.4 When a directory was removed and the old path still exists in the PATH variable, executing a file may get below misleading error. I had to remove the obsolete path from the PATH string.
$ . run_environment
-ksh: . /users/userID/run_environment: not found [No such file or directory]
3.5 RedHat RHEL6 changes mailx on using -a to email attachment. The right syntax:
$ echo "test_body" | mailx -s test-email -a test_attachment.txt emailid@youemail.com
NOTES on Linux commands:
1) Linux version
$ uname -a
Linux host_name.domain.com 2.6.32-642.3.1.el6.x86_64 #1 SMP Sun Jun 26 18:16:44 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.8 (Santiago)
2) Linux command to find CUP info
$ cat /proc/cpuinfo
$ cat /proc/cpuinfo | grep processor
processor : 0
processor : 1
3) Linux command to find total memory
$ cat /proc/meminfo
MemTotal: 20586472 kB
MemFree: 1406228 kB
Buffers: 873268 kB
Cached: 8353596 kB
SwapCached: 11332 kB
Active: 15413552 kB
Inactive: 2228904 kB
Active(anon): 7687868 kB
Inactive(anon): 729196 kB
Active(file): 7725684 kB
Inactive(file): 1499708 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 8388604 kB
SwapFree: 8288296 kB
... ...
$ free
total used free shared buffers cached
Mem: 20586472 19607488 978984 1504 876480 8510684
-/+ buffers/cache: 10220324 10366148
Swap: 8388604 100308 8288296
(note this instance has more then 350 EBS apps sessions in normal business hours)
Friday, December 2, 2016
Thursday, November 17, 2016
FNDCPASS and APP-FND-02704
When I tried to change apps password by FNDCPASS in R12.1.3, it hang for a while and then the log file shows APP-FND-02704 error.
$ FNDCPASS apps/oldAppsPWD 0 Y system/system_PWD SYSTEM APPLSYS N3WAqt_$EBS
APP-FND-02704: Unable to alter user APPS to change password.
The error does not tell the true cause. The problem in my case is the new password does not meet new rules added by DBA to APPS' profile for password security or single quotes are necessary to make FNDCPASS work with special character in the password.
$ FNDCPASS apps/oldAppsPWD 0 Y system/system_PWD SYSTEM APPLSYS 'N3WAqt_$EBS'
If the system_PWD has special character with it, single quotes are necessary. Otherwise FNDCPASS may not throw out an meaningful error but just does not change apps password.
Also when the password includes $ character, single quotes around it are needed to make sqlplus connection on Linux prompt work. Without it, it will give ORA-01017 error.
$ sqlplus apps/N3WAqt_$EBS
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 17 18:57:12 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
$ sqlplus apps/'N3WAqt_$EBS'
Connected to: ....
Or, get into sqlplus first and then connect to apps
$ sqlplus /nolog
SQL> conn apps/N3WAqt_$EBS
Connected.
If the password does not include $ character, the single quotes are not necessary for sqlplus:
$ FNDCPASS apps/'N3WAqt_$EBS' 0 Y system/system_PWD SYSTEM APPLSYS 'Cr8ze#p0Wd'
$ sqlplus apps/Cr8ze#p0Wd
Connected to: ....
Craze $
Additional notes:
$ FNDCPASS apps/oldAppsPWD 0 Y system/system_PWD SYSTEM APPLSYS N3WAqt_$EBS
APP-FND-02704: Unable to alter user APPS to change password.
The error does not tell the true cause. The problem in my case is the new password does not meet new rules added by DBA to APPS' profile for password security or single quotes are necessary to make FNDCPASS work with special character in the password.
Below line changes apps password to N3WAqt_$EBS. Note the single quotes.
$ FNDCPASS apps/oldAppsPWD 0 Y system/system_PWD SYSTEM APPLSYS 'N3WAqt_$EBS'
If the system_PWD has special character with it, single quotes are necessary. Otherwise FNDCPASS may not throw out an meaningful error but just does not change apps password.
Also when the password includes $ character, single quotes around it are needed to make sqlplus connection on Linux prompt work. Without it, it will give ORA-01017 error.
$ sqlplus apps/N3WAqt_$EBS
SQL*Plus: Release 10.1.0.5.0 - Production on Thu Nov 17 18:57:12 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:
$ sqlplus apps/'N3WAqt_$EBS'
Connected to: ....
Or, get into sqlplus first and then connect to apps
$ sqlplus /nolog
SQL> conn apps/N3WAqt_$EBS
Connected.
If the password does not include $ character, the single quotes are not necessary for sqlplus:
$ FNDCPASS apps/'N3WAqt_$EBS' 0 Y system/system_PWD SYSTEM APPLSYS 'Cr8ze#p0Wd'
$ sqlplus apps/Cr8ze#p0Wd
Connected to: ....
Craze $
Additional notes:
1. FNDCPASS may also give misleading error from changing password when db parameter sec_case_sensitive_logon is set to TRUE.
2. If the new password does not meet the complexity requirements by Oracle database, FNDCPASS will not change APPS password and does not tell what is the real problem. It only says
Working...
APP-FND-02704: Unable to alter user APPS to change password.
Oracle error 28003: has been detected in alterpassword2.
You have to have more complexity in the new password to make it work!
3. When I used FNDCPASS to change APPS password, it refused to do so and give strange error:
FNDCPASS was not able to decrypt password for user 'ABCD1' during applsys password change.
FNDCPASS was not able to decrypt password for user 'ABCD2' during applsys password change.
FNDCPASS was not able to decrypt password for user 'ABCD3' during applsys password change.
ABCD1, ABCD2 and ABCD3 are not database account at all. But they are EBS users in inactive status for many years. Apparently FNDCPASS does not like their EBS password or security. The fix is to change their EBS password first, and then FNDCPASS is able to change APPS password.
Tuesday, November 15, 2016
EBS Mailer status and troubleshooting
Some times, users do not get emails/alerts from EBS R12.1.3 even workflow Mailer is up and running. Table wf_notifications may give different status:
RECIPIENT_ROLE MESSAGE_TYPE STATUS MAIL_STATUS TO_USER
APINVGR APINVAPR OPEN FAILED AP GROUP
APINVGR APINVAPR OPEN MAIL AP GROUP
APINVGR APINVAPR OPEN SENT AP GROUP
APINVGR APINVAPR CLOSED SENT AP GROUP
Seems to me that only mails with MAIL_STATUS "SENT" were sent out successfully. If it stays in "MAIL", the Mailer may get stuck somewhere and may need a re-start.
Below query checks the email status:
SELECT notification_id N_ID, to_char(begin_date, 'mm-dd-yyyy hh24:mi:ss') beginDate, recipient_role, message_type, message_name, status, mail_status, context, to_user, subject
FROM wf_notifications
WHERE begin_date > sysdate - 5 -- List only emails within 5 days
ORDER by notification_id desc;
Here is a notification with FAILED status. Note: the record values could be dynamically changed with approval workflow's move.
N_ID RECIPIENT_ROLE MESSAGE_TYPE STATUS MAIL_STATUS CONTEXT TO_USER
15331513 APINVGR APINVAPR OPEN FAILED APINVAPR:11345495_1:116020 AP GROUP
To troubleshoot the failed notification, find and check the Workflow status first to see if any error.
You shall verify user APINVGR status and email address are correct.
Also try the Mailer logs in $APPLCSF/$APPLLOG to see if you are lucky to get some errors.
$ ls -lrt $APPLCSF/$APPLLOG/FNDCP*.txt
$ grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
$ grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
$ grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log
If the logs do not tell much, Doc ID 1051421.1 is a good document to follow on enabling debug for Mailer Service Component :
- In OAM : Site Map (or, WF Manager) > Notification Mailer > Edit > Advanced > Go to Step 2 > Change "Log Level" to "Statement" > Apply
- Bounce the Mailer container to generate a fresh log file
In OAM: Site Map > Generic Services > under Status Overview > Next (twice) > Generic Service Component Container > select Workflow Mailer Service > Restart (from the dropdown on the top).
(or, Dashboard > dropdown "Application Services" Go > Next ... to find Generic Service Component Container > ... )
Note it may take 5 minutes or so for a re-start cycle.
Even Workflow Mailer Service is running, Notification Mailer could be down if two $CONTEXT_FILE parameters are not right, because autoconfig will overwrite them if company's SMTP server name, e.g. mail.comany_name.com, is only entered under Outpound EMail Account section on OAM webpage. The correct way is to define them in $CONTEXT_FILE:
<hostname oa_var="s_smtphost">mail</hostname>
<domain oa_var="s_smtpdomainname">company_name.com</domain>
</oa_smtp_server>
If individual users do not receive notifications or their mails have Failed status, check two tables to see if their preference is turned off (disabled)which could happen when the Workflow Mailer attempts to send a notification to an invalid email address or when email server crashed and caused an outage of hours (Doc ID 360541.1).
SQL> select * from FND_USER_PREFERENCES where preference_value = 'DISABLED';
SQL> select * from WF_LOCAL_ROLES where notification_preference = 'DISABLED';
To enable it in FND_USER_PREFERENCES: log onto EBS as user who has the issue > click on Preferences > scroll down to Notifications > Email Style > dropdown "HTML mail with attachments"
To populate above change to WF_LOCAL_ROLES and WF_ROLES:
as System Administrator Responsibility > Define User form> query up user with issue:
a. Remove the Person Field > save.
b. Re-query the same user > add back the Person Field > Save
c. This effort does an internal synchronization for the particular user.
Notes on synchronizing workflow tables:
1. In my R12.1.3 instances, above actions synchronizes Status column in tables FND_USER and WF_LOCAL_ROLES. Doc 1213304.1 has info on three sync concurrent programs. In 11i, the synchronization seems more difficult. See Doc 728331.1 and Doc 364647.1.
2. Two steps are necessary to change a user's email address and synchronize it to workflow tables. Prior to about steps on Define User form, go to Setup (mostly under a Purchase/PO responsibility) > Personal > Employee form > update user's email address first.
3. If the Description column is out-of-date in table wf_local_roles, most likely newer data in table PER_ALL_PEOPLE_F is not populated to WF tables. The solution is to run concurrent program 'Synchronize WF LOCAL tables' with parameter FND_USR. That synchronized WF tables in my instances.
After this, run concurrent program Resend Failed/Error Workflow Notifications to resend those FAILED notifications.
This program should send FAILED records out from table wf_notifications. In my case, account APINVGR uses a service email address and each time after Mailer sends a notification to it, its notification_preference becomes DISABLED for unknown reason. After I replace that email address with a real user's email address, the problem is resolved.
If Workflow Mailer stopped, workflow notification will not be sent. Below query shows Workflow Mailer is not running, it needs a fix:
SQL> SELECT component_name, component_status, component_status_info
FROM fnd_svc_components_v
WHERE component_name like 'Workflow%';
component_name component_status component_status_info
------------------------------------------------------------- ----------------------- --------------
Workflow Deferred Agent Listener RUNNING
Workflow Deferred Notification Agent Listener RUNNING
Workflow Error Agent Listener RUNNING
Workflow Inbound JMS Agent Listener STOPPED
Workflow Inbound Notifications Agent Listener RUNNING
Workflow Java Deferred Agent Listener RUNNING
Workflow Java Error Agent Listener RUNNING
Workflow Notification Mailer DEACTIVATED_SYSTEM xxxxx
Doc ID 562551.1 has useful queries. If you want to check into an individual notification, get the NOTIFICATION_ID from above query result and run below quest to see its status from tables WF_NOTIFICATIONS, WF_DEFERRED and WF_NOTIFICATION_OUT.
NOTE: a Failed notification will not reach WF_NOTIFICATION_OUT table
SQL> SELECT
n.begin_date, n.status, n.mail_status, n.recipient_role, de.def_enq_time, de.def_deq_time,
de.def_state, ou.out_enq_time, ou.out_deq_time, ou.out_state
FROM applsys.wf_notifications n,
(SELECT d.enq_time def_enq_time,
d.deq_time def_deq_time,
TO_NUMBER((SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
msg_state def_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
(SELECT o.deq_time out_deq_time,
o.enq_time out_enq_time,
TO_NUMBER((SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
msg_state out_state
FROM applsys.aq$wf_notification_out o) ou
WHERE n.notification_id = 15331600
AND n.notification_id = de.d_notification_id(+)
AND n.notification_id = ou.o_notification_id(+) ;
Check if there are stuck messages in The PROCESS Folder:
SQL> SELECT trunc(win.enq_time) PROCESSED_TIME, wfn.status NOTIFICATION_STATUS, COUNT(*) NO_EMAILS
FROM (select wi.msgid, wi.corrid, wi.enq_time, wi.state, wi.sender_name,
(select str_value from table(wi.user_data.header.properties) where name = 'NOTIFICATION_ID') notification_id
from apps.WF_NOTIFICATION_IN wi) win, apps.WF_ERROR werr, apps.wf_notifications wfn
where win.notification_id = wfn.notification_id
and win.msgid = werr.msgid (+)
--and win.enq_time >= sysdate - 4/24 -- In the Process Folder for more than 4 hours
and wfn.status = 'OPEN'
group by trunc(win.enq_time), wfn.status;
RECIPIENT_ROLE MESSAGE_TYPE STATUS MAIL_STATUS TO_USER
APINVGR APINVAPR OPEN FAILED AP GROUP
APINVGR APINVAPR OPEN MAIL AP GROUP
APINVGR APINVAPR OPEN SENT AP GROUP
APINVGR APINVAPR CLOSED SENT AP GROUP
Seems to me that only mails with MAIL_STATUS "SENT" were sent out successfully. If it stays in "MAIL", the Mailer may get stuck somewhere and may need a re-start.
Below query checks the email status:
SELECT notification_id N_ID, to_char(begin_date, 'mm-dd-yyyy hh24:mi:ss') beginDate, recipient_role, message_type, message_name, status, mail_status, context, to_user, subject
FROM wf_notifications
WHERE begin_date > sysdate - 5 -- List only emails within 5 days
ORDER by notification_id desc;
Here is a notification with FAILED status. Note: the record values could be dynamically changed with approval workflow's move.
N_ID RECIPIENT_ROLE MESSAGE_TYPE STATUS MAIL_STATUS CONTEXT TO_USER
15331513 APINVGR APINVAPR OPEN FAILED APINVAPR:11345495_1:116020 AP GROUP
To troubleshoot the failed notification, find and check the Workflow status first to see if any error.
You shall verify user APINVGR status and email address are correct.
Also try the Mailer logs in $APPLCSF/$APPLLOG to see if you are lucky to get some errors.
$ ls -lrt $APPLCSF/$APPLLOG/FNDCP*.txt
$ grep ":ERROR:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrerr.log
$ grep "Exception:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrexc.log
$ grep ":UNEXPECTED:" $APPLCSF/$APPLLOG/FNDCPGSC*.txt > mlrunexp.log
If the logs do not tell much, Doc ID 1051421.1 is a good document to follow on enabling debug for Mailer Service Component :
- In OAM : Site Map (or, WF Manager) > Notification Mailer > Edit > Advanced > Go to Step 2 > Change "Log Level" to "Statement" > Apply
- Bounce the Mailer container to generate a fresh log file
In OAM: Site Map > Generic Services > under Status Overview > Next (twice) > Generic Service Component Container > select Workflow Mailer Service > Restart (from the dropdown on the top).
(or, Dashboard > dropdown "Application Services" Go > Next ... to find Generic Service Component Container > ... )
Note it may take 5 minutes or so for a re-start cycle.
Even Workflow Mailer Service is running, Notification Mailer could be down if two $CONTEXT_FILE parameters are not right, because autoconfig will overwrite them if company's SMTP server name, e.g. mail.comany_name.com, is only entered under Outpound EMail Account section on OAM webpage. The correct way is to define them in $CONTEXT_FILE:
<hostname oa_var="s_smtphost">mail</hostname>
<domain oa_var="s_smtpdomainname">company_name.com</domain>
</oa_smtp_server>
If individual users do not receive notifications or their mails have Failed status, check two tables to see if their preference is turned off (disabled)which could happen when the Workflow Mailer attempts to send a notification to an invalid email address or when email server crashed and caused an outage of hours (Doc ID 360541.1).
SQL> select * from FND_USER_PREFERENCES where preference_value = 'DISABLED';
SQL> select * from WF_LOCAL_ROLES where notification_preference = 'DISABLED';
To enable it in FND_USER_PREFERENCES: log onto EBS as user who has the issue > click on Preferences > scroll down to Notifications > Email Style > dropdown "HTML mail with attachments"
To populate above change to WF_LOCAL_ROLES and WF_ROLES:
as System Administrator Responsibility > Define User form> query up user with issue:
a. Remove the Person Field > save.
b. Re-query the same user > add back the Person Field > Save
c. This effort does an internal synchronization for the particular user.
Notes on synchronizing workflow tables:
1. In my R12.1.3 instances, above actions synchronizes Status column in tables FND_USER and WF_LOCAL_ROLES. Doc 1213304.1 has info on three sync concurrent programs. In 11i, the synchronization seems more difficult. See Doc 728331.1 and Doc 364647.1.
2. Two steps are necessary to change a user's email address and synchronize it to workflow tables. Prior to about steps on Define User form, go to Setup (mostly under a Purchase/PO responsibility) > Personal > Employee form > update user's email address first.
3. If the Description column is out-of-date in table wf_local_roles, most likely newer data in table PER_ALL_PEOPLE_F is not populated to WF tables. The solution is to run concurrent program 'Synchronize WF LOCAL tables' with parameter FND_USR. That synchronized WF tables in my instances.
After this, run concurrent program Resend Failed/Error Workflow Notifications to resend those FAILED notifications.
If Workflow Mailer stopped, workflow notification will not be sent. Below query shows Workflow Mailer is not running, it needs a fix:
SQL> SELECT component_name, component_status, component_status_info
FROM fnd_svc_components_v
WHERE component_name like 'Workflow%';
component_name component_status component_status_info
------------------------------------------------------------- ----------------------- --------------
Workflow Deferred Agent Listener RUNNING
Workflow Deferred Notification Agent Listener RUNNING
Workflow Error Agent Listener RUNNING
Workflow Inbound JMS Agent Listener STOPPED
Workflow Inbound Notifications Agent Listener RUNNING
Workflow Java Deferred Agent Listener RUNNING
Workflow Java Error Agent Listener RUNNING
Workflow Notification Mailer DEACTIVATED_SYSTEM xxxxx
Doc ID 562551.1 has useful queries. If you want to check into an individual notification, get the NOTIFICATION_ID from above query result and run below quest to see its status from tables WF_NOTIFICATIONS, WF_DEFERRED and WF_NOTIFICATION_OUT.
NOTE: a Failed notification will not reach WF_NOTIFICATION_OUT table
SQL> SELECT
n.begin_date, n.status, n.mail_status, n.recipient_role, de.def_enq_time, de.def_deq_time,
de.def_state, ou.out_enq_time, ou.out_deq_time, ou.out_state
FROM applsys.wf_notifications n,
(SELECT d.enq_time def_enq_time,
d.deq_time def_deq_time,
TO_NUMBER((SELECT VALUE
FROM TABLE(d.user_data.parameter_list)
WHERE NAME = 'NOTIFICATION_ID')) d_notification_id,
msg_state def_state
FROM applsys.aq$wf_deferred d
WHERE d.corr_id = 'APPS:oracle.apps.wf.notification.send') de,
(SELECT o.deq_time out_deq_time,
o.enq_time out_enq_time,
TO_NUMBER((SELECT str_value
FROM TABLE(o.user_data.header.properties)
WHERE NAME = 'NOTIFICATION_ID')) o_notification_id,
msg_state out_state
FROM applsys.aq$wf_notification_out o) ou
WHERE n.notification_id = 15331600
AND n.notification_id = de.d_notification_id(+)
AND n.notification_id = ou.o_notification_id(+) ;
Check if there are stuck messages in The PROCESS Folder:
SQL> SELECT trunc(win.enq_time) PROCESSED_TIME, wfn.status NOTIFICATION_STATUS, COUNT(*) NO_EMAILS
FROM (select wi.msgid, wi.corrid, wi.enq_time, wi.state, wi.sender_name,
(select str_value from table(wi.user_data.header.properties) where name = 'NOTIFICATION_ID') notification_id
from apps.WF_NOTIFICATION_IN wi) win, apps.WF_ERROR werr, apps.wf_notifications wfn
where win.notification_id = wfn.notification_id
and win.msgid = werr.msgid (+)
--and win.enq_time >= sysdate - 4/24 -- In the Process Folder for more than 4 hours
and wfn.status = 'OPEN'
group by trunc(win.enq_time), wfn.status;
Wednesday, October 19, 2016
FTP and SFTP
1. Run ftp using a specific account interactively
$ ftp -inv remote_host.domain.com
Connected to remote_host.domain.com (10.133.67.38).
220 Microsoft FTP Service
Remote system type is Windows_NT.
ftp> user ftpuser
331 Password required for ftpuser.
Password:
230 User logged in.
ftp>
Note below line does not work:
$ ftp ftpuser@remote_host.domain.com
ftp: ftpuser@remote_host.domain.com: Name or service not known
ftp> exit
2. Run FTP in a shell script
Assume FTP login info is saved in file $HOME/.netpw in format:
put $LOCAL_PATH/output.txt ouput1.txt
bye
EndFTP
}
3. Try "man ftp" for help.
4. SFTP options:
$ sftp -oport=2222 ftpuser@remote_host.domain.com
Connecting to remote_host.domain.com...
WARNING: Logon attempts are audited. Access and use allowed for authorized purposes only. Violators will be prosecuted
$ sftp -oIdentityFile=/path/to/sftp_key/id_dsa ftpuser@remote_host.domain.com
Also see https://erpondb.blogspot.com/2015/11/run-scp-or-sftp-without-password.html
$ ftp -inv remote_host.domain.com
Connected to remote_host.domain.com (10.133.67.38).
220 Microsoft FTP Service
Remote system type is Windows_NT.
ftp> user ftpuser
331 Password required for ftpuser.
Password:
230 User logged in.
ftp>
Note below line does not work:
$ ftp ftpuser@remote_host.domain.com
ftp: ftpuser@remote_host.domain.com: Name or service not known
ftp> exit
2. Run FTP in a shell script
Assume FTP login info is saved in file $HOME/.netpw in format:
machine WintEdi.domain.COM login ANET/userID password userPWD
REMOTE_PATH=......
LOCAL_PATH=......
REMOTE_MACHINE=WintEdi.domain.COM
REMOTE_PATH=......
LOCAL_PATH=......
REMOTE_MACHINE=WintEdi.domain.COM
function ftp_to_remote
{
ftpuserid=`grep $REMOTE_MACHINE $HOME/.netpw|cut -f4 -d' '`
ftpuserpwd=`grep $REMOTE_MACHINE $HOME/.netpw|cut -f6 -d' '`
ftp -inv <<EndFTP
open $REMOTE_MACHINE
user $ftpuserid $ftpuserpwd
cd $REMOTE_PATHput $LOCAL_PATH/output.txt ouput1.txt
bye
EndFTP
}
3. Try "man ftp" for help.
4. SFTP options:
$ sftp -oport=2222 ftpuser@remote_host.domain.com
Connecting to remote_host.domain.com...
WARNING: Logon attempts are audited. Access and use allowed for authorized purposes only. Violators will be prosecuted
$ sftp -oIdentityFile=/path/to/sftp_key/id_dsa ftpuser@remote_host.domain.com
Also see https://erpondb.blogspot.com/2015/11/run-scp-or-sftp-without-password.html
Troubleshhot UTL_FILE error
Ran a PL/SQL code and got error message:
ORA-06512: at
"SYS.UTL_FILE", line 536
ORA-29283: invalid file ope
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 90
Two steps to troubleshoot the error:
A. Find which directory it tries to write the file to, and verify it is defined in the database:
ORA-06512: at
"SYS.UTL_FILE", line 536
ORA-29283: invalid file ope
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 90
Two steps to troubleshoot the error:
A. Find which directory it tries to write the file to, and verify it is defined in the database:
SQL> set pages 100 lines 100
SQL> select * from dba_directories where directory_name='FTPOUT';
The directory could be owned by SYS. But APPS should be granted READ and WRITE.
SQL> SELECT * FROM dba_tab_privs WHERE table_name = 'FTPOUT';
B. Run a short code as APPS to test it. If it works, file test_UTL.txt shall create in the directory.
SQL> set serveroutput on
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
v_error_msg varchar2(1000);
BEGIN
fileHandler := UTL_FILE.FOPEN('FTPOUT', 'test_UTL.txt', 'W');
UTL_FILE.PUT_LINE(fileHandler, 'Writing TO a test file\n');
UTL_FILE.PUTF(fileHandler, 'Writing 2nd line to test file\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20001, 'ERROR: Invalid PATH for the file.');
when others then
v_error_msg := substr(sqlerrm,1,100);
utl_file.fclose(fileHandler);
dbms_output.put_line('Error message is - ' || v_error_msg);
raise;
END;
/
A MAX linesize written into file test_UTL.txt can be specified in the call:
UTL_FILE.FOPEN('FTPOUT', 'test_UTL.txt', 'W', 32767)
In EBS environment, the path for $APPLPTMP is not necessary to be in DBA_DIRECTORIES.
UPDATES in 2018:
During a database 12c upgrade, somehow the OS env variable ORA_NLS10 was unset. That made " utl_file.fcopy ('FTPOUT','test.txt','FTPOUT','test_copy.txt'); ", which is used by Export of AAD rules using Oracle Forms Lock/Unlock option, generate file test_copy.txt 0 in size and UTL_FILE.PUT_LINE fail if output file test_UTL.txt is larger than 1 MB with below errors:
declare
*
ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 690
ORA-06512: at line 256
ORA-29285: file write error
or
Also check the init parameter file and value for
utl_file_dir in v$parameter table.
declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at line 258
The fix is to set env variable ORA_NLS10 on database server to $ORACLE_HOME/nls/data/9idata
The directory could be owned by SYS. But APPS should be granted READ and WRITE.
SQL> SELECT * FROM dba_tab_privs WHERE table_name = 'FTPOUT';
B. Run a short code as APPS to test it. If it works, file test_UTL.txt shall create in the directory.
SQL> set serveroutput on
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
v_error_msg varchar2(1000);
BEGIN
fileHandler := UTL_FILE.FOPEN('FTPOUT', 'test_UTL.txt', 'W');
UTL_FILE.PUT_LINE(fileHandler, 'Writing TO a test file\n');
UTL_FILE.PUTF(fileHandler, 'Writing 2nd line to test file\n');
UTL_FILE.FCLOSE(fileHandler);
EXCEPTION
WHEN utl_file.invalid_path THEN
raise_application_error(-20001, 'ERROR: Invalid PATH for the file.');
when others then
v_error_msg := substr(sqlerrm,1,100);
utl_file.fclose(fileHandler);
dbms_output.put_line('Error message is - ' || v_error_msg);
raise;
END;
/
A MAX linesize written into file test_UTL.txt can be specified in the call:
UTL_FILE.FOPEN('FTPOUT', 'test_UTL.txt', 'W', 32767)
In EBS environment, the path for $APPLPTMP is not necessary to be in DBA_DIRECTORIES.
UPDATES in 2018:
During a database 12c upgrade, somehow the OS env variable ORA_NLS10 was unset. That made " utl_file.fcopy ('FTPOUT','test.txt','FTPOUT','test_copy.txt'); ", which is used by Export of AAD rules using Oracle Forms Lock/Unlock option, generate file test_copy.txt 0 in size and UTL_FILE.PUT_LINE fail if output file test_UTL.txt is larger than 1 MB with below errors:
declare
*
ERROR at line 1:
ORA-29282: invalid file ID
ORA-06512: at "SYS.UTL_FILE", line 77
ORA-06512: at "SYS.UTL_FILE", line 690
ORA-06512: at line 256
ORA-29285: file write error
or
Also check the init parameter file and value for
utl_file_dir in v$parameter table.
declare
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at line 258
The fix is to set env variable ORA_NLS10 on database server to $ORACLE_HOME/nls/data/9idata
By the way, the quick way to test $APPLPTMP and UTL_FILE_DIR in EBS is to run one line:
SQL> exec FND_FILE.PUT_LINE(fnd_file.log, 'Hello World!');
A new file with a name like l0009966.tmp shall be created in the first directory specified in the db parameter utl_file_dir, containing 'Hello World!'.
Subscribe to:
Comments (Atom)