Oracle Cloud – Updated UI

I was creating a new DBaaS Instance recently and noticed that much of the UI had changed from when I originally blogged about it previously. The differences are quite striking and it goes to show that you shouldn’t base your opinion on something you saw many months ago, always check your assumptions to see if they’ve changed!

So for example, in one of my older running DBaaS instances the home page looks like this

cloud_home.png

Old DBaaS Landing Page

whereas in the new instance it looks like this

cloud_home_new.png

New DBaaS Landing Page

So functionality equivalent, but very much nicer and more modern to look at.

The Database Monitor has been updated quite dramatically too, so for example it had an ‘ugly’ default webserver login page that looked like this

database_monitor.png

Database Monitor – Old Login Screen

and the home page looked like this

dbaas_home.png

Database Monitor – Old Version

the new version has a much cleaner HTML based login

dbaas_monitor_login.png

Database Login – New Login Page

and the home page looks much nicer

dbaas_monitor_new.png

Database Login – New Landing Page

To my eyes this is a much more visually appealing page.

As well as being visually updated, there has also been some functionality added, so for example I can now clone, unplug and plug PDB’s directly from this interface (I’m pretty sure you couldn’t before – although I could be mistaken).

clone.png

I have to say I’m pretty impressed by this update, whilst the visuals in no way change the underlying functionality (i.e. I can still perform most of these actions at the OS, DB level), having a visually pleasing UI gives another way to interact with the service.

Great job Oracle!

SQLcl the Future?

I’ve worked with SQLPlus for years and like many people who have used Oracle for a while you come to learn its strengths and weaknesses. However I must say that over the years SQLPlus has always been reliable but hasn’t exactly changed much.

However there’s a new kids on the block, enter SQLCL (or SQL Command Line), which is available here.

Why is SQLcl a contender to the venerable SQLPus? Well this feature alone makes it worthwhile:

  • Command History

that’s right, it’s 2016 and SQLPlus still doesn’t let you recall previous commands easily (there are ways to do it, but nothing as simple and analogous to the BASH history command)

However, it’s some of the other features that really set it apart, how about –

  • Doing a ‘Create Table As Select’ in a single CTAS command
  • Generating the DDL for database objects using the DDL command
  • Being able to ALIAS your own commands (incredibly useful for creating shortcut commands for things you frequently type)

I really encourage you to try out SQLcl, it’s going to be a HUGE productivity gain over SQLPlus.

Oracle Cloud – Using dbaascli

One of the nice features of Oracle Cloud is that it comes with some additional command-line tooling in the form of the dbaascli command.

When you run this command (while logged into your  DBaaS instance) you’ll see

[oracle@DEMO ~]# dbaascli
DBAAS CLI version 1.0.0
DBAAS>

This dumps you into an interactive shell, typing ‘help’ gives us a bit more info.

DBAAS>help
Help for dbaascli
dbaascli is a command line interface for different tools to be used with Oracle Cloud DB.
This command line interface supports logging, command history and autocomplete.
Logging is done on a session level. Each time dbaascli is run the last log is overwritten.
To access command history use the up/down arrow keys as you would normally do in a terminal.
Autocomplete uses the Tab key. Autocomplete will complete to the longest common string in case there is not an exact match.
To obtain help on a command use the help keyword at the begining or end.
Autocomplete is supported if the first word is the "help" keyword.
Special commands are available.
The list of special commands is:

-H
 -HELP
 ? - Get dbaascli help
 CLEAR - Clear the screen
 HELP - Get dbaascli help
 HIST - Print command line history
 HISTORY - Print command line history
 LIST - List available commands
 Q - Exit Command Line Interface
 QUIT - Exit Command Line Interface

DBAAS>

Note – you can also run the command non-interactively by specifying the options on the command-line.

So, let’s list the commands available.

DBAAS>list

Very Long Text, press q to quit
Available commands:
 
database bounce
database changepassword
database start
database status
database stop
dataguard failover
dataguard reinstate
dataguard status
dataguard switchover
dbpatchm
dv off
dv off cdb
dv off pdb
dv on
dv on cdb
dv on pdb
gg setup
gg status
glassfish start
glassfish status
glassfish stop
listener bounce
listener start
listener status
listener stop
mrec

