Dontcheff

Archive for the ‘OCI’ Category

Viewing cost saving recommendations in OCI

In Cloud, DBA, OCI on February 24, 2022 at 15:45

Do not save what is left after spending, but spend what is left after saving – Warren Buffett

Tracking and managing usage and cost in the Cloud in often neglected. Often it is seen as complex and difficult. Here is what we can do in Oracle Cloud Infrastructure. In the most right console screen of OCI, under Account Center, we have now Billing and Cost Management Savings. It shows how many savings have been already implemented and how many are still pending:

Best practices framework for Oracle Cloud Infrastructure describe the possible actions:

  • Evaluate the Different Pricing Models
  • Implement a Compartment Structure That Fits Your Organization
  • Set Up Compartment Quota Policies to Control Resource Usage
  • Implement Cost Tracking Tags for Flexible Cost Tracking
  • Define Budgets
  • Enable Block Volume Performance Auto Tuning
  • Implement Object Storage, Object Lifecycle Management
  • Leverage Cost Reports
  • Track and Optimize Your Spending by Using Cost Analysis
  • Implement a Process to Terminate or STOP Unused Resources
  • Evaluate What Compute Shape Fits Your Workload
  • Become Familiar with Cloud Advisor

If you go under recommendations (under Cloud Advisor), the screen will list all types and you can also filter on a category:

As I have several databases on OCI, both ADB and Database Systems, both Oracle and MySQL, spread among couple of regions, it is rather important for me to manage the cost as I use the databases on daily basis and often do no stop them during the week.

As you can see above, my pending recommendation is about defining lifecycle policy rule which that automatically moves Object Storage data to lower cost tiers when possible. Meaning in practice archive storage.

It is worth going through all recommendation and although some of them are rather obvious, they might not always come to mind to implement.

Viewing Autonomous database usage is straightforward: as you pay only for storage when the database is stopped (charges below are for February):

but for MySQL especially if using HeatWave, the charges are slightly different:

Finally, here are some useful links:

Cloud Advisor Overview

Now available: Oracle Cloud Advisor

10 effective ways to save cost in the cloud: Part 1

10 effective ways to save cost in the cloud: Part 2

Moving Autonomous Databases across regions

In Autonomous, DBA, OCI, Oracle database, Replication on January 3, 2022 at 13:26

With the new OCI regions, we might want to move our databases to a closer location – in my case moving my databases from Frankfurt to Stockholm.

First what comes to mind is to enable Autonomous Data Guard and then switch over. However, you need a paired region – we cannot create a standby database just anywhere:

The list of Autonomous Data Guard Paired Regions shows that for each Source Region, we have 1-4 Paired Regions.

You need to be subscribed to a region before it can appear on the list and use it. And note: once you subscribe, there is no unsubscribe! You will need to apply for a limit increase.

I am told that soon, once a good standing payment history has been established, you will be eligible for unlimited regions. 

Another option is simply to create a clone:

Note that if you are using a clone from a backup, you cannot have the clone in another region, no idea why not! As you can see from the screenshot below, I am not getting a drop down list of regions:

So, let us create the database clone in Stockholm from the current AEG database running in Frankfurt:

Once the database is cloned, you can optionally terminate the copy in Frankfurt. But do not do it before you have verified the new clone as you might get an (unknown) error, something like this:

In case you do not want to deal with support, just create a new database and move the data with Data Pump.

My home region is Germany Central (Frankfurt). So am I able to change now my home region to Sweden Central (Stockholm) instead of Germany Central (Frankfurt)? Alas, this is not possible. Not yet at least.

According to the Metalink document Change Home Region in OCI console (Doc ID 2389905.1), the home region of the tenancy is fixed at creation time and cannot be moved. If you still wish to have the region changed, the only way to get it done would be to re-create the tenancy. That would mean removal of the current tenancy with all its resources and creating a new one.

Having the primary database in one region and a standby database in another is good practice in terms of DR. Even more complex scenarios are available. How about having a sharded Oracle Database spread among OCI, AWS and Azure? Possible but I would avoid such a complexity.

Simple Oracle Document Access (SODA) in the Autonomous JSON Database

In Autonomous, DBA, OCI, Oracle database on November 12, 2021 at 12:09

“I ordered a soda – caffeine-free, low sodium, no artificial flavors. They brought me a glass of water.” – Robert E. Murray

SODA is a set of NoSQL-style APIs that let you create and store collections of documents in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the data in the documents is stored in the database.

Oracle database stores, manages, and indexes JSON documents, and developers can access these via document-oriented APIs in a NoSQL style.

A recent white paper written by Vlad Kamys, Francesc Mas and Sai Penumuru explained how to modernize your applications and increase business resilience and security with JSON on Oracle Database, and new cloud-based Oracle Autonomous JSON Database.

