Tuesday, August 13, 2019

Oracle Cloud (OCI):: Autonomous Database (ATP/ADW)

The Database service in Oracle Cloud Infrastructure offers autonomous and user-managed Oracle Database solutions.

  • Autonomous databases are preconfigured, fully-managed environments that are suitable for either transaction processing or for data warehouse workloads. 
  • User-managed solutions are bare metal, virtual machine, and Exadata DB systems that you can customize with the resources and settings that meet your needs.

Autonomous Database Features:


  • No Human Labor: Database automatically upgrades, patches, and tunes itself while running; automates security updates with no downtime window required.
  • No Human Error: SLA guarantees 99.995% reliability and availability, which minimizes costly planned and unplanned downtime to less than 30 minutes a year.
  • No Manual Performance Tuning: Database consumes less compute and storage because of machine learning and automatic compression. Combined with lower manual admin costs, Oracle offers even bigger cost savings.
  • Self-Securing: Oracle Autonomous Database protects you from both external attacks and malicious internal users, which means you can stop worrying about cyberattacks on unpatched or unencrypted databases.
  • SQL Developer Web
  • APEX Workspace
  • OML Notebooks
  • End-to-end automation for:
    • Provisioning
    • Security
    • Updates
    • Availability
    • Performance
    • Change management
    • Errors

In this tutorial, I will take you through the steps to create an Autonomous Oracle Database solution (Autonomous Transaction Processing and Autonomous Data Warehouse) in Oracle Cloud Console.

Create Autonomous Transaction Processing (ATP) Database:


1. Login to Oracle Cloud Console. Browse http://cloud.oracle.com/ and click Sign In at the top of the page. Enter the Account Name and click on Next. Select Identity Provider as 'oracleidentitycloudservice' and click on Continue button under Single Sign-On Section. Enter Username and Password, click on Sign In. You're then directed to the Oracle Cloud Console.

Refer to post to get trail access to Oracle Cloud.

2. Open the navigation menu. Under Database, click Autonomous Transaction Processing.



3. Click Create Autonomous Database.



4. On the Create Autonomous Database page, provide the specify following information:

Choose a compartment:
Display Name: A friendly, display name for the DB system. The name doesn't need to be unique. An Oracle Cloud Identifier (OCID) will uniquely identify the DB system.
Database Name:  The name for the database.
Choose a workload Type: Transaction Processing
Choose a deployment Type: Serverless or Dedicated Infrastructure
Configure the Database: Choose CPU Core Count and Storage(TB)

Create Administrator Credentials:

Username: ADMIN (Default, can't be changed)
Password:
Confirm Password: Re-enter the SYS password you specified.

Choose a license type: BYOL or License Included

Click on 'Create Autonomous Database'




5. Autonomous Database Provisioning page will be displayed. Once it's provisioned click on 'DB Connection'.




6. Enter password to download the client credentials. Click on 'Download' to download the client credentials.




Connect to Autonomous Database using SQL Developer Client:


7. Open SQL Developer and Add New Database Connection. In Connection Window, specify as following and click on Test, followed by Connect.

Username: ADMIN
Password: As specified in the Create DB System page
Choose Connection Type as 'Cloud Wallet'
Configuration File: Browse the wallet zip file downloaded in previous step.
Select Service Profile.




8. Execute SQL queries to review database info.



Autonomous Database Service Console:


9. In Autonomous Database page, Click on 'Service Console' to navigate to service console page.



10 .Service Console --> Overview  - Overall Status - Storage Usage, CPU Utilization,



11. Service Console --> Activity - Monitoring Load and SQL



12. Service Console --> Administration - Administrator options. Here you can download the client credentials, reset administrator password, set resource management rules etc.



13. Set Resource Management Rules



14. Service Console --> Development - Developer options. Here you can APEX workspace, SQL Developer Web, OML Notebooks etc.



15. APEX Workspace






16. SQL Developer Web









17. OML Notebooks




Terminate Autonomous Transaction Processing Database:


18. Go to Oracle Cloud Console. Open the navigation menu. Under Database, click Autonomous Transaction Processing. Click on the Autonomous Database to open autonomous database details page. In autonomous database details page, click on Actions --> 'Terminate'.





Create Autonomous Data Warehouse (ADW) Database:


19. Login to Oracle Cloud Console. Open the navigation menu. Under Database, click Autonomous Data Warehouse




20. Click Create Autonomous Database.



21. On the Create  Autonomous Database.page, provide the specify following information:

Choose a compartment:
Display Name: A friendly, display name for the DB system. The name doesn't need to be unique. An Oracle Cloud Identifier (OCID) will uniquely identify the DB system.
Database Name:  The name for the database.
Choose a workload Type: Data Warehouse
Configure the Database: Choose CPU Core Count and Storage(TB)

Create Administrator Credentials:

Username: ADMIN (Default, can't be changed)
Password:
Confirm Password: Re-enter the SYS password you specified.

Choose a license type: BYOL or License Included

Click on 'Create Autonomous Database'




22. Autonomous Database Provisioning page will be displayed. Once it's provisioned click on 'DB Connection'.




23. Enter password to download the client credentials. Click on 'Download' to download the client credentials.





Terminate Autonomous Data Warehouse Database:


24. Go to Oracle Cloud Console. Open the navigation menu. Under Database, click Autonomous Data Warehouse. Click on the Autonomous Database to open autonomous database details page. In autonomous database details page, click on Actions --> 'Terminate'.