Dontcheff

Archive for the ‘Autonomous’ Category

Automatic Segment Advisor in the Oracle Database

In Autonomous, Database tuning, DBA, Oracle database, Oracle internals on July 1, 2022 at 06:50

“From my experience, the best advisors help in three ways: encourage you to look at the problem or opportunity from multiple angles; help you balance the tug of the short-term with important long-term priorities; and ask the tough questions you need to know to reach the best solution.” Margo Georgiadis

The Oracle Segment Advisor identifies segments that have space which can be reclaimed. However, the Automatic Segment Advisor can be at times resource consuming and even slow down your database:

Why is this happening granted the Automatic Segment Advisor does not analyze every database object? Here is how it works internally: the advisor examines the database statistics, it samples segment data, and then selects the following objects to analyze:

  • Tablespaces that have exceeded a critical or warning space threshold
  • Segments that have the most activity
  • Segments that have the highest growth rate

In addition, the Automatic Segment Advisor evaluates tables that are at least 10MB in size and have at least 3 indexes to determine the amount of space saved if the tables are compressed with advanced row compression.

Now, here is the important part: if a database object is selected for analysis by the advisor but the maintenance window expires before the advisor can process the object, the object is included in the next Automatic Segment Advisor run. So, at one point you may come to a situation where lots of objects have to be analyzed. During the maintenance window, the following clients/task are being run, these are the predefined automated maintenance tasks:

Sometimes, it makes sense to disable the auto space advisor as you cannot change the set of tablespaces and segments that the Automatic Segment Advisor selects for analysis. You can, however, enable or disable the Automatic Segment Advisor task, change the times during which the Automatic Segment Advisor is scheduled to run, or adjust automated maintenance task system resource utilization. Especially in a situation like this:

Often, also ADDM may point out to the problem with the Automatic Segment Advisor task:

Here is how to disable (and enable) the tasks individually. The main switch is controlled by DBMS_AUTO_TASK_ADMIN.DISABLE().

Even after disabling the entire autotask job by running DBMS_AUTO_TASK_ADMIN.DISABLE(), the DBA_AUTOTASK_TASK will still show autotask client as enabled. For this you need to disable the jobs individually as shown above. And for a multitenant environment, CDB and PDBs have their own autotasks, disabling CDB’s autotask will not affect the PDBs, so you will have to do for each pluggable database.

Staring with 12.2, there is a parameter called ENABLE_AUTOMATIC_MAINTENANCE_PDB that can be used to enable or disable the running of automated maintenance tasks for all the PDBs in a CDB or for individual PDBs in a CDB. Changing ENABLE_AUTOMATIC_MAINTENANCE_PDB in the CDB root from TRUE to FALSE, the new value FALSE takes effect in the root and in all the PDBs in the CDB.

If you get into a situation where the Automatic Segment Advisor is consuming lots of resource and slowing the database during the maintenance windows do one of the following:

  1. Disable the autotask client for the segment advisor and run it manually on per need basis.
  2. Increase the maintenance window from the default which starts at 10 p.m. on Monday to Friday and ends at 2 a.m. Often 4 hours in just not enough. The weekend window is 20h long and in most cases long enough.

In the autonomous database, you have access to dba_autotask_client, etc. and you can disable and enable the auto space advisor task however you do not have full visibility on dba_scheduler_window_groups, etc. Some columns just show as NULL. Still the performance task in ADB can give you some idea of what is going on:

And finally, here are some other situations that might require disabling the Auto Space Advisor Job:

  • ORA-01555 While Running Auto Space Advisor Job on Object wri$_adv_objspace_trend_data (Doc ID 2576430.1)
  • Auto Space Advisor is Taking More Time due to Recursive Query Taking a Long Time (Doc ID 2382419.1)
  • SEGMENT ADVISOR not working as expected for LOB or SYS_LOB SEGMENT (Doc ID 988744.1)

The new CS_SESSION package and DB_NOTIFICATIONS view in the Autonomous Database

In Autonomous, DBA, Oracle database on January 20, 2022 at 09:19

Two new objects have been recently introduced in the Oracle Autonomous Database on Shared Exadata Infrastructure.

1. The first one is the CS_SESSION package.

