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

Oracle Cloud (OCI):: Database Service

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.

In this tutorial, I will take you through the steps to create a user-managed Oracle Database (virtual machine) solution in Oracle Cloud Console.

Pre-Requisites:

1. The public key, in OpenSSH format, from the key pair that you plan to use for connecting to the instance via SSH. The following command works with Linux and Git Bash (Windows), and you only have to press enter for all steps. The generated SSH keys can be found inside the "~/.ssh/" folder in Linux or in the "/c/Users/<username>/.ssh" folder in Windows.

$ ssh-keygen

Create Database System:

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 Bare Metal, VM, and Exadata.

3. Click on Create DB System button.

4. On the Create DB System page, provide the specify following information for the DB system:

Select a compartment: 
Name your DB system: 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.
Select an availability domain: 
Select a shape type: 
Select a shape: The shape determines the type of DB system created and the resources allocated to the system. To specify a shape other than the default, click Change Shape, and select an available shape from the list. By Default VM.Standard1.1 selected.


Configure the DB system: Specify the following:

Total node count: The number of nodes in the DB system. The number depends on the shape you select. For virtual machine DB systems, you can specify either 1 or 2 nodes, except for VM.Standard2.1 and VM.Standard1.1, which are single-node DB systems.
Oracle Database software edition:

Configure storage: Specify the following:

Available storage (GB): Virtual machine only. The amount of Block Storage in GB to allocate to the virtual machine DB system.
Total storage (GB): Virtual machine only. The total Block Storage in GB used by the virtual machine DB system.

Add public SSH keys: The public key portion of each key pair you want to use for SSH access to the DB system.

Choose a license type: 

Specify the network information:

Virtual Cloud Network: The VCN in which to launch the DB system.
Client subnet: The subnet to which the virtual machine DB system should attach.
Hostname prefix: Choice of host name for the virtual machine DB system.

Click Next.

5. Provide information for the initial database:

Database name: The name for the database.
Database version: The version of the initial database created on the DB system when it is launched.
Note: If you are launching a DB system with a virtual machine shape, you have option of selecting an older database version. Check Display all available versions

Create administrator credentials: A database administrator SYS user will be created with the password you supply.

Username: SYS
Password:
Confirm password: Re-enter the SYS password you specified.

Select workload type: Online Transactional Processing (OLTP) or Decision Support System (DSS)

Configure database backups: 

Click Show Advanced Options to specify advanced options for the initial database:

Character set: The character set for the database. The default is AL32UTF8.
National character set: The national character set for the database. The default is AL16UTF16.

Click on 'Create DB System'

6. An error occurred.

Error Message: VCN RESOLVER FOR DNS AND DNS LABEL must be enabled for all subnets used to launch the specified shape.

7. Open the navigation menu. Under Core Infrastructure, go to Networking and click 'Virtual Cloud Networks'. Existing VCN list will be displayed. Select the VCN that has been used to create DB. Check DNS is enabled or not for the VCN.If not enabled 'Create Virtual Cloud Network' and choose option 'USE DNS HOSTNAMES IN THIS VCN'.

8. Go back to Create DB System page and Specify the network information as per new VCN created with DNS option. Click on 'Create DB System'

9. Another error occurred.

Error Message: The database version must be one of 11.2.0.4 or 11.2.0.4.181016 etc.

10. Change the database version and Click on 'Create DB System'.

11. Another error occurred.

Error Message: The following service limits were exceeded in AD lad-ad-2: vm-standard1-ocpu-count. Request a service limit increase from the service limits page in the console.

12. Open the navigation menu. Under Governance and Administration, go to Governance and click 'Service Limits'. Check CPU limit for VM.Standard1.1. It's 0. So choose other shape having capacity. In this case, VM.Standard2.1 has limit available.

13. Go back to Create DB System page and choose VM.Standard2.1 as shape. Click on 'Create DB System'.

14. Database Provisioning page will be displayed. Navigate to Databases and Nodes tabs. Copy the Public IP Address.

15. Under Databases tab, click on Database Name or choose 'View Database' option to view database details. Check the database port number. Default to 1521.

16. In Database Details page, .Click on 'DB Connection' button. Copy the connection details.

Add Ingress Rule to Connect to Database:

17. Open the navigation menu. Under Core Infrastructure, go to Networking and click 'Virtual Cloud Networks'. Existing VCN list will be displayed. Select the VCN and Click on 'Security List'.

18. Check the ingress rule, whether TCP Port#1521 enabled or not. If not click on 'Add Ingress Rules'.

19. In the Add Ingress Rules dialog box, specify as following and then click on 'Add Ingress Rules' button. Review the added rules information.
SOURCE CIDR: 0.0.0.0/0
IP PROTOCOL: TCP
SOURCE PORT RANGE: ALL
DESTINATION PORT RANGE: 1521

20. Open SQL Developer and Add New Database Connection. In Connection Window, specify as following and click on Test, followed by Connect.
Username: SYS
Password: As specified in the Create DB System page
Host name: Copied Public IP Address
Port: 1521
Service Name: Copied Service Name

21. Execute SQL queries to review database info.

Connect to Database Node via Putty:

22. Open puttygen to save private rsa key as .ppk file.
23. Open putty and provide instance public IP address and choose private ppk file. Click Open. Log in user: opc

24. Set environment variable and run sqlplus command to login via CLI.

Scale Storage Up:

25. Open the navigation menu. Under Database, click Bare Metal, VM, and Exadata. Click on the database to open database details page. In database details page, Click on 'Scale Up Storage' option.

26. Select new storage size and Click on Update.

Terminate:

27. Open the navigation menu. Under Database, click Bare Metal, VM, and Exadata. Click on the database to open database details page. In database details page, click on 'Terminate'.