Here are few examples on how to use JSON and SODA in the Oracle Autonomous Database.

From the Tools tab, start “Database Actions”, and then select “SQL”:

The following below are typical SODA commands:

(1) list the collections

(2) create the EMP collection

(5), (6) and (7) insert three JSON documents into the collection

(10) gets all documents where the name is “Francesc”

(11) gets all documents where the salary is greater than 300

(12) gets all documents where the jobs starts with “D”

(13) gets all documents where the jobs contains the string “play”

Here is the output from command extracting all documents where the jobs starts with “D”:

If we do not have a text index on “job”, then we get an ORA-40467:

We can also run standard SQL on the EMP table which gets created as a result of creating the collection:

Here is how to get the output from EMP by using JSON_VALUE:

You can think of SODA as a programming bridge between the NoSQL model and the relational model.

There is also the DBMS_SODA package in the database. You can drop the EMP collection simply with “soda drop emp” but you can also run select DBMS_SODA.DROP_COLLECTION(’emp’) from dual; The function will return 1 when it succeeds and 0 when it fails.

Work with JSON Documents in Autonomous Database provides examples of how Java code opens a SODA collection of cart documents, how to use SQL with a SODA collection, etc.

Finally, there is JSON DB/SODA DB Health-Check Script that is a tool developed by Oracle Support Services. The tool, also known as jsonsodadb_hc, is used to check the environment in which a single SQL statement runs, checking for the current status of JSON DB and SODA DB components, makes recommendations based on current settings and checks if the components are being used.

Session settings and timeouts in OCI

In Cloud, DBA, OCI on October 26, 2021 at 14:41

The following question in the Oracle Groundbreakers Developer Community forum made me investigate on how to change the session timeout in the Oracle Cloud console. Have a look, it is still for some reason unanswered:

For those who use Oracle Cloud Infrastructure on daily basis, they know that the default session timeout is 480 minutes although in my case it is an hour – so often after you switch to the OCI tab, you see the following screen:

Here is the way how you can change it to a longer period with 32767 minutes being the maximum allowed.

Step 1: Open the Service User Console

Step 2: Open the Identity Cloud Service Admin Console:

Step 3: From the Dashboard, choose “Settings” and from there “Session Settings”:

Step 4: Set the “Session Duration” to a longer period of time:

Step 5: Saving the settings to the new value: I chose the maximum which is 32767 minutes:

Here are some additional resources:

Administering Oracle Identity Cloud Service

How to Change the Default Session Timeout Value for IDCS (Oracle Identity Cloud Service) in a P6 Cloud Environment (Doc ID 2812372.1)

Note that there are other setting besides session session: user settings, default settings and partner settings.

You can try also the Console Settings (thanks to Simo Vilmunen) but there I am getting a show stopper:

SQL Trace and X-ADG in the Oracle Autonomous Database

In Autonomous, DBA, OCI, Oracle database, Replication on October 6, 2021 at 09:12

Two very different in nature but equality useful features are now available in the Oracle Autonomous Database:

  1. SQL Tracing in Autonomous Database
  2. Cross-Region Autonomous Data Guard in ADB-S

Here is how to enable and use them:

SQL Trace in ADB:

You need first a standard bucket as SQL tracing files are only supported with buckets created in the standard storage tier. Also, create a token (you can have at most 2 tokens) and do not use your OCI password when creating the credentials.

Next, you have to create a credential for your Cloud Object Storage account. Note the full username below – do not simply use the one with what you login to the console.

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'JULIANDON_CREDENTIAL',
    username => 'oracleidentitycloudservice/juliandon@yahoo.com', 
    password => 'generated_token'
);
END;
/

PL/SQL procedure successfully completed.

Afterwards, set the init.ora parameters DEFAULT_LOGGING_BUCKET to specify the Cloud Object Storage URL for a bucket for SQL trace files:

SET DEFINE OFF;
ALTER DATABASE PROPERTY SET 
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/juliandon/b/adbkofa/o/';

Database altered.

Next, specify the credentials to access the Cloud Object Storage. Note that although I am doing this as the ADMIN user, I still have to prefix the credential with ADMIN. Otherwise, you get an error message.

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.JULIANDON_CREDENTIAL';

Database altered.

Before we can enable SQL trace, we configure the database to save SQL Trace files:

exec DBMS_SESSION.SET_IDENTIFIER('sqltrace_jd');

PL/SQL procedure successfully completed.

exec DBMS_APPLICATION_INFO.SET_MODULE('module_jmd', null);

PL/SQL procedure successfully completed.

ALTER SESSION SET SQL_TRACE = TRUE;

After running the SQLs, disable SQL tracing so that the collected data for the session is written to a table in your session and to a trace file in the bucket you configured when you set up SQL trace.

ALTER SESSION SET SQL_TRACE = FALSE;
ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';

