Tuesday, October 16, 2012

Time for Change: Migrate your Non-RAC Database to RAC

Hi Everyone,

One of my articles published in "IOUG Select Journal" and distributed at Oracle Open World 2012.

Time for Change: Migrate your Non-RAC Database to RAC - Page 25
By Satishbabu Gunukula

Please view the IOUG 2012 Q3 article using below link.
http://www.ioug.org/p/do/sd/sid=2371&fid=2133&req=direct


I hope you will like the article and it will be helpful to you.

Please leave your valuable comments.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Send mails using UTL_SMTP in Oracle

The UTL_SMTP Oracle package provides capability to send emails from PL/SQL program using Simple Mail Transfer Protocol. UTL_MAIL is easy to use as it eliminates steps of formatting the message body and explicit coding of details required by SMTP protocol.

Use below simple example to send mails using UTL_MAIL

begin
utl_mail.send(
sender => 'fromuser@oracleracexpert.com',
recipients => 'touser@oracleracexpert.com',
message => 'Test mail for UTL_MAIL'
);
end;

If you can want to use UTL_MAIL refer “Send EMAILs using UTL_MAIL

UTL_SMTP it is more fine-grained API that enables the attachments of larger size and it is very powerful utility.

Use below simple example to send mails using UTL_SMTP

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

You can write multiple line messages using the UTL_SMTP.WRITE_DTA command, use following example

CREATE OR REPLACE PROCEDURE send_mail_smtp(recipient IN VARCHAR2,
sender IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2,
mailhost IN VARCHAR2,
smpt_port IN NUMBER DEFAULT 25)
AS
smtp_mail_conn UTL_SMTP.connection;
BEGIN
Smtp_mail_conn := UTL_SMTP.open_connection(mailhost, smtp_port);
UTL_SMTP.helo(smtp_mail_conn, mailhost);
UTL_SMTP.mail(smtp_mail_conn, sender);
UTL_SMTP.rcpt(smtp_mail_conn, recipient);
UTL_SMTP.open_data(smtp_mail_conn);
UTL_SMTP.write_data(smtp_mail_conn, 'To: '||recipient||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'From: '||sender||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, 'Subject: '||subject||UTL_TCP.crlf);
UTL_SMTP.write_data(smtp_mail_conn, message||UTL_TCP.crlf||UTL_TCP.crlf);
UTL_SMTP.close_data(smtp_mail_conn);

UTL_SMTP.quit(smtp_mail_conn);
END;
/

Call the procedure by providing the below details

BEGIN
send_mail_smtp(recipient => 'touser@mycompany.com',
sender => 'fromuser@oracleracexpert.com',
subject => 'Test mail',
message => 'This is a test mail using UTL_SMTP.',
mailhost => localsmtp.oracleracexpert.com');
END;
/

To send emails to multiple recipients use UTL_SMTP.rcpt procedure for each separate email
For more information you can also refer Oracle Documentation http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_smtp.htm#ARPLS074

Common errors when using UTL_SMTP:-
1. The UTL_SMTP package requires the ORACLE xmldb component, please install the same to fix below error

ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_TCP"

2. You may receive below error if the “mailhost” was not set correctly

ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 20

3. You will receive below error if ACL is not assigned to target host and necessary privileges are not granted

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 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7

Convert Single Instance to RAC – Part1: Duplicate DB using RMAN

Hi Everyone,

My articles published in Oracle Experts website media "Allthingsoracle.com" by RedGate

Please view the article using below link.
Convert Single Instance to RAC - Part1: Duplicate DB using RMAN

I hope you will like the article and it will be helpful to you.

Please leave your valuable comments.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Friday, September 21, 2012

Manage Access Control List in Oracle 11g and ORA-24247

Oracle 11g offers fine-grained access to network services (ACL) and the packages used to access external network resources are restricted.

The 11g allows access to external packages UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL, UTL_INADDR, DBMS_LDAP, but the access must be granted explicitly. Please note that ACLs are stored in XML DB and user must install XML DB for the use of ACL, if not installed.

The DBMS_NETWORK_ACL_ADMIN package provides the interface to administer the network Access Control List (ACL).

     • ACL - Name of access control list in xml file and relative path will be “/sys/acls”
     • Description - Description of the ACL.
     • Principal - To whom the privilege is granted or revoked
     • Is_grant - Indicates weather the privilege is granted (TRUE) or denied (FALSE).
     • Privilege – Network privilege, Use ‘connect’ for access and ‘resolve’ for UTL_INADDR name/IP resolution.
     • Position – Position of ACL
     • Start_date – Start date of the ACL , the default value is NULL.
     • End_date – End date of an ACL.

Uses might see below error when they upgrade their databases from Oracle 10g to 11g. This is expected behavior in Oracle 11g

ORA-24247: network access denied by access control list (ACL)

This is expected behavior in 11g, if any of the UTL_% packages referred or used in any user defined programs. To resolve this issue user must explicitly grant the access using DBMS_NETWORK_ACL_ADMIN package.

For ex: - A used defined new send_mail PL/SQL program or existing program (After upgrading to 11g) failed with ORA-24247

DECLARE

mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/

DECLARE

*
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 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150
ORA-06512: at line 7

To resolve the issue user need to create ACL and grant required access, follow the below steps.

Step1:- Create New Access Control List

begin
dbms_network_acl_admin.create_acl (
acl => 'utl_smtp.xml',
description => 'Allow UTL_SMTP to send mails',
principal => 'TEST_USER',
is_grant => TRUE,
privilege => 'connect',
Start_Date => Null,
End_Date => Null);
end;

Step2:- Add privilege to Access control list

You can add the privilege like ‘resolve’ to the ACL.

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => ‘TEST_USER’,
is_grant => TRUE,
privilege => 'resolve');
end;
/

