Monthly Archives: December 2015

Oracle APEX 5.0.3 Patchset Released

Oracle APEX 5.0.3 patchset was released (early Christmas Present!), with (again) fewer fixed bugs than the previous patchset, some stand out ones are:

  • 22173641 – APEX_PLUGIN_UTIL.GET_DATA raises ORA-06502 for CLOB or LONG VARCHAR2
  • 22301102 – Redirect Loop if Rejoin Sessions Enabled for All, Logged In and On Public Page

As always, read the patchset notes, but I’d definitely recommend installing this update.

Oracle Cloud – Glassfish Port 4848 Madness?

In my last post on accessing Glassfish, it was a few days later and something dawned on me.

In the last post I mentioned that Glassfish was running on Port 4848, however when I accessed the DBaaS monitor I was able to access it via HTTP/HTTPs which run on port 80 and 443 respectively.

So, the question is, how am I able to access both APEX and DBaaS monitor via ports 80 / 443 when Glassfish is running on port 4848?

If you checked the DBaaS instance for the ports that are listening, using a command similar to this

[root@DEMO ~]# netstat -an | grep LISTEN
tcp 0 0 0.0.0.0:37764 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN
tcp 0 0 :::5500 :::* LISTEN
tcp 0 0 :::16386 :::* LISTEN
tcp 0 0 :::12164 :::* LISTEN
tcp 0 0 :::5000 :::* LISTEN
tcp 0 0 ::ffff:127.0.0.1:5006 :::* LISTEN
tcp 0 0 :::111 :::* LISTEN
tcp 0 0 :::8080 :::* LISTEN
tcp 0 0 :::1521 :::* LISTEN
tcp 0 0 :::8181 :::* LISTEN
tcp 0 0 :::22 :::* LISTEN
tcp 0 0 ::1:631 :::* LISTEN

You can see there’s nothing listening on port 80 (HTTP) or 443 (HTTPS). So how is our web request being handled? This did confuse me for more than a few minutes.

Based on having used Amazon AWS for years, I had a quick look in the network rules as I expected some Port Forwarding  rules doing the magic conversion of relaying traffic from port 80 to 4848 etc.

However…

network_forward.png

nothing there at all…I couldn’t even see an option for network port forwarding (this IMHO is pretty confusing, since I’d expect it to be here).

The answer turned out to be pretty simple. The GUI shows network rules enforced outside of the DBaaS instance itself, if you login to the DBaaS instance there are also firewall rules configured there.

Let’s SSH into the machine using our SSH key

[jes@mac oracle-cloud]$ ssh -i oracle_cloud_rsa opc@<my.public.ip.here>
[opc@DEMO ~]$

now, let’s SUDO to the root user

[opc@DEMO ~]$ sudo su -
[root@DEMO ~]#

and let’s check the firewall rules setup using iptables

[root@DEMO ~]# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Hmmm this threw me, I did expect something to be listed here.

Long story short, it’s the PREROUTING rules we need to look at, which can do via a command similar to

[root@DEMO ~]# iptables -L -t nat
Chain PREROUTING (policy ACCEPT)
target prot opt source destination
REDIRECT tcp -- anywhere anywhere tcp dpt:http redir ports 8080
REDIRECT udp -- anywhere anywhere udp dpt:http redir ports 8080
REDIRECT tcp -- anywhere anywhere tcp dpt:https redir ports 8181
REDIRECT udp -- anywhere anywhere udp dpt:https redir ports 8181

Chain INPUT (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

Chain POSTROUTING (policy ACCEPT)
target prot opt source destination

So here you can see that any traffic coming into the http ports is redirected to port 8080 and any https traffic is redirected to port 8181 (which is the SSL port that Glassfish listens on to).

So it’s these ‘magically transparent’ and ‘not very obvious’ iptables rules that make the incoming HTTP/HTTPS traffic get redirected internally to Glassfish running on Port 80.

Why is this relevant and why should you care?

Well this is important if (for example) you didn’t want users to directly access (such an old version of) Glassfish and instead put a Proxy like NGINX infront of Glassfish. You would need to remove / modify those pre-routing rules so that the traffic would go to NGINX (or Apache or whatever) first and then be reverse proxied from NGINX to Glassfish (this is something we do in our production instances.

Oracle Cloud – Glassfish Administration (port 4848 woes)

In the previous post I discussed accessing the DBaaS Monitor application, in this post I’ll show how to access the Glassfish Admin application.

On the home page for your DBaaS Instance, you’ll see a link for ‘Glassfish Administration’

cloud_home.png

However if you click on that link you’ll probably find the browser just hangs and nothing happens. It took me a while to notice but unlike the DBaaS monitor which is accessed via HTTP/HTTPs, the Glassfish Administration is done via port 4848 (you’ll notice 4848 in the URL once your browser times out).

The issue here is that by default port 4848 isn’t open in your network rules for your DBaaS instance, so the browser cannot connect to it.

So you have a couple of options –

  1. Open up port 4848 to the world (or to just specific IP addresses)
  2. Use an SSH Tunnel

I tend to go with option 2, since I’ve found occasionally while travelling and staying in a hotel if you go with option #1 you might be accessing from an IP address that isn’t in your whitelist.

As I blogged previously, we can setup an SSH tunnel to port 4848 pretty easily from the terminal, with a command similar to:

ssh -L 4848:localhost:4848 -i oracle_cloud_rsa opc@<my.remote.ip.here>

So now we should be able to access Glassfish using the URL http://localhost:4848

Why localhost? Remember when you setup an SSH tunnel you connect to your own local machine which then tunnels the traffic to the remote host via SSH over the ports you specify.

Once we’ve done that you should be able to access the Glassfish Administation homepage.

glassfish.png

You should be able to login using the username ‘admin‘ and the same password you specified when you created your DBaaS instance.

glassfish2.png

The first thing I noticed was that this is a pretty old version of Glassfish which is installed by default (version 3.1.2.2 in my case), when Glassfish 4 was already out. So you may wish to check if you’re missing any patches or need some Glassfish 4 features.

This is definitely one downside to going with the pre-bundled installation, you will (by definition) get an image which was created some time ago, so you need to check if there are any patches etc that have been released since the image was created.

I’m not going to go into detail on Glassfish itself, since it’s pretty much a standard (3.1) Glassfish and there are lots of blog posts and documents around that go into more detail. However if you go into the application section you’ll see that it comes pre-bundled with the APEX Listener / ORDS and also DBaaS Monitor which is how you can access them via the Glassfish server.

glassfish_apps.png

 

Oracle Cloud – Database Monitor

One of the nice features in Oracle Cloud is that they have incorporated a couple of extra tools available for you to use to monitor and maintain your Oracle DBaaS instance easily.

You can access Database Monitor if you have opened up the firewall for HTTP/HTTPS by accessing the URL

https://<your.public.ip.address>/dbaas_monitor/

(or you could use an SSH tunnel if you didn’t want to open it up).

Or you can navigate to it from the home page of (https://<your.public.ip.address&gt;) and clicking the Database Monitor link.

cloud_home.png

You will be prompted for a username and password to login

database_monitor.png

Now here’s where I wished I’d read the documentation before trying to “just guess”. I assumed that the username would be ‘system’ or ‘sysdba’ or some other DBA level account (perhaps the username / email address I used to sign up to the Cloud service).

But no…it turns out the default username is dbaas_monitor

The password is the same password you specified when you created the DBaaS instance.

 

Once you’ve entered those and (hopefully) logged in, you should see the DBaaS Monitor homepage

dbaas_home.png

As you can see we get a nice overview of the ‘health’ of our DBaaS Instance, including a summary of waits, CPU utilization and alert log entries.

We can drill into some CPU metrics

cpu.png

Get a nice (simplified) overview of storage

storage.png

and perform some (very simplified) management tasks like starting and stopping the database.

manage.png

So is this a replacement for Enterprise Manager? Absolutely not, it has very limited functionality, however it is also pretty light-weight so it’s potentially a faster way of checking the health of your DBaaS instance before you drill into EM etc.

I do hope Oracle extends and adds functionality to DBaaS Monitor in the future since it has a lot of potential.