Monday, January 14, 2019

Network ACL error after 12c database upgrade

begin
    dbms_network_acl_admin.append_host_ace (
    host=>'*',
    lower_port => 1,
    upper_port => 65535,
         ace=> sys.xs$ace_type( privilege_list=>sys.XS$NAME_LIST('JDWP') ,
                                principal_name=>'HMA_TM_PROD',
                                principal_type=>sys.XS_ACL.PTYPE_DB));
end;
/


BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => '*',
    lower_port => 1,
    upper_port => 65535,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'HMA_TM_PROD',
                              principal_type => xs_acl.ptype_db));
END;
/

*************************************
send test email from database

Create proc

CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;
/

Add privs


BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'send_mailx.xml',
    description  => 'Purpose of the acl is to send mail',
    principal    => 'sravan',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

   DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'send_mailx.xml',
    host        => '*',
    lower_port  => 1,
    upper_port  => 9999);

  COMMIT;
END;
/


Send
BEGIN
  send_mail(p_to        => 'xxx10563@xxx.local',
            p_from      => 'xxx-dba-alerts@xxx.local',
            p_message   => 'This is a test message.',
            p_smtp_host => '10.120.xx.xx');
END;
/

Featured Post

Apply Patch 22191577 latest GI PSU to RAC and DB homes using Opatch auto or manual steps

Patch 22191577: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.4.160119 (JAN2016) Unzip the patch 22191577 Unzip latest Opatch Version in or...