Unreleased ATG_PF.H RUP6 patch number is 5903765

Metalink note 444488.1 Fnd_logins Table Not Capturing Logins When Password Is Being Reset refers to ATG_PF.H RUP6 patch 5903765 as the fix. The bug itself is interesting.

When passwords are reset by administrators, expires, or when a new user is created, the next time a user logs into Oracle Applications, they are taken to a password reset screen when logging in - AppsChangePassword.jsp. After changing the password, the user is then brought into the applications home page. The user is then free to utilize the application.
However, in this scenario of a password reset, when a user is forced through the reset password screen, the application is NOT capturing this information in the fnd_logins table.



Cause
This issue is documented in Bug 5985964.
Updated FND_SIGNON package to correctly detect sign-on auditing settings when password is changed.

Fixed Version: AFSCSGNB.pls 115.41

Why is it called Apps and not Oracle ERP ?

A very interesting post by Anil Passi on his site:

http://getappstraining.blogspot.com/2006/10/why-call-it-apps-and-not-oracle-erp.html

I am pasting the main content from Anil's site. But do visit it to go through all the comments posted by readers

Why call it Apps and not Oracle ERP ?
Ever wondered why is Oracle’s eBusiness Suite nicknamed apps?

Is it just the short name of Oracle Applications? Possibly yes, however this question is an excuse for me to explain to you the evolution of APPS schema.

I started working in Oracle Financials 9 years ago. Those days each module had its own database schema(which we still have). However, a purchasing user (until version 10.6) used to connect to PO schema (by the virtue of the screen being a PO screen).

Hence, if a report or screen of AR ( Oracle Receivables ) module wanted to access a table named PO_HEADERS_ALL, they would then use notation PO.PO_HEADERS_ALL

However, now we have several database schemas(in most cases one schema per module).

The tables are still owned by their respective schema, but now we have a central schema named APPS. Oracle ERP simply connects to APPS database schema for all its operations(with a couple of exceptions that are best ignored for now).

Hence, if Oracle wants to create anew table named PO_HEADERS_ALL, they will do the following

Step 1. Connect to po/po@XX_DEVDB
Create table PO_HEADERS_ALL ( ...all columns here )

Step2. Grant all on po_headers_all to apps ;

Step 3. connect to apps/apps@XX_DEVDB
Create or replace synonym PO_HEADERS_ALL for PO.PO_HEADERS_ALL


By following the above steps, as you can see, APPS schema is able to access PO_HEADERS_ALL without the notation po.po_headers_all


In Oracle ERP, now we have 100s of schemas, example po, ar, ap, gl etc.

But the screens, reports, workflows etc in Oracle Applications connect to APPS schema only. Just like saying, ALL ROADS LEAD TO ROME. Here, all schema lead to APPS.

Hence if you have a pseudo report that joins ap_invoices_all table( in AP schema) to PO_HEADERS_ALL table( in AP schema), you will simply need to do the below once connected to APPS.

Selelct 'x’ from po_headers_all p, ap_invoices_all a where a.po_Id = p.po_Id

Note, prior to version 10.6(of Oracle ERP –not database version), one had to do
Selelct 'x’ from PO.po_headers_all p, AP.ap_invoices_all a where a.po_Id = p.po_Id

Note: To keep matters simple, I haven’t considered org_Id in this example.
Org_Id will be covered in one of the following chapters ( have a look at index).

Moral of the Story is:-
All the pl/sql packages will be created in APPS Schema
All the views will be created in APPS Schema
For each table in individual schema, there will exist one synonym in APPS schema
Tables are not created in APPS schema.
Every implementation has at least 1 custom schema, where custom tables are created.
For each custom table created by you, you will need to create a Synonym in APPS schema
As a developer, you will either connect to APPS Schema or to the custom schema where you will create new tables.

Some notes:-
Custom tables are generally required in Oracle ERP because:-
1. You wish to create a custom screens ( your own screen to capture some info) for a functionality that is not delivered by Oracle
2. Pre-Interface tables ( Interface will certainly be discussed in one of the latter chapters)
3. Temp processing
4. Staging of data for third party extract interfaces….and much more

Metalink Customer Knowledge Exchange

The Customer Knowledge Exchange provides a channel for customers to showcase their expertise and to share their knowledge about Oracle products. Your contributions to the Customer Knowledge Exchange are highly valued by customers, partners, and employees and are viewed by over 300,000+ total users worldwide.

Refer to Metalink Note 375443.1 for details about this program.

Currently there are 3 major forums:

Database / Server
EBS / Applications
Fusion

Do go through the papers submitted by customers to learn what they learnt. You could also submit your own paper which will be published as a Metalink Note, if it is accepted by Oracle.

Oracle Database 11g new features

I recently did a session on Oracle 11g database new features for my company. The material for the session was collected from Oracle white papers on 11g, blogsearch.google.com and 11g presentations of Rich Niemic, Penny Avril and Marc Townsend. Here's what I covered:

Glimpses of the new features
Rapid Application Testing
SQL Performance Analyzer
Online Application Upgrade
Online Patching of one-off patches
Faster Performance
Result Cache
Automatic Memory Management - MEMORY_TARGET
Invisible Index
The Virtual Column
Automatic SQL tuning
SQL Access Advisor
DDL_LOCK_TIMEOUT
Sequences in PL/SQL
Enhanced SQL - PIVOT keyword
Read Only tables
Automatic Diagnostic Repository
Fast Files
Total Recall
Data Compression
Multimedia & Spatial Enhancements
RMAN enhancements.

In future posts, I'll cover these topics in detail.

ORA-01722: invalid number when you SELECT on WF_ITEM_ATTRIBUTE_VALUES

SQL> select rownum from apps.WF_ITEM_ATTRIBUTE_VALUES
where item_type='HXCEMP' and item_key=120
SQL> /

ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11

ROWNUM
----------
12
13
14
15
16
17
18
19
20
21
22

ROWNUM
----------
23
24
25
26
27
28
29
30
31
32
33

ROWNUM
----------
34
35
36
37
38
39
40
41
42
43
44

ROWNUM
----------
45
ERROR:
ORA-01722: invalid number

SQL> select count(*) from wf_item_attribute_values;

COUNT(*)
----------
53014588

Metalink note 179260.1 says:

The column WF.ITEM_KEY can not be alphanumeric for upgraded order lines. It
stores the to_char(line_id) into it. ONT00065.sql should never for fail for
any records with ITEM_TYPE = 'OEOL'. Alphanumeric records will cause upgrade
errors.

However we dont have OEOL item type.

SQL> select name, number_value
2 from wf_item_attribute_values
3 where item_key=120;

NAME NUMBER_VALUE
------------------------------ ------------
ERROR_ACTIVITY_ID
ERROR_ACTIVITY_LABEL
ERROR_ASSIGNED_USER
ERROR_ITEM_KEY
ERROR_ITEM_TYPE
ERROR_MESSAGE
ERROR_MONITOR_URL
ERROR_NAME
ERROR_NOTIFICATION_ID
ERROR_PERSON_ID
ERROR_PERSON_USERNAME

NAME NUMBER_VALUE
------------------------------ ------------
ERROR_RESULT_CODE
ERROR_STACK
ERROR_TYPE
ERROR_USER_KEY
ERROR:
ORA-01722: invalid number

Still investigating this one.

11g release 1 transparent gateway available for download on edelivery.oracle.com

Oracle 11gR1 transparent gateway is available for download on edelivery.oracle.com. Alas the full DB is not available for download yet. I installed the transparent gateway in the hope that I'll get the binaries for creating a DB. However $ORACLE_HOME/rdbms/admin contains only these files:

-bash-3.00$ ls -ltr
total 2392
-rw-r--r-- 1 oracle oracle 853 Jan 12 2002 initjms.sql
-rw-r--r-- 1 oracle oracle 1226 Feb 16 2005 agtept.lst
-rw-r--r-- 1 oracle oracle 44280 Apr 26 23:14 shrept.lst
-rw-r--r-- 1 oracle oracle 2349003 Jun 5 10:28 recover.bsq
-rw-r--r-- 1 oracle oracle 64 Jun 5 12:35 libxdb.def
-rw-r--r-- 1 oracle oracle 708 Jun 5 12:42 libskgxp11.def
-rw-r--r-- 1 oracle oracle 244 Jun 5 12:42 libskgxn2.def

These are not sufficient to create a db.

insert_icm_record failed due to ORA-00001: unique constraint

Early morning today, I was called for investigating an issue in a Production instance which was not starting concurrent managers after a power outage. The SAN had gone down because of this power outage. The DB had come up without errors. Here's how the error looks:

APP-FND-01564: ORACLE error 1 in insert_icm_record

Cause: insert_icm_record failed due to ORA-00001: unique constraint (APPLSYS.FND_CONCURRENT_PROCESSES_U1) violated.

The SQL statement being executed at the time of the error was: INSERT INTO FND_CONCURRENT_PROCESSES (CONCURRENT_PROCESS_ID,
LAST_UPDATE_DATE, CREATION_DATE, CREATED_BY, LAST_UPDATED_BY, ORACLE_PROCESS_ID, QUEUE_APPLICATIO
N_ID, CONCURRENT_QUEUE_ID, OS_PROCESS_ID, SESSION_ID, PROCESS_START_DATE, PROCESS_STATUS_CODE, MANA
GER_TYPE, NODE_NAME, DB_NAME, DB_DOMAIN, SQLNET_STRING, DB_INSTANCE, Instance_Nu
mber) VALUES (:cpid, SYSDATE, SYSDATE, :mgrusrid, :mgrusrid, :opid,
:qappid, :qid, :ospid, :osid, SYSDATE, 'A', 0,
:node, :dbname, :dbdomain, :dbinst, (Select instance_name from v$instance), (Select instance_
number from v$instance)) and was executed from the file &ERRFILE.

