Subject: TWO-TASK COMMON ERRORS Creation Date: 01-APR-1999 Solution Description: ===================== TWO-TASK COMMON ERRORS ====================== Jointly Created By: Cameron Melvin, James Dettmer, Anant Singh, Judy Brink There is a lot of confusion between the definitions of the "TWO_TASK" environment variable and the TWO-TASK common layer. Sometimes, error messages will be returned to a user that say something to the effect of "fatal two-task communication protocol error", or something of a similar nature. While words like 'two-task' and 'protocol' have a familiar sounding interpretation, their true definition may be somewhat different. I will attempt to define "two_task" and two-task in a way that will hopefully clarify their differences. "TWO_TASK" ========== "TWO_TASK" is a Unix environment variable that can be set to a default TNS connect string. For example, you issue the following command at a Unix prompt: % setenv TWO_TASK test_db.world When you invoke a tool such as SQL*Plus, you would only need to type: % sqlplus scott/tiger in order to be connected to the TNS address of "test_db.world". Some users prefer this to typing: % sqlplus scott/tiger@test_db.world Two-Task Common =============== Two-Task Common provides character set and data type conversion between different character sets or formats on the client and server. This layer is optimized to perform conversion only when required on a per connection basis. At the time of initial connection, Two Task Common is responsible for evaluating differences in internal data and character set representations and determining whether conversions are required for the two computers to communicate. A "protocol" is simply a set of rules. Sometimes, Two-Task Common is referred to as a protocol because Two-Task Common needs a set of rules to perform the character set and data type conversions. Figure 1 illustrates the Two-Task Common layer in relation to other Oracle layers. Client Server Stack Stack +-------------------+ +-------------------+ |Client Application | User | Oracle Server | +-------+-------------------+-----------+-------------------+----------+ | | OCI | System | OPI | | | +-------------------+ +-------------------+ | |RDBMS | Two-Task Common | | A | Two-Task Common | RDBMS | +-------+-------------------+ | | +-------------------+----------+ | | NI | | | | NI | | | +-------------------+ | | +-------------------+ | |Net8 | NR/NN/NA | | | | NR/NN/NA | Net8 | | +-------------------+ | | +-------------------+ | | | TNS | | | | TNS | | | +-------------------+ | | +-------------------+ | | | Oracle Protocol | | | | Oracle Protocol | | | | Adapter | V | | Adapter | | +-------+-------------------+ +-------------------+----------+ | Network Specific |-----/ | Network Specific | | Protocol | /------| Protocol | +-------------------+ +-------------------+ Figure 1 This figure (and the definition of Two-Task Common) is provided in "Oracle Net8 Administrator's Guide Release 8.0" The definitions of Two-Task Common are not only limited to Oracle 8. In the following documentation "Understanding SQL*Net", a similar diagram and definition for Two-Task Common are given. The following errors are examples of two-task common errors: ORA-3100: communication area cannot be allocated ORA-3106: fatal two-task communication protocol error ORA-3117: two-task save area overflow Examples of "TWO_TASK" errors would be: TNS-12154: TNS could not resolve service name TNS-12203: TNS could not connect to destination TNS-12538: TNS no such protocol adapter Troubleshooting =============== Two-task common errors are generally RDBMS related issues, but could be caused by a problem with SQL*Net, or an application (i.e. Pro*C). ORA-3106 ======== Possible reasons for the ORA-3106 errors include: 1. Incompatibilities between the client application and the RDBMS server. For example, version incompatibilities or a client trying to use a feature not supported by the database kernel. 2. When using database links or gateways. 3. Network or SQL*Net problems. 4. Corruptions. 5. PL/SQL - language related. RDBMS - Troubleshooting two-task common errors ============================================== 1. Check for NLS incompatibilities between the client and the server. This is a very popular cause of the ORA-03106 error. 2. Check the Alert log and RDBMS trace files for any pertinent information / errors that coincide with getting the ORA-03106. 3. Try to get a testcase and reproduce the problem in-house. 4. If a testcase is not possible, ask for SQL*Net trace files, SQL traces, and try setting event 3106 to obtain the error stack. 5. In extreme circumstances, this could indicate that a shared memory segment used by Oracle is corrupted. You will have to shut down the database, possibly using the abort option. Make sure all semaphores have been released by using the IPCS command on Unix. Oracle uses semaphores to control concurrency between all of the background processes (pmon, smon, drwr, lgwr, and oracle shadow processes). Semaphores are also used to control Two-Task communication between the user process and the shadow process. 6. Try and narrow down the code that may be causing it. For example, it might have started to happen after increasing the number of parameters in a PL/SQL block or when using certain values for bind variables, etc. 7. If we still do not make headway, file a bug under RDBMS, but we still may need some or all of the above information as indicated above. ORA-3117: Two-Task Save Area Overflow ===================================== This error indicates that the buffer area used for NLS character set conversion is too small. This is caused by a data overflow in the Two-Task Common layer, which is shared between the client and server. This error is seen mostly after upgrading the Oracle Interface layer (Required Support Files) since this usually includes an upgrade to NLS (character conversion layer). The memory allocated for character set conversion/translation cannot be modified by the user. This error should be handled by the database, and therefore the RDBMS group should handle diagnostics and bug filing. ORA-3119: Two-Task coroutine has invalid state ============================================== 1. Make sure enough semaphores are available to Oracle. 2. Loading data from foreign source (SQL*Loader) and data contains packed decimal numbers. This error has occurred when converting packed decimal number to Oracle number. Check for bugs. 3. Binding a packed decimal to a PL/SQL function that returns a real. Check for bugs. 4. Using database links with Oracle Parallel Server. See bug 491378. ORA-3120: Two-Task co-routine integer overflow ============================================= An integer value overflowed an internal Oracle structure during a send or receive over a connection. This can be caused by invalid buffer length/size or by specifying a too great of row count. This error is generally an issue with the application or possibly SQL*Net. ORA-3129: The next piece to be inserted is required =================================================== This is an issue with bind variables. This is a language/OCI issue. What we agreed to was for each group (rdbms, net, lang) to come up with a list of troubleshooting methods to go through when presented with a two-task problem. Networking - Troubleshooting two-task common errors =================================================== 1. Get level 16 client side trace file. 2. Using an editor, find the 3106 error. Since this error is coming back from the RDBMS, you should see it in a data packet coming from the server. For example: nsprecv:00 46 00 00 06 00 00 00 |.F......| nsprecv:00 00 08 02 00 00 00 00 |....ORA-| nsprecv:00 00 00 00 00 04 01 00 |3106.fat| nsprecv:00 00 00 00 00 00 00 00 |al error| nsprecv:01 00 00 00 2F 00 00 00 |in two-t| nsprecv:00 00 00 00 00 00 00 00 |ask prot| nsprecv:00 00 00 00 00 00 00 00 |ocol....| nsprecv:00 00 00 00 00 00 00 25 |.......%| nsprecv:00 00 01 00 00 00 00 00 |........| 3. Once you've found the error, start reading the trace file backwards, looking at the packets. What you are looking for may be something like the last SQL statement ran. You will see this in a data packet being sent from the application. For example: nspsend:00 BA 00 00 06 00 00 00 |........| nspsend:00 00 03 03 0E 01 00 00 |........| nspsend:00 08 22 45 00 A1 00 00 |.."E....| nspsend:00 73 65 6C 65 63 74 20 |.select | nspsend:61 74 74 72 69 62 75 74 |attribut| nspsend:65 2C 73 63 6F 70 65 2C |e,scope,| nspsend:6E 75 6D 65 72 69 63 5F |numeric_| nspsend:76 61 6C 75 65 2C 63 68 |value,ch| nspsend:61 72 5F 76 61 6C 75 65 |ar_value| nspsend:2C 64 61 74 65 5F 76 61 |,date_va| nspsend:6C 75 65 20 66 72 6F 6D |lue from| nspsend:20 73 79 73 74 65 6D 2E | system.| nspsend:70 72 6F 64 75 63 74 5F |product_| nspsend:70 72 69 76 73 20 77 68 |privs wh| nspsend:65 72 65 20 28 75 70 70 |ere (upp| nspsend:65 72 28 27 53 51 4C 2A |er('SQL*| nspsend:50 6C 75 73 27 29 20 6C |Plus') l| nspsend:69 6B 65 20 75 70 70 65 |ike uppe| nspsend:72 28 70 72 6F 64 75 63 |r(produc| nspsend:74 29 29 20 61 6E 64 20 |t)) and | nspsend:28 75 70 70 65 72 28 75 |(upper(u| nspsend:73 65 72 29 20 6C 69 6B |ser) lik| nspsend:65 20 75 73 65 72 69 64 |e userid| nspsend:29 00 00 00 00 00 00 00 |).......| 4. Look at SQL data headers. This is a little trickier, but will provide a flow of communications between the application and Oracle. For example, you may see packets above the ORA-03106 packet that look very small, or appear to carry no data. On the contrary, these packets sometimes provide a wealth of information, but you have to know what to look for. In the packet below, the first 10 bytes of data (00, 0D, 00, 00, 06, 00, 00, 00, 00, 00) is the TNS packet header. Depending of the function, the next byte (the 11th byte) starts the SQL header. The RDBMS or application uses the SQL header. In the example below, '03' is the start of the SQL header. The next byte (the 12th byte) is normally some kind of flag that accompanies the byte before it. So, in this case, '03' is "User to Oracle (UPI) Request", and '08' is "OCLOSE - close cursor". Since this packet is being sent from the application, you would ask yourself why the application is calling OCLOSE? nspsend:00 0D 00 00 06 00 00 00 |........| nspsend:00 00 03 08 37 00 00 00 |....7...| With a few packets decoded like this, you can get a real feel for what is going on between the client and server. Some of the more popular byte combinations are as follows (this is a small fraction of all Oracle byte combinations. The full list is on tao in "/rdbms/include/opidef.h", and don't forget to convert hex to decimal if needed): 11th byte 12th byte 03 - User to Oracle (UPI) Request 03 47 - OALL7 Fast UPI Call to opial7 03 2B - ODSCRARR Describe array 03 05 - OFETCH Fetch Row 03 52 - O3LOGA 1st half of logon 03 51 - O3LOGON 2nd half of logon 03 3B - OVERSION Get oracle version date string 03 02 - OOPEN Open Cursor 03 27 - OPARSEX Parse and Execute 03 08 - OCLOSE close cursor 03 03 - OSQL Parse Statement 03 3E - OALL Bundled Execution Call 03 09 - OLOGOFF Logoff from DB 03 0E - OCOMMIT - Commit 08 xx = Return OPI Parameter 06 xx = Row Transfer Header 0B xx = Sending I/O VEC only for fast UPI 01 xx = Set Station protocol (NLS stuff) 02 xx = Set Datatype representations 04 xx = Error Return Status 09 = Oracle Done/Continue 07 xx = Row Transfer Data Follows 5. You may see Marker packets (NSPTMK) and some ORA-03111 errors in the trace file. These can usually be ignored. It is just a part of the break handling protocol that attempts to resync communications if the client app and server are operating too fast for each other. If you think this may be a problem, work the issue. 6. There may be an SQL*Net or ODBC incompatibility with the application of Gateway. Languages - Troubleshooting two-task common errors ================================================== 1. Two-Task errors can occur if old libraries were linked with the application. Check the version of required support files and any other libraries for compatibility problems with the application. 2. Try to narrow down the code that may be causing the problem. For example, it might have started to happen after increasing the number of parameters in a PL/SQL block or when using certain values for bind variables.