Provisioning Autonomous Database
Introduction
Description
This lab will introduce you to the Oracle Autonomous Database on Oracle Cloud. You will provision an Autonomous Data Warehouse instance, then connect to it, and view sample data using the Oracle SQL Developer web tool.
Lab Objectives
- Provision Autonomous Data Warehouse
- Connect via SQLWeb
- Run a query
Lab Overview Video
- Watch the video below for an accelerated view of the lab run through by the author.
Provisioning Autonomous Database Video
Intended Audience
- Executive/Management level audience interested in the basics of Oracle Autonomous Database
Prerequisites
- Basic computer operation experience
- Familiarity with basic database concepts
Resources
- Oracle Autonomous Database
- Oracle Autonomous Data Warehouse
- Using Autonomous Database on Shared Exadata Infrastructure
Task 1: Create an Autonomous Data Warehouse instance
- After you've followed the steps to login to the OCI console in the web browser, click the navigation menu in the upper left and choose Oracle Database, then click on Autonomous Data Warehouse from the Autonomous Database section of the drop down menu.
- Choose your assigned compartment from the List Scope - Compartment selection field. Click the plus and minus icons in the drop down and navigate to the Luna-Labs compartment to find your assigned lab compartment. Click on the compartment name to select it.
- Click on the Create Autonomous Database button.
- There are just a few questions to answer in order to create an autonomous database. We'll walk through the form in 3 sections. Use the information from the table below to fill out the form.
Section 1:
| Field | Recommended Information |
|---|---|
| Compartment: | Your assigned compartment |
| Display name: | ADWFinance |
| Database name: | ADWFINANCE |
| Choose a workload type: | Data Warehouse |
Section 2:
| Field | Recommended Information |
|---|---|
| Choose a deployment type: | Shared Infrastructure |
| Choose database version: | 19c |
| OCPU Count: | 2 |
| Storage (TB): | 1 |
| OCPU auto scaling: | Select this option |
| Storage auto scaling: | Deselect this option |
Section 3:
| Field | Recommended Information |
|---|---|
| Username (Read Only) | ADMIN this option can't be edited |
| Password: | Choose a 12 digit or longer password - example: LN%;Bx5Db9\c |
| Choose network access | Allow secure access from everywhere |
| Choose a license type: | Bring Your Own License |
| Choose an Oracle Database Edition | Oracle Database Enterprise Edition (EE) |
Review the form for any items you might have missed, confirm your entries, and click the Create Autonomous Database button.
The autonomous database will enter the provisioning state. The ADW icon will change to an orange color for just a few moments and then will turn green to indicate it's ready for use.
- At this point your Autonomous Data Warehouse is provisioned and ready for use. Examine the details on the information screen to familiarize yourself with ADW characteristics, including OCPU count, storage, lifecycle state, and more.
Task 2: Connect to the database with SQL Developer Web and run a query
In this portion of the lab, you will access the ADW instance that you created and run a query to view the sample data set.
The Autonomous Database service provides three different service levels depending on a customers database performance needs. They can select from high, medium, or low and each provides different levels of performance and concurrency.
| Level | Expected performance characteristics |
|---|---|
| High | Provides maximum CPU resources for a query with a limit on concurrency. So the number of queries you can run will be low, but the ones you do run will run very quickly. |
| Medium | Contains multiple I/O and CPU resources to balance performance and concurrency. |
| Low | This service provides the least amount of CPU resources for a query but provides unlimited concurrency. |
The service information above is just for reference. We're connecting via SQL Developer Web which only utilizes the Low service. In other Autonomous Labs you will be using SQL Developer, which can utilize any of the service types.
In the next steps you will run a query on a sample data set provided out of the box with ADW. ADW provides the Oracle Sales History sample schema and a Star Schema Benchmark - SSB data set; these data sets are in the SH and SSB schemas, respectively. Our basic query will run on the SSB data set which is a 1TB, one fact-table with around 6 billion rows, and several dimension tables.
- In your database's details page, click the Database Actions button.
- A new tab will appear entitled Database Actions | Launchpad with all of the available database actions tools. Click the SQL tool to open the SQL Web tool.
- The SQL tool will open to a worksheet tab and will start with a brief tour of the interface. Click Next on the various pop-ups to familiarize yourself with the SQL Developer Web interface. You can click the X to end the tour at any time.
Run a query on the autonomous data warehouse
- Type the following query (or copy/paste) into the worksheet on the SQL Developer Web page. The query will run on the Star Schema Benchmark, one of the two ADW sample data sets that can be accessed from any ADW instance. Click the Run Statement button (green circle with the white arrow) to execute the query.
select /* low */ c_city,c_region,count(*)
from ssb.customer c_low
group by c_region, c_city
order by count(*);
- Review the output in the Query Result section of the screen. Note the execution time.
- ADW also attempts to cache results of your queries. When running identical queries you should notice improved response times with subsequent runs, indicating that the query result is coming from from cache, thus speeding up your work. Click the Run Statement button again and note the response time.
- That's it! You've successfully deployed an Autonomous Data Warehouse on OCI and used the SQL tool to run a sample query. You're well on your way to utilizing the most powerful database technology on the planet.