Monthly Archives: November 2015

Oracle Cloud – Connecting to your APEX Instance

Following my last blog post, now you have an Oracle Cloud DBaaS Instance up and running, you might want to access your (pre-installed) APEX instance.

First step to doing this is to open the Firewall port to allow HTTP and HTTPS access.

The network rules for ora_p2_http and ora_p2_httpssl need to be enabled (HTTP should redirect to HTTPS).


Type in your public IP address into your browser and it should take you to the home page for your instance. Note depending which browser you use you might get an SSL warning due to the self signed certificate.


In Chrome – you can click the ‘Advanced’ link (which isn’t obvious you can click on it), and then you can ignore the warning about the self-signed certificate and continue navigation to the site.


Success! You can navigate to the various options (like Database Monitor, APEX etc) from here. Note in a production system you might not want this page open to the world and we’ll discuss options for doing that in a later blog post.


Oracle Cloud DBaaS – Connecting to the Database

Once you have your Oracle Cloud DBaaS service up and running and you can connect to it via SSH, then the next step is connecting to your database.

This is where things get a little confusing at first (or at least I got more than a little confused). Because let’s imagine you want to connect SQL Developer running on your laptop to the Oracle Database, there’s a couple of ways we can do that

  1. Open up the database so that port 1521 is open to the world (really bad idea – don’t do that!)
  2. Use an SSH tunnel from your laptop to the DBaaS service

Let’s say for the sake of argument you want to do option #1, how do we open up the firewall to allow external access to port 1521?

Well – again this is where I found it slightly confusing and maybe not the most logically laid out. After a bit of searching I found you need to go to the Compute Service rather than the DBaaS Service console. When you created a DBaaS Service a Compute Service (i.e. a virtual machine) will have been created for you, so instead of configuring the network rules and filtering at the DBaaS level you configure them at the Compute Service level. This does make sense when you think about it, but it would be nice if I could jump directly to the network rules from the DBaaS Service console.


here I’m filtering for the rules just for my DEMO instance.

Opening Port 1521 to the Public Internet (PLEASE DON’T DO THIS!!!)

So, firstly lets try and connect from my laptop to the DBaaS instance using SQLPlus:

[jes@AEMBP ~]$ sqlplus system@\'<>:1521/\'

SQL*Plus: Release Production on Fri Nov 7 14:28:26 2015

Copyright (c) 1982, 2012, Oracle. All rights reserved.

Enter password:

ORA-12170: TNS:Connect timeout occurred

notice that I’m using Easy Connect syntax so I can connect using an IP address and don’t need to setup a TNSNames etc.

The connection hangs because there’s no path through the firewall to port 1521.

Now let’s open up the firewall to allow access to port 1521. To do this click the little ‘hamburger’ menu option next to the rule for ora_dblistener (unfortunately this page does not display the port numbers which would be helpful).


Let’s update that rule to enable it:


Now lets try and login via SQLPlus again

[jes@mac ~]$ sqlplus system@\'<>:1521/\'

SQL*Plus: Release Production on Fri Nov 7 14:39:51 2015

Copyright (c) 1982, 2012, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c EE Extreme Perf Release - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options



Ok, now go back to the rule and disable it! Immediately! Please don’t have port 1521 open to the public internet. There’s much better ways to do this in SQLPlus using a custom SSH Tunnel or by using SQLDeveloper (which we’ll blog about later).

So now we know the basics of opening network ports and connecting to the DBaaS database remotely.

Use an SSH Tunnel Instead (Please!)

Ok, assuming you’ve now closed port 1521 to the public internet, let’s see how to use an SSH tunnel to connect via SQLPlus securely to our DBaaS instance.

We can create an SSH tunnel by starting a new terminal window (I’m on a Mac, which means essentially the same command would work on a Linux machine). If you’re on Windows you could use PuTTY or other software to create your tunnel.

So in a terminal window I can run:

[jes@mac oracle-cloud]$ ssh -L 10521:localhost:1521 -i oracle_cloud_rsa opc@<>
[opc@DEMO ~]$

the syntax here opens up a port on my local machine (10521 – you can choose any number you like) which will be tunnelled over the SSH connection and connect to port 1521 on ‘localhost’ where in this case localhost represents my DBaaS instance.

This maintains an open session on the DBaaS instance and my tunnel will be valid for as long as this connection is active (if I close the session then the tunnel will end).

So now lets re-run the SQLPlus connection and try to connect via the tunnel:

[jes@mac ~]$ sqlplus system@\'localhost:10521/\'

SQL*Plus: Release Production on Fri Nov 7 17:28:52 2015

Copyright (c) 1982, 2012, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c EE Extreme Perf Release - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options


Success! Notice how I change the hostname / ip address to be ‘localhost’ (i.e. my laptop) and the local port to be 10521, i.e. connect to the SSH tunnel I just created.

This is a really nice way to not have to open up your DBaaS instance to the world but still enjoy SQLPlus connections from your local machine (or any other machine which can SSH to the DBaaS instance).

It’s worth pointing out that SQL Developer has SSH Tunnelling built in, you can read more on Jeff Smiths blog here.



Oracle Cloud – Logging into your instance via SSH

In my last post I showed how easily we could create a new instance, after the machine has initialised you should be able to login using the SSH key

We can find out the public IP address for our instance from the Services dashboard and drilling into the Service by clicking the name.



note – I’ve obscured my Public IP here for obvious reasons.

So now that I know the Public IP, I can try and connect via SSH using the private key I created in the earlier post.

[jes@mac oracle-cloud]$ ssh -i oracle_cloud_rsa root@<>
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).

I use the -i parameter to SSH to use my private key and pass my IP address, but it doesn’t work. Hmmm, after quickly checking the docs I see that the SSH key doesn’t correspond to the root user but rather the ‘opc‘ user (where presumably ‘opc‘ represents Oracle Public Cloud).

So let’s try again, using the opc user this time:

[jes@mac oracle-cloud]$ ssh -i oracle_cloud_rsa opc@<>
[opc@DEMO ~]$

success (and a very underwhelming login message)! We’re now SSH’d into our Oracle DBaaS service.

So let’s run a few basic system commands what we have here:

[opc@DEMO ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvdb3  25G   16G  7.8G 68% /
tmpfs      3.6G  2.0G  1.6G 56% /dev/shm
/dev/xvdb1 477M  148M  300M 34% /boot
/dev/xvdc1  30G  8.3G   20G 30% /u01
 99G 4.0G 90G 5% /u02
 9.8G 131M 9.1G 2% /u03
 9.8G 3.1G 6.2G 33% /u04

So you can see the standard installation creates a few Oracle specific mount points (/u01, /u02, /u03 and /u04).

My first observation was that I would have preferred /home to be on a seperate mount as I’m always slightly paranoid about the root filesystem filling up (as most verteran Linux / Unix admins are). Personally I think 7.8Gb free in the root filesystem is just a little too low for a production system and I’d like to see

  • An option for putting /home on a seperate mount
  • Ability to tweak the default sizes for / and /home

(just as Amazon AWS does)

Ok, so that’s the disk, what about the CPU? Running the ‘top’ command shows me the following output.



Remember I opted for 1 OCPU, but that each OCPU gives me 2 threads (vCPU’s), that’s why I see Cpu0 and Cpu1 in the output (if I’d gone for 4 OCPU’s then I’d see 8 CPU’s listed in top).

I can see what type of CPU’s they are reported to the OS by querying /proc/cpuinfo (remember this is a virtualised environment so the information might not be strictly ‘correct’, but should be a good indication).

[opc@DEMO ~]$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 62
model name : Intel(R) Xeon(R) CPU E5-2690 v2 @ 3.00GHz
stepping : 4
microcode : 0x428
cpu MHz : 2992.846
cache size : 25600 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx rdtscp lm constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 cx16 pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm xsaveopt fsgsbase smep erms
bogomips : 5985.69
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
...extra duplicate output for processor 1 omitted...

So we can see we have 2 Intel Xeon E5-2690 v2 CPU’s running at 3.00Ghz (or at least reporting that they are).

Not much else to say here, once you’ve SSH’d into the machine it’s just like any other machine. In the next post we’ll cover connecting to the Oracle DB.


Oracle Cloud – Creating an Instance

In this post I’m going to walk through creating a new DBaaS instance with your Oracle Cloud account. As a reminder you can sign up for a free Trial account (without any credit card details having to be entered – so I encourage you to head over to and sign up for a free trial).

As you can see, I already have one instance created and running in my account:


Since this is a trial account, I’ve been testing out the Enterprise Edition – High Performance option.

However let’s go ahead and create a new Instance, by hitting the ‘Create Service’ button (logically enough!).


DBaaS Create Service – Step 1

I need to decide if I want an instance based on the Oracle Database Cloud Service, i.e. one where the database is pre-installed and I also get some cloud tooling (command-line tools etc) to help manage the instance, or do I want a Virtual Image where I have responsibility for installing everything.

Also I get to choose the billing frequency – since this is a Trial account I’ll go ahead and choose Oracle Database Cloud Service and Monthly Billing (although hopefully I won’t be billed anything!).



DBaaS Create Service – Step 2

This step is pretty self-explanatory, do you want to use 11gR2 or 12cR1?

In the next step, we choose the Edition


DBaaS Create Service – Step 3

since this is a Trial account, let’s go all-in and choose Enterprise Edition – Extreme Performance (which gives us the In-Memory option).

Depending which option you select, the options on the next screen will vary slightly, however you should see something similar to:



DBaaS Create Service – Step 4

Lots of options here, most of them self-explanatory, but let’s go through some of the key ones.


shape.pnghere you are essentially selecting the number of OCPU’s and the memory you want.

the key thing to bear in mind is that it’s relatively easy to scale up later on, but not so easy to scale down (at least using the built in tooling). So choose wisely!


SSH Public Key

ssh-public-key.pngIn order to connect to your instance later on you’ll need to generate a public key (or use an existing one).

This key will then be used to authenticate to your instance via SSH without requiring you to enter a password .


So how do you generate an ssh key? Well on my Mac, I went to a terminal and typed the following commands:

[jes@mac oracle-cloud]$ ssh-keygen -t rsa
Generating public/private rsa key pair
Enter file in which to save the key (/Users/jes/.ssh/id_rsa): ./oracle_cloud_rsa
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in ./oracle_cloud_rsa.
Your public key has been saved in ./
The key fingerprint is:
SHA256:l0ISI/J6AFTt/n5n2mbhAPguUYGOK9mm0+afBxAHIa4 jes@mac
The key's randomart image is:
+---[RSA 2048]----+
|o.+.*oo |
| o =.+.o |
| =o+oo . |
| o+=++ o . |
|E+o+=.. S o |
|o..o.o.. + |
|. o..oo . |
| . .o.o* . |
| . .+=.o |
[jes@AEMBP oracle-cloud]$ ls -al
total 16
drwxr-xr-x 4 jes staff 136 12 May 00:25 .
drwxr-xr-x 102 jes staff 3468 12 May 00:24 ..
-rw------- 1 jes staff 1679 12 May 00:25 oracle_cloud_rsa
-rw-r--r-- 1 jes staff 397 12 May 00:25

(FYI don’t try and use the above information – it was just a test and not used for a real key!)

The important bit here is making sure you enter a different path to generate the file (otherwise you risk overwriting any default keys you have). Once I’ve generated my private key (the one without the .pub suffix) and my public key (the one with the .pub suffix) I can upload the public key to be used by my Oracle instance.

Database Configuration


There are some obvious and not so-obvious things in this section.

For example, admin password, DB SID, PDB name are all quite obvious.

But what about Usable Database Storage and Total Data File Storage?



One thing I don’t like in this section is that it’s possible to enter values in the Storage fields that will be accepted, but will (later on) make you exceed your allowed quota.

More than a few times, I entered what I thought were sensible values to be alerted a day or so later that I’d exceeded my storage quota and my instance had been suspended (in reality it didn’t seem to be suspended at all and I could still access it). But still – I feel there should be better validation on these items. After all, why would I NOT want to allocate the maximum amount of space (but not exceed it). It’s not immediately obvious what the optimal value is to enter in these fields.

Backup and Recovery

backup_1.pngThis looks like a fairly innocuous section, but this is where I became most confused.




For my backup strategy I’m allowed to select:

  • None
  • Cloud Storage Only
  • Both Cloud Storage and Local Storage

note that previously there was an option for Local Storage only, but that seems to have been removed (or maybe it’s dependent on the Edition you select)

Ok, I want to backup – so I need to enter my Cloud Storage Container details, but what are those and where can I get them from?

At this point I swopped a lot of emails with the extremely helpful Brian Spendolini who helped me to understand that I needed to have created my Cloud Storage manually first using REST Requests (I used CuRL commands). This is definitely an area that is screaming out for a UI to be added to the Cloud Console to allow me to do it from there. I don’t like that I’ve filled out a load of information in the Create Service wizard already and now I need to bail out of the UI and run some command line commands to create the necessary Cloud Storage account and containers before I can continue. Anyway, I’ll stay tuned and hopefully this is an area they’ll improve on soon.

For the purposes of this example, I’ll chose No Backups (PLEASE don’t do this yourself unless you really want to!). Which allows me to progress and create my instance.

Clicking the final ‘Create’ button in the wizard should return you to the Dashboard page where you’ll see your new instance listed amongst any existing ones you have.


Note in my case I already had another instance running so the figures for OCPU’s, memory and storage is the aggregate of both my instances.

The instance will sit in the ‘In Progress’ status for some time – my experience varied anywhere from 30 minutes to a few hours (although this seemed to be early on in the trial and it seems faster now).

Clicking on the name of the Instance will take you to the details page where you get a bit more information, including ‘In-Progress Provisioning Messages’ telling you at what stage in the provisioning process it is (e.g. starting the machine, installing the DB etc). Once the machine is up and running you should be able to ssh into it while the rest of the installation is progressing.



and that’s it! You now have a DBaaS instance created and (almost) up-and-running. In the next post I’ll show how to connect to the instance and poke around what has been installed by default.