List of errors encountered:
.............................................................................

_ 1 _
Routine AFPCAL received failure code while parsing or running your
concurrent program CPMGR

Since the error is due to unique index, I checked for the table_name and column_name of the index by firing this query:

1 select table_name,column_name from dba_ind_columns
2* where INDEX_NAME='FND_CONCURRENT_PROCESSES_U1'
SQL> /

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
FND_CONCURRENT_PROCESSES
CONCURRENT_PROCESS_ID

Next I looked for all the tables which had a column called CONCURRENT_PROCESS_ID:

SQL> select table_name from dba_tab_columns
2 where column_name='CONCURRENT_PROCESS_ID';

TABLE_NAME
------------------------------
FND_CONCURRENT_PROCESSES
FND_ENV_CONTEXT
CS_CONT_BILL_IFACE_ALL
CS_ORDERS_INTERFACE_ALL
CS_TERMINATION_INTERFACE_ALL
CS_TERMINATION_INTERFACE
CS_ORDERS_INTERFACE
CS_CONT_BILL_IFACE

Assuming that it is inserting a duplicate vale for concurrent_process_id, I checked for duplicates first by this query:

SQL> select concurrent_process_id,count(*)
2 from apps.fnd_concurrent_processes
3 group by concurrent_process_id
4 having count(*)>1;

no rows selected

So no existing duplicates. That means a new record which is being inserted is generating a duplicate concurrent_process_id.

SQL> select max(concurrent_process_id)
2 from apps.fnd_concurrent_processes
3 /

MAX(CONCURRENT_PROCESS_ID)
--------------------------
3483

Now we have to look for this value in all the tables which had this column:

SQL> select CONCURRENT_PROCESS_ID
2 from fnd_env_context
3 where CONCURRENT_PROCESS_ID=3483;
from fnd_env_context
*
ERROR at line 2:
ORA-01410: invalid ROWID

1 select concurrent_process_id
2 from apps.CS_CONT_BILL_IFACE_ALL
3* where concurrent_process_id=3483
SQL> /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_ORDERS_INTERFACE_ALL
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_TERMINATION_INTERFACE_ALL
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_TERMINATION_INTERFACE_ALL
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_TERMINATION_INTERFACE
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_ORDERS_INTERFACE
3 where concurrent_process_id=3483
4 /

no rows selected

SQL> select concurrent_process_id
2 from apps.CS_CONT_BILL_IFACE
3 where concurrent_process_id=3483
4 /

no rows selected

Something wrong with FND_CONTEXT_ENV. FND_ENV_CONTEXT table is populated with environmental data when a concurrent process starts.

I did 3 things:

1.
SQL>select CONCURRENT_PROCESS_ID from FND_ENV_CONTEXT;

CONCURRENT_PROCESS_ID
---------------------
3462
3462
3462
3462
3462
3462
3462
3462
3462
3462
3462
ERROR:
ORA-08103: object no longer exists

2.
SQL> create table apps.fnd_env_context_bak as select * from apps.fnd_env_context;
create table apps.fnd_env_context_bak as select * from apps.fnd_env_context
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcbgtcr_12], [1], [], [], [], [],
[], []

3. Bimal tried export and got this error:
. . exporting table FND_ENV_CONTEXT
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.

A lookup on ORA-00600 thru ORA-600 lookup tool on metalink note: 153788.1 reveals this:

ERROR:
ORA-600 [kcbgtcr_12] [a]

VERSIONS:
Version 9.0 to 10.01

DESCRIPTION:

ORA-600 [kcbgtcr_12] is raised when Oracle is trying to create a current
buffer after reading the block from disk and fails since there already is
a current buffer for this block in the cache.

ARGUMENTS:
Arg [a] The buffer class

FUNCTIONALITY:
Kernel Cache Buffer management Implementation

IMPACT:
NON CORRUPTIVE - No underlying data corruption.

SUGGESTIONS:

Known issues:
Bug# 5523799 + See Note 5523799.8
Various OERI (eg kcbgtcr_12) using ASSM managed segments
Fixed:

Bug# 4996133 See Note 4996133.8
OERI[kcbgtcr_5] updating an IOT in RAC environment
Fixed: 10.2.0.3, 11

Bug# 4464828 See Note 4464828.8
OERI[kcbgtcr_12] using ANALYZE on ASSM segment
Fixed: 9.2.0.8

Bug# 2928757 See Note 2928757.8
OERI[kcbgcur_6] / OERI:[kcbgtcr_12] can occur for files converted to locally managed
Fixed: 9.2.0.5, 10.1.0.2

Bug# 2619867 See Note 2619867.8
OERI:[KCBGTCR_12] / ORA-8103 / ORA-1410 SELECTing from bitmap managed segment
Fixed: 9.2.0.3, 10.1.0.2

Bug# 2122159 See Note 2122159.8
OERI:KCBGTCR_12 possible in RAC (eg during parallel load)
Fixed: 9.0.1.4, 9.2.0.1

Bug# 1935292 See Note 1935292.8
OERI:KCBGTCR_12 etc.. doing STAR QUERY with TEMP TABLE TRANSFORMATION in IPQ
Fixed: 9.0.1.2, 9.2.0.1

Bug# 1777175 See Note 1777175.8
OERI:kcbgcur_12 / OERI:kcbgcur_6 / OERI:kclchkinteg_1 accessing a table which is being DROPPED
Fixed: 9.0.1.2, 9.2.0.1

We can try to truncate the table or drop / recreate it. However this being an Oracle seeded table and this being a Production instance, it is best to log an SR and let Oracle guide us.

While Bimal was working with Oracle to fix the FND_ENV_CONTEXT table, Subu gave a workaround for the issue:

The concurrent_process_id in the table fnd_concurrent_processes is populated by the sequence FND_CONCURRENT_PROCESSES_S. The abonormal shutdown of the server during the power outage may have prevented the sequence from incrementing. If you manually do a nextval query on the sequence, it increments the value of the sequence:

SQL> select apps.FND_CONCURRENT_PROCESSES_S.currval
2 from dual;

CURRVAL
----------
3483


SQL> select apps.FND_CONCURRENT_PROCESSES_S.nextval
2 from dual;

NEXTVAL
----------
3484

SQL> select apps.FND_CONCURRENT_PROCESSES_S.currval
2 from dual;

CURRVAL
----------
3484

After doing this query, the concurrent managers log showed a different error for each manager:

Starting SYSADMIN Concurrent Manager : 21-JUL-2007 09:05:07

ORACLE error 8102 in insert_fcp

Cause: insert_fcp failed due to ORA-08102: index key not found, obj# 40781, dba 121643011 (2)
ORA-06512: at "APPS.FND_CP_FNDSM", line 121
ORA-06512: at line 1.

The SQL statement being executed at the time
Could not initialize the Service Manager FNDSM_TSGP1402_gpsgnep1. Verify that TSGP1402 has been registered for concurrent pro
cessing.
ORACLE error 8102 in cleanup_node

Cause: cleanup_node failed due to ORA-08102: index key not found, obj# 40781, dba 121643011 (2)
ORA-06512: at "APPS.FND_CP_FNDSM", line 29
ORA-06512: at line 1.

Looks like object# 40781 is corrupt too:

SQL> select object_name
2 from dba_objects
3 where object_id=40781;

FND_CONCURRENT_PROCESSES_N1

The index was dropped and recreated and the concurrent managers started up.

Meanwhile for the corrupt FND_ENV_CONTEXT, Oracle gave this solution:

1. Make sure that concurrent managers are not running and there are no FNDLIBR processes.

2. drop table FND_ENV_CONTEXT as APPLSYS user.

3. Open telnet window and source apps environment.

4. Execute the following:

adodfcmp userid=applsys/apps mode=tables odffile=/patch/115/odf/afcmgr.odf
touser=apps/ priv_schema=system/ logfile="adodfcmp.log"

Apply all E-biz CPU patches from scratch when you upgrade E-Biz

I attended Integrigy's CEO Stephen Kost's session on July 2007 CPU. One of the interesting things which Stephen explained was the CPU situation when you upgrade. Suppose you are current on your quarterly CPU and have applied July 2007 CPU. You are on 11.5.9 release and decide to upgrade to 11.5.10.2. Once you ugprade to 11.5.10.2, you'll have to apply all E-biz security patches starting from July 2005 - July 2007. 11.5.10.2 comes with April 2005 CPU pre-loaded.

This is because security patches are version dependant and if you had them on 11.5.9, it doesn't protect your 11.5.10.2 code.

Unable to clear JspWriter buffer, data already written to stream

While trying to login to Apps the following error is displayed:

Message: Unable to clear JspWriter buffer, data already written to stream.
Stack:
java.io.IOException: Unable to clear JspWriter buffer, data already written to stream.
at oracle.jsp.runtime.OracleJspWriter.clear(OracleJspWriter.java:474)
at _oa__html._z__gepp__voc._jspService(_z__gepp__voc.java:908)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:385)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:259)
at oracle.jsp.JspServlet.internalService(JspServlet.java:178)
at oracle.jsp.JspServlet.service(JspServlet.java:148)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)


On http://www.oreillynet.com/pub/a/oreilly/java/news/jsptips_1100.html tip 2 provides details of why this error occurs:

Dealing with Buffer Flushing Issues

An HTTP response message contains both headers and a body. The headers tell the browser things like what type of data the body contains (HTML text, an image), the size of the body, if the body can be cached, and so on. Headers are also used to set cookies and to tell the browser to automatically get another page (a redirect). All response headers must be sent to the browser before the body is sent.

To allow parts of the body to be produced (from static template text as well as content generated dynamically by JSP elements) before headers are set, the body is buffered. Instead of sending the response to the browser as soon as something is written to the response body, the JSP container writes all static markup code and all dynamic content generated by JSP elements to the buffer. At some point, such as when the buffer is full or the end of the page is reached, the container sends all headers that have been set followed by the buffered body content. In servlet speak, this is called committing the response. After the response has been committed, you can't set headers, such as for cookies or a redirection instruction. Another thing you can't do is forward the request to another page.

In most cases, this is not a problem. The default buffer size is 8KB, more than enough for a typical page, and you can increase it with the buffer attribute of the page directive. But if you use the include action in a page, you may be in for a surprise. Due to limitations in the way the servlet features used by are specified, the buffer is always flushed before the target page is invoked. This means that you can't set headers or use after a action.

An unfortunate side-effect of this automatic flushing is that runtime errors triggered by JSP elements after a action may not be reported correctly, since many JSP containers use the forward mechanism to display the error page. If you see an error message like "response already committed" in a page with elements, I suggest that you use the include directive instead (at least until you have isolated the problem).

Workaround: Bounce Apache. One hit in Metalink for this error is note 234431.1 which is in iStore and as expected is a bug (code error) for which a specific iStore patch is suggested.

If you get this issue too often, log an SR with Oracle to get a patch.

Sabrix doesn't generate invoices with tax

I was dragged into a Sabrix issue today for which the DBAs had already consulted me in the morning, without giving sufficient details about the issue. The DBAs said that Sabrix team was complaining about JVM errors. I told them that no Sabrix JVM exists in E-Business Suite. Sabrix JVM is present on the Sabrix Application Server which is installed in a different physical server. In the evening, I was again contacted and given this error:

Auto Invoice Request Id 467832
Batch Source: PROJECTS INVOICES

Transaction Flexfield: PROJECTS INVOICES: 102992.2.381591-C.7560 CONTROLS/NUMAC.Duncan, Michael George. 1.Line

Type Bill to Customer Name Item Description Currency Amount
-------------------- ---------------------------------------- ---------------------------------------- ----------- -----------------
Invoice EXAMPLE VENDOR Example items Procured USD 5000.00

Errors: 1) Sabrix Error: [Servlet];Sev[2];Msg: [Invalid file format: expected INVOICE start tag in XML.]
2) Unable to find a default tax code for this transaction.

They engaged Sergei Nikonorov of Sabrix Inc. After asking the application support team to fire various queries, he concluded that the issue was with temp tables in Sabrix schema. After truncating these temporary tables, the issue was solved. Sergei advised us to have these tables truncated every week.

truncate table sabrix_log;

truncate table TMP_SABRIX_REGISTRATION;

truncate table TMP_SABRIX_LINE;

truncate table TMP_SABRIX_INVOICE;

truncate table TMP_SABRIX_INVOICE_TAX;

truncate table TMP_SABRIX_LINE_TAX;

truncate table TMP_SABRIX_MESSAGE;

truncate table TMP_SABRIX_LINE_OUT;

truncate table TMP_SABRIX_INVOICE_OUT;

After doing the above, AutoInvoice ran successfully.

Complete: Migration of old blog posts from vikramdas.livejournal.com

Finally I have copy pasted everything I had posted on http://vikramdas.livejournal.com. All posts are here except the post related to Apps 11i to 12 upgrade and the July 2007 CPU announcement. I'll post them later with refinements.

For now, this is my new blog and I hope that it lives longer than I live.

Most missed rapidclone steps

During cloning, DBAs most often miss the finishing tasks as outlined in Metalink Note 230672.1 (Cloning Oracle Applications Release 11i with Rapid Clone). These are tasks which need to be done manually by a DBA as RapidClone doesn't take care of them :

Section 3: Finishing Tasks

This section lists tasks that may be necessary depending on your implementation and the intended use of the cloned system.

1. Update profile options
   Rapid Clone updates only site level profile options. If any other profile options are set to instance specific values, you must update them manually.

2. Update printer settings
   If the new cloned system needs to utilize different printers, update the target system with the new printer settings now.

3. Update workflow configuration settings
   Cloning an Oracle Applications instance will not update the host and instance specific information used by Oracle Workflow. Review the following tables and columns to verify there is no instance specific data in the Workflow configuration on the target system.

   Table Name	Column Name	Column Value Details
   WF_NOTIFICATION_ATTRIBUTES	TEXT_VALUE	Value starts with http:// : Update to new web host
   WF_ITEM_ATTRIBUTE_VALUES	TEXT_VALUE	Value starts with "http:// : Update to new web host
   WF_SYSTEMS	GUID	Create a new system defined as the new global database name using the Workflow Administrator Web Applications responsibility.
   WF_SYSTEMS	NAME	Value needs to be replaced with the database global name
   WF_AGENTS	ADDRESS	Update database link with the new database global name.
   FND_FORM_FUNCTIONS	WEB_HOST_NAME	Update with the new web host name
   FND_FORM_FUNCTIONS	WEB_AGENT_NAME	Update to point at the new PLSQL listener name
   FND_CONCURRENT_REQUESTS	LOGFILE_NAME	Update with the correct path to the logfile directory
   FND_CONCURRENT_REQUESTS	OUTFILE_NAME	Update with the new directory path on the target system

4. Verify the APPLCSF variable setting
   Source the APPS environment and review that the variable APPLCSF (identifying the top-level directory for concurrent manager log and output files) points to an acceptable directory. To modify it, change the value of s_applcsf in the contextfile and run AutoConfig.

5. Update the SESSION_COOKIE_DOMAIN value in ICX_PARAMETERS
   If the target system is in a different domain name than the source system and SESSION_COOKIE_DOMAIN was not null in the source system, update that value to reflect the new domain name.

Customize appsweb.cfg

If you keep any customization in appsweb.cfg, it is overwritten whenever autoconfig is run on the instance.
To ensure that the new appsweb.cfg created by adconfig, keeps your customizations, include your customizations in the myExtensions section of the appsweb.cfg template file present in $FND_TOP/admin/template/appsweb.cfg

Autoconfig uses the template $FND_TOP/admin/template/appsweb.cfg and creates these files:

$FND_TOP/resource/appsweb.cfg
$FND_TOP/resource/appsweb_CONTEXT_NAME.cfg

$OA_HTML/bin/resource/appsweb.cfg
$OA_HTML/bin/resource/appsweb_CONTEXT_NAME.cfg

How to find the CPU clock speed and frequency of a Solaris server

Use this command:

/usr/platform/`uname -i`/sbin/prtdiag

In our Solaris E2900 Server following is the excerpt from the output which shows CPU frequency as 1350 MHz and clock frequency as 150 MHz.

System Configuration: Sun Microsystems sun4u Sun Fire E2900
System clock frequency: 150 MHZ
Memory size: 96GB

======================================= CPUs =======================================
E$ CPU CPU Temperature Fan
CPU Freq Size Impl. Mask Die Ambient Speed Unit
------ -------- ---------- ------ ---- -------- -------- ----- ----
SB0/P0 1350 MHz 16MB US-IV 3.1 82 C 35 C
SB0/P1 1350 MHz 16MB US-IV 3.1 81 C 35 C
SB0/P2 1350 MHz 16MB US-IV 3.1 78 C 34 C
SB0/P3 1350 MHz 16MB US-IV 3.1 80 C 34 C
SB2/P0 1350 MHz 16MB US-IV 3.1 77 C 35 C
SB2/P1 1350 MHz 16MB US-IV 3.1 78 C 36 C
SB2/P2 1350 MHz 16MB US-IV 3.1 83 C 37 C
SB2/P3 1350 MHz 16MB US-IV 3.1 82 C 37 C
SB4/P0 1350 MHz 16MB US-IV 3.1 66 C 32 C
SB4/P1 1350 MHz 16MB US-IV 3.1 73 C 34 C
SB4/P2 1350 MHz 16MB US-IV 3.1 84 C 38 C
SB4/P3 1350 MHz 16MB US-IV 3.1 74 C 33 C

prtdiag can be executed by any user and doesn't require root privilege.

Testgroup is secured and requires execution privileges

Oracle Diagnostics 2.5 has introduced a new security model. Because of this you can not execute all tests now. Details are given in Metalink note 409141.1. Here's a cut paste of the relevant detais from the note:

I can’t execute tests; Tests have lock icon and when selected gives 'Testgroup is secured and requires execution privileges'

Ask System Administrator to grant required roles to the responsibilities of the user.

How to provide user access to a test?

To be able to access a test, the user’s responsibility should have proper role granted depending on the sensitivity of the test.
Roles are granted to users through responsibilities:
Responsibilities are assigned to users
Roles are granted to responsibilities
Navigate to
Configuration > Security > Select the appropriate diagnostics role
This will take you to the ‘Role Responsibility Assignment’ page through which you can grant the selected role to various responsibilities.

How to decide what role should be granted to which responsibilities?

