文章目录

• 问题描叙
• Solution
• • 设置SMTP_OUT_SERVER 参数
  • 创建ACLS
  • 验证

    问题描叙

    记录一下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.