Archive for the ‘Autonomous’ Category

SQL Trace and X-ADG in the Oracle Autonomous Database

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

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

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

Here is how to enable and use them:

SQL Trace in ADB:

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

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

    credential_name => 'JULIANDON_CREDENTIAL',
    username => 'oracleidentitycloudservice/', 
    password => 'generated_token'

PL/SQL procedure successfully completed.

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


Database altered.

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


Database altered.

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

exec DBMS_SESSION.SET_IDENTIFIER('sqltrace_jd');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.


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


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


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


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


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

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

Cross-Region Autonomous Data Guard in ADB-S

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

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

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

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

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

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

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

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

Simple and elegant!

The Oracle Cloud Infrastructure Mobile App

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

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

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

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

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

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

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

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

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

Autonomous Robots need Autonomous Databases or Top 100 Automatic Features of the Oracle Database

In Autonomous, Databases, DBA, Oracle database on February 13, 2021 at 17:34

One of the very few words in the English language originating from Slavic languages is the word robot. It comes from an Old Church Slavonic word rabota which means (servitude of forced) labor/work.

100 years ago, in 1921, Karel Čapek’s play RUR introduced the word “robot” to science fiction. And for the Oracle DBAs: he itroduced just the robots not the “Release Update Revisions” 🙂

In a recent paper called “A Realtime Autonomous Robot Navigation Framework for Human like High-level Interaction and Task Planning in Global Dynamic Environment“, the authors proposed the self-driving technology framework to perform high-level interactions and human-like tasks:

You can see the overview of the proposed framework: a robot-centric on-demand database and a working memory are used for autonomous navigation, behavior planner, and learning. The robot can download database from network or cloud if necessary.

Yet in another research paper called “Autonomous robot system architecture for automation of structural health monitoring“, the proposed system uses an autonomous robot, database and the proposed architecture is used to integrate all sub-systems for the automation of the Structural Health Monitoring.

For such Autonomous Robots, we need also Autonomous Databases.

Here is a new Guide to the Oracle’s self-driving database and you can see below Gartner’s rankings where Oracle Autonomous Database is on top of every list but the aim of this post is not to compare Oracle Autonomous Database with other databases but to list all 100 automation/autonomous features of the Oracle database. First have a look at what is new with the latest release Oracle 21c:

– Native Blockchain Tables
– Automatic Index Optimization
– Automatic Zone Maps
– Automatic SQL Tuning Sets
– Automatic Indexing Enhancements
– Autonomous Health Framework and Object Activity Tracking System
– Automatic Materialized Views
– Auto-Result Cache

And here are the Top 100 features (in no order whatsoever) that make Oracle database “The Autonomous Database”:

