Wednesday, December 20, 2017

EBS apps OCT2017 & JAN2018 CPU patches

I followed below document to apply EBS October 2017 CPU patch:
Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (October 2017) (Doc ID 2304968.1).

1. Identify pre-requisites listed in README. 3 patches are needed in my case:
SQL> select * from ad_bugs where bug_number in
('23231676', -- R12.JTT.B.delta.4 (Oracle CRM Technical Foundation. Doc ID 2179284.1)
'19559960',  -- R12.FRM.B.DELTA.4 (Report Manager. Doc ID 1941098.1)
'22284589',  -- R12.FWK.B.DELTA.5 (Applications Framework. Doc ID 1931412.1)
'19273341', -- R12.BNE.B.delta.4. Applied
 '9239089', -- R12.AD.B.delta.3 (Applications DBA 12.1.3). Applied
 '8919491'  -- R12.ATG_PF.B.Delta.3 (Doc ID 1066312.1). Applied
);
 

2. Identify and apply Oracle database (12.1.0.2) patches
- From Doc ID 2304968.1
26636270 - Main October DB PSU
- Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2 (Doc ID 2034610.1)
 25838690
 23665623
 18430870
 19174639
 21171382 - Using merge patch 23117432
 25892909
 20804826
 Removed recommendation to disable the "_optimizer_reduce_groupby_key" parameter, as the inclusion of these fixes renders it unnecessary.
 Removed recommendation to disable the "_optimizer_aggr_groupby_elim" parameter, as the inclusion of fixes for both bugs 21826068 and 23147905 renders it unnecessary.
- Things to Consider for 12.1.0.2 to Avoid Problems with SQL Plan Management (SPM) (Doc ID 2035898.1)
 18747342
 26570111
 22324460
 21075138
 21463894
- Oracle E-Business Suite Recommended Performance Patches (Doc ID 244040.1). Also see: Getting Conflict While Applying The EBS Recommended Performance Patch 20807398 And 20871556 (Doc ID 2288792.1)
  20807398 - already applied
  20871556 - Using merge patch 26072453
- Database Patch Set Update Overlay Patches Required for Use with PSUs and Oracle E-Business Suite (Doc ID 1147107.1)
 20903906   version 12.1.0.2.160119
 

3. Oracle Fusion Middleware 10.1.3.5
Security Patch Update CPUOct2015 (Patch 21845960)   - already applied

$ cd $INST_TOP/ora/10.1.3
$ . xxxx.env
$ opatch lsinventory | grep 21845942


4. Oracle Fusion Middleware 10.1.2.3
Apply patch 26825525 (Security Patch Update CPU Oct2017)

$ echo $ORACLE_HOME
/path/to/apps/tech_st/10.1.2
--verify two pre-patches were applied
$ opatch lsinventory | grep 14825718
$ opatch lsinventory | grep 8551790
   
$ unzip p26825525_101232_LINUX.zip
$ cd 26825525  
$ opatch apply
(it will roll back patch 22698265)

5. Oracle Java (JRE Plug-in)
Skip Java Runtime Environment (JRE) 8 Update 151 Patch 26594952 , because JRE 1.8.0_92 was applied on EBS hosts. If 1.8.0_151 is applied, all users have to load JRE 1.8.0_151 to their desktop.

6. Merge 3 pre-requisites and apply it
$ more manifest_oct17cup.txt
/path/to/patches/oct17cup_merge/p23231676_R12.JTT.B_R12_GENERIC.zip
/path/to/patches/oct17cup_merge/p19559960_R12.FRM.B_R12_GENERIC.zip
/path/to/patches/oct17cup_merge/p22284589_R12.FWK.B_R12_LINUX.zip

$ admrgpch -s /path/to/patches/oct17cup_merge -d oct17cup_pre -manifest manifest_oct17cup.txt

$ cd oct17cup_pre
$ adpatch
Note it spent 8 minutes on below line, and gave fail, error and warnings in file $INST_TOP/logs/appl/rgf/ojsp/ojspc_error.log. Ignore them.
$ perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile --quiet

Troubleshooting: At this time point, I tested login page and found responsibilities from the left main menu are not expanding in the Home Page. 
Fixes: See Doc ID 2282306.1 (MENU Folders On Homepage Not Expanding After Applying 22284589 R12.FWK.B.delta5)
1). run $perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2 -log /tmp/ojspc_error.log
2). $ rm -f $OA_HTML/cabo/images/cache/*.*
     $ rm -f $OA_HTML/cabo/styles/cache/*.*
3). IE browser: delete 'Temporary Internet files'.
Notes: in my cases, 2) and 3) are good enough as the fix and can be performed after Step 8.

More references: Doc ID 742107.1 (How To Clear Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI Caches in E-Business Suite?)
Doc ID 2224727.1 (R12 Applications Framework Home Page Left Side Menu For The Responsibility List Does Not Expand, And No Error Is Received After Applying Patch 22284589 R12.FWK.B.DELTA.5). I did not apply patch 24671878 as recommended in this document.

7. DBA runs 26574496/admin/adgrants.sql as SYSDBA
(without this step, adpatch will fail on adrevokegrants.sql). Unzip it first
$ unzip p26574496_12.1.0_R12_LINUX.zip
SQL> @adgrants.sql apps   ( <- lower case)

8. Apply EBS OCT2017 cpu patch 26574496 to R12.1.3 instance
$ cd 26574496
$ adpatch
Note it runs this line again:
$ perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile --quie

9. Clear cache and start services

1). $ rm -f $OA_HTML/cabo/images/cache/*.*
     $ rm -f $OA_HTML/cabo/styles/cache/*.*
2). disable maintenance mode and start EBS services
3). IE browser: delete 'Temporary Internet files'.

Notes: Without 1), some users may get below errors after users login and click to expend the main menu in the first time due to the confusion in re-creating files in cache:

Error while invoking Main Menu.Application: FND, Message Name: FND_STATE_LOSS_ERROR. Tokens: Reason = RootApplication Module state corruption:oracle.apps.fnd.framework.navigate.server.OANavigatePortletAM
has already been released;


Error while invoking Main Menu. Application: FND, Message Name: FND_NO_DATABASE_CONNECTION.
(Notes: Doc. 1298103.1 says Patch 20217845, which was included in Step 6, should fix this error)

UPDATE: See other error on R12.1.3 main menu on Windows 10:  Error while invoking Main Menu. Application: FND, Message Name: FND_SESSION_MGR_INST_ERROR.
I followed Doc ID 2149258.1 and applied patch 23200210 for in a non-prod instance.

10. After CPU patches, adstrtal.sh log may show below lines:
        *** Latest formsapp.ear has NOT been deployed ***
        Deploying the latest EAR file...


11. Post patches: I did NOT apply any post-patches because below three patches were not applied to my instances (and Landed Cost Management (INL) was not implemented. See Section 2 of Doc ID 2304968.1) :
SQL> select * from ad_bugs where bug_number  in
('21198991', -- R12.PRC_PF.B
'19342134',  -- R12.IBE.B.DELTA.4
'21980909'   -- R12.HR_PF.B.delta.9
);

But, it may need some post-patches for following newly applied patches. See each Oracle document for details.
'23231676', -- R12.JTT.B.delta.4 (CRM Tech Foundation. Doc ID 2179284.1)
'19559960', -- R12.FRM.B.DELTA.4 (Report Manager. Doc ID 1941098.1)
'22284589'  -- R12.FWK.B.DELTA.5 (Applications Framework. Doc ID 1931412.1)

UPDATES in April 2018:  I followed document Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (January 2018) (Doc ID 2334374.1) to apply EBS January 2018 CPU patch. Most steps are the same.  The only differences are


Step 2: Database PSU patch 27010839 (Document 2325393.1)
Step 7 & 8: EBS JAN2018 cpu patch 27040859

UPDATES in August 2018: Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (July 2018) (Doc ID 2379675.1) is for July 2018 CPU patch 28018146.
It asks Oracle JRE 8 Update 181 Patch 27890728 for JRE and JWS. (but 1.8.0_162 still works with.)

UPDATES in May 2021: Query has all (and latest) EBS R12.1 CPU patches:
SQL> select distinct bug_number Patch, decode(bug_number,
'32438190','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2021',
'32071645','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2021',
'31643022','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR OCT 2020',
'31198341','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JUL 2020',
'30812013','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2020',
'30445462','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2020',
'30077281','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR OCT 2019',
'29692308','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JUL 2019',
'29224722','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2019',
'28840561','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2019',
'28421543','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR OCT 2018',
'28018146','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JUL 2018',
'27468057','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2018',
'27040859','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2018',
'26574496','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR OCT 2017',
'25982921','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JUL 2017',
'25449171','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2017',
'25032333','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2017',
'24390793','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR OCT 2016',
'23144507','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JUL 2016',
'22614470','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2016',
'22133441','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2016',
'21507207','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR OCT 2015',
'20953340','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JUL 2015',
'20406628','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR APR 2015',
'19873049','ORACLE APPLICATIONS RELEASE 12.1: CPU PATCH FOR JAN 2015') 
Description, 
CREATION_DATE Applied
from ad_bugs
where bug_number in
('32438190','32071645','31643022','31198341','30812013','30445462','30077281',
'29692308','29224722','28840561','28421543','28018146','27468057','27040859',
'26574496','25982921','25449171','25032333','24390793','23144507','22614470',
'22133441','21507207','20953340','20406628','19873049') 
order by Patch desc;

Tuesday, November 7, 2017

Set session timeout in R12

Profile option "ICX:Session Timeout" indicates when an EBS Forms session expires after inactivity. See Doc ID 307149.1 (E-Business Suite Applications Technology Stack Information Regarding How AutoConfig Sets Profile Option 'ICX: Session Timeout')

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 120) 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 7200000)
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.

NOTES in January 2022:  
s_oc4j_sesstimeout is not used in R12.2.  R12.2 still uses s_sesstimeout and has a setting in WebLogic.  See Doc ID 2142867.1 (Troubleshooting Session Timeouts in EBS Applications):

Login to WebLogic Admin Console
Under 'Change Center', click on 'Lock & Edit'
Under 'Domain Structure', click on 'Deployments'
Go to the next page until you see the 'oacore' deployment
Click on the plus sign next to the word 'oacore'
Click on the module '/OA_HTML'
Click on the 'Configuration' tab
Set 'Session Timeout (in seconds)' to 1800 (= 30 minutes)
Click 'Save'.
Under 'Domain Structure', click on 'Deployments'
Go to the next page until you get to the 'oacore' deployment and click on it
Click on the 'Configuration' tab
Set 'Session Timeout (in seconds)' to 1800
Click 'Save'.
Click 'Release Configuration'
Set the 'ICX Session Timeout' profile option value at the site level to 30 ( s_sesstimeout => 1800000)
Restart services via adstpall.sh / adstrtal.sh

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 (modified from Doc ID 1020007.6) to find the process IDs on Apps server that may place a lock on table rows.

SQL> select  nvl(S.USERNAME,'Internal') username, s.process EBS_PID, l.ctime, s.machine,
    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,
    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 ;


EBS_PID is the process ID on Apps node. The query may return a long list of rows and so becomes not very useful.

OS command "top" can be used to see if any process runs for too long or uses high CPU/memory. Before kill it on OS level to release the block, Linux line can be used to verify its start time:

$ ps -eo pid,lstart,cmd | grep EBS_PID

For example, a Form session has run for days:
$ ps -eo pid,lstart,cmd | grep 16303
   9557 Thu Oct 15 09:40:59 201x grep --color=auto 16303
16303 Mon Oct 12 09:57:02 201x frmweb server webfile=HTTP-0,0,1,default

A simple query can be used to find R12 Forms' user name (in database 12c and 11g).  If the query returns nothing, it does not connect to database any more and it is safe to kill the run-away process on OS level. 

SQL> select sid, SERIAL#, process EBS_PID, machine, action, module, CLIENT_IDENTIFIER
FROM v$session where process = '16303' ;     -- 16303 is the EBS_PID

Below query can be used to verify if there is a true blocking session or not:

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') status, sid sess_ID, id1, id2, lmode, request, type
FROM    V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

Oracle Application Manager (OAM) also lists forms sessions with user name (OAM -> Dashboard -> Site Map -> Monitoring -> Forms Sessions).  But if the list is very long, it is very difficult to sort it out. Note if Forms sessions are not populated in OAM, set the profile option "Sign-On:Audit Level" to "FORM" (see Doc ID 1270277.1).

NOTES: 
Doc ID 1610624.1 gives a SQL to find Forms user name in 11i :
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 = '5629'  -- enter process ID (EBS_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


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:

[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

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.

Friday, May 12, 2017

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 /ifsu02/app/IFSPROD/inst/apps/IFSPROD_xifsapm3p/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

Tuesday, April 11, 2017

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.

Sunday, February 19, 2017

Stop Old Outbound Workflow Notification Emails after Clone

I followed below document to cancel/purge R12.1.3 notifications:
How To Purge E-Mail Notifications From The Workflow Queue So The E-Mail Is Not Sent (Doc ID 372933.1)

It gives a query getting notifications that would be sent, and are waiting to be e-mailed when the Mailer get started:

SQL> select notification_id, begin_date, recipient_role, message_type, message_name, status, mail_status
 from wf_notifications
where Status In ('OPEN', 'CANCELED')
   and Mail_Status In ('MAIL', 'INVALID')
   and begin_date < sysdate-30              -- List only emails older than 30 days ago
order by notification_id;

SQL> select count(*) from wf_notifications
         where status in ('OPEN', 'CANCELED')
         And Mail_Status In ('MAIL', 'INVALID')
         order by notification_id;
  COUNT(*)
----------
    271022
SQL> select count(*) from wf_notifications
    where status in ('OPEN', 'CANCELED')
    and Mail_Status In ('MAIL', 'INVALID')
    and begin_date < sysdate-30;    -- List only emails older than 30 days ago
  COUNT(*)
----------
    270991
SQL> create table apps.wf_notifications_BK_120816 as
    select * from wf_notifications
    where status in ('OPEN', 'CANCELED')
    and Mail_Status In ('MAIL', 'INVALID')
    and begin_date < sysdate-30;
Table created.

SQL> update WF_NOTIFICATIONS set mail_status = 'SENT'
    where status in ('OPEN', 'CANCELED')
    and Mail_Status In ('MAIL', 'INVALID')
    and begin_date < sysdate-30;
270998 rows updated.
SQL> commit;
Commit complete.

SQL> @$FND_TOP/patch/115/sql/wfntfqup.sql apps apps_PWD applsys
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.76
Commit complete.
Elapsed: 00:00:00.00
**** TEMPORARY TABLES / AQs created ****
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.27
**** Messages backed up ****
Commit complete.
Elapsed: 00:00:00.00
**** wfaqback.sql completed,  executing SQLs from wfntfqup
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.60
***  invoking wfaqrenq.sql ******
*** Re-enqueing messages
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
Commit complete.
Elapsed: 00:00:00.01
***** Re-enqueue OF Alerts completed *****

I tried to use an email address to hold the old notifications after SMTP works fine on the CM server. Seems to me that the "Set Override Address" will not work until the Mailer was started, because prior to that, it would not send the Verification Code out to the email address.

References: Doc ID 562551.1 includes two old documents:

- How to Cancel Email Notifications for Particular Workflow Type (Doc ID 736508.1 for 11i)
- How To Stop Old Outbound Workflow Notification Email Messages During Clone Activity (Doc ID 828812.1 for 11.5.9 to 12.0.0)