Oracle by Example brandingGetting Started and Provisioning Autonomous Data Warehouse

section 0Before You Begin

This tutorial shows you how to log in to the Oracle Cloud Infrastructure console, create an autonomous database instance with a workload type of Data Warehouse and create a database user. This tutorial takes approximately 15 minutes to complete.

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

Background for Oracle Autonomous Data Warehouse

Oracle Autonomous Data Warehouse 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 Data Warehouse supports all standard SQL and business intelligence (BI) tools and delivers scalable analytic query performance.

In this tutorial, you create an Autonomous Data Warehouse database, connect SQL Developer to the database, and create a database end user to use in all subsequent tutorials.

What Do You Need?

  • Subscription to Oracle Autonomous Data Warehouse
    • 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.
  • Tenancy in Oracle Cloud Infrastructure, including the creation of a compartment and the setting of access to resources (see Oracle Cloud Infrastructure documentation for setting up your tenancy).
  • Oracle SQL Developer (see Oracle Technology Network download site). Version 19.1 or later is recommended. Versions 18.2 or later contain enhancements for key Autonomous Data Warehouse features.
    If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 8 included' distribution as it includes the files necessary to run SQL Developer and connect to your Autonomous database.

    If you are using a SQL Developer version earlier than 18.2, see the documentation topic Connecting with Oracle SQL Developer (earlier than Version 18.2).

section 1Create an Instance

Sign in to Autonomous Data Warehouse and create an instance.

  1. Sign in from On the Oracle Cloud page, click Sign In.
  2. On the Cloud Account page, enter your cloud account name and click Next.
    Cloud Account
    Description of the illustration cloud_login
  3. Enter your User Name and Password and click Sign In to sign in to your Oracle Cloud Account.
    Cloud Account
    Description of the illustration cloud sign in
  4. On the Oracle Cloud Infrastructure page, click the navigation menu in the upper left to show top level navigation choices.
    Cloud Account
    Description of the illustration cloud console
  5. Click Autonomous Data Warehouse.
    Navigation menu
    Description of the illustration console navigation
  6. The console for Autonomous Database displays. You can use the List Scope drop-down menu to select a compartment; in this example the Doc compartment is selected. Click here for documentation on creating compartments.
    This console shows no databases. If there were a long list of databases, you could filter the list by using the Filters drop-down menu to filter by the state of the databases (available, stopped, terminated, and so on). You can also sort by workload type. Here Data Warehouse is selected.
    Click Create Autonomous Database to create a database instance.
    create autonomous database
    Description of the illustration create_autonomous_data_warehouse_database
  7. The Create Autonomous Database dialog appears. Enter the following information:

    Provide basic information for the Autonomous Database:

    • Choose a compartment - Select a compartment for the database from the drop-down list.
    • Display Name - Enter a name for the database for display purposes.
    • Database Name - Use letters and numbers only, starting with a letter. Maximum length is 14 characters. (Underscores not initially supported.)

    Choose a workload type. Select the workload type for your database from the choices:

    • Data Warehouse - For this tutorial, choose Data Warehouse as the workload type. Do not choose Transaction Processing.
    • Transaction Processing - (Alternatively you can chosen Transaction Processing as the workload type.)
    Choose a deployment type:
    • Shared Infrastructure - For this tutorial, select Shared Infrastructure. This choice creates the autonomous database on shared Exadata infrastructure, without provisioning a dedicated infrastructure.
    • Dedicated Infrastructure - (Alternatively, you can choose the Dedicated Infrastructure deployment type to create the autonomous database on a dedicated Exadata infrastructure. This tutorial does not cover the Dedicated Infrastructure deployment type.)
    Configure the database:
    • Always Free - For this tutorial, do not activate this option. Always Free databases are provided free of charge, and are suitable for small-scale applications or for learning about and exploring Oracle Cloud Infrastructure.
    • CPU core count - Number of CPUs for your service.
    • Storage (TB) - Select your storage capacity in terabytes. It is the actual space available to your service instance, including system-related space allocations.
    • Auto Scaling - For this tutorial, do not activate this option. If you select the auto scaling option, the Autonomous database can use up to three times more CPU and IO resources than specified by the number of OCPUs. When auto scaling is enabled, if your workload requires additional CPU and IO resources, the database automatically uses the resources without any manual intervention required.
    • Enable Preview Mode - For this tutorial, do not activate this option. Oracle periodically provides a preview version of Autonomous Database. You can enable the preview mode to test your applications and become familiar with features in the next release of Autonomous Database.

    Create administrator credentials:

    • Password and Confirm Password - Specify the password for ADMIN user of the service instance. The password must meet the following requirements:
    • The password must be between 12 and 30 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 contain the double quote (") character.
    • The password must be different from the last 4 passwords used.
    • The password must not be the same password that is set less than 24 hours ago.
    Choose network access:
    • By default, secure connections are allowed from all IP addresses. You can control and restrict access to your Autonomous Database by setting network access control lists (ACLs). You can select from 4 IP notation types: IP Address, CIDR Block, Virtual Cloud Network, Virtual Cloud Network OCID).

    Choose a license type:

    • Bring Your Own License - Select when you have existing licenses.
    • License Included - Select when you want to subscribe to new database software licenses and the database cloud service.
  8. The Create Autonomous Database dialog closes. On the console, the Lifecycle State field indicates that the database is Provisioning. When creation is completed, the Lifecycle State field changes from Provisioning to Available.
    provisioning state
    Description of the illustration provisioning_state

section 2Download the Credentials Zip File

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

  1. In the console, in the details page of your new Autonomous database, select DB Connection.
    db connection button
    Description of the illustration open_service_console
  2. The Database Connection dialog opens for downloading client credentials. For wallet type, select Instance Wallet.
    Note: Oracle recommends you download the database-specific wallet type, Instance Wallet, to provide to your end users and for application use whenever possible. The other wallet type, Regional wallet, should only be used for administrative purposes that require potential access to all Autonomous Databases within a region.
    Click Download Wallet.
    database connection dialog
    Description of the illustration database_connection_dialog
  3. In the Download Wallet dialog, enter an encryption password for the wallet, confirm the password, and then click Download.
    click download
    Description of the illustration download_wallet
  4. Click Save File, and then click OK.
  5. Store the zip file and make note of the password. You will use the zip file in the next step to define a SQL Developer connection to your Autonomous Data Warehouse database.

section 3Define a SQL Developer Connection

Define a SQL Developer connection to the autonomous database.

  1. Open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.
    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 Data Warehouse administrator specified when creating the instance.
    • Connection Type - Select Cloud Wallet.
    • Configuration File - Click Browse, and select the Client Credentials zip file, downloaded from the Autonomous Data Warehouse service console by you, or given to you by your Autonomous Data Warehouse administrator.
    • Service - In the drop-down menu, service selections are prepended with database names. Select the high, medium, or low menu item for your database. These service levels map to the HIGH, MEDIUM and LOW consumer groups, which provide different service levels for your session. 
    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 Data Warehouse Database

Once you have connected SQL Developer to your Autonomous Data Warehouse 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, swapping in a password with the guidelines provided in the following Note section.
    create user adwc_user identified by "<password>";
    grant dwrole to adwc_user;
    SQL Developer commands creating adwc_user
    Description of the illustration sql_developer_commands_create_user_adwc
    Note: Autonomous Database 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 30 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

    : Autonomous Data Warehouse databases come with a predefined database role named DWROLE.

  2. In the next tutorial, "Connecting SQL Developer and Creating Tables", you will connect SQL Developer to your Autonomous Data Warehouse 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.