Forum Stats

  • 3,827,529 Users
  • 2,260,790 Discussions
  • 7,897,291 Comments

Discussions

TEST

tkoketsu-Oracle
tkoketsu-Oracle Member Posts: 1 Employee
edited Mar 15, 2019 4:24AM in Social Groups

Oracle Autonomous Database

Hands-on Workshop

Contents

  1. 0.     Workshop Overview    0-2

Lab Environment Setup  0-4

  1. 1.     Provisioning and Connectivity   1-10

Lab 1-1:   Sign-In to Oracle Cloud Infrastructure Console  1-11

Lab 1-2:   Provisioning an Autonomous Transaction Processing Database  1-15

Lab 1-3:   Connecting to Oracle Autonomous Database  1-23

  1. 2.     Data Loading and Integration   2-32

Lab 2-1:   Loading Data Using SQL Developer Import Data Wizard   2-33

Lab 2-2:   Loading Data from Object Storage  2-39

Lab 2-3:   Loading Data Using SQL Loader   2-50

  1. 3.     Migrating to the Autonomous Database   3-60

Lab 3-1:   Migrating to Autonomous Database using Data Pump  3-61

  1. 4.     Developing Applications with Autonomous Database   4-69

Lab 4-1:   Building Node.js Applications with ADB   4-70

Lab 4-2:   Building Microservices with Docker   4-76

Lab 4-3:   Using Python with Autonomous Database  4-83

  1. 5.     Managing the Autonomous Database   5-87

Lab 5-1:   Start, Stop and Scale Autonomous Database  5-88

Lab 5-2:   Using REST APIs to Manage ADB   5-93

  1. 6.     Performance Monitoring   6-103

Lab 6-1:   Database Services and Resource Management in ATP   6-104

Lab 6-2:   Scalability and Performance  6-108

Lab 6-3:   Performance Monitoring   6-113

  1. 7.     Optional Labs  7-119

Lab 7-1:   Exploring Autonomous Database Using SQL Developer   7-120

Lab 7-2:   Use Swingbench to Generate Workload and Monitor Performance  7-133

Lab 7-3:   Backup and Recovery  7-138

0.     Workshop Overview

Introducing Oracle Autonomous Database

Oracle redefines data management with the world’s first autonomous database. Oracle Autonomous Database eliminates complexity, human error, and manual management, helping to ensure higher reliability, security, and more operational efficiency at the lowest cost. Compared to traditional database technology, an autonomous database cloud has greater availability greater security, and lower operating costs.

Other industry terms for autonomous database are self-driving database, self-repairing and self-securing.

  • Self-driving
  • Self-securing
  • Self-repairing

With Oracle Autonomous Database you do not need to configure or manage any hardware or install any software. Autonomous database handles database creation at the push of a button, database backups, patching and upgrading the database, and growing or shrinking the database.

Oracle Autonomous Database is built upon the Oracle Database, so that all applications and tools that support Oracle Database also support Oracle Autonomous Database. These tools and applications connect to autonomous database using standard SQL*Net connections. The tools and applications can either be in your data center or in a public cloud. Oracle Analytics Cloud and other Oracle Cloud services are preconfigured for autonomous database.

Optimized for Workload Types

Modern automobiles are specialized by workload: family car, van, pickup truck, sports car, etc. In the same way, the Autonomous Database consists of a single set of technologies available in multiple products, each tailored to a different workload:

Oracle Autonomous Data Warehouse

Autonomous Data Warehouse is a fully managed database tuned and optimized for data warehouse workloads with the market-leading performance of Oracle Database. As a data warehouse developer, business user, or data scientist, Autonomous Data Warehouse lets you use all your existing data warehouse design, data integration, analysis, and reporting tools.

Oracle Autonomous Transaction Processing

Autonomous Transaction Processing is designed to run mission-critical enterprise applications, including mixed workloads and real-time analytics, with no compromise on application performance. It provides a high-performance Oracle Database in an environment that is tuned and optimized for transaction processing workloads.

This workshop walks you through all the steps to get started using Oracle Autonomous Database.

