Provisioning an Oracle Autonomous Database

Let’s assume that you are developing a data warehouse project, and your team needs the appropriate environment immediately and under the minimum budget. Business users have to explore their data as soon as possible. This will be our goal for the current tutorial: to create an autonomous database for importing and exploring the data quickly and straightforwardly using Oracle.

CodingSight - Provisioning Oracle Autonomous Database

Overview of Oracle Autonomous Database

Oracle Autonomous Data Warehouse (ADW) is a fully managed and high-performance solution. It includes all of the Oracle Database advantages in the environment adjusted and optimized for the Data Warehouse workload. Thus, you don’t need to take more effort and resources in the DBA role to do the database management and optimization jobs.

Notice the most essential pluses of Oracle Autonomous Database:

  • Self-Driving – The user defines service levels while the database makes them happen.
  • Self-Securing – It protects the data from both external attacks and malicious internal users.
  • Self-Repairing – It provides automated protection from all downtime.
Oracle ADW creation steps

Register an Oracle Cloud Infrastructure Console (OCI) Account

Follow the below steps:

  • Access https://signup.cloud.oracle.com/
  • Enter the required information. You will receive the confirmation email to your account.
  • Confirm the account by clicking the link in an email, and complete the registration process.
Oracle Cloud Infrastructure Console (OCI) Account registration

Oracle offers free access for 1 month to use all cloud services. However, you need to put $300 in credits, and there will be some functional limitations in the service.

Log into the Oracle Cloud Infrastructure

After the successful account registration with Oracle Cloud, you’ll get one more email message with a confirmation.

Confirmation that Oracle ADW account  was created successfully

Click Sign In to Oracle Cloud – you will get redirected to the Login page:

  • Select Identity Provider = oracleidentitycloudservice. The service is used to manage Cloud Account in your organization. It is quite similar to the Windows Active Directory.
  • Click Continue
  • Enter your username and password
Log into the OCI Cloud

Create an Autonomous Database Instance

Before creating an ADW instance (database instance), we have to create a compartment in OCI.

A compartment is a logical object serving as a container for grouping the related resources, such as Virtual Cloud Networks, Compute Instances, Databases, etc. together.

This step is optional but it is recommended to create it. It will help you to manage the cloud resources effectively. On the top-left hamburger menu, select Identity > Compartments

ADW Instance creation

Click Create Compartment

Compartment creation in Oracle Cloud

Enter DEMO as the compartment name and Description. The Parent Compartment is always root. Click Create Compartment.

Entering DEMO as a compartment name when creating compartment

The DEMO compartment appears in the list:

The DEMO compartment appears in the list

Return to the main menu in the top-left corner and select Autonomous Data Warehouse

Autonomous Data Warehouse in Oracle Cloud

On the ADW console display, select the DEMO compartment from the drop-down list and click Create Autonomous Database:

Autonomous Database creation with DEMO compartment

Enter basic information:

  • Compartment: DEMO
  • Display Name: ADWDEMO
  • Database Name: ADWDEMO
  • Workload type: Data Warehouse. The system provides several workload types for specific purposes (Data Warehouse, Online Transaction Processing, JSON application, etc.)
  • Development type: Shared Infrastructure. This is the server-less architecture, so you don’t need to care about the Operation System and Server installation.
Automomous Database Creation steps

Next, we proceed to Configure the database

For this tutorial, enable Always Free. With this option, you won’t be charged any fee. However, note that the database server only provides you with 1 OCPU and 0.02 TB of Storage.

Go to Create administrator credentials. By default, Oracle Autonomous Database only provides you with an ADMIN account for the database instance. This account ensures most of the DBA privileges, but it does not grant full permissions as the DBA account or SYS account in an Oracle Database would give you.

Enter the password for ADMIN: Oracle#202104

Entering the ADMIN password when creating Autonomous Database

The next section is Choose Network Access

  • Access Type: Allow secure access anywhere. It allows you to access your database instance from any location, such as your company’s network or the home network. However, ADW supports Access Control Limit that allows you to limit the end-points access. E.g., you can allow only one computer note to access Oracle Autonomous Database via IP Address.
  • Choose a license type: License Included
  • Click Create Autonomous Database
Choosing Network Access when creating autonomous database

The provisioning process will take a few minutes to create the database instance.

Database Instance is created

Connecting SQL Developer to the Oracle Data Warehouse Instance

After provisioning the ADW instance, you can set up a connection to the ADW instance using SQL Developer or SQL Plus (a command client tool). Click here to download and install the latest version of SQL Developer. Make sure to download the Wallet Credentials File.

Navigate to the Autonomous Database page console and click on DB Connection.

ADW connection creation

The Database Connection page displays the following data:

Wallet Type: you can select from the two kinds:

  • Instance Wallet: a wallet for a single database only (a database-specific wallet). It is suitable for end-users and applications.
  • Regional Wallet: a wallet for all Autonomous Database for your tenant and region. E.g., your tenant is in the Tokyo region (Data Center in Tokyo), and you use this wallet type to access all Autonomous databases in this region. This type is suitable for the database administration role.

Click Download Wallet

Database Connection in Oracle Cloud

Enter the password for the Wallet file to protect it and save the zip file in a secured location.

Entering the password for the Wallet file to protect this file

Open the SQL Developer and create a new connection:

Open the SQL Developer and create a new connection in Oracle
  • Name: ADWDEMO_ADMIN
  • Username: ADMIN
  • Password: Oracle#202104
  • Checkmark the Save Password option
  • Connection Type: Cloud Wallet
  • Configuration file: browse to the location of your downloaded Wallet file
  • Service: adwdemo_high
Insert Data to create new connection:

Click Test to make sure you can connect to the ADW instance successfully.

Note: If the computer is behind the company’s proxy or firewall, you’ll need to ask the network admin to configure the proxy or open an outbound connection to hosts in the oraclecloud.com domain using port 1522.

Create a Database User

We’ve provisioned an ADW database instance and connected it to ADW with the ADMIN user. However, we never use the ADMIN schema for the end-users or applications. Instead, we need to create the database schema user for business purposes, such as HR for the HR department, or SALES for the SALES department.

Do the following:

  • Connect to the ADW database instance with ADMIN
  • Create a DEMO user by executing the below command
CREATE USER DEMO IDENTIFIED BY Oracle#202104;
GRANT DWROLE TO DEMO;
GRANT UNLIMITED TABLESPACE TO DEMO; 
Create a database user

Create a connection to ADW with the DEMO user in SQL Developer:

Create a connection to ADW with the DEMO user in SQL Developer

Conclusion

This tutorial illustrated the process of connecting to the ADW database instance. Now you can load the data to Oracle Autonomous Database and process it. Therefore, you don’t need any further efforts for installation and management, the on-premised Oracle database instance does it.

Read Also:

  1. Import Data into Oracle Autonomous Data Warehouse using Oracle Object Storage
  2. Database Security in Oracle
  3. Create and Configure Oracle Linked Server in SQL Server

Dung Dinh

Dung Dinh

BI Specialist, Data Modelling, working as Oracle Consultant in Oracle Cloud.

Leave a Reply

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