Monday, January 22, 2018

Unable to load login page after clone

After I cloned a R12.1.3 instance on an 11g database, all EBS services started fine. But, It failed to re-direct to the login page with posting a generic error message in browsers. File 
$LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1/application.log shows the true error:
Caused by: oracle.apps.jtf.base.resources.FrameworkException: ORA-01578: ORACLE data block corrupted (file # 45, block # 863844)
ORA-01110: data file 45: '/path/to/a_txn_data05.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

The message is kind of misleading. I followed Doc ID 418130.1 (Unable To Login After Clone) to fix the problem. After I ran below PL/SQL procedure, the login page worked.
SQL> select count(1) from WF_LOCAL_USER_ROLES;
 COUNT(1)
----------
      2050
SQL> begin
apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL',
 P_PARALLEL_PROCESSES=>2,
 P_LOGGING=>'LOGGING',
 P_RAISEERRORS=>TRUE,
 P_TEMPTABLESPACE=>'APPS_TS_TX_DATA');
end;
/
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.
SQL> exit

Wednesday, December 20, 2017

EBS apps OCT2017 CPU patch

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. 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)
 '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
'22284589' -- R12.FWK.B.DELTA.5 (Applications Framework. Doc ID 1931412.1)
);
 

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


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 got fail, error and warnings in file $INST_TOP/logs/appl/rgf/ojsp/ojspc_error.log
$ perl -x $FND_TOP/patch/115/bin/ojspCompile.pl --compile --quiet

Troubleshooting: At this time point, test login page and find 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'.
Note that 2) and 3) are good enough and can be performed after Step 8.

More references: How To Clear Apache/iAS, Cabo, Modplsql, Browser, Jinitiator, Java, Portal, WebADI Caches in E-Business Suite? (Doc ID 742107.1)
I did not apply patch 24671878 as recommended in document:
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 (Doc ID 2224727.1)

7. DBA runs adgrants.sql as SYSDBA
(without this step, adpatch will fail on adrevokegrants.sql)

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

9. Post patches: I applied none of them (and "Landed Cost Management (INL)" was not implemented)
SQL> select * from ad_bugs where bug_number  in
('21198991',
'19342134', -- R12.IBE.B.DELTA.4
'21980909'  -- R12.HR_PF.B.delta.9
);
Also, it may need some post patches for modules:
'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)

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 ;

RTI_PID is the process ID on Apps concurrent host. It 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. Alternative way is to use below script 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'...

$