About sending mail from Apex on Amazon EC2

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This is just a little reminder in case I forget (again) that the SMTP port on Amazon EC2 receives special attention due to the potential exploitation by spammers.

If you run multiple Apex instances on Amazon EC2, it may sound convenient to configure a single mail server for all instances. Ideally one should not even bother to set up a dedicated mail server and use Amazon Simple Mail Service, but unfortunately the service only accepts SSL connections which rules out the standard APEX_MAIL API.
It is also less than desirable, I guess, to maintain separate mail servers for each instance, therefore I consolidated the mail server on a micro instance that does almost only that job at a very reasonable price.

In order to allow the communication between two EC2 instances belonging to the same security group, normally it is sufficient to enable the inbound traffic for the whole EC2 security group instead of each and every IP address, resulting in a much shorter firewall rules list. For instance, if I have machine A and B and they both belong to a security group called default whose security group ID is SG-8ab12345, then I can configure this default security group to accept inbound connections on a whole range of ports for SG-8ab12345.

Unfortunately this simple and effective method doesn't apply to port 25 (or so it seems).
The fact is further complicated by the dynamic nature of the private IP addresses assigned to each machine at every start.
One way to work around the problem of dynamic IPs so far has been to use elastic IPs (public static IPs) which are internally resolved into private IPs but this technique works for all ports except port 25.
So, the only solution I know is to explicitly add each and every elastic IP address against port 25 in the relevant security group firewall rules.

In Apex terms it means that you need to specify the elastic IP of the mail server in the Manage Instance/Feature Configuration page, in the Apex administrator (INTERNAL) workspace.

If you later remove the rule for port 25 as I did accidentally this morning, the APEX_MAIL_LOG view will be cluttered with unsent messages reporting SMTP transient error: 421 Service not available in the MAIL_SEND_ERROR column.

The best news however is that just yesterday Amazon announced the preliminary availability of VPC (Amazon Virtual Private Cloud), a new type of service that addresses multi-tier application issues, so may be that we can finally say goodbye to all these workarounds and headaches and configure our own public and private subnets inside the Amazon EC2 network.

Yet another click counting topic: counting clicks from blogspot

I do not promise this is going to be my final message on the click counting topic for the simple fact that click counting has so many facets!

The problem arose when i decided to log the ip address of the people downloading software from yocoya's web server, but not just those who do the operation from within Apex scope, but also those who perform the operation directly from this blog.

Theoretically there was nothing that prevented me from using Z function directly, however i quickly discovered that owing to a bug of blogger.com (or at least that is what i presume), the resulting link was bogus (it contained one escaped ampersand that i could not get rid of).

But not only. Having the site behind a firewall, the built-in Z function was logging the IP address of the firewall instead of recording the remote IP address. Luckily enough, John Scott provided me with an alternate custom CGI environment variable (a replacement for the native REMOTE_ADDR) where he stores the real remote IP address, so now i had two good reasons for developing my own Z function wrapper.

I do not claim this is the best solution, however it gets the job done very smoothly and allows me to have the data logged in the db where i can easily do whatever i like in terms of statistics, charting and so on. If you read a previous article, you should also be able to retrieve the corresponding domain name quite easily, if any is available.

Last but not least, the download folder is now hidden, so it gives you a minimal form of protection and it makes very easy to relocate the download folder elsewhere without having to go after each and every link in external documents. For additional protection you could even obfuscate this procedure using oracle wrap utility.

I'll not make it longer than necessary, you can download here the source code of this wrapper and make the necessary adjustments for your environment.

One more thing, do not forget to:

GRANT EXECUTE ON download_it TO APEX_PUBLIC_USER
/

Without a public synonym the procedure needs to be invoked specifying the schema name.

As you can see above, in my case it becomes:

http://www.yocoya.com/pls/apex/yocoya.download_it?p_name=filename

If you don't want to use the schema prefix, then you can create a public synonym and grant the execute privilege on it.

