SOA 12c RCU: Oracle XE 11g TNS listener does not currently know of SID

Recently, I installed Oracle XE 11g database on my windows machine to host my SOA 12c RCU.
Note: Although XE is not a certified database for SOA 12c, it works just fine for development purposes.

Strangely enough, my RCU utility was unable to connect to the database instance. I kept getting the error that "Unable to connect to the DB. Service not available".
I was pretty sure that all my connect parameters were correct.

Also, worth noting is that, I couldn't connect to the DB apex application running @ http://127.0.0.1:8080/apex/f?p=4950

First suspicion was to check the service name, as sometimes during installation, the domain name gets appended to the service name. eg., instead of orcl, it might be registered as orcl.localdomain

A quick look at the listener.ora file revealed that the default service name was indeed XE.

However, when I ran the lsnrctl status command, I could see that the XE service was not listed.

Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\SATANNAM-US\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully.

This is due to the fact that the listener hasn't registered the XE service properly. In my case, restarts of database and listener services didn't help. Remember, as a best practice the listener must always be started ahead of starting the database for it to register the services.

The fix is to manually instruct the database to register the XE service. To do this, login to sqlplus as sysdba and issue the following commands.

> sqlplus / as sysdba
> Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))' scope=both;
alter system register;

Exit sqlplus and restart your OracleServiceXE and listener services.

Now, lsnrctl status command gives the following output;

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\SATANNAM-US\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=8080)) Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xe" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully.

You can see that XE service is now registered and ready. Also note that the http port 8080 is up and running - meaning you can now successfully access the APEX url.

Creating JDBC Connection Pool in GlassFish Server

Documenting the steps required to create a JDBC data source & connection pool in GlassFish server.

1. Copy the ojdbc*.jar to the following location;
<GLASSFISH_DOMAIN_HOME>/lib/ext

You can find the ojdbc*.jar under the following folder of DB installation;
<DB_HOME>/server/jdbc/lib

Note: Restart your GlassFish server

2. Login as admin user to GlassFish server Admin Console eg., http://localhost:4848/

3. On the home page, choose "Create New JDBC Connection Pool" option. Alternatively, you can also choose Resources -> JDBC -> JDBC Connection Pools from the "Common Tasks" left panel

4. Click "New..." option from the JDBC Connection Pools page

5. Enter the following details in the "General Settings" page

Pool Name: TestPool
Resource Type: javax.sql.DataSource
Database Driver Vendor: Oracle (You can choose appropriate DB vendor here..)

and click "Next"

6. Configure the following "Additional Properties" and leave the other default values

User: DB Username/Schema Name
Database Name: ORCL (SID of your DB connection)
Password: DB Password
URL: jdbc:oracle:thin:@localhost:1521:ORCL
ServerName: localhost
PortNumber: 1521

7. Click Finish

8. In the final page, you can choose to ping the connection to test the connectivity

Lets now create a data source.

9. Go to "JDBC Resources" and choose "New..."

10. Enter a JNDI name (eg., jdbc/test), choose the created connection pool from the list (TestPool) and click on OK.

Now, all your applications deployed on GlassFish server can gain access to your backend via the data source.