Toad World® Forums

Automation task runs successfuly but not all the time


#1

I have a task that is scheduled to run every 30mn, there are times that it runs successfuly,289 records are exported but there are times that only few records, 20, 60 are exported. I know there is not problem with the sql statement or task because it runs successfully sometimes; please help me to figure it out why the task fails to export all the records in some instances. This particular schedule has been running for the last 3 days but the problem reocurres from time to time. When the task fails, toad gets open and keeps the following data. Please see attachment.

Exception Information.doc (106 KB)


#2

That looks like somekind of painting error for a different part of the program than automation. Do you have Toad shut down? Or are you using Toad at the same time?

For version 2.0 I have made many changes. Can you download the TDA 2.0 Beta and schedule this same automation script to run? In 2.0 I have added better exception handling and logging. You will need to set up the excpetion email and logging level. But this may help isloate the issue.

The lastest Beta is located here --> http://www.toadworld.com/Downloads/ToadforDataAnalysis/tabid/186/Default.aspx

Debbie


#3

Toad was not used at all when the automatic task run. But I noticed than when the task fails, Toad was opened and did not get closed so it stayed active displaying the error I sent in the attached file.

I will download the beta 2.0; Thanks for your reply.


#4

Hi,

I installed Beta 2.0 and I scheduled a sript to run automatically. As before, there are times the automation task runs without a problem, but there are other times, it failsThe attached file contains more details on the error.
Automation script error.doc (42.5 KB)


#5

The log shows that 20 rows were successfully exported from the first query. After that the connection is lost but I can’t tell why it is lost. See clip of log below.

Export Finished, click REX MFG Backlog.xls to view file.ORA-03113: end-of-file on communication channel

I have a few questions:1) 20 rows exported is not the correct number of rows. Is it always 289 rows?2) Are the 20 rows exported to REX MFG Baclog.xls correctly exported?3) Do you have any resource or policy restrictions on your logid?

ORA-03113: end-of-file on communication channel is a general connection error that simply means the connection was lost for some reason such as a timeout.

See link for other ideas.
http://www.dba-oracle.com/m_ora_03113_end_of_file_on_communications_channel.htm

Please try any/all of the following items.

  1. Go to Options/Database/Timeouts and change all values to 0. 0 means unlimited time.
  2. Do you have a different logid to use? If so please try.
  3. Can you use direct connect? This may not be possible if using OS Authentication.

Let me know if these helped.

Debbie


#6

20 rows is not correct. The excell file usually contains between 250 to 300 rows. I changed the time out to 0. Is there an option to run again the dame script in case the task was not completed?. Thanks


#7

Currently I do not have any code in place to do anything but ignore errors or stop and email.

I did forget to mention to turn on verbose logging as this may also lead to more info. To do this, select the default description image at the top of the script and change the logging level to Verbose.

Let’s see if the timeout and logging level will get the script running consistantly.


#8

Hi Debbie, I cannot locate the option to change the logging to verbose; on top of the script is an icon with the words Editor and script name.


#9

Does this image help?
LoggingLevel.png


#10

yes it did; Thanks Debbie. Unfortunatelly I still have the same problem. But for now, I stop the automation operation if the task takes more than 5 mn to get completed.
NOTE: before I had Toad for Oracle 8.0 and lost connection was hardly an issue. Now that I have oracle 9.6 and also purchased Toad data for analysis, all the time I have a problem pointing, lost connection. I noticed that if my automation tasks fails is because there is a problem with the connection; nothing wrong with the network connection, but it seems my toad versions are very sensitive;


#11

That’s a bummer. Perhaps this is worth looking into a bit further. I assume you still have your Toad 8.0 installed? Can you go back and confirm it doesn’t have the same issue? Then check what OCI client it is using. Is is the same as the other Toads? Also, what timeout and other options are set in Toad 8.0 that might be affecting this?

If it turns out that Toad 8.0 is also having the issue but perhaps not as often, then check to see if there has been any outside changes such as upgrade to OCI client, Oracle resources, User poilicy, virus applications, etc.