The SQL Trace facility writes the trace data collected in the session to Cloud Object Store in the following format:

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

When you enable SQL Tracing, the same trace information that is saved to the trace file on Cloud Object Store is available in the SESSION_CLOUD_TRACE view in the session where the tracing was enabled.

SELECT trace FROM SESSION_CLOUD_TRACE ORDER BY row_number;

After you close the session, the data is no longer available in SESSION_CLOUD_TRACE.

DESC SESSION_CLOUD_TRACE

Name       Null? Type
---------- ----- ------------------------------
ROW_NUMBER       NUMBER
TRACE            VARCHAR2(32767)

Check Connor McDonald’s blog entitled SQL trace on your cloud database.

Cross-Region Autonomous Data Guard in ADB-S

Autonomous Data Guard provides a standby database instance in a different availability domain in the same region or in a standby database instance in different region.

If you create the standby database in the current/local region and if the primary instance becomes unavailable – the Autonomous Database automatically switches the role of the standby database to primary and begins recreating a new standby database.

ADB currently supports up to 2 standby databases – a local one in the same-region and an additional one which is remote – called cross-region.

So, with the new cross-region standby database, you can perform a manual failover to the standby database if the current region goes down.

A detailed blog by Nilay Panchal entitled Cross-Region Autonomous Data Guard – Your complete Autonomous Database disaster recovery solution! covers in detail how to create the remote standby database and how to manually switch over.

Note that each region has one or a few nearby paired regions in which a remote standby may be created. As you can see from the screenshot above my tenancy in Frankfurt is subscribed to 3 remote regions in which I can create a remote standby.

It is important to know that ADB-S does not allow us access to the standby databases but after a switchover or failover, the database wallet downloaded in the primary database region can be used in the remote region.

It is extremely simple to manually switchover to the other region – in my case from Frankfurt to Zurich, just with a click of a button:

Simple and elegant!

The Oracle Cloud Infrastructure Mobile App

In Autonomous, DBA, OCI on September 1, 2021 at 06:46

Being on summer vacation without my laptop, I still have access to my OCI tenancy via the mobile app by simply using my phone.

The OCI mobile app is available for both Apple iOS and Android. With the app, we can check the resources and view alarms, billing, days elapsed and limits.

First, you have to download the app – search for “Oracle Cloud Infrastructure”:

After you login in to the mobile app, you get the following screen from where you can either modify the settings of your profile or/and view your resources, billing status, alarms and limits:

Indeed, we cannot do much besides viewing some of your resources and billing charges (you cannot drill down). And for now, I can see only my ADW and ATP databases, not AJD or APEX.

But, at least I can check if my databases were stopped before I left for vacation:

For faster sign-in to the mobile app, you can enable automatic sign-in. Automatic sign-in uses an API key to authenticate you when you access the app, keeping you signed in until you sign out. Each user has a limit of 3 API keys. If your account has reached this limit, you can’t use this feature in the mobile app until you delete one of the existing API keys. You can use the Console to delete API signing keys. My virtual private vault count is zero – so I could not enable automatic sign-in:

It is also possible to switch the regions (my default is Frankfurt as you can see from above) and you can set the mobile app to use UTC time or local time.

HeatWave MySQL DB Systems in OCI

In DBA, MySQL, OCI on June 7, 2021 at 10:44

HeatWave is a distributed, scalable, shared-nothing, inmemory, columnar, query processing engine designed for fast execution of analytic queries. It is enabled when you add a HeatWave cluster to a MySQL DB System.

You can think of HeatWave as an easy way to run high performance analytics against the MySQL database.

When creating a MySQL DB System in Oracle Cloud Infrastructure, the options are now Standalone, High Availability and (the newest) HeatWave:

Here is a how the HeatWave architecture looks like:

A HeatWave cluster supports up to 64 nodes. The number you choose depends on the size of the database and the amount of compression that is achieved when loading the data into the HeatWave cluster. You cannot connect the app/session drectly to the HeatWave cluster.

Starting from scratch, choose a MySQL DB system from the OCI Databases menu:

And then start creating a MySQL DB system with the HeatWave option:

Once ready, adding additional clusters (when needed) is simple:

As of now, the only available shape is VM.Standard.E3, so you might get error messages when trying to add more than 4 clusters:

Here is what I got: “You have reached your Analytics Cluster service limit of 4 in this Availability Domain for MySQL.HeatWave.VM.Standard.E3. Please try launching in a different Availability Domain or Region, or try using a different shape. If you have reached all Service limits, please contact Oracle support to request a limit increase.”

Note that 1 node matches to ½ a TB of memory. The increase is linear.

You can rely also on Oracle to estimate the number of nodes:

