Overview Of Oracle Autonomous Transaction Processing

Description:-

  1. Autonomous Transaction Processing is a self-driving database, meaning it eliminates the human labor needed to provision, secure, update, monitor, backup, and troubleshoot a database.
  2. Oracle Cloud Infrastructure’s Autonomous Transaction Processing Cloud Service is a fully managed, preconfigured database environment. You do not need to configure or manage any hardware, or install any software. After provisioning, you can scale the number of CPU cores or the storage capacity of the database at any time without impacting availability or performance. Autonomous Transaction Processing handles creating the database, as well as the following maintenance tasks:

    Backing up the database
    Patching the database
    Upgrading the database
    Tuning the database of Oracle Autonomous Transaction Processing

  3. This reduction in database maintenance tasks reduces costs and frees scarce administrator resources to work on higher-value tasks. Oracle Autonomous Transaction Processing supports mixed workloads for data warehousing and transaction processing.
  4. The newly introduced Oracle Autonomous Transaction Processing Cloud automates the entire data management life cycle.
  5. Self-Driving
    Leverages machine learning to automate database and infrastructure management, monitoring, and tuning
  6. Self-Securing
    Protects from both external attacks and malicious internal users.
    Isolates operational users from application data and ensures up to
    date security patching via software update automation
  7. Self-Repairing
    Protects from all downtime including planned maintenance and
    online application schema updates
  8. Oracle guarantees 99.995 percent availability—that’s less than 2.5 minutes of downtime per month, including patching. Autonomous management lowers administration costs, so you can focus on higher value projects.
  9. Same Experience us Autonomous Transaction Processing uses the same Oracle Database software and technology that runs your existing on-premises database applications, making it compatible with all your existing tools and skill sets.

Key Features of Autonomous Transaction Processing

Managed: Oracle simplifies end-to-end management of the database:

Provisioning new databases
Growing or shrinking storage and compute resources
Patching and upgrades
Backup and recovery

Fully elastic scaling:

Scale compute and storage independently to fit your database workload with no downtime:
Size the Autonomous Transaction Processing to the exact compute and storage required
Scale the Autonomous Transaction Processing on demand: Independently scale compute or storage
Shut off idle compute to save money

Autonomous Transaction Processing supports:

Existing applications, running in the cloud or on-premises
Connectivity via SQL*Net, JDBC, ODBC
Third-party data-integration tools
Oracle cloud services: Analytics Cloud Service, Golden Gate Cloud Service, Integration Cloud Service, and others

Simple Cloud-based Data Loading

Autonomous Transaction Processing provides:
Fast, scalable data-loading from Oracle Object Store, Azure Blob Storage, AWS S3, or on-premises data sources.

Connect to Your Autonomous Transaction Processing

As a database application developer, business user, or DBA, Autonomous Transaction Processing lets you use all your existing design, data integration, analysis, and reporting tools.

Application Developers
Build new applications using use any of the supported client drivers including JDBC, .NET, Python, Node.js, PHP, C/C++, and more.

Business Users
Connect rapid application development tools like APEX* and other standard
reporting tools through JDBC or SQL*Net.

Database Administrators
SQL Developer provides a full database development environment:
SQL Worksheet, data loading wizards, data modeling, and more tailored to the optimized features of Autonomous Transaction Processing.
Create tables, indexes, and materialized views in Autonomous Transaction Processing
Load data into Autonomous Transaction Processing
Copy tables to Autonomous Transaction Processing
Transfer a schema to Autonomous Transaction Processing

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                       FB Group:https://www.facebook.com/groups/894402327369506/                   FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

Loading Your Data Into Autonomous Data Warehouse

 

Description:-

  1. Steps to create oracle Autonomous Data Warehouse database here
  2. Steps to Connecting SQL Developer To Autonomous Data Warehouse And Creating Tables here

Data Loading Options

  1. Data loading via SQL*Net
  2. Suitable for small volumes of data
    SQL*Loader from local filesystem
    ETL scripts that use DML to insert/update data
    Data loading from Oracle Object Storage
  3. Preferred technique for large volumes of data
    Additionally enables data-sharing with other cloud services
  4.  Stage data in Oracle Object Storage, then load into the database using   new PL/SQL APIs

You can load data into Autonomous Data Warehouse using Oracle Database tools, and Oracle and 3rd party data integration tools. You can load data:

from files local to your client computer, or
from files stored in a cloud-based object store

  • For the fastest data loading experience Oracle recommends uploading the source files to a cloud-based object store, such as Oracle Cloud Infrastructure Object Storage, before loading the data into your Autonomous Data Warehouse.
  • To load data from files in the cloud into your Autonomous Data Warehouse database, use the new PL/SQL DBMS_CLOUD package. The DBMS_CLOUD package supports loading data files from the following Cloud sources: Oracle Cloud Infrastructure Object Storage, Oracle Cloud Infrastructure Object Storage Classic, and Amazon AWS S3.
  • Here we are going to see how to load data from Oracle Cloud Infrastructure Object Storage using two of the procedures in the DBMS_CLOUD package:
    create_credential: Stores the object store credentials in your Autonomous Data Warehouse schema.
  • You will use this procedure to create object store credentials in your Autonomous Data Warehouse admin schema that you defined in a previous tutorial.
  • copy_data: Loads the specified source file to a table. The table must already exist in Autonomous Data Warehouse.
  • You will use this procedure to load tables in the admin schema with data from data files staged in the Oracle Cloud Infrastructure Object Storage cloud service.
  • Here we are going to see  how to load data to SH tables (sales history tables from an Oracle sample schema: SALES, COSTS, TIMES, PRODUCTS, CHANNELS, PROMOTIONS, CUSTOMERS, COUNTRIES, SUPPLEMENTARY_DEMOGRAPHICS).
Data files already uploaded to a staging area; otherwise follow the steps below, to upload your data files to the Oracle Cloud Infrastructure Object Storage service.

To use data files already in an object store, your cloud administrator must provide you the object store credentials and the URL path to the files that you will be copying to your Autonomous Data Warehouse tables.
If you will be uploading data files to an object store in Oracle Cloud Infrastructure Object Storage, your cloud administrator must provide you the cloud tenant name, and user name and password with read/write privileges to the object store location where the data is to be stored.

Upload Data Files to Your Object Store

Upload to your cloud-based object store the data files that you want to load to your Autonomous Data Warehouse database. here we are use an object store in the Oracle Cloud Infrastructure Object Storage service.

  1. Log in to your Oracle Cloud Infrastructure Console with the following credentials provided by your Oracle Cloud administrator: cloud tenant, user name, password.
  2. Select Object Storage from the menu at the top left of the Oracle Cloud Infrastructure console. Select Object Storage from the sub-menu.
  3. Select a compartment in which to create a bucket to upload your database table data.
  4. Click Create Bucket to create the storage bucket in which to upload your source files. You will later copy this staged data into database tables in your Autonomous Data Warehouse.
  5. Enter a bucket name, select the standard storage tier, and click Create Bucket
  6. Click Upload Object to begin selecting the data files to upload to the bucket.
  7. Navigate to the location of the data files on your local computer. Drag and drop each file or click Upload Object to upload each file individually.
    This example uploads the data files of the SH tables (sales history tables from an Oracle sample schema). Click here datafiles_for_sh_tables to download a zipfile of the 10 SH data files for you to upload to the object store.
    Note: Alternatively, you can use curl commands to upload large numbers of files.
  8. The data files are uploaded to the bucket. These files staged in the cloud are ready to be copied into the tables of your Autonomous Data Warehouse database. Remain logged in to Oracle Cloud Infrastructure Object Storage.

Create an Object Store Auth Token

To load data from an Oracle Cloud Infrastructure Object Storage object store, you need to create an Auth Token for your object store account. The communication between your Autonomous Data Warehouse database and the object store relies on the Auth Token and username/password authentication.

  1. If you have logged out of Oracle Cloud Infrastructure Object Storage, log back in with the following credentials provided by your Oracle Cloud administrator: cloud tenant, user name, password.
  2. Hover your mouse cursor over the human figure icon at the top right of the console and click User Settings from the drop-down menu.

3. Click Auth Tokens under Resources on the left of the console.

4.Click Generate Token.5.A pop-up dialog appears. Set the Auth Token by performing the following steps:
In the pop-up dialog, enter a description.
Click the Generate Token button.
Copy the generated token to a text file. The token does not appear again.
Click Close.

Create Object Store Credentials in your Autonomous Data Warehouse Schema

Now that you have created an object store Auth Token, store in your Autonomous Data Warehouse admin schema the credentials of the object store in which your data is staged.

Open SQL Developer and connect to your Autonomous Data Warehouse database as user admin. See here, Connecting to SQL Developer and Creating Tables, for steps to connect SQL Developer to your Autonomous Data Warehouse database. In that tutorial, WelcomeADWC1! was the suggested password for the user admin.
In a SQL Developer worksheet, use the create_credential procedure of the DBMS_CLOUD package to store the object store credentials in your admin schema.

Create a credential name. You reference this credential name in the copy_data procedure in the next step.

Specify the credentials for your Oracle Cloud Infrastructure Object Storage service: The username and the object store Auth Token you generated in the previous step.

After you run this script, your object store’s credentials are stored in your Autonomous Data Warehouse admin schema.

Copy Data from Object Store to Autonomous Data Warehouse Database Tables

The copy_data procedure of the DBMS_CLOUD package requires that target tables must already exist in in your Autonomous Data Warehouse database. In the previous article, Connecting SQL Developer and Creating Tables, you created in your Autonomous Data Warehouse admin schema all of the target tables.

Now run the copy_data procedure to copy the data staged in your object store to your Autonomous Data Warehouse admin tables.

  1. In a SQL Developer worksheet, use the copy_data procedure of the DBMS_CLOUD package to copy the data staged in your object store.
    For credential_name, specify the name of the credential you defined in , Create Object Store Credentials in your Autonomous Data Warehouse Schema.
    For file_uri_list, specify the URL that points to the location of the file staged in your object store. The URL is structured as follows. The values you specify are in bold:
    https://swiftobjectstorage.<region name>.oraclecloud.com/v1/<tenant name>/<bucket name>/<file name>
    Click here for an example script. In the script, use your own table names, region name, tenant name, bucket name, and file names.
Note: The region name, tenant name, and bucket name can all be found in one place by clicking the ellipsis option menu and going to file details.
Note: If you receive an error message that your admin does not have read/write privileges into the Object Store, you may need to properly set up your user privileges or contact your administrator to do so.

2.After you run the procedure, observe that the data has been copied from the object store to the tables in your Autonomous Data Warehouse database.

Reference:-

https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/adwc/OBE_Loading%20Your%20Data/loading_your_data.html

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZsxfzXxHD6gTg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba