ORA-03136 in alert log

A lot of ORA-3136 started appearing in alert log after the latest 10g upgrade:

Thu Mar 20 02:52:16 2008
WARNING: inbound connection timed out (ORA-3136)
Thu Mar 20 02:58:00 2008

Metalink Note 345197.1, suggests setting the value of INBOUND_CONNECT_TIMEOUT_ TO 120 or higher in listener.ora and in sqlnet.ora to prevent this.

Symptoms

The Oracle Net 10g parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername default to 0 (indefinite) in 10.1. To address Denial of Service (DOS) issues, the parameters were set to have a default of 60 (seconds) in Oracle 10.2.

If applications are longer than 60 secs to authenticate with the Oracle database, the errors occur.

The following may be seen in the alert log: WARNING: inbound connection timed out (ORA-3136)

SQLNET.INBOUND_CONNECT_TIMEOUT is set to a value in seconds and determines how long a client has to provide the necessary authentication information to a database.

INBOUND_CONNECT_TIMEOUT_listenername is set to a value in seconds and determines how long a client has to complete its connect request to the listener after the network connection has been established.

To protect both the listener and the database server, Oracle Corporation recommends setting INBOUND_CONNECT_TIMEOUT_listenername in combination with the SQLNET.INBOUND_CONNECT_TIMEOUT parameter.

Cause

Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.

Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.

Solution

Set the parameters SQLNET.INBOUND_CONNECT_TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername to 0 (indefinite) or to an approprate value for the application yet still combat DOS attacks (120 for example).

These parameters are set on the SERVER side:
listener.ora: INBOUND_CONNECT_TIMEOUT_listenername
sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT

Further tuning of these parameters may be needed is the problem persists.

ORA-01986: OPTIMIZER_GOAL is obsolete

I got a call for issue with a form. After the 10g upgrade, a standard form Launch Contracts which takes 1 second to launch was taking 30 minutes to open. A trace on this form showed

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 2)
unable to set optimizer goal
ORA-01986: OPTIMIZER_GOAL is obsolete

parse error offset: 33

Bug 5854184 describes this issue and advises:

5386974.992 KAT Alt 100 05-May-2006 11.5.10 LAUNCH CONTRACTS IS VERY SLOW
Fix: Check if you have index WF_NOTIFICATIONS_N1 for table WF_NOTIFICATIONS
If you have this index please rebuild the index, analyze the table and the index. This will allow the optimizer to choose the best plan to execute the
Query.

Rebuilding the index didn’t help. Analyzing the table and index did not help. Gathering statistics with DBMS_STATS package on table did the trick:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => NULL, TABNAME =>'WF_NOTIFICATIONS');

PL/SQL procedure successfully completed.

SQL> exit

Once I ran this, the form started opening in less than 1 second.

make: Fatal error: Don't know how to make target '$FND_TOP/lib/wfresgcp.o'

While applying patches on a cloned instance the relink of WFRESGEN failed with following error on the web tier:

make -f $APPL_TOP/admin/$TWO_TASK/out/link_fnd_24844.mk $FND_TOP/bin/WFRESGEN

Starting link of fnd executable 'WFRESGEN' on Fri Mar 28 16:21:26 EDT 2008
make: Fatal error: Don't know how to make target `$FND_TOP/lib/wfresgcp.o'
Done with link of fnd executable 'WFRESGEN' on Fri Mar 28 16:21:26 EDT 2008

Relink of module "WFRESGEN" failed.

There was no file called $FND_TOP/lib/wfresgcp.o. To fix this I copied this file from the concurrent tier to the web tier. Relink of WFRESGEN completed successfully after this.

Interesting post on max_dump_file_size

From Oracle Database reference:

MAX_DUMP_FILE_SIZE

Property	Description
Parameter type	String
Syntax	MAX_DUMP_FILE_SIZE = { integer [K | M] | UNLIMITED }
Default value	UNLIMITED
Modifiable	ALTER SESSION, ALTER SYSTEM
Range of values	0 to unlimited, or UNLIMITED
Basic	No

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.

• A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.

• A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.

• The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.

I found a very interesting post on Doug's Oracle blog about issues faced while setting this parameter.

OS Watcher (OSW) and Lite Onboard Monitor (LTOM)

While searching for something else, I came across Metalink Note 370936.1 (Previous Announcements from New in the Knowledge Base). What captured my attention was this:

December 12, 2007 - Oracle's Center of Expertise Releases New Documents

The following four new white papers have just been released by Oracle's Center of Expertise:

• 10g Upgrade Companion
  
• Determining CPU Resource Usage for Linux and Unix
  
• Measuring Memory Usage for Linux and Unix
  
• Best Practices for Load Testing

I checked the second and third white papers, both of which are written by Roger Snyde from Oracle Support's Center of Expertise. These white papers describe a tool called OSW (OS Watcher) . Oracle Support’s Center of Expertise has developed OSWatcher, a script-based tool for Unix and Linux systems that runs and archives output from a number of operating system monitoring utilities, such as vmstat, top, iostat, mpstat and ps.

OSWatcher is available from Metalink as note 301137.1. It is a shell script tool and will run on Unix and Linux servers. It operates as a background process and runs the native operating system utilities at user-settable intervals, by default 30 seconds, and retains an archive of the output for a user settable period, defaulting to 48 hours. This value may be increased in order to retain more information when evaluating performance, and to capture baseline information during important cycle-end periods.

Oracle recommends customers download and install OSWatcher on all production and test servers that need to be monitored.

While going through 301137.1, I found the mention of another tool called LTOM(The embedded Lite Onboard Monitor): To collect database metrics in addition to OS metrics consider running LTOM. The Lite Onboard Monitor (LTOM) is a java program designed as a real-time diagnostic platform for deployment to a customer site. LTOM differs from other support tools, as it is proactive rather than reactive. LTOM provides real-time automatic problem detection and data collection. LTOM runs on the customer's UNIX server, is tightly integrated with the host operating system and provides an integrated solution for detecting and collecting trace files for system performance issues. The ability to detect problems and collect data in real-time will hopefully reduce the amount of time it takes to solve problems and reduce customer downtime.

Both OSW and LTOM now provide a graphing utility to graph the data collected. This greatly reduces the need to manually inspect all the output files.

Sample graph from OSW:

Sample graphs from LTOM:

E-Business Suite Diagnostics XML Files

The latest E-Biz Diagnostic patch is 6459585 (E-Business Suite Diagnostic Pack February 2008). I came across metalink note 357745.1:

This document contains the most recent versions of the xml files used by diagnostics tools within Oracle Diagnostics. XML files are used by Oracle Support to hold data that requires being regularly updated such as maintainance packs, family packs, patches and other supporting data. These XML files are used by diagnostics tools executable in Oracle Diagnostics to verify for example if a particular family pack is installed.

In order for diagnostics tools to check your environment against the latest information available from Oracle, these xml files are updated and released on an ongoing basis. This means that the latest available xml files will always be available from this note while the latest E-Business Suite diagnostics patch will include the latest available xml files at the time of the patch build.

So, it is a good practice to visit this metalink note, if you are applying the latest diagnostic pack. As a post step you can download the latest xml files from this note and unzip them in your $IZU_TOP/html directory.