netsec config
netsec config encryption
netsec config integrity
netsec deconfig
netsec deconfig encryption
netsec deconfig integrity
netsec status
netsec status encryption
netsec status integrity
ore setup

orec
tde config
tde rotate
tde rotate masterkey
tde status
tfa install
tfa uninstall


DBAAS>

Most of these commands are wrappers around other commands but it does give you one “go-to” centralised command which is consistent across all your DBaaS instances rather than trying to remember each command (and perhaps having to deal with different configurations).

So let’s try a few commands, this time I’ll run them non-interactively (type ‘quit’ to exit the interactive version).

Database Commands

Let’s check the database status

[oracle@DEMO ~]$ dbaascli database status
DBAAS CLI version 1.0.0
Executing command database status
Database Status:
Database is open
Database name: ORCL

Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

You could leverage this script to perhaps grep for the phrase ‘Database is open’ to check for problems.

We could also do the commands

dbaascli database bounce
dbaascli database start
dbaascli database stop

Glassfish Commands

Lets check Glassfish

[oracle@DEMO ~]$ dbaascli glassfish status
DBAAS CLI version 1.0.0
Executing command glassfish status
Unable to run command. This command should be run as user: root. Currently: oracle

Hmmm ok, so depending on the command we need to run it as different users, lets switch to root and try it again

[root@DEMO ~]# dbaascli glassfish status
DBAAS CLI version 1.0.0
Executing command glassfish status
Calculating the status and metrics:
Authentication failed for user: admin
with password from password file: /root/statuspwd.txt
(Usually, this means invalid user name and/or password)
Authentication failed for user: admin
with password from password file: /root/statuspwd.txt
(Usually, this means invalid user name and/or password)
domain1 running
Command list-domains executed successfully.
Command list-http-listeners failed.
Command uptime failed.

 

Notice the error message stating it’s reading the /root/statuspwd.txt file to get the password. Once I entered the password there and re-ran the command worked (I’ll omit the output here as it’s quite lengthy).

Listener Commands

Let’s check the listener

[root@DEMO ~]# dbaascli listener status
DBAAS CLI version 1.0.0
Executing command listener status
Invalid parameter(s)
Command Error
Command: listener status
Listener Status

Ok, this is getting annoying, maybe I need to be the oracle user for this one?

[root@DEMO ~]# su - oracle
[oracle@DEMO ~]$ dbaascli listener status
DBAAS CLI version 1.0.0
Executing command listener status
Starting listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-MAY-2016 13:15:50

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 4-JAN-2015 23:52:34
Uptime 1 days 13 hr. 23 min. 15 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/DEMO/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DEMO.compute-ae.oraclecloud.internal)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.1.0/dbhome_1/admin/ORCL/xdb_wa
llet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "ORCL.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "ORCLXDB.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
Service "pdb1.ae.oraclecloud.internal" has 1 instance(s).
 Instance "ORCL", status READY, has 1 handler(s) for this service...
The command completed successfully

So that worked, obviously the output is quite lengthy, but again you could see how this simple command could be used in a monitoring script.

Command Specific Help

If you’re unsure what a particular command might do, rather than risking running it, you can get some help on each command. For example what does that mrec command do?

[oracle@DEMO ~]$ dbaascli mrec help
DBAAS CLI version 1.0.0
Executing command mrec help
Command: mrec
Help for MREC tool.
Parameters:
 --args Format: Not defined Required: NO

Usage:
 mrec --args -oss_cfgfile <cfgfile> -old_hostname <vm_name>
 mrec --args -oss_cfgfile <cfgfile> -old_hostname <vm_name> -sid <oracle_sid>
Where:
 -help = Print this help.
 -oss_cfgfile = full path to OSS config file.
 -old_hostname = Name of the host to recover.
 -sid = ORACLE SID of the DB instance to recover. Default is orcl
Examples:
 mrec --args -oss_cfgfile /var/opt/oracle/mrec/oss.cfg -old_hostname vmprod01
 mrec --args -oss_cfgfile /var/opt/oracle/mrec/oss.cfg -old_hostname vmprod01 -sid orcl

Ok, so it doesn’t tell me what it actually does (I had to search the documentation to figure out MREC = Media Recovery), but it does at least tell you what parameters it expects.

 

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.