Workshop Objectives

The Oracle Autonomous Database hands-on workshop is focused on the following (some topics are dependent on whether the workshop is Transaction Processing focused or Data Warehouse focused):

  • Provisioning and Connectivity: Learn to provision a new autonomous database and connect your favorite client tools.
  • Management and Monitoring: Learn to Manage and Monitor the service, and scale dynamically to experience the elasticity of autonomous database.
  • Data Loading and Integration: Learn to load data into the autonomous database and integrate with data residing in Cloud storage or other RDBMs (e.g. using Oracle Data Sync).
  • Migration to Autonomous Database: Migrate Oracle Databases on premise or Cloud to the autonomous database.
  • Build Applications using Autonomous Database (ATP): Learn to use your favorite development environments to connect and build applications using the autonomous database.

This is an instructor-led workshop, please follow the guidance from the instructor before attempting the lab exercises.

Lab Environment Setup

Cloud Accounts

Obtain your access to the following Cloud Services and lab accounts. Your instructor will provide this information.

  • Oracle Cloud Account (includes Oracle Autonomous Database):
    • URL :
    • Cloud Tenant :
    • User Name :
    • Password :
    • Cloud Region :
  • Lab VM Account:
    • Remote Desktop IP :
    • User Name :
    • Password :
  • OCI Compartment to Create Autonomous Services
    • ADB Compartment :

Required Software

A lab VM hosted in Oracle Cloud Infrastructure is provided to you to run the hands-on lab exercises. However, to connect to the lab VM, you would need the following software:

For Microsoft Windows hosts:

For macOS, Mac OS X hosts:

About the Lab VM

The lab VM is a preconfigured virtual machine that is available to you to assist with the lab exercises. Without the lab VM, you would need to install quite a few software components which would take valuable time away from the class.

The lab VM is hosted in the Oracle Compute Cloud and you would connect to it from your laptop/desktop using the RDP protocol.

The lab VM includes some of your favorite client tools and other required software preinstalled to help you complete the labs. Below is a partial list of software packages that are preinstalled and configured in the VM:

  • Oracle SQL Developer
  • Oracle Database Client
  • Swingbench load generator
  • Node.js & Docker

Lab VM Setup Steps

Perform the following setup before starting the lab exercises.

  1. Connect to the Lab VM
    • To access the lab VM, start Microsoft Remote Desktop 10 App on macOS, or Remote Desktop Connection or Remote Desktop for Windows.
  • Add a new connection for the lab VM. If this is your first time using the App, click on Add Desktop, or you may click on then Desktop.

Windows

Mac

  • On the Add Desktop pop-up, enter the Remote Desktop IP or the domain name of the lab VM as PC Name and.

Windows

Mac

  • Click on to add a user account, or the User Account and select Add User Account.

Windows

Mac

  • Enter User Name and Password provided to you for the lab VM. Click Save.

Windows

Mac

  • Click Save one more time to Save the connection.

Windows

Mac

  • Connect to the lab VM by Double Clicking the connection you just.

Windows

Mac

  • Click Continue when prompted to continue with a unsecure connection.
  • You should now be connected to the VM and the following desktop should be displayed.

Note: If you have issues connecting, check if your firewall is blocking port 3389 as the Remote Desktop connection uses this port. This is usually the case when you are connected to your corporate VPN.

  • If you see the above desktop, you have successfully connected to the lab VM.

1.     Provisioning and Connectivity

Lab 1-1: Sign-In to Oracle Cloud Infrastructure Console

This lab walks you through the steps to log in to the Oracle Cloud Infrastructure console.

Objectives

  • Learn to sign-in to Oracle Cloud Infrastructure console.
  • An active Oracle Public Cloud account. You may use your own cloud account or a cloud account that you obtained through a trial or the lab account provided by the instructor.
  • If you are using a laptop or a desktop, ensure that you have a supported version of web browser for Oracle Cloud. Alternatively, you may use the lab VM that has a supported browser pre-installed.
  1. Sign-In to Oracle Cloud Infrastructure Console
    • Using a web browser on your laptop/desktop or the lab VM, browse to the ADB Cloud Account URL provided to you by your instructor.

Required Artifacts

Lab Steps

In the screenshot below, the URL assigned is https://console.us-phoenix-1.oraclecloud.com (yours may be different so please check your lab handout).

  • On the Oracle Cloud Infrastructure Sign In page, enter the Cloud Tenant assigned to you and click Continue.

In the screenshot below, the Cloud Tenant assigned is oraclepartnersas (yours may be different so please check your lab handout).

  • You will be presented one of the following options to login, depending on whether the authentication is federated to an external identity provider such as Oracle Identity Cloud Services, or if it is done locally by Oracle Cloud Infrastructure.

Choose to Sign In with your Oracle Cloud Infrastructure credentials as the lab accounts are not federated.

Windows

Mac

(or)

  • Enter your Oracle Cloud User Name and Password supplied to you by the instructor and click Sign In.
  • If you are logging in for the first time or after a password reset, you will be prompted to change the password.

Enter the Current Password supplied to you by the instructor, the New Password, Confirm New Password and click Save New Password.

Note the password requirements in the screenshot below.

  • Upon a successful login you will be presented OCI Console Home Page. Notice that Quick Actions, Solutions and Learn sections in the main pane and Action Center on the right.
  1. Select your Cloud Data Center Region
    • From the Region drop-down menu on the top, select the Cloud Region assigned to you by your instructor (check your lab handout).

IMPORTANT: The drop-down displays all Cloud Data Center regions that are assigned to your account. Ensure that you are in the correct region at all times as you work through the labs.

  • You have successfully logged into the Oracle Cloud Infrastructure console.

Lab 1-2: Provisioning an Autonomous Transaction Processing Database

This lab walks you through the steps to create a new Oracle Autonomous Transaction Processing database.

Objectives

  • Learn to provision a new Oracle Autonomous Transaction Processing database.
  • An active Oracle Public Cloud account. You may use your own cloud account or a cloud account that you obtained through a trial or the lab account provided by the instructor.
  • If you are using a laptop or a desktop, ensure that you have a supported version of web browser for Oracle Cloud. Alternatively, you may use the lab VM that has a supported browser pre-installed.

  1. Sign-In to Oracle Cloud Infrastructure Console
    • Sign-in to Oracle Cloud Infrastructure console by following the steps outlined in Sign-In to Oracle Cloud Infrastructure Console lab.
    1. Browse to Autonomous Transaction Processing page
      • From the OCI Console Home Page, browse to Autonomous Database Home Page by clicking the hamburger Menu on the top-left and selecting Autonomous Transaction Processing from the Database section.

Required Artifacts

Lab Steps

  • You will be presented the Autonomous Database Home Page where you will see the list of ATP Databases created in your compartment and for the Region selected.

  • Verify the Workload Type is ATP.

  • From the Compartment drop-down menu on the left, choose the pre-created compartment assigned to you by your instructor (check your lab handout). Again, ensure that you are in the correct Region and the assigned Compartment at all times, especially when you work through the lab steps.
  • Oracle Cloud Infrastructure allows logical isolation of users within a tenant through Compartments. This allows multiple users and business units to share a tenant account while being isolated from each other. More information about Compartments and Policies is provided in the OCI Identity and Access Management documentation here.

Note: The below screenshot assigns Q03 compartment. Please change it to your assigned compartment instead.

Enter your assigned compartment

Note: If you have chosen the compartment you do not have privileges on, you will not be able to see or provision any instances in it.

  • Note that the list of ATP Databases shown is filtered for the Compartment selected and may be further filtered using State and Tags Filters.

  1. Create an Autonomous Transaction Processing Database Instance
    • Provision a new Autonomous Transaction Processing instance from the Autonomous Database Home Page.
    • Click on Create Autonomous Database button to start the instance creation process.

  • This will bring up Create Autonomous Database screen where you specify the configurations for the autonomous database.
  • First, validate the Workload Type selected is Autonomous Transaction Processing.

Enter your assigned compartment

Second, verify that your assigned Compartment is selected (check your lab handout).

  • Next, specify a Display Name and a unique Database Name for the instance.

IMPORTANT: Since the lab account may be shared by others, append a unique (and unused) integer or initials to ATPLab to come up with a unique name, for e.g. ATPLabMA (the name must contain only letters and numbers, starting with a letter, 14 characters max).

  • You can choose an instance shape, specified by the CPU count and storage size. Default CPU Core Count is 1 and Storage is 1 TB. Please keep default selections at this time.

  • Specify the Password for the instance. For this lab, we will be using the following as password:

WElcome_123#

  • For License Type, you will see the following two options:
    • My organization already owns Oracle database software licenses: Oracle allows you to bring your unused on-premise licenses to the cloud and your instances are billed at a discounted rate. This is the default option so ensure you have the right license type for this subscription.
    • Subscribe to new database software licenses and the database cloud service: Your cloud service instance should include database license. This is an all-inclusive cost and you do not need to bring any additional licenses to cloud.
  • Select My Organization Already Owns Oracle Database Software Licenses for the purpose of this lab.

  • Optionally, you may create Tags. Tagging is a metadata system that allows you to organize and track resources within your tenancy. Tags are composed of keys and values that can be attached to resources. More information about Tags and Tag Namespaces is provided in the OCI Identity and Access Management documentation here.

For lab purposes, you will not create a Tag Namespace.

  • Click on Create Autonomous Database to start provisioning the instance.

  • You will see the database in Provisioning status as follows:

  • Click on Autonomous Database link on the top breadcrumb to go back to Autonomous Database Home Page.

  • Note that the Autonomous Database is in Provisioning state.

  • In a few minutes, the status will automatically change to Available indicating the database is ready and provisioned.

  1. Browse to Autonomous Database Details page
    • From the Autonomous Database Home Page, click on your instance name that was just created to browse to Autonomous Database Details Page.

  • The Autonomous Database Details Page displays more information about the instance. Take a note of the various menu buttons that help you manage your autonomous database instance. Notice the green color of the ATP logo indicating the service is available.

  • You have successfully created your first Autonomous Database of type ATP.

Lab 1-3: Connecting to Oracle Autonomous Database

Oracle Autonomous Database is preconfigured to support Oracle Net Services with secure TCPS connections which allows the clients to connect using secure client credentials.

This lab will walk you through the steps of connecting to Oracle Autonomous Database using credentials wallets for secure connections. The tool you will use to connect is Oracle SQL Developer. You will also perform simple queries to validate the connection.

Objectives

  • Download client credentials of autonomous database for secure connectivity.
  • Connect to the autonomous database using a secure connection from Oracle SQL Developer.
  • Run sample queries to validate the connection.
  • Please ensure you have already provisioned an Oracle Autonomous Transaction Processing or an Autonomous Data Warehouse database.
  • If you are not using the instructor supplied lab VM,
  1. Sign-In to Autonomous Database Service Console
    • Sign in to Oracle Cloud Infrastructure Home Page using the credentials provided and the instructions from an earlier lab.
    • Browse to your Autonomous Database Home Page (either Autonomous Transaction Processing or Autonomous Data Warehouse), based on what you created in the previous labs.

Required Artifacts

Lab Steps

  • From the Autonomous Database Home Page, browse to the Autonomous Database Details page by clicking on the service name.

  • Click on Service Console to sign in to Autonomous Database Service Console.

  • Sometimes you may be prompted to Sign In (due to timeout). Fill in the following User and Password and select Sign In:
    • Username: ADMIN
    • Password: <Password> specified during ADB provisioning (e.g. WElcome_123#)

Note: The database is initially created with only one user i.e. the ADMIN user.

  • You will be placed in the Overview page. Notice there is no activity displayed because this is a new instance.

  • Click on Administration link in the left menu to go to the service Administration page.

  • Notice the six options on the Administration page:
    • Download a Connection Wallet: This contains the credentials files used for connectivity to the instance from client applications, tools.
    • Set Administrator Password: Used to change the “Admin” account password Download Oracle.
    • Instant Client: Points to different clients that can be used to connect to the database (like SQL*Plus).
    • Set Resource Management Rules: ADB has pre-created resource consumer groups which are managed here.
    • Manage Oracle ML Users: ML Notebook development environment that can be used with the ADB.
    • Send Feedback to Oracle: Email feedback to Oracle.
  1. Download the Client Credentials Wallet

The connection wallet provides a secure authentication method that can be used to connect to your ADB database. This wallet must be downloaded to the client that will be connecting to the database.

The wallet is downloaded either from the autonomous database Service Console or the Administration page within the console.

  • From the service Administration page click on Download Client Credentials Wallet.

  • On the Download Client Credentials (Wallet) menu, enter a password for the wallet and click Download.
    • Note that this password is separate from the ADMIN user password created earlier (but the same password can be used).

  • Save this file in a secure location. The credentials zip file contains the encryption wallet, Java Keystore and other relevant files to make a secure TLS 1.2 connection to your database from client applications.
  • Navigate to the location in your system where the file was downloaded (typically your Downloads directory).
  • The format of the file is always Wallet_<dbname>.zip. Extract the contents of the wallet into a directory (using a zip utility, usually by right clicking on the file), you will find the following files:

  • There are a few files from the list that you will work with during the hands-on labs. Some tools use the wallet file (.zip) directly whereas some use specific files contained in the wallet. Here is the description of some of these files:
    • Wallet_<dbname>.zip
    • sqlnet.ora
    • tnsnames.ora
    • ojdbc.properties
  1. Connect to Autonomous Database using Oracle SQL Developer

Create a connection for your database using the default administrator account, ADMIN, by following these steps.

  • Launch SQL Developer and click Add Connection on top left.

  • Enter the following in New database connection
    • Connection Name: Name for your connection
    • Username: ADMIN
    • Password: ADMIN user’s password (e.g. WElcome_123#)
    • Save Password: Checked
    • Connection Type: Cloud Wallet
    • Role: Default
    • Configuration File: Click on Browse and select the wallet file you downloaded
    • Service: <Database_Name>_TP. The service name is the Database Name followed by suffix of either TP, TP_URGENT, LOW, MEDIUM, or HIGH. These suffixes determine degree of parallelism used and are relevant for a DSS workload. For OLTP workloads it's safe to select any of them.
    • In the screenshot below, the <Database_Name> is ATPLabUser05 and the Service being connected to is atplabuser05_tp.

  • Test your connection by clicking Test. The Status bar will show Success if it is a successful connection.

  • Save the Connection by clicking Save.

  • Click on Connect.

  • Upon a successful connection you will see a SQL Developer Worksheet.

Note: If you do not see a Worksheet for your connection, just click the Worksheet drop-down on the top  and select your connection to force open a worksheet.

  • Run test a query. The autonomous database you created contains the sample Sales History (SH) schema, we will use this schema to run a test query to make sure everything is working correctly.
  • Copy the contents of the file /home/oracle/labfiles/select_sql1.sql

Note: Do not copy/paste directly from below because sometimes copy/paste from PDF has issues.

SELECT channel_desc,
TO_CHAR(SUM(amount_sold),'9,999,999,999') SALES$,
RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
WHERE sales.prod_id=products.prod_id

AND sales.cust_id=customers.cust_id
AND customers.country_id=countries.country_id
AND sales.time_id=times.time_id
AND sales.channel_id=channels.channel_id
AND times.calendar_month_desc IN ('2000-09','2000-10')
AND country_iso_code='US'
GROUP BY channel_desc;

  • Click F5 or the Run Script button. Verify the query executes and results are displayed.

  • You have successfully connected SQL Developer to autonomous database and validated the connection.

2.     Data Loading and Integration

Lab 2-1: Loading Data Using SQL Developer Import Data Wizard

Traditionally transaction processing systems ingest data through routine transactions or DML operations; Data Warehouses normally perform bulk data loads using Oracle Database tools, and Oracle or other 3rd party data integration tools.

In general, you load data from files local to your client computer or from files stored in a cloud-based object store.

Oracle SQL Developer provides the

Tagged: