oracle12c - ORA-04024: 在尝试互斥锁定游标时检测到自死锁而没有问题的 SQL 计划基线

Question

我继承了一项任务，即在 Oracle 12.1.0.2 数据库上应用包罗万象的统一审计策略。对可审计对象、过去和未来的所有操作都需要进行审计。

按照 Oracle 文档，我想出了以下脚本，它运行良好（经过大量试验和错误）并且没有错误：

--create initial audit policy and set it to audit the first object in your list of auditable objects

create audit policy Audit_All actions all on sys.icol$;

--get a list of what you need to audit
define object_type_pattern = '(Directory|java Source|java Class|java Resource|Library|MATERIALIZED VIEW|MINING MODEL|PACKAGE|FUNCTION|PROCEDURE|SEQUENCE|TABLE|VIEW|type)';

-- alter the audit policy to audit all the rest of your auditable objects

declare
  v_cmnd varchar2(1000);
  no_such_object EXCEPTION;
  iot_overflow_table EXCEPTION;
  it_are_external_table EXCEPTION;
  invalid_audit_option EXCEPTION;
  pragma exception_init(no_such_object, -942);
  pragma exception_init(iot_overflow_table, -25191);
  pragma exception_init(it_are_external_table, -30657);
  pragma exception_init(invalid_audit_option, -46359);
begin
  for c in (select owner, object_name from dba_objects where regexp_instr(object_type, '&object_type_pattern') != 0)
    LOOP
        begin
            v_cmnd := 'alter audit policy Audit_All add actions all on ' || c.owner || '.' || '"'|| c.object_name ||'"';
            dbms_output.put_line(v_cmnd);
            execute immediate v_cmnd;
            EXCEPTION 
            WHEN no_such_object then dbms_output.put_line( 'Object does not exist, will not run ' || v_cmnd);
            WHEN iot_overflow_table then dbms_output.put_line( 'iot table issue faced, will not run ' || v_cmnd);
            WHEN it_are_external_table then dbms_output.put_line( 'This is an externally organized table, will not run ' || v_cmnd);
            WHEN invalid_audit_option then dbms_output.put_line( 'Invalid audit option for this object, will not run ' || v_cmnd);
        END;
    end loop;
end;
/

--alter the policy to add auditing on all system actions:

alter audit policy Audit_All add actions all;

--enable your policy:

audit policy Audit_All;

当我运行最后一个查询以启用审计策略时，我得到了这个：

SYS@orcl>audit policy Audit_All ;
audit policy Audit_All 
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00604: error occurred at recursive SQL level 3
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07E9E1BB0
ORA-00604: error occurred at recursive SQL level 3
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07E9E1BB0
ORA-00604: error occurred at recursive SQL level 3
ORA-04024: self-deadlock detected while trying to mutex pin cursor 0x07E917A40
Process ID: 2667
Session ID: 48 Serial number: 37985

我用谷歌搜索了这个错误代码，发现一个 Oracle 文档说这是由未发布的错误 21196809 - ORA-04024：在尝试 MUTEX PIN CURSOR 时检测到自死锁引起的。他们的解决方案是升级到 Oracle 12.2，这不是我能做的。

给出的另一个解决方案是删除上述递归 SQL 语句的 SQL 计划基线的解决方法。我跑了

SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED FROM DBA_SQL_PLAN_BASELINES WHERE  SQL_TEXT LIKE 'audit policy Imperva_All';

它空无一物地回来了。我不知道要删除什么。在我重新启动整个机器之前，数据库无法运行。

问题是可重现的。警报日志有点太长，无法放在这里，所以放在这里

标签： oracle12caudit-trail