Cloud Database

Using the Oracle Cloud with our Oracle JET applications

Welcome to the 2nd article on our new Blog series about Oracle JET and Oracle Cloud.

Today we’ll be working on two main subjects:

  • Setup our Oracle Cloud account, as well as the cloud services we need (Database, Storage. The Application Runtime Cloud Service will be configured in another article and maybe a few others along the way…)
    • If you already have an account and configured the necessary services, you can skip this.
  • Configuring our Oracle SQL Developer to connect to our cloud Database.
  • Create a test table in the Cloud.

Without further ado, let’s dig into the cloud.

Setup your cloud account

The first thing you need to do is to create your Oracle Cloud account.

You can go to Oracle Cloud’s homepage at https://cloud.oracle.com and either buy services outright or opt for a trial. In our case, we’ll go for the trial option. Click that green “Try for Free” button.

The Oracle Cloud homepage

The Oracle Cloud homepage

You’ll be taken to the trial page, where you can create your free account (use the “Create a Free Account” button). This page also has some estimations on how much time will the free 300 USD cloud credits grant you. From my personal experience, those estimations are way off the mark. The real numbers are much lower.

Trial page

Trial page

Anyway, once you click the “Create a Free Account” button, the site will take you to a sign-up form, where you’ll fill in your information and supply a mobile number for verification. Please take into account that you must provide a real mobile number, as Oracle will send you a confirmation code that you’ll need to put on the sign-up form. Also, pay attention to the Default Data Region: you should select the data center that is closest to you, to increase the overall performance.

The sign-up form

The sign-up form

The verification step

The verification step

Once the account is verified, you’ll be able to add a payment method. As explained, it will not charge you anything, but it’s a required step. Just place your credit card information and billing data and then accept the terms and conditions.

Payment and Terms

Payment and Terms

And you’re good to go (you’ll be as soon as the account is prepared, which can take up to 15 minutes, but usually takes 2). Just wait for the confirmation e-mail, which will also give you your Oracle Cloud access details (credentials).

Mail coming from Oracle - Ready to start

Mail coming from Oracle – Ready to start

Now your account is up and running. Time to add a few cloud services and start to spend those free 300 USD. Click on that “Get Started with Oracle Cloud” button, and it will take you to the sign in screen. Fill in with the Access Details supplied in the e-mail. Don’t worry… it will require you to change the password immediately.

Oracle Cloud Login Screen - Place the credentials supplied in the e-mail that was sent to you

Oracle Cloud Login Screen – Place the credentials supplied in the e-mail that was sent to you.

New password screen - Respect the password rules shown in the screen

New password screen – Respect the password rules shown on the screen

You are then taken to the “My Services Guided Tour” and, for the sake of simplicity, just click on the Dashboard icon (either on the side menu or the top bar). You’re ready to start creating new cloud service instances.

Accessing the Cloud Dashboard

Accessing the Cloud Dashboard

The My Services Dashboard - Where it all begins

The My Services Dashboard – Where it all begins

As this information will be need later, take notice of the Identity Domain associated with your account. In my example, the domain is “ojetblog”. Take notice of what it is in your particular case.

Creating a new Cloud Service

Good! Your Oracle Cloud account is all set up and ready to be used. So now it’s time to create new services.

Creating a Storage Classic cloud service

As Storage Classic is something that most/all services will need, let’s just go ahead and create it. Click on the “Create Instance” option in the Dashboard and then choose the “Create” button in the “Storage Classic” area.

Create a new cloud service, in this case, the Storage service

Create a new cloud service, in this case, the Storage service

When you try to create a Storage service, it will ask you about the “georeplication” policy associated with that service. Select one from the available options. For real-life scenarios, please read through the guidelines for selecting a replication policy and make your choice according to what’s best suited for you.

Select a georeplication policy

Select a “georeplication” policy

Your Storage cloud service is now ready to be used.

Going back to the My Services Dashboard, you’ll notice that the Storage service you just created doesn’t appear on it. That’s because some cloud services are not automatically shown on the Dashboard. You can set it to show up using the “Customize Dashboard” option and selecting the ones you want to show. Locate the Storage Classic service and click on Show. Et voilá… the Storage service is now showing on your Dashboard.

