using-oracle-autonomous-data-warehouse-shared-exadata-infrastructure.pdf - Oracle\u00ae Cloud Using Oracle Autonomous Data Warehouse on Shared Exadata

Using-oracle-autonomous-data-warehouse-shared-exadata-infrastructure.pdf

This preview shows page 1 out of 359 pages.

You've reached the end of your free preview.

Want to read all 359 pages?

Unformatted text preview: Oracle® Cloud Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure E85417-94 May 2020 Oracle Cloud Using Oracle Autonomous Data Warehouse on Shared Exadata Infrastructure, E85417-94 Copyright © 2018, 2020, Oracle and/or its affiliates. Primary Author: Thomas Van Raalte This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited. The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing. If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable: U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs) and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government end users are "commercial computer software" or “commercial computer software documentation” pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the license contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloud services are defined by the applicable contract for such services. No other rights are granted to the U.S. Government. This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc, and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group. This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle. Contents Preface Audience xiii Documentation Accessibility xiii Related Documents xiii Conventions xiv Part I 1 2 Using Autonomous Data Warehouse Getting Started with Autonomous Data Warehouse About Autonomous Data Warehouse 1-1 Before You Begin with Autonomous Data Warehouse 1-3 Key Features of Autonomous Data Warehouse 1-4 Typical Workflow for Using Autonomous Data Warehouse 1-5 Use Oracle Machine Learning with Autonomous Data Warehouse 1-6 Build Reports and Dashboards with Analytics in Autonomous Data Warehouse 1-7 Use Sample Data Sets in Autonomous Data Warehouse 1-7 Security and Authentication in Autonomous Data Warehouse 1-9 Always Free Autonomous Database 1-9 Preview Versions for Autonomous Database 1-12 Oracle Database Versions and Availability by Region 1-13 Quickstart Tutorials 1-15 Quickstart Tutorial: Getting Started and Provisioning an Autonomous Database 1-15 Quickstart Tutorial: Connecting SQL Developer to Autonomous Data Warehouse 1-15 Quickstart Tutorial: Loading Your Data 1-15 Quickstart Tutorial: Running a Query on Sample Data 1-15 Quickstart Tutorials for Oracle Machine Learning with Autonomous Data Warehouse 1-15 Connecting to Autonomous Data Warehouse About Connecting to an Autonomous Data Warehouse Instance 2-1 iii Connect Autonomous Data Warehouse Using a Client Application 2-4 About Connecting to Autonomous Data Warehouse Using a Client Application 2-5 Prepare for Oracle Call Interface (OCI), ODBC, and JDBC OCI Connections 2-5 Prepare for JDBC Thin Connections 2-8 Using Applications with Support for Wallets 2-8 Download Client Credentials (Wallets) Connect to Autonomous Data Warehouse Using Oracle Database Tools 2-9 2-11 Connect with Oracle SQL Developer (18.2 or later) 2-11 Connect with Oracle SQL Developer (earlier than Version 18.2) 2-14 Connect with SQL*Plus 2-16 Connect with Oracle SQLcl Cloud Connection 2-17 Connect with Built-in SQL Developer Web 2-18 About SQL Developer Web 2-18 Access SQL Developer Web as ADMIN 2-19 Provide SQL Developer Web Access to Database Users 2-20 JDBC Thin Connections and Wallets 2-22 JDBC Thin Driver Connection Prerequisites 2-22 Using a JDBC URL Connection String with JDBC Thin Driver 2-23 Using a JDBC Connection with 18.3 JDBC Driver 2-24 Connecting Using JDBC Thin Driver 12.2 or Older 2-26 JDBC Thin Connections with an HTTP Proxy 2-28 Oracle Call Interface (OCI) Connections and Wallets 2-29 Predefined Database Service Names for Autonomous Data Warehouse 2-30 Connect with Oracle Analytics Desktop 2-31 Connect with Oracle Analytics Cloud 2-31 Connect Applications to Autonomous Data Warehouse 2-31 Connect with Microsoft .NET and Visual Studio 2-31 Connect with JDBC Thin Driver and UCP 2-32 Connect with Python, Node.js, and other Scripting Languages 2-32 Connection and Networking Options and Features Using ACLs, VCNs, and Private Endpoints with Autonomous Database 2-35 2-36 About Network Access Options 2-36 Overview of Restricting Access with ACLs 2-37 Overview of Private Endpoints 2-38 Connect with Oracle Cloud Infrastructure FastConnect 2-38 Access Autonomous Data Warehouse with VCN Transit Routing 2-38 Access Autonomous Data Warehouse with Service Gateway 2-39 Enable and Disable Application Continuity 2-39 Use Database Resident Connection Pooling with Autonomous Database 2-40 Use Database Links with Autonomous Data Warehouse Create Database Links from Autonomous Database to Other Databases 2-42 2-42 iv 3 Create Database Links from Other Databases to Autonomous Database 2-45 Drop Database Links 2-47 Loading Data with Autonomous Data Warehouse About Data Loading 3-1 Load Data from Files in the Cloud 3-2 Create Credentials and Copy Data into an Existing Table 3-2 Create Credentials and Load Data Pump Dump Files into an Existing Table 3-4 Load JSON Documents with Autonomous Database 3-6 About Loading JSON Documents 3-6 Load a Line-Delimited JSON Document into a Collection 3-6 Load an Array of JSON Documents into a Collection 3-8 Monitor and Troubleshoot COPY_COLLECTION Loads Monitor and Troubleshoot Loads 3-11 List Credentials 3-13 Delete Credentials 3-13 Import Data Using Oracle Data Pump on Autonomous Data Warehouse 3-13 Export Your Existing Oracle Database to Import into Autonomous Data Warehouse 3-14 Import Data Using Oracle Data Pump Version 18.3 or Later 3-15 Import Data Using Oracle Data Pump (Versions 12.2.0.1 and Earlier) 3-17 Access Log Files for Data Pump Import 3-19 Load Data from Local Files with SQL Developer Web Load Data into Existing Autonomous Database Table with SQL Developer Web 4 3-10 3-19 3-20 Use Oracle GoldenGate to Replicate Data to Autonomous Data Warehouse 3-24 Load Data from Local Files Using SQL*Loader 3-24 Managing DML Performance and Compression 3-24 Querying External Data with Autonomous Data Warehouse Query External Data 4-1 Query External Data with Parquet or Avro Source Files 4-3 Query External Partitioned Data 4-6 Query Hybrid Partitioned Data 4-8 Query External Data Pump Dump Files 4-10 Validate External Data 4-12 Validate External Partitioned Data 4-13 Validate Hybrid Partitioned Data 4-14 View Logs for Data Validation 4-15 v 5 Creating Dashboards, Reports, and Notebooks with Autonomous Data Warehouse About Creating Dashboards, Reports, and Notebooks with Oracle Machine Learning 5-1 6 Work with Oracle Machine Learning for Data Access, Analysis, and Discovery 5-2 Work with Analytics and Visualization 5-4 Using Oracle Analytics Cloud with Autonomous Data Warehouse 5-4 Working with Oracle Analytics Desktop in Autonomous Data Warehouse 5-5 Moving Data from Autonomous Data Warehouse to Other Oracle Databases Move Data with Data Pump Export to an Autonomous Database Directory Use Data Pump to Create a Dump File Set on Autonomous Data Warehouse 6-2 Move Dump File Set from Autonomous Data Warehouse to Your Cloud Object Store 6-4 Move Data with Data Pump Export to Object Store 7 8 6-1 6-5 Use Oracle Data Pump to Export Data to Object Store (Version 19.6 and Earlier) 6-6 Use Oracle Data Pump to Export Data to Object Store (Version 19.7 or Later) 6-9 Download Dump Files, Run Data Pump Import, and Clean Up Object Store 6-12 Access Log Files for Data Pump Export 6-13 Developing RESTful Services in Autonomous Database About Oracle REST Data Services in Autonomous Database 7-1 Access RESTful Services and SODA for REST 7-1 Develop with Oracle REST Data Services on Autonomous Database 7-3 Use SODA for REST with Autonomous Database 7-4 Creating Applications with Oracle Application Express in Autonomous Database About Oracle Application Express 8-1 Access Oracle Application Express Administration Services 8-2 Create Oracle Application Express Workspaces in Autonomous Data Warehouse 8-4 Access Oracle Application Express App Builder 8-5 Create Oracle Application Express Developer Accounts 8-5 Use JSON Data with Oracle Application Express 8-6 Create A View with JSON Data Guide 8-6 Create A View with the json_table Function 8-7 vi Use Web Services with Oracle Application Express 8-7 Send Email from Oracle Application Express 8-9 Restrictions and Limitations for Oracle Application Express with Autonomous Data Warehouse 9 10 11 12 Creating and Managing Directories on Autonomous Database Create Directory in Autonomous Database 9-1 Drop Directory in Autonomous Database 9-2 List Contents of Directory in Autonomous Database 9-3 Copy Files Between Object Store and a Directory in Autonomous Database 9-3 Sending Mail with Email Delivery on Autonomous Database Send Mail Using Email Delivery on Autonomous Database 10-1 SMTP Send Mail Sample Code 10-2 Use Oracle Extensions for IDEs to Develop Applications Use Oracle Cloud Infrastructure Toolkit for Eclipse 11-1 Use Oracle Developer Tools for Visual Studio 11-2 Use Oracle Developer Tools for VS Code 11-3 Using JSON Documents with Autonomous Database Work with Simple Oracle Document Access (SODA) in Autonomous Database 12-1 Work with JSON Documents Using SQL and PL/SQL APIs on Autonomous Database 12-3 Load JSON Documents with Autonomous Database 12-3 Part II 13 8-10 Managing and Monitoring Autonomous Data Warehouse Starting, Stopping, and Scaling Autonomous Data Warehouse Provision Autonomous Data Warehouse 13-1 Start Autonomous Data Warehouse 13-4 Stop Autonomous Data Warehouse 13-4 Restart Autonomous Data Warehouse 13-5 Terminate Autonomous Data Warehouse 13-5 Add CPU or Storage Resources or Enable Auto Scaling 13-6 Remove CPU or Storage Resources or Disable Auto Scaling 13-7 vii Use Auto Scaling 14 Managing Users on Autonomous Data Warehouse Create Users with Autonomous Data Warehouse 14-1 Remove Users with Autonomous Data Warehouse 14-3 Manage the Administrator Account on Autonomous Data Warehouse 14-3 Change the Administrator Password in Autonomous Data Warehouse 14-3 Unlock the Administrator Account in Autonomous Data Warehouse 14-4 Manage User Privileges with Autonomous Data Warehouse 14-5 Create and Update User Accounts for Oracle Machine Learning 14-6 Create User 14-6 Add Existing Database User Account to Oracle Machine Learning 14-8 Use Microsoft Active Directory with Autonomous Database Configure CMU with Microsoft Active Directory on Autonomous Database 14-9 14-9 Add Microsoft Active Directory Roles on Autonomous Database 14-13 Add Microsoft Active Directory Users on Autonomous Database 14-14 Connect to Autonomous Database with Active Directory User Credentials 14-15 Tools Restrictions with Active Directory on Autonomous Database 14-16 Verify Active Directory User Connection Information with Autonomous Database 15 13-8 14-16 Remove Active Directory Users and Roles on Autonomous Database 14-17 Disable Active Directory Access on Autonomous Database 14-17 Managing and Monitoring Performance of Autonomous Data Warehouse Monitor the Performance of Autonomous Data Warehouse 15-1 Monitor Activity and Utilization 15-2 Console Overview 15-3 Console Activity 15-4 Monitor SQL Statements 15-8 Managing Concurrency and Priorities on Autonomous Data Warehouse 15-10 Manage CPU/IO Shares on Autonomous Data Warehouse 15-12 Manage Runaway SQL Statements on Autonomous Data Warehouse 15-14 Manage Optimizer Statistics on Autonomous Data Warehouse 15-15 Manage Automatic Indexing on Autonomous Database 15-16 Monitor Autonomous Data Warehouse with Performance Hub 15-17 Monitor the Performance of Autonomous Data Warehouse with Oracle Management Cloud 15-19 Monitor Performance with Autonomous Database Metrics 15-19 View Metrics for an Autonomous Database Instance 15-20 View Metrics for Autonomous Databases in a Compartment 15-20 viii Autonomous Database Metrics and Dimensions 16 17 Backing Up and Restoring Autonomous Data Warehouse About Backup and Recovery on Autonomous Data Warehouse 16-1 Restore and Recover your Autonomous Data Warehouse Database 16-3 Manual Backups on Autonomous Data Warehouse 16-7 Configure Manual Backups on Autonomous Data Warehouse 16-8 Perform Manual Backups on Autonomous Data Warehouse 16-9 Cloning, Moving, or Upgrading an Autonomous Data Warehouse Database Clone an Autonomous Data Warehouse Instance 17-1 Clone Autonomous Data Warehouse from a Backup 17-4 Notes for Cloning Autonomous Database 17-7 Resource Management Rules and Performance Data for a Cloned Database 17-8 Optimizer Statistics for a Cloned Database 17-8 Move an Autonomous Data Warehouse Database to a Different Compartment Upgrade an Autonomous Database Instance to Oracle Database 19c 18 15-21 17-9 17-10 Manually Upgrade to Oracle Database 19c 17-11 Manually Upgrade by Performing a Full Clone 17-13 Manually Upgrade by Cloning from a Backup 17-14 Automatically Upgrade to Oracle Database 19c 17-14 Wallet Security, Data Safe, and Oracle Database Vault Rotate Wallets for Autonomous Database 18-1 Update License Type on Autonomous Database 18-4 Update Always Free Instance to Paid with Autonomous Database 18-4 Safeguard Your Data with Data Safe on Autonomous Database 18-5 Use Oracle Database Vault with Autonomous Database 18-5 Oracle Database Vault Users and Roles on Autonomous Database 18-5 Enable Oracle Database Vault on Autonomous Database 18-6 Disable Oracle Database Vault on Autonomous Database 18-7 Disable User Management with Oracle Database Vault on Autonomous Database 18-7 Enable User Management with Oracle Database Vault on Autonomous Database 18-8 ix 19 20 Configuring Network Access with Access Control Rules (ACLs) and Private Endpoints Configure an Access Control List with Autonomous Database 19-1 Configure Private Endpoints with Autonomous Database 19-4 Private Endpoint Configuration Examples on Autonomous Database 19-7 Maintenance Windows, Work Requests and Managing Events and Notifications About Events and Notifications 20-1 About Work Requests 20-1 About Autonomous Database Maintenance Windows 20-3 Part III A Appendixes Autonomous Database Supplied Package Reference Summary of DBMS_CLOUD Subprograms A-1 COPY_COLLECTION Procedure A-2 COPY_DATA Procedure A-3 COPY_DATA Procedure for Parquet or Avro Files A-5 CREATE_CREDENTIAL Procedure A-6 CREATE_CREDENTIAL Procedure (OCI Signing Key Credentials) A-7 CREATE_EXTERNAL_TABLE Procedure A-8 CREATE_EXTERNAL_TABLE Procedure for Parquet or Avro Files A-10 CREATE_EXTERNAL_PART_TABLE Procedure A-11 CREATE_HYBRID_PART_TABLE Procedure A-13 DELETE_ALL_OPERATIONS Procedure A-14 DELETE_FILE Procedure A-15 DELETE_OBJECT Procedure A-15 DROP_CREDENTIAL Procedure A-16 GET_OBJECT Procedure A-16 LIST_FILES Function A-17 LIST_OBJECTS Function A-18 PUT_OBJECT Procedure A-20 UPDATE_CREDENTIAL Procedure A-20 VALIDATE_EXTERNAL_TABLE Procedure A-21 VALIDATE_EXTERNAL_PART_TABLE Procedure A-22 VALIDATE_HYBRID_PART_TABLE Procedure A-23 DBMS_CLOUD Package File URI Formats A-24 x DBMS_CLOUD Package Format Options A-29 DBMS_CLOUD Package Format Options for Parquet and Avro A-33 DBMS_CLOUD Package Parquet to Oracle Data Type Mapping A-34 DBMS_CLOUD Package Avro to Oracle Data Type Mapping A-36 DBMS_CLOUD Package Parquet and AVRO to Oracle Column Name Mapping A-37 Summary of DBMS_CLOUD_ADMIN Subprograms A-39 CREATE_DATABASE_LINK Procedure A-39 DISABLE_APP_CONT Procedure A-41 DROP_DATABASE_LINK Procedure A-42 ENABLE_APP_CONT Procedure A-42 GRANT_TABLESPACE_QUOTA Procedure A-43 Summary of DBMS_MAX_STRING_SIZE Subprograms CHECK_MAX_STRING_SIZE Function A-45 MODIFY_MAX_STRING_SIZE Procedure A-45 Summary of DBMS_CLOUD_MACADM Subprograms A-46 CONFIGURE_DATABASE_VAULT Procedure A-47 DISABLE_DATABASE_VAULT Procedure A-47 DISABLE_USERMGMT_DATABASE_VAULT Procedure A-48 ENABLE_DATABASE_VAULT Procedure A-48 ENABLE_USERMGMT_DATABASE_VAULT Procedure A-49 DBMS_CLOUD REST APIs B A-44 A-49 DBMS_CLOUD REST API Overview A-50 DBMS_CLOUD REST API Constants A-50 GET_RESPONSE_HEADERS Function A-51 GET_RESPONSE_RAW Function A-51 GET_RESPONSE_STATUS_CODE Function A-52 GET_RESPONSE_TEXT Function A-52 SEND_REQUEST Function A-53 DBMS_CLOUD REST API Examples A-54 Autonomous Data Warehouse for Experienced Oracle Database Users About the Autonomous Data Warehouse Database B-1 Autonomous Data Warehouse Oracle Database 19c Features B-3 Restrictions for Database Initialization Parameters B-3 Restrictions for SQL Commands B-4 Restrictions for Data Types B-7 Managing Partitions, Indexes, and Materialized Views B-9 Restrictions and Notes for Database PL/SQL Packages B-9 Restrictions for Database Features B-11 xi C D E Restrictions for Oracle XML DB B-11 Restrictions for Oracle Text B-12 Restrictions for Oracle Spatial and Graph B-13 Restrictions for Oracle Application Express B-14 Restrictions for Oracle Flashback B-14 Restrictions for Fast Application Notification (FAN) B-15 List of Restricted and Removed Oracle Features B-15 Migrating Amazon Redshift to Autonomous Data Warehouse Autonomous Data Warehouse Redshift Migration Overview C-1 Connect to Amazon Redshift C-2 Connect to Autonomous Data Warehouse C-4 Start the Cloud Migration Wizard C-5 Review and Finish the Amazon Redshift Migration C-11 Use Generated Amazon Redshift Migration Scripts C-12 ...
View Full Document

  • Fall '16
  • Test, The Land, Oracle Database, Oracle Corporation, Oracle software, autonomous data warehouse, Oracle Autonomous Data Warehouse

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture