This discussion is archived
11 Replies Latest reply: Oct 14, 2013 1:19 AM by a73f2c5c-407f-44fa-a70d-b744a2bf5128 RSS

Executing a job using DBMS_SCHEDULER inside a trigger

a73f2c5c-407f-44fa-a70d-b744a2bf5128 Newbie
Currently Being Moderated

Hi All,

 

I am fairly new to Oracle, so excuse me if I am not very clear with the explanation of my problem.

 

I would like to execute an external batch file in Windows using DBMS_SCHEDULER in an Oracle database (10g or a higher version). I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle. The problem is that I can't create triggers on SYS objects, but DBMS_SCHEDULER is owned by  (and as far as I understand, supposed to be used with) SYS user. Could someone please guide me exactly on what permissions need to be granted to which user? Also, can this be done on creating a new user and not using the default SYS and SYSTEM users?

 

Any help would be much appreciated!

 

Thanks

  • 1. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    sb92075 Guru
    Currently Being Moderated

    >I intend this to be done through a trigger, which would be called on encountering specific errors in Oracle.

    Intend all you want, but a trigger does not exist/fire when error gets thrown.

     

    You need to change the application code to include  EXCEPTION  handler which would invoke DBMS_SCHEDULER

  • 2. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    a73f2c5c-407f-44fa-a70d-b744a2bf5128 Newbie
    Currently Being Moderated

    I have been able to use triggers to send email notifications on encountering this error, so that shouldn't be a problem. The trigger works AFTER SERVERERROR ON DATABASE.

     

    I don't have the source code for the application which causes this problem, which is why I would like to handle this situation completely independent of the application. Is this even possible/feasible?

  • 3. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    sb92075 Guru
    Currently Being Moderated

    a73f2c5c-407f-44fa-a70d-b744a2bf5128 wrote:

     

    I have been able to use triggers to send email notifications on encountering this error, so that shouldn't be a problem. The trigger works AFTER SERVERERROR ON DATABASE.

     

    I don't have the source code for the application which causes this problem, which is why I would like to handle this situation completely independent of the application. Is this even possible/feasible?

     

     

    exception within application code is DIFFERENT from SERVER ERROR which gets logged to the alert logfile.

  • 4. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    a73f2c5c-407f-44fa-a70d-b744a2bf5128 Newbie
    Currently Being Moderated

    Sorry - I meant that the application causes database errors, which I have been able to track. Thats not my concern right now

     

    Could you please advise on how to run DBMS_SCHEDULER jobs inside a trigger? What roles or permissions need to be given to which user in order for this to work?

  • 5. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    sb92075 Guru
    Currently Being Moderated

    a73f2c5c-407f-44fa-a70d-b744a2bf5128 wrote:

     

    Sorry - I meant that the application causes database errors, which I have been able to track. Thats not my concern right now

     

    Could you please advise on how to run DBMS_SCHEDULER jobs inside a trigger? What roles or permissions need to be given to which user in order for this to work?

     

    when all else fails, Read The Fine Manual

     

    http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_sched.htm#ARPLS72235

  • 6. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    rp0428 Guru
    Currently Being Moderated

    a73f2c5c-407f-44fa-a70d-b744a2bf5128 wrote:

     

    Sorry - I meant that the application causes database errors, which I have been able to track. Thats not my concern right now

     

    Could you please advise on how to run DBMS_SCHEDULER jobs inside a trigger? What roles or permissions need to be given to which user in order for this to work?

    No - because you should NOT be executing transactional code in a non-transactional trigger?

     

    Why do you think a trigger is the place to do this?

     

    A table trigger fires based on DML that is performed on the table. That firing has NOTHING to do with any 'database errors'.

     

    And if a user executes DML that causes a trigger to fire that user can then execute a ROLLBACK but the trigger will have already fired and executed its code. So you want this job to execute even if the transaction is rolled back? That seldom makes any sense.

     

    Instead of focusing on the solution (i.e. trigger) that you want to use why don't you tell us the PROBLEM you are trying to solve?

  • 7. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    a73f2c5c-407f-44fa-a70d-b744a2bf5128 Newbie
    Currently Being Moderated

    I'm sorry, I'll try to explain this better.

     

    We have an application, which picks up data from an Oracle 10g database, does certain calculations on it, and updates entries in the database based on these calculations. We don't have the source code for this application; we just have an executable which runs.

     

    Due to time constraints, we have no other option but to run this application simultaneously on multiple systems, especially on days when the volume of data to be processed is large. During such cases, we often see an error on one of the instances of the application, which reads - ORA-00060: deadlock detected while waiting for resource. When this happens, the application stops responding and freezes up, and we need a person to (manually) monitor all the systems running this application. So, whenever the person monitoring the application finds this error message on the application, he forces the application to shut down, waits for a while, and launches the application again. From what we have observed, there is no pattern followed by the application in terms of the data it processes - it randomly picks a set of data and starts processing it. We presume that a deadlock happens when another instance of this application running on another system chooses to process the very same set of data, causing a deadlock (is this reasoning correct?)

     

    I want to come up with a system for removing manual monitoring, completely.

     

    Now for the solution, the obvious idea was to detect the deadlock in Oracle and somehow kill the application instance which caused this issue. Moreover, this processing is done outside of business hours, so it is safe to assume that there are no other users using the database in any way, and that the application running on parallel systems is the only thing which is accessing the database at the time.

     

     

    I made the following setup -

    1. A table called DINFO which logs the timestamp and the host name whenever a deadlock happens. Please note that I intend to enable this trigger only when this processing application needs to run on simultaneous systems, and the trigger will remain disabled during normal operation.

    2. A trigger on ORA-00060, which logs data into DINFO, and then executes a windows batch file. The batch file is made to take the host name corresponding to the latest timestamp as the parameter. Based on this, it selects the credentials for the Admin account on the host, makes a remote call to the host and runs another batch file on it, which kills the application, waits and starts the application again.

     

    Here is the rough code that I used -

     

    CREATE OR REPLACE TRIGGER TR_DEADLOCK AFTER SERVERERROR ON DATABASE

    BEGIN

    ROLLBACK;

    IF ( IS_SERVERERROR(60) ) THEN

       INSERT INTO DINFO SELECT SYSDATE, SYS_CONTEXT('USERENV','HOST') FROM DUAL;

       COMMIT;

       <TRY TO CALL THE DBMS_SCHEDULER TO EXECUTE THE BATCH TO KILL APPLICATION INSTANCE BASED ON THE HOSTNAME JUST LOGGED>

    END IF;

    END;

     

     

     

    I hope this explanation is more coherent than my previous ones. Can someone suggest a better solution keeping in mind the constraints? I understand that this is a very poor approach to solving the problem we face, but desperate times call for desperate measures

  • 8. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    rp0428 Guru
    Currently Being Moderated

    Thanks for the updated explanation. That makes a lot more sense than the first one.

    From what we have observed, there is no pattern followed by the application in terms of the data it processes - it randomly picks a set of data and starts processing it. We presume that a deadlock happens when another instance of this application running on another system chooses to process the very same set of data, causing a deadlock (is this reasoning correct?)

    This is what I would focus on first.

     

    There has to be SOME pattern. At least in where the app can get data to begin with (which table, view, etc) and how it even distinguishes what data needs to be processed.

     

    Certainly you don't use some unknown app without knowing what it is doing to your data or what data it is manipulating. That just doesn't make sense.

     

    I would want to see if I couldn't solve this problem by adding a new layer between the app and the current data. This new layer would determine what data to let an instance of the app have access to and it could then prevent multiple instances from accessing the same data.

     

    That new access could be done in several ways. One way would be to create a new synonym that hides the actual data source.

    So the current query might look like this:

    SELECT * FROM myDataSource

    where 'myDataSource' is a table. By creating a synonym named 'myDataSource' you can make the source a different table, a view or even a pipelined procedure.

     

    A view or procedure could then use SYS_CONTEXT variables as part of the query or logic and those variables can contain info about the host/ip of the app instance and then use those to control what data that instance uses.

     

    It is better to prevent the problem from occuring than try to deal with it after the fact.

     

    Do you have ANY info about how the app determines what data source to use and what data in that source (or query) to actually manipulate?

  • 9. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    sb92075 Guru
    Currently Being Moderated

    >During such cases, we often see an error on one of the instances of the application, which reads - ORA-00060: deadlock detected while waiting for resource.

    EVERY ORA-00060 gets logged to the alert log & shows both SQL statements involved; which should make identification straight forward.

     

    the only real solution is to either fix the application or avoid running multiple copies against the object being contested

  • 10. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    Alvaro Pro
    Currently Being Moderated

    Just another suggestion, you can also do an errorstack on that error through events. It will generate a trace file with user, SQL and dump of the process that error.

     

    That can give you more information on the error, and hopefully help you find a pattern and fix the source of the problem.

  • 11. Re: Executing a job using DBMS_SCHEDULER inside a trigger
    a73f2c5c-407f-44fa-a70d-b744a2bf5128 Newbie
    Currently Being Moderated

    I am from IT and we don't know anything at all about the way this application is made - the vendor (obviously) is not very open/declarative about the way the application works. However, since we deploy their applications in our own production environments, we do have full access to the infrastructure, but thats about it.

     

    Anyhow, thanks a lot to everyone for the solutions! I will post again if I find alternative ways of going about this!

     

    Thanks again!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points