Autonomous Database

Monitor your Oracle Cloud Free Tier with Grafana on Oracle Linux 8

In a previous blog post I wrote about monitoring Oracle Cloud Infrastructure components by Grafana. In the meantime, we got the Oracle Cloud Free Tier. Here is an updated version.

This blog post shows you how to install and configure the Grafana plugin based on the Oracle blog entry https://blogs.oracle.com/cloudnative/data-source-grafana on an Oracle Enterprise Linux 8 server.

Steps to monitor the Oracle Cloud Free Tier by the OCI Grafana Plugin

  1. Install and configure the Oracle Cloud Infrastructure CLI – by download or by YUM install
  2. Configure Group, User and Policy in Oracle Cloud Infrastructure Console
  3. Install Grafana and the OCI Plugin
  4. Configure the Grafana DataSource
  5. Create a new Dashboard with OCI Metrics

Machine Requirements

The server needs access to the internet.

Install and configure the Oracle Cloud Infrastructure CLI

Link: https://docs.cloud.oracle.com/iaas/Content/API/SDKDocs/cliinstall.htm

In this step, the software will be installed an configured. The new created SSH public key has to be added in the OCI console for further actions.

As OS user root we create a new user for OCI actions. 

Login as user oci, execute the CLI download and installation script. Answer questions with Y / Enter to get the default installation.

Default values:

install directory /home/oci/lib/oracle-cli
executable directory /home/oci/bin
OCI scripts /home/oci/bin/oci-cli-scripts
optional CLI packages db
shell/tab completion Y
path to rc file /home/oci/.bashrc

 

After the successful CLI installation, you have to configure it.

Based on your OCI account, these information are required – let the config and key location on default values.

config location /home/oci/.oci/config
user OCID OCI > Identity > Users > [YOUR_USER] > OCID
tenancy OCID OCI > Administration > Tenancy Details > [YOUR_TENANCY] > OCID
region choose your region, e.g. eu-zurich-1
generate a new key pair Y -> only if you don’t have already created a key pair
key directory /home/oci/.oci
key name oci_api_key

 

Add the content of the public key file in the OCI console to your user which you want to work with.

Attention: Be sure that you add the public key to the user which you have used for the CLI configuration!

Test the CLI configuration – example to list all compartments in your tenant.

Alternative Method Oracle Linux 7 – YUM Repository

Thanks to Sergio Leunissen from Oracle for his input, the Python SDK and oci utilities are is available in the YUM repository too and ready to install. Take a look at his blog post to see how to work with the Python SDK and OCI metadata:

Configure Group, modify User and add a Policy in Oracle Cloud Infrastructure Web Interface

Group

Create a new OCI group called Grafana. OCI > Identity > Groups.

Modify User

Add the selected user to the group – for example this is my user.

Add a Policy

Create a new policy called GrafanaPolicy. OCI > Identity > Policies.

allow group grafana to read metrics in tenancy
allow group grafana to read compartments in tenancy

Install Grafana and the OCI Plugin

Link: https://grafana.com/grafana/download?platform=linux

Login as user root and install Grafana.

Enable auto start and start the Grafana server manually.

Enable port 3000 (Grafana default port in firewall – the port can be changed in /etc/grafana/grafana.ini) to provide web access to Grafana.

Install the Grafana Oracle Cloud Infrastructure oci-datasource plugin.

Verify the Grafana plugin directory with the installed plugin.

Grafana needs the configuration file and the SSH Key from the user oci. As user root, copy the files and set the ownership to OS user grafana.

Change the path to the key file in /usr/share/grafana/.oci/config.

# vi /usr/share/grafana/.oci/config

From:

To:

Create a new Dashboard based on OCI Metrics

Open your browser and log in into Grafana with [SERVERNAME]:3000. Username and password are admin/admin. You have to change your initial password imme diately.

Add data source

Select Oracle Cloud Infrastructure

Configure the Data Source

Fill in your tenancy OCI, region and set Environment = Local. Test the connection. For troubleshooting see Grafana logfile in directory /var/log/grafana. If your default region like ZRH / EU-ZURICH-1 is not listed, then you have to edit the a plugin file as described below. Otherweise no metrics are shown.

Example to use Grafana for the Datacenter eu-zurich-1:

Edit the file /var/lib/grafana/plugins/oci-datasource/dist/constants.js and add your missed region – restart Grafana.

Error message in the grafana.log when your region is not added in file content.js but you select the region as data source:

Create a new Dashboard and Add Query

Create a Query to visualize Data

In this dashboard example I used the region eu-zurich-1, my compartment, the namespace oci_autonomous_database and the metric CpuUtilization.

There are a lot of other metrics available like:

  • CurrentLogons
  • ExecutionCount
  • Sessions
  • StorageUtilization (in %)
  • etc.

Available Metrics

 Learn more about metrics and monitoring in the OCI documentation here:

Summary

The OCI Grafana plugin is a nice solution to visualize your Oracle Cloud Free Tier environment based on Open Source software. Take care, Grafana needs access to the OCI CLI SSH information for the Oracle Cloud Infrastructure connection.

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 3

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump. 

Oracle Import Prerequisites

Credentials

To get acccess to the dump file on the Oracle Object Storage, a credential has to be created in the Oracle Autonomous Transaction Processing database with the DBMS_CLOUD procedure. For more information about the package, see the blog post from Christian Antognini – DBMS_CLOUD Package – A Reference Guide.

The DBMS_CLOUD procedure needs a password value which is the token from the user account. If you don’t now your token, create a new one. Go to Identity – Users – your username and click on the left side on “Auth Tokens”. Create a new token by click on “Generate Token”. The random generated string is the value for the DBMS_CLOUD password.

Enter a name for the token and click on “Generate Token”.

Note your generated token and “Close” the window.

Login into the Autonomous Transaction Processing database as admin user and create a new credential called ATPCS_CRED.

A new Database User called HRATP

In the ATP, we create a new user called HRATP. The tablespace DATA is the default tablespace in an Autonomous Transaction Processing database and does not have to be defined.

Oracle Data Pump Import

The impdp tool is part of my Instant Client installation in Part 1 of this blog serie. Oracle recommends to set parameters like 

  • partition_options=merge
  • transform=segment_attributes:n
  • transform=dwcs_cvt_iots:y
  • transform=constraint_use_default_index:y
  • exclude=index, cluster, indextype, materialized_view, materialized_view_log, materialized_zonemap, db_link

Two new Oracle Data Pump parameters to work with the Oracle cloud databases are credential and and dumpfile.

  • credential: The DBM_CLOUD created credential
  • dumpfile: The URL where the dumpfile is located

Attention

The URL provided by the Object Storage menu cannot be accessed directly by impdp:

The URL has to be mapped from objectstorage to swiftobjectstorage:

to

Data Pump Import Execution with REMAP of Schema and Tablespace

Start of the Oracle Data Pump job from my Windows client:

The message about the existing user can be ignored. 

Data Pump Logfile

The logfile of the import process cannot be access directly, it has to be moved into the Object Storage with the DBMS_CLOUD package first.

Now the file can be access in the Object Storage menu:

Connect as new User HRATP to verify the Data

Summary of Part 3

If all prerequistes are met, the data transfer with Oracle Data Pump is easy to configure and easy to handle. Take care about the token, only 2 token per user can be generated. If you losed it, you have to delete a existing token, rebuild it and re-create the credentials before you can import data again.

Oracle Autonomous Transaction Processing – Move your Data with Oracle Data Pump – Part 2

In this blog post serie which has three parts, I want to describe how data will be uploaded from an on-premises environment into the Oracle Autonomous Transaction Processing database using Oracle Data Pump. 

 

Oracle Data Pump Export on On-Premises Database

Oracle recommends the following settings for the Oracle Data Pump export job. If you use already an 18c database, you have to set the version parameter to 12.2 to avoid this error during ATP import process: ORA-39358: Export dump file version 18.0.0 not compatible with target version 12.2.0.

My expdp export job for the schema HR:

The example schema contains these objects – output from expdp job

Transfer the Dump File Set into the Oracle Cloud

The export dump file has to be transferred into the Oracle Cloud Object Storage for later usage. First we have to create an Object Storage Bucket. Login into Object Storage menu and click on the “Create Bucket” button. 

Enter a Bucket Name and click on “Create Bucket”.

Enter the new created bucket. Click on the bucket name link or the “three bullets” on the right side to view the details.

Upload Data

Upload the export dump file, click on “Upload Object”.

“Browse” local for the Oracle Data Pump export file and click on “Upload Object”.

Object Details

Click on object “Details” to verify the object on the Object Storage. 

 

Note the URL. The URL will be used later for the import process.

Summary of Part 2

Upload objects into the Oracle Cloud Object Storage is very easy. If you don’t want to use the browser functionality, there are other possibilities to upload files like the API.

Now the export dump file is ready to import into the Autonomous Transaction Processing Database.