Archive for the ‘DBA’ Category

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

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

5. 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:


More on 21c? Check:

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

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/ 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 - Production on Tue Nov 10 08:35:00 2020

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

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

Setup complete.
SQL queries complete.
OS commands complete.
Disconnected from Oracle Database 20c EE High Perf Release - Production
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.

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

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:

Automatic SQL Tuning Sets (ASTS) in Oracle 19c and 20c

In Autonomous, Database tuning, DBA, Oracle database on August 27, 2020 at 09:26

A well performing SQL statement starts all of sudden to misbehave? Fixing such queries is very much embedded into Oracle Autonomous’ strategy and here is how Automatic SQL Tuning Sets help.

What is the Automatic SQL Tuning Set? – Nigel Bayliss explains it all in a short informative blog post where he describes it as a system-maintained SQL tuning set that is an incredibly useful source of historic SQL performance metrics and execution plans. You can use ASTS to repair SQL performance regressions very quickly using SQL plan management.

An example of how to fix a SQL statement using SQL Plan management can be found in a previous post of Nigel entitled Repairing SQL Performance Regression with SQL Plan Management. Check this github example or who to “fix a misbehaving SQL query”.

Automatic SQL Tuning Sets were introduced in Oracle 19.7 where they were enabled by default. In 19.8 and 20c, they are disabled by default and MOS Doc ID 2686869.1 gives examples on how to enable and disable them:

Client_Name => 'Auto STS Capture Task',
Operation => NULL,
Window_name => NULL);

You can check if the task is disabled or enabled by running:

SELECT Task_Name, Enabled 
WHERE Task_Name = 'Auto STS Capture Task';

The MOS note above shows also how to check the amount of space consumed by all SQL tuning sets and also the statement counts per SQL tuning set:

Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS'), count(*)
FROM DBA_SQLSet_Statements

Oracle recommends that you enable ASTS and one reason for this is that Automatic Indexing relies on the ASTS.

The view DBA_SQLSET_STATEMENTS from the query above displays information about the SQL statements, along with their statistics that form all SQL tuning sets in the database. Worth keeping it in mind. Franck Pachot showed interesting screenshots on the topic.

In 20c, the data dictionary views related to ASTS changed a bit, so the queries form 19c will not work. Oracle moved it to DBA_AUTOTASK_SETTINGS.

The 20c run the following:

SELECT Task_Name, Enabled, Interval Task_Interval_in_Seconds
WHERE Task_Name = 'Auto STS Capture Task';

You can check details about the status of the task by running:

SELECT Task_Name, Status
WHERE Task_Name = 'Auto STS Capture Task';

Do not worry that Oracle captures a lot of SQL statements – that is the idea after all as you improve the likelihood that you can repair queries that previously ran very quickly and did not show up in AWR. SQL statement performance regressions are repaired automatically and the automatic mode is enabled by setting the DBMS_SPM parameter ALTERNATE_PLAN_BASELINE to AUTO on supported platforms and it is currently equivalent to “AUTOMATIC_WORKLOAD_REPOSITORY+CURSOR_CACHE+SQL_TUNING_SET”.

Check also Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0 by Mike Dietrich.

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.