We’ve spent a bit of time trying to figure it out recently, so putting it out there for future Google search :)

GCP network is using a 10 minute connection tracking and drops the connection deemed ‘idle’ once they exceed this limit. In human terms it means that if you’re sending an Oracle query that exceeds this limit, the open connection will be closed. This, in conjuction with a restrictive enough firewall configuration, would lead to the following situation:

• your client (say TM1) sends a query to Oracle server on whatever port (let’s assume 1521) from a dynamic port allocated by oracle driver (i.e. 50003), so we end up opening a tm1_server:50003 -> oracle_server:1521 connection. You’d obviously want an open egress rule for 1521 for TM1 server.
• if finishes within 10 minutes, there’s not problem as the results are send back within the same connection.
• if query time exceeds 10 minuts, connection is dropped and once the query result are ready on oracle side, it’ll try to open oracle_server:1521 -> tm1_server:50003, which then is denied unless you allow ingress connection for TM1_server on port 50003. This port is dynamic, so you’d need a wide open ingress range and that is insecure.
• the kicker is that from the application point of view it looks quite peculiar, TM1 sends a SQL query and waits for a response foreever (if firewall ingress isn’t open), so you’d blame slow Oracle server, but from Oracle point of view it got a query, processed it and sent the results in a reasonable timeframe. Gotta love network issues, they are the worst to trace on application level.

So the proper way to fix it is to:

• configure KeepAlive for Oracle by setting ENABLE=BROKEN in tnsnames
• configure keepalives on operating system level as well

And back to our regular TM1 / PA programming: in case you’ve got TM1 / Planning Analytics in GCP as well, most of it’s clients wouldn’t suffer the same fate, with the notable exception of tm1runti

TM1RunTI as far I can see would happily wait for a callback from the TM1 server that TI has finished and the connection would be dropped if takes over 10 minutes of inactivity. You’d need to open dynamic ports on the server running runti for ingress so that the callback from TM1 of “we’re all done here” can establish a new connection and tell the runti the execution result.