As you see it's absolutely trivial, but you are free to make things more complicated ;-)

For instance, the first thing that comes to my mind is that you could use an oracle sequence to keep track of the total number of downloads and store that number in the click id which i left NULL.

By the way, by clicking on the link, you are using the DOWNLOAD_IT procedure in question.

See more articles about Oracle Application Express or download tools and utilities.

ORA-12170: TNS:Connect timeout occurred aka TNS-12170

If you are wondering why are you getting this error *and* all the following prerequisite conditions apply to you:

1. you are trying to connect to a remote database through the internet;
2. tnsping net_service_name is working telling you that the remote listener is up and running;
3. the remote database is running on a Windows platform;
4. server parameter USE_SHARED_SOCKET is not set or is set to FALSE;
5. your client is attempting to connect in dedicated server mode;
6. you get ORA-12170:TNS:Connect timeout occurred (or TNS-12170 on versions earlier than 10g), when you try to open a connection from SQL*Plus or from another client program;

then you might try setting USE_SHARED_SOCKET=TRUE and see if it works.
USE_SHARED_SOCKET can be either an environment variable or a windows registry value.
I preferred the former option and i defined it as a system variable, in My computer's properties/advanced tab/environmental variables.

If you look up ORA-12170 in the Oracle error message list for Oracle 10G, then you are confronted with an explanation where either network delays or a denial-of-service attacks are the most probable causes.
Well, in my humble opinion, is also very likely that you are trying to access a database behind a firewall and the only open port is 1521 (or a non-standard port if that's the case).

Opening port 1521 is not enough because the listener is only accepting initial requests through that port, but once the dedicated server process is started, the connection between client and server is moved to a different port, typically a randomly chosen port, if i am not wrong.

In view of this fact, you can't just open up all the ports of the firewall and here's where this USE_SHARED_SOCKET=TRUE comes in handy, because it forces the connection to occur on the same initial port.

See Appendix C of the Database Platform Guide for further information.


Hope it helps.



Updated April 12.
If the above scenario doesn't fit well to your case, you may want to read the official documentation, starting from this section of the Net Services Admininistrator's Guide (10G).

Updated October 12.
I am getting ORA-12170 also when i try to access Oracle XE running on my Windows laptop when i forget to start the OracleTNSListener service that i modified from "Automatic" to "Manual". After starting OracleTNSListener, you may get ORA-12514 until the Oracle database server registers with the listener (within 1-2 minutes typically).

---

ORA-12170: TNS:Connect timeout occurred

is the english message corresponding to the following translated versions:

ORA-12170: TNS: si è verificato il timeout della connessione
ORA-12170: TNS:Se ha producido un timeout de conexión
ORA-12170: TNS:S'ha superat el temps d'espera de la connexió
ORA-12170: TNS : délai de connexion dépassé
ORA-12170: TNS: Zeitüberschreitung bei Verbindung
ORA-12170: TNS:Προέκυψε τέλος χρόνου σύνδεσης
ORA-12170: TNS:Forbindelses-timeout opstod
ORA-12170: TNS: Tidsgränsen överskreds vid anslutning
ORA-12170: TNS:Det oppstod et tidsavbrudd for tilkoblingen
ORA-12170: TNS: Yhteyden aikakatkaisu
ORA-12170: TNS:Csatlakozási időtúllépés történt.
ORA-12170: TNS:A survenit o eroare la expirarea timpului alocat pentru conectare
ORA-12170: TNS: time-out van verbinding.
ORA-12170: TNS:Ocorreu timeout de conexão
ORA-12170: TNS:Ocorrência de tempo de espera esgotado da ligação
ORA-12170: TNS:Истекло время ожидания соединения
ORA-12170: TNS:Vyskytlo se odpojení z důvodu vypršení časového limitu
ORA-12170: TNS: Došlo k uplynutiu časového limitu pripojenia
ORA-12170: TNS:przekroczenie limitu czasu połączenia
ORA-12170: TNS: Bağlantı zaman aşımı oluştu