6 Replies Latest reply on May 9, 2016 7:03 AM by Wolfgang Mayer

    Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode

    Collin Cudd Newbie

      I'm writing this in hopes of saving others some time and frustration.

       

      First off, I'm using a thin style connection string with a TNSNames alias.  This setup relies on a system property ("oracle.net.tns_admin") to specify the location of the tnsnames.ora file.  To set this system property with the Wildfly JVM, startup Wildfly using "./domain.sh -Doracle.net.tns_admin=$TNS_ADMIN".  This will become important later...

       

      Ok, now that we have Wildfly running in domain mode, let's install the jdbc drivers...

      The recommended way to install a jdbc driver is via a regular jar deployment: DataSource configuration - WildFly 9 - Project Documentation Editor

      Unfortunately, this doesn't work at all in domain mode.  In fact the only way I've found to successfully install the driver is via the jboss-cli.  First you must issue a command to create a module containing the oracle jar, note this command is performed offline, if you include a "--connect" it will fail.

       

      ./jboss-cli.sh --command="module add

      --name=com.oracle.ojdbc6

      --resources=/home/appl/buildmaster/local/wildfly/drivers/ojdbc6.jar

      --dependencies=javax.api,javax.transaction.api"

       

      After running this command, check the directory at modules/com/oracle/ojdbc6/main/.  This directory should contain module.xml and ojdbc6.jar.
      Next, issue a command to add the module as a jdbc driver, to the profile of your choice, I'm using the "full" profile below.

       

      ./jboss-cli.sh --connect --command="/profile=full/subsystem=datasources/jdbc-driver=ojdbc6:add(driver-name=ojdbc6,driver-module-name=com.oracle.ojdbc6"

       

      There are a number of different commands to list the installed drivers, this is the one that works for domain mode:

      curl --digest -L http://localhost:9990/management --header "Content-Type: application/json" -u user:password -d '{"operation":"read-children-resources","address":[{"profile":"full"},{"subsystem":"datasources"}],"child-type":"jdbc-driver","json.pretty":1}'

       

      and the response:

      {

          "outcome" : "success",

          "result" : {

              "h2" : {

                  "deployment-name" : null,

                  "driver-class-name" : null,

                  "driver-datasource-class-name" : null,

                  "driver-major-version" : null,

                  "driver-minor-version" : null,

                  "driver-module-name" : "com.h2database.h2",

                  "driver-name" : "h2",

                  "driver-xa-datasource-class-name" : "org.h2.jdbcx.JdbcDataSource",

                  "jdbc-compliant" : null,

                  "module-slot" : null,

                  "profile" : null,

                  "xa-datasource-class" : null

              },

              "ojdbc6" : {

                  "deployment-name" : null,

                  "driver-class-name" : null,

                  "driver-datasource-class-name" : null,

                  "driver-major-version" : null,

                  "driver-minor-version" : null,

                  "driver-module-name" : "com.oracle.ojdbc6",

                  "driver-name" : "ojdbc6",

                  "driver-xa-datasource-class-name" : null,

                  "jdbc-compliant" : null,

                  "module-slot" : null,

                  "profile" : null,

                  "xa-datasource-class" : null

              }

          }

      }

       

      Ok, now that we have our Oracle driver installed, let's setup a datasource.  I'm using the web based management interface for this part.

      1. Navigate to localhost::9990/ and login.
      2. Select Configuration > Profiles > Full > Datasources > XA > Add
      3. Select Oracle XA Datasource > Next
      4. Enter a name and a jndi name > Next
      5. On step 2/4, hit the 'Detected Driver' tab and select 'ojdbc6'
      6. Still on step 2/4, enter 'oracle.jdbc.xa.client.OracleXADataSource' in the text field to specify the xa-datasource-class > Next
      7. Enter the url, mine looks like this "jdbc:oracle:thin:@tns_entry" > Next
      8. Enter username and password > Next
      9. Test Connection will fail, don't worry about it yet, just hit done.

       

      Why did the test connection fail? 

      Looking through my server logs I see this error every time I re-test the connection: "Caused by: oracle.net.ns.NetException: could not resolve the connect identifier  'tns_entry'".  I suspect that the tnsnames.ora file is not being found.  So I modified the datasource, changing the URL from "jdbc:oracle:thin:@tns_entry" to the full connection string "jdbc:oracle:thin:@//myhost:1521/myservicename".  After making the change and restarting the servers I have a successful connection to  an Oracle database.  This is great and all, but I have to use a tns alias..


      The problem ends up being 2 fold.  First, it doesn't appear that the "-Doption=value" command line options work like you expect in domain mode.  I believe the options you specify on the command line are only passed to the domain controller and not the other server(s) or group(s).  Second, the individual hosts do NOT have access to the file system outside of the Wildfly directory.  So, individual Wildfly instances will not be able to access your tnsnames file unless you place it within the Wildfly config directory.

       

      To work around the above issues.

      1. Make a symlink within jboss.domain.config.dir that points to your tnsnames.ora file: ln -s $TNS_ADMIN/tnsnames.ora tnsnames.ora
      2. Ensure all the servers in a group have a JVM option specifying the path to the tnsnames.ora file.
        1. From the web management interface select Runtime > Server Groups
        2. Select a server group and then hit the 'View' button
        3. Jvm Configuration > Edit
        4. Enter "-Doracle.net.tns_admin=${jboss.domain.config.dir}", without quotes, into the "JVM Options" field.
        5. Restart all servers in the group
        6. Re-test the connection and it should work.  If it still doesn't work, I'd suspect an error in the tnsnames.ora file itself.
        • 1. Re: Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
          Wolfgang Mayer Master

          I am not sure what you are trying to achieve. But there should not be any need to provide the oracle.net.tns_admin parameter (you should make sure that the listener is running and managing the Oracle instance).

          It should then be enough to install the driver as a module as descibed here (JDBC Driver / DataSource Setup | HAL Management Console).

          The cli command to install the driver for oracle would then look like:

          [domain@localhost:9990 /] /profile=full/subsystem=datasources/jdbc-driver=oracle:add(driver-name=oracle,driver-module-name=com.oracle,driver-class-name=oracle.jdbc.driver.OracleDriver,xa-datasource-class=oracle.jdbc.xa.client.OracleXADataSource)

          The data source installation could also be done via the web interface.

          • 2. Re: Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
            Collin Cudd Newbie

            Wolfgang,

             

            What is this listener you speak of? I'm not a DBA, I don't manage any Oracle instances, but I do connect to them.  I have a few servers running instances of Wildfly, each server needs to connect to an Oracle database located on a different server/network/building.

             

            Straight from the Oracle docs: "The oracle.net.tns_admin system property must be set to the location of the tnsnames.ora file so that the JDBC Thin driver can locate the tnsnames.ora file." --Data Sources and URLs

             

            -Collin

            • 3. Re: Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
              Wolfgang Mayer Master

              Table 8-3 Supported Database Specifiers mentions the "TNSNames alias" as one of more options to connect to the Oracle database. Since Wildfly does not use OCI you can take the "Thin-style service name" approach.

              The format is usually: "jdbc:oracle:thin:@hostname:1521:servicename". You should provide this information when configuring the data source in WildFly.  I think this is by far the easiest configuration option.

              Your question regarding Oracle listener: The Oracle listener is only required when accessing the database via the network. "The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server." (Configuring and Administering the Listener).

              You can check the status via the command: lsnrctl status

              • 4. Re: Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
                John Smith Newbie

                this is really great and helpful post this is really helpful for everyone thank for provide this great post. avg phone number

                • 5. Re: Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
                  Collin Cudd Newbie

                  Large organizations have teams of database administrators, that may be running thousands of Oracle instances.  Sometimes databases move from one server/host/port to another.  In a large organizations it happens far more than you'd think, and that makes the use of connection strings that contain server/port/host (e.g. jdbc:oracle:thin:@hostname:1521:servicename) very painful to manage.  In this scenario, every time a database moves, the Wildfly administrator is forced to update the datasource as well.

                   

                  This is where TNSNames alias comes in.  Our DBA's manage the tnsnames.ora file and keep it on a network share that's accessible to my servers that are running Wildfly.  When a database moves, the DBA simply updates the tnsnames.ora file with the new server/port/host, no further changes to Wildfly are necessary.  I have a requirement to use TNSNames for this exact reason.

                   

                   

                  Also, I did a bit of experimenting with the command to add the ojdbc driver, this is what seems to work best (allowing you to add an XA, or Non-XA, datasource without specifying a class-name):

                   

                  ./jboss-cli.sh --connect --command="/profile=full/subsystem=datasources/jdbc-driver=ojdbc6:add(\

                          driver-name=ojdbc6,\

                          driver-module-name=com.oracle.ojdbc6,\

                          driver-class-name=oracle.jdbc.driver.OracleDriver,\

                          driver-xa-datasource-class-name=oracle.jdbc.xa.client.OracleXADataSource)"

                  • 6. Re: Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
                    Wolfgang Mayer Master

                    Sorry, I had a typo in the datasource configuration.

                     

                    But if you need to take the service name from tns you could try to use the OCI driver instead.

                     

                    Connection URL: jdbc:oracle:oci8:@serviceName
                    (If you get the message: "java.lang.UnsatisfiedLinkError: no ocijdbc11 in java.library.path" you

                    should also set the LD_LIBRARY_PATH to point to your Oracle lib)