Blog

Oracle Cloud, going down the rabbit hole – Part 3

Share this article

So far we have created our Oracle Cloud Account, set a storage georeplication policy, created a backup user and private IP subnet.
Now, it is time to create our DBaaS guest.

Oracle DBaaS guest creation

From the dashboard page, open the “navigation menu” and click on “Database”.

Navigation window - DBaaS

As this is our first guest, we are first getting a welcome page.
Use the “Go to Console” url to open the service console.

Oracle DBaaS - goto console

In the service console, you can get an overview of the DBaaS guests, control their status and create new guests.
Click on the “Create Instance” button.

Oracle DBaaS - create instance

In the first instance creation page, specify a name for the guest and the address to which the notification mail needs to be send.
The region will depend on your location.
In the IP Network box, select the ip reservation you created in part 2 of this series and check the “Assign Public IP” box.

The database type depends on the chosen software edition.
With “Standard Edition”, the only available database type is “Single Instance”. If you want to setup a Data Guard standby (with either the primary in the cloud or on premise) you need to choose at least “Enterprise Edition”.
For RAC, you need “Enterprise Edition – Extreme Performance”.

As I don’t want to spend all my free credits at once, I’m picking the “Standard Edition” option for the Edition and thus the “Single Instance” as Database Type.

Oracle DBaaS - create instance part 1

In the next screen, you specify more database details.
Even with a Standard Edition, you will always get a container database. Provide a name for both the container and the PDB instance.
As this is just a small POC, I picked the smallest shape, with only 1 OCPU.

For the backup destination, I selected the “Cloud Storage Only” option.
This will setup an RMAN job in crontab to backup the archived redo logs every hour and the database once a day. The backup pieces will be placed into the specified storage container.
The cloud storage container can be an existing container or you can opt to create a new one during the guest creation.
Check the “Create Cloud Storage Container” option and provide the credentials of the dedicated backup user we created in part 1.

Oracle DBaaS - create instance part 2

Click on the “Edit” button next to the “SSH Public Key” option.
If you have an existing ssh key, you can either upload the public key file or copy / paste the content.
If not, you can let Oracle generate a new public / private key pair for you.
You can also use the following guide on how to create the necessary ssh keys (on linux or windows).

Oracle DBaaS - add SSH key

The other fields can be left at their default value.

Oracle DBaaS - post SSH key addition

Click ‘next’ to get a summary of your choices.
Note that at this point, your storage container will already be created.

Oracle DBaaS - create instance part 3

If everything is ok, click on “Create” to start with the instance creation.
This will take some time and you will get an email when the guest creation has finished.

Oracle DBaaS - instance creation in progress

Once the instance has been created, you can control its status from the service console, using the options in the hamburger menu next to the instance.
You can also click on the instance to get more detailed information about it.

Oracle DBaaS guest details

Logging in to your guest

Ok, cool. You just created your first DBaaS guest.
Now what?

To be able to do something useful, you need to be able to login on the guest (and the database).
By default, the guest has ssh (port 22) open to public internet. Everything else is blocked.

Authentication is done via the ssh key you specified during guest creation. You can login as either oracle or opc.
The latter can use sudo to switch to root (yes, you have full os access, which is nice).
Check the public ip for the guest in the details page (see above) and use your favorite ssh client to connect to the guest.

dhoogfr@dhoogfr-lpt1 ~ $ ssh -i ~/.ssh/id_dhoogfr opc@130.162.100.152
*** OBKUP::ERROR INFO ***
OBKUP::ERROR Cannot complete the Archivelogs Backup to Cloud Storage
OBKUP::ERROR KBHS-00715 HTTP CONNECTION ERROR
OBKUP::ERROR An http error was detected please verify your connectivity
*** OBKUP::ERROR END ***

[opc@lab-db-01 ~]$ hostname
lab-db-01

[opc@lab-db-01 ~]$ ps -ef | grep pmon
oracle    3240     1  0 17:59 ?        00:00:00 ora_pmon_labcon
opc      14850  5300  0 22:14 pts/0    00:00:00 grep pmon

[opc@lab-db-01 ~]$ sudo -i
[root@lab-db-01 ~]# whoami
root

[root@lab-db-01 ~]# ip addr show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 8900 qdisc mq state UP qlen 1000
    link/ether 02:4e:e7:2c:6f:7d brd ff:ff:ff:ff:ff:ff
    inet 192.168.6.2/29 brd 192.168.6.7 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::4e:e7ff:fe2c:6f7d/64 scope link 
       valid_lft forever preferred_lft forever