1. Automatic Storage Management (ASM)
2. Automatic Workload Repository (AWR)
3. Automatic Data Optimization (ADO)
4. Automatic Database Diagnostic Monitor (ADDM)
5. Automatic Space Segment Management (ASSM)
6. Automatic Diagnostic Repository (ADR)
7. Automatic In-Memory
8. Automatic Big Table Caching
9. Automatic Degree of Parallelism
10. Automatic Index Optimization
11. Automatic Indexing
12. Automatic Materialized Views
13. Automatic SQL Plan Management
14. Automatic SQL Tuning Set
15. Automatic Zone Maps
16. Autonomous Health Framework
17. Automatic Memory Management
18. Automatic Undo Management
19. Automatic Undo Retention Tuning
20. Automatic Shared Memory Management
21. Automatic PGA Memory Management
22. Automatic Plan Capture with SPM
23. Automatic Refresh of Materialized Views
24. Automatic VIP failback
25. Automatic AWR PDB Autoflush
26. Automatic Segment Advisor
27. Automatic Tablespace Point In Time Recovery
28. Automatic Outage Resolution with Oracle Data Guard
29. Automatic Statistics Gathering
30. Automatically Created Storage Indexes in Exadata
31. Automates Database Upgrades with AutoUpgrade
32. Auto-List Partitioning
33. Automating Diagnostic Collection for Trace File Analyzer
34. Oracle GoldenGate Automatic CDR
35. Automatically Enabled Resource Manager for Database In-Memory
36. Automatic Data Corruption Repair
37. Automatic Oracle restart
38. Automated tracking: Object Activity Tracking System (OATS)
39. Automatic Storage Management Cluster File System
40. SQL*Net Auto-Detection of Support for Out-of-Band Breaks
41. Automatic Service Registration
42. Automatic Purging of Workspaces
43. Automatic DBCA creation of database management policy for RAC
44. Automatic Storage Management Cluster File System (Oracle ACFS) Snapshots
45. Oracle Data Guard Broker Support for Multiple Automatic Failover Targets
46. ACFS Auto-Resize
47. Automatic Background Index Maintenance for Oracle Text
48. Automatically Maintained Standby Databases
49. Automatically Enabling Oracle Database Quality of Service (QoS) Management
50. Automatic Collection of Dynamic Statistics
51. Automatically Synchronize Password Files in Oracle Data Guard Configurations
52. Automatic Data Optimization (ADO) for Hybrid Columnar Compression (HCC)
53. Automatic Locking of Inactive User Accounts
54. Automatic Management of Near Real-Time Indexes
55. Heat Map and Automatic Data Optimization Support for CDBs
56. High-Frequency Automatic Optimizer Statistics Collection
57. Automatic Storage Management (ASM) File Access Control
58. Automatic Support for Both SASL and Non-SASL Active Directory Connections
59. Automatic KDC Discovery When Configuring OCI Clients
60. Automatic Column Group Detection
61. Automatic CDR
62. AutoShrink for ACFS
63. Auto-Tuning Oracle C Client Interface (OCI) Applications
64. Automated Transaction Draining for Oracle Grid Infrastructure Upgrades
65. Automatic CDR Support of Tables with Unique Indexes/Constraints
66. Automatic Real-Time statistics
67. Automatic Correction of Non-logged Blocks at a Data Guard Standby Database
68. Automatic Disaster Recovery Fails with RMAN
69. Automatic Channel Failover
70. Automatic Synchronization of Oracle Text Index
71. Automatic Registration of the Database with the Default Listener
72. Automatic Offlining of the Datafile in Noarchivelog
73. Automatic Datatype Conversion
74. Automatic Tape Drive Cleaning and a Cleaning Tape in a Tape Library
75. Automatic Controlfile Backup
76. Automatic Eject Of Tape After Backup Using Oracle Secure Backup
77. Automatic BackupSet Failover On Missing or Corrupt BackupPieces
78. Automatic BMR (Block Media Recovery)
79. Automatic System Tasks
80. Automatic Database Performance Monitoring
81. Automatic Archiving
82. Automatic Propagation in Replication
83. Automatic Job Scheduling
84. Automatic Resume of DataPump
85. Automatic (Real-Time) ADDM Trigger Control
86. Automatic Identify and Allocate Compute & Storage Resources for ADB
87. Automatic Install and Configure Database Software for ADB
88. Automatic Configure Oracle Grid Infrastructure for ADB
89. Automatic Configure Oracle Real Application Clusters for ADB
90. Automatic System & Storage Optimization for ADB
91. Automatically Derived Settings for ADB
92. Automatic Scripted Scaling for ADB
93. Automatic Health Check for ADB
94. Automatic Updates for ADB
95. Automatic Schema Level Optimization for ADB
96. Fault Detection & Resolution for ADB
97. Automatic Security Updates for ADB
98. Automatic Secondary Indexing for ADB
99. Automatic Database Encryption ADB
100. Automatic Database Backups for ADB

Note please: these are the Top 100 on my list – there are much more!

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.

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.

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.

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?

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> @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=>'ALL', adb_type=>'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        EVALUATION CONTEXT             AQ$_KUPC$DATAPUMP_QUETAB_1_V            
SYS        MATERIALIZED ZONEMAP           RDBMS_ZMAP                              
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> 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!