Hello,
We deployed VisualCron 8.3.2 on a test server and did setup multiple SQL Tasks starting Oracle packages. Everything was running fine.
Now we deployed VisualCron 8.3.4 on a production server and imported everything we had on the test server. Everything is still fine except for 2 nightly tasks that gets stuck in running state.

Those two tasks are usually taking around 2 hours to complete. When I connect in the morning, it is still running. I look in the database and VisualCron is not connected to it anymore. I then force the job to stop and we have the same problem the next night.

We have some other tasks doing almost the same thing that are running fine.

I just ran the job againg during the day today and start to monitor it. I can see that the job connects to the DB, and it does everything it has to do. Then, I see on the DB server that the VisualCron database connection ends but the task is still running in VisualCron.


I'm wondering if it could be a bug with version 8.3.4?

Can you try with a more complex procedure that just returns result of COUNT or similar?

---

Hello Henrik,
I think you meant less complex. I tried a count and it works fine, after a couple of seconds, it triggers the next task.
Thanks!

Hello Henrik,
Would you like me to do more tests on that?
We have jobs that are not working and I will have to go back to an older version soon.

Yes, sorry, less complex. What if you create a stored procedure with a 2 hours sleep in it. To determine if it is related to the long time it runs?

---

OK, we try it tonight and we'll see the result tomorrow morning.
Thanks!

By the way, could you tell us what kind of connection string you use in Oracle. You can replace host, user and pass? Also, which version of oracle are you using? Can you also tell us if you can refresh stored procedures correctly in the interface?

---

I can reproduce the problem with the 2 hours sleep procedure. I scheduled it to run every 4 hours and it's stuck running since 4 PM yesterday (The firts time it ran).
I also tried to run it with a 2 minute sleep and it completed successfully.

The Oracle Server version is 11.2.0.3.
The Oracle client version was 11.2.0.3 on our VisualCron TEST server and 12.1.0.2 on our PROD server. To make sure the problem on the PROD server is not related to the Oracle client, I uninstalled it and installed version 11.2.0.3 and we still have the same problem.

I'm not sure what you mean here: "Can you also tell us if you can refresh stored procedures correctly in the interface?"

Here is the connection String:

Data Source=BICJTEST;User Id=HIDDEN;Password=**********;Unicode=true;

This is what we have in tnsnames.ora on the VisualCron server:
BICJTEST=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=HIDDEN)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=BICJTEST)
)
)

There is a Refresh button next to Stored procedures list. Does it properly work and list the Stored procedures?

---

Hello Henrik,

On friday I did 2 tests :
I executed a procedure with a 55 minutes sleep and it succeed.
I executed a procedure with a 65 minutes sleep and it is still running.

I tried on two different servers and got the same result. It looks like something happens after 1 hours of execution.

About the Refresh button, I never tried it before because instead of using stored procedures, we use Oracle packages that we run using the SQL / Text tab.
As a test, I created a stored procedure and when I go to SQL / Stored procedure tab, the "Name of Stored Procedure" drop down is empty and the refresh button does nothing. I have to input the full procedure name and then I see I have to input the parameters.

I just tried the 65 minutes task on 3 Oracle instances and it succeed on one instance, it kept running on the other two instances.

I just tried the 65 minutes task on 3 Oracle instances and it succeed on one instance, it kept running on the other two instances.

What is the difference on the instances? Do you use different connection strings?

---

The two not working are different instances on the same Windows server. Oracle version 11.2.0.3
The one working is on a Solaris server. Oracle version 11.2.0.4
The connection strings are written the same way.

Are you sure all are using Oracle->Native->Directmode?

---

We are using Oracle->Native-> ?
The connection type in unchecked. If I check it, I see 5 choices in the list including "Standard 2 - direct mode (no Oracle Client)". Is it the one I should select?
Thanks!

I tried it with "Standard 2 - direct mode (no Oracle Client)" and it is still running after 65 minutes. I see the user isn't connected in the database anymore.
Here is the connection string
Provider=Oracle;data source=SERVERNAME;user=CJMCQ_JOBS;password=**********;direct=true;Port=1521;SID=BICJTEST;Service Name=BICJTEST;

Hello Henrik,

Yesterday, I installed version 8.3.4 on our TEST server and the jobs ran properly.
So the problem is not with version 8.3.4, it is not with the Oracle server, it must be something wrong with the Oracle client.

Thanks for the feedback!

---

For the benefit of other users that could face the same issue.

We discovered that some kind of firewall is killing the connection after one hour because there is no activity (while the Oracle server process a very long query).
To avoid this, on the Oracle server, we added a parameter in the sqlnet.ora file:

SQLNET.EXPIRE_TIME=10

This parameter indicates to Oracle to look for death connections every 10 minutes. That also has the effect of keeping the network connection alive so the firewall won't kill the connection after one hour.

:o)

For the benefit of other users that could face the same issue.

We discovered that some kind of firewall is killing the connection after one hour because there is no activity (while the Oracle server process a very long query).
To avoid this, on the Oracle server, we added a parameter in the sqlnet.ora file:

SQLNET.EXPIRE_TIME=10

This parameter indicates to Oracle to look for death connections every 10 minutes. That also has the effect of keeping the network connection alive so the firewall won't kill the connection after one hour.

:o)

Thanks for the feedback!

---

I am facing the same issue today with MS SQL server. We upgraded our Visualcron server and clients today to 9.6.5.
My stored procedure simply brings back a few rows of data and runs in under 3 seconds when I run directly on the SQL server. Historically, that stored procedure has worked fine on VisualCron but today after the upgrade, it kept running in a loop. I deleted that step and recreated the DAtabase > SQL task with the same issue.

Any assistance is apprecaited.

I am facing the same issue today with MS SQL server. We upgraded our Visualcron server and clients today to 9.6.5.
My stored procedure simply brings back a few rows of data and runs in under 3 seconds when I run directly on the SQL server. Historically, that stored procedure has worked fine on VisualCron but today after the upgrade, it kept running in a loop. I deleted that step and recreated the DAtabase > SQL task with the same issue.

Any assistance is apprecaited.

Hi Ankeet, please create an account with your official work mail. It makes it easier to identify you and help you.

---