Customize your dashboard to show up the services you want

Customize your dashboard to show up the services you want

The Storage Classic cloud service now showing up on the Dashboard

The Storage Classic cloud service now showing up on the Dashboard

Creating a Database Cloud Service

Once the Storage Classic service is set up, let’s try to create the Database Cloud Service. Once again, click on the Create Instance option on the Dashboard and then create a Database service.

Create an instance of Database cloud service

Create an instance of Database cloud service

The system now presents some QuickStarts (a few weeks ago, it didn’t) but, for the sake of education, we’ll go the hard way and choose the Custom option.

Database service Quickstart screen - Choose the Custom option

Database service Quickstart screen – Choose the Custom option

The Database Cloud Service set up wizard

The custom option will take you to a 3-step wizard, in which you’ll supply the necessary information to create your very own database in the cloud. The first step is where you define the basic characteristics of your database: Which version, edition, and type (single instance, RAC), as well as the name and on which region should it be created. For the region, try to choose the same as you did for the georeplication of the Storage service.

Creating a Database Cloud Service - Step 1 - Defining the basic characteristics of the service

Creating a Database Cloud Service – Step 1 – Defining the basic characteristics of the service

After this, we go into more detail and specify the characteristics of the machine (shape) that will hold our database, as well as backup and recovery configurations. Fill in the information in step 2, taking a few things into account:

  • Supply an SSH Public Key – This step allows me to create a new SSH key and download it. I always choose this way (instead of providing an existing one on my own)
  • Cloud Storage Container – This field will hold the Storage container URL. This has the following syntax:
    • <Storage Cloud Service REST Endpoint>\<container name>
    • The way to get the Storage Cloud Service REST Endpoint is to go your My Services Dashboard, click on the Storage service, and in the Overview information->Additional Information there’s the REST Endpoint there. Just copy it and use it. The container name is anything you like. I typically use dbcsbackup.
  • Create Cloud Storage Container – I choose this option to allow the system to automatically create a storage container for my backups
  • Backup and Recovery Configuration
    • Username and Password – Use your Oracle Cloud Account Credentials (the ones you use to log in to your My Services Dashboard)
Creating a Database Cloud Service - Step 2 - Defining the shape and backup options

Creating a Database Cloud Service – Step 2 – Defining the shape and backup options

You can click on Next and it will show your configurations and ask you for a confirmation to create the Database. Click on Create and you’re done.

Database service confirmation step - All that you need to do now is press Create

Database service confirmation step – All that you need to do now is press Create

You’ll be taken to the Database Services list where you can see your service being created. It will take more than a few minutes (around 30 minutes).

Database service creation process running. it takes a while

Database service creation process running. it takes a while

Database service started and running

Database service started and running

You now have your Database running in the cloud. How cool is that?!

We’ll skip setting up the other cloud services for now, as we’ll focus on the database in these first parts.

Onwards to connect our SQL Developer to our recently created Database Cloud Service.

Connecting the Oracle SQL Developer to our Database in the cloud

Begin by launching your Oracle SQL Developer. Once it’s launched, locate the connections window and create a new connection

Create a new connection in SQL Developer

Create a new connection with SQL Developer

Just fill in the form for creating a new connection with the following:

  • Connection Name – Anything you like. I used “Oracle JET DB Cloud”
  • Username – Any username of that database. For the sake of simplicity, and because we still didn’t create any specific users, I just used SYS.
  • Password – Remember filling in the Database Administration Password in the second step of configuring your Database Cloud Service? That’s the password you put here.
  • Save password (optional) – Just so I don’t have to type the password everytime I connect to the database.
  • Connection Color (optional) – I like to color code my Cloud connections in blue, but it’s just a personal choice. Do as you please.
  • Connection Type – Basic
  • Role – If you choose SYS as your username be sure to select the SYSDBA role.
  • Hostname – This is the public address of your Cloud Database. You can check it by accessing the detailed information of your Database cloud instance.
Accessing the Database Cloud Service Detailed information

Accessing the Database Cloud Service Detailed information