Step3:- Assign Access control List

begin
dbms_network_acl_admin.assign_acl(
acl => 'utl_smtp.xml',
host => 'localsmtp.oracleracexpert.com',
lower_port => 25,
upper_port => NULL);
end;
/

Step4:- Check the permission

Check the required permission is granted to the user “TEST_USER” using below query

SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
4 DECODE(
5 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'resolve'),
6 1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
7 FROM dual;

Connect     Resolve
----------- -------
DENIED DENIED

The access is not granted and user still receives error “ORA-24247: network access denied by access control list (ACL)”.

You must “COMMIT” the changes in order to work the Network ACL’s.

Now user should be able to see the changes using below queries

SQL> SELECT DECODE(
2 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'connect'),
3 1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
4 DECODE(
5 DBMS_NETWORK_ACL_ADMIN.check_privilege('utl_smtp.xml', 'TEST_USER', 'resolve'),
6 1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
7 FROM dual;

Connect      Resolve
------------ -------
GRANTED GRANTED

SQL> select * from dba_network_acls where acl like '%utl_smtp%%';

HOST LOWER_PORT UPPER_PORT ACL ACLID
------------------------ ---------- ---------- ------------------------------ ---------------------------------------------
localsmtp.oracleracexpert.com /sys/acls/utl_smtp.xml C6B2CCC62AC30707E04025AC80DA7FA3

Now user defined send_mail program is working fine.

DECLARE
mailhost VARCHAR2(64) := ‘localsmtp.oracleracexpert.com;
sender VARCHAR2(64) := 'fromuser@oracleracexpert.com';
recipient VARCHAR2(64) := 'touser@ oracleracexpert.com';
smtp_mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection (mailhost, 25);
utl_smtp.helo (smtp_mail_conn, mailhost);
utl_smtp.mail (smtp_mail_conn, sender);
utl_smtp.rcpt (smtp_mail_conn, recipient);
utl_smtp.data (smtp_mail_conn, 'This is a test mail using UTL_SMTP '
chr(10));
utl_smtp.quit (smtp_mail_conn);
END;
/
PL/SQL procedure successfully completed.

Manage the Network ACLs as below

Add a user or role to newly created Access control list (ACL) – Using ADD_PRIVILEGE procedure adding privilege to the user “SCOTT”

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => ‘SCOTT’,
is_grant => TRUE,
privilege => 'connect');
end;
/

Remove a user or role to newly created Access control list (ACL) – Using DELETE_PRIVILEGE procedure deleting a privilege in an access control list from user “SCOTT”

begin
dbms_network_acl_admin.delete_privilege (
acl => 'utl_smtp.xml',
principal => ‘SCOTT’,
is_grant => FALSE,
privilege => 'connect');
end;
COMMIT;
/

Drop Access control list (ACL) – Using DROP_ACL procedure dropping an access control list (ACL).

begin
dbms_network_acl_admin.drop_acl (
acl => 'utl_smtp.xml');
COMMIT;
end;
/

Unassign Access control list (ACL) – Using UNASSIGN_ACL procedure un-assigning the access control list (ACL) from a host.

begin
dbms_network_acl_admin.unassign_acl (
acl => 'utl_smtp.xml',
host => 'hostname’);
COMMIT;
end;
/

You can query below views for ACL and privilege information

DBA_NETWORK_ACL_PRIVILEGES describes the network privileges defined in all access control lists that are currently assigned to network hosts.

DBA_NETWORK_ACLS describes the access control list assignments to network hosts.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Packt Publishing reaches its 1000 titles

Hi Readers,

Packt Publishing is about to publish its 1000th title and they would like to celebrate this occasion by giving a surprise gift to their readers.

These gifts will only be revealed between 28th and 30th September, as when their 1000th title gets unveiled. The gifts are valid for only those readers who are currently registered on their website, and those who sign up for an account before 30th September.

You can find complete information about the event on http://www.packtpub.com/news/packt-publishing-reaches-1000-it-titles-and-celebrates-open-invitation

You can visit Packt Publishing Website http://www.packtpub.com to see their collection of books.

I hope you will get benefited.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com