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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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)
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.
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).
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
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.
- 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.
Now Save it and Test it.
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.
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.
A few seconds later, the DB listener port is open. You can now Test the connection back in SQL Developer and … Success!
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.
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.
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;
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.
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