When you open a connection to the Autonomous Database, that session is assigned a consumer group. But consumer groups affect the concurrency and the DOP (degree of parallelism). HIGH service is run in parallel while LOW service runs in serial. So for example, if you have a PL/SQL procedure or function that has to execute statements in the same session with changing consumer groups, the way is to use the new CS_SESSION.SERVICE_NAME procedure.

The number of concurrent statements run in the database depends on 2 factors: the type of the service name and the numbers of the Oracle CPUs provisioned for the ADB:

The new package CS_SESSION contains only one procedure: SERVICE_NAME. My current version is 19.14, I am not sure if the package/procedure is included in all lower RUs. Most likely it is.

The valid values accepted as an IN parameter are: HIGHMEDIUMLOWTP and TPURGENT.

After running the procedure, Oracle does not reset any session attributes. Anything the user set for their session before calling this procedure will continue as-is. 

The ADMIN user is granted EXECUTE privilege on CS_SESSION with GRANT OPTION. The privilege is also granted to DWROLE without the GRANT OPTION.

Here is how to change the consumer group from LOW to TPURGENT:

You might get the following error:

ORA-02097: parameter cannot be modified because specified value is invalid ORA-01031: insufficient privileges ORA-06512: at “C##CLOUD$SERVICE.CS_SESSION”, line 142

Note there is an open bug for this.

You can create an AFTER SET CONTAINER trigger if you would like to limit the values a user can set for the consumer group session. You might not want that all users can set to TPURGENT but allow them go with TP only.

2. The second new object is the DB_NOTIFICATIONS view which stores information about maintenance status notifications and timezone version upgrade notifications for the ADB instance. Let us describe the view

and check what is in it:

As we can see there was a patch run on the data dictionary and there is another maintenance scheduled for the 19th of January.

3. Good to know that we can now use GitHub Raw URLs with DBMS_CLOUD APIs to access source files that reside on a GitHub Repository.

4. Also, the DBMS_PIPE package is now available in the Autonomous Database.

Moving Autonomous Databases across regions

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

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

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

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

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

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

Another option is simply to create a clone:

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

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

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

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

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

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

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

Operating System access from within the Autonomous Database

In Autonomous, DBA, Oracle database, OS on December 1, 2021 at 09:47

Oracle Autonomous Database does not let us access the operating system. Not even DBA access is allowed.

So, how can we view the content of ADB log or trace files from the OS? How about the alert.log file? “Not needed as all is autonomous” is the common answer. But that is not good enough for most DBAs.

Since September 2021, SQL Trace is supported in the Autonomous Database. If DBAs enable SQL Trace, the same tracing information 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.

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.

But how about other trace files? It is still possible via Oracle’s internal views. In fact, we don’t have access directly to the V_$ views but rather to their V$ synonyms.

Full access is available to V$DIAG_OPT_TRACE_RECORDS, V$DIAG_SQL_TRACE_RECORDS, V$DIAG_SESS_SQL_TRACE_RECORDS and V$DIAG_SESS_OPT_TRACE_RECORDS.

The names of the trace files and their content is visible through V$DIAG_TRACE_FILE_CONTENTS:

We can see the contents of a a given trace file, say eqp12pod2_p009_193404.trc. The file has 253 lines, so here is a screenshot from the top of the file content:

We can view the latest trace files generated by the database from V$DIAG_TRACE_FILE:

Here are a couple of blog post and articles related to the topic:

How about the alert.log file? The content is in X$DBGALERTEXT:

select to_char(ORIGINATING_TIMESTAMP,'YYYYMMDD-HH24:MI:SS'), MESSAGE_TEXT
from X$DBGALERTEXT
order by RECORD_ID;

However, in ADB, we do not have access to X$DBGALERTEXT. Good news is that we have access to V$DIAG_ALERT_EXT which shows the contents of the XML-based alert log in the Automatic Diagnostic Repository (ADR) for the current container (PDB). V$DIAG_ALERT_EXT which came in 12.2 is sort of a subset of X$DBGALERTEXT. Here is how you can search for ORA- errors. Use:

SELECT ORIGINATING_TIMESTAMP, MESSAGE_LEVEL, MESSAGE_TEXT, PROBLEM_KEY
FROM V$DIAG_ALERT_EXT
WHERE MESSAGE_TEXT LIKE '%ORA-%' AND ORIGINATING_TIMESTAMP > sysdate-7
ORDER BY ORIGINATING_TIMESTAMP DESC;

