使用Oracle将事件放入Windows事件日志 [英] Place Event into Windows Event Log with Oracle

问题描述

这是对此问题的后续行动

我在Oracle从DBMS_SCHEDULER执行eventcreate Windows命令时遇到麻烦.

作为一种解决方法，我创建了一个基本的C#应用​​程序来执行相同的eventcreate功能.它可以在基本水平上运行，但我面临一些障碍.

这是程序. (我没有在这个问题中标记C#，因为这个问题不是关于C#的.我只是将其作为信息提供.)

using System;
using System.Diagnostics;

class myEventCreateClass
{
    public static void Main(String[] args)
    {   
        using(EventLog eventLog = new EventLog("Application"))
        {
            eventLog.Source = "MySource";
            eventLog.WriteEntry(args[0], EventLogEntryType.Warning, 218);
        }
    }
}

我将DBMS_SCHEDULER作业修改为此:

BEGIN
sys.dbms_scheduler.create_job( 
   job_name            => 'SYS.TESTJOB',
   job_type            => 'EXECUTABLE',
   job_action          => 'C:\myEventCreate.exe',
   job_class           => 'DEFAULT_JOB_CLASS',
   number_of_arguments => 1,
   auto_drop           => FALSE,
   enabled             => FALSE);
sys.dbms_scheduler.set_job_argument_value('SYS.TESTJOB', 1, 'testing123');
sys.dbms_scheduler.enable('SYS.TESTJOB');
END;

当我在SYS模式下手动运行此作业时，它成功地将一个事件放入Windows事件日志中，内容为:

testing123

这就是我成功的终点...

---

如果我在不同的架构下创建相同的作业(例如，将SYS.TESTJOB的所有实例更改为MYSCHEMA.TESTJOB)，它将在该架构中创建该作业，但是当我尝试运行该作业(从任何架构)时，我得到了以下一长串错误:

ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 6a
ORA-27303: additional information: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

当我尝试从MYSCHEMA运行SYS.TESTJOB时，它告诉我该作业不存在:

ORA-27476: "SYS.TESTJOB" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

我如何通过SYS以外的模式来完成这项工作?

---

另一个问题(可能是更大的问题):我正在尝试从触发器内部运行此作业.

根据此问题，更改了DBMS_SCHEDULER作业的设置(在我的情况下，每次尝试在运行该作业之前，我都试图更改作业参数)会导致Oracle中隐式的COMMIT，这在触发器中是不允许的.

对我来说，Oracle甚至将它们标记为参数"似乎是一种误导，因为参数的值在作业内部是固定的，而更改参数意味着更改作业本身.

无论如何，此问题中可接受的答案是说要使用DBMS_JOB，因为它不会隐式地COMMIT，但是我找不到使用DBMS_JOB来运行外部.exe文件的方法.

因此，有可能以某种方式修改此作业以允许动态作业参数吗?

---

我也对其他解决方案持开放态度，但据我所读，DBMS_SCHEDULER似乎是实现此目的的最佳方法.

---

根据要求，以下是我要完成的工作的上下文:

在我公司，我们对其进行了设置，如果将某个条目放置在Windows事件日志中的某个特定来源下(例如，在这种情况下，如提供的C#应用​​程序中所示的mySource)，则文本消息将包含用户日志消息的内容会自动发送给我本人和其他一些管理员的手机.

这非常有用，因为它可以立即通知我们发生了一些重要事件，并且我们可以准确地控制我们要包括的事件以及要通知的有关这些事件的具体信息.

以下是一些我们当前通过短信收到通知的示例:

1. 停止或启动我们的任何自定义应用程序(如果没有崩溃，谁停止/启动了它).
2. 当我们的任何自定义应用程序进入或退出看门狗控制(以及执行此操作的人)时.
3. 当某些已知问题"出现或即将发生时，我们尚未完全解决.这使我们能够领先于游戏"，这样我们就可以主动应对它，而不必等待有人将其告知我们.

我想将此功能扩展到Oracle数据库中的某些事件(这就是为什么我试图基于Oracle中的触发器将事件放入事件日志中的原因.)

到目前为止，我想着一些要通过短信通知的事情，所有这些都可以在触发器内确定:

1. 当不在某个批准的"用户列表中的任何人(这是我们的管理员以及与Oracle连接的自定义应用程序)连接到我们的Oracle数据库时.这可以通过登录触发器来完成. (实际上，由于登录触发器由SYS架构调用，因此我已经可以使用该架构了，因此我不会遇到其他架构无法运行该作业的问题.但是...因为我仍然无法更改任何参数，我目前能做的最好只是说"Someone" not approved logged into Oracle database....如果我可以将用户名传递给Windows事件日志，它将很有用.)

2. 当除自定义应用程序以外的任何其他内容更改Oracle数据库中的数据时. (我们的自定义应用程序可处理所有插入/更新/删除等操作.只有在极少数情况下，我们才需要手动修改某些内容.我们希望在 任何人 时收到通知[包括我自己或其他管理员在内的人]都会修改数据库的任何内容.)这可以通过对每个表进行更新/插入/删除触发器来实现.

解决方案

它在 SYS 下工作的原因是它是特殊特权帐户.您需要创建一个新凭据并将其映射到作业

解决方案是使用 DBMS_SCHEDULER.CREATE_CREDENTIAL 以及具有足够特权的OS帐户，并将此新凭据分配给您的工作.

说实话，我不知道触发问题.

编辑-使用Oracle的子事务工具的解决方案

在更新OP并对评论做出反应之后:

基于工作流程，我认为最好使用内部Oracle通知进行响应审核.我认为尝试通过外部应用程序将自己入侵Windows事件日志会带来另一种不必要的复杂性.

我将在数据库中创建一个表，在该表中存储所有事件，并在该表的顶部创建一个带有通知(SMS，邮件等)的作业，如果对日志表进行任何更改，该作业将运行.

为了在发生错误时使用触发器，您应该使用 PRAGMA autonomous_transaction 从您的主作用域(允许您进行子交易).这将使您可以提交您可能拥有的任何DML，但对其余部分进行回滚.

This is a follow-up to this question.

I was having trouble with Oracle performing the eventcreate Windows command from DBMS_SCHEDULER.

As a workaround, I instead created a basic C# application to perform the same eventcreate function. It works on a basic level but I'm facing a few roadblocks.

Here is the program. (I'm not tagging C# in this question because the question is not about C#. I am just providing this as information only.)

using System;
using System.Diagnostics;

class myEventCreateClass
{
    public static void Main(String[] args)
    {   
        using(EventLog eventLog = new EventLog("Application"))
        {
            eventLog.Source = "MySource";
            eventLog.WriteEntry(args[0], EventLogEntryType.Warning, 218);
        }
    }
}

I modified the DBMS_SCHEDULER job to this:

BEGIN
sys.dbms_scheduler.create_job( 
   job_name            => 'SYS.TESTJOB',
   job_type            => 'EXECUTABLE',
   job_action          => 'C:\myEventCreate.exe',
   job_class           => 'DEFAULT_JOB_CLASS',
   number_of_arguments => 1,
   auto_drop           => FALSE,
   enabled             => FALSE);
sys.dbms_scheduler.set_job_argument_value('SYS.TESTJOB', 1, 'testing123');
sys.dbms_scheduler.enable('SYS.TESTJOB');
END;

When I run this job manually under the SYS schema, it successfully places an event into the Windows event log that says:

testing123

This is where my success ends...

---

If I create the same job under a different schema (e.g. change all instances of SYS.TESTJOB to MYSCHEMA.TESTJOB), it creates the job in that schema but when I attmept to run the job (from any schema) I get the following long list of errors:

ORA-27370: job slave failed to launch a job of type EXECUTABLE
ORA-27300: OS system dependent operation:accessing job scheduler service failed with status: 2
ORA-27301: OS failure message: The system cannot find the file specified.
ORA-27302: failure occurred at: sjsec 6a
ORA-27303: additional information: The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

And when I try to run SYS.TESTJOB from MYSCHEMA, it tells me the job doesn't exist:

ORA-27476: "SYS.TESTJOB" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1

How can I get this job working from a schema other than SYS?

---

One more problem (probably the bigger issue): I am trying to run this job from inside a trigger.

According to this question, changing the settings of a DBMS_SCHEDULER job (in my case, I'm attempting to change the job arguments each time before I run the job) causes an implicit COMMIT in Oracle, which is not allowed in triggers.

To me it seems misleading for Oracle to even label these as "arguments", because the values of the arguments are fixed inside the job, and changing the arguments means changing the job itself.

Anyway, the accepted answer in this question says to use DBMS_JOB since this does not implicitly COMMIT, but I can't find a way to use DBMS_JOB to run an external .exe file.

Therefore, is it possible to modify this job somehow to allow dynamic job arguments?

---

I'm also open to other solutions, but from what I have read, DBMS_SCHEDULER seems to be the best way to accomplish this.

---

As requested, here is some context for what I am trying to accomplish:

At my company, we have it set up such that if an entry is placed into the Windows event log under a certain source (e.g. in this case, mySource as shown in the provided C# application), a text message containing the content of the user log message is automatically sent the cell phones of myself and a few other admins.

This is extremely useful as it gives us an immediate notification that some event of importance happened, and we can control exactly which events we want to include and what specific information about these events we want to be notified of.

Here are some examples of what we currently get notified about via text message:

1. The stopping or starting of any of our custom applications (and who stopped/started it if it didn't crash).
2. When any of our custom applications are taken into or out of watchdog control (and who did this).
3. When certain "known issues" arise or are about to arise that we haven't fully fixed yet. This allows us to get "ahead of the game" so that we can deal with it proactively rather than waiting for someone to tell us about it.

I want to extend this functionality to some events in our Oracle database (which is why I am trying to place an event into the event log based on a trigger in Oracle).

Here are some things I have in mind as of now that we want to be notified of via text message, all of which can be determined inside a trigger:

1. When anyone not in a certain "approved" list of users (which would be our admins plus the custom applications with connections to Oracle) connects to our Oracle database. This can be accomplished with a logon trigger. (Actually, I already have this one working since the logon triggers are called by the SYS schema, so I'm not having issues with other schemas not being able to run the job. But... since I still can't change any arguments, the best I can currently do is just say "Someone" not approved logged into Oracle database.... It would be alot more useful if I could pass the username to the Windows event log.)

2. When anything besides our custom applications changes data in our Oracle database. (Our custom applications handle all of the inserts/updates/deletes etc. Only in very rare cases would we need to manually modify something. We want to be notified when anyone [including myself or other admins] modifies anything the database.) This can be accomplished with an update/insert/delete trigger for each table.

解决方案

The reason why it is working under SYS is that it is special privileged account. You need to create a new credential and map it to the job

The solution would be create a credential with DBMS_SCHEDULER.CREATE_CREDENTIAL together with OS account that has enough privileges and assign this new credential to your job.

For the trigger issue to be honest I don't know yet.

Edit - solution based using Oracle's subtransaction facility

After OP update and reaction to comments:

Based on the workflow I think it is better to use internal Oracle's notification to do the responsive audit. I think trying to hack yourself f into Windows event log via external application is bringing another unnecessary layer of complexity.

I would create a table within DB where I would store all the events and on top of that table I would create a Job with notifications (SMS, mail, etc) which would be run if any change to the log table occurs.

In order to use triggers when an error occurs you should use PRAGMA autonomous_transaction from your main scope (allows you to do a subtransaction). This will allow you to commit any DML you may have, but do a rollback the rest.

这篇关于使用Oracle将事件放入Windows事件日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！