DBA Hub

📋Steps in this guide1/1

Configuring mail alerts via PLSQL – Troubleshot ORA-29278

in this blog i have explained you how to resolve ORA-29278

oracle configurationintermediate
by OracleDba
13 views
1

Overview

Configuring mail alerts via PLSQL – Troubleshot ORA-29278 The obselete utl_smtppackage was first introduced in Oracle 8i to give access to the SMTP protocol from PL/SQL. The package is dependent on the JServer option which can be loaded using the Database Configuration Assistant (DBCA) or by running the following scripts as the sys user if it is not already present. Using the package to send an email requires some knowledge of the SMTP protocol, but for the purpose of this text, a simple send_mail procedure has been written that should be suitable for most error reporting. >> My SMTP server IP is : 170.1.1.1 >> We will use ALERT user to send test mail OR If you are facing "ORA-29278: SMTP transient error: 421 Service not available" error, the read the below steps. You are trying to use the UTL_MAIL package to send email from your database. When attempting to send a mail, you recieve the following error: ORA-29278: SMTP transient error: 421 Service not available ORA-06512: at "SYS.UTL_MAIL", line 654 ORA-06512: at "SYS.UTL_MAIL", line 671 ORA-06512: at line 2 If this fails it could be a result of many things, so please check all of the items below: Check the value for the initialisation parameter SMTP_OUT_SERVER. This should be set to the SMTP server IP. If not, run: Are you running Oracle database 11g? If so, you will need to have XMLDB & Java installed in order to configure fine grained auditing and enable it there for each user explicitly. You can run through these checks to confirm you have everything in place: If the “JServer JAVA Virtual Machine” and “Oracle XML Database” components are not there then you need to install them to get this working. The following scripts are what you would use to install Java and XML DB Note: These instructions are taken directly from Oracle Metalink, but I would recommend double checking the notes on there just to make sure there have been no updates to them since I wrote this. Have you configured your Access Control List (ACL)? If not, you can do it with the code below. This is required for any Oracle 11g database where you want to send email using Access Control Lists (ACLs) and is a very common error to encounter after upgrading your database from 10g to 11g. Hopefully that has worked for you, but it may not…You might now be be getting a different error reporting ORA-24247: network access denied by access control list (ACL) if you have not configured your ACLs for the user running the package. if that’s the case, check out that article. It also covers the error whereby you have refreshed your environment from your production environment and receive the ORA-24247: network access denied by access control list (ACL). Hopefully one of the suggestions above should help you to resolve the issue. Note: Limitations on sending e-mail in Oracle with utl_mail. There are several limitations in utl_mail for sending e-mail messages from inside Oracle. The utl_mail package can only handle a RAW datatype, and hence a maximum value of 32k for a 32k mail message.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
SQL> create user ALERT identified by xwdkjdlc13ns default tablespace USERS temporary tablespace TEMP profile DEFAULT;
SQL> grant CONNECT to ALERT;
SQL> grant execute on UTL_MAIL to ALERT;
SQL> grant MGMT_USER to ALERT;
SQL> grant SELECT_CATALOG_ROLE to ALERT;

SQL> alter system set smtp_out_server = '170.1.1.1' scope=spfile;

SQL> show parameter smtp;
NAME                TYPE        VALUE
------------------- ----------- ------------------------------
smtp_out_server     string
SQL>

SQL> show parameter smtp;
NAME                TYPE        VALUE
------------------- ----------- ------------------------------
smtp_out_server     string      170.1.1.1
SQL>

SQL> grant execute on UTL_MAIL to public;

SQL> grant execute on UTL_MAIL to ALERT;
SQL> ALTER SESSION SET smtp_out_server = '170.1.1.1';
SQL> exec UTL_MAIL.send(sender => 'oracle.com', recipients => '
[email protected]
', subject => 'Test Mail', message => 'Hello World', mime_type => 'text; charset=us-ascii');

SQL> exec utl_mail.send('oracle.com','
[email protected]
','test mail','Hello World',mime_type => 'text; charset=us-ascii');

ALTER SYSTEM SET SMTP_OUT_SERVER=”<IP>” scope=both;

col COMP_NAME format a40;
col VERSION format a12;
col STATUS format a12;
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;

COMP_NAME                                VERSION         STATUS
---------------------------------------- --------------- ---------
Oracle Enterprise Manager                11.2.0.3.0      VALID
Oracle XML Database                      11.2.0.3.0      VALID
Oracle Expression Filter                 11.2.0.3.0      VALID
Oracle Rules Manager                     11.2.0.3.0      VALID
Oracle Workspace Manager                 11.2.0.3.0      VALID
Oracle Database Catalog Views            11.2.0.3.0      VALID
Oracle Database Packages and Types       11.2.0.3.0      VALID
JServer JAVA Virtual Machine             11.2.0.3.0      VALID
Oracle XDK                               11.2.0.3.0      VALID
Oracle Database Java Packages            11.2.0.3.0      VALID

10 rows selected.

SQL/> connect as sysdba
SQL> @?/rdbms/admin/utlmail.sql
SQL> @?/rdbms/admin/prvtmail.plb
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO USER;

e.g.,
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;

SQL> @?/rdbms/admin/utlmail.sql
Package created.
Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb
Package created.
Package body created.
Grant succeeded.
Package body created.

No errors.
SQL> GRANT EXECUTE ON SYS.UTL_MAIL TO alert;
Grant succeeded.

SQL>
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl => 'UTL_SMTP.xml',
    description => 'Granting privs to required users for UTL_SMTP.xml',
    principal => 'ALERT',
    is_grant => TRUE,
    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl => 'UTL_SMTP.xml',
    principal => 'ALERT',
    is_grant => TRUE,
    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl => 'UTL_SMTP.xml',
    host => '170.1.1.1');
END;
/

COMMIT;

SQL>
PL/SQL procedure successfully completed

Commit complete
SQL>

SQL> exec utl_mail.send('oracle.com','
[email protected]
','test mail','Hello World',mime_type => 'text; charset=us-ascii');

PL/SQL procedure successfully completed
SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!