Tuesday, November 7, 2017

Set session timeout in R12

Profile option "ICX:Session Timeout" indicates when an EBS / Forms session expires after inactivity.

The "ICX:Session Timeout" is set by Autoconfig script adwebprf.sql using the s_sesstimeout variable, and so manual change on it will be reset by Autoconfig. Ensure that variable s_sesstimeout and variable s_oc4j_sesstimeout" (controls OC4J session timeout) in context file match (Doc ID 1067115.1).

Steps to set timeout to 60 (or 45) minutes:
1. Context variable in $INST_TOP/appl/admin on all mid-tiers: s_oc4j_sesstimeout to 60 (or 45)
2. Context variable on all mid-tiers: s_sesstimeout to 3600000 (or 2700000)
Note: 360000/(1000*60) = 60
3. Run autoconfig.

Autoconfig uses s_oc4j_sesstimeout to propagate following files:
- $INST_TOP/ora/10.1.3/j2ee/forms-c4ws/application-deployments/forms-c4ws/formsclient/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/forms/application-deployments/forms/formsweb/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/ascontrol/ascontrol/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/mapviewer/web/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oafm/application-deployments/oafm/webservices/orion-web.xml
- $INST_TOP/ora/10.1.3/j2ee/oacore/application-deployments/oacore/html/orion-web.xml

After a Forms session times out, will its database session (or lock if any) stay intact in the database? Hope its database session(s) will be terminated together, however seems they are not.

Wednesday, November 1, 2017

Find the R12 Froms session that may lock data row

User received message "Could not reserve record[2 tries]. Keep trying? " on R12 Receipts Summary form (ARXRWMAI). I used below script (see Doc ID 1986754.1) to find the process IDs on apps server that may place a lock on table rows.

select  nvl(S.USERNAME,'Internal') username, s.process RTI_PID, l.ctime,
    L.SID||','||S.SERIAL# Kill,
    s.module,
    U1.NAME||'.'||substr(T1.NAME,1,20) table_name,
        decode(L.LMODE,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive',null) lmode,
    s.program,
    nvl(S.TERMINAL,'None') terminal,
    decode(L.REQUEST,1,'No Lock',
        2,'Row Share',
        3,'Row Exclusive',
        4,'Share',
        5,'Share Row Exclusive',
        6,'Exclusive',null) request
from   V$LOCK L, V$SESSION S, SYS.USER$ U1, SYS.OBJ$ T1
where    L.SID = S.SID 
and    T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2) 
and    U1.USER# = T1.OWNER#
and    S.TYPE != 'BACKGROUND' and U1.name != 'SYS'
order by l.ctime desc,2,6 ;

Now RTI_PID can be used to find the username from Forms Sessions monitoring in Oracle Application Manager (OAM). But if the list of forms sessions is very long, it is very difficult to sort it out. Then use RTI_PID, the process IDs on EBS apps server, to find the Forms usernames who may lock the data. Below script is modified from Doc ID 1610624.1 (It is for 11i. But seems it works for R12.1.3 on 12c database as well).

SELECT (SELECT substr(fu.user_name ,1 ,20)
         FROM   apps.fnd_user fu
         WHERE  fu.user_id = fl.user_id)
         user_name, to_char(fl.start_time,'DD-MON-YYYY HH24:MI') login_start_time,
        substr(fl.process_spid ,1 ,6) spid,
        to_char(fl.pid) pid, vs.machine host,
        substr(vs.process, 1 ,8) host_pid,
        substr(to_char(rf.audsid) ,1 ,6) audsid,
        to_char(vs.sid) sid,
        substr(to_char(vs.serial#) ,1 ,8) serial#,
        substr(vs.module || ' - ' ||
               (SELECT substr(ft.user_form_name, 1 ,40)
                FROM   apps.fnd_form_tl ft
                WHERE  ft.application_id = rf.form_appl_id
                AND    ft.form_id = rf.form_id
                AND    ft.LANGUAGE = 'US')
              ,1 ,40) form
FROM   apps.fnd_logins  fl,   gv$process vp,
       apps.fnd_login_resp_forms rf,   gv$session vs
WHERE  fl.end_time IS NULL
AND    fl.start_time > SYSDATE - 31
-- AND    fl.login_type = 'FORM'
AND    fl.process_spid = vp.spid
AND    fl.pid = vp.pid
AND    fl.login_id = rf.login_id
AND    rf.end_time IS NULL
AND    rf.audsid = vs.audsid
AND    vs.process = '26931'  -- enter process ID (RTI_PID) from above
ORDER  BY user_name, login_start_time, sid;

Friday, September 15, 2017

An unexpected error with librw.so by hotspot virtual machine

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

$

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';

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