Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Sunday, April 27, 2008

Extranet tier prompts for username/password twice after clone

Anand called me today and described a problem being faced:

After clone of extranet tier, when username and password are keyed in the first time, the username and password fields become blank and the user is prompted again for entering them. After the user enters the username and password the second time, the Self Service Page is shown. I asked him to tail the Apache log and see what corresponding entries were getting formed when we were doing this. This is how they looked:

192.168.15.1 - - [27/Apr/2008:10:44:14 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?requestUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2FOA_HTML%2FOA.jsp%3Fpage%3D%2Foracle%2Fapps%2Ffnd%2Fframework%2Fnavigate%2Fwebui%2FHomePG%26homePage%3DY%26OAPB%3DFWK_HOMEPAGE_BRAND%26transactionid%3D1622860638%26oapc%3D2%26oas%3D5PZsF5fcdnhFfJ1R0rPWPA..&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&errText=You+have+insufficient+privileges+for+the+current+operation. HTTP/1.1" 302 705 0
192.168.15.1 - - [27/Apr/2008:10:44:15 -0400] "GET /OA_HTML/AppsLocalLogin.jsp?requestUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2FOA_HTML%2FOA.jsp%3Fpage%3D%2Foracle%2Fapps%2Ffnd%2Fframework%2Fnavigate%2Fwebui%2FHomePG%26homePage%3DY%26OAPB%3DFWK_HOMEPAGE_BRAND%26transactionid%3D1622860638%26oapc%3D2%26oas%3D5PZsF5fcdnhFfJ1R0rPWPA..&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&errText=You+have+insufficient+privileges+for+the+current+operation.&s1=IinGd45sClw4CKn4cMrklw.. HTTP/1.1" 200 7261 1
192.168.15.1 - - [27/Apr/2008:10:44:52 -0400] "POST /OA_HTML/fndvald.jsp HTTP/1.1" 302 412 0
192.168.15.1 - - [27/Apr/2008:10:44:52 -0400] "GET /OA_HTML/OA.jsp?page=/oracle/apps/fnd/framework/navigate/webui/HomePG&homePage=Y&OAPB=FWK_HOMEPAGE_BRAND&transactionid=1622860638&oapc=2&oas=5PZsF5fcdnhFfJ1R0rPWPA.. HTTP/1.1" 200 11180 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /OA_HTML/OALogout.jsp?menu=Y HTTP/1.1" 302 270 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogout HTTP/1.1" 302 320 0
192.168.15.1 - - [27/Apr/2008:10:45:00 -0400] "GET /OA_HTML/AppsLocalLogout.jsp?returnUrl=http://extdev.justanexample.com:8000/oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 281 0
192.168.15.1 - - [27/Apr/2008:10:45:01 -0400] "GET /oa_servlets/oracle.apps.fnd.sso.AppsLogin?langCode=US HTTP/1.1" 302 435 0
192.168.15.1 - - [27/Apr/2008:10:45:01 -0400] "GET /OA_HTML/AppsLocalLogin.jsp?requestUrl=APPSHOMEPAGE&cancelUrl=http%3A%2F%2Fextdev.justanexample.com%3A8032%2Foa_servlets%2Foracle.apps.fnd.sso.AppsLogin&langCode=US&s2=34554EE5947F1746754F671CF150A536B02E53E4049CFB609BE4970DB131D03B HTTP/1.1" 200 8510 0

When the user was prompted for the password again, in the Apache logs it was showing this:

AppsLogin&errText=You+have+insufficient+privileges+for+the+current+operation. HTTP/1.1" 302 705 0

Seeing this error, Anand checked for the value of the profile option PON: External URL and PON: Default External User Responsibility:

SQL> select fnd_profile.value('PON_EXT_LOGIN_URL') from dual;

FND_PROFILE.VALUE('PON_EXT_LOGIN_URL')
--------------------------------------------------------------------------------
http://ext.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp

SQL> select fnd_profile.value('PON_DEFAULT_EXT_USER_RESP') from dual;

FND_PROFILE.VALUE('PON_DEFAULT_EXT_USER_RESP')
--------------------------------------------------------------------------------
PON_SOURCING_SUPPLIER

The profile option PON: External URL was still having the value of Production URL ext.justanexample.com instead of extdev.justanexample.com. He change this value to extdev.justanexample.com so that:


SQL> select fnd_profile.value('PON_EXT_LOGIN_URL') from dual;

FND_PROFILE.VALUE('PON_EXT_LOGIN_URL')
--------------------------------------------------------------------------------
http://extdev.justanexample.com:8000/OA_HTML/AppsLocalLogin.jsp

Apache was bounced.

The issue was resolved.

Saturday, April 26, 2008

Apps binaries write core files in /var/crash/cores

Ever since we migrated to Solaris 10, we have had various concurrent program binaries creating core dumps. Some of them get resolved after we relink the binaries. But some continue core dumps even after relinking them. ARGLTP or the AR to GL Transfer Program is one of the binaries which does this. I have had an SR logged with Oracle for some time. The strangest thing is that the concurrent requests calling ARGLTP complete normal. But for every run of ARGLTP a coredump with signal 11 is created. Here's a stack trace:

adb /erp11i/erp/appl/ar/11.5.0/bin/ARGLTP
1208446806-ARGLTP-15767-62984-54011-tsgsd1009-sun4u
core file = 1208446806-ARGLTP-15767-62984-54011-tsgsd1009-sun4u -- program
``/erp11i/erp/appl/ar/11.5.0/bin/ARGLTP
'' on platform SUNW,Netra-T12
SIGSEGV: Segmentation Fault
$c
libc.so.1`strcmp+0x170(1739c8, 73657364, 0, 0, 1d0634, 1d0614)
afppre+0x1cc(2a81e0, 261b88, 50, 50, ff1ea2b4, ff1f29f8)
fdpcls+0x3e4(4e, 1c2b28, 1c2b18, 1c2800, 1c2800, 1)
main+0x76e0(14f730, 14f774, ffbf9fbc, a, 2a4760, 7f80)
_start+0xdc(0, 0, 0, 0, 0, 0)
$q

A common thing in all the coring binaries is the OS library libc.so.1. Oracle has asked us to check with Sun if this is a known issue and if a new version of libc.so.1 is available. I have an SR logged with Sun also for this.

We finally got a solution. Patch 6815663 is a post ATG RUP6 one off patch which fixes this issue. Since it is not fully regression tested, it is protected by a password. You have to log an SR to get the password. After applying this patch, we have seen that none of the binaries like ARGLTP, PARGDR, PAVDVC, PASGLT etc. are coring anymore.

Friday, April 25, 2008

DBUA vs manual upgrade

While upgrading an Oracle Database, which is better DBUA or manual upgrade ?

Arguments for DBUA:
1. DBUA takes care of all the pre-install checks like kernel parameters, memory, space etc.
2. DBUA gives the option of automatic RMAN backup of the existing DB
3. DBUA upgrade is faster than manual upgrade.(As DBUA automatically sets job_queue_processes)
4. Easier to use.

Arguments against DBUA:
1. If there are errors during the upgrade, there is usually no way to resume. In many cases, where a single or multiple components of the upgrade have failed, you can restart DBUA and it takes care of the missed steps without issues.
2. Manual upgrade gives you absolute control. You know exactly where you are in the upgrade process. Tracking this in DBUA involves watching DBUA logs, Alert log at the time DBUA is running. I never liked the progress bar. It doesn't convey anything. Along with progress bar, DBUA should also show DB alert log and what script is being executed. That helps in debugging and keeps you in touch with the upgrade instead of insulating you from it.
3. DBUA takes the value of sga and other memory settings from your 9i database and creates 10g/11g init.ora based on those. Unless you pre-size your 9i init.ora, you may get ORA-4031 errors during the actual upgrade with DBUA.

Most of the time, when DBUA screws up, you have to continue your upgrade by abandoning DBUA and take a detour to manual upgrade. E-Business Suite Databases are not small, and do not provide you the luxury of restoring backup and starting with DBUA again.

CTXSYS and PORTAL30 invalids after upgrade

Benette asked me about CTXSYS and PORTAL30 invalids which had resulted after upgrade. I asked him for a list and this is what he gave me:

APPS Package Body AST_OFL_TERR_MISS_ASGN_REPORT
APPS Package Body FND_OID_DIAG
CTXSYS Procedure WWSBR_CORNER_CTX_202
CTXSYS Procedure WWSBR_DOC_CTX_202
CTXSYS Procedure WWSBR_PERSP_CTX_202
CTXSYS Procedure WWSBR_THING_CTX_202
CTXSYS Procedure WWSBR_TOPIC_CTX_202
PORTAL30 Package WWV_MONITOR_WEBVIEW
PORTAL30 Package WWV_QBE_TABLE
PORTAL30 Package WWV_RENDER_QBE
PORTAL30 Package WWV_SYS_RENDER_QBE
PORTAL30 Package Body DBE_TABLE_ACTION
PORTAL30 Package Body WWV_AK
PORTAL30 Package Body WWV_COMPONENT_CONTROL
PORTAL30 Package Body WWV_MENU_BUILD
PORTAL30 Package Body WWV_MONITOR_DATABASE
PORTAL30 Package Body WWV_MONITOR_WEBVIEW
PORTAL30 Package Body WWV_QBE
PORTAL30 Package Body WWV_QBE_PROC
PORTAL30 Package Body WWV_QBE_TABLE
PORTAL30 Package Body WWV_QBE_UTILITY
PORTAL30 Package Body WWV_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_PAGINATE
PORTAL30 Package Body WWV_SYS_RENDER_QBE
PORTAL30 Package Body WWV_SYS_RENDER_QBEFORM
PUBLIC Synonym DEBUG_CARTX

The simple answer to all the invalids above is: "Drop them, they are not needed." The Metalink Notes which advise this are:

444348.1 for Portal30 invalids
458370.1 for CTXSYS invalids which are calling the PORTAL30 invalids
352808.1 advises dropping AST_OFL_TERR_MISS_ASGN_REPORT as it is no longer used.
380480.1, 386746.1 and 444348.1 advise dropping DEBUG_CARTX
360285.1 advises dropping FND_OID_DIAG if you are not using OID/SSO.

Thursday, April 24, 2008

TNS issues

Recently in a fresh installation, TNS listener refused to start with these errors:

TNS-12546 / ORA-12546: TNS:permission denied
TNS-12560 / ORA-12560: TNS:protocol adapter error
TNS-00516: permission denied
Solaris Error: 13: permission denied

I checked the port on which we were trying to start:

$ telnet localhost 1521
Trying 127.0.0.1...
telnet: connect to address 127.0.0.1: Connection refused
Trying ::1...
telnet: Unable to connect to remote host: Network is unreachable

This means the port is free

I checked the directory /var/tmp/.oracle

$ ls -ld /var/tmp/.oracle
drwxrwxrwt 2 root root 2560 Apr 22 11:10 /var/tmp/.oracle

Even though the permission was 777 on this directory, it was unable to write to it. So I gave ownership of this directory to oracle user

su - root
chown oracle:dba /var/tmp/.oracle

I started listener again. It worked. ls -ltr /var/tmp/.oracle showed two new files:

srwxrwxrwx 1 oracle dba 0 Apr 23 14:26 sEXTPROCgpslmpd1
srwxrwxrwx 1 oracle dba 0 Apr 23 14:26 s#23310.1

If you issue a file command on these:

$ file sEXTPROCgpslmpd1
sEXTPROCgpslmpd1: socket
$ file s#23310.1
s#23310.1: socket
oralmpd1@tsgsd1007 #

These are not files but sockets. Because the ownership was not with oracle, it was unable to create a socket in this directory. Since listener had started and socket was created, I changed back the permission of this directory to root:

su - root
chown root:root /var/tmp/.oracle

I shutdown and restarted listener just to double check and it worked fine.

Tuesday, April 22, 2008

ICX_TRANSACTIONS_U1 ORA-0142

One of the DBAs got this error while doing an E-biz patch through adpatch:

The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:

Start time for statement below is: Tue Apr 22 2008 18:19:38

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX

AD Worker error:
The following ORACLE error:

ORA-12801: error signaled in parallel query server P000
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found


occurred while executing the SQL statement:

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS PARALLEL TABLESPACE ICXX

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above


Solution is:

Execute the following SQL to prevent errors during Patch Application through adpatch:
SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1
If the Above query returns any Row then Please execute the following SQL :
$ICX_TOP/sql (named ICXDLTMP.sql).

Keep in mind: This program has to be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION (otherwise they will grow out of control).

Monday, April 21, 2008

Configure workflow notification mailer without IMAP

This is one my favorite interview questions, which has stumped many a great candidate. Question is "I do not need inbound processing, is there a way to configure workflow notification mailer without using IMAP ?" The candidate who is asked this question, goes blank. Details of implementing it is given in metalink note 268274.1:

How To Configure The Workflow Java Mailer Without An IMAP Account (Assumes No Inbound Processing)

Goal
How To Run The Java Workflow Mailer Without Inbound Notification Processing

If you would like to apply 11i.OWF.G you must implement the new java notification mailer,
This requires you to have an IMAP4 compliant email account for inbound notification
processing and a SMTP account for outbound processing. Unless both are configured correctly
you are unable to complete the setup of the mailer as the verification page (page 3-4) in OAM
never completes (unless both inbound and outbound email servers are configured and setup corretly.

Some Customers only use the outbound email processing from the mailer and do not require
inbound processing.

Solution

Steps to setup the WF Mailer for Outbound Processing only

Please apply patch:3409889 to install this new functionality. After applying the patch, you need to perform the following steps:

1:- Login to OAM
2:- Choose the Workflow Manager screen
3:- Choose Service Components
4:- Edit the "Workflow Mailer"
5:- Navigate to page 3 of Workflow Configuration
6:- Set Inbound Thread Count = 0 (zero)
7:- If you use outbound processing set Outbound Thread Count = 1
8:- In the Inbound EMail Account details please enter any data
(this cannot be null)
9:- In the Outbound EMail Account details add the details for your outbound server
10:- Shutdown and restart the application server and the mailer

Setting inbound thread count = 0 makes the OAM Configuration screen to not perform and verification of the IMAP server. Therefore, this allows you to continue with the setup of Workflow Mailer.

NOTE: For standalone ONLY:

You will have the code within workflow 2.6.3.5 which does not validate the IAMP server
if the Inbound Thread Count is set to Zero, if you upgrade to iAS10.1.2 and use
Content Manager 9.0.4.2.
References
Bug 3178398 - In 11.5.9 Must Use Email Processing When Responding To Notifications
Note 231286.1 - Configuring the Oracle Workflow 2.6 Java-based Notification Mailer with Oracle Applications 11i

Run a concurrent program from command line

It is very much possible to run a concurrent program from unix command line. Metalink Note 1031719.6 describes how to run some GL programs from command line. We can use these instructions as base for being able to run any concurrent program from command line.

Problem Description
-------------------

How do you run Posting (GLPPOS), or other concurrent programs from the
command line?


Solution Description
--------------------

You can run any GL program from the command line. Follow these steps:

1) Verify that you run this at a time when there are no incompatible
programs running. To find the incompatible programs:

Responsibility = System Administrator
GUI Navigation = Concurrent/Program/Define

Query on GLPPOS in the Short Name field.
Click on the Incompatibilities button.

2) Change the profile "Concurrent Hold Request" to Yes.

Responsibility = System Administrator
GUI Navigation = Profile/System

Query on the profile option 'Concurrent Hold Requests'.
Set it to Yes at the User level and Save.

3) Select a journal batch for posting. The request will be put on hold.

Responsibility = General Ledger Super User
GUI Navigation = Journals/Post

4) Go to the Concurrent Requests form and get the parameters for the
Posting concurrent process.

The parameters for posting are:

set of books id
chart of accounts id
posting_run_id

5) Run posting from the command line.

$GL_TOP/bin/GLPPOS 0 Y X 2>

Where: : apps username and password
: The parameters for the program,
separated by spaces. For NULL parameters
you need to specify closed double quotes ("").
X : Indicates that you want to run the program
in debug mode. This can be any character and
is optional.
2> : Use this to redirect standard errors and
output to a file. File name is the output
file name it will create. It will be created
in your current directory, if a full path
is not specified. This is optional
but recommended.

Example:
If you go to the concurrent requests form and see in the
Parameters field: 1,101,2546, you would enter the following command
at the Unix prompt:

$GL_TOP/bin/GLPPOS 0 Y 1 101 2546 X 2>

6) Change the profile option 'Concurrent Hold Requests' back to its
original setting.


Be sure to run the program from a directory for which you have write
privileges. That is where the log and out files will be created.
Also make sure that you are in the proper application environment.


Here is an example of the command and the output you will see.

FIN107>$GL_TOP/bin/GLPPOS apps/apps 0 Y 8 50128 84 2 2> njpost2.out
DEBUG: started in debug_mode ......

FIN107> ls
L54536.log L54540.log O54536.out O54540.out njpost.out post.out
L54538.logL54542.log O54538.out O54542.out njpost2.out test
FIN107> more njpost2.out
Log filename : L54542.log
Report filename : O54542.out
glpmai() fnd_user_id is -1
glpmai() fnd_user_name is ANONYMOUS
glpmai() req_id is 0
glpmai() sob_id is 8
etc.

NOTE: In Release 11.5, selected General Ledger programs can be run in debug
mode by setting the profile option 'GL: Debug Mode' to Yes at the
appropriate level.
These programs are:
- GLCRVL - Revaluation
- GLPPOS - Posting
- GLTTRN - Translation
- GLCCON - Consolidation Transfer
- GLAMAS - Run MassAllocations

See Note 232669.1: 'How To Run Selected General Ledger Programs In Debug Mode
in R11.5' for more information.

Thursday, April 17, 2008

Corrupt label, wrong magic number

I found the following in /var/adm/messages:

Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a7,0 (ssd1):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a7,0 (ssd1):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@18,600000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a9,0 (ssd0):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@18,600000/SUNW,emlxs@1/fp@0,0/ssd@w50060482d52d43a9,0 (ssd0):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number
Apr 17 14:40:05 erpdbbox1 scsi: [ID 107833 kern.warning] WARNING: /ssm@0,0/pci@19,700000/SUNW,emlxs@2/fp@0,0/ssd@w50060482d52d43a8,0 (ssd2):
Apr 17 14:40:05 erpdbbox1 Corrupt label; wrong magic number

The "corrupt label" warning means that the disk doesn't have a Solaris label on it yet.

You must label your Solaris disk to get rid of this message.

Command line diagnostic tests for notification mailers

While searching for something else on metalink, my eyes caught the phrase "command-line diagnostic" in the search results. This was present in the readme of ATG_PF.H RUP6 patch:

You can now dedicate a notification mailer to process only instances of a particular message from a particular item type by specifying both the item type and the message name within the correlation ID.You can run command-line diagnostic tests for notification mailers through the new oracle.apps.fnd.wf.mailer.Mailer program.

* Test connectivity to the IMAP mail server.
* Test connectivity to the SMTP mail server.
* Test connectivity to the Web tier, which is required for the notification mailer to generate notifications that include Oracle Application Framework content.
* Check the number of messages in an IMAP folder or the total size of the messages in the folder in bytes, which indicates how much space you can regain by purging messages from the folder.

More details about these tests are given in Metalink Note 332152.1

Java Mailer Diagnostics for HTTP Framework Region:

You need to have minimum ATG CU2 applied:

$AFJVAPRG -classpath $AF_CLASSPATH -Dnid= \
-Ddbcfile= \
[ -Dappuser= \
-Dappresp= \
-Dappid=\
-Dhtp= \
-Durltimeout= (Default 30)]\
[ -DAFLOG_ENABLED=true \
-DAFLOG_FILENAME=test5b.log \
-AFLOG_LEVEL= ] \
oracle.apps.fnd.wf.mailer.Mailer

Note: When specifying -Dhtp=https, the LD_LIBRARY_PATH environment variable should be replaced with $AF_LD_LIBRARY_PATH. Otherwise, "java.lang.UnsatisfiedLinkError: initSSLContextNative" will be encountered.


NON-SSL TEST:

Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification and the dbc filename (-Ddbcfile).

==============Begin garymailertest.sh=================

. APPSORA.env

$AFJVAPRG -classpath /tmp:${AF_CLASSPATH} -Dnid=841969 -Ddbcfile=$FND_TOP/secure/orlncatst-02_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=http -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer

=============End garymailertest.sh=====================


SSL TEST:

Modify in a text editor to make sure the command is listed on one line as it is one long statement. You need to specify the notification_id (-Dnid) of the failing notification and the dbc filename (-Ddbcfile).

==============Begin garymailertestSSL.sh===============

. APPSORA.env

export LD_LIBRARY_PATH=${AF_LD_LIBRARY_PATH}

$AFJVAPRG -classpath $AF_CLASSPATH -Dnid=841969 -Ddbcfile=$FND_TOP/secure/aoldev-pc_vis.dbc -Dappuser=0 -Dappresp=20420 -Dappid=1 -Durltimeout=120 -Dhtp=https -DAFLOG_FILENAME=mailerTest.log -DAFLOG_LEVEL=STATEMENT -DAFLOG_ENABLED=true oracle.apps.fnd.wf.mailer.Mailer

============End garymailertestSSL.sh===============

Java Mailer Diagnostics for HTTP Framework Region Screen Output:

Mailer : oracle.apps.fnd.cp.gsc.Logger.Logger(String, int) : Logging to System.out until necessary parameters are retrieved for Logger to be properly started.
URL {https://aoldev-pc.us.oracle.com:8443/OA_HTML/OA.jsp?page=/oracle/apps/fnd/wf/worklist/webui/NotifMailerPG&WFRegion=NtfDetail&NtfId=841969&dbc=aoldev-pc_vis&OALAF=blaf&OARF=email}
Connection to application server was successful and retrieved
3392 characters plus 4 referenced objects.
Time taken to establish the connection and
obtain the content was 31.627 seconds

Content ID File name and Content Type

========== ==========================

24537094 /OA_HTML/cabo/images/errorl.gif image/gif
5612344 /OA_HTML/cabo/images/cache/cmbte-1.gif image/gif
8344960 /OA_HTML/cabo/images/t.gif image/gif
16625677 /OA_HTML/cabo/images/cache/cmbbn-1.gif image/gif

NOTE: Upload the information echoed to your session and the -DAFLOG_FILENAME=mailerTest.log and test.log which contains quite a bit of Statement log level data.

Wednesday, April 16, 2008

Mobile patches

There is no separate product called MWA (Mobile Wireless Access) though we have an MWA_TOP. All SRs logged for MWA are assigned to WMS(Warehouse Management System) or INV/RCV. To have a stable mobile server which doesn't crash often, Oracle has recommended that you should be on:

Latest Oracle Inventory & Receiving (PO) Rollup (RUP) Patches:

Rollup Patch

Patch Number

11.5.10 INV/RCV RUP6

6461517

Latest Oracle Oracle Warehouse Management System Rollup (RUP) Patches:

Rollup Patch

Patch Number

11.5.10 WMS RUP4

5855276


Tuesday, April 15, 2008

April 2008 Critical Patch Update

Refer to Metalink Note 557157.1 for the April 2008 Critical Patch Update.

In nutshell:

Database patch 6864068 for DB 10.2.0.3
No new patches for Application Server, Developer Suite or Jinitiator if you are on Jan 2007 CPU or higher and Jinitiator 1.3.1.29 or Sun JRE
You need to be on ATG RUP5 or 6 as a baseline on top of which you need to apply 4 E-biz patches:
Another interesting fact given is that the last CPU for Oracle 10.2.0.3 will be in Jan 2009.

Exception: Invalid index

Nilesh asked me for help. He was unable to launch forms from self service pages. The status bar would show: Exception invalid index. When I checked the java console, I found these errors:

Loading http://justanexample.com:8000/OA_JAVA/oracle/apps/fnd/jar/fndlist.jar from JAR cache

sun.misc.InvalidJarIndexException: Invalid index

at sun.misc.URLClassPath$JarLoader.getResource(Unknown Source)
at sun.misc.URLClassPath$JarLoader.getResource(Unknown Source)
at sun.misc.URLClassPath.getResource(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at sun.applet.AppletClassLoader.findClass(Unknown Source)
at sun.plugin.security.PluginClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.applet.AppletClassLoader.loadCode(Unknown Source)
at sun.applet.AppletPanel.createApplet(Unknown Source)
at sun.plugin.AppletViewer.createApplet(Unknown Source)
at sun.applet.AppletPanel.runLoader(Unknown Source)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

This was happening only on Nilesh's laptop. The usual suspect is JAR cache. I got him to delete his JAR cache present in "C:\Documents and Settings\Nilesh\Oracle Jar Cache" subdirectory. The problem was solved after new jar files were downloaded.

Friday, April 11, 2008

Cannot read value from field PARAMETER.CONFIG'

This error appeared when adstrtal.sh was done on an instance. This came immediately after upgrading the OS of the instance from Solaris 8 to Solaris 10. So the usual suspect was the OS upgrade. However 356434.1 says that this happens when GUEST user is end dated. We queried the DB:

SQL> select end_date from fnd_user
2 where user_name='GUEST';

END_DATE
---------------
11-APR-08

SQL>

Sure enough it was end-dated. What a co-incidence ! It was end dated with the date on which OS upgrade was planned. So we issued this command:

update fnd_user set end_date=null where user_name='GUEST';

Issue was solved.

Wednesday, April 9, 2008

Cannot complete applications logon

Recently after a clone none of the services would start up and gave error like this one:

Cannot complete applications logon. You may have entered an invalid applications password, or there may have been a database connect error.
Apache Web Server Listener :httpd ( pid 21496 ) is running.

We checked for GUEST password and it was ORACLE in system profile option and encrypted passwords. However the services wouldn't start. If we changed the GUEST password to GUEST the Apache services started but Jserv wouldn't start:

JVM logs had this error:

Exception in static block of jtf.cache.CacheManager. Stack trace is: oracle.apps
.jtf.base.resources.FrameworkException: oracle.apps.fnd.common.PoolException: No
t able to create new database connection: FNDSECURITY_APPL_LOGIN_FAILED

It was clear that GUEST password was out of sync. Since we could not pinpoint where the issue was we followed this strategy to solve this issue:

1. Change the s_guest_passwd to GUEST/GUEST in context file and run autoconfig.
2. Checked everything was working fine, including Apache, Jserv, Forms
3. Changed back s_guest_passwd to GUEST/ORACLE in context file and run autoconfig.

It worked fine after this.

Sandhya reported another instance with the same symptoms, however the above 3 points were correct.  Something had gone wrong with the encryption of passwords.  The encryption had the old production password, but the database had the new apps password.  Changing back the APPS and APPLSYS passwords back to its production value, through alter user command solved it.

Monday, April 7, 2008

ssh user equivalence

I found two metalink notes which have detailed step by step instructions to do ssh user equivalence:

372795.1
329476.1 Read the section about setting up ssh.

You can stop using rsh/rcp and set up ssh using these notes easily. rsh/rcp has following disadvantages:

1. It is not secure.
2. rcp uses standard ports below 1024 to transfer files, Available free ports in this range are free, so if you have to rsh a lot of files in parallel, it won't succeed.

Friday, April 4, 2008

ALECDC, ARGLTP, XDPCTRLS, FNDLIBR writing core files

After moving the storage to Solaris 10 or cloning from Solaris 8 box to Solaris 10 box, in a few instances the /var/crash/cores directory was reaching 100%. It was full of cores written by ALECDC, ARGLTP, XDPCTRLS, FNDLIBR, MSONEW, MSCSLD etc. I logged an SR with Oracle and they asked me for a stack trace. Metalink Note 1812.1 describes how to get a stack trace from a core file. Here's what I did:

Stack trace for ARGLTP

$ adb /apps11i/erp/appl/ar/11.5.0/bin/ARGLTP
1207343405-ARGLTP-16927-66128-54055-tsgsd1007-sun4u
core file = 1207343405-ARGLTP-16927-66128-54055-tsgsd1007-sun4u -- program
``
/apps11i/erp/appl/ar/11.5.0/bin/ARGLTP'' on platform SUNW,Netra-T12
SIGSEGV: Segmentation Fault
$c
libc.so.1`strcmp+0x60(174ba4, 73737431, 0, 0, 1d1900, 1d18e0)
afppre+0x1cc(2a9900, 262b50, 50, 50, ff1ea2b4, ff1f29f8)
fdpcls+0x3e4(4e, 1c3df4, 1c3de4, 1c3c00, 1c3c00, 1)
main+0x7704(14fc30, 14fc74, ffbf9efc, a, 2a5e28, 7f80)
_start+0xdc(0, 0, 0, 0, 0, 0)
$q

We nailed it finally after long discussions with Oracle. It was a bug. After ATG_PF.H RUP6 when concurrent programs try to write Completed successfully, a variable was not initialized in the code. Because of this even though the concurrent program completed successfully, it used to coredump before it was able to write to its log file: Completed successfully. Applying Patch 6815663 resolved this issue.

System error 53 - The network path was not found

In our loftware Windows 2000 box we have mounted a NAS device as Z:. It stopped working a few days ago. Mapping it with net use z: \\nasmount1\loftware again gave this error:

System error 53 - The network path was not found.

Symptom: when using net view ip or computername, you get system error 53. Resolutions:

1) if it is domain environment, check your WINS;
2) if it is peer-to-peer workgroup, enable NetBIOS over TCP/IP;
3) make sure the machine is running;
4) make sure file and Printer Share enabled on remote computer;
5) make sure client for ms networks is enabled on local computer;
6) make sure you type the correct name.

However all of the above were ok. On top of that, it mounted fine if I tried the net use command on my laptop. After many hours of trial and error, I realized that I am working from home and connected through VPN. I pinged a few colleagues in office and asked them to try the net use command. They got the same error as the loftware box. It looked like firewall settings had been changed. I installed VPN client on the loftware box and connected through VPN. Voila, net use command worked fine. I have to now talk to the firewall guys to find out what changed and revert to previous state.

Thursday, April 3, 2008

150 Opening ASCII mode data connection hangs

I was asked to investigate an ftp issue in one of our test environments. ftp was being done through a concurrent program which was connecting to an external site and sending files:


$ ftp external.site.on.internet.com 39000

Connected to external.site.on.internet.com

Name (external.site.on.internet.com:user1): user1

331 Password required for user1.

Password:

ftp> dir

150 ASCII data connection (192,168,29,1,211,181 ).

Hangs here

Any command like dir or get or put would hang. However if we executed the very same commands from our laptops, it worked fine. Clearly something was blocking it. I did a network trace of connections through Wireshark and found that ftp was actually using two ports: 39000 and 39001. An explanation of this behavior is given in RFC 1579:

   The FTP protocol [1] uses a secondary TCP connection for actual
transmission of files. By default, this connection is set up by an
active open from the FTP server to the FTP client. However, this
scheme does not work well with packet filter-based firewalls, which
in general cannot permit incoming calls to random port numbers.

If, on the other hand, clients use the PASV command, the data channel
will be an outgoing call through the firewall. Such calls are more
easily handled, and present fewer problems.
However the ftp server did not support passive mode. Since the network trace gave us the information that two ports were being used, we have requested for firewall to be opened for incoming traffic from this site on those two ports. I'll update the post, once this gets done.

isRtl(java.lang.String) has protected access in oracle.apps.fnd.sso.SessionMgr

In one of our instances, after applying ATG_PF.H RUP6, July 2007, Oct 2007 and Jan 2008 CPU patches, the login page (AppsLocalLogin.jsp) did not come up. We got HTTP-500 Internal Server Error. Apache logs showed this:

Apache access_log showed:

192.168.3.5 - - [02/Apr/2008:16:33:13 -0400] "GET /OA_HTML/AppsLocalLogin.jsp HTTP/1.1" 500 544 2

jserv.log showed:

[02/04/2008 16:58:26:550 EDT] JspServlet: unable to dispatch to requested page:
Exception:oracle.jsp.provider.JspCompileException:

Errors compiling:/apps11i
/comn/_pages/_oa__html//_AppsLocalLogin.java

Line #Error

416

[jsp src:line #:308]
isRtl(java.lang.String) has protected access in oracle.apps.fnd.sso.SessionMgr
if (SessionMgr.isRtl(langCode)) {


I had never seen such an error and presumed that it could be due to the patches. Later on in the day, Akhilesh called me to say that the issue is resolved. He said that the project team had asked the DBAs to do a code move which contained a customized AppsLocalLogin.jsp. Once they replaced it with the standard AppsLocalLogin.jsp the issue was resolved. I never thought that someone would customize AppsLocalLogin.jsp. I learnt that the project team wanted to put some disclaimers on the login page because of which they were customizing it. Metalink Note 468971.1 describes tips for personalizing AppsLocalLogin.jsp. The project team had logged an SR with Oracle with this query:

Hi we have to add the copyright message at the login page in Bold and in red color for that we have updated the message FND_SSO_SARBANES_OXLEY_TEXT . then submited the "Generate Messages" and also updated the profile value "Local Login Mask" with 96 and finally bounced the appache , but our message is not coming on the single screen and and we have to scroll down to right of the screen to read that . we have tried to add the HTML tags in the
message but thay are cmoing as it is in the screen without any effect , can you please help in achiving this requirment .


Solution given by Oracle which involved editing AppsLocalLogin.jsp:

ANSWER
=======
Please review the Note 391826.1 FND_SSO_SARBANES_OXLEY_TEXT Does Not Line Wrap On AppsLocalLogin.jsp

Solution

The following solution is a workaround. You make the following change at your own risk. Furthermore, the following change wil
l be lost when applying patches that replace $OA_HTML/AppsLocalLogin.jsp. In thi
s case it is up to you to manage this change.

1. Backup $OA_HTML/AppsLocalLogin.jsp

2. edit AppsLocalLogin.jsp with a text editor such as vi

3. change the line :
StyledTextBean legalMessage = new StyledTextBean();
to:
RawTextBean legalMessage = new RawTextBean();

4. Save

5. cd $OAD_TOP/_pages/_oa__html/, rm *AppsLocalLogin*

6. Change the Sarbanes Oxley text so that your using html tags to place line breaks using navigation: Application Developer - Messages

7. Bounce Apache

The text should now wrap

Seems it didn't work, because project team replied:

Hi,

Did the changes suggested. There no change except the font size and color. Now its getting displayed in RED color.
Attached the screen shot of the output.

changes done were:
1. Download file AppsLocalLogin.jsp fron $OA_HTML.
2. Comment the Styled TextBean and Add RawTextBean
//StyledTextBean legalMessage = new StyledTextBean();
RawTextBean legalMessage = new RawTextBean();

UPDATE
=======
Hi Vijay,

As the text is now interpreted as html code, you can use
tag in the text FND_SSO_SARBANES_OXLEY_TEXT to make the text to wrap.
Please upload the FND_SSO_SARBANES_OXLEY_TEXT text if you need help to customize
the message.

Thanks,
Adrian

Hi Adrian
please find the attcahed message we have put for FND_SSO_SARBANES_OXLEY_TEXT .
but it is showing the message in the single line , but when we use the same in normal HTML file it is coming properly
.
Regards

Regards
Vijay Sharma



26-MAR-08 13:55:08 GMT

.
UPDATE
=======
Hi Vijay,

Please try the followings text for the FND_SSO_SARBANES_OXLEY_TEXT :

My Oracle ERP is a non-export controlled system. All technical information disclosed or otherwise provided (orally, electronically, visually or in writing) shall at all times be subject to U.S.A. export control laws and regulations.

Accordingly all technical data and information, technical assistance and software disclosed or otherwise provided by Justanexample Company, and any product thereof,
shall not be exported directly or indirectly from the U.S.A. unless
explicitly permitted by Justanexample Company in writing in accordance with U.S.A. export control laws and regulations.
It is understood that “export” includes re-export and any disclosures within a country to a citizen or resident of another country.
The aforesaid obligations shall be without limitation as to time.



Thanks,
Adrian

After this the TAR was closed. I'll find out if that really solved the issue.

Tuesday, April 1, 2008

How to compile a java class in Oracle database

The short answer is:

alter java class "java class name" resolve;

For example: alter java class "/e6bc455_DocumentGenerator" resolve;

The long answer is given in the post titled "Long road to loadjava" in Frederic Tang's blog.

The following metalink notes describe some well known invalid java classes:

363679.1
301345.1
339962.1
471901.1