Connect Apache Web Server and Apache Tomcat

We have Apache Web Server (httpd) as front-end and Apache Tomcat as back-end.
This solution is based on mod_proxy and AJP connector.

Apache web server config

You have to configure mod_proxy in apache httpd.
I added the following conf file in conf.d directory:

<VirtualHost 172.22.215.219:80>
    Servername sisifo.domain1
 
    RewriteEngine on
    RewriteCond %{SERVER_PORT} !^443$
    RewriteRule ^/(.*) https://%{HTTP_HOST}/$1 [NC,R=301,L]
 
 
    LogLevel info
    ErrorLog  /var/log/httpd/sisifo.domain1_error_log
    CustomLog /var/log/httpd/sisifo.domain1_access_log combined
</VirtualHost>
 
 
 
<VirtualHost 172.22.215.219:443>
    Servername sisifo.domain1
 
    SSLEngine on
    SSLProtocol all -SSLv2 -SSLv3
    SSLCipherSuite ECDHE-RSA-AES128-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA128:DHE-RSA-AES128-GCM-SHA384:DHE-RSA-AES128-GCM-SHA128:ECDHE-RSA-AES128-SHA384:ECDHE-RSA-AES128-SHA128:ECDHE-RSA-AES128-SHA:ECDHE-RSA-AES128-SHA:DHE-RSA-AES128-SHA128:DHE-RSA-AES128-SHA128:DHE-RSA-AES128-SHA:DHE-RSA-AES128-SHA:ECDHE-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:AES128-GCM-SHA384:AES128-GCM-SHA128:AES128-SHA128:AES128-SHA128:AES128-SHA:AES128-SHA:DES-CBC3-SHA:HIGH:!aNULL:!eNULL:!EXPORT:!DES:!MD5:!PSK:!RC4
 
    SSLCertificateKeyFile /etc/pki/tls/private/localhost.key
    SSLCertificateFile /etc/pki/tls/certs/localhost.crt
    #SSLCertificateChainFile /etc/pki/tls/certs/server-chain.crt
    #SSLCACertificatePath /etc/httpd/certs/ca
 
    <Location "/">
    Order Deny,Allow
                    Deny from all
                    Allow from all
                    #Allow from 10.224.0.0/12
    </Location>
 
    ##########
 
    ProxyRequests Off
    ProxyPassMatch ^/(.*)$ balancer://tomcat_cluster/$1$2 stickysession=JSESSIONID|jsessionid
 
    <Proxy balancer://tomcat_cluster>
      BalancerMember ajp://[TOMCAT1]:8009 loadfactor=1 route=jvmTomcat1 ping=1
   BalancerMember ajp://[TOMCAT2]:8009 loadfactor=1 route=jvmTomcat2 ping=1
       
      ProxySet lbmethod=byrequests
      ProxySet nofailover=off
      ProxySet timeout=300
    </Proxy>
     
    ##########
 
    LogLevel info  
    ErrorLog /var/log/httpd/sisifo.domain1_ssl_error_log
    CustomLog /var/log/httpd/sisifo.domain1_ssl_access_log combined
</VirtualHost>

Tomcat config

You have to edit $TOMCAT_HOM/config/server.xml of each tomcat instance and set an unique value for  jvmRoute attribute. This attribute is in Engine tag.

<Engine defaultHost="localhost" name="Catalina" jvmRoute="jvmTomcat1">

ORA-00942 Table or View Does Not Exist - Identify target table

During the installation of a third party application which uses Oracle Database, I found the following error in server log:

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"

The error was clear, the application was trying to execute an SQL statement against a table that was not in database, or the user did not have permissions. But, which table or view was that?

In order to discover the SQL the client was invoking I found this post: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2975793633621#284628100346267414. In my post I´ll just refactor it in order to easy understanding.

The idea behind of this is to register a trigger for server error 942, obtain  context information and the sql being executed, and save all this info in other table. The complete code is as follow:

-- Table to store errors
create table ERROR942 (id number, 
    "time" timestamp,
    "event" varchar(100), 
    "user" varchar(100),
    "server_error" varchar2(4000),
    "sql" varchar2(4000) );

-- Sequence to generate the table id
create sequence SEQ_ERROR942;


-- Trigger to insert errors in table
create or replace trigger TGR_SERVER942
after servererror on database
declare
   l_sql_text ora_name_list_t;
   full_sql varchar2(4000);
   l_n number;
begin
    if ( is_servererror(942) )
    then        
        full_sql := '';
        l_n := ora_sql_txt( l_sql_text );
        for i in 1 .. l_n
        loop
           full_sql := full_sql || l_sql_text(i);
        end loop;
        
         insert into ERROR942 values (SEQ_ERROR942.nextval, current_date, ora_sysevent, ora_login_user,  ora_server_error(1), full_sql);
    end if;
end;

commit;

The main aspects here are:

• We create ERROR942 table in order to store all errors
• We create SEQ_ERROR942 in order to generate the previous table ids
• We create TGR_SERVER942 trigger in order to capture server errors and store then in the table. 

You can test the solution as follow:

select * from UNEXIST_TABLE;

select * ERROR942;