Make tomcat wait until oracle database is available

Recently I’ve been installing the OATOA (Oracle APEX on Tomcat with ORDS behind Apache) stack a number of times on both Oracle Linux 7 and CentOS7.

In the past this was no problem, Tomcat came up and a request to http://myserver:8080/ords/ gave me the expected APEX login screen.

However since Oracle Linux 7 and CentOS7, it seemed that Tomcat started before it could find the Oracle database. A simple restart of the tomcat service would do the trick. On my virtual machine (recently switched from VirtualBox to parallels btw) meant for doing presentations no problem at all.

systemctl restart tomcat

Now I’m playing around with “droplets” at DigitalOcean, just another name for virtual servers in my opinion, but they have great features at a reasonable price. Take a look:

But my droplets showed the same behaviour as my parallels virtual machine. I first had to restart tomcat to get a working system.

You all should know that I’m not a linux guru (really I’m not), so broke my brains about this for a looooong time.

Yesterday I finally managed to get this working. The idea is to make the tomcat start-script wait until it can see “something” on port 1521 (the db listener) and only then continue to start.

Open the file /usr/libexec/tomcat/server

nano /usr/libexec/tomcat/server

now add some lines of code _before_ the line that starts with MAIN_CLASS like this:

. /usr/libexec/tomcat/preamble
# .=.=.=.=.= START Make tomcat wait on oracle .=.=.=.=.=
while netstat -lnt | awk ‘$4 ~ /:1521$/ {exit 1}’0
  sleep 10
  let i+=1
  if [ “$i” -gt “5” ]
    break    #Abandon the loop.
# give Oracle some slack to also start the database
sleep 10
# .=.=.=.=.= END Make tomcat wait on oracle .=.=.=.=.=


add the obvious lines to your script.

What is happening?

The line “netstat -lnt | awk …” tries to find the string “:1521” in the result of a “netstat -lnt” command. This would indicate that something is listening on that port. In our case it will be the Oracle Listener.

If it does not find an open port 1521, it will sleep for 10 seconds, increment a counter (“i”) by 1 and do the loop again. The loop will loop a maximum of five times to prevent an endless loop.

After the loop completes we give the Oracle database another 10 seconds to get started.

That should do the trick. It does for me (I use the same code at digitalocean as well as my parallels virtual linux server.

I’m sure linux must have some system in place that could do the trick as well (make one service dependable on the other) but I haven’t found an easy one to use. If someone could help me out here, feel free to leave a comment.



Calling a REST webservice with pl/sql

For a customer i needed to call a external REST webservice to update an external database with some specific information.

With the use of the utl_http package Oracle has made this very simple.

I tried to make the procedure i made, self-explaining.

create or replace procedure call_rest_webservice
  t_http_req     utl_http.req;
  t_http_resp    utl_http.resp;
  t_request_body varchar2(30000);
  t_respond      varchar2(30000);
  t_start_pos    integer := 1;
  t_output       varchar2(2000);

  /*Construct the information you want to send to the webservice.
    Normally this would be in a xml structure. But for a REST-
	webservice this is not mandatory. The webservice i needed to
	call excepts plain test.*/
  t_request_body := 'the data you want to send to the webservice';

  /*Telling Oracle where the webservice can be found, what kind of request is made
    and the version of the HTTP*/
  t_http_req:= utl_http.begin_request( 'http://the_url_of_the_webservice'
                                     , 'POST'
                                     , 'HTTP/1.1');

  /*In my case the webservice used authentication with a username an password
    that was provided to me. You can skip this line if it's a public webservice.*/

  /*Describe in the request-header what kind of data is send*/
  utl_http.set_header(t_http_req, 'Content-Type', 'text/xml charset=UTF-8');

  /*Describe in the request-header the lengt of the data*/
  utl_http.set_header(t_http_req, 'Content-Length', length(t_request_body));

  /*Put the data in de body of the request*/
  utl_http.write_text(t_http_req, t_request_body);

  /*make the actual request to the webservice en catch the responce in a
  t_http_resp:= utl_http.get_response(t_http_req);

  /*Read the body of the response, so you can find out if the information was
    received ok by the webservice.
    Go to the documentation of the webservice for what kind of responce you
    should expect. In my case it was:
  utl_http.read_text(t_http_resp, t_respond);

  /*Some closing?1 Releasing some memory, i think....*/

Calculate time difference between log-entries

For me, myself and I (as a reminder) 🙂

I needed to calculate the difference between a log entry and the previous log-entry to be able to investigate where performance was dropping:

With alittle bit of help here and here I figured out how to do it:

with iv 
as  (select PROGRAM
     ,      RUN_ID
     ,      PAL_N_LEVEL
     ,      MESSAGE
     ,      DATE_TIME
     ,      LOG_D_DATE
     ,      case
              when message like 'Start%' then ''
              when message like 'End%' then ''
              else substr(to_char(numtodsinterval(LOG_D_DATE - lag(LOG_D_DATE, 1, sysdate) over (order by LOG_D_DATE asc), 'day')), 15, 5)
            end time_spent
     from   vla 
     where  run_id = 264225 
     order  by log_d_date asc)
select PROGRAM
,      RUN_ID
,      PAL_N_LEVEL
,      MESSAGE
,      DATE_TIME
,      LOG_D_DATE
,      TIME_SPENT "Time spent (mm:ss)"
from   iv
where  1=1
  and  (1=0
        or message like 'refreshed%'
--        or message like 'refresh%'
        or message like 'Start%'
        or message like 'End%'

Oracle 11g on Windows DBConsole does not start

Wow. I have broken my thoughts about this for a loooooong time. In the end stuff can be so simple:

When you get the errormessage that it cannot start the DBConsole do this:

  • Make sure you have the MS loopback adapter installed (remember my previos Blog-entry)
  • Make an entry in your hosts file:
    xx.xx.xx.xx yyyy
    where xx.xx.xx.xx is your fixed IP-number on the MS Loopbackadapter and yyyy is your computrename
  • Restart the service

That should do the trick

Great thanks to

Oracle DB on a notebook

If you want to install an Oracle database on a notebook, then it is essential that beforehand you install the MS Loopback adapter. This is a “virtual” network adapter.

After installation assign it a fixed IP-addres ( for example) and you’re ready to install the Oracle Listener and Database instances.

You should not believe people who claim that Oracle doesn’t need it anymore. Maybe Oracle can do without the loopback adapter, but it will prevent a lot of issues that you would need to solve manuallly.