to find the ORA- errors during the past week.

Possible level message values are:

1: CRITICAL: critical errors
2: SEVERE: severe errors
8: IMPORTANT: important message
16: NORMAL: normal message

And here is how to see the top of the alert.log file content from the last 24 hours:

SELECT to_char(ORIGINATING_TIMESTAMP,'DD.MM.YYYY-HH24:MI:SS')||': '||MESSAGE_TEXT as "alert.log"
FROM V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate-1
ORDER BY ORIGINATING_TIMESTAMP DESC;

Regardless of the fact that we do not have server OS access, we can still create directories from within the Autonomous Database.

CREATE DIRECTORY creates the database directory object and also creates the file system directory if it does not already exist. If the file system directory exists then CREATE DIRECTORY only creates the database directory object.

It is possible also to create subdirectories. For example, the following command creates the database directory object version_patches and the file system directory version/patches:

CREATE DIRECTORY version_patches AS 'version/patches;

You can list the contents of a directory in the Autonomous Database by using the function DBMS_CLOUD.LIST_FILES.

The following query lists the contents of the VERSION directory:

SELECT * FROM DBMS_CLOUD.LIST_FILES('VERSION');

You can copy Files Between Object Store and a Directory in Autonomous Database.

Starting with Oracle Database 21c, Data Pump can perform exports from Oracle Autonomous Database into dump files in a cloud object store.

Thus, now we can easily migrate data out from an Oracle Autonomous Database and import it into another location.

Here is an example assuming that we have already created the credential_name JMD_OBJ_STORE_CRED:

Details on how to create the credentials can be found in the Oracle Cloud Infrastructure User Guide which has “only” 5952 pages!

Beware Bug 33323028 – DATA PUMP EXPORT TO OCI OBJECT STORAGE FAILS – present even in 21.4 – Object store ODM Library is not enabled by default – How To Setup And Use DBMS_CLOUD Package (Doc ID 2748362.1)

Conclusion: as you can see the OS access is close to zero. There are very few views that let us see what is residing on the the operating system. What I like most is (1) the possibility to run SQL Trace, (2) being able to view the contents of the XML-based alert log and (3) the ability to run an export directly from object store (21c and above). I would like to see access to X$DBGALERTEXT.

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

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

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

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

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

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

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

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

The following below are typical SODA commands:

(1) list the collections

(2) create the EMP collection

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Trace and X-ADG in the Oracle Autonomous Database

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

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

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

Here is how to enable and use them:

SQL Trace in ADB:

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

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

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

PL/SQL procedure successfully completed.

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

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

Database altered.

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

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

Database altered.

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

exec DBMS_SESSION.SET_IDENTIFIER('sqltrace_jd');

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

ALTER SESSION SET SQL_TRACE = TRUE;

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

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

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

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

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

SELECT trace FROM SESSION_CLOUD_TRACE ORDER BY row_number;

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

DESC SESSION_CLOUD_TRACE

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

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

Cross-Region Autonomous Data Guard in ADB-S

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

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

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

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

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

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

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

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

Simple and elegant!

The Oracle Cloud Infrastructure Mobile App

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

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

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

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

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

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

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

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

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

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:

exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE','NDA_DOCS',TRUE);

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

exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE','NDA_DOCS',FALSE);

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

exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE','NDA_DOCS',NULL);

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:

exec DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_TABLE',NULL,TRUE);

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.
Cause
Action

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 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> exec DBMS_CONNECTION_POOL.START_POOL();
PL/SQL procedure successfully completed.

SQL>  exec DBMS_CONNECTION_POOL.STOP_POOL();
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:

CPU_COUNT * 20
SESSIONS / 4

– 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/xxxxx.oraclevcn.com dbsat_output

Database Security Assessment Tool version 2.2.1 (May 2020)

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

Connecting to the target Oracle database...

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

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

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

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

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

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

4. Generate the report:

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

Database Security Assessment Tool version 2.2.1 (May 2020)

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

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

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

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

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

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

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

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

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

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

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

DBSAT on Youtube: