Tuesday, May 15, 2018

Run CONCSUB from command line

Assume Oracle EBS was installed on server node1q under /u06/app/
$ echo $APPL_TOP
/u06/app/EBSQA/apps/apps_st/appl
$ echo $ORACLE_HOME
/u06/app/EBSQA/apps/tech_st/10.1.2
$ echo $TWO_TASK
EBSQA
$ cd $FND_TOP/bin
$ ls -al CONCSUB
-rwxr-xr-x 1 app ora 1033192 Aug 28  2011 CONCSUB

Now, business users want to schedule shell scripts to submit concurrent jobs and then use their output files to complete other tasks. To avoid sharing the Applmgr account with business users, a separate account is used to run concurrent jobs on the EBS concurrent node.

1. Create OS user batchuser on the CM host node1q
2. Install Oracle client on node1q but under a different directory
$ whoami
batchuser
$ echo $ORACLE_HOME
/batchu01/app/product/11.2.0/EBIZ

3. Setup env variables (or put all lines into file setenv.ebsqa)
$ more setenv.ebsqa
. /u06/app/EBSQA/apps/apps_st/appl/EBSQA_node1q.env
# Notes: above file does not include below variables
# Don't run /u06/app/EBSQA/apps/apps_st/appl/APPSEBSQA_node1q.env
export PATH=$PATH:/batchu01/app/product/11.2.0/EBIZ/bin
export ORACLE_HOME=/batchu01/app/product/11.2.0/EBIZ
export TNS_ADMIN=/batchu01/app/product/11.2.0/EBIZ/network/admin
export ORACLE_SID=EBSQA
export TWO_TASK=EBSQA
export LD_LIBRARY_PATH=/batchu01/app/product/11.2.0/EBIZ/lib:$LD_LIBRARY_PATH
echo `which CONCSUB`
echo `which sqlldr`
echo $TWO_TASK
echo $ORACLE_HOME

$ . setenv.ebsqa
/u06/app/EBSQA/apps/apps_st/appl/fnd/12.0.0/bin/CONCSUB
/batchu01/app/product/11.2.0/EBIZ/bin/sqlldr
EBSQA
/batchu01/app/product/11.2.0/EBIZ

4. Make sure entry EBSQA is defined in file $TNS_ADMIN/tnsnames.ora

5. Copy file libclntsh.so.10.1 from EBS installation ORACLE_HOME/lib (i.e. /u06/app/EBSQA/apps/tech_st/10.1.2/lib) to
/batchu01/app/product/11.2.0/EBIZ/lib
$ cd /batchu01/app/product/11.2.0/EBIZ/lib
$ ls -al libclntsh.so.10.1
-rwxr-xr-x 1 batchuser batch 13696149 Aug  4  2015 libclntsh.so.10.1

Notes: this step will avoid below error:
CONCSUB: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory
It may hit below error if the file version is wrong:
CONCSUB: error while loading shared libraries: libclntsh.so.10.1: wrong ELF class: ELFCLASS64

6. Submit the concurrent job as Linux user batchuser
$ whoami
batchuser
$ CONCSUB APPS/appsPWD SYSADMIN "System Administrator" u34567 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"'
Submitted request 33514235 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users"
Normal completion

7. Submit the concurrent job by putting one line to a shell script. This can be done by saving appsPWD to a flat file.
$ more /home/batchuser/pwd/apps.pwd
appsPWD
$ submission=$(CONCSUB APPS SYSADMIN "System Administrator" u34567 WAIT=Y CONCURRENT FND FNDSCURS PROGRAM_NAME='"Active Users"' < /home/batchuser/pwd/apps.pwd)

