When run rwconverter in R12.1.3, it hits below error:
$ cd $CUSTOM_TOP/reports/US
$ rwconverter userid=apps/xxxxxx source=ARXCPH.rdf dest=ARXCPH.rdf stype=rdffile dtype=rdffile overwrite=yes batch=yes compile_all=yes
Report Builder: Release 10.1.2.3.0 - Production on Fri Sep 15 16:30:51 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Converting 'ARXCPH.rdf' to 'ARXCPH.rdf'...
#
# An unexpected error has been detected by HotSpot Virtual Machine:
#
# SIGSEGV (0xb) at pc=0xf6ea4a69, pid=3955, tid=4098397952
#
# Java VM: Java HotSpot(TM) Server VM (1.4.2_14-b05 mixed mode)
# Problematic frame:
# C [librw.so+0x979a69]
#
# An error report file with more information is saved as hs_err_pid3955.log
#
# If you would like to submit a bug report, please visit:
# http://java.sun.com/webapps/bugreport/crash.jsp
#
Abort(coredump)
To fix the problem, follow Doc ID 1161803.1 to unset two environment variables:
$ echo $EVENT_10932
8
$ echo $DE_DISABLE_PLS_512
1
$ unset EVENT_10932
$ unset DE_DISABLE_PLS_512
$ rwconverter userid=apps/xxxxxx source=ARXCPH.rdf dest=ARXCPH.rdf stype=rdffile dtype=rdffile overwrite=yes batch=yes compile_all=yes
Report Builder: Release 10.1.2.3.0 - Production on Fri Sep 15 16:42:57 2017
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Converting 'ARXCPH.rdf' to 'ARXCPH.rdf'...
$
Friday, September 15, 2017
Monday, August 28, 2017
User's last login and number of current EBS connections
- To answer who is logged in EBS R12.1, Oracle DocID 269799.1 says:
You can run the Active Users Data Collection Test diagnostic script to get information about all active users currently logged into EBS. This diagnostic test (on "Application Object Library") will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.
- To check running EBS forms sessions, go to System Administration > Oracle Application Manager > Dashboard > Site Map > Monitoring > Forms Sessions
It links the OS session ID with user ID who runs the Forms session.
- FND_USER table stores the details of all end users. Below query can give a good idea who is logged on:
SQL> select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');
- Use below code to get active users' last login:
set heading on
set feedback off
set echo off
ttitle on
Column the_today noprint new_value the_date format a20
Select distinct to_char(sysdate,'MM/DD/YY HH:MIPM') the_today from dual;
ttitle skip 2 -
left 'Run on: ' the_date center 'EBS Active Users and their Active Roles' skip 1 -
left 'Page: ' format 99999 sql.pno skip 1
set lines 145
set pages 30000
col user for a55 trunc
col RESPONSIBILITY_NAME for a40 trunc
col Last_Logon_date for a20
select substr(c.user_name||' - '||c.description, 1, 60) "USER", RESPONSIBILITY_NAME,
c.start_date "ActiveDate", to_char(last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date
from FND_Responsibility_tl a,
FND_USER_RESP_GROUPS b,
fnd_user c
where a.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and b.user_id = c.user_id
and (c.end_date is null or c.end_date > sysdate)
and (b.end_date is null or b.end_date > sysdate)
-- and c.last_logon_date > sysdate - 180
order by 1, 2
spool EBS_Active_users_08_2017.txt
/
Spool off
- For terminated user, run below to find the last login. Seems the user never logged onto EBS if its last_logon_date is NULL.
SQL> select substr(c.user_name||' - '||c.description, 1, 60) "USER",
to_char(c.last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date, user_id, creation_date, password_date, start_date, end_date
from fnd_user c
where c.user_name like '%ABC%';
- POSSIBLE queries to get number of EBS users connections. (not sure how accurate they are)
1) SQL> select count(*), to_char(sysdate, 'DY MON DD HH24:MI:SS YYYY') format
from v$session
where module like '%FNDSCSGN';
2) The number of users on the system in the past 1 hour:
SQL> select count(distinct user_id) "users" from icx_sessions
where last_connect > sysdate - 1/24 and user_id != '-1';
The number of users on the system in the past 1 day:
SQL> select count(distinct user_id) "users" from icx_sessions
where last_connect > sysdate - 1 and user_id != '-1';
You can run the Active Users Data Collection Test diagnostic script to get information about all active users currently logged into EBS. This diagnostic test (on "Application Object Library") will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs.
- To check running EBS forms sessions, go to System Administration > Oracle Application Manager > Dashboard > Site Map > Monitoring > Forms Sessions
It links the OS session ID with user ID who runs the Forms session.
- FND_USER table stores the details of all end users. Below query can give a good idea who is logged on:
SQL> select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');
- Use below code to get active users' last login:
set heading on
set feedback off
set echo off
ttitle on
Column the_today noprint new_value the_date format a20
Select distinct to_char(sysdate,'MM/DD/YY HH:MIPM') the_today from dual;
ttitle skip 2 -
left 'Run on: ' the_date center 'EBS Active Users and their Active Roles' skip 1 -
left 'Page: ' format 99999 sql.pno skip 1
set lines 145
set pages 30000
col user for a55 trunc
col RESPONSIBILITY_NAME for a40 trunc
col Last_Logon_date for a20
select substr(c.user_name||' - '||c.description, 1, 60) "USER", RESPONSIBILITY_NAME,
c.start_date "ActiveDate", to_char(last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date
from FND_Responsibility_tl a,
FND_USER_RESP_GROUPS b,
fnd_user c
where a.RESPONSIBILITY_ID = b.RESPONSIBILITY_ID
and b.user_id = c.user_id
and (c.end_date is null or c.end_date > sysdate)
and (b.end_date is null or b.end_date > sysdate)
-- and c.last_logon_date > sysdate - 180
order by 1, 2
spool EBS_Active_users_08_2017.txt
/
Spool off
- For terminated user, run below to find the last login. Seems the user never logged onto EBS if its last_logon_date is NULL.
SQL> select substr(c.user_name||' - '||c.description, 1, 60) "USER",
to_char(c.last_logon_date, 'DD-MON-YYYY HH24:MI:SS') Last_Logon_date, user_id, creation_date, password_date, start_date, end_date
from fnd_user c
where c.user_name like '%ABC%';
- POSSIBLE queries to get number of EBS users connections. (not sure how accurate they are)
1) SQL> select count(*), to_char(sysdate, 'DY MON DD HH24:MI:SS YYYY') format
from v$session
where module like '%FNDSCSGN';
2) The number of users on the system in the past 1 hour:
SQL> select count(distinct user_id) "users" from icx_sessions
where last_connect > sysdate - 1/24 and user_id != '-1';
The number of users on the system in the past 1 day:
SQL> select count(distinct user_id) "users" from icx_sessions
where last_connect > sysdate - 1 and user_id != '-1';
Monday, July 24, 2017
Restore files by TSM on Linux server
Unix/Linux file restoration by TSM:
Logon to server where file is locally stored (i.e. not where the file may be NFS mounted)
User id must be the owner of the file. TSM backups files under the ‘true’ name. Linked files would be backed up the target file’s name
$ dsmc
To get a list of backups available, issue
tsm> q b -inactive -pitdate=04/01/2017 -subdir=yes /u02/app/EBSQA/
Note: 1. The result list is way too long to read
2. The result list is much short without '/' at the end.
(I do not know/understand why and what it reports)
tsm> q b -inactive -pitdate=04/01/2017 -subdir=yes /u02/app/EBSQA
To restore a folder, issue
tsm> restore –inactive –replace=no –pitdate=03/092017 -subdir=yes /u02/app/EBSQA/ ‘
Total number of objects restored: 43,597
Total number of objects failed: 0
Total number of bytes transferred: 24.77 MB
Data transfer time: 1.96 sec
Network data transfer rate: 12,921.81 KB/sec
Aggregate data transfer rate: 48.57 KB/sec
Elapsed processing time: 00:08:42
tsm> quit
$ find /u02/app/EBSQA -type f | wc -l
659159
NOTES:
1. Below line also does a restoration
restore -subdir=yes -replace=no -pitdate=02/30/2017 /u02/app/EBSQA/apps/apps_st/appl/*
2. I saw this on a different server. Do not know why.
tsm> restore -inactive -replace=no -pitdate=03/09/2017 -subdir=yes /u02/app/EBSQA/
Total number of objects restored: 43,393
Total number of objects failed: 0
Total number of bytes transferred: 0 B
Data transfer time: 0.00 sec
Network data transfer rate: 0.00 KB/sec
Aggregate data transfer rate: 0.00 KB/sec
Elapsed processing time: 00:19:59
tsm>
3. If getting this error, there may be a permission problem. It does not mean the backup was not running.
$ dsmc
ANS1398E Initialization functions cannot open one of the Tivoli Storage Manager logs or a related file: /opt/tivoli/tsm/client/ba/bin/dsmerror.log. errno = 13, Permission denied
Logon to server where file is locally stored (i.e. not where the file may be NFS mounted)
User id must be the owner of the file. TSM backups files under the ‘true’ name. Linked files would be backed up the target file’s name
$ dsmc
To get a list of backups available, issue
tsm> q b -inactive -pitdate=04/01/2017 -subdir=yes /u02/app/EBSQA/
Note: 1. The result list is way too long to read
2. The result list is much short without '/' at the end.
(I do not know/understand why and what it reports)
tsm> q b -inactive -pitdate=04/01/2017 -subdir=yes /u02/app/EBSQA
To restore a folder, issue
tsm> restore –inactive –replace=no –pitdate=03/092017 -subdir=yes /u02/app/EBSQA/ ‘
Total number of objects restored: 43,597
Total number of objects failed: 0
Total number of bytes transferred: 24.77 MB
Data transfer time: 1.96 sec
Network data transfer rate: 12,921.81 KB/sec
Aggregate data transfer rate: 48.57 KB/sec
Elapsed processing time: 00:08:42
tsm> quit
$ find /u02/app/EBSQA -type f | wc -l
659159
NOTES:
1. Below line also does a restoration
restore -subdir=yes -replace=no -pitdate=02/30/2017 /u02/app/EBSQA/apps/apps_st/appl/*
2. I saw this on a different server. Do not know why.
tsm> restore -inactive -replace=no -pitdate=03/09/2017 -subdir=yes /u02/app/EBSQA/
Total number of objects restored: 43,393
Total number of objects failed: 0
Total number of bytes transferred: 0 B
Data transfer time: 0.00 sec
Network data transfer rate: 0.00 KB/sec
Aggregate data transfer rate: 0.00 KB/sec
Elapsed processing time: 00:19:59
tsm>
3. If getting this error, there may be a permission problem. It does not mean the backup was not running.
$ dsmc
ANS1398E Initialization functions cannot open one of the Tivoli Storage Manager logs or a related file: /opt/tivoli/tsm/client/ba/bin/dsmerror.log. errno = 13, Permission denied
Tuesday, July 11, 2017
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:
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)
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)
Friday, June 30, 2017
How to change APPLSYSPUB 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. $ 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)
3. 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 $FND_SECURE/xxx.dbc file. But it will not update files $FND_TOP/resource/appsweb.cfg and $OA_HTML/bin/appsweb.cfg.
Also see http://erpondb.blogspot.com/2016/11/fndcpass-and-app-fnd-02704.html about 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. $ 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)
3. 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 $FND_SECURE/xxx.dbc file. But it will not update files $FND_TOP/resource/appsweb.cfg and $OA_HTML/bin/appsweb.cfg.
Also see http://erpondb.blogspot.com/2016/11/fndcpass-and-app-fnd-02704.html about special character.
Subscribe to:
Posts (Atom)