Saturday, 20 January 2018

Oracle Cloud : Database as a Service (DBaaS) - Create Service

This article provides a run through of creating a new DBaaS service on the Oracle Cloud.

◉ Create SSH Key


Before you start, you are going to need a key pair for authentication to your service.

$ ssh-keygen -b 2048 -t rsa -f myOracleCloudKey
$ chmod 600 myOracleCloudKey*

Enter and confirm the passphrase when prompted. You will be asked to upload the public key during the service creation.

If you have any problems, or need instructions for using PuTTYgen on Windows

◉ Create Service


Log into your Oracle Cloud "My Services" dashboard. Scroll down to the "Oracle Database Cloud Service" section and click either the title, or the "Open Service Console" link.

On the "Oracle Database Cloud Service" page, click the "Create Service" button.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

The next page allows you to enter some details about the service, including the following.

➪ Subscription Type : "Oracle Database Cloud Service" or "Oracle Database Cloud Service - Virtual Image". Pick the first.
➪ Software Release : 11gR2, 12cR1, 12cR2
➪ Software Edition : Standard Edition, Enterprise Edition, Enterprise Edition - High Performance, Enterprise Edition - Extreme Performance. Check the definitions of the options included in the Enterprise Edition variants.
➪ Billing Frequency : Hourly, Monthly

Once you are happy with your choices, click the "Next" button.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

Enter the service details. The shape determines the number of virtual CPUs and memory associated with the service, so pick a shape that is relevant to your performance needs. Enter the database configuration details and your SSH key. Pick the backup configuration appropriate to your system. When you are happy with the configuration, click the "Next" button.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

If you are happy with the service setting listed on the "Confirmation" screen, click the "Create" button.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

Wait while the new service is created. The progress is shown under the status.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

Once complete, the status disappears.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

The service hamburger allows you to navigate to a number of management tools. You will need to amend the firewall rules to access these.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

Click on the service name to drill down into the service. The detail page gives basic information about the service, including the public IP address and the database connection string.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

The hamburger gives you basic management operations (Start, Stop, Restart, Scale Up/Down) for the service.

Oracle Cloud, Oracle Database, Database as a Service, Oracle Database Certifications, Oracle Database Tutorials and Materials

If there are any patches available for your database, they will be displayed in the "Administration" section.

◉ Connecting to the VM using SSH


Most of the time you will probably be connecting to the "oracle" operating system user. You do this by specifying your private key and connect to the "oracle" user on the public IP address from your service detail page.

$ ssh -i ./myOracleCloudKey oracle@123.123.123.123

[oracle@obtest1 ~]$

Once connected, you can do all the usual stuff.

[oracle@obtest1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 8 09:44:42 2016

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

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

SQL> ALTER SESSION SET CONTAINER = pdb1;

Session altered.

SQL> CREATE USER test IDENTIFIED BY test;

User created.

SQL> GRANT CREATE SESSION TO test;

Grant succeeded.

SQL>

If you need to perform any tasks as root, you must connect to the "opc" user and run them using "sudo".

$ ssh -i ./myOracleCloudKey opc@123.123.123.123
-bash-4.1$ sudo vi /etc/hosts

◉ Oracle Compute Cloud Service - Network (Firewall)


The DBaaS services are run under the Oracle Compute Cloud (IaaS). This has it's own firewall configuration, allowing you to limit access to your services. By default, all endpoints except SSH are disabled. There are a number of predefined "Security Rules" to open up the assorted endpoints, but they typically open the endpoints to public, which is rather risky. Instead, you should define custom rules, opening access to ports from specific machines.

◈ Navigate to the main "Oracle Database Cloud Service".
◈ Click on the hamburger next to the service of interest.
◈ Click the "Access Rules" option on the popup menu.
◈ Click the "Create Rule" button.
◈ Enter a "Rule Name".
◈ Select "" as the "Source" and enter your IP address in resulting box.
◈ Select "DB" as the "Destination".
◈ Enter "1521" as the "Destination Port(s)".
◈ Leave "TCP" as the "Protocol".
◈ Click the "Create" button.

You should now be able to connect to the database from the specified IP address.

You will need to do a similar process for the other tools you want to connect to, like APEX, DB Express etc.

◉ Connecting to the database using Oracle Net


The "sqlnet.ora" file on the server contains the following entries, so any connections to the server are encrypted using Native Network Encryption by default.

SQLNET.ENCRYPTION_SERVER = required
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)
SQLNET.CRYPTO_CHECKSUM_SERVER = required
ENCRYPTION_WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/cdb1/tde_wallet)))
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256, AES192, AES128)
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
SQLNET.WALLET_OVERRIDE = FALSE
SQLNET.EXPIRE_TIME = 10
SSL_VERSION = 1.0
WALLET_LOCATION = (SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/cdb1/db_wallet)))

SQL*Net access is disabled by default, but you can enable it as described above. Once enabled, create a local "tnsnames.ora" entry as follows. The connection details are available from your service detail screen.

pdb1_oc=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=123.123.123.123)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVICE_NAME=pdb1.my-identity.oraclecloud.internal)
    )
  )

Now you can connect to the database.

C:\>sqlplus test/test@pdb1_oc

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 26 14:40:23 2015

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


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

test@cdb1>

Alternatively, connect using the EZconnect URL.

C:\>sqlplus test/test@123.123.123.123:1521/pdb1.my-identity.oraclecloud.internal

SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 26 14:42:31 2015

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

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

SQL>

◉ Notes


1. Assuming your public IP address were 123.123.123.123 and you've opened the relevant services on the firewall, the following URLs are available.

DBaaS Monitor: https://123.123.123.123/dbaas_monitor/
Username: dbaas_monitor
Password: (set during installation)

APEX: https://123.123.123.123/ords/pdb1
Workspace: INTERNAL
Username : ADMIN
Password : (set during installation)

Glassfish: https://123.123.123.123:4848/common/index.jsf
Username : admin
Password : (set during installation)

DB Express : https://123.123.123.123:5500/em

2. The database created by the "Oracle Database Cloud Service" option does not have multiplexed redo logs, so you will need to sort this manually.

3. The "Oracle Database Cloud Service - Virtual Image" option says there is a software installation present, but this is not the case. Instead, there is a tarball containing the software (/scratch/db12102_bits.tar.gz). To do the installation, you would need to Up Scale the service to add storage, then do the installation manually. I see no value in this offering. I would rather provision a regular compute node and do everything manually.