This shows that I was able to login using my private key, that a database is running, that I could switch to root and that I have a private ip address from the ip network that we created.

Note 2 other things:
One is that the public ip address is not plumbed on the guest.
This is normal. The public IP exists only in the software defined network that is part of the Oracle Cloud.
The other thing is that we encounter an error message concerning our database backups (which is not great).
I’m not going to discuss the way database backups work within DBaaS (that would be a blog post on its own), but to fix this you need to add the _OPC_VALIDATE_CERT=TRUE clause to the ${ORACLE_HOME}/dbs/opc${SID}.ora file.

dhoogfr@dhoogfr-lpt1 ~ $ ssh -i ~/.ssh/id_dhoogfr oracle@130.162.100.152
*** OBKUP::ERROR INFO ***
OBKUP::ERROR Cannot complete the Archivelogs Backup to Cloud Storage
OBKUP::ERROR KBHS-00715 HTTP CONNECTION ERROR
OBKUP::ERROR An http error was detected please verify your connectivity
*** OBKUP::ERROR END ***

[oracle@lab-db-01 ~]$ cat /u01/app/oracle/product/12.2.0/dbhome_1/dbs/opclabcon.ora 
OPC_HOST=https://fdhlabs.eu.storage.oraclecloud.com/v1/Storage-fdhlabs
OPC_WALLET='LOCATION=file:/u01/app/oracle/admin/labcon/opc_wallet CREDENTIAL_ALIAS=alias_opc'

OPC_CONTAINER=labcon_bck

_OPC_DEFERRED_DELETE=true
 
[oracle@lab-db-01 ~]$ echo _OPC_VALIDATE_CERT=TRUE >> /u01/app/oracle/product/12.2.0/dbhome_1/dbs/opclabcon.ora 
 
[oracle@lab-db-01 ~]$ cat /u01/app/oracle/product/12.2.0/dbhome_1/dbs/opclabcon.ora 
OPC_HOST=https://fdhlabs.eu.storage.oraclecloud.com/v1/Storage-fdhlabs
OPC_WALLET='LOCATION=file:/u01/app/oracle/admin/labcon/opc_wallet CREDENTIAL_ALIAS=alias_opc'

OPC_CONTAINER=labcon_bck

_OPC_DEFERRED_DELETE=true
_OPC_VALIDATE_CERT=TRUE

At this point we can login to the guest, but the database itself is not accessible from the outside.
One way to work around this is to setup an ssh tunnel.
With an ssh tunnel, data sent to a local port is transported over ssh to the remote server, where it is send to the destination port.
Check this blogpost to learn a lot of possibilities on port forwarding.

This time we need to use the format: ssh -f oracle@<DBaaS Public IP> -L 2000:<DBaaS private IP>:1521 -N
After that we can connect with sqlplus on port 2000 of our local host.

dhoogfr@dhoogfr-lpt1 ~ $ ssh -f oracle@130.162.100.152 -L 2000:192.168.6.2:1521 -N 

dhoogfr@dhoogfr-lpt1 ~ $ sqlplus sys@\"127.0.0.1:2000/labcon.598838623.oraclecloud.internal\" as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Mar 23 23:56:48 2018

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

Enter password: 

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production


INSTANCE_NAME    HOST_NAME                      STATUS       ROLE
---------------- ------------------------------ ------------ -------------------------------------------------------
labcon           lab-db-01                      OPEN         PRIMARY

sys@LABCON> @list_pdbs

NAME                           OPEN_MODE  RESTRICTED OPEN_TIME_STR              TOTAL_SIZE_GB
------------------------------ ---------- ---------- -------------------------- -------------
CDB$ROOT                       READ WRITE NO         23/03/2018 17:59:41 +00:00           ,00
FDHT                           READ WRITE NO         23/03/2018 17:59:47 +00:00          1,39
PDB$SEED                       READ ONLY  NO         23/03/2018 17:59:41 +00:00          1,30

sys@LABCON> exit
Disconnected from Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

Note that Oracle will add a domain part to your service name, consisting out of the service instance ID of the underlying IaaS guest (not of the DBaaS guest itself) and “oraclecloud.internal”

Tools such as SQL Developer, also have an option to create the ssh tunnel for you when connecting to the database.

Summary

In this episode we created our first DBaaS guest, learned how to logon to the guest and how to use ssh port forwarding to connect to the database from our client.
Next time we will look how to manage the access rules for the DBaaS guest.

Tags: Blog
Oracle Cloud, going down the rabbit hole – Part 4
Oracle Cloud, going down the rabbit hole – Part 2

You May Also Like