ORA-24247处理过程

问题描叙

记录一下ORA-24247引发job执行失败的处理过程：

select sjrd.log_id,sjrd.log_date,sj.owner,sj.job_name,sj.job_subname,sjrd.status from dba_scheduler_job_run_details sjrd
join dba_scheduler_jobs sj
on sjrd.owner=sj.owner and sjrd.job_name=sj.job_name
where sjrd.status<>'SUCCEEDED'
order by log_date desc

输出：

    LOG_ID LOG_DATE                                                                    OWNER      JOB_NAME     JOB_SUBNAM STATUS
---------- --------------------------------------------------------------------------- ---------- ------------ ---------- ----------
    716776 17-MAY-24 03.04.04.980417 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    716700 16-MAY-24 03.04.03.233071 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    716624 15-MAY-24 03.04.02.564900 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    716548 14-MAY-24 03.04.02.866003 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    716348 12-MAY-24 03.04.03.004062 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    716223 11-MAY-24 03.04.03.778856 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    716071 09-MAY-24 03.04.03.256950 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    715990 08-MAY-24 03.04.06.599765 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED
    715914 07-MAY-24 03.04.07.171701 AM +07:00                                         IPCASICS   IT_INVENTORY            FAILED 

查看addition_info信息:
addition_info显示错误为ORA-24247

select sjrd.additional_info from dba_scheduler_job_run_details sjrd
join dba_scheduler_jobs sj
on sjrd.owner=sj.owner and sjrd.job_name=sj.job_name
where sjrd.log_id=716776 and sjrd.status<>'SUCCEEDED'
order by log_date desc

输出：

ADDITIONAL_INFO
-------------------------------------------------
ORA-24247: 存取控制清單 (ACL) 拒絕網路存取
ORA-06512: 在 "SYS.UTL_TCP", line 17
ORA-06512: 在 "SYS.UTL_TCP", line 267
ORA-06512: 在 "SYS.UTL_SMTP", line 161
ORA-06512: 在 "SYS.UTL_SMTP", line 197
ORA-06512: 在 "IPCASICS.PKG_MAIL", line 240
ORA-06512: 在 "IPCASICS.PKG_MAIL", line 118
ORA-06512: 在 "IPCASICS.PKG_MAIL", line 333
ORA-06512: 在 "IPCASICS.EMAIL", line 30
ORA-06512: 在 line 9
ORA-24247: 存取控制清單 (ACL) 拒絕網路存取

手动执行IPCASICS.EMAIL产生的错误讯息跟addition_info记录一致：

exec ipcasics.email('SYS.ADMIN@YUYUN.COM','BOB@YUYUN.COM','TEST','THIS A TEST')
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 267
ORA-06512: at "SYS.UTL_SMTP", line 161
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at "IPCASICS.PKG_MAIL", line 240
ORA-06512: at "IPCASICS.PKG_MAIL", line 118
ORA-06512: at "IPCASICS.PKG_MAIL", line 333
ORA-06512: at "IPCASICS.EMAIL", line 30
ORA-06512: at line 1

Solution

从 11g 开始，要使用 UTL_MAIL 或 UTL_SMTP 发送邮件，需要在数据库中创建 ACL。
执行下面的查询，host部风为mail.yuyun.com,实际公司mail host已经变更为zsys.yuyun.com,看来问题出在这里

SELECT na.aclid,na.acl,nap.principal,nap.privilege,nap.is_grant,na.host FROM dba_network_acl_privileges nap
JOIN dba_network_acls na
ON nap.aclid=na.aclid
ACLID                            ACL                            PRINCIPAL                      PRIVILEGE               IS_GRANT             HOST
-------------------------------- ------------------------------ ------------------------------ ----------------------- -------------------- ------------------------------
77DAF452E0DF08C9E050007F01005A84 /sys/acls/UTL_SMTP.xml         IPCASICS                       connect                 true                 mail.yuyun.com

设置SMTP_OUT_SERVER 参数

SQL> alter system set smtp_out_server='zsys.yuyum.com' scope=both;
System altered.

创建ACLS

--- creating ACL as below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'IPCASICS', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','IPCASICS',TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','IPCASICS',TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml','ZSYS.YUYUN.COM',25);
commit;

验证

查看权限已经成功授予

SELECT na.aclid,na.acl,nap.principal,nap.privilege,nap.is_grant,na.host FROM dba_network_acl_privileges nap
JOIN dba_network_acls na
ON nap.aclid=na.aclid
ACLID                            ACL                            PRINCIPAL                      PRIVILEGE               IS_GRANT             HOST
-------------------------------- ------------------------------ ------------------------------ ----------------------- -------------------- ------------------------------
77DAF452E0DF08C9E050007F01005A84 /sys/acls/UTL_SMTP.xml         IPCASICS                       connect                 true                 mail.yuyun.com
18A4B8E845E61416E0634A0113AC8DBC /sys/acls/send_mail.xml        IPCASICS                       connect                 true                 zsys.yuyun.com
18A4B8E845E61416E0634A0113AC8DBC /sys/acls/send_mail.xml        IPCASICS                       resolve                 true                 zsys.yuyun.com

重新手动执行email发送成功

SQL> exec ipcasics.email('SYS.ADMIN@YUYUN.COM','BOB@YUYUN.COM','TEST','THIS A TEST');
PL/SQL procedure successfully completed.