Most likely, the database server has disconnected the database connection and now ->connect_cached tries to reuse the connection but times out. Consider speaking with your database administrator and network administrator after what time the database and the network will drop an existing connection.

Another problem can be if you use the fork system call. Ideally, DBD::Oracle (and the underlying database library) would detect that and reestablish the connection, but maybe they don't and try to reuse the connection of the parent.

Thank you Corion for the revert.
Here lexical scoped variable $dbh has been used (not a global one), so here it should have been created a new connection, isn't it? but somehow it seems to act as a global variable.
Is there any downside if I retain the "connect" itself instead of "connect_cached", at least for this problematic part of the code.

Please suggest.
Regards,

The point of the ->connect_cached method is to keep a global cache of the connection.

In an ideal world, ->connect_cached is a drop-in replacement for ->connect, but reusing an old connection.

The downside of using ->connect is that maybe you are a bit slower because you rebuild the network connection. But that is better than having a long timeout, in your case.