Which DB is Apex running out of?

With the use of Apex growing and growing inside our organisation at the moment I've been wondering how we actually know when we connect to an Apex url (and I'm talking using EPG or emebedded plsql gateway here - i.e. no apache/weblogic etc just running it out of the database) which database are we actually connected to?

So for example if we connect to http://server:7777/apex - which database is serving us up the pages?

In our case we have servers with up to 12 databases on - many of which can be running apex.

Now you may think on the face of it this is a very easy thing to find out - but it seems that it's not - at least not without logging in to the Apex instance itself and then running some kind of query or inferring it from some objects/schemas that are visible - but what if i don't want to do that and i just want to directly link a port to a database?

Now you still may be thinking again this is easy and I've missed a trick - we just run this SQL against every database

select DBMS_XDB.GETHTTPPORT from dual; 

Whichever database returns 7777 is the winner right?

Well kind of - but what if you have multiple databases have 7777 configured- which one is actually the working one? The one that started first you may think (and grabbed the port) - but what if there is some misconfiguration somewhere or the listener was restarted - it's not definitive..... Actually this whole process is very annoying - you dont' get any kind of error if you pick a port that is already in use - it just seems to silently fail.

So how to find it out?

After a bit of digging i found a way of doing it but it's still not as straightforward as i would like.....

So here goes

The first thing we do is go into lsnrctl and do the following:

LSNRCTL> show trc_level
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_level" set to off
The command completed successfully
LSNRCTL> set trc_level user
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_level" set to user
The command completed successfully





This enables a basic degree of tracing in the listener.

Once the trace is active i visit the url http://server:7777/apex

Then i switch tracing off and find the location of the file that was created

LSNRCTL> set trc_level OFF
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_level" set to off
The command completed successfully
LSNRCTL> show trc_file
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_file" set to ora_25305_140243129435552.trc
The command completed successfully
LSNRCTL> show trc_directory
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "trc_directory" set to /oracle/diag/tnslsnr/server/listener/trace
The command completed successfully

Now i look at the contents of this trace file

vi /oracle/diag/tnslsnr/server/listener/trace/ora_25305_140243129435552.trc

trawling through that file i find this section

2014-10-07 18:17:01.655473 : nttcnr:connected on source ipaddr x.x.x.x port 7777
2014-10-07 18:17:01.655485 : nttcnr:connected on destination ipaddr x.x.x.x port 60341
2014-10-07 18:17:01.655496 : nttvlser:valid node check on incoming node x.x.x.x
2014-10-07 18:17:01.655508 : nttvlser:Accepted Entry: x.x.x.x
2014-10-07 18:17:01.655531 : nttcon:set TCP_NODELAY on 62
2014-10-07 18:17:01.655544 : nsopen:transport is open
2014-10-07 18:17:01.655569 : nsopen:global context check-in (to slot 42) complete
2014-10-07 18:17:01.655611 : nsanswer:deferring connect attempt; at stage 9
2014-10-07 18:17:01.655632 : nstoClearTimeout:ATO disabled for ctx=0x0xa92c60
2014-10-07 18:17:01.655670 : nstoUpdateActive:Active timeout is -1 (see nstotyp)
2014-10-07 18:17:01.655687 : nstoControlATO:ATO disabled for ctx=0x0xa92c60
2014-10-07 18:17:01.655739 : nsglbgetRSPidx:returning ecode=0
2014-10-07 18:17:01.655804 : nsglbgetSdPidx:secondary protocol=4
2014-10-07 18:17:01.655831 : nsc2addr:(ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=14217))

The key line being the last one - the connection has been routed to port 14217 - this is the port the database dispatcher process is listening on for apex requests.

Now it's just a simple case of using lsof to map this pid to a process

lsof |grep 14217

ora_d000_  1067     oracle   13u     IPv6         1853344907         0t0        TCP *:14217 (LISTEN)



OK so the vital name part didn;t quite fit - but now we just do a ps -ef to locate the full process name

[oracle@server]::[~]# ps -ef |grep 1067
oracle    1067     1  0 Aug21 ?        00:00:48 ora_d000_APEXDB
oracle    1653 12834  0 18:21 pts/1    00:00:00 grep 1067


So there we can see the result - APEXDB

So we've tracked through from a connection on port 7777 to the actual destination database it connects to

There is probably more than one way to do this but this worked for me........