Oracle by Example brandingProvisioning Autonomous Data Warehouse Cloud

section 0Before You Begin

This tutorial shows you how to log in to the Oracle Autonomous Data Warehouse Cloud console, create a service, and create a user. This tutorial takes approximately 15 minutes to complete.

Oracle Autonomous Data Warehouse Cloud Tutorial Series

This is the first in a series of tutorials for the Oracle Autonomous Data Warehouse Cloud. Perform the tutorials sequentially.

Background

Oracle Autonomous Data Warehouse Cloud (Autonomous DW Cloud) is fully-managed, offers high-performance, and is elastic. You have all of the performance of the market-leading Oracle Database in a fully-managed environment that is tuned and optimized for data warehouse workloads. Autonomous DW Cloud supports all standard SQL and business intelligence (BI) tools and delivers scalable analytic query performance.

In this tutorial, you create a service, connect SQL Developer to Autonomous DW Cloud, and create a database end user to use in all subsequent tutorials. 

What Do You Need?

  • Subscription to Oracle Autonomous Data Warehouse Cloud
    • Your Oracle Cloud user name, password, and identity domain

      Locate your account details in the New Account Information email that you received from Oracle Cloud when your user account was set up. If you don't have your New Account Information email, contact your account administrator.
  • Oracle SQL Developer (see Oracle Technology Network download site). We recommend that you download version 17.4 or later, because this version contains enhancements for key Autonomous DW Cloud features. SQL Developer 17.3.1 will also work with Autonomous DW Cloud; versions earlier than 17.3.1 will not.
    Note:
    If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 8 included' distribution as it includes both Java 8 and the Java Cryptography Extension (JCE) files necessary to run SQL Developer and connect to your Autonomous DW Cloud.
    If you are a non-Windows user, Download and Install the appropriate Java 8 JDK for your Operating System. Download and Extract the Java Cryptography Encryption Archive to the directory as indicated in the README.txt.

section 1Create a Service

The first step is to sign in to the Autonomous Data Warehouse Cloud and create a service.

  1. Sign in to Autonomous Data Warehouse Cloud, using the connection information provided by your cloud service administrator. On the Welcome page, click Go to Console.
    welcome page go to console
    Description of the illustration welcome_page_go_to_console
  2. On the Services page, click Create Service.
    click create service
    Description of the illustration create_service
  3. In the first page of the Create Service wizard, enter the following information:
    • Database Name - Use letters and numbers only. Maximum length is 16 characters. (Underscores not initially supported.)
    • Description - You may add an optional description that can be used to help identify this new service.
    • Notification Email - Provisioning status updates will be sent to the specified e-mail.
    • Region - Name of the Compute Region. 
    • CPU Count - Number of CPUs for your service.
    • Storage Capacity (TB) - Select your storage capacity in TB. It is the actual space available to your service, including system-related space allocations.
    • Administrator Password - Password for ADMIN user of the service. The password must meet the following requirements:
      • The password must be between 12 and 60 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.
      • The password cannot contain the keyword ADMIN.
      • The password cannot contain the double quote (") character.

        Click Next.
    create service first wizard page left side
    Description of the illustration create_service_first_wizard_page_one_left_side
    create service first wizard page one right side
    Description of the illustration create_service_wizard_page_one_right_side
  4. In the second page of the Create Service wizard, confirm your entries and click Create.
    create service second wizard page
    Description of the illustration create_service_wizard_page_two
  5. The Create Service wizard closes. On the Services page, the Status field indicates the service is being created. When service creation is completed, the Status field disappears. You can click the refresh button to see the latest creation status.
    status
    Description of the illustration status


section 2Download the Credentials Zip File

Once you have created the service, download the credentials zip file for client access to that service. You will use this file in the next tutorial to connect SQL Developer to your Autonomous DW Cloud database.

  1. In the Autonomous DW Cloud service console, in the menu across from the name of your new service, select  Service Console.
    open service console
    Description of the illustration open_service_console
  2. A dialog opens to sign in to the service console. In the Username field, enter the following information:
    • Username - admin (the default administrative user named admin that was created for your Autonomous DW Cloud account)
    • Password - the administrator password you specified when you created your service instance
    username and password to open service console
    Description of the illustration sign_in_to_service_console
  3. The service console opens in the Overview mode. Click the Administration tab.
    click administration tab
    Description of the illustration click_administration
  4. Click Download Client Credentials.
    download client credentials
    Description of the illustration download_client_credentials
  5. You are prompted to create a password for the credentials zip file. Enter a password and click Download.
    credentials password
    Description of the illustration credentials_password
  6. Store the zip file and make note of the password. You will use the zip file and password in the next step to define a SQL Developer connection to your Autonomous DW Cloud database.

section 3Define a SQL Developer Connection

First, define a SQL Developer connection to the database in your Autonomous DW Cloud service.

  1. Open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.
    Note:
    Depending on your version of SQL Developer, do not right-click Cloud Connection or Database Schema Service Connections. That menu selection is for connecting to a different Oracle cloud service, the Oracle Database Schema Service.
    select new connection
    Description of the illustration select_new_connection
  2. The New/Select Database Connection dialog appears. Enter the following information:
    • Connection Name - Enter the name for this cloud connection.
    • Username - Enter the database username. Use the default administrator database account (admin) that is provided as part of the service.
    • Password - Enter the admin user's password that you or your Autonomous DW Cloud administrator specified when creating the service instance.
    • Connection Type - Select Cloud PDB.
    • Configuration File - Click Browse, and select the Client Credentials zip file, downloaded from the Autonomous DW Cloud service console by you, or given to you by your Autonomous DW Cloud administrator.
    • Keystore Password - Enter the password generated when you or your Autonomous DW Cloud administrator downloaded the Client Credentials from the Autonomous DW Cloud service console.
    • Service - In the drop-down menu, service selections are prepended with database names. Select the low, medium or high menu item for your database. These service levels map to the LOW, MEDIUM, and HIGH consumer groups, which provide different levels of performance and concurrency.
      Note: Earlier versions of SQL Developer may not support this feature.  
    connection definition completed
    Description of the illustration new_select_database_connection_dialog
  3. Click Test.
    Status: Success displays at the left-most bottom of the New/Select Database Connection dialog.
  4. Click Connect.
    An entry for the new connection appears under Connections.

section 4Create a User in your Autonomous DW Cloud Database

Once you have connected SQL Developer to your Autonomous DW Cloud database, use a SQL Developer worksheet to define a create user statement to create the user adwc_user. In the next tutorial, you will create sales history tables in the adwc_user schema and load data into these tables from an object store.

  1. Open a SQL Developer worksheet and run the following SQL statements to create the user adwc_user.
    create user adwc_user identified by "WelcomeADWC1!";
    grant dwrole to adwc_user;
    SQL Developer commands creating adwc_user
    Description of the illustration sql_developer_commands_creating_adwc_user
    Note:
    Autonomous Data Warehouse Cloud requires strong passwords. The password you specify must meet the default password complexity rules. This database checks for the following requirements when you create or modify passwords:
    • The password must be between 12 and 60 characters long and must include at least one uppercase letter, one lowercase letter, and one numeric character.
    • The password cannot contain the username.
    • The password cannot be one of the last four passwords used for the same username.
    • The password cannot contain the double quote (") character

    Note
    : Autonomous DW Cloud databases come with a pre-defined database role named DWROLE.
    This role provides the common privileges for a data warehouse developer: CREATE ANALYTIC VIEW, CREATE ATTRIBUTE DIMENSION, ALTER SESSION, CREATE HIERARCHY, CREATE JOB, CREATE MINING MODEL, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, READ,WRITE ON directory DATA_PUMP_DIR, EXECUTE privilege on the PL/SQL package DBMS_CLOUD  

  2. In the next tutorial, "Connecting SQL Developer and Creating Tables", you will connect SQL Developer to your Autonomous DW Cloud database as user adwc_user, and define SH tables (sales history tables from an Oracle sample schema) for that user. Later, you will load data into those tables from an Object Store.