"Seegrid will be due for a migration to confluence on the 1st of August. Any update on or after the 1st of August will NOT be migrated"

Contents


GA's Architecture for deploying Geoserver.

When we went to deploy Geoserver on our web servers outside the firewall we hit a major problem. This is because of the way Geoserver connects to Oracle (and probably all JDBC) databases.

In the catalog.xml file you describe the elements required to generate a thin client jdbc connection in the following way:

<?xml version="1.0" encoding="UTF-8" ?>
<catalog>
schema  <!-- defines the datastores, more than one is possible -->
  <datastores>
    <datastore id="remote.oracle" enabled="true" namespace="topp">
      <connectionParams>
        <parameter name="host" value="oracle"/>
        <parameter name="port" value="1521"/>
        <parameter name="instance" value="test"/>
        <parameter name="user" value="testuser"/>
        <parameter name="passwd" value="pass"/>
        <parameter name="dbtype" value="oracle"/>
      </connectionParams>
    </datastore>
  ...
  </datastores>
  ... 
</catalog>

so that Geoserver can produce the following connection string:

jdbc:oracle:thin:@oracle:1521:test

This worked fine while the Geoserver instance was located within our intranet environment because Geoserver can use this connection string to connect directly with our databases. The problem came when we deployed Geoserver on our web servers outside the firewall. This is because we only allow SQL connections through to our databases inside the firewall via an sqlproxy. To use the proxy you require a string like this:

jdbc:oracle:thin:@( description=(SOURCE_ROUTE = YES)(ADDRESS_LIST = (ADDRESS = (COMMUNITY = test)(PROTOCOL = TCP)(HOST = 111.111.111.111)(PORT = 1521)) (ADDRESS = (COMMUNITY = test)(PROTOCOL = TCP)(HOST = oracle)(PORT = 1521)))(CONNECT_DATA =(SID = oracleSID)(GLOBAL_NAME = oracleSID)))

Fortunately these connection strings are held in the tsnnames.ora ($ORACLE_HOME/network/admin/tnsnames.ora) file as aliases. In the Advanced section of the Geoserver documentation for Oracle it mentions that you can actually use aliases to make an OCI thick connection instead of a thin connection and that an option to use the alias should show up in the Geoserver Feature Type Edit page IF you are using Geoserver version 1.2.2 or above. Unfortunately we're only using version 1.2.0-rc2. However we took a punt and added the following element to the data store configuration in the catalog.xml file:

<?xml version="1.0" encoding="UTF-8" ?>
<catalog>
schema  <!-- defines the datastores, more than one is possible -->
  <datastores>
    <datastore id="remote.oracle" enabled="true" namespace="topp">
      <connectionParams>
        <parameter name="alias" value="oracleSID"/>
        <!--parameter name="host" value="oracle"/-->
        <!--parameter name="port" value="1521"/-->
        <!--parameter name="instance" value="test"/-->
        <parameter name="user" value="testuser"/>
        <parameter name="passwd" value="pass"/>
        <parameter name="dbtype" value="oracle"/>
      </connectionParams>
    </datastore>
  ...
  </datastores>
  ... 
</catalog>

Note that we also commented out the host, port and instance parameters - don't actually know if this is required but it seemd like a sensible thing to do.

Amazingly enough it worked. Well it did initially on my PC but then didn't on our web servers. We found that we had to add the following environment variable to the environment variables of the (Unix) user that instantiates Tomcat (not Apache):

set env=$LD_LIBRARY_PATH=$ORACLE_HOME/lib (this may differ depending on what UNIX shell you're using).

We then updated the environment variables for all our Tomcat instantiating users and everything worked fine for all our servers, internal and external.

Bob's your Aunty's live in lover!

GA Architecture for Deploying Geoserver

-- StuartGirvan - 11 Nov 2004
Topic revision: r2 - 15 Oct 2010, UnknownUser
 

Current license: All material on this collaboration platform is licensed under a Creative Commons Attribution 3.0 Australia Licence (CC BY 3.0).