Oracle installation - Enable Access from Another Machine : Newbie Oracle Tip

How many times have we installed Oracle any version on windows or unix (when the IP address is not static) and then found that even though you can connect to Database on the server through sqlplus  it is not accessible on other machines.

Personally there used to be a time in my previous organisation when most business and technical consultants installing oracle were clueless and ended up only talking to the database on their machine.

Oracle has a file called listner.ora in the directory $ORACLE_HOME\NETWORK\ADMIN
Here is the file from one of the installation

	# listener.ora Network Configuration File: I:\Oracle\product\11.2.0\dbhome_2\network\admin\listener.ora
	# Generated by Oracle configuration tools.
	SID_LIST_LISTENER =
	(SID_LIST =
	(SID_DESC =
	(SID_NAME = CLRExtProc)
	(ORACLE_HOME = I:\Oracle\product\11.2.0\dbhome_2)
	(PROGRAM = extproc)
	(ENVS = "EXTPROC_DLLS=ONLY:I:\Oracle\product\11.2.0\dbhome_2\bin\oraclr11.dll")
	)
	)
	LISTENER =
	(DESCRIPTION_LIST =
	(DESCRIPTION =
	(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
	)
	)
	ADR_BASE_LISTENER = I:\Oracle

view raw gistfile1.txt hosted with ❤ by GitHub

	# listener.ora Network Configuration File: I:\Oracle\product\11.2.0\dbhome_2\network\admin\listener.ora
	# Generated by Oracle configuration tools.
	SID_LIST_LISTENER =
	(SID_LIST =
	(SID_DESC =
	(SID_NAME = CLRExtProc)
	(ORACLE_HOME = I:\Oracle\product\11.2.0\dbhome_2)
	(PROGRAM = extproc)
	(ENVS = "EXTPROC_DLLS=ONLY:I:\Oracle\product\11.2.0\dbhome_2\bin\oraclr11.dll")
	)
	)
	LISTENER =
	(DESCRIPTION_LIST =
	(DESCRIPTION =
	(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
	(ADDRESS = (PROTOCOL = TCP)(HOST = 10.91.1.105)(PORT = 1521))
	(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
	)
	)
	ADR_BASE_LISTENER = I:\Oracle

view raw gistfile1.txt hosted with ❤ by GitHub

Notice that the server has the IP address 10.91.1.105 in this case. That's it ! Now how to check if your Oracle server is accepting connections? Simple my favourit utility is nmap. Just scan the ports on the server and the port (in our case the default 1521) will be open.

That's it !

Amazon SES Oracle: Production Access and still not able to send emails

Scenario: We received the AWS credentials but were not able to decide what was the quickest way to test if the mail service was working correctly.

Few minutes on google and there was a python script which we modified to our test case. All your need to do is key in your AWScredentials.



What is the good thing about this script? The script writes a detailed log so for example in our case the program was accidentally changed and the from email address was changed.  Even though we had Production access enabled we were not able to send emails at all.

I am attaching the script output for quick reference

	$ python emailscripttest.py S
	['S']
	send: 'ehlo ip-172-31-5-44.ap-southeast-1.compute.internal\r\n'
	reply: '250-email-smtp.amazonaws.com\r\n'
	reply: '250-8BITMIME\r\n'
	reply: '250-SIZE 10485760\r\n'
	reply: '250-STARTTLS\r\n'
	reply: '250-AUTH PLAIN LOGIN\r\n'
	reply: '250 Ok\r\n'
	reply: retcode (250); Msg: email-smtp.amazonaws.com
	8BITMIME
	SIZE 10485760
	STARTTLS
	AUTH PLAIN LOGIN
	Ok
	send: 'STARTTLS\r\n'
	reply: '220 Ready to start TLS\r\n'
	reply: retcode (220); Msg: Ready to start TLS
	send: 'ehlo ip-172-31-5-44.ap-southeast-1.compute.internal\r\n'
	reply: '250-email-smtp.amazonaws.com\r\n'
	reply: '250-8BITMIME\r\n'
	reply: '250-SIZE 10485760\r\n'
	reply: '250-STARTTLS\r\n'
	reply: '250-AUTH PLAIN LOGIN\r\n'
	reply: '250 Ok\r\n'
	reply: retcode (250); Msg: email-smtp.amazonaws.com
	8BITMIME
	SIZE 10485760
	STARTTLS
	AUTH PLAIN LOGIN
	Ok
	send: 'AUTH PLAIN xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\r\n'
	reply: '235 Authentication successful.\r\n'
	reply: retcode (235); Msg: Authentication successful.
	send: 'mail FROM:<from@domain.com> size=194\r\n'
	reply: '250 Ok\r\n'
	reply: retcode (250); Msg: Ok
	send: 'rcpt TO:<to@domain.com>\r\n'
	reply: '250 Ok\r\n'
	reply: retcode (250); Msg: Ok
	send: 'data\r\n'
	reply: '354 End data with <CR><LF>.<CR><LF>\r\n'
	reply: retcode (354); Msg: End data with <CR><LF>.<CR><LF>
	data: (354, 'End data with <CR><LF>.<CR><LF>')
	send: '<Message Text>'
	reply: '554 Message rejected: Email address is not verified.\r\n'
	reply: retcode (554); Msg: Message rejected: Email address is not verified.
	data: (554, 'Message rejected: Email address is not verified.')
	send: 'rset\r\n'
	reply: '250 Ok\r\n'
	reply: retcode (250); Msg: Ok
	Traceback (most recent call last):
	File "emailscripttest.py", line 56, in <module>
	server.sendmail(fromaddr, toSuccessAddrs, msgSuccess)
	File "/usr/lib/python2.7/smtplib.py", line 739, in sendmail
	raise SMTPDataError(code, resp)
	smtplib.SMTPDataError: (554, 'Message rejected: Email address is not verified.')
	ubuntu@ip-172-31-5-44:~$

view raw gistfile1.txt hosted with ❤ by GitHub

Notice that it clearly says 554, 'Message rejected: Email address is not verified.'


Tip: The production access allows to send email to any email id but it restricts that the "from" address at least must be verified if not the domain. This was the culprit in our case as the from email id was not verified.