Super User Role:
Has unrestricted privileges to execute, configure, view reports and setup security for all groups and all applications.
Grant this role to the most privileged responsibilities (like System Administration) across all applications. This role should NOT be commonly granted.
By default, this role has been granted to ‘System Administrator’ and ‘CRM HTML Administration’ responsibilities.
Application Super User:
Has unrestricted privileges to execute, configure, view reports for the application associated with his responsibility. Grant this role to the most privileged responsibilities (like super user) for an application.
End User:
Has restricted privileges (execute, view reports). Grant this role to regular application responsibilities that need to access diagnostics for running tests. By default this role is granted to ‘Oracle Diagnostics Tool’ responsibility.

Unix level test to see if smtp is working from command line

Start a shell, then:

$ telnet justanexample.com 25
Trying 192.168.100.187...
Connected to justanexample.com.
Escape character is '^]'.
220 justanexample.com ESMTP Sendmail 8.11.7p2+Sun/8.11.7; Thu, 12 Jul 2007 18:29:38 -0400 (EDT)
HELO justanexample.com
250 justanexample.com Hello justanexample.com [your IP address], pleased to meet you
MAIL FROM:user1@justanexample.com
250 2.1.0 user1@justanexample.com... Sender ok
RCPT TO:appsdba@yahoo.com
250 2.1.5 appsdba@yahoo.com... Recipient ok

If relaying is denied, then it will give message

550 5.7.1 appsdba@yahoo.com... Relaying denied

Unexpected failure. Password file/table unchanged

While changing password in Solaris 8 by giving passwd command you may get this error:

Unexpected failure. Password file/table unchanged

Solution is given in Sun Alert 57592: http://sunsolve.sun.com/search/document.do?assetkey=1-26-57592-1

The described issue only occurs if all of the following are true:

* One of the patches listed above is installed on the system
* /etc/nsswitch.conf(4) contains the entry "passwd: compat"
* user accounts have passwd(4) entries that begin with a "+" (plus sign) or a "-" (minus sign)

Note: Password entries with a "+" or "-" selectively incorporate entries from a network repository (NIS, NIS+, or LDAP) for the password data. To view the "passwd" entries in the nsswitch.conf(4) file, execute the following command:

$ egrep "^passwd" /etc/nsswitch.conf
passwd: compat

3. Symptoms If the described issue occurs, users may see the following error message when attempting to change their password:

$ passwd
passwd: Changing password for username
passwd: Unsupported nsswitch entry for "passwd:". Use "-r repository ".
Unexpected failure. Password file/table unchanged

Solution Summary Top
4. Relief/Workaround Users whose accounts are specified as +/- in the passwd(4) file can workaround this issue by specifying the repository to update with the "-r" option, such as:

$ passwd -r nis
OR
$ passwd -r nisplus
OR
$ passwd -r ldap

5. Resolution This issue is addressed in the following releases: SPARC Platform

* Solaris 8 with patch 108993-35 or later
* Solaris 9 with patch 112960-14 or later

x86 Platform

* Solaris 8 with patch 108994-35 or later
* Solaris 9 with patch 114242-09 or later

UNEXPECTED:[fnd.common.logging.FileHandler.publish]

Today, the extranet tier did not show the AppsLocallogin.jsp page and instead gave this error:

Request URI:/OA_HTML/AppsLocalLogin.jsp

Exception:
java.lang.NoClassDefFoundError

No errors in Apache logs. No errors in mod_jserv.log


jvm logs showed this error:

[Jul 2, 2007 6:39:26 PM EDT]:1183415966826:Thread[Thread-10883,10,main]:-1:-1:::29004:29015:UNEXPECTE
D:[fnd.common.logging.FileHandler.publish]:Please check File Permission/Disk Space for: aferror.log, defaulting Logging to ST
DERR

java.lang.NoClassDefFoundError
at oracle.apps.fnd.common.WebRequestUtil.validateContext(WebRequestUtil.java:391)
at oracle.apps.fnd.framework.webui.OAHttpSessionCookieImpl.validateIcxCookie(OAHttpSessionCookieImpl.java:171)
at oracle.apps.fnd.framework.OASessionCookieHelper.validateIcxSession(OASessionCookieHelper.java:467)
at oracle.apps.fnd.framework.OASessionCookieHelper.initializeApplicationModule(OASessionCookieHelper.java:125)
at oracle.apps.fnd.framework.webui.OAHttpSessionCookieImpl.initializeApplicationModule(OAHttpSessionCookieImpl.java:9
01)
at oracle.apps.fnd.framework.webui.OAHttpSessionCookieImpl.useApplicationModule(OAHttpSessionCookieImpl.java:481)
at oracle.jbo.common.ampool.SessionCookieImpl.useApplicationModule(SessionCookieImpl.java:398)
at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:2
08)
at oracle.apps.fnd.framework.webui.OAJSPApplicationRegistry.registerApplicationModule(OAJSPApplicationRegistry.java:7
8)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1177)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:502)
at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:423)
at _oa__html._OA._jspService(_OA.java:88)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:98)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at _oa__html._OA._jspService(_OA.java:98)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at oracle.jsp.provider.Jsp20RequestDispatcher.forward(Jsp20RequestDispatcher.java:162)
at oracle.jsp.runtime.OraclePageContext.forward(OraclePageContext.java:187)
at _oa__html._OA._jspService(_OA.java:98)
at oracle.jsp.runtime.HttpJsp.service(HttpJsp.java:119)
at oracle.jsp.app.JspApplication.dispatchRequest(JspApplication.java:417)
at oracle.jsp.JspServlet.doDispatch(JspServlet.java:267)
at oracle.jsp.JspServlet.internalService(JspServlet.java:186)
at oracle.jsp.JspServlet.service(JspServlet.java:156)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:588)
at org.apache.jserv.JServConnection.processRequest(JServConnection.java:456)
at org.apache.jserv.JServConnection.run(JServConnection.java:294)
at java.lang.Thread.run(Thread.java:534)

Investigating it now.

E-biz instance slow

Sometime back, Jimmy reported that the E-biz instance which had gone live today was seeming slow. These were the symptoms:

1. If you launched a forms session from self service it would not launch and display this on status bar:

waiting for http://justanexample.com/OA_HTML/....

2. We had blocking locks where the blocking program was FNDSCAUS and waiting program were self service OA Framework pages

3. Alert log stopped showing new entries after a new tablespace was added for installation of STATSPACK:

Tue Jul 10 19:04:53 2007
create tablespace PERFSTAT_TBLSPC
datafile '$ORACLE_HOME/dbdata/perfstat01.dbf' size 500M
Tue Jul 10 19:05:15 2007
Completed: create tablespace PERFSTAT_TBLSPC
datafile '/

4. SQL> select object_name from dba_objects
2 where object_id in (select object_id from v$locked_object);

OBJECT_NAME
--------------------------------------------------------------------------------
AQ$_WF_CONTROL_H
AQ$_WF_CONTROL_I
AQ$_WF_CONTROL_T
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_QUEUES
FND_SVC_COMPONENTS
PA_COMMITMENT_TXNS_TMP
WF_JAVA_DEFERRED
AQ$_WF_JAVA_DEFERRED_T
AQ$_WF_JAVA_DEFERRED_H
AQ$_WF_JAVA_DEFERRED_I

OBJECT_NAME
--------------------------------------------------------------------------------
AQ$_FND_CP_GSM_OPP_AQTBL_T
AQ$_FND_CP_GSM_OPP_AQTBL_H
AQ$_FND_CP_GSM_OPP_AQTBL_I
AP_WEB_PROXY_ASSIGNMENTS
GEPS_TCARD_EXT_TEMP

16 rows selected.

5. We had waits on these events:
90 library cache lock
10 library cache pin

6. adapcctl.sh stop command went into hang state.

After about 2 hours, everything seemed to come back to life suddenly. The hanging adapcctl.sh stop command succeeded, The waiting forms session launched itself, blocking locks disappeared and after a 2 hour gap, entries started appearing in the alert log:

Tue Jul 10 19:05:15 2007
Completed: create tablespace PERFSTAT_TBLSPC
datafile '/
Tue Jul 10 20:58:08 2007
Beginning log switch checkpoint up to RBA [0x2f8.2.10], SCN: 0x08f3.2555e155
Thread 1 advanced to log sequence 760

Research on metalink revealed note 287059.1, which describes an instance hang situation when there is a library cache pin/lock pile, which is similar to our situation.


Subject: Library Cache Pin/Lock Pile Up hangs the application

Note:287059.1 Type: PROBLEM
Last Revision Date: 09-MAY-2007 Status: PUBLISHED

The information in this document applies to:
Oracle Server - Enterprise Edition - Version: <8.1.7.4 to 10.2.0.0
This problem can occur on any platform.
Symptoms
SystemState Dump shows 2 session on a library cache pin deadlock or a single session on a "library cache pin" self deadlock.
The Call stack Trace of one of the session shows the following pattern :

kqdgtc -> ... -> kkscls -> kxstcls -> ... -> kglpin

The sessions are doing a recursive operation.

Here is an example of the self deadlock.
It is requesting a Pin in S mode on handle 5eb723ec and at the same time it has the already acquired the pin in X mode.

----------------------------------------
SO: 0x6614b2ec, type: 52, owner: 0x54fc1324, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=6614b2ec handle=5eb723ec request=S lock=8c04b694
user=54fc1324 session=54fc1324 count=0 mask=0000 savepoint=558512 flags=[00]
----------------------------------------
SO: 0x78093280, type: 52, owner: 0x54fc1324, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=78093280 handle=5eb723ec mode=X lock=804ad2e4
user=54fc1324 session=54fc1324 count=0 mask=0041 savepoint=558293 flags=[00]
----------------------------------------
Changes
TIMED_STATISTICS=TRUE
Cause
Bug 3661076 INSTANCE HANG DUE TO DEADLOCK WAITING ON LIBRARY CACHE PIN

A session is in need of a row cache cursor from the LRU and it could pick any dictionary cursor to map which it has previously used.
Now consider a second process (or session) concurrently doing some different operation parsing a different row cache cursor but needing to close one from its LRU could end up deadlocked with a similar stack segment.

The X mode when closing the cursor is needed when updating the statistics of the cursor generated while having some tracing enabled like (but not limited to) TIMED_STATISTICS=TRUE, or SQL_TRACE.
The deadlock and the pile up will not happen until there is a request in X mode.

Some of the reasons to close a row cache cursor are

- Lots of Recursive operations
- Shared Pool too small
- runs out of cache

There is currently a limitation to detect deadlock at the row cache level.
Fix
Identify the blocking session and get 3 errorstack dumps to confirm.

SELECT s.sid, kglpnmod "Mode", kglpnreq "Req", SPID "OS Process"
FROM v$session_wait w, x$kglpn p, v$session s ,v$process o
WHERE p.kglpnuse=s.saddr
AND kglpnhdl=w.p1raw
and w.event like '%library cache pin%'
and s.paddr=o.addr
/


To free up the instance kill the blocking session at the OS level.

To prevent it from happening do any, some or all of the following :

- set TIMED_STATISTICS=FALSE
- set _row_cache_cursors=20 or more (10 default)
- don't do any kind of tracing
References
Note 34579.1 - WAITEVENT: "library cache pin" Reference Note
Bug 3661076 - Instance Hang Due To Deadlock Waiting On Library Cache Pin
Bug 3679080 - Deadlock On Library Cache Pin
Note 122793.1 - HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK


Later on we were informed by Production Support team that this is a known issue with STATSPACK installation, and it should be done only during downtime and not when all apps services are up. Difficult to swallow this, but will go deeper in this one, another day.

E-biz instance hung, low values of sga_max_size, ORA-600 in alert log

During 1 o' clock today, I got a call. The instance which had gone live had come to a standstill. I had already received some ORA-4031 (Refer metalink note 396940.1 for details of this error) while applying a diagnostic patch and had managed to complete it by doing "alter system flush shared_pool" multiple times.

The situation was so bad that E-business suite was not allowing any new logins. While connected to db if you gave the command show parameter shared_pool it would hang. Finally it was not allowing to do sqlplus /nolog connect / as sysdba also. We stopped all the application services and killed all the LOCAL=NO DB processes, but were unable to connect / as sysdba. The alert log showed these:

Tue Jul 10 13:36:28 2007
Waited too long for library cache load lock. More info in file $ORACLE_HOME/admin/$CONTEXT_NAME/udump/$ORACLE_SID_ora_6094.trc.
Tue Jul 10 13:39:06 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/udump/$ORACLE_SID_ora_926.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:06 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_cjq0_29067.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:06 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_smon_29063.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:07 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_cjq0_29067.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:08 2007
Non-fatal internal error happenned while SMON was doing work on sort segment.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Tue Jul 10 13:39:50 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/udump/$ORACLE_SID_ora_1544.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:50 2007
Errors in file $ORACLE_HOME/admin/$CONTEXT_NAME/bdump/$ORACLE_SID_cjq0_29067.trc:
ORA-00600: internal error code, arguments: [kksscl-inf-inl-loop], [2500], [1], [362], [725], [725], [], []
Tue Jul 10 13:39:50 2007

Since we could not login with sqlplus, we had to kill all oracle processes on OS level and started the db.

The parameters which had low values were:

sga_max_size = 600 M
shared_pool_size=300M
processes=2654
sessions=800

We increased these to:

sga_max_size=4G
shared_pool_size=1G
processes=3000
sessions=6000

Restarted the DB. Warning about oradism appeared in the alert log (described in a previous post). Shutdown the DB and fixed the permissions of oradism binary. Restarted.

No issues after this.

WARNING: oradism not setup correctly in alert log

When we increased the sga_max_size to 4096G in our instance, which came to a standstill because the value was 600M only, the following warning appeared in the database alert log file:

WARNING: -------------------------------
WARNING: oradism not set up correctly.
Dynamic ISM can not be locked. Please
setup oradism, or unset sga_max_size.
[diagnostic 0, 16, 64882]
----------------------------------------

Metalink note 374367.1 describes the issue and provides the solution:

The real problem was with the oradism executable itself.

The oradism executable cannot have 755 permissions, and owned by root or oracle
(rwx-r-x-r-x)like the other executables have in this directory


The permissions for oradism must be 4550 with the setid for the group.
(-r-sr-s--- 1 root dba 12912 Feb 16 2005 oradism)

(Solaris8 64-bit / oracle v9.2.0.4)
Solution

1- cd $ORACLE_HOME/bin

2- chmod 4550 oradism
3- chmod g+s oradism
4- chown root:dba oradism
5- Bounce the database

The oradism warning does not appear in the alert log after the permissions were changed.

Good way to find out all timeout varibles set by autoconfig

Here's a good way to find out all the timeout variables present in your context file which are configured by autoconfig:

$ grep timeout $CONTEXT_FILE
15
3600000
90
300
10000
100
100
100
100
1000
100
100
100
100
100
100
100
300

s_keepalive_timeout: Number of seconds OHS will wait for a subsequent connection

s_sesstimeout : Jserv Session Timeout in milliseconds to wait before an unused
session is invalidated

s_apjserv_vmtimeout: Amount of time given to a JVM to startup and wait to ping the JVM to see if it is alive

s_ohstimeout: This parameter stands for the amount of time (in seconds) the Oracle HTTP Server will wait for certain events(GET,POST,PUT etc) before failing a request

s_servlet_init_timeout: Number of milliseconds to wait before aborting initialization of a servlet

s_apctimeout: Apache Process Timeout in seconds

s_apcplstimeout: Apache Process Timeout for PL/SQL in seconds

s_tnstimeout: TNS Process Timeout in seconds

s_tcftimeout: TCF Process Timeout in seconds

s_conctimeout: Concurrent Process Timeout in seconds

s_formstimeout: Forms Process Timeout in seconds

s_reptstimeout: Forms Process Timeout in seconds

s_metcltimeout: Metrics Client Process Timeout in seconds

s_metcsrvtimeout: Metrics Server Process Timeout in seconds

s_icsmtimeout: ORACLE ICSM Process Timeout in seconds

s_jtffcsrvtimeou: Oracle Fulfillment Server Timeout in seconds

s_icxblksrvtimeout: Procurement Bulk Loader Timeout in seconds

s_discotimeout: Discoverer Process Timeout in seconds

What is the version of raaips.lpc

I got a query from Bimal: How to find out the version of raaips.lpc. A search on metalink revealed that this is a file related to Receivables along with this info:

Note: At the time of this writing the latest version of raaips.lpc was 115.11 in Patch 2892559.

I checked for patch 2892559 and saw that it was only 75 KB in size. Promptly I downloaded it in the hope of finding the path of the raapis.lpc file. However the file raapis.lpc was not present in the patch. I searched for raapis* and found this:

D:\download\p2892559_11i_LINUX>dir raaips*/s
Volume in drive D is Data
Volume Serial Number is AC3C-A42C

Directory of D:\download\2892559\ar\lib

01/03/2002 03:03 AM 25,396 raaips.o
1 File(s) 25,396 bytes

That means the file would be in $AR_TOP/lib

$ cd $AR_TOP/lib
$ adident Header raaips.o
raaips.o:
$Header raaips.lpc 115.14 2004/09/21 15:45:22 mraymond ship $

I gave the information to Bimal.

adpatch options=nocompilejsp

When you use adpatch options=nocompilejsp, the lengthy time spent in compiling out of date jsps is saved. The command to compile jsps outside of adpatch is:

perl -x $JTF_TOP/admin/scripts/ojspCompile.pl --compile

Here's how the output of the above command looks like:

starting...(compiling delta)
using 8i internal ojsp ver: 1.1.3.5.2
including compatibility flag -whiteSpaceBetweenScriptlet
synchronizing dependency file:
loading deplist...15409
enumerating jsps...15409
updating dependency...0
initializing compilation:
eliminating children...12318 (-3091)
searching uncompiled...8677
translating and compiling:
searching untranslated...0
compiling jsps... 12% complete: 1100/8677 ETA: 14m7s


Do not use --quite option, as you would not be able to know the progress.

Complete syntax is:

syntax: $JTF_TOP/admin/scripts/ojspCompile.pl COMMAND {ARGS}
COMMAND --compile update dependency, compile delta
--create rebuild entire dependency file
-delta.out update dependency, list delta to file
-dep.out update dependency, output heirarchy to file

ARGS -s matching condition for JSPs filenames
-p number of parallel compilations
-log to override logfile from ojspCompile.conf
You are
recommended to set the log file location
outside of any network file system shared (NFS) area/drive.
-conf to override ojspCompile.conf
--retry retry previously failed compilation attempts
--flush forces recompilation of all parent JSPs
--quiet do not provide an actively running progress meter
--fast instantly fail jsps that are *possibly* invalid

example1: ojspCompile.pl --compile -s 'jtf%' -p 20 --retry
example2: ojspCompile.pl --compile -s 'jtflogin.jsp,jtfavald.jsp' --flush
example3: ojspCompile.pl --compile --fast --quiet


jsps do compile on the fly, but that affects the application performance. So it is a good idea to compile them in advance, if you have not done it during adpatch itself.

In which directory does FND_FILE write ?

Just now I was asked this question. FND_FILE writes to which directory ? Oracle documentation says that FND_FILE writes to $APPLPTMP. This happens only when the $APPLPTMP directory is the first directory in the db init parameter utl_file_dir. If it so happens that /usr/tmp is the first directory in your utl_file_dir, FND_FILE is going to write to /usr/tmp and not to $APPLPTMP. This can be tested:

If you go to sqlplus and exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');
A new file with a name like lnnnnnnnn.tmp is created in /usr/tmp containing the text: 'THIS IS A TEST'. /usr/tmp is the first directory in utl_file_dir.

Metalink Note 261693.1 has information on troubleshooting ORA-20100 on Concurrent Processing, which is where FND_FILE is involved.

Segmentation fault and coredump on Tru64 Unix

I was contacted for investigating segmentation fault and coredump on Tru64 Unix which was being caused by a Pro*C program. Since I did not have direct access to the instance, the user who had the access did a screen share. I asked him to do a truss on the process id of the Pro*C program.

Truss is available in tru64 if you have the extended system V software install that you get from your associated products disk. When loaded it is
in /usr/opt/svr4/usr/bin.

But /usr/opt/svr4/usr/bin/truss gave command not found error.

At this time, the user told me that the program is doing a segmentation and coredump when certain type of data is keyed in and they have zeroed in on it. I suggested that they build validation in their code to disallow entry of such data.

PARGDR PRC: Generate Draft Revenue for a Range of Projects Errors With Ora-1555

We faced this error during go-live. The concurrent request was erroring out with snapshot too old error after running for 1 hour and 30 minutes. We checked the undo_retention parameter and found that it was set to 1 hour (undo_retention=3600). We increased this to 10 hours (undo_retention=36000). Metalink Note 309690.1 describes this problem and offers this solution:

Solution

Increase the undo_retention parameter. The proper setting for this parameter can be determined by querying V$UNDOSTAT view once the database has been running for a while. Change the
UNDO_RETENTION parameter to at least equal the following query:

SQL> select max(maxquerylen) from v$undostat;

In our instance the value returned by above query was 14854.

undo_retention was correctly set as 36000 which is much above the 14854 requirement. The request completed successfully in 4 hours 15 minutes.

During an R12 Multi-node Install Why Do All Middle Tiers Run All Services?

Metalink note 415385.1 explains the reason for something queer which I observed during an R12 multi node install. All middle tiers run all services irrespective of what you have defined for them. Here's a copy paste from the relevant section in the note, which explains why this is happening:

In R12, the concept for Applications Nodes has changed. When installing R12 with multiple nodes. all the nodes are now set as 'Y' in FND_NODES.

This occurs because in R12, concept of unified APPL_TOP is introduced which means everything is laid down on all servers.

From the APPL_TOP perspective, all the servers on a Multi-node environment will have the same files and can now potentially start any service if needed. In some cases, additional configuration will be required before this can be done since there can be profiles, etc associated with each server.

For R12, the only difference between the servers, are the Services that have been activated on each node.

The services are identified by the variables on the /service_group/ section in the Apps Context File:

Root Service Group : s_root_status
Web Entry Point Services : s_web_entry_status
Web Application Services : s_web_applications_status
Batch Processing Services : s_batch_status
Other Service Group : s_other_service_group_status
Depending on the value of these variables (enabled or disabled), adstrtal.sh / adstpall.sh will only start/stop the services associated with them, ignoring the rest.

For example, if a node has only /s_batch_status/ "enabled" and the rest of the services are disabled, when you run adstrtall.sh on that server it will only start the Concurrent Managers and the TNS Listener for Apps.

NOTE: If you experience the problem where all of the service_group variables are enabled on all application tiers make sure you are using the latest version of the Rapid Install StartCD (12.0.0.21) shipped with Patch 5766801.

ORA-07445: exception encountered: core dump [drurnew()+184] on 10.2.0.3 on E-biz

After the upgrade of database from 9.2.0.8 to 10.2.0.3, the DBA team was applying pre-requisite patches of ATG_PF.H RUP5, One of these patches is 5395066 (DOCUMENTATION FOR ORACLE REPORT MANAGER FRM.H ). While applying this patch, all workers failed on process FNDGFU. The worker log showed "afpexcp failed due to ORA-03114: not connected to ORACLE". The database alert log showed:

ORA-07445: exception encountered: core dump [drurnew()+184] [SIGSEGV] [Address not mapped to object] [0x000000040]

On investigation, we found that it was a known Oracle bug # 5587976. The solution is to apply database patch 5587976. Once you apply this patch on your database through opatch and resume the failed patch, no errors are reported.

Errors during txkprepatch.pl for ATG_PF.H RUP5 on concurrent tier

One of the pre steps for running ATG_PF.H RUP5 is to run the techstack validation perl script. We got these errors while doing so:

$ perl $PATCH_TOP/5473858/fnd/patch/115/bin/txk

prepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps -reporttype=text

Errors encountered running $PATCH_TOP/5473858/fnd/patch/115/bin/txkValidateRollup.pl

*******FATAL ERROR*******
PROGRAM : ($PATCH_TOP/5473858/fnd/patch/115/bin/txkprepatchcheck.pl)
TIME : Tue Jul 3 11:52:34 2007
FUNCTION: TXK::IO::open [ Level 3 ]
MESSAGES:
error = Unable to open file by ref
errorno = Illegal seek

STACK TRACE
TXK::Error::abort('TXK::Error', 'HASH(0x1c788c)') called at $PATCH_TOP/5473858/fnd/perl/TXK/Common.pm line 299
TXK::Common::doError('TXK::IO=HASH(0x9e71b8)', 'HASH(0xe62690)', undef) called at $PATCH_TOP/5473858/fnd/perl/TXK/Common.pm line 314
TXK::Common::setError('TXK::IO=HASH(0x9e71b8)', 'HASH(0xe62690)') called at $PATCH_TOP/5473858/fnd/perl/TXK/IO.pm line 302
TXK::IO::open('TXK::IO=HASH(0x9e71b8)', 'HASH(0xce27fc)') called at $PATCH_TOP/5473858/fnd/perl/TXK/SQLPLUS.pm line 601
TXK::SQLPLUS::_doExecute('TXK::SQLPLUS=HASH(0xd099b4)', 'true') called at $PATCH_TOP/5473858/fnd/perl/TXK/SQLPLUS.pm line 376
TXK::SQLPLUS::validateConnectInfo('TXK::SQLPLUS=HASH(0xd099b4)') called at $PATCH_TOP/5473858/fnd/perl/TXK/SQLPLUS.pm line 267
TXK::SQLPLUS::setConnectInfo('TXK::SQLPLUS=HASH(0xd099b4)', 'HASH(0xe61554)') called at $PATCH_TOP/5473858/fnd/perl/TXK/TechstackDB.pm line 383
TXK::TechstackDB::validateDBPassword('TXK::TechstackDB', 'HASH(0x538d68)') called at $PATCH_TOP/5473858/fnd/patch/115/bin/txkValidateRollup.pl line 327
TXK::RunScript::validateInputParameters('TXK::ARGS=HASH(0x7cb004)') called at $PATCH_TOP/5473858/fnd/patch/115/bin/txkValidateRollup.pl line 135
require $PATCH_TOP/5473858/fnd/patch/115/bin/txkValidateRollup.pl called at $PATCH_TOP/5473858/fnd/perl/TXK/RunScript.pm line 105
TXK::RunScript::require('TXK::RunScript', '$PATCH_TOP/5473858/fnd/patch/115/bin/txkValidateRollup...') called at $PATCH_TOP/5473858/fnd/perl/TXK/Script.pm line 177
eval {...} called at $PATCH_TOP/5473858/fnd/perl/TXK/Script.pm line 177
TXK::Script::run('TXK::Script=HASH(0x4ea284)', '$COMMON_TOP/rgf/gpslmpd1_tsgsd1005/TXK', '$PATCH_TOP/5473858/fnd/patch/115/bin/txkValidateRollup...') called at $PATCH_TOP/5473858/fnd/patch/115/bin/txkprepatchcheck.pl line 239

The perl environment is set correctly and there are no periods in the directory names from where the perl file is being called.

$ env |grep PERL
ADPERLPRG=$IAS_ORACLE_HOME/Apache/perl/bin/perl
PERL5LIB=$IAS_ORACLE_HOME/Apache/perl/lib/5.00503:$IAS_ORACLE_HOME/Apache/perl/lib/site_perl/5.005:$AU_TOP/perl

Simple solution to the problem:

Check your current shell by doing echo $SHELL. If it returns "sh", then it is a shell issue. Instead of using sh, use ksh. That means, before launching the txkprepatch.pl command, on unix prompt type:

$ ksh
Then issue the txkprepatch.pl command:
$ perl $PATCH_TOP/5473858/fnd/patch/115/bin/txk

prepatchcheck.pl -script=ValidateRollup -outfile=$APPLTMP/txkValidateRollup.html -appspass=apps -reporttype=text

