Skip to Main Content
  • Questions

  • Connection lost with ORA-01335 with ODA server

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Khaled.

Asked: May 29, 2025 - 5:40 pm UTC

Last updated: June 04, 2025 - 6:58 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Hello,

We have migration project form Oracle database 11g in Exadata server, to 19c in ODA server, when we tested the query in the new server with client tool such as Sql developper, Sqlplus and Sqlcl, the connection was lost after 1 hour of inactivity, the only difference between Exadata and ODA is in the network architecture, for the traceroute Exadata, we take switch A, but for ODA we, take firstly Firewall, after switch b to come to ODA, we increase timeout in Firewall for the port 1521 to 24h but the same issue. When we try to connect ton ODA directly, there is no timeout.

I added this ligne: SQLNET.EXPIRE_TIME = 10, in my sqlnet file, and seems work perfectly, my question is it the right solution, or there is another solution more efficnecy. If it is the only solution, I need to do others configurations for the connection doesn't use sqlnet, such as SSIS that use ODBC, and others that use JDBC.

Thanks in advance

and Connor said...

The fact that connecting directly encounters no timeout suggests that something in your network layer is causing this issue, and is probably worth investigating. For example, it might be that the firewall is blocking some packets that are being used to confirm that the connection is still alive.

To my knowledge, there are no real downsides to setting SQLNET.EXPIRE_TIME = 10, and ODBC sits on top of an Oracle client anyway. For JDBC, typically you'd be using connection pools which will manage their own connection timeouts etc.

Note: Franck Pachot did a nice post on expiry time and some other considerations that you might want to look at

https://www.dbi-services.com/blog/sqlnet-expire_time-and-enablebroken/

Rating

  (3 ratings)

Comments

Thanks Connor for the answer

Khaled, June 03, 2025 - 12:26 pm UTC

Hi,

Thanks Connor, yesterday, we did a lot of tests, because our ODA server is connected to core switch, and core switch to firewall, our tests are used two laptop, one with the same windows copie as us, and the second laptop is new one without antivirus, without security policy, so these laptop are linked to core switch directly without passed with firewall, the first laptop is deconnected after 1H, but the second one still connected, so I think the cause is Defender or security policy, we will do others tests like use Linux laptop, link fiest and second laptop to ODA directly with small switch to identify who block the session, if ODA server or Core switch.

For Expire_time, it work perfectly for application and clients that use sqlnet, but our SSIS use OLE DB, and in OLE DB, no parametres to add connection tiemout, I tryed to do it in task flow, but still we have error, my question how we can configurate SSIS to use keepalive like sqlnet, is SSIS we use Oracle OLE DB driver:

Severity Code Description Project File Line Suppression State
Error An error has occurred while connecting DB_ODA.ACHALBI: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040E21 Description: "Une opération OLE-DB en plusieurs étapes a généré des erreurs. Vérifiez chaque valeur d'état OLE-DB disponible. Aucun travail n'a été effectué.".
. CAP_ARRET_VOY_STOP_VLV 0


Connor McDonald
June 04, 2025 - 6:58 am UTC

If you're using OLE then most likely you're using ODAC XCOPY to do this? ie

https://www.oracle.com/database/technologies/net-downloads.html

If so, then you still have network/admin folder that you can edit sqlnet.ora in

Thanks Connor for the answer

Khaled, June 03, 2025 - 12:57 pm UTC

Hi,

We did another test, is to take the same subnet as Exadata, it work perfectly, because in our Exadata, no timeout, and Exadata don't pass by Firewall, and core switch, also when tryed to connect to Exadata using firewall and core switch, we have timeout, so the cause is core switch or firewall, but in firewall we did session ttl timeout=24h for port 1521, but still we have timeout, we don't use NAT in firewall, do you have others suggestions like session helper,,,,?

Thanks in advance,
Connor McDonald
June 04, 2025 - 6:53 am UTC

A sqlnet trace might reveal something, but to be honest, that sounds its a lower level down in the network stack.

Thanks Connor for the answers

Khaled, June 04, 2025 - 11:06 am UTC

Hi,

What do you think about parameters session helper in the firewall fortifate, we find this option activated, desactivate it may be fix the issue?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.