Archive for the ‘New features’ Category

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!

Automatic Materialized Views in Oracle Database 21c

In Databases, DBA, New features, Oracle database on February 5, 2021 at 15:51

“It’s supposed to be automatic, but actually you have to push this button.” ― John Brunner

With Oracle Database 21c, there is no button for Automatic Materialized Views – it is fully automatic.

DBA_MVIEWS has a new column call AUTO – that is how a DBA can distinguish the auto MVs from the manual ones. The auto naming convention is something like AUTO_MV$$_H3KBHG7DAH6T5. That is all start with AUTO_MV$$.

The Oracle database automatically collects workload statistics, SQL statements and query execution statistics. Oracle also maintains and purges the history of the workload. Automatic materialized views use workload information provided by the Object Activity Tracking System (OATS) as part of the automated decision-making processes.

All preconfigured / additionally configured parameters can be viewed from DBA_AUTO_MV_CONFIG:

Here is how automatic materialized views work:

– The database automatically detects and collects workload query execution statistics including buffer-gets, database time, estimated cost, and other statistics: DBA_AUTO_MV_ANALYSIS_EXECUTIONS displays information about analysis and tuning executions, including concurrency, degree of parallelism (DOP) requested by the user and actual DOP upon execution finish, status, associated advisor, and informational or error message.

– Oracle creates candidate materialized views hidden from the database workload and verifies that they will deliver the projected performance benefit by test executing a sample of workload queries in the background: DBA_AUTO_MV_ANALYSIS_RECOMMENDATIONS
Displays recommendations associated with automatic materialized views

– There are provided reports with detailed performance test results and which materialized views have been implemented: DBA_AUTO_MV_ANALYSIS_REPORT reports on analyses and recommendations, including task and execution names, sequence number of the journal entry, and message entry in the journal

– Automatic materialized view refresh is also automatic: DBA_AUTO_MV_REFRESH_HISTORY displays the owner name, view name, date, start and end time, elapsed time, status, and error number (if an error occurred) for each automatic materialized view refresh

Automatic MVs are off by default. As DBAs, we can use the CONFIGURE procedure of the DBMS_AUTO_MV package to configure automatic materialized views creation in the database. The AUTO_MV_MODE parameter enables (IMPLEMENT) or disables (OFF) automatic materialized views, or engages report-only mode (REPORT ONLY). The AUTO_MV_MAINT_TASK parameter activates or deactivates the task performing the maintenance (refreshes, validations, and clean up).

Let us enable it all:

Oracle Database 21c includes data dictionary views that display information about automatic materialized views as well as OATS (Object Activity Tracking System). DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control the three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES','30');

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS','60');

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT','10');

You mostly likely will get though the following error, even alter system set “_exadata_feature_on”=true scope=spfile; and restart:

ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_ACTIVITY", line 278
ORA-06512: at "SYS.DBMS_ACTIVITY", line 314
ORA-06512: at line 1

The feature is only available on Exadata and ExaCs.

Here are some additional details:

– Automatic materialized views support partitioned and non-partitioned base tables
– Incremental materialized view refresh is supported
– If partitioned base tables use either range, list, or composite partitioning, then they are eligible for Partition Change Tracking (PCT) view refresh
– If there is performance advantage, the automatic materialized view recommendations will include a partitioned automatic materialized view based on the partitioning of the base table of the materialized view and the partitioning type supported is auto-list partitioning, which will mirror the partitioning of the fact table
– The automatic materialized view maintenance module decides the type of refresh that is the most beneficial at the time of refresh, and will decide during run time whether to switch from incremental refresh to full refresh
– DBAs can drop automatic materialized views using the dbms_auto_mv.drop_auto_mv procedure

5 new core DBA features in Oracle Database 21c

In DBA, New features, Oracle database, Oracle internals, PL/SQL on January 8, 2021 at 08:14

If wondering what new can come after so many releases of the Oracle Database – well, here is something for system DBAs.

Here are 5 new features related to Database Vault, Syslog, the new SYS_AUTO_STS_MODULE, and the 2 new DBA packages SYS.DBMS_FLASHBACK_ARCHIVE_MIGRATE & SYS.DBMS_ACTIVITY.

1. No need any longer to disable Oracle Database Vault in every container before the upgrade! We only need to grant the DV_PATCH_ADMIN role to SYS commonly before performing the upgrade. After the upgrade is complete the Database Vault controls work as before. Then we can revoke the DV_PATCH_ADMIN role from SYS. Alternatively, we can explicitly disable Oracle Database Vault in all containers before the upgrade and then after the upgrade explicitly enable Oracle Database Vault in all the containers.

2. A new concept in the Oracle Database is the Object Activity Tracking System.

Object Activity Tracking System (OATS) tracks the usage of various types of database objects. Usage includes operations such as access data manipulation or refresh.

The DBMS_ACTIVITY PL/SQL package contains functions and procedures for configuring Object Activity Tracking System (OATS) information collection and management. Data collected by OATS is used in analyses performed by automatic materialized views.

DBAs can use the DBMS_ACTIVITY.CONFIGURE procedure to control three OATS parameters within a specific database.

ACTIVITY_INTERVAL defines the interval between snapshots:

exec dbms_activity.configure('ACTIVITY_INTERVAL_MINUTES',30);

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS',60);

ACTIVITY_SPACE_PERCENT sets how much of available space is reserved for snapshots:

exec dbms_activity.configure('ACTIVITY_SPACE_PERCENT',10);

And use these tables to monitor the activity:


3. SYSLOG destination for common Unified Audit Policies:

Certain predefined columns of unified audit records from common unified audit policies can be written to the UNIX SYSLOG destination.

To enable this new feature we should set UNIFIED_AUDIT_COMMON_SYSTEMLOG which is a new CDB level init.ora parameter. This enhancement enables all audit records from common unified audit policies to be consolidated into a single destination. This feature is available only on UNIX platforms not Windows (of course).

The new parameter has no default, we should set both the facility_clause and the priority_clause values.

Only a subset of unified audit record fields are written to ensure that the audit record entries do not exceed the maximum allowed size for a SYSLOG entry (typically 1024 bytes).

4. SYS_AUTO_STS_MODULE is the new module for the Auto SQL Tuning Sets. The feature exists since Oracle 19.7 so technically not really a new 21c feature although listed as such. Check Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards (Doc ID 2686869.1)

If you try searching Google or MOS for SYS_AUTO_STS_MODULE, then most likely you will get similar to what I got from my search:

Clearly, Franck Pachot has already met with the auto SQL tuning set module but that is expected behavior – he is one of the pioneers in new features and database research.

First, what is the Automatic SQL Tuning Set? In short, auto STS is an automatic repository for historic SQL performance metrics and execution plans. Oracle is now having the module handling all that automatically.

Recently, Doc ID 2733254.1 shows after a search for the SYS_AUTO_STS_MODULE but the note is about a hang when creating text indexes although SYS_AUTO_STS_MODULE pops up 3 times under SQL ordered by CPU Time.

Out of curiosity, you may run this SQL also against your 21c database and observe the actions the module has been taking:

select ACTION, count(*) 
where module = 'SYS_AUTO_STS_MODULE' 
group by ACTION;

In a real production database (ADW), I noticed that after the module stopped appearing in the AWR report, the performance went back to normal:


A new PL/SQL package called DBMS_FLASHBACK_ARCHIVE_MIGRATE enables the migration of Flashback Data Archive enabled tables from a database on any release (in which the package exists) to any database on any release (that supports Flashback Data Archive).

The package has 3 procedures:

– EXPORT exports the given Flashback Archive enabled base tables, their history and related tablespaces
– EXPORT_ANALYZE analyzes the given Flashback Archive enabled base tables, their history, and related tablespaces for self containment using Transportable tablesapce checks
– IMPORT imports the Flashback Archive enabled base tables that were exported, their history, and related tablespaces

Here are the prerequisites:

Database version >= 11.2
If database version is 11.2, following conditions should be met:
shared_pool_size >= 500M
streams_pool_size >= 40M or
sga_target >= 2G

The DBMS_FLASHBACK_ARCHIVE_MIGRATE package must be compiled on both the source and target databases as SYS. The source file is located at ?/rdbms/admin/crefbamig.sql, using which the package can be created or compiled.

The export and import procedures must be executed as SYS. Since the package uses DBMS_DATAPUMP, DBMS_LOCK, DBMS_SYSTEM, DBMS_SQL and DBMS_SCHEDULER PL/SQL packages, their security models are also applicable.

More on 21c? Check:

1. Introducing Oracle Database 21c
2. A glimpse of what is new in Oracle Database 21c

A glimpse of what is new in Oracle Database 21c

In Autonomous, New features, Oracle database on December 4, 2020 at 16:12

Oracle Database 21c will be soon available first on Oracle Cloud: from the Database Cloud Service and the Autonomous Database Free Tier.

Here is a preview of what we can expect as new features:

1. You can enable automatic indexing at the table level:

The AUTO_INDEX_TABLE configuration setting specifies tables that can use auto indexes. When you enable automatic indexing for a schema, all the tables in that schema can use auto indexes. However, if there is a conflict between the schema level and table level setting, the table level setting takes precedence.

Here is an example of how to instruct Oracle to create auto indexes on the NDA_DOCS table:


If I would like to add the NDA_DOCS table to the auto index exclusion list, I simply run:


If later, I decide to remove it from the exclusion list, I will run:


And if at one point, I decide to remove all the tables from the exclusion list, so that all the tables in the database can use auto indexes, I will execute:


In Oracle 20c and below, you will get the following error message: ORA-38133: invalid parameter name AUTO_INDEX_TABLE specified.

2. Attention Log

The attention log which is unique for each database instance is according to the documentation “structured, externally modifiable file that contains information about critical and highly visible database events”.

We can use the attention log to quickly access information about critical events that need action.

The attention log has the following attributes:

Attention ID: A unique identifier for the message.
Attention type: The type of attention message. Possible values are Error, Warning, Notification, or Additional information. The attention type can be modified dynamically.
Message text
Urgency: Possible values are Immediate, Soon, Deferrable, or Information.
Scope: Possible values are Session, Process, PDB Instance, CDB Instance, CDB Cluster, PDB (for issues in persistent storage that a database restart will not fix), or CDB (for issues in persistent storage that a database restart will not fix).
Target user: The user who must act on this attention log message. Possible values are Clusterware Admin, CDB admin, or PDB admin.

Here is an example from Oracle:

3. For Oracle Autonomous Database, the size of the sequence cache is dynamically computed based on the rate of usage of sequence numbers:

“The automatic sequence cache size on each instance is dynamically computed based on the rate of usage of sequence numbers. Each instance caches the maximum of the manually configured sequence cache size and the projected cache size requirement for the next 10 seconds. Based on the sequence usage, the sequence cache size can shrink or grow. The minimum size to which the cache can shrink is the manually configured cache size. To prevent the sequence cache size from growing indefinitely, the cache size and each increment in the cache size is capped.”

4. Traditional auditing is now deprecated and Oracle recommends that we use unified auditing instead.

5. REMOTE_OS_AUTHENT is now desupported – in fact it was deprecated in Oracle 11.1 and afterwards retained only for backward compatibility.

The New Features 21c Guide lists also the following features which you can use also in Oracle 20c:

Blockchain Tables
– Database Resident Connection Pooling (DRCP) can be configured at the PDB level:

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

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

– Transportable tablespace jobs can be restarted:

expdp system/password attach=jmd_exp_20c
Export> continue_client

– In Oracle Cloud environments, a PDB can be downsized by reducing the value of the CPU_MIN_COUNT parameter. The default value for JOB_QUEUE_PROCESSES across all containers is automatically derived from the number of sessions and CPUs configured in the system. It is the lesser value of:


– The IGNORECASE parameter for the orapwd file is desupported and all newly created password files are case-sensitive.

SecureFiles segments can be shrunk for improving performance.

Detecting Data Tampering and Measuring Asymmetry and Tailedness of Data in Oracle Database 20c

In DBA, New features, SQL on September 11, 2020 at 11:27

There are seven types of SQL functions in the Oracle database:

1. Single-Row Functions
2. Aggregate Functions
3. Analytic Functions
4. Object Reference Functions
5. Model Functions
6. OLAP Functions
7. Data Cartridge Functions

The single-row functions are the most popular and used ones but the aggregate and analytical function are also extremely popular among Oracle developers (and by DBAs too).

Three new analytical and statistical aggregate functions are now available in Oracle Database 20c. Let us use the RDBMS_BRANDS table for the 3 examples below. For the sake of clarity the last column shows if the database is only available from the cloud.

1. CHECKSUM computes the checksum of the input values or expression and can be applied on a column, a constant, a bind variable, or an expression involving them. All datatypes except ADT and JSON are supported.

In earlier releases you can still use DBMS_SQLHASH.GET_HASH in order to check the integrity of result sets or some other other PL/SQL packages: STANDARD_HASH or DBMS_CRYPTO.

Here is an example of how to use CHECKSUM:

where DB_ENGINES_RANK < 50; 

--------------- ----------------
              1          1345.44
              2          1282.64
              3          1078.31
              4           514.81
              5           162.64
              8            90.09
             10            73.89
             13            50.54
             15            27.59
             18            20.27
             42             3.73
             48             2.36

12 rows selected.


------------------------- --------------------------
                   288250                     209742

Let us now update the rankings of Oracle which in September went up to 1369.36 points and observe how the checksum value for DB_ENGINES_SCORE changed from 209742 to 180002!


1 row updated.

SQL> commit;

Commit complete.

SQL> select 

------------------------- --------------------------
                   288250                     180002

Note: NULL values in CHECKSUM column are ignored. Also, if you rollback the transaction, then the checksum value does not change.

There are 2 other analytical functions which are new to Oracle 20c. Skewness and Kurtosis describes the shape of a probability distribution and there are different ways of quantifying it for a theoretical distribution and corresponding ways of estimating it from a sample from a population. Oracle is now offering a very easy way of calculating their values by providing the in-built analytical functions in Oracle 20c.

2. SKEWNESS functions SKEWNESS_POP and SKEWNESS_SAMP are measures of asymmetry in data. A positive skewness is means the data skews to the right of the center point. A negative skewness means the data skews to the left.

Here is an example of how to use SKEWNESS:

SQL> select CLOUD_ONLY, count(*) from RDBMS_BRANDS group by CLOUD_ONLY;

C   COUNT(*)
- ----------
N          9
Y          3

group by CLOUD_ONLY; 

- ----------------------------- ------------------------------
N                     2.0503487                     .584150452
Y                    .685667537                      -.4626607

Skewness makes sense in the situation where DB_ENGINES_RANK and DB_ENGINES_SCORE represent the database brand rank and score in the list and you want to determine whether the outliers in data are biased towards the top end or the bottom end of the distribution, that is, if there are more values to the top of the mean when compared to the number of values to the bottom of the mean.

3. KURTOSIS functions KURTOSIS_POP and KURTOSIS_SAMP measure the tailedness of a data set where a higher value means more of the variance within the data set is the result of infrequent extreme deviations as opposed to frequent modestly sized deviations.

Here is an example of how to use KURTOSIS:

SQL> select CLOUD_ONLY, count(*) from RDBMS_BRANDS group by CLOUD_ONLY;

C   COUNT(*)
- ----------
N          9
Y          3

group by CLOUD_ONLY;  

- ----------------------------- ------------------------------
N                    2.88880521                     -1.4382391
Y                             0                              0

Note that a normal distribution has a kurtosis of zero. Have a look at the KURTOSIS_POP for the cloud-only databases Google BigQuery, Amazon Redshift and Snowflake.

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
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
--&gt; Running transaction check
---&gt; Package mysql80-community-release.noarch 0:el7-3 will be installed
--&gt; Finished Dependency Resolution

Dependencies Resolved

 Package             Arch   Version
                                  Repository                               Size
                     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

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

[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
--&gt; Running transaction check
---&gt; Package mysql-shell.x86_64 0:8.0.21-1.el7 will be installed
--&gt; Finished Dependency Resolution

Dependencies Resolved

 Package         Arch       Version             Repository                 Size
 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

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

[opc@julian ~]$

Then connect to your DB System using the MySQL client:

mysqlsh ICO@ 

[opc@julian ~]$ mysqlsh ICO@
Please provide the password for 'ICO@': ************
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@'
[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:

Sharding Advisor and Federated Sharding in Oracle Database 20c

In DBA, New features, Oracle database on August 3, 2020 at 05:58

“Shards of glass can cut and wound or magnify a vision.” – Terry Tempest Williams

Same with databases: a single shared database can be a performance bottleneck, a sharded database can magnify your IT vision stragegy.

Oracle RAC and Oracle Data Guard meet more than 99% of the scalability and availability needed by business applications but sometimes infinite availability is also required.

Sharding is an architectural concept which aims at high scalability and absolute availability by splitting the database into a collection of independent physical databases. But this is from the perspective of the DBA. From the perspective of an application, a sharded database looks like a single database; the number of shards, and the distribution of data across those shards, are completely transparent to the application.

Sharding was introduced with Oracle 12c and MOS Doc ID 2226341.1 is the master note for Oracle Sharding. What is important to know is that you can shard database tables by consistent hash (system-managed sharding), by range or list (user-defined sharding), or a combination (composite sharding).

In Oracle 20c, Oracle came with 2 new advisors: Oracle Autonomous Database Advisor and the Oracle Sharding Advisor. The Oracle Sharding Reference is good page to bookmark!

Sharding Advisor is a standalone command-line tool that helps you redesign a database schema so that you can efficiently migrate an existing non-sharded Oracle Database to an Oracle sharding environment. Sharding Advisor analyzes your existing database schema and produces a ranked list of possible sharded database designs.

The Sharding Advisor is a an OS command line tool called gwsadv and by default the workload is captured from V$SQL_PLAN_STATISTICS_ALL.

Here is how you invoke/run the Oracle Sharding Advisor:


[oracle@julian ~]$ gwsadv -n -s -u julian -p abc -c -w


Sharding Advisor: Release 20.0 - Development on Sun Jul 26 2020 07:16:07
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.

Altogether there are 10 possible parameters/flags for the tool, only 3 are mandatory: the username -u, the password -p and -w which directs Sharding Advisor to use the query workload for sharding configuration generation and ranking. The -c parameter is required only on a first run or on a changed workload. For the remaining 6 non-mandatory flags, check the Sharding Advisor Usage and Options.

Note that the sch flag specifies the list of schemas for the Sharding Advisor if you want to run it as a different user which you create specifically for the Sharding Advisor. That user will need SELECT privileges on GV$SQL_PLAN and GV$SQL_PLAN_STATISTICS_ALL, and on the DBA_HIST_SQL_PLAN, DBA_HIST_SQLSTAT, and DBA_HIST_SNAPHSOT tables. The user does not need any other special privileges.

Once you start the Sharding Advisor with the command above, you will be asked you some questions to help in the sharded database design, the first question being on how many shards you are planning to have:

** The number of shards in a sharded configuration is an important parameter **
** that can influence scalability, fault isolation, and availability. **




Usually, a database schema has some tables with multiple foreign key constraints. Sharding has a limitation that a sharded table can only have one foreign key constraint that links it to another table in the same table family. As a result, for every table that has more than one foreign key constraint, a single constraint must be chosen to be enforced in the sharded schema. You will be asked questions like this:

** The 'JULIAN.ORDERS' table has multiple foreign key constraints as shown below. **

** 1. 'JULIAN.ORDERS' is related to primary table 'JULIAN.ORDER_TYPE' by foreign key 'ORDER_TYPE_FK' with a fan-out of '1.8' **
** 2. 'JULIAN.ORDERS' is related to primary table 'JULIAN.CLIENTS' by foreign key 'CLIENTS_FK1' with a fan-out of '1.3' **


Note: Fan-out, in any tree, is number of pointers to child nodes in a node. In Oracle, in this case, fan-out is the ratio of number of rows in the referencing table to that of the referenced table (parent/primary table).

To review the Sharding Advisor, use the following 6 output tables out of which only the first 3 are documented:


Check also the following Sharding Advisor Output Review SQL Examples.

There are few restrictions to the Sharding Advisor:

– The source database must be Oracle Database 10g or later
– The Sharding Advisor discovers the table families based on primary key-foreign key relationships, so if the schema does not have any primary key-foreign key constraints, sharding by PARENT clause is recommended
– Sharding Advisor recommends only single-table family, system-managed sharding (sharding by reference) configurations if the source database has foreign key constraints
– Sharding Advisor recommends sharding using the PARENT clause if the source database does not have foreign key constraints

Oracle have declarative rapid deployment automation for Sharding available in OCI Marketplace as well as on-premises with Terraform and Kubernitis. Check the Oracle blog article Sharding Oracle Database Cloud Service by Shailesh Dwivedi.


In Oracle 20c, you can also create a Sharded Database from multiple existing databases – this is called Federated Sharding.

It is now possible to convert a set of existing physical independent databases running the same application into a sharded database without modifying the database schemas or the application.

Oracle Sharding, in a federated sharding configuration, treats each independent physical database as a shard, and thus you can run multi-shard queries on all the shards.

You can create a federated sharding configuration with minor version mismatches between the shards. For example, the “North America” region could be on Oracle 20.2 and the “Europe” region could be on Oracle 20.3. But all database shards and the shard catalog must be on Oracle Database 20c or later. The databases must have the same schemas and only minor differences are supported. For example, a table can have an extra column or a missing column in one of the databases.

Often there are application upgrades which means that new tables, new columns, new constraint are added or column data types are being modified. When part of an overall Federated Sharding configuration, Oracle Sharding handles the schema differences caused by an application upgrade, as long as the overall schema structure stays the same.

To create a Federated Sharding environment using existing databases, you define the database layout just as you would for user-defined sharding, using GDSCTL commands. You need to execute these 7 steps in order to deploy a Federated Sharding configuration.

Just as an example, here is how you convert tables to duplicated or sharded tables:


These are the limitations on Federated Sharding:

– There is no concept of chunk in a federated sharding configuration, so the GDSCTL MOVE CHUNK command is not supported
– Application sharding key-based routing is not supported
– The existing databases, before being added to a federated sharding configuration, must be upgraded to Oracle Database 20c or later

If you managed to bare reading until this last paragraph, please check also Partitioning vs. Federation vs. Sharding. Its last paragraph too…

PDB Point-in-Time Recovery and Flashback in Oracle 20c

In DBA, New features, Oracle database, RMAN on July 10, 2020 at 14:41

The most significant point about the Oracle 20c database architecture is that non-CDB Oracle Database upgrades to non-CDB architecture are desupported. Meaning you need a container database in 20c and your data will reside within a pluggable database.

But then how about if you need to restore one PDB to any time in the recent past?

In Oracle database 20c, flashback and PITR (=point-in-time recovery) are supported when recovering PDBs to an ancestor or orphan PDB incarnations. These operations were not possible in 19c and below. Just as a reminder, in Oracle 12.1 flashback database operations were possible on root container level and thus affected all PDBs under the root container. Oracle 12.2 started supporting flashback of a PDB.

There is one restriction though in 20c: you cannot perform PDB flashback or PITR operation to a PDB incarnation within an orphan database incarnation. In other words, you can flashback as long as the CDB incarnation does not change. Or restated: you can flashback a PDB to an orphan PDB incarnation that is either within the same CDB incarnation or in an ancestor CDB incarnation. Also, flashback of a PDB to an orphan incarnation is supported only when the database uses local undo.

Now, this might confusing. Let me first shortly explain what is an ancestor incarnation and an orphan incarnation.

Database incarnations have the following relationships to each other:

– The current incarnation is the one in which the database is currently operating
– The incarnation from which the current incarnation originated after an OPEN RESETLOGS operation is the parent incarnation of the current incarnation
– The parent of the parent incarnation is an ancestor incarnation and any parent of an ancestor incarnation is also an ancestor of the current incarnation
– A noncurrent incarnation that is not a direct ancestor of the current incarnation is called orphan incarnation

During the flashback of the PDB, Oracle modifies only the data files for that PDB. The data in the other PDBs is not impacted. The point in time for the flashback can be one of the following:

– System Change Number
– Specific time in the past
– CDB restore point
– PDB restore point
– PDB clean restore point
– PDB guaranteed restore point

Here is an example of how flashback to any time in the recent past works in Oracle 20c.

We have lost at 2:30pm a table called RDBMS_BRANDS and a materialized zone map RDBMS_ZMAP from a pluggable database called NOVOPDB2. We have a restore point called rp1_novo_pdb2 created before the “disaster” at 8am in the morning. So, let us first flashback and verify we get the 2 objects back:

SQL> select systimestamp from dual;

05-JUL-20 PM +00:00


no rows selected


Pluggable database altered.


Flashback complete.


Pluggable database altered.



Well, unfortunately, now we notice that slightly before noon time data was loaded into a new table called RDBMS_HISTORY which was not at 8am in the PDB. All SCNs between 8am and the current time are now on an orphan PDB incarnation. We will flahsback again using another restore point created at 12 o’clock.


Pluggable database altered.


Flashback complete.


Pluggable database altered.



SQL> select systimestamp from dual;

05-JUL-20 PM +00:00

So, we managed to flashback to a point few hours after we opened with RESETLOGS from the previous flashback. But within the same incarnation of the CDB.

DBAs can follow the process of restore and recovery using the V$SESSION_LONGOPS and V$RECOVERY_PROGRESS views, respectively.

For the restore, the V$SESSION_LONGOPS view’s column OPNAME should be ‘Flashback Database’. Just like this:

SELECT sofar, totalwork, units 
FROM v$session_longops 
WHERE opname = 'Flashback Database';

The column SOFAR shows the data currently read in megabytes while the column TOTALWORK shows the total number of megabytes of flashback logs that must be read.

Restore points are created with the following command:


Restore point created.

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:

– 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);
  IF (substr(username,1,1) = '"') THEN
    execute immediate 'begin dbms_utility.canonicalize(:p1,  :p2, 128); end;'
                        using IN username, OUT canon_username;
   IF NOT ora_complexity_check(password, 12, null, 1, 1, 1, null) THEN
   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;

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:


create profile DBA_PROFILE 
ORA-01031: insufficient privileges


create profile DBA_PROFILE 
Profile DBA_PROFILE created.

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.

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

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

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.

An example of the Oracle Autonomous Database Advisor

In Autonomous, DBA, Init.ora, New features, Oracle database on June 22, 2020 at 08:33

Moving the database to Oracle Autonomous Cloud is a rather simple task but how do you avoid possible issues after the migration? How do you know in advance which objects will not at all migrate to Autonomous or which objects will be migrated with some changes? How do you get beforehand advice and guidelines on the migration to Autonomous?

Note from January 13th, 2022: Cloud Premigration Advisor Tool (CPAT) Analyzes Databases for Suitability of Cloud Migration (Doc ID 2758371.1) is the new/replaced tool. The link below for the “Oracle Autonomous Database Schema Advisor” is not working and the note is missing. 

The new tool called “Oracle Autonomous Database Schema Advisor” can give you the answers to these questions.

All the details are in MOS Doc ID 2462677.1 but here are the basics and an example in which I will be moving my 20c schema to ATP Dedicated.

First, you need to install the advisor. Meaning run the script install_adb_advisor.sql (as sysdba) which you download from the MOS note above. The script will create a user, 7 tables, 4 indexes and a package. I have decided to call the user adb_advisor and the password will be tiger:

[oracle@julian ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release - Production on Thu Jun 18 07:24:31 2020

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

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

SQL&gt; @install_adb_advisor.sql adb_advisor tiger


Index created.
Package created.
Package body created.
No errors.

Second, you run the advisor as the user created in step one. What you need to specify is (1) the database schemas and (2) the ADB type you will be using in the Autonomous Cloud. You can list maximum 30 schemas in a single advisor run or just use schemas=>’ALL’. And these are the 4 options for the ADB type:

– ATP for Autonomous Transaction Processing (Serverless)
– ADW for Autonomous Data Warehouse (Serverless)
– ATPD for Autonomous Transaction Processing (Dedicated)
– ADWD for Autonomous Data Warehouse (Dedicated)

Do not try to run the script as SYSDBA, you will be getting all sorts of errors (I found it the hard way), something like:

ORA-06598: insufficient INHERIT PRIVILEGES privilege
PLS-00201: identifier ‘ADB_ADVISOR.REPORT’ must be declared
ORA-00942: table or view does not exist

If the package complies with a loop index error and ORA-00942, just run GRANT SELECT ON DBA_XML_TABLES TO ADB_ADVISOR; and recompile.

The Advisor will generate a report with the following information:

– The counts of discovered objects and a summary of migration status
– Objects that cannot be migrated due to the restrictions and lockdowns imposed by the Autonomous Database on certain data types, database options and SQL statements
– The objects that will migrate with modifications that are automatically made during the import process or upon the execution of object creation DDL
– Informational section containing certain best practice recommendations and guidance

Here is the output after I ran ADB_ADVISOR.REPORT:

exec ADB_ADVISOR.REPORT(schemas=&gt;'ALL', adb_type=&gt;'ATPD');
PL/SQL procedure successfully completed.


ADB Advisor Version   :
Instance Name         : DB0223
Database Name         : DB0223
Host Name             : julian
Database Version      :
Pluggable Database    : NOVOPDB1
Schemas Analyzed      : PDBADMIN,PDBUSER,JULIAN
Analyzing for         : Autonomous Transaction Processing (Dedicated)
Report Start date/time: 18-JUN-2020 07:47


                                           Objects         Objects         Total
                           Object          Will Not        Will Migrate    Objects
Object Type                Count           Migrate         With Changes    Will Migrate
-------------------------  --------------  --------------  --------------  --------------
CONSTRAINT                 8               0               0               8
INDEX                      4               0               0               4
INDEX PARTITION            4               0               0               4
TABLE                      8               0               2               8
TABLE PARTITION            4               0               0               4
User Objects in SYS        87              87              0               0
User Objects in SYSTEM     0               0               0               0             


1) User-defined objects in SYS schema will not migrate (Count=87):
Note: User-defined objects were detected in SYS schema. Consider moving them out of SYS prior to migration.

Owner      Object Type                    Object Name
---------- ------------------------------ ----------------------------------------
SYS        INDEX                          SYS_C008044
SYS        TABLE                          AQ_SRVNTFN_TABLE_1
SYS        JOB                            ORA$_ATSK_AUTOZM
SYS        TABLE                          WRI$_ADV_OBJSPACE_TREND_DATA
SYS        TABLE                          WRI$_ADV_OBJSPACE_CHROW_DATA
SYS        TABLE                          WRI$_ADV_SEGADV_SEGROW                  


1) Index Organized table will be created as regular table (Count=1):
Note: Index Organized tables are disallowed in ADB. When you create
an IOT in ADB, the table gets created as non-IOT (regular table).
When the Data Pump export file contains tables with IOT, use
'dwcs_cvt_iots:y' transformation at import time to transform IOTs
as regular tables.


2) INMEMORY Tables will be created as NO INMEMORY Tables (Count=1):
Note: Database In-Memory is not enabled in ADB. All In-Memory
tables and partitions will be created with NO INMEMORY clause.



1) Database Parameters are detected as modified in the current database but can't be modified in the ADB (Count=13):
Note: The following init parameters are modified in your database
that you would not be able to modify in ADB. Please refer to the
Oracle Autonomous Database documentation on the parameters that
you are allowed to modify.


Report End Datetime   : 18-JUN-2020 07:47
Report Runtime        : +000000000 03:00:02.125000000

So, I am facing based on the report above the following 4 issues:

– I have user-defined objects in the SYS schema. They will not be migrated.
– My IOT table cannot be migrated as-is. It will be migrated as an normal, regular table.
– My INMEMORY table cannot be migrated as-is. It will be created as a NO INMEMORY table.
– I cannot use some of my init.ora parameters in ADB-D.

My recommendation is that, before you migrate your schemas to Autonomous Cloud, to run the advisor. It will minimize your post-migration hassle.

Few additional comments about the advisor:

By default, the output gets truncated when the number of rows exceeds the maximum limit set in the Advisor package. You can reset the number of rows by running the following command prior to running the Advisor.

SQL&gt; exec ADB_ADVISOR.setmaxrows(500);

You have to reset the max rows every time you run the Advsior as the settings is not saved in the database.

If you want to query the data dictionary for the output, you may try:

SELECT a.owner, a.object_type, a.object_name, c.*
FROM adb_advisor_objects_tmp a,
adb_advisor_rejects_tmp b,
adb_advisor_codes_tmp c

Final note: you can run the advisor on any database version from 10g to 20c!