You will not get any errors this time.

Complex solution

Metalink Note 335822.1 does describe a similar problem in AIX, but we are on Solaris. Here's a copy paste from that note:
=====================================

Cause

This issue is happening on tiers that are not webNodes.
The reason is that the Perl executable in iAS 1.0 on these
tiers (o/s HP or AIX) is not compatible to run this script.

Solution

To implement the solution, please execute the following steps:

Solutions:

1. Upgrade the iAS on this tier
Probably not desirable since this is not a primary Web Tier
but a valid solution.

2. Use the o/s version of Perl if it is higher than the iAS
If upgrading the perl version see AutoConfig FAQ Note 218089.1
for information on how to propogate this change in apps.

Either of the above will resolve this issue.
=========================================

I downloaded perl 5.8.8 and installed it on the concurrent tier.

Changed these environment variables:

ADPERLPRG=/usr/local/bin/perl
PERL5LIB=/usr/local/lib/perl5/5.8.8: /usr/local/lib/perl5/site_perl/5.8.8: $AU_TOP/perl

$ /usr/local/bin/perl txkprepatchcheck.pl -script=ValidateRollup -outfile=txkValidateRollup.html \
> -appspass=apps
*** ALL THE FOLLOWING FILES ARE REQUIRED FOR RESOLVING RUNTIME ERRORS
*** STDOUT = $APPLRGF/TXK/txkValidateRollup_Tue_Jul_3_17_17_08_2007_stdout.log
Reportfile txkValidateRollup.html generated successfully.

How to find the wordsize (32-bit or 64-bit) of Oracle Database

If you have access to an Oracle database which is installed on a 64-bit OS, how can you identify whether Oracle is 32 bit or 64 bit ?

Gary Robinson's oracleadvice.com has very good pointers on how to determine the wordsize on http://www.oracleadvice.com/Tips/32or64bit.htm. ; I have tweaked the query given on oracleadvice.com like this:

select (case length(address)
when 16 then '64-bit Oracle'
when 8 then '32-bit Oracle'
when 32 then '128-bit Oracle'
end) Wordsize
from v$sql
where rownum <2
/

WORDSIZE
--------------
64-bit Oracle

Activate extended SQL trace for a given session

I was consulted for issue with a session which appeared not to be doing anything. I advised them to enable extended SQL trace since they had already found the session id. Here are the steps for activating extended SQL trace as given in the book, Optimizing Oracle Performance by Cary Milsap:

There are several ways to activate extended SQL tracing for a given session. Two such ways are shown in Example 6-5 and Example 6-6. Oracle encourages you to use the DBMS_SUPPORT package instead of DBMS_SYSTEM if you have a choice (Oracle MetaLink note 62294.1). However, Oracle does not ship dbmssupp.sql and prvtsupp.plb with some software distributions. If you cannot find DBMS_SUPPORT on your system, don't despair. My colleagues and I have used DBMS_SYSTEM.SET_EV in hundreds of performance improvement projects without negative incident. Friends in Oracle Support have informed me that the DBMS_SUPPORT.START_TRACE_IN_SESSION procedure is implemented as a call to SET_EV anyway.

The safety of using START_TRACE_IN_SESSION is that you're not susceptible to typographical errors in specifying event 10046. Accidentally typing the wrong event number could obviously lead to catastrophe.

Example 6-5. Activating extended SQL trace at level 8 with START_TRACE_IN_SESSION for a session identified by :sid and :serial
sys.dbms_support.start_trace_in_session(:sid, :serial,waits=>true, binds=>false)

/* code to be traced executes during this time window */
sys.dbms_support.stop_trace_in_session(:sid, :serial)

Do not use DBMS_SYSTEM.START_SQL_TRACE_IN_SESSION to activate extended SQL trace, because this procedure can activate SQL tracing only at level 1. You cannot activate extended SQL tracing with START_SQL_TRACE_IN_SESSION.

Example 6-6. Activating extended SQL trace at level 8 with SET_EV for a session identified by :sid and :serial
sys.dbms_system.set_ev(:sid, :serial, 10046, 8, '')
/* code to be traced executes during this time window */
sys.dbms_system.set_ev(:sid, :serial, 10046, 0, '')

You may also refer to Metalink Note: 376422.1 which describes recommended methods for getting extended trace:

Subject:	Recommended Method for Obtaining 10046 trace for Tuning
	Doc ID:	Note:376442.1	Type:	FAQ
	Last Revision Date:	19-FEB-2008	Status:	PUBLISHED

PURPOSE

Outline how to effectively gather 10046 trace for use with tuning issues.

SCOPE & APPLICATION

DBAs, Developers and Support personnel

Gathering 10046 trace

Event 10046 is the standard method of gathering extended sql_trace information for Oracle sessions.
For details of the event see:

Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"

For Tuning issues the typical need is to record wait and bind variable infrormation which is achieved using level 12.
The following examples outline how to set the event in various scenarios:

• Session Tracing
• Tracing a process after it has started
• Instance wide tracing
• Initialisation parameter setting
• Tracing sessions via a logon trigger

Event 10046 tracing will produce a trace file in the for user processes and for background processes.
To show the loaction of the user_dump_dest, the following command can be used:

show parameter user_dump_dest
 

Note that some examples include setting a 'tracefile_identifier' to assist with finding the resultant trace output.

• Session Tracing
  
  This tracing can be used where the session is accessible to the user prior to the start of the statement(s) to be traced.
  To gather 10046 trace at the session level:

  
  alter session set tracefile_identifier='10046';
  
  alter session set timed_statistics = true;
  alter session set statistics_level=all;
  alter session set max_dump_file_size = unlimited;
  
  alter session set events '10046 trace name context forever,level 12';
  
  -- Execute the queries or operations to be traced here --
  
  select * from dual;
  exit;
   

  If the session is not exited then the trace can be disabled using:

  
  alter session set events '10046 trace name context off';
   

  Note that if the session is not closed cleanly and tracing is disabled, then important trace information may be missing from the trace file.
  
  
• Tracing a process after it has started
  
  If trace from an existing session is required then oradebug can be used to attach to the session and initiate 10046 tracing.
  
  
  1. The first step is to identify the session to be traced by some means:
     
     For example, in SQL*Plus, start a session to find the OS process id (spid) for the target session:

     
     select p.PID,p.SPID,s.SID
     from v$process p,v$session s
     where s.paddr = p.addr
     and s.sid = &SESSION_ID
     /
      

     SPID is the operating system Process identifier (os pid)
     PID is the Oracle Process identifier (ora pid)
     
     
  2. Once the OS process id for the process has been determined then the trace can be initialised as follows:
     
     Lets assume that the process to be traced has an os pid of 9834.
     Login to SQL*Plus as a dba and execute the following:

     
     connect / as sysdba
     oradebug setospid 9834
     oradebug unlimit
     oradebug event 10046 trace name context forever,level 12
      

     Remember to replace the example '9834' value with the actual os pid.
  Note that it is also possible to attach to a session via oradebug using the 'setorapid'.
  In this case the PID (Oracle Process identifier ) would be used (rather than the 'SPID') and the oradebug text would change to:

  
  connect / as sysdba
  oradebug setorapid 9834
  oradebug unlimit
  oradebug event 10046 trace name context forever,level 12
   

  Remember to replace the example '9834' value with the actual ora pid.
  
  To disable oradebug tracing once tracing is finished:

  
  oradebug event 10046 trace name context off
   

  Alternatively you can use the DBMS_SUPPORT package to trace sessions.
  To install the DBMS_SUPPORT package see: Note 377204.1 How to Install the DBMS_SUPPORT Package
  
  To set session tracing using the DBMS_SUPPORT package:

  
  exec DBMS_SUPPORT.START_TRACE_IN_SESSION( &SID, &Serial, waits=>true, binds=>true )
   

  SID is the Oracle Session ID for the session that is to be traced.
  To disable the tracing use:

  
  exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION( &SID , null )
   

• Instance wide tracing
  
  This setting will trace every session that is created after the parameter is set. Existing sessions will not be traced.
  
  Setting system-wide 10046 tracing can be useful for scenarios where a problem session is known to occur but cannot be identified in advance.
  In this situation, tracing can be enabled for a short period of time, the problem can then be reproduced and tracing disabled and the resultant traces searched for evidence of the problem.
  
  System-wide tracing can be enabled as follows:

  
  alter system set events '10046 trace name context forever,level 12';
   

  The setting can be disabled in all sessions by using the following command:

  
  alter system set events '10046 trace name context off';
   

• Initialisation parameter setting
  
  This setting will trace every session in the instance when it is restarted.

  
  event="10046 trace name context forever,level 12"
   

  The setting can be disabled by removing the parameter and restarting the instance or by using an alter system command as follows:

  
  alter system set events '10046 trace name context off';
   

• Via a Logon Trigger
  
  There may be some situations where it is necessary to trace the activity of a specific user. In this case a logon trigger could be used.
  An example is provided below:

  
  CREATE OR REPLACE TRIGGER SYS.set_trace
  AFTER LOGON ON DATABASE
  WHEN (USER like  '&USERNAME')
  DECLARE
      lcommand varchar(200);
  BEGIN
      EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
      EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
      EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
  END set_trace;
  /
   

RELATED DOCUMENTS

