Enable Oracle database to send emails via SMTP server

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

2. Check SMTP access of database Linux server on mail server

– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

$ telnet smtp_server 25

If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:

helo
mail from: my_email@my_company.com # you should see "Sender OK'
rcpt to: my_email@my_company.com   # you should see "Recipient OK"
data                               # Start mail input
test email via SMTP and orcl DB
[Enter]
.                                  # mail should be sent
[Enter]
quit

3. Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example: Read more »

How to fix ORA-12547 TNS lost contact when try to connect to Oracle

Issue / Oracle error

sqlplus scott/tiger
SQL*Plus: Release 10.2.0.5.0 - Production on Wed May 18 09:32:35 2011
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
ERROR:
ORA-12547: TNS :lost contact when try to connect to Oracle.

Issue description

I saw that TNS connection issue along with ORA-12547 Oracle error several times, usually when trying to connect to Oracle database server on Unix / Linux host with an OS user that does not belong to oinstall group (Oracle binaries owner group).  In this case, interesting enough that local TNS connection to database (when using tnsnames alias) works fine: Read more »

How to make HP-UX Korn shell similar to Linux Bash

Working as Oracle DBA on HP-UX and Linux both I prefer Bash shell which is available on Linux compared to Korn shell which is default shell on HP-UX. It’s more convenient to work on Linux Bash using handy key combinations, etc. Unfortunately Bash is not installed on HP-UX by default and also not officially supported there – you will not get help from HP support if problems with it arise.

Nevertheless, I could find a way of improving environment settings on HP-UX to make the backspace and some other keys work similar to Linux and Bash shell. Read more »

Oracle ADDM shows Virtual Memory Paging on 10gR2 11gR2

Problem description

I’ve recently notices a common issue on Oracle Enterprise Linux 5.x with Oracle database 10gR2 and 11gR2. The Oracle ADDM Performance Analysis showed the following alert as a top event  in both OEM console – Virtual Memory Paging and in the ADDM report – Significant virtual memory paging was detected on the host operating system.

Problem Analysis

From the first look I thought this was a virtual memory issue on the boxes but on the other hand I could not find any evidence at OS level of any problem with virtual memory paging. Additionally I was surprised that the issue occurred at the same time on different servers and Oracle versions. So, below I did some checks to prove there was no memory issue on these Linux boxes. Read more »

Oracle 11gR2 vs 10gR2 installation on Linux RHEL 5/OEL 5

In the last weeks I was involved in a series of Oracle database 10gR2 and 11gR2 server installations on Linux RHEL 5/OEL 5 (x86_64).

Below I will give a quick comparison of the new installation features of Oracle 11gR2 Server to Oracle10gR2.
Read more »

Next Page »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email