ORA-12592 "TNS:bad packet" + ORA-12161 "TNS:internal error: partial data received" with simple query

[Bernhard S]

I get the ORA-12592 using PL/SQL Developer (PSD) v9.0.6.1665 against our Prod DB when trying to execute the following SQL in a SQL Window:

select * from v$parameter vp
 where vp.isdefault='FALSE';

PSD completely hangs then and one of the two CPUs keeps spinning at 100% until I force close it.

In SQL*Plus I don't get any error against the same DB with the same SQL.
In PSD against our Dev DB I don't get the problem either. I also don't get the problem against Prod DB, if I leave out the where-clause.
How can the root cause of this error be found and fixed?

 

[Marco Kalter]

Is the error specific for this query? It seems like an issue when making a secondary connection.

To obtain some more diagnostic information, can you modify the shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory or in the %APPDATA%\PLSQL Developer directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer).

 

[Bernhard S]

Yes, it's specific for this query. It's also specific to certain databases.
Against one of those databases I had the error yesterday, but not today anymore.
Unfortunately the query works fine when PL/SQL Developer is started with the DebugSQL parameter.
So how can we further investigate?

 

[Bernhard S]

I managed to limit the selected columns while still getting the ORA-12592

select name, value, display_value
  from v$parameter
 where isdefault='FALSE';

If I remove any of those three columns and select only the remaining two, I won't get the error anymore. It doesn't matter, which of the three I remove.
Here the datatype of all affected columns as shown via describe:

NAME                  VARCHAR2(80)
VALUE                 VARCHAR2(512)
DISPLAY_VALUE         VARCHAR2(512)
ISDEFAULT             VARCHAR2(9)

I hope this helps in figuring out the cause of this problem.

 

[Bernhard S]

I get this error also in a "Command Window", which has the advantage, that my whole PL/SQL Developer won't freeze and force me to kill it. I just get the error and that's it. I found out now, that after getting this error with previous query and trying to query then:

select * from v$parameter vp
 where vp.num between 1 and 1000
 order by vp.name;

I got an ORA-12161: TNS:internal error: partial data received
After this error I no longer got an error with the previously error causing queries from above in the same "Command Window" anymore! So this query actually cured the ORA-12592 condition in this session.

 

[Bernhard S]

An alternative "first cause ORA-12161 and then it's ok"-query would be:

select * from v$parameter vp
 where rownum <=200 --won't work with 199 as limit anymore!
 order by vp.name;

This is also a workaround for now in "SQL Window". If I query this first, I get a pop-up with ORA-12161 and then the previous ORA-12592 causing queries won't make trouble anymore.
With this query I was also able to produce the ORA-12161 error when PL/SQL Developer was started with DebugSQL command line parameter. I have sent you the generated Debug.txt via email and hope this helps fixing this problem.

 

[Marco Kalter]

This really seems like an Oracle Client/Server communication issue. For example a version compatibility issue or a firewall issue.

Can you try the same on a different PC or from a different Oracle Client on the same PC? Or disable firewalls if applicable?

 

[Bernhard S]

I got this on two different PCs against various databases (Prod, Test), all of them used for our CRM system. I can reproduce the ORA-12161 anytime, even on my Dev DB now. You will certainly understand that I cannot demand any firewall changes just for testing purposes in a company of our size. Besides: If it's really a firewall issue, shouldn't the problem appear with all DBs on the same server using the same query, as long as the same ports are used? This is not the case!
I have set Oracle home and OCI library to 10g in the PL/SQL Developer settings, which is also the DB version and still I get this error. I get it also if I use 11g client against 10g database.
It must have to do with something specific to our CRM databases. I never got this error using the same queries with other 10g or also 11g databases so far. Could it have to do with the size of the SGA, which of course is largest for the CRM databases? After all v$parameter internally in Oracle DB is based on x$ksppi and x$ksppcv. The x$ structures are the sql interface to viewing Oracle's memory in the SGA.

These are my SGA values for Dev CRM database (it's all empty otherwise so far, as I'm just in the process of creating it):

SQL> show sga

Total System Global Area 1610612736 bytes
Fixed Size                  2052448 bytes
Variable Size             385879712 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14721024 bytes
SQL>

 

[Bernhard S]

After changing about 70 parameters on my Dev CRM database, I now get the ORA-12592 even with this plain query:

select * from v$parameter vp;

After about 150 rows returned, I get the ORA-12592.
So it seems like with more changed parameters the risk to run into this error increases. On this database I don't manage to get the less troublesome ORA-12161, which prevented further ORA-12592 in the same seession, at all now.
So any ideas how to go from there?

 

[Bernhard S]

After some further investigation I found out, that those ORA errors never occurred so far while I was connected to a different listener configured to use port 1526, even though I used the same queries that always cause the ORA errors with the default port 1521. I think this is a clear indication that it's realated to a firewall issue.
The following My Oracle Support article describes this:

Troubleshooting guide for ORA-12592 / TNS-12592: TNS: bad packet [ID 373431.1]
...
A common cause is the firewall's "Advanced SQL features" with various wording (depending on the vendor):
- SQLNet fixup (protocol)
- Deep Packet Inspection or DPI
- SQLNet packet inspection
- SQL ALG (Juniper firewalls)

These can have a negative impact on some more advanced Oracle communication packets, especially ones using DBLinks, Archive logging, MViews, Redo logs, etc.

A workaround would be to either DISABLE these features, or move communication off the default port of 1521 (these firewalls are usually setup to inspect or fix the 1521 port as it is Oracle's known SQL port).

A solution would be to contact the Firewall vendor and explain the situation, that their product is altering TCP packets on the network for Oracle communications (and a patch will most likely be provided by the Firewall vendor).

So I will contact our data center DBAs and fire wall specialists for further analysis. My workaround for now is to use a port not equal to the default 1521.

 

[Marco Kalter]

Both errors indicate a problem at the client/server communication level:

ORA-12161: TNS:internal error: partial data received
Cause: The connection may be terminated.
Action: Reconnect and try again. For further details, turn on tracing and reexecute the operation. If error persists, contact Worldwide Customer Support.

ORA-12592: TNS:bad packet
Cause: An ill-formed packet has been detected by the TNS software.
Action: For further details, turn on tracing and reexecute the operation. If error persists, contact Oracle Customer Support.

I can only suggest that you contact Oracle Support.

 

[Bernhard S]

Yes we should contact Oracle Support, but the firewall vendor needs to get involved too, which is beyond my responsibility.

Independently of this further investigation for the root cause, I expect PL/SQL Developer (PSD) to behave more gracefully when a ORA-12592 is encountered in a "SQL Window". It shouldn't completely hang then with CPU spinning at 100%, leaving you only with the option to kill the PSD process altogether, without any chance to save anything unsaved before that and therefore loosing data.
In fact I expect PSD to NEVER get into situations, where you're forced to kill it. Any occurring trouble thould stay within one PSD window and things should in any case be ok after terminating and reopening that PSD window again.

I've managed to get the ORA-12592 now also with PSD called with DebugSQL command line parameter. I will sent you the resulting Debug.txt file, so you can analyze why it ends up CPU spinning there, forcing you to kill PSD altogether.

 

[T-Gergely]

You're right (unless the bug was in oci.dll -- but that's not the case, generally). Eg. I keep getting ORA-03113 (due to bug 14006273), and PSD hangs the same way.

 

[Marco Kalter]

We'll check it out.