Showing posts with label acl error. Show all posts
Showing posts with label acl error. Show all posts

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...