Connecting To An Autonomous Transaction Processing Database

Description:-

This topic gives an overview of connecting a client to an Autonomous Transaction Processing database and describes how to obtain the credentials and information you need to create a connection.

Applications and tools connect to Autonomous Transaction Processing databases by using Oracle Net Services (also known as SQL*Net). SQL*Net supports a variety of connection types to Autonomous Transaction Processing databases, including Oracle Call Interface (OCI), ODBC drivers, JDBC OC, and JDBC Thin Driver.

To support connections of any type, you’ll need to download the client security credentials and network configuration settings required to access your database. You’ll also need to supply the applicable TNS names or connection strings for a connection, depending on the client application or tool, type of connection, and service level. You can view or copy the TNS names and connection strings in the DB Connection dialog for your Autonomous Transaction Processing database.

About Downloading Client Credentials
The client credentials .zip that you download contains the following files:

cwallet.sso – Oracle auto-login wallet
ewallet.p12 – PKCS #12 wallet file associated with the auto-login wallet
sqlnet.ora – SQL*Net profile configuration file that includes the wallet location and TNSNAMES naming method
tnsnames.ora – SQL*Net configuration file that contains network service names mapped to connect descriptors for the local naming method
Java Key Store (JKS) files – Key store files for use with JDBC Thin Connections

Wallet files, along with the database user ID and password, provide access to data in your Autonomous Transaction Processing database. Store wallet files in a secure location. Share wallet files only with authorized users. If wallet files are transmitted in a way that might be accessed by unauthorized users (for example, over public email), transmit the wallet password separately and securely

To access the client credentials and connection information for your Autonomous Transaction Processing database

Step1:-Open the navigation menu. Under Database, click Autonomous Transaction Processing.Step2:-Choose your Compartment.

Step3:-In the list of Autonomous Transaction Processing databases, click on the display name of the database you are interested in.

Step4:-Click DB Connection.Step5:-To obtain the client credentials, click Download.

You will be prompted to provide a password to encrypt the keys inside the wallet. The password must be at least 8 characters long and must include at least 1 letter and either 1 numeric character or 1 special character.Save the client credentials zip file to a secure location.

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

Creating An Autonomous Transaction Processing Database

Description:-

This topic describes how to provision a new Autonomous Transaction Processing database using the Oracle Cloud Infrastructure Console

To create an Autonomous Transaction Processing Database

Step1:-  Click Services to show the available services. In the list of available services, select Autonomous Transaction Processing.

Step2:-The console for Autonomous Transaction Processing displays. You can use the List Scope drop-down menu to select a compartment;

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.)

Click Create Autonomous Transaction Processing Database to create a database instance.

Step3:-The Create Autonomous Transaction Processing Database dialog appears. At the top of the dialog, select a compartment for the database from the drop-down list.

In the Create Autonomous Transaction Processing Database dialog, enter the following:
Display Name,Database Name,CPU Core Count,Storage,Administrator Credentials,License Type,Tags

Step4:-Click Create Autonomous Transaction Processing Database.

Database creation in Progress

Step5:- Database ready for use

Successfully Created Autonomous Transaction Processing Database

 

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

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

Connecting SQL Developer To Autonomous Data Warehouse And Creating Tables

 

Description:-

In this article we are going to see how to connect SQL Developer to a database in Oracle Autonomous Data Warehouse, and then create tables.

Steps to create Oracle Autonomous Data Warehouse Database here

Step1:-First, define a SQL Developer connection as the user admin to a database in your Autonomous Data Warehouse.

Step2:-Open SQL Developer on your local computer. In the Connections panel, right-click Connections and select New Connection.

Note:
Do not right-click Database Schema Service Connections. That menu selection is for connecting to a different Oracle cloud service, the Oracle Database Schema Service

Step3:-The New/Select Database Connection dialog appears. 

Step4:-Enter the following information

Connection Name – Enter the name for this cloud connection.
Username – Enter the database username . Use the admin database account that you created in the previous tutorial.
Password – Enter the password that you specified when creating the adwc_user
Connection Type – Select Cloud Wallet.
Configuration File – Click Browse, and select the path to your local copy of 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.
Keystore Password –
If using SQL Developer 18.2.0 or newer: Starting with version 18.2.0, SQL Developer does not require that you enter a Keystore Password and does not provide the Keystore Password field.
If using a version of SQL Developer older than 18.2.0: Enter the password that was generated when you or your Autonomous Data Warehouse administrator downloaded the Client Credentials zip file from the Autonomous Data Warehouse 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.

Step5:- Test the connections

Click Test.
Status: Success displays at the bottom left of the New/Select Database Connection dialog.
Click Connect.
An entry for the new connection appears under Connections

Step6:-Login to the Autonomous Data Warehouse Database

Step7:-Create SH Tables in your Autonomous Data Warehouse Database

After you have connected SQL Developer to your Autonomous Data Warehouse database, use a SQL Developer worksheet to define CREATE TABLE statements to create the SH tables (sales history tables from an Oracle sample schema) in the admin schema. In the next tutorial, you will load data into these tables from an object store.

Copy and paste sh to a SQL Developer worksheet. Run the script to create the SH tables and click Commit.

Step8:-Examine the SH Tables that you Created
Now that you have created the SH tables, take a moment to examine them. In the next tutorial, you will load data into these tables from an object store.

The SQL Developer Script Output panel shows the tables that have been created in the user named admin

Step9:-Note that the new tables also appear in the SQL Developer Connections panel. 

Step10:-Examine the details of each column of the CHANNELS table.

Step11:-Click the Data tab of the CHANNELS table. Note that so far, you have defined tables, but these tables are not yet populated with Data.

Reference:-

https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/adwc/obe_provisioningautonomousdatawarehouse/provisioning_autonomous_data_warehouse.html

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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

 

Step-by-Step Creation of Oracle Autonomous Data Warehouse Cloud Service

                              Autonomous Data Warehouse Cloud

Description:-

In this we are going to see article creating a new Autonomous Data Warehouse (ADW) service on the Oracle Cloud.

  • You have an Oracle Cloud account to build (ADW), if not request a free trial.
    Here: https://cloud.oracle.com/tryit
  • Step by step to create Free Oracle Cloud account here

Creating step by step Oracle Autonomous Data Warehouse Cloud Service

Step1:-Log into Oracle Cloud and click the Create Instance link.

Step2:-Click on the Autonomous Data Warehouse  Create menu option.

Step3:-Click on the Autonomous Data Warehouse

Provisioning a Database information

Step4:-Enter the details of DB Name,CPU & Storage information (default sizes are 1 CPU core and 1TB of storage)

Step4:-Enter the details of Credentials of admin user

Step5: Select the appropriate your licensing model and Click the Create Autonomous Data Warehouse button .

Step5: Autonomous Data Warehouse Database creation in progress .

New service created in <30 seconds

Step6: Autonomous Data Warehouse Database Ready for use.

Step7: Autonomous Data Warehouse Database Details.

 

Connecting to the Database

  • Securely connect using credential wallets via SQL*Net,JDBC,ODBC
  • Wallet can be downloaded from the service console or using REST APIs

Step8:- Downloading the credential wallet (Click db connection menu option)

Step9:Type credential wallet password and click download

Step10:-Zipfile downloaded in the local machine

Step9:-Connecting to the database using the wallet in SQL Developer

Location of the wallet zip file
admin password
Database Service name

Step10:-We have successfully created your first Autonomous Data Warehouse Database Cloud Service.

Reference:-

https://docs.oracle.com/en/cloud/paas/autonomous-data-warehouse-cloud/user/getting-started.html#GUID-00645C09-4E76-44C6-8BBE-B433D501AADB

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
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

Migrating On-Premises Oracle Database to Oracle Cloud service Methods

Description:-

  • In this article we are going to see Migrating On-Premises Oracle Database to Oracle Cloud service Methods.
  • We can migrate our on-premises Oracle Database to an Oracle Database Cloud database using different methods and tools

There are four possible scenarios covered officially by oracle documentation

  1. Migrating from Oracle Database 11g to Oracle Database 11g in the Cloud.
  2. Migrating from Oracle Database 11g to Oracle Database 12c in the Cloud.
  3. Migrating from Oracle Database 12c CDB to Oracle Database 12c in the Cloud.
  4. Migrating from Oracle Database 12c Non-CDB to Oracle Database 12c in the Cloud.

Options for Migrating Databases to Cloud

1.Logical Migration: allows reorganization and optimization
–Data Pump, GoldenGateReplication
2.Physical Migration: simplest, byte-to-byte copy
–RMAN backup, Transportable technologies, Data Guard
–Restore from backup on Oracle Public Cloud
3.Data Movement Options:
–Use public internet
–Private high bandwidth virtual

To determine which migration methods are applicable to your migration scenario, See the below some methods

Migrate Using Backup/Restore

Migrate Using Data Guard

Migrate Using Pluggable Databases Method-1: Unplug/Plug

Migrate Using Pluggable Databases Method-2: Remote Cloning

Migrate Using Pluggable Databases Method-3: Clone a Non-CDB

Upgrade/Migrate Using Data Pump

Upgrade/Migrate Using Full Transportable Export/Import

Upgrade/Migrate Using GoldenGate Cloud Service

 

Catch Me On:- Hariprasath Rajaram

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