Dontcheff

Archive for the ‘Cloud’ 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

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:

Licensing Types of the Oracle Database

In Cloud, Database options, Databases, DBA, New features, Oracle database on May 16, 2021 at 13:18

After being asked on daily basis all kinds of questions on Oracle Database Licensing, as time goes by, you sort of understand it. Sort of, because the Oracle Database Licensing Guide is 602 pages long and gets often updated. The latest one is from April 2021 – now it is mid-May.

Moreover, you have perhaps seen all Oracle certifications but if you search for one on licensing you will find what I did – there isn’t one.

What I am trying to do now, is to summarize Database Licensing in a short blog post – this might be helpful for many to at least understand the concept.

There are 3 types of licenses for the Oracle Database: Packs, Options and Features and 9 Oracle Database Offerings: Standard Edition 2, Enterprise Edition, Oracle Database Appliance, Exadata, Exadata Cloud Service and Cloud@Customer, Database Cloud Service Standard Edition, Database Cloud Service Enterprise Edition, Database Cloud Service Enterprise Edition – High Performance and Database Cloud Service Enterprise Edition – Extreme Performance (you can see their abbreviations in the table below).

  1. Packs: there are 5 different packs for the Oracle Database:

2. Options: there are 15 database options for the Oracle Database:

  • Oracle Active Data Guard
  • Oracle Advanced Compression
  • Oracle Advanced Security
  • Oracle Database In-Memory
  • Oracle Database Vault
  • Oracle Label Security
  • Oracle Machine Learning
  • Oracle Multitenant
  • Oracle On-Line Analytical Processing (OLAP)
  • Oracle Partitioning
  • Oracle RAC One Node
  • Oracle Real Application Clusters (Oracle RAC)
  • Oracle Real Application Testing
  • Oracle Spatial and Graph
  • Oracle TimesTen Application-Tier Database Cache

Here are the ones related to Consolidation, HA, Managability and Performance:

3. Features: there are 131 features that can be licensed with the Oracle Database out of which 105 are for EE and 123 are for Exadata. As you can see, there are 3 features available for Exadata, ExaCS and ExaC@C falling under the functional category of Autonomous:

If you would like to drill down in detail, use the Database Feature and Licensing tool which is available online without the need to register or have an Oracle account.

Moreover, the Oracle Enterprise Manager Licensing Manual is 366 pages, so there is more to read if you are done with the Database Licensing Manual.

You might think that is way too much for me, and perhaps it is, but the situation is very similar with other database vendors. Let us look at AWS and GCP for instance:

AWS have more than 10 database offering:

Amazon Aurora
Amazon RDS
Amazon Redshift
Amazon DynamoDB
Amazon ElastiCache
Amazon DocumentDB (with MongoDB compatibility)
Amazon Keyspaces (for Apache Cassandra)
Amazon Neptune
Amazon Timestream
Amazon Quantum Ledger Database (QLDB)
AWS Database Migration Service (DMS)

GCP have also more than 10 database offerings:

Relational: Bare Metal Solution for Oracle workloads
Cloud SQL: Managed MySQL, PostgreSQL and SQL Server
Cloud Spanner and BigQuery
Key value: Cloud Bigtable
Document: Firestore and Firebase Realtime Database
In-memory: Memorystore
NoSQL: MongoDB Atlas and managed offerings from open source partner network including MongoDB, Datastax, Redis Labs, and Neo4j

And, after all, Azure are not much behind:

Azure SQL Database
Azure SQL Managed Instance
SQL Server on Virtual Machines
Azure Database for PostgreSQL
Azure Database for MySQL
Azure Database for MariaDB
Azure Cosmos DB
Azure Cache for Redis
Azure Database Migration Service
Azure Managed Instance for Apache Cassandra

After all, being expert in database licensing in a skill of its own!

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!

Migrating databases with several database links

In Cloud, Consolidation, Databases, DBA, Oracle database, Replication on April 1, 2021 at 09:08

In a couple of recent database migration cases, one of the main questions raised, was how to figure out all outgoing and incoming database links as they have to be modified after the massive migrations.

DBLINKS5

Outgoing database links is simple: DBA_DB_LINKS describes all database links in the database. And this view has been part of the database (at least) since 7.3.4

The tricky part is how to find all incoming database links. At least before 12.2, where a new view called DBA_DB_LINK_SOURCES, shows the information of the source databases that opened database links to the local database.

So, how about the databases that are version 12.1 and below?

An Oracle community discussion on the MOS DBA forum gives several ideas:

Option 1: Bruno suggests to “start from the listener logfile; with some “awk/sed/vi” work it should be possible to extract the list of “origins” of the connections… -> From this list, identify the database servers -> Search database links on relevant databases on these servers”.

Might work but might be rather tedious work if there are 100s of different servers.

Option 2: Brian suggests “to query V$SESSION to see active sessions from the other database server. Hint…look at the MACHINE column to see if it matches the other database server name. Querying V$SESSION will only work if the link is open when you query it. As such, you may want to add an AFTER LOGON trigger which writes an audit entry to a table if the connection is from that database server.”

If you create a logon trigger to insert all incoming connection via database link note that in 11g, you can do that using value sys_context(‘USERENV’,’DBLINK_INFO’) which will give us all information. But check first Doc ID 2593966.1 as there is Bug 18974508: sys_context(‘userenv’, ‘dblink_info’) returns incomplete information.

But before 10g, there is no DBLINK_INFO, we we must use x$k2gte:

 
select username, osuser, status, sid, serial#, machine,
process, terminal, program from v$session
where saddr in (select k2gtdses from sys.x$k2gte);

The above is documented in Doc ID 332326.1: How to identify a session started by a remote distributed transaction? The fixed table x$k2gte contains 2PC Global Transaction Entry. The column k2gtdses in x$k2gte has the session state object and this can be mapped to the saddr column of v$session.

But as explained by Mark, the problem is that until the trigger finishes the session the remote db link session is not considered to exist and only upon successful session connection does Oracle then go and update related facts about the session.  Oracle does not guarantee read consistency on v$ views and the v$ views are based on x$ tables which are really program storage areas.  These areas get updated at various points in the logic.  It is possible that a logon trigger may not work in this specific case.  An alternate approach would be to run a process every N time that just snapshots what is out there and records new remote queries.  After all you really only need one capture per remote source whether you care about only database links or care about each client server.

One of the top database experts, Mariami Kupatadze, gave us a very elegant way of how to find remote sessions executing over a database link using x$k2gte, x$ktcxb, x$ksuse and v$session_wait in a single SQL statement.

A more detailed version called Identifying database link usage was written by John Hallas in 2015.

Long story short: for databases from 7.3 till 12.1 create a job capturing the distributed transactions based on the script given in Doc ID 104420.1 “Script to show Active Distributed Transactions”. And you can modify the scripts if not only the active remote transactions need to be captured. For 12.2 and after, just use the view  DBA_DB_LINK_SOURCES. 

create_database_link

 

How to use DBSAT for Oracle 20c and the Autonomous Database

In Autonomous, Cloud, DBA, Oracle database, Security and auditing on November 13, 2020 at 10:13

“There should be no on/off button for security, it should always be on, everything should always be encrypted – there should be no option to turn security off” – Larry Ellison

If you would like to check how secure your Oracle database it, just run the free tool called Oracle Database Security Assessment Tool (DBSAT)!

First, download it for free from MOS Doc ID 2138254.1. Check also the DBSAT documentation.

The Oracle Database Security Assessment Tool is a simple command line tool showing how securely your database is configured, who are the users and what are their privileges, what security policies and controls are in place, and where sensitive data resides with the goal of promoting successful approaches to mitigate potential security risks.

DBSAT has three components: Collector, Reporter, and Discoverer.

Here are the steps of how to produce the report in Oracle Database 20c. DBSAT supports also Oracle Database versions Oracle 10.2 through Oracle 19c.

1. Make sure first you have zip, unzip and python on the database server:

 [root@julian ~]# id
 uid=0(root) gid=0(root) groups=0(root)
 [root@julian ~]# cd /home/oracle/
  [root@julian oracle]# yum install -y zip unzip python
 Loaded plugins: ulninfo, versionlock
 mysql-connectors-community                               | 2.6 kB     00:00
 mysql-tools-community                                    | 2.6 kB     00:00
 mysql80-community                                        | 2.6 kB     00:00
 ol7_UEKR5                                                | 2.5 kB     00:00
 ol7_latest                                               | 2.7 kB     00:00
 (1/3): mysql-connectors-community/x86_64/primary_db        |  68 kB   00:00
 (2/3): mysql80-community/x86_64/primary_db                 | 128 kB   00:00
 (3/3): mysql-tools-community/x86_64/primary_db             |  83 kB   00:00
 (1/5): ol7_UEKR5/x86_64/updateinfo                         |  41 kB   00:00
 (2/5): ol7_UEKR5/x86_64/primary_db                         | 7.6 MB   00:00
 (3/5): ol7_latest/x86_64/updateinfo                        | 3.1 MB   00:00
 (4/5): ol7_latest/x86_64/group                             | 660 kB   00:00
 (5/5): ol7_latest/x86_64/primary_db                        |  30 MB   00:00
 Excluding 262 updates due to versionlock (use "yum versionlock status" to show them)
 Package zip-3.0-11.el7.x86_64 already installed and latest version
 Package unzip-6.0-20.el7.x86_64 already installed and latest version
 Package python-2.7.5-86.0.1.el7.x86_64 already installed and latest version
 Nothing to do
 [root@julian oracle]#
   

2. Create a database user for running the Security Assessment Tool. You can also run it as sysdba but I would rather have a separate user:

create user dbsat_user identified by dbsat_user;
grant create session to dbsat_user;
grant select_catalog_role to dbsat_user;
grant select on sys.registry$history to dbsat_user;
grant select on sys.dba_users_with_defpwd to dbsat_user; 
grant select on audsys.aud$unified to dbsat_user; 
grant audit_viewer to dbsat_user; 
grant capture_admin to dbsat_user;
grant dv_secanalyst to dbsat_user;

3. Run the tool:

[oracle@julian dbsat]$ ./dbsat collect dbsat_user/dbsat_user@//localhost:1521/xxxxx.oraclevcn.com dbsat_output

Database Security Assessment Tool version 2.2.1 (May 2020)

This tool is intended to assist you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.

Connecting to the target Oracle database...

SQL*Plus: Release 20.0.0.0.0 - Production on Tue Nov 10 08:35:00 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 20c EE High Perf Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

Setup complete.
SQL queries complete.
.......
OS commands complete.
Disconnected from Oracle Database 20c EE High Perf Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
DBSAT Collector completed successfully.

Calling /u01/app/oracle/product/20.0.0/dbhome_1/bin/zip to encrypt dbsat_output.json...

Enter password:
Verify password:
  adding: dbsat_output.json (deflated 88%)
zip completed successfully.
  
[oracle@julian dbsat]$

4. Generate the report:

[oracle@julian dbsat]$ ./dbsat report dbsat_output

Database Security Assessment Tool version 2.2.1 (May 2020)

This tool is intended to assist you in securing your Oracle database
system. You are solely responsible for your system and the effect and
results of the execution of this tool (including, without limitation,
any damage or data loss). Further, the output generated by this tool may
include potentially sensitive system configuration data and information
that could be used by a skilled attacker to penetrate your system. You
are solely responsible for ensuring that the output of this tool,
including any generated reports, is handled in accordance with your
company's policies.

Archive:  dbsat_output.zip
[dbsat_output.zip] dbsat_output.json password:
  inflating: dbsat_output.json
DBSAT Reporter ran successfully.

Calling /usr/bin/zip to encrypt the generated reports...

Enter password:
Verify password:
        zip warning: dbsat_output_report.zip not found or empty
  adding: dbsat_output_report.txt (deflated 78%)
  adding: dbsat_output_report.html (deflated 84%)
  adding: dbsat_output_report.xlsx (deflated 3%)
  adding: dbsat_output_report.json (deflated 82%)
zip completed successfully.

5. Extract (for example) the .html file:

[oracle@julian dbsat]$ unzip dbsat_output_report.zip
Archive:  dbsat_output_report.zip
[dbsat_output_report.zip] dbsat_output_report.txt password:
  inflating: dbsat_output_report.txt
  inflating: dbsat_output_report.html
  inflating: dbsat_output_report.xlsx
  inflating: dbsat_output_report.json

6. View the report, note that most areas will probably be in PASS status, some will be with LOW RISK, some might be even classified as HIGH RISK:

Although in ADB we have no OS access, you can still run the Database Security Assessment Tool. Roy Salazar explained on the Pythian blog how to run DBSAT against Autonomous Database.

You need to have the Instant Client installed and then use the ADMIN database user from Autonomous DB.

Clearly, you will get “ORA-20002: Complete without OS Commands” as the execution of the Collector was on the client server instead of on the underlying DB server to which we have no access.

The security of Autonomous Database is so high that you most likely will get a very different report from what we usually see on-premises.

DBSAT on Youtube:

DBAs are a wealth of knowledge!

In Autonomous, Cloud, Databases, DBA on October 19, 2020 at 10:53

Fifteen years ago, in 2005, I remember I read the following speculation about the DBA profession: Oracle 2020 – a Glimpse Into the Future of Database Management. I was curious what would be true 15 years later. Just have a look at some of the predictions:

– 2018: Oracle 14m provides inter-instance sharing of RAM resources. All Oracle instances become self-managing.
– 2019: The first 128-bit processors are introduced.
– 2020: Oracle 16ss introduces solid-state, non-disk database management.

Looking even further we read: “Changing Role of the Oracle DBA in 2020: But the sad reality of server consolidation was that thousands of mediocre Oracle DBAs lost their jobs to this trend. The best DBAs continued to find work, but DBAs who were used for the repetitive tasks of installing upgrades on hundreds of small servers were displaced.” And further: “Inside Oracle 2020: The world of Oracle management is totally different today than it was back in 2004. We no longer have to worry about applying patches to Oracle software, all tuning is fully automated and hundreds of Oracle instances all reside within a single company-wide server.” ADB rings a bell?

Four years ago, another forecast article called The 2020 DBA: A Look Into the Future appeared in dzone.com predicting that despite the evolving role of the DBAs, DevOps has actually made database administrators more relevant than ever before.

True, as the DBA profession is still among the Top 10 jobs in Technology. DBA is #4 in Best Technology Jobs, #15 in Best STEM Jobs and #30 in 100 Best Jobs.

Now, going back to the title of this blog post. Jeff Smith’s, Russ Lowenthal’s and Chris Saxon’s Oracle DBA 2020 Data Masterclasses are something I recommend now to every DBA. Jeff raised an important topic but let us take a step back.

Last month (September 2020), FlashDBA discussed the Evolution of the DBA from 1.0 through 2.0 until 3.0:

DBA 1.0: The (Good) Old Days – clearly the old days are over. Regardless if they were good or bad is a memory-lane discussion.

DBA 2.0: The IT Generalist – I remember when saw this paper for the first time about 10-11 years ago: Oracle DBA 2.0. ASM, Direct NFS, Clusterware, VMware, Flash, Linux. The DBA had to learn OS, Storage and Network administration. With Exadata, I even heard the term DBA 2.1

DBA 3.0: The Cloud DevOps DBA – new game, rather new set! “A DBA building a database in the public cloud is making decisions which have a direct affect on the (quite possibly massive) monthly bill from AWS/Azure/GCP/OCI”.

Checking the Database Management Predictions from 2019, we can see they now we are close to:

DBA 4.0: Autonomous DBA – more attention on data management, data security, data architecture, machine learning, devops:

“Oracle Autonomous Database can quickly provision, resize, and relocate databases with little human interaction. However, as more database provisioning tasks are automated, DBAs will still need to classify the data.” – Michelle Malcher

“CEOs will force DBAs to step into more-important roles—such as data architects, data managers, and chief data offcers—as a company’s data and machine learning algorithms become important drivers of the stock price.” — Rich Niemiec

“Oracle Enterprise Manager Cloud and other third-party tools, in conjunction with Oracle Multitenant will make managing large numbers of databases easier. DBAs will be able to manage 10 times or more databases after consolidation with Oracle Multitenant.” — Anuj Mohan

“DBAs need to understand that there is a true sea change afoot, and there’s no way to stop these market forces. Hopefully, we’ll all be able to embrace this tidal wave and avoid being caught up in the undertow.” — Jim Czuprynski

I believe the adoption of a hybrid infrastructure is inevitable, and the new skills needed (such as cloud set-up, configuration, and monitoring) are mostly cloud-related. DBAs will need to be able to assess the databases and define what they are best suited for. Replication of data and databases will become more complex in hybrid environments—especially when different clouds are involved.

That is coming in few years at most (when most companies will adopt multi-cloud) and call the profession DBA 5.0 if you prefer or just DBA. Every decade has its challenges for IT professionals. Challenges are becoming more complex and DBAs are often on the front line with every new fashionable IT concept. And we can quote now Larry Ellison who said that the computer industry is the only industry that is more fashion-driven than women’s fashion.

Here are finally few additional recent articles on the topic of the future of the DBA profession (if interested to read more on the topic):

My Three Beliefs About The Future Of The DBA Job
The Future for the DBA
Will automated databases kill the DBA position?
What Does the Future Hold for DBAs?
For DBAs In 2020: Understand Your Worth, Seize The Moment
What Happens to DBAs When We Move to the Cloud?
The Future of The DBA in The Era of The Autonomous Database

How is Oracle Autonomous JSON Database different from Oracle ATP and MongoDB?

In Autonomous, Cloud, DBA, Oracle database on October 1, 2020 at 09:09

Oracle Autonomous JSON Database is an Oracle Cloud service that is specialized for developing NoSQL-style applications that use JavaScript Object Notation (JSON) documents. Autonomous JSON Database (in short AJD) stores the JSON documents in a native tree-oriented binary format making it highly optimized for fast reads (avoiding linear scans) and partial updates (reducing redo/undo log sizes).

Like ADW and ATP, AJD delivers also auto-scaling, automated patching, upgrades, maximum security and auto-tuning. I do agree with Philipp Salvisberg that AJD is a special version of the Autonomous Transaction Processing (ATP).

The leader of pure document stores (as of September 2020) is MongoDB. Amazon DynamoDB and Microsoft Azure Cosmos DB are also extremely popular.

Autonomous JSON Database provides all of the same features as Autonomous Transaction Processing and, unlike MongoDB, allows developers to also store up to 20 GB of non-JSON data. There is no storage limit for JSON collections.

An excellent comparison of Oracle AJD and MongoDB can be found in the article entitled Introducing Oracle Autonomous JSON Database for application developers by Beda Hammerschmidt:

Oracle AJD is very similar to Oracle ATP with the major difference that AJD is meant for document databases containing lots of JSON format documents. You can think of ATP as more of a hybrid version of the Autonomous database.

Looking at the init.ora parameters I observed something after comparing an ATP and an AJD spfile parameters. Except the obvious ones like instance_name or service_names I found only the following differences (both are with 1 OCPU and 1TB of storage):

cpu_count: 6 for AJD and 2 for ATP
db_recovery_file_dest_size: 88406716M for AJD and 123789329M for ATP
gcs_server_processes: 4 for AJD and 5 for ATP
pdb_lockdown: JDCS for AJD and OLTP for ATP
resource_manager_cpu_allocation: 92 for AJD and 100 for ATP
shared_pool_reserved_size: 2254857830 for AJD and 3248069017 for ATP
transactions: 66083 for AJD and 66110 for ATP

Clearly, this is what I did not expect. Obviously, the biggest difference between AJD and ATP comes from the lockdown profiles.

So, here are the three differences between Oracle AJD and ATP:

1. Lockdown profiles
2. AJD can store at most 20 GB of non-JSON data
3. Small differences in the init.ora parameters

And here are the three differences between Oracle AJD and MongoDB Atlas:

1. Autonomous JSON Database costs 30% less than comparable MongoDB Atlas configurations: $2.74/hr versus $3.95/hr
2. Autonomous JSON database gives you 2x throughput consistently across different workload types and collection sizes
3. Autonomous JSON Database comes with more capabilities than MongoDB Atlas

A good starting point is the documentation of Autonomous JSON Database for Experienced Oracle Database Users. It is mostly about restrictions for SODA and JSON, SQL and other database features.

The SODA and JSON Tutorials are a good starting point to getting used to working with AJD.

Finally, here are 5 Oracle Autonomous JSON Database use cases:

– Mobile applications
– Applications with dynamic personalized experiences
– Content and catalog management
– Integrated IoT applications
– Digital payment applications

And here is a good article by Maria Colgan on How does Autonomous Transaction Processing differ from the Autonomous Data Warehouse.

MySQL DB System on Oracle Cloud Infrastructure

In Cloud, Databases, DBA, IaaS, MySQL, New features on September 1, 2020 at 08:37

MySQL has been for a long time second next to Oracle in the DB-Engines rankings. Moreover, MySQL was their 2019 Database of the Year!

Now, MySQL is also available as DB System on Oracle Cloud Infrastructure. The database version is 8.0.21 with InnoDB Storage Engine. Here is how the set-up works:

1. Create a MySQL DB System:

2. Note that there are different shutdown types:

3. Connect to a MySQL DB system:

It is not possible to connect directly from a remote IP to the MySQL DB System endpoint. You must connect to a Compute Instance, and from the Compute Instance to the MySQL DB System.

We first install MySQL Shell on the Compute instance:

 
[opc@julian ~]$ sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
Loaded plugins: ulninfo, versionlock
mysql80-community-release-el7-3.noarch.rpm               |  25 kB     00:00
Examining /var/tmp/yum-root-X8dAdb/mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
Marking /var/tmp/yum-root-X8dAdb/mysql80-community-release-el7-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package             Arch   Version
                                  Repository                               Size
================================================================================
Installing:
 mysql80-community-release
                     noarch el7-3 /mysql80-community-release-el7-3.noarch  31 k

Transaction Summary
================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
  Installing : mysql80-community-release-el7-3.noarch                       1/1
  Verifying  : mysql80-community-release-el7-3.noarch                       1/1

Installed:
  mysql80-community-release.noarch 0:el7-3

Complete!
[opc@julian ~]$

===

[opc@julian ~]$ sudo yum install mysql-shell
Loaded plugins: ulninfo, versionlock
mysql-connectors-community                               | 2.5 kB     00:00
mysql-tools-community                                    | 2.5 kB     00:00
mysql80-community                                        | 2.5 kB     00:00
ol7_UEKR5                                                | 2.5 kB     00:00
ol7_latest                                               | 2.7 kB     00:00
(1/3): mysql80-community/x86_64/primary_db                 | 115 kB   00:00
(2/3): mysql-connectors-community/x86_64/primary_db        |  62 kB   00:00
(3/3): mysql-tools-community/x86_64/primary_db             |  76 kB   00:00
(1/5): ol7_UEKR5/x86_64/updateinfo                         |  72 kB   00:00
(2/5): ol7_latest/x86_64/group                             | 660 kB   00:00
(3/5): ol7_latest/x86_64/updateinfo                        | 2.9 MB   00:00
(4/5): ol7_UEKR5/x86_64/primary_db                         |  12 MB   00:00
(5/5): ol7_latest/x86_64/primary_db                        |  36 MB   00:00
Excluding 238 updates due to versionlock (use "yum versionlock status" to show them)
Resolving Dependencies
--> Running transaction check
---> Package mysql-shell.x86_64 0:8.0.21-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package         Arch       Version             Repository                 Size
================================================================================
Installing:
 mysql-shell     x86_64     8.0.21-1.el7        mysql-tools-community      31 M

Transaction Summary
================================================================================
Install  1 Package

Total download size: 31 M
Installed size: 106 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/mysql-tools-community/packages/mysql-shell-8.0.21-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Public key for mysql-shell-8.0.21-1.el7.x86_64.rpm is not installed
mysql-shell-8.0.21-1.el7.x86_64.rpm                        |  31 MB   00:02
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering "
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-shell-8.0.21-1.el7.x86_64                              1/1
  Verifying  : mysql-shell-8.0.21-1.el7.x86_64                              1/1

Installed:
  mysql-shell.x86_64 0:8.0.21-1.el7

Complete!
[opc@julian ~]$

Then connect to your DB System using the MySQL client:

 
mysqlsh ICO@10.0.0.3 

[opc@julian ~]$ mysqlsh ICO@10.0.0.3
Please provide the password for 'ICO@10.0.0.3': ************
MySQL Shell 8.0.21

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'ICO@10.0.0.3'
[opc@julian ~]$

My suggestion is to start from the MySQL Database Service Overview.

MySQL database is also available from GCP, Azure and AWS but check the pricing:

The CLOUD_VERIFY_FUNCTION in Autonomous Database Cloud

In Autonomous, Cloud, DBA, New features, Oracle internals, Security and auditing on July 1, 2020 at 12:58

Choosing a hard-to-guess but easy-to-remember password is by far the easiest one from all the hard tasks!

1. Database passwords and their complexity:

According to GDPR personal data must be processed “in a manner that ensures appropriate security of personal data including protection against unauthorized or unlawful processing and against accidental loss, destruction or damage, using appropriate technical or organizational measures.”

But GDPR does not define any requirements about passwords such as password length, complexity, or how often password should be renewed. Regulation (EU) 2016/679 just stipulates that “a high level of protection of personal data” is required.

One way to enforce strong passwords on database users is by using the following rule:

A minimum of 1 lower case letter [a-z] and
a minimum of 1 upper case letter [A-Z] and
a minimum of 1 numeric character [0-9] and
a minimum of 1 special character: ~`!@#$%^&*()-_+={}[]|\;:”,./?
Passwords must be at least N characters in length
N attempts to block login
Set password expiration to N days

Oracle is following the above mentioned rules and the Oracle script catpvf.sql provides several password functions for taking care of the verification process:

– ora_complexity_check,
– verify_function
– verify_function_11G
– ora12c_verify_function
– ora12c_strong_verify_function
– ora12c_stig_verify_function

Note that the VERIFY_FUNCTION and VERIFY_FUNCTION_11G password verify functions are desupported in Oracle Database 20c. Also, in Oracle 20c, the IGNORECASE parameter for the orapwd file is desupported. All newly created password files are case-sensitive.

3. Non-autonomous databases

Now, how about those who prefer to use less complex passwords for database users? How do you bypass that problem first in a non-autonomous environment?

There are several ways to avoid the verification process by say the ora12c_verify_function:

– ALTER PROFILE DEFAULT LIMIT PASSWORD_VERIFY_FUNCTION NULL;
– Create a separate profile for the user
– Edit the catpvf.sql script to use the password verification function that you want, and then run the script to enable it – it is located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql
– Modify “CREATE OR REPLACE FUNCTION ora12c_verify_function …” in utlpwdmg.sql, a file which is used to change the DEFAULT profile to use different password complexity functions – it is located in $ORACLE_HOME/rdbms/admin/utlpwdmg.sql (not in 20c though)

Note here that the Oracle documentation says clearly: “Do not modify the admin/catpvf.sql script or the Oracle-supplied password complexity functions. You can create your own functions based on the contents of these files.”

3. Autonomous databases

Next, how about Autonomous, where we have no access to the operating system layer?

The Oracle Autonomous Database Cloud offers a new (unique to ADB) a function called CLOUD_VERIFY_FUNCTION. It is not available in the non-autonomous releases and not even in Oracle 20c.

The CLOUD_VERIFY_FUNCTION function is specified in the PASSWORD_VERIFY_FUNCTION attribute of the DEFAULT profile. This function internally calls ORA_COMPLEXITY_CHECK and checks the password entered according to the following specifications.

– If password contains the username
– The password must contain 1 or more lowercase characters
– The password must contain 1 or more uppercase characters
– The password must contain 1 or more digits
– The password length less than 12 bytes or more than 60 bytes

Let us check first what the function CLOUD_VERIFY_FUNCTION looks like:

 
create or replace FUNCTION cloud_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
   differ integer;
  db_name varchar2(40);
  i integer;
  reverse_user dbms_id;
  canon_username dbms_id := username;
  len integer := nvl (length(password), 0);
BEGIN
  IF (substr(username,1,1) = '"') THEN
    execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
  END IF;
   IF NOT ora_complexity_check(password, 12, null, 1, 1, 1, null) THEN
     RETURN(FALSE);
   END IF;
-- Check password length
   IF len > 60 THEN
     raise_application_error(-20020, 'Password too long');
   END IF;
-- Check if the password contains the username
   IF regexp_instr(password, canon_username, 1, 1, 0, 'i') > 0 THEN
     raise_application_error(-20002, 'Password contains the username');
   END IF;
RETURN(TRUE);
END;
/

We cannot modify the scripts mentioned above as we do not have OS access in ADB – may be then we can change the default profile or create a new one? But in ATP, user’s profile will be set to ‘DEFAULT’, and you are not allowed to create additional PROFILEs. Autonomous Data Warehouse requires strong passwords – the password must meet the default password complexity rules.

The output below is identical in ADW and ATP:

Well, we are stubborn – so let us try in any case:

In ATP:

 
create profile DBA_PROFILE 
LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30
ORA-01031: insufficient privileges

In ADW:

 
create profile DBA_PROFILE 
LIMIT PASSWORD_REUSE_MAX 10 PASSWORD_REUSE_TIME 30;
Profile DBA_PROFILE created.

alter profile DBA_PROFILE limit PASSWORD_VERIFY_FUNCTION null;
Profile DBA_PROFILE altered.

alter user admin profile DBA_PROFILE;
ORA-01031: insufficient privileges

create user app_user identified by abc profile DBA_PROFILE
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 12 bytes

create user app_user identified by Exadataa2020 profile DBA_PROFILE;
User APP_USER created.

 
alter user app_user identified by abc
ORA-28219: password verification failed for mandatory profile
ORA-20000: password length less than 12 bytes

Well, the password verify function is still used although we set the app_user’s profile to DBA_PROFILE.

Conclusion: there is no way to modify or bypass the CLOUD_VERIFY_FUNCTION in Autonomous Cloud.

In 20c, the script utlpwdmg.sql has been modified, it does not contain any longer the creation of the verification functions. I really don’t know if that is intentional.

 
[oracle@julian admin]$ cat utlpwdmg.sql
...

-- This script sets the default password resource parameters
-- This script needs to be run to enable the password features.
-- However the default resource parameters can be changed based
-- on the need.
-- A default password complexity function is provided.

Rem *************************************************************************
Rem BEGIN Password Management Parameters
Rem *************************************************************************

-- This script alters the default parameters for Password Management
-- This means that all the users on the system have Password Management
-- enabled and set to the following values unless another profile is
-- created with parameter values set to different value or UNLIMITED
-- is created and assigned to the user.

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX  UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
INACTIVE_ACCOUNT_TIME UNLIMITED
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;

/**
The below set of password profile parameters would take into consideration
recommendations from Center for Internet Security[CIS Oracle 11g].

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 90
PASSWORD_GRACE_TIME 3
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX  20
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION ora12c_verify_function;
*/

/**
The below set of password profile parameters would take into
consideration recommendations from Department of Defense Database
Security Technical Implementation Guide[STIG v8R1].

ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX  5
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;
*/

Rem *************************************************************************
Rem END Password Management Parameters
Rem *************************************************************************
[oracle@julian admin]$

On a final note: the hashed values of the passwords in 20c can be still found in the data dictionary – look into the SPARE4 column of the SYS.USER$ table. It is similar to 19c, read this article for all the details.