Oracle Datasource using TNSNAMES with Wildfly operating in Domain Mode
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.
- Navigate to localhost::9990/ and login.
- Select Configuration > Profiles > Full > Datasources > XA > Add
- Select Oracle XA Datasource > Next
- Enter a name and a jndi name > Next
- On step 2/4, hit the 'Detected Driver' tab and select 'ojdbc6'
- Still on step 2/4, enter 'oracle.jdbc.xa.client.OracleXADataSource' in the text field to specify the xa-datasource-class > Next
- Enter the url, mine looks like this "jdbc:oracle:thin:@tns_entry" > Next
- Enter username and password > Next
- 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.
- Make a symlink within jboss.domain.config.dir that points to your tnsnames.ora file: ln -s $TNS_ADMIN/tnsnames.ora tnsnames.ora
- Ensure all the servers in a group have a JVM option specifying the path to the tnsnames.ora file.
- From the web management interface select Runtime > Server Groups
- Select a server group and then hit the 'View' button
- Jvm Configuration > Edit
- Enter "-Doracle.net.tns_admin=${jboss.domain.config.dir}", without quotes, into the "JVM Options" field.
- Restart all servers in the group
- 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.