$ echo $submission
ORACLE Password: Submitted request 33514294 for CONCURRENT FND FNDSCURS PROGRAM_NAME="Active Users" Normal completion
$ reqno=$( echo ${submission#*Submitted request}|awk {'print $1'} )
$ echo $reqno
33514294
$ ls -al $APPLCSF/out/*33514294*.*
-rw-r--r-- 1 apps ora 25613 May 15 23:57 /path/to/conc/out/o33514294.out

Notes: It may cause a security issue from saving appsPWD on a flat file. Do not forget to secure it.
$ ls -al /home/batchuser/pwd/apps.pwd
-rw-------  1 batchuser batch   12 Aug 17  2016 apps.pwd
If multiple users need to run concurrent jobs on command line, you may create and assign them to a new OS group, and give the rw- permission on file apps.pwd to that group as well.

Concurrent job can also be submitted from SQL statement by calling FND_REQUEST.SUBMIT_REQUEST.

Friday, April 27, 2018

Enable BNE / WebADI debug trace

Instructions for BNE debug (in R12.1.3): 
1) Login to System Administrator Responsibility.
2) Open the System -> Profiles form.
3) Search for %BNE%LOG% in the profiles.
This will return three profiles:
BNE Debug Log Level -- This should be set to "TRACE" (all caps)
BNE Debug Log Filename -- The log file name should be specified here (eg: "bne2018.log")
BNE Debug Log Directory - -The log file directory should be specified here.
NOTE: The log file should exist, even if empty. Application will not create a new file. Create an empty log file at the location specified in BNE Debug Log Directory like (eg: "bne2018.log"). A services recycle may be needed to make the changes effective.

Once login as System Administrator, paste below URL to same browser window to view details, including the log:
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet

https://hostname.domain:portnumber/OA_HTML/BneAdminServlet?bne:action=log-first&bne:lines=10
https://hostname.domain:portnumber/OA_HTML/BneAdminServlet?bne:action=log-last&bne:lines=10

To reproduce an issue:
(a). Click the "clear-cache" link.
(b). For the attribute "log-set-level" (Current JVM only) , click on TRACE
(c). Run through reproducible case
(d). examine/upload the log

All BNE profile settings in my instance:
NAME
PROF_LEVEL
PROFILE_OPTION_VALUE
DESCRIPTION
BNE: ALLOW MAX DELIMITER
SITE
Y
To Allow maximum delimiter character to import text files in the integrator.
BNE Allow No Security Rule
SITE
N
Allow applications that have not defined a security rule to function
BNE Allow Set Parameters
SITE
Y
Controls access to the Upload Parameters window
BNE Allow Set Parameters
App:IGS
Y
Controls access to the Upload Parameters window
BNE Disable
NOT SET
NOT SET
Disables all Web ADI functionality
BNE Viewer Displays Image
SITE
Y
Excel Viewer does display default FND display image in WebADI documents created
BNE Document Lifetime
NOT SET
NOT SET
How long do documents live before they are automatically disabled.
BNE Enable Digital Signature
SITE
N
BNE Enforce Parameter Values
NOT SET
NOT SET
Sets whether a user can make an alternate value selection where the request parameter is not valid
BNE Offline List of Values Limit
NOT SET
NOT SET
Limit on the number of values to be downloaded for an offline List of Values
BNE Redirect Portal URL
NOT SET
NOT SET
URL of location Web ADI will redirect to.
BNE Debug Log Filename
SITE
bns2018.log
Name of the Web ADI log file.  If not set, file name is bne.log
BNE Debug Log Level
SITE
TRACE
Level of logging recorded in the Web ADI log file
BNE Debug Log Directory
SITE
/path/to/utl_dir
Directory for Web ADI log file on application server
BNE UIX Base Path
NOT SET
NOT SET
Web server path to the uix/cabo base.
BNE UIX Physical Directory
NOT SET
NOT SET
Physical directory that is mapped to BNE_UIX_BASE_PATH.
BNE Upload Batch Size
SITE
200
Number of uploaded rows in each batch sent from the middle tier to the database
BNE: Enable Upload Compression
NOT SET
NOT SET
Enable Upload Compression for the Instance
BNE Upload Staging Directory
SITE
/path/to/bne/upload
Physical file system directory for upload files
BNE Upload Text Directory
NOT SET
NOT SET
Physical file system directory for imported text files.
BNE Excel Worksheet Maximum
NOT SET
NOT SET
Allows users to set the maximum number of worksheets for a workbook
BNE XML Response Compression
NOT SET
NOT SET
Allows compression XML data

Tuesday, April 24, 2018

Patching to enable Java Web Start (JWS) for EBS R12.1.3

The purpose is to make EBS forms work with browsers on Windows 10. Before this task, my EBS instances work with Microsoft IE11 and JRE 1.8 only on Windows 7 client. The key Oracle document to follow is Using Java Web Start with Oracle E-Business Suite (Doc ID 2188898.1).

First of all, run query to see how many pre-patches are needed. Make sure first 6 patches were applied. If not, apply them first.  After all steps, all below 12 patches shall be installed.
select * from ad_bugs where bug_number in (
'9239090',  -- 12.1.3 Release Update Pack        (applied)
'22284589', -- R12.FWK.B.delta.5                    (applied with OCT17 CPU)
'17932167', -- R12.TXK.B.delta.3                     (applied with OCT17 CPU)
'24693065', -- R12.AD.B (Doc ID 2166227.1)  (applied with OCT17 CPU)
'8919491',  -- R12.ATG_PF.B.delta.3                (applied)
'7461070',  -- R12.AD.B.1 (pre-requisite for patch 23569686)  (applied)
'23569686', -- R12.AD.B.delta.8  (Doc ID 2166227.1)
'24498616', -- R12.AD.B  AD: Add Java Web Start support to EBS
'24319156', -- R12.TXK.B TXK: Add Java Web Start support to EBS
'25380324', -- R12.FND.B EBS Java Applets launching with Java Web Start 
'24709902', -- R12.OWF.B Workflow Java Applets launching with Java Web Start
'25140916'  -- R12.GL.B GL Account Hierarchy Manager
) order by creation_date desc
;


1. Backup file system, and copy/download patch .zip files to a same folder
2. Verify patch 26825525 was applied to 10.1.2 ORACLE_HOME.  If it not, apply it.
Notes: (a) after 26825525 was applied, patch 25441839 is not needed
(b) It is part of October 2017 CPU and also part of January 2018 CPU patch.
(c) Its prerequisite patch: 14825718 ORACLE FORMS BUNDLE PATCH 10.1.2.3.2

$ echo $ORACLE_HOME
/path/to/apps/tech_st/10.1.2
$ vi /etc/oraInst.loc
to make sure it points to the right location
$ opatch lsinventory | grep 26825525
  1) Patch  26825525 applied on Mon Dec 11 12:59:06 EST 2017

3. Apply AD patch 23569686 R12.AD.B.delta.8
Confirmed with DBA that database patch 19393542 is not needed after OCT2018 PSU patch was applied.
$ adadmin  (to turn on Maintenance Mode)
$ unzip p23569686_R12.AD.B_R12_LINUX.zip
$ cd 23569686
$ ls
$ adpatch

Post Step: patch 23569686 has a post step (Doc ID 2166227.1)
$ adadmin => 3. Compile/Reload Applications Database Entities menu => 1. Compile APPS schema.
Type "No" when prompted for:
  Run Invoker Rights processing in incremental mode [No]

4. Apply AD patch 24498616 R12.AD.B
$ unzip p24498616_R12.AD.B_R12_GENERIC.zip
$ cd 24498616
$ ls
$ adpatch  (it runs very quick)

5. Merged 4 patches and then apply it
24319156 R12.TXK.B
25380324 R12.FND.B
24709902 R12.OWF.B
25140916 R12.GL.B
Did NOT need below step in 12.1.0.2 database after OCT2017 PSU patch was applied:
   Additionally the following parameter need to be set to "true" before applying this patch
      "_plsql_conditional_compilation"=true ( refer note 338821.1)
     (From README of patch 25380324, 24709902, 25140916)

$ unzip win10_merge.zip
$ cd win10_merge
$ ls
$ adpatch

6. Apply JRE patch (to web tier).
Follow ID 393931.1 "Deploying JRE (Native Plug-in) for Windows Clients in Oracle E-Business Suite Release 12" to find the latest release. Download the 32-bit for MS Windows patch.
Patch 27638638 for 1.8.0_162. Or
Patch 26595878 for 1.8.0_152 (then, run below lines)
$ cp -p jre-8u152-windows-i586.exe $COMMON_TOP/webapps/oacore/util/jinitiator/j2se18152.exe
$ $FND_TOP/bin/txkSetPlugin.sh 18152

7. Enable JWS (Java Web Start)
$ vi $CONTEXT_FILE
 from <config_option type="techstack" oa_var="s_forms_launch_method">browser</config_option>
     to  <config_option type="techstack" oa_var="s_forms_launch_method">jws</config_option>

8. Fix bug 27138793 : UNABLE TO LAUNCH FORMS AFTER APLYING JWS RELATED PATCHES
Add following 4 lines to file $ORA_CONFIG_HOME/10.1.3/j2ee/forms/config/server.xml
        <shared-library name="oracle.jdbc" version="10.2.0.2" library-compatible="true">
                <code-source path="$COMMON_TOP/java/lib/jdbc6.zip"/>
                <code-source path="$COMMON_TOP/java/lib/orai18n.zip"/>
        </shared-library>
above the last line and replace $COMMON_TOP with real path. If it hits error, check file $LOG_HOME/ora/10.1.3/j2ee/forms/forms_default_group_1/server.log

To preserve that customization from overwritten by autoconfig:
 (a). Backup current server.xml file.
 (b). Create a folder called "custom" under $FND_TOP/admin/template where forms_server_xml_1013.tmp exists.
 (c). Copy forms_server_xml_1013.tmp to the custom folder. (copy and do not move)
 (d). Add 4 lines of code to file custom/forms_server_xml_1013.tmp

9. Run autoconfig (and verify customization was saved in server.xml).

10. Start services
$ adadmin (to disable maintenance mode)
$ ./adstrtal.sh apps/XXXXXX

11. EBS works in browsers (Google Chrome 63.0.3239.xxxx, Firefox 57.0.2, and Microsoft Edge 38.14393.xxx) on Windows 10, and also IE 11 on Windows 7 (with JRE 1.8.0_162 pre-installed) !
It matches results in Doc ID 2188898.1 Section 6: Browser User Experience and Configuration.

NOTES: Doc ID 389422.1 also lists all necessary patches for browsers. 10 patches exist in my instances:
select * from ad_bugs where bug_number in (
'9239090',
'17932167',
'18936791',
'21565052',
'20986782',  -- No patch 20986782. It supersedes the previously recommended patch 19659562.
'19659562',  -- Patch 19659562 was applied in 2015 for IE11. Seems both are not necessary if CRM is not used.
'19486870',
'21482112',
'19273341',
'19559960',
'22284589'

order by creation_date desc;

NOTES: On a Windows 7 VM machine with JRE 1.6 installed, IE 11 failed to launch EBS forms. It gives a popup:
Unable to launch the application
Error: The application has requested a version of the JRE (version 1.8+) that currently is not locally installed. Java Web Start is unable to automatically download and install the requested version. This JRE must be installed manually.

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 & 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 READ ME. 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

$ 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'.
Note that 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

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 --quiet

9. 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:  Following 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 of 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