Before loading data into the HeatWave cluster, the data/tables have to be prepared. Preparing tables involves modifying table definitions to exclude certain columns, define string column encodings, add data placement keys, and specify HeatWave (RAPID) as the secondary engine for the table – note that InnoDB is the primary engine. Loading a table into a HeatWave cluster requires executing an ALTER TABLE operation with the SECONDARY_LOAD keyword.

If a query accesses a table that is not loaded, the query is not offloaded to the HeatWave cluster for processing. Note that queries that meet certain prerequisites are automatically offloaded from the MySQL DB System to the HeatWave cluster for accelerated processing. Results are returned to the MySQL DB System node and to the MySQL client or application that issued the query.

When a table is loaded, data is sliced horizontally and distributed among HeatWave nodes. After a table is loaded, changes to a table’s data on the MySQL DB System node are automatically propagated to the HeatWave nodes. No user action is required to keep data synchronized.

All the details can be found in the HeatWave documentation (it is short – 68 pages only). This is the best place to understand how to prepare & load the data and how to unload tables. The document is full of examples and lists and string functions and operators. Also, you can find a list of all functions, data types, variables, JOIN types, SQL modes, and other expressions and functionality that are not supported by HeatWave.

Unloading table is pretty straightforward:

mysql> ALTER TABLE sales SECONDARY_UNLOAD;

Here are some additional link if interested in more detail:

Applying one-off patches in the Cloud on Oracle Database 21c

In Cloud, DBA, OCI, Oracle database on April 27, 2021 at 08:37

Oracle have just released new fixes for the 21c version of database release: a security fix and a JDK bundle patch.

The recommendation is to apply these two patches mentioned below to your databases:

• 32640471 21C SECURITY FIXES FOR CPUAPR2021
• 32685286 JDK BUNDLE PATCH 21.0.0.0.210420

Most likely, you will first get an email from Oracle to let you know that the patches are already available:

How to apply the patch? The one-off patches (now they are call interim patches) can be applied via the Console, API or even manually. To apply an interim patch manually, you can use the Opatch utility. The detailed steps are provided in the Applying one-off patches on Oracle Database 21c documentation. The patches can be applied in any order.

Here is how simple and easy it is:

1. For the database on which you want to apply the patches, just click its name to display details and under Resources, click Updates:

2. Click on “Apply a one-off patch“:

3. Then, in the Apply one-off patch dialog, enter the patch numbers. Use a comma-separated list to enter more than one patch. I did apply them one after each other. Paste the patch number and then click Apply.

While the patch is being applied, the database’s status displays as Updating:

A work request is created for the patching operation allowing us to monitor the progress of the operation.

If the operation completes successfully, the database’s status changes to Available:

It is that simple!

Oracle Cloud Infrastructure (OCI) Database Management

In DBA, OCI, Oracle database on March 18, 2021 at 15:51

Early this year, Oracle announced the Announcing the general availability of Oracle Cloud Infrastructure Database Management.

With Database Management Cloud Service, DBAs get a unified console for on-premises and cloud databases with lifecycle database management capabilities for monitoring, performance management, tuning and administration.

As of today, Database Management is available to use with external Oracle databases deployed on-premises. Support for Oracle Databases on Oracle Cloud Infrastructure will be coming soon.

The Database Management pricing is quite simple and clear, here it is:

DBM_price

This is a deep dive into the new OCI Database Management Service, and here are the basics:

  1. From the main menu go down and search for Database Management:

DMB2

2. On the right side, go to “Oracle Databases” in order to enable Database Management (opens a new tab):

DBM3

3. Start registering external pluggable databases – as of today we cannot register cloud databases:

DBM4

4. Select the compartment and choose a database display name:

DBM5

5. Select the database and click on register.

DBM6

The Database Management service comes with 4 main features:

  • Fleet monitoring and management
  • Database groups
  • Database summary
  • Custom PL/SQL jobs

Before you enable and use Database Management, you must review and complete some prerequisite tasks:

  • Install Management Agents for Database Management
  • Register the Oracle Database with the External Database service
  • Connect the Oracle Database to the External Database handle
  • Create Oracle Cloud Infrastructure IAM user groups

After you complete these prerequisite tasks, you must create policies to assign permissions to user groups in order to enable and use Database Management. 

In Oracle Cloud Infrastructure, policies provide the permissions required to allow users to work in certain ways with specific types of resources in a tenancy or a particular compartment.

A policy is written to determine who can perform what functions on which resources using the following basic syntax:

Allow <subject> to <verb> <resource> in <location>

In this policy example, you can create to grant the DB-AEG user group the permission to enable Database Management for all External Databases my your tenancy JULIANDON:

Allow group DB-AEG to use external-database-family in JULIANDON

The external-database-family is the family resource-type for the External Database service, which includes the following individual resource-types:

  • external-container-databases
  • external-pluggable-databases
  • external-non-container-databases
  • external-database-connectors

For more details, check the Oracle Cloud Infrastructure Documentation.