Database service detailed information - Check the Public IP and place it in the SQL Developer Connection dialog

Database service detailed information – Check the Public IP and place it in the SQL Developer Connection dialog

  • Port – Just leave the default 1521 (if you didn’t change it when configuring the database cloud service)
  • SID – Remember filling in the DB Name in the second step of configuring your Database Cloud Service? That’s what you put here.

 

Creating a connection to our cloud Database

Creating a connection to our cloud Database

 

Now Save it and Test it.

Error connecting to our cloud database

Error connecting to our cloud database

Oops… why can’t we connect to our cloud database? All configurations seem correct so… what’s wrong?

Tha’s because the database cloud instance has very strict access rules by default. I’ll check them out and see if I can understand what’s the matter. I’ll access the context menu of my Database cloud service and select the Access Rules option.

Check the database access rules

Check the database access rules

As you can see, most of the ports are blocked. In particular, the DB listener is also blocked. Go to the “Actions” menu of the ora_p2_dblistener and enable it. Notice that the icon changes and the red cross is no longer there.

Enabling the access to the db listener

Enabling the access to the DB listener

A few seconds later, the DB listener port is open. You can now Test the connection back in SQL Developer and … Success!

Success connecting to our cloud database

Success connecting to our cloud database

You’re now all set to use Oracle’s SQL Developer to create your artifacts inside your Cloud Database. Let’s do a small test to ensure that this really works.

Creating our first table in the cloud

The first thing we need to do is to create a user and its respective schema so that we can then create a table inside it. Let’s create a new SQL Worksheet and select the connection you created to the cloud database.

Create a new session with a SQL Worksheet - Either press Alt+F10 or click on the New Worksheet icon

Create a new session with a SQL Worksheet – Either press Alt+F10 or click on the New Worksheet icon

As you may know, there are now (since 12c and the concept of Containers and Pluggable databases) two types of “normal” users (forget the admins, sys, etc…)

  • Common users – users created at the Container Database (CDB) level.
    • These users are recognized inside the CDB and all its current and future PDBs.
    • Generally used for administrative purposes, such as managing the PDB’s.
    • These usernames must also start with C##.
    • As a general rule of thumb, don’t create these types of users unless you understand their full implications.
  • Local users – users created at the Pluggable Database (PDB) level.
    • Your database users in the sense we all know from 11g and before.
    • These are the users that we’ll use (pardon the pun) in our applications

An interesting and more complete description of what Common and Local users are is found here: https://dbasolved.com/2013/06/29/common-user-vs-local-user-12c-edition/

So we need to create our own pluggable database user. I called it OJETBLOG. First, make sure you’re working at the PDB level. When the cloud database was created I set the PDB name as PDB1. You can see that is the name inside my container database.

Identify the PDB name - In this case PDB1

Identify the PDB name – In this case PDB1

Once you have the PDB name, perform set it as the context in which you’ll perform the next actions, using the Alter Session command.

Then, proceed with the creation of the User itself, grant some privileges to it and then create the table in the respective schema.

The commands to do is are as follow. Select the entire text and press Alt+Enter to execute or click on the Play button

ALTER SESSION SET CONTAINER=PDB1; -- Set the working context to PDB1
CREATE USER OJETBLOG IDENTIFIED BY <password>; -- Creates the user with the supplied password
GRANT CONNECT,RESOURCE,CREATE TABLE TO OJETBLOG; -- Set some basic privileges to that user
CREATE TABLE OJETBLOG.TESTTABLE (OJETBLOGID NUMBER(5) NOT NULL, OJETBLOGDESC VARCHAR2(15) NOT NULL); -- Create the Table
COMMIT;
Create the user and the table

Create the user and the table

The output should read something like this:

Session altered.
User OJETBLOG created.
Grant succeeded.
Table OJETBLOG.TESTTABLE created.
Commit complete.

You can check that the user has been properly created and that the table has also been created in the appropriate schema.

Verification User and Table successfully created

Verification User and Table successfully created

Wrap up

We’re finally set to start working on our application. Next time, we’ll start modeling and implementing our Database, using the excellent Data Modeler. Stay tuned!

José Rodrigues, a.k.a. Maverick

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *