Oracle 21c: Get Started with Oracle Cloud Always Free

In a previous blog post I described the Oracle Cloud Free Tier, which includes both Always Free and Free Trial.  In that blog I detailed what you get and how to enroll.  This blog takes it to the next level by showing you how to create an Oracle 21c database and connect existing applications or develop new applications.

Recall Oracle Always Free allows you to create up to 2 VMs and up to 2 databases.  Also recall there is no relationship between the Oracle VMs and the Oracle databases - they run on different servers and cannot talk to each other until you perform a bunch of configuration steps.  The exception is when you want to create a MySQL or other database, then you do so manually on the VM.

Creating Your First Oracle 21c Database for Free

Let's start by creating an Oracle 21c database in the Oracle Cloud Free Tier.  The process is very logical and easy.  It requires zero prior knowledge with of Oracle Database. 

First, log into your Oracle Cloud account.  The Oracle Cloud Free Tier's dashboard looks like this:

The dashboard's core tasks are self-explanatory.  To create a transactional database click Create an ATP Database.  To create a non-transactional database for reporting or analytics click Create an ADW Database.  You don't have to create a VM to create a database - your databases are created on shared Exadata appliances, not in your personal VM.

Let's look at the process of creating a transactional database.  As an example, start by clicking on Create an ATP Database.  Don't worry, it doesn't lock you into that type of database.  The setup wizard will ask you to choose from four different databases.

The screen shown below will appear.  I don't show it, but the screen actually starts with fields for naming the new database, or you can accept the system generated name.  Then, you can choose the type of database based on workload.  To remain free of cost you must choose Shared Infrastructure and enable the option for Always Free.

Don't click the Create button just yet.  Keep scrolling down.  This is a long form.

Next, in the screen shown below, choose a version of Oracle like 21c.  Free databases can only have 1 Oracle CPU and 20 GB of storage, so those selectors are read-only.  Skip Auto Scaling for now.  Then, set a password for the ADMIN database user: the password must be at least 12 characters without spaces or quotation marks, and contain at least one uppercase letter, one lowercase letter, and one number.

Scrolling down we see the screen shown below which contains the last set of fields.  All of these fields have acceptable default values, so you don't need to do anything.  Notice it defaults to allowing access from the public network so you don’t need VPN software.  The Advanced Options page, not shown, allows you to set tags.

Click on Create Autonomous Database.  Behind the curtain Oracle will run a series of SQL statements like CREATE PLUGGABLE DATABASE and ALTER PLUGGABLE DATABASE OPEN.  However, there is no option to see those statements.

After the database is created and you are shown a summary page.  At the bottom of that page are metrics, which of course won't have anything until you put some data in the database.  Just remember to come back here later for monitoring.

A quick tip about log on/off from Oracle Cloud Free Tier.  In the upper right corner of the Oracle Cloud page is an icon for your profile, and when you click on it you'll see a Sign Out option.  I recommend you do this, because it takes you to a special sign-in screen.  Bookmark this screen!  Otherwise, you'll have to sign-in through the main Oracle Cloud page and that's a multi-step process.

Connecting to Your Database Using Oracle Cloud Tools

Now that you have a database, you can create users, tables, indexes, and start developing applications.  SQL Developer Web is the preferred and easiest way to explore and manage your database.  You can access it from your Oracle Cloud database monitoring page.

If you haven't done so already, log into your Oracle Cloud account either using the bookmark I recommended earlier, or through https://www.oracle.com/cloud/sign-in.html.

Upon login Oracle the main display includes two tabs in the upper-left: Get Started, and Dashboard.  The Get Started tab allows you to create VMs, databases, and so on.  The Dashboard tab allows you to keep working with things you previously created.  My screen is shown below.

Let's click on Dashboard.  On the Dashboard page we can access a previously created database by expanding the Autonomous Database service and clicking on its entries.

Note there's a bug in the system where it always displays Autonomous Data Warehouse as the service, but as you drill down it correctly displays your Autonomous Transaction Processing databases.

After you select a database a screen like the one shown below will appear:

SQL Developer Web is the preferred and easiest way to inspect and manage your database, and for creating database objects and running queries.  Clicking on SQL Developer Web exposes a button to launch the application.

You will be prompted for a database username and password, not your cloud credentials.  The first login must be as the ADMIN user with the password you set when creating the database, because at this point there are no other database user accounts.

The SQL Developer Web user interface will be displayed, as shown below, and you will be walked-through all of the features.  After the tutorial ended I ran a simple query, just as an example.

Note: querying system objects like I did isn't necessary, and if you're new to Oracle then I would not bother with system objects as it gets confusing fast! 

At this point you're in the database as the superuser, ADMIN, and you can do whatever you like.

I strongly recommend you click on the menu button in the upper-left corner, just to the left of the word ORACLE.  It will expose many features that are otherwise hidden.  The menu option SQL will take you back to the screen you saw earlier.  Data Modeler launches a modeling app that can be used to automatically generate CREATE TABLE statements.  Under Administration is an option to create users.

Giving Developers Access to Your Database

Let's start with Database Users.  Our goal will be to create a non-admin user with permission for creating all types of database objects or "the schema".

On the right side of the Database Users screen click the button named "+ Create User". 

The following screen will appear.  Enter a unique name for the user, which will be their login ID, and enter a starting password which must be at least 12 characters with one upper, one lower, and one number.  Then, click on Password Expired which will force the user to change the password to a personal value on first login.  Please note: to allow the user to connect via web-based tools like SQL Developer Web you must enable REST.  If you do not enable REST, then the user can only connect via OCI clients like SQL*Plus.

Click on the tab Granted Roles, and select the roles named CONNECT and RESOURCE as shown below.  The RESOURCE role includes privileges to create tables, indexes, and other schema objects.  The CONNECT role includes just one privilege - the login privilege.

Click Create User.

The new user SADIE can now connect to the database and change their password.  For example,

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 5 11:21:02 2021
Version 19.9.0.0.0

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

Enter user-name: sadie@mydb
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for sadie
New password:
Retype new password:
Password changed

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.2.0.0.0

SQL>

So that's it.  We've created a developer account and verified it is working.  Just remember, if you want to allow this user to connect using Oracle Cloud tools like SQL Developer Web, then you must alter the user to enable REST.

The above example assumes the user has SQL*Plus installed on their computer.  I installed Oracle Instance Client and added the SQL*Plus app to it.  I also downloaded my database's Oracle Cloud Wallet to the Oracle Instant Client directory.  This is described in my next blog: Connecting to Oracle Cloud Free Databases from OCI Clients.

 

Comments

Post a Comment

Popular posts from this blog

Using DBGen to Generate TPC-H Test Data

Image
In this post I describe the Data Base Generator (DBGen) utility in terms of what it is, how to install it, and how to use it.   This post assumes a Linux operating system.   There are different build procedures for DBGen on Windows, but the concepts carry over. DBGen is used to create a TPC-H schema (i.e., it provides the CREATE TABLE statements) and to generate the data.   DBGen is used in official TPC-H benchmarking, but it can also be used in informal TPC-H-like benchmarking.   The schema and data is compatible with most 3 rd party benchmarking tools like HammerDB, so even if you plan to run a TPC-H-like test using HammerDB you may still use DBGen to create the schema and data. The  CREATE TABLE   statements are ANSI standard SQL and can be run in any DBMS without editing.   There are no SQL statements for indexes or foreign key constraints provided by DBGen since those types of objects are not required.   The data is created as delimited...
Read more

Oracle 21c Caching Solutions

This blog post reviews all of the caching solutions available in Oracle Database 21c.  I dive into several of the caching solutions, but I do try to keep things relatively short-and-sweet here.  I have other blogs dedicated to each caching solution. I don't mean the Oracle SGA or PGA.   I mean optional features you can enable to enhance performance. The content of this blog post is applicable to Oracle 12g, 18c, 19c, and 21c.   Some of the caching solutions were available in Oracle 11g, but I did not regression test my examples back that far. There have been no new caching solutions in Oracle 12g R2 through 21c.   One solution in particular, the In-Memory Column Store, has received some major enhancements.   I briefly describe it here, but the deep dive is in another blog post. Summary of Oracle Caching Solutions Oracle 21c includes six caching features as listed in my table below.   I've listed them in order from newest on top to oldest at t...
Read more

TPC-like Database Benchmarking Tools

Image
  This post describes five popular tools for benchmarking relational databases: HammerDB, Quest Benchmark Factory, SwingBench, pgbench, and Oracle RAT.  Previously, I described TPC and TPC-like benchmarks for relational databases.   Recall a benchmark is "like" a TPC benchmark if it follows some of the rules but not all of them.   The tools described in this post follow some of the TPC's rules so we call them "TPC-like".   Publishing results is generally not allowed by commercial DBMS vendors, and use of the benchmark trademark is also restricted. Ok then, let's look at the most commonly used benchmarking tools for relational databases. HammerDB is my #1 choice for benchmarking relational databases.   HammerDB supports every major DBMS on Linux and Windows including bare metal, VM, and cloud.   This means you can use one tool for all environments.   You can even compare performance across platforms or DBMS, such as comparing Oracle on Linux...
Read more