Note 41634.1 TKPROF and Problem Solving
Note 214106.1 Using TKProf to compare actual and predicted row counts
Note 32951.1 Tkprof Interpretation
Note 75713.1 Important Customer information about using Numeric Events
Note 21154.1 EVENT: 10046 "enable SQL statement tracing (including binds/waits)"
Note 258418.1 Getting 10046 Trace for Export and Import:
Note 242374.1 Tracing PX session with a 10046 event or sql_trace
Note 171647.1 Tracing Oracle Applications using Event 10046
Note 1058210.6 How to Enable SQL_TRACE for Another Session Using Oradebug
Note 224270.1 Trace Analyzer TRCANLZR - Interpreting Raw SQL Traces with Binds and/or Waits generated by EVENT 10046
Note 377204.1 How to Install the DBMS_SUPPORT Package
Note 62294.1 The DBMS_SUPPORT Package.
Note 62160.1 Tracing Sessions in Oracle

10g Startup upgrade shows ORA-00604 ORA-00942 in alert log

I was called to help out in an issue during an Oracle 10g upgrade. When the STARTUP UPGRADE command was given, the following error appeared in the alert log file:

Errors in file $ORACLE_HOME/admin/$TWO_TASK/bdump/$TWO_

TASK_smon_nnnn.trc: (Actual file name changed to generic variables)
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist

To get more details about which table was missing we put in this line in the init.ora file:

event="942 trace name ERRORSTACK level 3"
An additional trace file was generated which gave us this detail:

ORA-00942: table or view does not exist
Current SQL statement for this session:
delete from javaobj$ where obj#=:1

Usually all objects with a $ suffix are owned by SYS.

SQL> desc sys.javaobj$
ERROR:
ORA-04043: object javaobj$ does not exist

So the error was coming because javaobj$ did not exist.

We checked in other 9i databases and found that javaobj$ did not exist there too. But a describe on 10g databases found the javaobj$"

SQL> desc sys.javaobj$
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
AUDIT$ NOT NULL VARCHAR2(38)

Further research led me to metalink note 316889.1 Complete checklist for manual upgrades to 10gR2. Step 20 in that note says:

- Set the COMPATIBLE initialization parameter to an appropriate value. If you are upgrading from 8.1.7.4 then set the COMPATIBLE parameter to 9.2.0 until after the upgrade has been completed successfully. If you are upgrading from 9.2.0 or 10.1.0 then leave the COMPATIBLE parameter set to it's current value until the upgrade has been completed successfully. This will avoid any unnecessary ORA-942 errors from being reported in SMON trace files during the upgrade (because the upgrade is looking for 10.2 objects that have not yet been created)

The compatible parameter was set to 10.2.0 in the init.ora file. That's why even though the database was still in 9i, it was looking for objects present in 10g. However since this error could be ignored, I advised the team doing the upgrade to go ahead and complete the upgrade. I also asked them to update their installation document to set the compatible parameter to 10.2.0 only after the upgrade is over, so that they do not witness this issue in further iterations.

Solaris NIC speed and duplex settings

In a newly created environment, we had a performance problem copying from app tiers to CUSTOM_TOP which is a NAS mount. On investigation it was found that the NIC speed on some of the app servers was set to 100mbps half duplex, whereas on switch side it was 1Gbps full duplex. This was corrected by the network team. However when the servers were rebooted during a SAN upgrade, the problem resurfaced. I googled to find out the Solaris command to check NIC speed and duplex settings and found this link:

http://www.brandonhutchinson.com/Solaris_NIC_speed_and_duplex_settings.html

The site is a goldmine of unix gotchas. This is the description of the site: "The purpose of this Web site is to document "lessons learned" as a Senior UNIX Systems Engineer for an application services provider in Minneapolis, MN. It contains notes that I wrote for my own and co-workers' reference; these notes may benefit others working on the same problems"

I am reproducing the article from google cache as it is not visible to me anymore:

Solaris NIC speed and duplex settings

The following is a Bourne shell script I wrote to determine the speed and duplex settings for all active network interfaces on a Solaris system. Thanks to William Favorite for fixing the script to work with two-letter network interfaces (e.g. ge0), and to Royce Williams for providing a patch to exclude subinterface information.

Download the script.

Example output:

Interface Speed Duplex
--------- ----- ------
hme0 100 Mbit/s full
hme1 100 Mbit/s full
hme2 100 Mbit/s full

Setting NIC speed and duplex

Solaris is often unable to correctly auto-negotiate duplex settings with a link partner (e.g. switch), especially when the switch is set to 100Mbit full-duplex. You can force the NIC into 100Mbit full-duplex by disabling auto-negotiation and 100Mbit half-duplex capability.

Example with hme0:

1. Make the changes to the running system.
# ndd -set /dev/hme adv_100hdx_cap 0
# ndd -set /dev/hme adv_100fdx_cap 1
# ndd -set /dev/hme adv_autoneg_cap 0

2. Make kernel parameter changes to preserve the speed and duplex settings after a reboot.
# vi /etc/system

Add:
# set hme:hme_adv_autoneg_cap=0
# set hme:hme_adv_100hdx_cap=0
# set hme:hme_adv_100fdx_cap=1

Note: the /etc/system change affects all hme interfaces if multiple NICs are present (e.g. hme0, hme1).

More information:
http://www.science.uva.nl/pub/solaris/solaris2/Q4.13.html

The /etc/system settings listed above are not supported for configuring ce Ethernet adapters during system startup; you may either use ndd commands in an /etc/rc?.d script or create a /platform/sun4u/kernel/drv/ce.conf file with appropriate settings.

Example: /etc/init.d/nddconfig

#!/bin/sh

ndd -set /dev/ce instance 0
ndd -set /dev/ce adv_1000fdx_cap 0
ndd -set /dev/ce adv_1000hdx_cap 0
ndd -set /dev/ce adv_100fdx_cap 1
ndd -set /dev/ce adv_100hdx_cap 0
ndd -set /dev/ce adv_10fdx_cap 0
ndd -set /dev/ce adv_10hdx_cap 0
ndd -set /dev/ce adv_autoneg_cap 0

# ln -s /etc/init.d/nddconfig /etc/rc2.d/S31nddconfig

$ dmesg | grep ce0
Jan 20 11:05:01 crmmdb22 genunix: [ID 611667 kern.info] NOTICE: ce0: xcvr addr:0x01 - link up 100 Mbps half duplex
Jan 20 11:05:15 crmmdb22 genunix: [ID 408822 kern.info] NOTICE: ce0: no fault external to device; service available
Jan 20 11:05:15 crmmdb22 genunix: [ID 611667 kern.info] NOTICE: ce0: xcvr addr:0x01 - link up 100 Mbps full duplex

Manually determining NIC speed and duplex

If you have ce or bge interfaces, use kstat ce and kstat bge, respectively, to return NIC settings. All other interfaces may use ndd to determine NIC settings.

ndd example with hme0, assuming "instance" is 0:

# ndd -get /dev/hme link_mode
Interpretation:
0 -- half-duplex
1 -- full-duplex

# ndd -get /dev/hme link_speed
Interpretation:
0 -- 10 Mbit
1 -- 100 Mbit
1000 -- 1 Gbit

To query a different NIC, such as hme1, set the "instance" to 1, and then perform the link_mode and link_speed queries above.

# ndd -set /dev/hme instance 1

Note: the ndd commands above must be run as root. Otherwise, you will receive errors such as "couldn't push module 'hme0', No such device or address."

ce Ethernet adapters

Older versions of the Sun GigaSwift Ethernet 1.0 driver do not support the ndd link_mode and link_speed parameters. You may either install the latest Sun GigaSwift Ethernet adapter patch (111883) or you may use kstat ce ce_device to get speed and duplex information for ce Ethernet adapters.

For example (from http://www.samag.com/documents/s=9142/sam0405l/0405l.htm):

$ netstat -k ce | egrep 'link_speed|link_status|link_duplex'

Interpretation:
link_up - 0 down, 1 up
link_speed - speed in Mbit/s
link_duplex - 1 half duplex, 2 full duplex, 0 down

Host/link partner mismatch example

A large number of output errors or collisions may indicate a host and link partner mismatch. The following is netstat -in output from a system configured for 100 half-duplex while the switch was configured for 100 full-duplex.

Name Mtu Net/Dest Address Ipkts Ierrs Opkts Oerrs Collis Queue
ce0 1500 192.168.1.0 192.168.1.1 2707133478 25 2895422910 142310052 182856975 0

In this example, the switch was configured for 100 half-duplex and the system was configured for 100 full-duplex. Note the percentage of Ierrs/Ipkts (~ 1.7%).

Name Mtu Net/Dest Address Ipkts Ierrs Opkts Oerrs Collis Queue
qfe1 1500 10.0.0.0 10.0.0.3 1430247 24663 1779341 0 0 0

CHANGELOG

2007/07/10 -- Added e1000g interfaces
2006/11/29 -- Added dmfe interfaces, fixed bge and ce interfaces, many other modifications and fixes.
2006/04/27 -- Solaris 7 and earlier include subinterface information with netstat -i. Patch by Royce Williams to only query physical NIC interfaces.
2006/03/09 -- Fixed bge interfaces, added support for iprb interfaces, added "support" for le interfaces, modified script to only require root access if using /usr/sbin/ndd to determine NIC speed and duplex settings
2005/05/25 -- Fixed ce interface handling.
2004/09/15 -- Added support for ce and bge interfaces.


The script to show the NIC settings is available on http://www.brandonhutchinson.com/speed_duplex. I have kept a copy of it in our google group here as I was unable to access it from bradonhutchinson.com.