a email was arrived from a friend to query about the abnormal information in trace file which contains something like “opiino: Attach failed!….”. while left no “ORA-” error msg in alert file.
after checking the support.oracle.com, I had something to share,check below:
It’s quite useful.
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4 – Release: 10.2 to 10.2
Oracle Net Services – Version: 10.2.0.1.0 to 10.2.0.4.0 [Release: 10.2 to 10.2]
Information in this document applies to any platform.
Checked for relevance on 09-JUNE-2011
When a client process is abnormally disconnected from the Database (server) process, specifically at a point where the Database needs to negotiate and communicate certain OCI / TWO-TASK data to the client such as with character sets, OCI, etc., then the Database may throw one of the following errors in a UDUMP trace:
opiino: Attach failed! error=-1 ifvp=0
opiino: Attach failed! error=-1 ifvp=(nil)
Perhaps an abnormally high increase in connection activity from App Servers or a network condition or change causing instability.
There are various reasons why this error might be reported by the Database and some are offered below, but it should be noted that it is “post-connection” generated meaning that the Client connection has been handed off successfully by the Listener process (shown as “Established” in the listener log and services summary) and is generated by the Database itself.
1. The error is being caused by an abnormal disconnection at the OPI / OCI (TWO-TASK) level of the Client process by the Database.
This will occur if the client is terminated (abnormally) before the Database can complete the TWO-TASK communication functions. Please see the reference note on TWO-TASK analysis and resolution.
2. Another possible cause and one that can contribute to a high increase of connections or a cause of disconnections, is a default (or lower) setting of the INBOUND_CONNECT_TIMEOUT value introduced with 10g and which was found to have a default value that was too low for many environments. Another error that might be reported in the Database alert log for the same type of abnormal disconnection and especially with 11g, is ORA-609 “opiodr aborting process unknown ospid”. These two are very similar in that they are generated from the “opi” Database layer and due to an abnormal client termination (with the “ORA-609” being more common in 11g and the “Attach failed” being more common in 10g).
3. Another potential (but rare) cause is if the Listener being used for Client access is of a LOWER version than the Database being accessed. For example, using a 9.2.x listener to access a 10.x Database which is not certified or supported and will cause abnormal conditions for connections.
1. Due to the fact that this is a generic error within the Database OPI code, the error itself is not useful in determining any actual cause or in assisting to provide a solution.
Analysis needs to be done on what was happening between the clients and the Database server at the time these were thrown and which caused the failure.
Some examples of questions to ask and data to collect:
– was the network between the affected clients and the Database in a stable condition?
– was there a sudden and abnormal increase in connection activity that may have flooded the Database with connections?
– was the Client abnormally terminated on its side (such as a machine failure)?
2. To ensure that this is not related to a possible INBOUND_CONNECT_TIMEOUT issue (IBCT), make certain to set both the IBCT in the listener.ora file and sqlnet.ora file on the Database Server side (these are INBOUND CONNECT settings so only affect incoming client connections requests) to a higher value, such as 120.
Please note, that this increase in IBCT will not have a negative effect on how long a client connection will take to be established. This setting is a security feature and is a threshold used to determine how long to wait before an unresponsive connection is terminated (used specifically to prevent Denial of Service attacks on the Database). See the notes referenced for complete details.
3. If this occurs ALL the time and there is an indication that a lower version listener is used for a higher version Database, then the same (or higher) version listener should be started and used for the Database.
See reference notes following on this.
NOTE:1012295.102 – HOW TO SUPPORT TWO-TASK COMMON ERRORS
NOTE:149628.1 – Creating Multiple Listeners on UNIX – Including Migration or Upgrades
NOTE:151812.1 – Setting up Multiple Listeners on Windows – Including Migration or Upgrades
NOTE:345197.1 – Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113,ORA-3106 or ORA-3136 from 10.2 Onwards
NOTE:609.1 – ORA-609 TNS-12537 and TNS-12547 in 11g Alert.log
NOTE:1121357.1 – Troubleshooting ORA-609 : opiodr aborting process unknown ospid