Using Pandas and Mathplotlib with Oracle database

 Install the following packages if not yet

• pip install pandas
• pip install SQLAlchemy
• pip install cx_Oracle
• pip install matplotlib

import pandas as pd
import cx_Oracle
import sqlalchemy
from sqlalchemy.exc import SQLAlchemyError
try:
   engine = sqlalchemy.create_engine("oracle+cx_oracle://donghua:password@ol8.oci.net/?service_name=pdb1", arraysize=1000)
   employees_sql = """SELECT * FROM hr.employees"""; 
   df_employees= pd.read_sql(employees_sql, engine)
   departments_sql = """SELECT * FROM hr.departments"""
   df_departments = pd.read_sql(departments_sql, engine) 
except SQLAlchemyError as e:
   print(e)div>

Transform the pandas data frame

df_emp_deprt = df_employees.merge(df_departments)

df_emp_deprt.head(5)

df_emp = df_emp_deprt[['employee_id','job_id','salary','department_name']]

df_salary_by_job_id = df_emp[['job_id','salary']].groupby(['job_id'],as_index=False).mean().rename(columns={'salary':'avg_salary'}

df_salary_by_job_id.head(5)

df_salary_by_job_id_sorted = df_salary_by_job_id.sort_values(['avg_salary'], ascending=False)

df_salary_by_job_id_sorted

ax = df_salary_by_job_id_sorted.plot.bar(x='job_id', y='avg_salary', rot=90, title="Average Salary Per Title")


import matplotlib.pyplot as plt
fig, axs = plt.subplots(figsize=(10, 7)) 
df_salary_by_job_id_sorted.plot.bar(x='job_id', y='avg_salary', ylim=[0,20000],\
     ax=axs, rot=90, legend=True,title="Average Salary Per Title")
axs.set_ylabel('Average Salary')
axs.set_xlabel('Job ID')
#plt.tight_layout()
#plt.show()
# Add series data on top of the bar
for i, v in enumerate(df_salary_by_job_id_sorted['avg_salary']):
    axs.text(i, v+300, '{:.0f}'.format(v),ha='center', va='bottom')
fig.savefig("/tmp/df_salary_by_job_id_sorted.png",bbox_inches='tight')     

Blockchain table works for 19.10 RU with patch 32431413 and compatible 19.10.0

[oracle@ol8 32431413]$ $ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed |grep 32431413
32431413   32431413  Sat Jan 30 12:17:07 SGT 2021   19.10 RU FOR ORACLE IS MISSING QCPLK.O WHICH GETS

[oracle@ol8 32431413]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 12:19:49 2021
Version 19.10.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
  2  NO DROP UNTIL 25 DAYS IDLE
  3  NO DELETE UNTIL 31 DAYS AFTER INSERT
  4  HASHING USING "SHA2_512" VERSION v1;
CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
*
ERROR at line 1:
ORA-05728: COMPATIBLE needs to be 19.10.0.0.0 or higher to use blockchain table
ORA-00722: Feature "Blockchain table"

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      19.0.0
noncdb_compatible                    boolean     FALSE

SQL> alter system set compatible='19.10.0' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 5368705904 bytes
Fixed Size                  9192304 bytes
Variable Size            3036676096 bytes
Database Buffers          167772160 bytes
Redo Buffers                7581696 bytes
In-Memory Area           2147483648 bytes
Database mounted.
Database opened.

SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
  2  NO DROP UNTIL 25 DAYS IDLE
  3  NO DELETE UNTIL 31 DAYS AFTER INSERT
  4  HASHING USING "SHA2_512" VERSION v1;
CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
*
ERROR at line 1:
ORA-05729: blockchain table cannot be created in root container

SQL> conn donghua@pdb1
Enter password:
Connected.
SQL> CREATE BLOCKCHAIN TABLE ledger_tab1 (bank VARCHAR2(128), d_date DATE, d_amount NUMBER)
  2  NO DROP UNTIL 25 DAYS IDLE
  3  NO DELETE UNTIL 31 DAYS AFTER INSERT
  4  HASHING USING "SHA2_512" VERSION v1;

Table created.

SQL> insert into ledger_tab1 values('dbs',sysdate,100);

1 row created.

SQL> update ledger_tab1 set d_amout=200 where bank='dbs';
update ledger_tab1 set d_amout=200 where bank='dbs'
                       *
ERROR at line 1:
ORA-00904: "D_AMOUT": invalid identifier


SQL> update ledger_tab1 set d_amount=200 where bank='dbs';
update ledger_tab1 set d_amount=200 where bank='dbs'
       *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> delete from ledger_tab1;
delete from ledger_tab1
            *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> truncate table ledger_tab1;
truncate table ledger_tab1
               *
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table


SQL> drop table ledger_tab1;
drop table ledger_tab1
           *
ERROR at line 1:
ORA-05723: drop blockchain table LEDGER_TAB1 not allowed

SQL> set long 2000 longc 2000
SQL> select dbms_metadata.get_ddl('TABLE','LEDGER_TAB1') from dual;

DBMS_METADATA.GET_DDL('TABLE','LEDGER_TAB1')
--------------------------------------------------------------------------------

  CREATE TABLE "DONGHUA"."LEDGER_TAB1"
   (    "BANK" VARCHAR2(128),
        "D_DATE" DATE,
        "D_AMOUNT" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

SYSAUX Tablespace Occupants in Vanilla Oracle 19c database

 [oracle@ol8 ~]$ sql / as sysdba

SQLcl: Release 20.3 Production on Sun Jan 24 20:23:49 2021

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0


SQL> set sqlformat json-formatted
SQL> select * from v$sysaux_occupants order by space_usage_kbytes desc;
{
  "results" : [
    {
      "columns" : [
        {
          "name" : "OCCUPANT_NAME",
          "type" : "VARCHAR2"
        },
        {
          "name" : "OCCUPANT_DESC",
          "type" : "VARCHAR2"
        },
        {
          "name" : "SCHEMA_NAME",
          "type" : "VARCHAR2"
        },
        {
          "name" : "MOVE_PROCEDURE",
          "type" : "VARCHAR2"
        },
        {
          "name" : "MOVE_PROCEDURE_DESC",
          "type" : "VARCHAR2"
        },
        {
          "name" : "SPACE_USAGE_KBYTES",
          "type" : "NUMBER"
        },
        {
          "name" : "CON_ID",
          "type" : "NUMBER"
        }
      ],
      "items" : [
        {
          "occupant_name" : "SDO",
          "occupant_desc" : "Oracle Spatial",
          "schema_name" : "MDSYS",
          "move_procedure" : "MDSYS.MOVE_SDO",
          "move_procedure_desc" : "Move Procedure for Oracle Spatial",
          "space_usage_kbytes" : 238464,
          "con_id" : 1
        },
        {
          "occupant_name" : "SM/AWR",
          "occupant_desc" : "Server Manageability - Automatic Workload Repository",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 194112,
          "con_id" : 1
        },
        {
          "occupant_name" : "XDB",
          "occupant_desc" : "XDB",
          "schema_name" : "XDB",
          "move_procedure" : "XDB.DBMS_XDB_ADMIN.MOVEXDB_TABLESPACE",
          "move_procedure_desc" : "Move Procedure for XDB",
          "space_usage_kbytes" : 70016,
          "con_id" : 1
        },
        {
          "occupant_name" : "SM/OTHER",
          "occupant_desc" : "Server Manageability - Other Components",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 67392,
          "con_id" : 1
        },
        {
          "occupant_name" : "AO",
          "occupant_desc" : "Analytical Workspace Object Table",
          "schema_name" : "SYS",
          "move_procedure" : "DBMS_AW.MOVE_AWMETA",
          "move_procedure_desc" : "Move Procedure for Analytical Workspace Object Table",
          "space_usage_kbytes" : 46912,
          "con_id" : 1
        },
        {
          "occupant_name" : "SM/OPTSTAT",
          "occupant_desc" : "Server Manageability - Optimizer Statistics History",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 31616,
          "con_id" : 1
        },
        {
          "occupant_name" : "SM/ADVISOR",
          "occupant_desc" : "Server Manageability - Advisor Framework",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 11072,
          "con_id" : 1
        },
        {
          "occupant_name" : "LOGMNR",
          "occupant_desc" : "LogMiner",
          "schema_name" : "SYSTEM",
          "move_procedure" : "SYS.DBMS_LOGMNR_D.SET_TABLESPACE",
          "move_procedure_desc" : "Move Procedure for LogMiner",
          "space_usage_kbytes" : 11072,
          "con_id" : 1
        },
        {
          "occupant_name" : "WM",
          "occupant_desc" : "Workspace Manager",
          "schema_name" : "WMSYS",
          "move_procedure" : "DBMS_WM.move_proc",
          "move_procedure_desc" : "Move Procedure for Workspace Manager",
          "space_usage_kbytes" : 6720,
          "con_id" : 1
        },
        {
          "occupant_name" : "AUDSYS",
          "occupant_desc" : "AUDSYS schema objects",
          "schema_name" : "AUDSYS",
          "move_procedure" : "DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION",
          "move_procedure_desc" : "Move Procedure for Unified Audit Trail",
          "space_usage_kbytes" : 5184,
          "con_id" : 1
        },
        {
          "occupant_name" : "TEXT",
          "occupant_desc" : "Oracle Text",
          "schema_name" : "CTXSYS",
          "move_procedure" : "DRI_MOVE_CTXSYS",
          "move_procedure_desc" : "Move Procedure for Oracle Text",
          "space_usage_kbytes" : 2880,
          "con_id" : 1
        },
        {
          "occupant_name" : "PL/SCOPE",
          "occupant_desc" : "PL/SQL Identifier Collection",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 2880,
          "con_id" : 1
        },
        {
          "occupant_name" : "SQL_MANAGEMENT_BASE",
          "occupant_desc" : "SQL Management Base Schema",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 2752,
          "con_id" : 1
        },
        {
          "occupant_name" : "SMON_SCN_TIME",
          "occupant_desc" : "Transaction Layer - SCN to TIME mapping",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 2240,
          "con_id" : 1
        },
        {
          "occupant_name" : "JOB_SCHEDULER",
          "occupant_desc" : "Unified Job Scheduler",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 2048,
          "con_id" : 1
        },
        {
          "occupant_name" : "STREAMS",
          "occupant_desc" : "Oracle Streams",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 1728,
          "con_id" : 1
        },
        {
          "occupant_name" : "LOGSTDBY",
          "occupant_desc" : "Logical Standby",
          "schema_name" : "SYSTEM",
          "move_procedure" : "SYS.DBMS_LOGSTDBY.SET_TABLESPACE",
          "move_procedure_desc" : "Move Procedure for Logical Standby",
          "space_usage_kbytes" : 1600,
          "con_id" : 1
        },
        {
          "occupant_name" : "AUTO_TASK",
          "occupant_desc" : "Automated Maintenance Tasks",
          "schema_name" : "SYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 576,
          "con_id" : 1
        },
        {
          "occupant_name" : "EM_MONITORING_USER",
          "occupant_desc" : "Enterprise Manager Monitoring User",
          "schema_name" : "DBSNMP",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 512,
          "con_id" : 1
        },
        {
          "occupant_name" : "ORDIM/ORDDATA",
          "occupant_desc" : "Oracle Multimedia ORDDATA Components",
          "schema_name" : "ORDDATA",
          "move_procedure" : "ordsys.ord_admin.move_ordim_tblspc",
          "move_procedure_desc" : "Move Procedure for Oracle Multimedia",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "ORDIM/ORDPLUGINS",
          "occupant_desc" : "Oracle Multimedia ORDPLUGINS Components",
          "schema_name" : "ORDPLUGINS",
          "move_procedure" : "ordsys.ord_admin.move_ordim_tblspc",
          "move_procedure_desc" : "Move Procedure for Oracle Multimedia",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "ORDIM/SI_INFORMTN_SCHEMA",
          "occupant_desc" : "Oracle Multimedia SI_INFORMTN_SCHEMA Components",
          "schema_name" : "SI_INFORMTN_SCHEMA",
          "move_procedure" : "ordsys.ord_admin.move_ordim_tblspc",
          "move_procedure_desc" : "Move Procedure for Oracle Multimedia",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "EM",
          "occupant_desc" : "Enterprise Manager Repository",
          "schema_name" : "SYSMAN",
          "move_procedure" : "emd_maintenance.move_em_tblspc",
          "move_procedure_desc" : "Move Procedure for Enterprise Manager Repository",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "XSOQHIST",
          "occupant_desc" : "OLAP API History Tables",
          "schema_name" : "SYS",
          "move_procedure" : "DBMS_XSOQ.OlapiMoveProc",
          "move_procedure_desc" : "Move Procedure for OLAP API History Tables",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "STATSPACK",
          "occupant_desc" : "Statspack Repository",
          "schema_name" : "PERFSTAT",
          "move_procedure" : "",
          "move_procedure_desc" : "Use export/import (see export parameter file spuexp.par)",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "ULTRASEARCH_DEMO_USER",
          "occupant_desc" : "Oracle Ultra Search Demo User",
          "schema_name" : "WK_TEST",
          "move_procedure" : "MOVE_WK",
          "move_procedure_desc" : "Move Procedure for Oracle Ultra Search",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "EXPRESSION_FILTER",
          "occupant_desc" : "Expression Filter System",
          "schema_name" : "EXFSYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "TSM",
          "occupant_desc" : "Oracle Transparent Session Migration User",
          "schema_name" : "TSMSYS",
          "move_procedure" : "",
          "move_procedure_desc" : "*** MOVE PROCEDURE NOT APPLICABLE ***",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "XSAMD",
          "occupant_desc" : "OLAP Catalog",
          "schema_name" : "OLAPSYS",
          "move_procedure" : "DBMS_AMD.Move_OLAP_Catalog",
          "move_procedure_desc" : "Move Procedure for OLAP Catalog",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "AUDIT_TABLES",
          "occupant_desc" : "DB audit tables",
          "schema_name" : "SYS",
          "move_procedure" : "DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION",
          "move_procedure_desc" : "Move Procedure for Traditional DB audit tables",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "ORDIM",
          "occupant_desc" : "Oracle Multimedia ORDSYS Components",
          "schema_name" : "ORDSYS",
          "move_procedure" : "ordsys.ord_admin.move_ordim_tblspc",
          "move_procedure_desc" : "Move Procedure for Oracle Multimedia",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        },
        {
          "occupant_name" : "ULTRASEARCH",
          "occupant_desc" : "Oracle Ultra Search",
          "schema_name" : "WKSYS",
          "move_procedure" : "MOVE_WK",
          "move_procedure_desc" : "Move Procedure for Oracle Ultra Search",
          "space_usage_kbytes" : 0,
          "con_id" : 1
        }
      ]
    }
  ]
}
32 rows selected.

Execute orachk (part of AHF 20.4)

 [oracle@ol8 ~]$ /opt/oracle.ahf/bin/tfactl access lsusers

.--------------------------------.
|        TFA Users in ol8        |
+-----------+---------+----------+
| User Name | Status  | Promoted |
+-----------+---------+----------+
| oracle    | Allowed | false    |
'-----------+---------+----------'

[oracle@ol8 ~]$ orachk


List of running databases

1. orcl
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. 1
.  .
Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

-------------------------------------------------------------------------------------------------------
                                                 Oracle Stack Status
-------------------------------------------------------------------------------------------------------
  Host Name       CRS Installed       ASM HOME  RDBMS Installed    CRS UP    ASM UP  RDBMS UP    DB Instance Name
-------------------------------------------------------------------------------------------------------
        ol8                        No           No          Yes           No       No      Yes                orcl
-------------------------------------------------------------------------------------------------------


Copying plug-ins


*** Checking Best Practice Recommendations ( Pass / Warning / Fail ) ***

Collections and audit checks log file is
/opt/oracle.ahf/data/ol8/orachk/user_oracle/output/orachk_ol8_orcl_012321_160827/log/orachk.log

============================================================
                    Node name - ol8
============================================================
. . . . . .


 Collecting - Database Parameters for orcl database
 Collecting - Database Undocumented Parameters for orcl database
 Collecting - List of active logon and logoff triggers for orcl database
 Collecting - CPU Information
 Collecting - Disk I/O Scheduler on Linux
 Collecting - DiskMount Information
 Collecting - Kernel parameters
 Collecting - Maximum number of semaphore sets on system
 Collecting - Maximum number of semaphores on system
 Collecting - Maximum number of semaphores per semaphore set
 Collecting - Memory Information
 Collecting - OS Packages
 Collecting - Operating system release information and kernel version
 Collecting - Patches for RDBMS Home
 Collecting - Table of file system defaults
 Collecting - number of semaphore operations per semop system call


Data collections completed. Checking best practices on ol8.
------------------------------------------------------------

 WARNING =>  Linux swap configuration does not meet recommendation
 WARNING =>  Package compat-libcap1-1.10-7.el7-x86_64 is recommended but not installed
 INFO =>     Most recent ADR incidents for /u01/db
 INFO =>     Oracle GoldenGate failure prevention best practices
 INFO =>     user_dump_dest has trace files older than 30 days for orcl
 INFO =>     At some times checkpoints are not being completed for orcl
 WARNING =>  Package compat-libstdc++-33-3.2.3-61-x86_64 is recommended but not installed
 WARNING =>  One or more redo log groups are not multiplexed for orcl
 WARNING =>  Primary database is not protected with Data Guard (standby database) for real-time data protection and availability for orcl
 INFO =>     Important Storage Minimum Requirements for Grid & Database Homes
 CRITICAL => Operating system hugepages count does not satisfy total SGA requirements
 FAIL =>     Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for orcl
 FAIL =>     Database parameter DB_LOST_WRITE_PROTECT is not set to recommended value on orcl instance
 INFO =>     The Optimizer Fix 28345522 is disabled by default for orcl
 WARNING =>  Database parameter DB_BLOCK_CHECKING on primary is not set to the recommended value. for orcl
 INFO =>     The Optimizer Fix 22149010 is disabled by default for orcl
 INFO =>     The Optimizer Fix 25167306 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28965084 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28776811 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29132869 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28498976 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29687220 is disabled by default for orcl
 INFO =>     The Optimizer Fix 30232638 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29930457 is disabled by default for orcl
 INFO =>     The Optimizer Fix 29304314 is disabled by default for orcl
 INFO =>     The Optimizer Fix 28776431 is disabled by default for orcl
 INFO =>     Operational Best Practices
 INFO =>     Database Consolidation Best Practices
 INFO =>     Computer failure prevention best practices
 INFO =>     Data corruption prevention best practices
 INFO =>     Logical corruption prevention best practices
 INFO =>     Database/Cluster/Site failure prevention best practices
 INFO =>     Client failover operational best practices
 WARNING =>  Oracle patch 29423227 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 31031240 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 29867728 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 26749785 is not applied on RDBMS_HOME /u01/db
 WARNING =>  Oracle patch 29259068 is not applied on RDBMS_HOME /u01/db
 INFO =>     The Optimizer Fix 30347410 is disabled by default for orcl
 INFO =>     The Optimizer Fix 27261477 is disabled by default for orcl
 WARNING =>  Redo log files should be appropriately sized for orcl
 WARNING =>  Oracle clusterware is not being used
 WARNING =>  RAC Application Cluster is not being used for database high availability on orcl instance
 WARNING =>  Flashback on PRIMARY is not configured for orcl
 INFO =>     Database failure prevention best practices
 WARNING =>  fast_start_mttr_target has NOT been changed from default on orcl instance
 FAIL =>     Active Data Guard is not configured for orcl
 INFO =>     Parallel Execution Health-Checks and Diagnostics Reports for orcl
 INFO =>     Oracle recovery manager(rman) best practices
 INFO =>     Database feature usage statistics for orcl
 WARNING =>  Consider investigating changes to the schema objects such as DDLs or new object creation for orcl
 WARNING =>  Consider increasing the value of the session_cached_cursors database parameter for orcl
 WARNING =>  Consider investigating the frequency of SGA resize operations and take corrective action for orcl
Best Practice checking completed. Checking recommended patches on ol8
--------------------------------------------------------------------------------
Collecting patch inventory on ORACLE_HOME /u01/db
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1 Recommended RDBMS patches for 190000 from /u01/db on ol8
--------------------------------------------------------------------------------
Patch#   RDBMS    ASM     type                Patch-Description
--------------------------------------------------------------------------------
 32218454yes              merge                                                                                           
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
              RDBMS homes patches summary report
--------------------------------------------------------------------------------
Total patches  Applied on RDBMS Applied on ASM ORACLE_HOME
--------------------------------------------------------------------------------
1              1              0                /u01/db
--------------------------------------------------------------------------------

------------------------------------------------------------
Detailed report (html) -  /opt/oracle.ahf/data/ol8/orachk/user_oracle/output/orachk_ol8_orcl_012321_160827/orachk_ol8_orcl_012321_160827.html

UPLOAD [if required] - /opt/oracle.ahf/data/ol8/orachk/user_oracle/output/orachk_ol8_orcl_012321_160827.zip

Check ORACLE_PATCH 29259068 for RDBMS home

Recommendation

Benefit / Impact: This would fix one of the top issues since 12.2 where automatic optimizer statistics collection job takes long time due to MERGE query on MON_MODS_ALL$. The performance of the MERGE query is now improved. Risk: Automatic optimizer statistics collection job would take long time due to poor performance of MERGE query on MON_MODS_ALL$. Action / Repair: It is highly recommended to download & apply the Patch 29259068.

Links

Note: 2708396.1 - Merge on SYS.MON_MODS_ALL$ Running Slow During Auto Stats Job

Check ORACLE_PATCH 26749785 for RDBMS home

Recommendation

Benefit / Impact: Currently there is no option to disable automatic statistics advisor that runs everyday during maintenance window and incur huge SYSAUX space. This fix would provide the new preference "AUTO_STATS_ADVISOR_TASK" in DBMS_STATS to enable/disable AUTO_STATS_ADVISOR_TASK manually. Risk: The statistics advisor runs everyday during maintenance window and incurs high SYSAUX space. Action / Repair: It is highly recommended to download & apply the Patch 26749785. You can run the following command to disable advisor. SQL> exec DBMS_STATS.set_global_prefs('AUTO_STATS_ADVISOR_TASK','FALSE');

Links

Note: 2686022.1 - How To Disable Optimizer Statistics Advisor From 12.2 Onwards

Check ORACLE_PATCH 29867728 for RDBMS home

Recommendation

Benefit / Impact: This would fix one of the critical issues reported since 12.2 regarding SQL regression with UNION ALL PUSHED PREDICATE (JPPD) query transformation. Risk: There could be SQL plan regression found for SQL statement using UNION ALL where the plan shows 'UNION ALL PUSHED PREDICATE' and runs for long time. Action / Repair: It is highly recommended to download & apply the Patch 29867728. Remember to enable the fix control after applying the patch. ALTER SYSTEM SET "_FIX_CONTROL"="29867728:1" SCOPE=BOTH;

Links

Note: 29867728.8 - Bug 29867728 - UNION ALL PUSHED PREDICATE Is Chosen On 19c When UNION-ALL Plan May Have Less Cost

Check ORACLE_PATCH 31031240 for RDBMS home

Recommendation

Benefit / Impact: This would fix one of the issues reported in 19c for slow inserts to global temporary table (GTT). Risk: The elapsed time for Insert into global temporary table (GTT) keeps on increasing proportionately with growing no.of records to the GTT. Action / Repair: It is highly recommended to download & apply the Patch 31031240.

Check ORACLE_PATCH 29423227 for RDBMS home

Recommendation

Benefit / Impact: This would fix one of the issues reported in 19c for 'library cache lock' waits where the hang might happen when dropping a partition with global indexes created in the table. Risk: The SQL command to drop a partition with global indexes created in the table might hang with 'library cache lock' wait. Action / Repair: It is recommended to download & apply the Patch 29423227 if 'alter table drop partition' command is executed with global indexes

Links

Note: 2619066.1 - High Library Cache Lock Waits After Upgrading To 19C During Partition Index Maintenance