Oracle Database Cloud - Database as a Service Quick Start


Options



Before You Begin

Purpose

In this tutorial, you learn how to quickly create a service instance in the Oracle Database Cloud - Database as a Service (DBaaS), use SQL Developer to perform operations on the data in your cloud database, and use the DBaaS Monitor to check the status of your database.  

Time to Complete

Approximately 90 minutes.

Background

Oracle Database Cloud - Database as a Service provides a platform to create full Oracle database instances in a virtual machine (VM). It's the same familiar Oracle Database –  now in a cloud.

You have full access to the features and operations available with Oracle Database, but with Oracle hosting the VM and cloud storage and (optionally) performing certain database maintenance and management operations automatically. You have full OS root access and SYS database access, so you have full administrative control of your VM and your database via SSH, SQL Developer, Data Pump, SQL*Plus and other tools.

Scenario

In this tutorial, you create a service instance using a wizard interface, use SQL Developer to perform some simple operations against the data in your cloud database, and use the DBaaS Monitor to check your database status and parameters.

What Do You Need?

Before starting this tutorial, you should:

Generating an SSH Key Pair Using PuTTY Key Generator

When you define your Oracle DBaaS database instance, you will need to provide a secure shell (SSH) public key to establish secure connections. Perform the following steps to generate an SSH key pair using the PuTTY Key Generator on Windows.

  1. Find puttygen.exe in the PuTTY folder on your computer, for example, C:\Program Files (x86)\PuTTY. Double-click puttygen.exe to open it.

    PuTTY Key Generator
    Description of this image
  2. Accept the default key type, SSH-2 RSA.

    SSH-2 is the most recent version of the SSH protocol (and is incompatible with SSH-1). RSA and DSA are algorithms for computing digital signatures.

    PuTTY Key Generator parameters
    Description of this image
  3. Set the Number of bits in a generated key to 2048 bits, if it is not already set with that value.

    This sets the size of your key and thus the security level. A minimum of 2048 bits is recommended for SSH-2 RSA.

    PuTTY Key Generator parameters
    Description of this image
  4. Click Generate.

    Generate button
    Description of this image
  5. Move your mouse around the blank area to generate randomness to the key.

    Note: the dotted red line in the image below is for illustration purposes only. It does not appear in the generator pane as you move the mouse.

    Generating key - status
    Description of this image
  6. The generated key appears under Public key for pasting into OpenSSH authorized_keys file.

    Generated public key
    Description of this image
  7. The key comment is the name of the key that you will use to identify it. You can keep the generated key comment or create your own.

    Key comment field
    Description of this image
  8. If you want to password-protect your key, enter a Key passphrase and enter it again for Confirm passphrase. When you reload a saved private key, you will be asked for the passphrase, if one is set.

    Key passphrase fields
    Description of this image

    While a passphrase is not required, you should specify one as a security measure to protect the private key from unauthorized use.

    There is no way to recover a passphrase if you forget it.

  9. Save the private key of the key pair. Depending on how you work with the private key in the future, you may need one saved in the PuTTY PPK format and one saved in OpenSSH format. Let's save the private key in both formats.

    1. To save the key in the PuTTY PPK format, click Save private key to save the private key of the key pair.

      Save privatge key button
      Description of this image

      You can name it anything you want, although you may want to use the same name as you used for the key comment. The private key is saved in PuTTY's Private Key (PPK) format, which is a proprietary format that works only with the PuTTY toolset.

      Save private key - file name and type
      Description of this image

      You can use this key whenever you use Putty to perform SSH actions.

    2. To save the key in OpenSSH format, open the Conversions menu and select Export SSH key. This will be the same key as above, just saved in a different format.

      Save private key in OpenSSH format
      Description of this image

      You can name it anything you want, but to keep track of your keys, you should give it the same name as the key you saved in PPK format in the previous step. You can also use any extension (or no extension), but let's use .ssh, to make it clear what format it is.

      Save private key in OpenSSH format
      Description of this image

      You can use this key whenever you use OpenSSH to perform SSH actions using ssh utitlities that support OpenSSH, for example when using Linux in a command shell.

  10. Now you need to create the public key to be paired with the private key(s) you just created. However, clicking the Save public key button will create a public key that won't work with Oracle Cloud services in certain cases. So, for the purposes of this tutorial, there is no reason to save a public key using the Save public key button.

    Do not click Save public key button
    Description of this image

    Instead, proceed as follows.

  11. In the PuTTY Key Generator, select all of the characters under Public key for pasting into OpenSSH authorized_keys file.

    Make sure you select all of the characters, not just the ones you can see in the narrow window. If a scroll bar is next to the characters, you aren't seeing all the characters, and you will need to scroll to select all of the characters.

    Public key for pasting
    Description of this image
  12. Right click somewhere in the selected text and select Copy from the menu.

    Public key for pasting - copy
    Description of this image
  13. Open a text editor and paste the characters, just as you copied them. Start at the first character in the text editor, and do not insert any line breaks.

    Public key pasted into text editor
    Description of this image
  14. Save the key as a text file, using the same root name as you used for the private key. Add a .pub extension. You can give it any extension you want, but .pub is a useful convention to indicate that this is a public key.

    Save public key - file name and type
    Description of this image
  15. Write down the names of your public and private keys, and note where they are saved. You will need the public key when you create a service instance in the next section. You would also need the private key when trying to access a service instance's virtual machine via SSH.

Creating a Service Instance

Invoking the Create Database Cloud Service Wizard

Log in to the Oracle Database Cloud Service (DBaaS) console using the credentials provided by your Oracle Cloud account administrator. For details on accessing the console, see the documentation on Accessing the Database Cloud Service (DBaaS) Console.

  • Your administrator may provide you a link to the My Services console, from which you can choose Oracle Database Cloud Service (DBaaS).
  • Or,  your administrator may provide you a direct link to the Oracle Database Cloud Service (DBaaS) console.
  1. To create a new database instance, in the Database Cloud Service console click Create Service.

  2. The Create Database Cloud Service Instance wizard is invoked, displaying the Subscription Type page.

    subscription type page
    Description of this image

Defining the Subscription Type


Use the Subscription Type page to select the service level and billing frequency.
  1. The two types of service level are Oracle Database Cloud Service and Oracle Database Cloud Service - Virtual Image.

    • You can choose the Oracle Database Cloud Service service level to have the wizard create a database instance for you, using configuration options provided in the wizard. This service level also makes additional cloud tooling available for backup, recovery and patching.
    • You can choose the Oracle Database Cloud Service - Virtual Image service level to define a database instance manually or using the Oracle Database Creation Assistant (DBCA). 

      For this tutorial, select Oracle Database Cloud Service (not the Oracle Database Cloud Service - Virtual Image service level).
    choose service level
    Description of this image
  2. On the Subscription Type page, you also select the billing frequency. For this tutorial, select Monthly. Then click Next.

    billing frequency
    Description of this image

Selecting the Software Release

The wizard Software Release page appears. Use this page to select the Oracle Database software release that you want to run on your instance.

For this tutorial, select Oracle Database 12c Release 1. Then click Next.

software release page
Description of this image

Selecting the Software Edition

The Software Edition page appears. Use this page to select the Oracle Database software edition that you want to run on your instance.

For this tutorial, select Enterprise Edition. Then click Next.

software edition page
Description of this image

Configuring the Service Instance

The Service Details page appears. On this page, you configure details in three areas:

  • Service configuration
  • Database configuration
  • Backup and recovery configuration
  1. First, you fill the fields in the Service Configuration section.
    Note: Several fields have Help icons to assist you. For example, the Help message for the Service Name field explains that the service name of your choice can have up to 50 characters, must start with a letter, and can contain only letters, numbers, and hyphens (-).

    service details page

    Description of this image

  2. Enter a service name.
    Note: The Service Name is the name by which this database service instance will be listed among potentially multiple database instances in your DBaaS service. It is not the database SID or service name, which you will provide in the Database Configuration section of this page.

    Optionally enter a description of the service instance.
    Note: You can use the description to show reminders, such as the public key you used and the storage size. If you create multiple database instances in your DBaaS service, the description of each instance will help distinguish them in the list of instances.

    Select a compute shape from the drop box.
    Note: The compute shape specifies the number of Oracle Compute Units (OCPUs) and amount of memory (RAM) for the virtual machine (VM) hosting the new service instance. Database Cloud Service (DBaaS) offers several OCPU/RAM combinations.

    Enter the timezone for the service instance to use.
    Note:  The default is Coordinated Universal Time (UTC).

    instance name description compute shape

    Description of this image
  3. Click Edit to enter the key file name of an existing SSH public key that was uploaded earlier to your DBaaS virtual machine, or to upload a new public key.
    Note: For this tutorial, you will upload the new public key that you generated in the first section of the tutorial.

    Select the key file name of the SSH public key that you generated earlier, by clicking Choose File. After choosing the file, click Enter. The file you select will be uploaded to your DBaaS virtual machine during this service creation process.

    key file name
    Description of this image


    Note: If you were not following this tutorial and an SSH public key had already been uploaded earlier to your DBaaS VM, you would select the key value of the existing SSH public key, using the full hierarchical name of the public key in the format /Compute-<identity-domain>/<username>/<key-name>.

    key name
    Description of this image

Configuring the Database

Next, you fill the fields in the Database Configuration section.

Enter the following:  the amount of storage in GB for actual database data, the administration password, the database name (SID), and the PDB pluggable database name (if you have selected Oracle 12c).
Note: The administration password you provide is used for the SYS and SYSTEM database users, and for the admin Oracle Application Express user. Make note of this password, as you will need it later when defining a SQL Developer connection.

You may change the default character set and national character set for the database.

The Total Estimated Monthly Storage field displays a computed estimate, including data files and backup storage.

Standby Database with Data Guard controls whether Data Guard is to be configured.

You may enable Oracle GoldenGate, to configure the database for use as the replication database of an Oracle GoldenGate Cloud Service instance.

You may include the "Demos" PDB in the database, which contains demos for many new features of 12c such as in-memory and multitenant.

database configuration
Description of this image

Configuring Backup and Recovery

Next, you fill the fields in the Backup and Recovery Configuration section.

Typically, for backup and recovery, you would enter your choice of destinations: cloud storage container name, cloud storage user name and user password for an Oracle Storage Cloud container (if backing up to cloud storage, or to both cloud storage and local storage on the VM).
When specifying the name of the container, use the format: instance-id_domain/container, where instance is the name of the Oracle Storage Cloud Service instance, id_domain is the name of the identity domain, and container is the name of the container.
Example for Cloud Storage Container: storagesvc999-usoracleaccoutrial00999/backup_container

However, for this tutorial, select None. Then click Next.

backup and recovery configuration
Description of this image

Confirming Your Database Instance Definition

On the Confirmation page, review your database definition choices.

When you are satisfied with the information, click Create.

confirmation page
Description of this image


When you return to the Database Cloud Service console, you will see your new database instance listed. It will have a status of "In Progress" until the service creation is completed. Service instance creation will take a number of minutes; possibly 30 or more.

in progress
Description of this image

Finding the Connection Details for your Database Instance

Before you can use Oracle SQL Developer to connect to your database, you need to find the IP address and SID of your database for connecting to the database with SQL Developer.

  1. Log in to the Oracle Database Cloud Service (DBaaS) console using the credentials provided by your Oracle Cloud account administrator.

    • Your administrator may provide you a link to the My Services console, from which you can choose Oracle Database Cloud Service (DBaaS).
    • Or, your administrator may provide you a direct link to the Oracle Database Cloud Service (DBaaS) console.  

    database console
    Description of this image
  2. Click the name of the database to which you want to connect.

    click database name
    Description of this image
  3. Write down the Public IP address and SID name. You will need these later when you define your SQL Developer connection.

    connection information
    Description of this image

Enabling Secure Network Access to your Database Instance

When a database service instance is created, a number of Oracle Compute Cloud Service access rules are created, but set to a disabled status. When you enable one of these predefined access rules, the given port on the compute node is opened to the public internet.

In order to use SQL Developer and the Oracle Cloud on-instance database monitor (DBaaS Monitor) in this tutorial, you will enable two predefined rules:

  • ora_p2_dblistener, which controls access to port 1521, the port used by SQL*Net, and therefore SQL Developer.
  • ora_p2_httpssl, which controls access to port 443, the port used for HTTPS connections to the instance, including the DBaaS Monitor.
  1. To enable these two automatically created Oracle Compute Cloud access rules from the Database Cloud Service console, click the menu hamburger menu beside the service name.

    click compute cloud service
    Description of this image
  2. Note: As an alternative to the steps shown here, if you are not signed in to the Database Cloud Service console, sign in to the My Services application by clicking the link in your welcome email or by going to http://cloud.oracle.com, clicking Sign In and selecting your My Services Data Center from the drop-down menu. The Platform Services Dashboard is displayed. In the entry for Oracle Compute Cloud Service, click Open Service Console.Then in the Compute console, click the Network tab to access the security rules that are the same as these access rules.

  3. Select Access Rules from the drop-down menu.

    click network
    Description of this image
  4. The Access Rules page is displayed. A list of access rules and their enabled status is displayed. You can create, delete, and update access rules from this page. By default, only the ora_p2_ssh and ora_trusted_hosts_dblistener access rules are enabled.

    hover mouse over rule name
    Description of this image
  5. To enable SQL Developer access, click the menu hamburger menu beside  the ora_p2_dblistener access rule, and select Enable.

    select update
    Description of this image
  6. The Enable Access Rule window is displayed. Click Enable.

    change rule status to enabled
    Description of this image
  7. The Enable Access Rule window closes, and the Status column indicates that you successfully updated the access rule. To enable DBaaS Monitor access, click the menu hamburger menu beside the ora_p2_httpssl access rule, and select Enable.
    In the Enable Access Rule window, click Enable.

    change status to enabled for ora_p2_httpssl
    Description of this image
  8. You may exit the Access Rules page.

    .

Connecting SQL Developer to your Cloud Database

Now let's define an Oracle SQL Developer connection to your database in the cloud.

  1. Open SQL Developer. Right-click Connections and select New Connection.
    Note: If you are using a version of SQL Developer in which the Connections panel shows both "Connections" and "Cloud Connections", do not right-click Cloud Connections, which is for Oracle Database Cloud - Schema Service.

    right click connections
    Description of this image
  2. The New / Select Database Connection dialog appears. Provide the following information:
    Connection Name: Create a name for this connection.
    Username: For simplicity, since we have not yet defined users, let's just connect with the system user.
    Password: Provide the "Administration" password that you specified when you created the database service instance.
    Hostname: Provide the Public IP address for the service instance you are connecting to. You made note of the Public IP address and SID in the tutorial's "Finding the Connection Details for your Database Instance" section.
    Port: 1521
    SID: Your SID
    or
    Service Name: If you are connecting to an Oracle 12c pluggable database (PDB), provide the service name instead of the SID, as shown in the second screen of this step.
    Click Test. You should receive a success status.

    define sql dev connection
    Description of this image

    To connect to an Oracle 12c pluggable database, provide the service name instead of the SID, as shown in the following screen.

    connect SQL Dev to PDB
    Description of this image
  3. If your test results show success, click Connect.
    Note: If you had not enabled the ora_p2_dblistener access rule in the previous section, you could have defined an SSH connection using a copy of the private key that you created when generating the private/public key pair in an earlier section of this tutorial. To learn about SSH tunneling, use the link to the documentation in the "Want to Learn More?" section at the end of the tutorial.

Performing Operations on Data in your Cloud Database

You have connected SQL Developer to your database service instance in Oracle Database Cloud - Database as a Service. Now you can use SQL Developer as you normally would with an on-premises database. Perform some simple operations on the sample schema that comes with your database service instance in the cloud.

Adding a Row to a Table

  1. Your connection is displayed in the Connections tab on the left side and a SQL worksheet is opened automatically. Expand the connection you just defined.

    expand your connection
    Description of this image
  2. Expand Other Users > HR > Tables. Select COUNTRIES. The panel on the right will display table information, with the Columns tab open.

    select countries
    Description of this image
  3. Click the Data tab to see data from the COUNTRIES table in your cloud database.

    select the data tab
    Description of this image
  4. Click the Insert Row toolbar button to Insert a row of data. Specify a country ID of NZ, country name of New Zealand, and region ID of 3. Click the Commit Changes button. You have added a small bit of data to your cloud database from your local computer. See the links below for tutorials on migrating large data sets.

    insert a row
    Description of this image

Querying the HR Schema

  1. Click the SQL Worksheet icon under the menu bar.

    open worksheet
    Description of this image
  2. The Select Connection dialog appears. If you are still connected to the database from the previous topic, click OK.

    select connection
    Description of this image
  3. The SQL Worksheet appears. You can use the SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements as you would with your non-cloud, on-premises databases. You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file.

    You can display all columns of data in a table by entering an asterisk (*) after the SELECT keyword. Execute the following statement to view all rows and columns in the DEPARTMENTS table of the HR user. The results appear in the Query Result tab:

    SELECT * FROM HR.DEPARTMENTS;
    select from departments
    Description of this image
  4. You can display specific columns of data in a table by specifying the column names in the SELECT statement. Execute the following statement to view the JOB_ID and JOB_TITLE columns in the JOBS table:

    SELECT job_id, job_title FROM HR.JOBS;
    select from jobs
    Description of this image
  5. You can use the WHERE clause to restrict data. Execute the following query to restrict the number of rows to DEPARTMENT_ID 60:

    SELECT * FROM HR.DEPARTMENTS WHERE department_id=60;
    select where department id is sixty
    Description of this image
  6. Execute the following SELECT statement to retrieve the LAST_NAME, JOB_ID, and HIRE_DATE columns of employees who belong to  the SA_REP job ID. Sort the rows in ascending order based on the HIRE_DATE column:

    SELECT last_name, job_id, hire_date
    FROM HR.EMPLOYEES
    WHERE job_id='SA_REP'
    ORDER BY hire_date;

    select by job id and hire date
    Description of this image

Monitoring Your Database Service Instance with the DBaaS Monitor

Use the DBaaS Monitor to monitor your Oracle Database Cloud - Database as a Service, and the database instances in it. You connect to the Monitor via Port 443 of your DBaaS VM. You opened this port in a previous section of the tutorial, ''Enabling Secure Network Access to your Database Instance".

  1. From the My Services console, return to the Database Cloud Service console.
    Click the menu next to the database instance you wish to monitor, and note the variety of console choices.
    Click Open DBaaS Monitor Console.

    open dbaas monitor console
    Description of this image
  2. If your browser warns that your connection is not private, click Advanced, and then click the link to proceed to the Public IP address shown. The log in dialog appears. Enter dbaas_monitor as the user name. For the password, use whatever password was specified when your database instance was created. Click Log In.

    monitor login dialog
    Description of this image
  3. The DBaaS Monitor home appears, displaying generic information about the database instance.  The Database area contains sections showing snapshot information about database status, listener status, online database storage, alerts, sessions, and wait events.  The Operating System area contains sections showing snapshot information about memory, CPU, file system storage, and processes.

    You can click each section to drill down to a more detailed view of the monitored area.

    Alternatively, you can access the same detailed views using the Database and OS drop-down menus.

    monitor home page
    Description of this image
  4. In the Database area, click Online Database Storage.

    database monitor page
    Description of this image
  5. The Storage page displays a breakdown of the storage currently allocated to the database. It shows the overall database storage, followed by the storage for each PDB. For each PDB, you can show or hide storage information about its tablespaces.

    In CDB$ROOT and PDB1, click show tablespaces.

    In PDB1, click the SYSTEM tablespace.

    storage page
    Description of this image
  6. The tablespace detailed page provides an interactive report on the segments that exist within the tablespace. Most segments are user objects, and they include tables, large objects, and indexes.

    click update access rule
    Description of this image
  7. From the Database drop-down menu, click Alerts. An interactive report displays the entries in the database's alert log. The alert log is a chronological log of messages and errors commonly used to learn whether the background processes have encountered errors.

    click alert log
    Description of this image


    database alert log
    Description of this image
  8. From the Database drop-down menu, click Sessions to view open user sessions. An interactive report displays information about all currently opened sessions in the database. The top-most table shows summarized data about each open session. If you click certain rows in this table, you can scroll down to the bottom of the window to see more detailed information about that session.

    click sessions
    Description of this image


    sessions page
    Description of this image


    session detailed row info
    Description of this image


  9. From the Database drop-down menu, click Waits to view the wait events in real time. Your database may not show wait events. In this example, the wait events are essentially of CPU consumption, plus a little bit of system I/O, and various waits categorized as "Other".

    click wait events
    Description of this image


    wait events
    Description of this image


  10. From the Database drop-down menu, click Manage. The Manage page allows you to see the status of the database and allows you to turn the database on and off. In 12c databases, the status of individual PDBs is also displayed. On this page you can also create, clone, plug, unplug or modify PDBs

    Click Create PDB and examine the Create PDB dialog. Then cancel out of the dialog.

    click configure select rdbms
    Description of this image


    database configuration page
    Description of this image

  11. From the Database drop-down menu, click Parameters to view or modify current parameter values. 

    click parameter settings
    Description of this image

    The Database Parameters page appears. To modify the value of a parameter, select the parameter, change its value, and click the Save changes button at the top. You will have an opportunity to review your change before applying it. Changes that are saved to memory will take effect immediately across all sessions, but will be lost upon the next database instance shutdown. Changes that are saved to the PFILE will take effect upon the next database instance startup and are persistent.

change a parameter
Description of this image

  1. From the Database drop-down menu, click Real Time SQL Monitor to view the SQL statements that are being monitored in the database

    click parameter settings
    Description of this image

    The Real Time SQL Monitor page appears for viewing, in real time, the SQL statements that are being monitored in the database. This tool provides two major functions - a general view of monitored statements, and a view of execution details.

    The general view is provided in a table containing: status, duration, SQL ID, session ID, session serial number, instance degree of parallelism, CPU time, I/O time, start time, and the SQL statement being monitored.

    When a SQL statement is drilled down from the main monitor table, a detailed view is shown. The detailed view shows general information about the query execution, general statistics of the SQL statement, and detailed information about the statement (SQL text, plan statistics, parallelism details, activity line chart for the CPU usage).

change a parameter

Want to Learn More?

Credits

  • Lead Curriculum Developer: Rick Green