Dontcheff

5 new core DBA features in Oracle Database 21c

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

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

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

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

2. SYSLOG destination for common Unified Audit Policies:

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

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

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

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

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

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

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

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

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

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

select ACTION, count(*) 
from V_$SQLAREA_PLAN_HASH 
where module = 'SYS_AUTO_STS_MODULE' 
group by ACTION;

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

4. DBMS_FLASHBACK_ARCHIVE_MIGRATE

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

The package has 3 procedures:

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

Here are the prerequisites:

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

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

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

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

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

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

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

ACTIVITY_INTERVAL defines the interval between snapshots:

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

ACTIVITY_RETENTION_DAYS defines how long snapshots are saved:

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

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

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

And use these tables to monitor the activity:

ACTIVITY_CONFIG$
ACTIVITY_TABLE$
ACTIVITY_MVIEW$
ACTIVITY_SNAP_META$

More on 21c? Check:

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

A glimpse of what is new in Oracle Database 21c

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

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

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

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

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

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

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: